Modify the archive mode in the RAC Environment

The archive mode switching in the RAC environment is slightly different from that in a single instance, mainly because of the differences in shared storage. In this case, we can switch the RAC database to a non-cluster state and switch the archive mode on an instance to complete the archive Mode Conversion of the RAC database. This document describes how to switch from non-archive mode to archive mode, but not archive mode.

1. main steps:

Back up spfile to prevent database startup failure due to Parameter Modification failure

Set cluster_database to false.

Start a single instance to the mount status

Place the database in archive mode (alter database archivelog/noarchivelog)

Modify the cluster parameter cluster_database to true.

Close a single instance

Start cluster Database

2. Environment

Oracle @ bo2dbp: ~> Cat/etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64)-Kernel \ r (\ l ).

Oracle @ bo2dbp: ~> Sqlplus-v

SQL * Plus: Release 10.2.0.3.0-Production

Use asm storage to store archived logs

3. Modify the cluster database to archive Mode

Oracle @ bo2dbp: ~> Export ORACLE_SID = ora10g1

Oracle @ bo2dbp: ~> Sqlplus/as sysdba

SQL * Plus: Release 10.2.0.3.0-Production on Mon Dec 24 16:53:18 2012

Copyright (c) 1982,200 6, Oracle. All Rights Reserved.

Connected:

Oracle Database 10g Release 10.2.0.3.0-64bit Production

With the Real Application Clusters option

SQL> archive log list; --> View the archiving mode of the current database

Database log mode No Archive Mode --> non-Archive mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 59

Current log sequence 60

SQL> select instance_name, host_name, status from gv $ instance;

INSTANCE_NAME HOST_NAME STATUS

------------------------------------------------

Ora10g1 bo2dbp OPEN

Ora10g2 bo2dbs OPEN

SQL> show parameter cluster --> View cluster Parameters. If cluster_database is set to true, it indicates the cluster database. Otherwise, it is not a cluster database.

NAME TYPE VALUE

-----------------------------------------------------------------------------

Cluster_database boolean TRUE

Cluster_database_instances integer 2

Cluster_interconnects string

SQL> create pfile = '/u01/oracle/db/dbs/ora10g_0000.ora' from spfile; --> back up spfile first

File created.

SQL> alter system set cluster_database = false scope = spfile sid = '*'; --> modify this parameter to a non-cluster database. This parameter is a static parameter and must be set to scope = spfile.

System altered.

Oracle @ bo2dbp: ~> Srvctl stop database-d ora10g --> close database

Oracle @ bo2dbp: ~> Srvctl start instance-d ora10g-I ora10g1-o mount --> start a single instance to the mount status

Oracle @ bo2dbp: ~> Sqlplus/as sysdba

SQL> select instance_name, status from v $ instance;

INSTANCE_NAME STATUS

----------------------------

Ora10g1 MOUNTED

SQL> alter database archivelog; --> change database to archive Mode

Database altered.

SQL> alter system set cluster_database = true scope = spfile sid = '*'; --> change the database to cluster mode.

System altered.

SQL> ho srvctl stop instance-d ora10g-I ora10g1 --> close the current instance

SQL> ho srvctl start database-d ora10g --> start cluster database

SQL> archive log list;

ORA-03135: connection lost contact

SQL> conn/as sysdba

Connected.

SQL> archive log list; --> View archiving Mode

Database log mode Archive Mode --> Archive mode

Automatic archival Enabled --> Automatic archiving

Archive destination USE_DB_RECOVERY_FILE_DEST --> the Archive location is the value of DB_RECOVERY_FILE_DEST.

Oldest online log sequence 60 --> The following is the sequence information.

Next log sequence to archive 61

Current log sequence 61

SQL> show parameter db_recovery_file

NAME TYPE VALUE

-----------------------------------------------------------------------------

Db_recovery_file_dest string + REV

Db_recovery_file_dest_size big integer 2G

4. Archive Verification

SQL> select inst_id, name, thread #, sequence #, status from gv $ archived_log; --> NO archived logs currently

No rows selected

SQL> alter system switch logfile; --> archive on instance 1

System altered.

SQL> col name format a65

SQL> select inst_id, name, thread #, sequence #, status from gv $ archived_log; --> the logs with 61 sequence are archived.

INST_ID name thread # SEQUENCE # S

------------------------------------------------------------------------------------------------

1 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

SQL> select name, thread #, sequence #, status from v $ archived_log; --> the following is an instance-level view.

Name thread # SEQUENCE # S

--------------------------------------------------------------------------------------

+ REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

SQL> conn system/oracle @ ora10g2 --> connect to instance 2

Connected.

SQL> show parameter instance_name;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Instance_name string ora10g2

SQL> alter system switch logfile; --> archive on instance 2

System altered.

SQL> select inst_id, name, thread #, sequence #, status from gv $ archived_log;

--> Logs with a sequence of 43 are archived.

--> Note that when querying this view, the same archived log is visible to other instances in addition to its own instances.

INST_ID name thread # SEQUENCE # S

------------------------------------------------------------------------------------------------

1 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

1 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_43.458.802893283 2 43

2 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

2 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_43.458.802893283 2 43

--> View the log status

--> Note that the v $ log view displays the group and status of the two instances.

--> Here we use thread # To differentiate. If thread # is set to 1, the log group on instance 1 has 1, 2, and 1 is in the current status. thread #2 is similar.

SQL> select * from v $ log;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM

-------------------------------------------------------------------------------------------

1 1 62 52428800 2 no current 4314741 24-DEC-12

2 1 61 52428800 2 yes active 4312116 24-DEC-12

3 2 43 52428800 2 yes active 4312300 24-DEC-12

4 2 44 52428800 2 no current 4315097 24-DEC-12

--> Author: Robinson

--> Archive the current log. Note that this command is equivalent to alter system switch logfile under a single instance.

--> In the rac environment, the current logs on all instances are archived.

SQL> alter system archive log current;

System altered.

--> The following query verifies the preceding description.

--> Log 62 and 44 are the archived logs generated at the same time by the preceding command.

SQL> select inst_id, name, thread #, sequence #, status from gv $ archived_log;

INST_ID name thread # SEQUENCE # S

------------------------------------------------------------------------------------------------

2 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

2 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_43.458.802893283 2 43

2 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_44.456.802894343 2 44

2 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_62.457.802894341 1 1 62

1 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_61.459.802892953 1 61

1 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_43.458.802893283 2 43

1 + REV/ora10g/archivelog/2012_12_24/thread_2_seq_44.456.802894343 2 44

1 + REV/ora10g/archivelog/2012_12_24/thread_eclipseq_62.457.802894341 1 1 62

8 rows selected.

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html