Sunday, April 20, 2008

Manual Switchover.

Data Guard maintains the standby databases as transactionally consistent copies of the production database. Then, if the production database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role, minimizing the downtime associated with the outage.

Due to low network bandwidth or when using Standard Edition, we maintain manual dataguard, in which, we transfer the archive log files manually to the DR site and then apply them there. In such cases, it is very difficult to use oracle internal switch over mechanism. This document is prepared for the customers having manual dataguard and wants to do the switch over.

This document consider following pre-requisites.

 All Dataguard pre-requisites (same OS and Oracle version etc.) are fulfilled.
 Manual Dataguard setup is in place having standby database with standby controlfile.
 All the archive logs are properly applied on DR site.

Switch-over Process

1. Shutdown the primary database.
2. Make sure all the archive logs are transferred and applied on DR site.
3. Shutdown the standby database
4. Copy the controlfile and online redo log files of primary database to DR site.
5. Copy the standby controlfile from DR site to primary site.
6. Startup the primary database (Current standby database). Here we may have to do recovery to apply changes of online redo log files. This must be a complete recovery.
7. Startup the standby database (Current primary)
8. Check the databases & their role.

We have successfully completed the switchover activity and same steps can be followed for switch back activity.

Following is the demonstration of the above-mentioned steps. We will create a table and check the availability of it after switchover.

Primary Database –

SQL> create table std_test_1 as select * from dba_objects;


Table created.


SQL> insert into std_test_1 select * from std_test_1;


6165 rows created.


SQL> insert into std_test_1 select * from std_test_1;

12330 rows created.

SQL> commit;

Commit complete.

Note down the current sequence number of primary database.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
235

Now we will check the status of standby database. (Its current sequence no.)

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
233

The standby database is lagging of primary database. So we will recover the
standby database and sync it with primary.

SQL> recover standby database;
ORA-00279: change 50652 generated at 04/02/2008 22:32:37 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_234.DBF
ORA-00280: change 50652 for thread 1 is in sequence #234

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 50750 generated at 04/02/2008 22:36:08 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_235.DBF
ORA-00280: change 50750 for thread 1 is in sequence #235
ORA-00278: log file 'D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_234.DBF' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 50759 generated at 04/02/2008 22:36:14 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_236.DBF
ORA-00280: change 50759 for thread 1 is in sequence #236
ORA-00278: log file 'D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_235.DBF' no longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00308: cannot open archived log
'D:\ORACLE\DATAFILES\ORA9ISTD\ARCH\ORA9I_236.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

Check the current status of standby database.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
235

Now we are ready to proceed for switchover process. To start with, we will first
shutdown both primary and standby database.

Primary –

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

Standby –

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

We are doing manual switchover process, so we need to copy the control and
redo log files from primary to standby database. At the same time, we have to
copy the controlfile from standby database location to primary database.

Assuming copy process is over, we will startup the Current Primary (former
standby) database.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL>
SQL> alter database mount;

Database altered.

SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\DATAFILES\ORA9ISTD\SYSTEM01.DBF'

Here we are getting recovery error because this is standby database; the changes
lying in current redo logs (which we have copied) are yet to apply on current
primary. The important thing to note here is that, this recovery must be complete
recovery. Post to this, we will open the check the status of the database.

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
235

As shown above, the recovery has been completed and current primary (former
standby) database is up. We will check for test table created.

SQL> select count(1) from std_test_1;

COUNT(1)
----------
24660

We have successfully completed one phase of the switchover process. Now we
will startup current standby (former primary) database to proceed further.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
235

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
MOUNTED PHYSICAL STANDBY

The primary and standby databases are in sync. We will generate the archive log
files at primary site and will apply these files at standby database.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select max(sequence#) from v$log_history;

MAX(SEQUENCE#)
--------------
238

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
---------- ----------------
READ WRITE PRIMARY

We have generated three archive log files here and now we will copy and apply
them at standby database.

SQL> recover standby database;
ORA-00279: change 50759 generated at 04/02/2008 22:36:14 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_236.DBF
ORA-00280: change 50759 for thread 1 is in sequence #236

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 51098 generated at 04/02/2008 22:49:37 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_237.DBF
ORA-00280: change 51098 for thread 1 is in sequence #237
ORA-00278: log file 'D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_236.DBF' no
longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 51119 generated at 04/02/2008 22:49:50 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_238.DBF
ORA-00280: change 51119 for thread 1 is in sequence #238
ORA-00278: log file 'D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_237.DBF' no
longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 51126 generated at 04/02/2008 22:49:51 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_239.DBF
ORA-00280: change 51126 for thread 1 is in sequence #239
ORA-00278: log file 'D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_238.DBF' no
longer needed for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00308: cannot open archived log 'D:\ORACLE\DATAFILES\ORA9I\ARCH\ORA9I_239.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

The recovery is completed successfully. Here we have completed our switchover
process.

It is important to note that, in standby database, it is mandatory to have the database in force logging mode. This feature has been introduced by oracle in version 9i. For pre-9i versions, do check UNRECOVERABLE_TIME column value of V$DATAFILE view. This value must be less than the standby database creation time fir all existing datafiles. If any datafile is having value greater than standby creation time, we need to restore the backup of this datafile and continue with recovery. The unrecoverable_time value gets updated by oracle for any nologging operation. To avoid such nologging operations, do ensure that, all objects must be in logging mode.

Note – The above-mentioned steps can be used for switchback process.