archivelog mode setup in 9i RAC/non RAC, 10g and 11g

October 16, 2009 at 12:22 pm (Oracle) (archivelog mode 9i RAC non RAC 10g and 11g, archivelog mode setup in 9i 10g and 11g)

10g/11g non rac single node instance

enable archiving:

SQL> SELECT log_mode FROM v$database; or SQL> archive log list
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

disable archiving:

SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 26
Current log sequence 28
SQL>

9i non rac single node instance

SQL> ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
SQL> ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYSID/archive/’ SCOPE=spfile;
SQL> ALTER SYSTEM SET log_archive_format=’arch_%t_%s.arc’ SCOPE=spfile;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

9i rac

SQL> ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
SQL> ALTER SYSTEM SET log_archive_dest_1=’location=/u01/oradata/MYSID/archive/’ SCOPE=spfile;
SQL> ALTER SYSTEM SET log_archive_format=’arch_%t_%s.arc’ SCOPE=spfile;
SQL> ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

From the command line stop the entire cluster using:

srvctl stop database -d databasename

With the cluster down, connect to a single node and issue the following commands:

SQL> STARTUP MOUNT;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile;
SQL> SHUTDOWN IMMEDIATE;

From the command line start the entire cluster using:

srvctl start database -d databasename