how to change archive log dest ?
1.check current status and setting
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dblog1x3
Oldest online log sequence 9
Next log sequence to archive 13
Current log sequence 13
SQL> show parameter archive
log_archive_dest /dblogwf #we need to change this value to /dblog1
log_archive_duplex_dest 'null' #we want to add this value to /dblog1bk
log_archive_format %t_%s_%r.ARC # we need to change this value to 'SS%s.ARC'
2.check god damn spfile and pfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------------------------------------------------
spfile string
SQL> alter system reset open_cursors scope=spfile sid='*';
alter system reset open_cursors scope=spfile sid='*'
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
unknow bug , oracle miss spfile. but the instance still use this god damn spfile, so you can see but can't change any thing
3.fore recreate spfile
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2083728 bytes
Variable Size 318768240 bytes
Database Buffers 922746880 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> show parameter pfile
NAME TYPE
------------------------------------ ----------------------
spfile /ora10g/dbs/spfilewebflow.ora
SQL> show parameter spfile;
NAME TYPE
------------------------------------ ----------------------
spfile /ora10g/dbs/spfilewebflow.ora
4.now we can change any parameter in spfile
SQL> ALTER SYSTEM SET log_archive_dest ='/dblog1' scope=both;
System altered.
SQL> ALTER SYSTEM SET log_archive_duplex_dest='/dblog1bk' scope=both;
System altered.
SQL> alter system set log_archive_format='SS%s.ARC' scope=spfile;
System altered.
5.update all pfile/spfile
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-19905: log_archive_format must contain %s, %t and %r
6.it is no way to use only one %s, so change pfile log_archive_format='SS_%s%r%t.ARC'
Format options available on 10g
%s – log sequence number
%S – log sequence number, zero filled
%t – thread number, needed when running RAC as each node creates it’s own archivelog
%T – thread number, zero filled, needed when running RAC as each node creates it’s own archivelog
%a – activation ID
%d – database ID
%r – resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.
In 10g, %s, %t, %r are required to be present in the parameter, if it doesn’t the database fail to start with the error ORA-19905: log_archive_format must contain %s, %t and %r. Using this format makes it the archive log filename unique for that instance.
how to diable spfile/i don't want to use spfile anymore
1.create pfile from spfile
2.shutdown immediate / startup pfile=your new pfile
留言列表
