Thursday, May 5, 2011

How to change the database to archive log mode from noarchive log mode in oracle 10G or 11g ?

How to change the database to archive log mode from noarchive log mode in oracle 10G ?
====================================================================================

Steps are:-

SQL>create pfile='c:\temp\init.ora' from spfile;
SQL>created;
SQL>shutdown immediate;

edit the init.ora file by adding the following information:
NOTE: You can add upto 10 archive log destination:

*.LOG_ARCHIVE_DEST_1='LOCATION=C:\oracle10GDB\arc_1'
NOTE: directory should be changed to your specific location:

*.LOG_ARCHIVE_DEST_2='LOCATION=C:\oracle10GDB\arc_2'
NOTE: directory should be changed to your specific location:

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

sql>startup mount pfile='c:\temp\init.ora'
ORACLE instance started.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle10GDB\arc_2
Oldest online log sequence 3
Next log sequence to archive 4
Current log sequence 4

SQL> create spfile from pfile='c:\temp\init.ora';
File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle10GDB\arc_2
Oldest online log sequence 3
Next log sequence to archive 4
Current log sequence 4



NOte:-[LOG_ARCHIVE_START parameter is no longer required to be set in order to run the database in ARCHIVELOG mode. This parameter is deprecated in Oracle 10g.
Also, LOG_ARCHIVE_FORMAT must be in the format: %s,%t,%r. %s log sequence number, %t thread number, %r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
]

No comments: