Activating the Standby Database

Activating the Standby Database - Failover Method

In the event of a disaster on the primary machine, you need to make the standby database active as soon as possible. In this example, it is assumed that the primary database server is not available and that the standby database needs to be activated as the primary database immediately. The standby database will need to be activated in its current state - no other redo from the primary database will be applied as it is assumed to be down The steps involved are:

1. If the standby database is in a RAC environment, stop all but one instance.
oracle $ srvctl stop instance -d database_name> -i instance_name

2. If the standby database is in managed recovery mode, it will need to be cancelled and the instance shutdown using the IMMEDIATE option:

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate

3. Startup the instance and then MOUNT the DB in standby mode:

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074152 bytes
Variable Size 1593838040 bytes
Database Buffers 536870912 bytes
Redo Buffers 14700544 bytes

SQL> alter database mount standby database;

Database altered.

4. If possible, archive the current redo logs on the primary database and transfer them (along with any remaining archived redo logs) to the standby machine.

5. Apply the primary database's archived logs by recovering the standby database:

SQL> recover standby database until cancel;

6. Cancel the recovery and activate the standby database. A CANCEL will need to be performed as this is Incomplete Recovery. In issuing the ALTER DATABASE ACTIVATE STANDBY DATABASE command, the standby bit in the control file is reset. This process takes the database back to nomount mode:

SQL> alter database activate standby database;

Database altered.

NOTE: This process can take several minutes to complete so be patient!

7. Shutdown and restart the new primary database. This step ensures that all file headers are reset and clears all buffers. Remember that in the previous step when activating the standby database, that the command puts the database back into nomount mode:

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

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

SQL> startup open
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2074152 bytes
Variable Size 1593838040 bytes
Database Buffers 536870912 bytes
Redo Buffers 14700544 bytes
Database opened.

NOTE: Since this is a Failover Scenario, you cannot copy online redo logs from the primary to the standby.

8. Shutdown the standby instance and backup if possible, then open the database for normal use. Users can now connect to the "new" primary database. The standby bit in the controlfile is now set, so you can never go back - the standby is now your primary database. Since the redo log sequence was reset when the standby was opened, it is a good idea to take a full backup at this point.

Activating the Standby Database : Switchover Method

The main advantage of a graceful switchover is that it avoids the resetlogs operation. This option is implicitly done by the ALTER DATABASE ACTIVATE STANDBY DATABASE command. By avoiding the resetlogs operation, the source database can resume its role as the standby database almost immediately with no data loss. Another feature to a graceful switchover is that it does not invalidate previous backups.

The prerequisites of initiating graceful switchovers are the following:

1. Production database is shutdown normal or shutdown immediate.
2. There is no loss of any archive logs that haven't been (yet) applied to the standby database. 3. All archive logs will be applied to the standby database.
4. Standby database is shutdown normal or shutdown immediate.
5. Source database's online redo logs are available and intact.
6. Target database is still intact (datafiles, control files are present) and has the same resetlogs version as the source database.
7. Loss of unrecoverable transactions (e.g. direct loader unrecoverable) is acceptable.
8. All steps have been validated and tested in a test environment first
9. Experienced DBAs with in-depth knowledge of database and standby recovery are managing the environment.


Switchover steps

1. Discontinue client logons and complete the last set of transactions on the primary database. All clients should be logged out.

2. In an Oracle 10g RAC environment, shutdown all instances except for one.

3. Shutdown the primary database:

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

4. Open the primary database in RESTRICTED mode:

SQL> startup restrict
ORACLE instance started.

Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

5. Archive the current log on the primary database:

SQL> alter system archive log current;
System altered.

6. Make sure the primary database and standby database are in sync. On both the primary and standby instances, issue the following:

SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
---------- --------------
1 1934
2 1718

Now, compare the results and make sure the Thread and Sequence # are the same. If the standby instance is ahead by 1 or none, you are in sync.

7. Initiate the switchover on the primary database.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

8. Once the step above has completed, log on to the standby database and issues the following command:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

9. Immediately return to the former primary database and issue a shutdown and mount the new standby database.

SQL> shutdown immediate
SQL> startup mount;

10. On the new primary/old standby, you can now open the database as the primary database.

SQL> alter database open;
Database opened.

11. Verify the new standby database status.

SQL> select name, database_role from v$database;

NAME DATABASE_ROLE
--------- ----------------
PHYSICAL STANDBY

12. Put the new standby/former primary database into managed recovery mode:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

13. Test the communications for archive by performing a log switch.

SQL> alter system switch logfile;
System altered.

SQL> alter system archive log current;
System altered.

14. On the new primary database instance, take a backup if possible. If the new primary is a RAC database, use server control to startup the other instances. Users can now connect to the "new" primary database.

15. On the new standby database, if the database is in a RAC cluster, connect as SYSDBA via SQL*PLUS and put into recovery mode for each instance in the cluster.

srvctl start instance -d database_name -i instance_name