Friday 22 July 2022

How to put newly created Oracle database in archivelog mode?


SQL> ARCHIVE LOG LIST


SQL> alter system set log_archive_dest_1='LOCATION=/oracle/archive/ORADB' scope=both;



SQL> ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;


SQL> shut immediate


SQL> startup mount


SQL> alter database archivelog;


SQL> alter database open;


SQL> ARCHIVE LOG LIST

or

SQL> SELECT log_mode FROM v$database;


SQL> alter system switch logfile;



Note:


By default oracle creates archive logs in .dbf format.

which will confuse DBA's, because database datafiles also having same extention.

hence we need to change it to some meaningful format like .arc or .arch


%s : log sequence number

%S : log sequence number, zero filled

%t : thread number

%T : thread number, zero filled

%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


Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros.










No comments:

Post a Comment

How to put newly created Oracle database in archivelog mode?

SQL> ARCHIVE LOG LIST SQL> alter system set log_archive_dest_1='LOCATION=/oracle/archive/ORADB' scope=both; SQL> ALTER SYST...