Oracle Data Guard - Role Management

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96653/role_management.htm#1033701

switchover
- a reversible role transition between the primary database and one of its standby databases

failover
- tansitions a standby database to the primary role in response to a failure of the primary database

During any role transition, the amount of downtime required to complete the operation, the potential for data loss, and the effects on other standby databases in the configuration are determined by:

- The state of the primary database just before the transition
- The state of the standby database selected for the role transition at the time of the transition
- If the selected standby database was configured as a physical standby database or a logical standby database
- If the role transition is a switchover or a failover

Switch over operation

- used to reduce primary database downtime during planned outages, such as OS or hardware upgrades- takes place in two phases:
-- (1) an existing primary database is transitioned to a standby role
-- (2) a standby database is transitioned to the primary role

Note:
Because the primary and standby database releases must be the same at all times, do not use a switchover operation to perform a rolling upgrade of Oracle database software. However, it might be possible to use a switchover operation to perform a rolling upgrade of
system hardware.

Jul 21, 06 2:33 PM

Practise: perform a switchover to physical standby

On the current primary database
Step 1 Verify that it is possible to perform a switchover operation
SQL> select switchover_status from v$database;

expected to see: TO STANDBY
In my case showed: SESSION ACTIVE
maybe it's due to I haven't set up the initailization parameters for role transition.

Now following 5.8.2 to set up initialization parameters for swithover:

(1) create init.ora files on primary site, directly edit the parameters there
specify the following paramters in the initoemrep.ora file

Example 5-7 Primary Database: Standby Role Initialization Parameters

FAL_SERVER=oemrep2
FAL_CLIENT=oemrep
DB_FILE_NAME_CONVERT=('/standby','/primary')
LOG_FILE_NAME_CONVERT=('/standby','/primary')
STANDBY_ARCHIVE_DEST=/disk1/oracle/oradata/payroll/
STANDBY_FILE_MANAGEMENT=AUTO

Notes: it seems that *_covert paramters can not be set through 'alter system set ...' command

(2) on the standby site:
SQL> alter system set log_archive_dest_2='SERVICE=oemrep';
SQL> alter system set log_archive_dest_state_2=enable ( Note: already enabled)
SQL> alter system set fal_server=oemrep
SQL> alter system set fal_client=oemrep2

(3) shutdown the primary db and startup using pfile.
verifying all the parameters are set for switch over
create spfile from pfile
(4)
On the original primary database (the new standby) set this parameter to allow the receipt of the redo from the new primary database. For example:
SQL> ALTER SYSTEM SET REMOTE_ARCHIVE_ENABLE=RECEIVE SCOPE=MEMORY;


On the new primary database (the former standby) set this initialization parameter to allow the sending of redo to the standby database.SQL> ALTER SYSTEM SET REMOTE_ARCHIVE_ENABLE=SEND SCOPE=MEMORY;

Now verifying again on primary db
SQL> select switchover_status from v$database;
still get session_active
close the session on standby db


DOUBLE CHECK INITILIZATON PARAMETERS:

Primary Database: Primary Role Initialization Parameter
log_archive_dest_1 = 'LOCATION=e:\oracle\oradata\oemrep mandatory'
log_archive_dest_2 = 'SERVICE=oemrep2'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
log_archive_format=oemrep_arch%s.arc
remote_archive_enable=true

Primary Database: Standby Role Initialization Parameter
fal_server=OEMREP2
fal_client=OEMREP
db_file_name_convert=('e:\oracle\oradata\standby\oemrep2\','e:\oracle\oradata\oemrep\')
log_file_name_convert=('e:\oracle\oradata\standby\oemrep2\','e:\oracle\oradata\oemrep\')
standby_archive_dest= e:\oracle\oradata\oemrep
standby_file_management=AUTO
(FAL: fetch archive log)

Standby Database: Standby Role Initilization Parameters
fal_server=OEMREP
fal_client=OEMREP2
db_file_name_convert=('e:\oracle\oradata\oemrep\', 'e:\oracle\oradata\standby\oemrep2\')
log_file_name_convert=('e:\oracle\oradata\oemrep\', 'e:\oracle\oradata\standby\oemrep2\')
standby_archive_dest=e:\oracle\oradata\standby\oemrep2
standby_file_management=AUTO
log_archive_dest_1='LOCATION=e:\oracle\oradata\standby\oemrep2'
log_archive_dest_state_1=enable
log_archive_format=oemrep_arch%s.arc
remote_archive_enable=TRUE

Standby Database: Primary Role Initialization Parameters
log_archive_dest_2='SERVICE=oemrep'
log_archive_dest_state_2=enable


11:48am continue - perform switchover

Enabling Initialization Parameters During Role Transition

On the original primary database (the new standby)
For example:
SQL> ALTER SYSTEM SET REMOTE_ARCHIVE_ENABLE=RECEIVE SCOPE=MEMORY;


On the new primary database (the former standby)
SQL> ALTER SYSTEM SET REMOTE_ARCHIVE_ENABLE=SEND SCOPE=MEMORY;

(in my case I set the 'TRUE', so no need to perform this step)


=> do Step 1 again: Verify that it is possible to perform a switchover operation.
SQL> select switchover_status from v$database

still got session active; igore this continue

Step 2 Initiate the switchover operation on the primary database.
on the primary database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

instead, I use:
SQL> alter database commit to switchover to standby with session shutdown;


Step 3 Shut down and restart the former primary instance.
Shut down the former primary instance and restart it without mounting the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT;


Mount the database as a physical standby database:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;


At this point in the switchover process, both databases are configured as standby databases

On the target physical standby database
Step 4 Verify the switchover status in the V$DATABASE view.

After you transition the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, you should verify if the switchover notification was processed by the target standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database.

For example:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
SWITCHOVER PENDING
1 row selected

DS. got 'session active' ignore continue

Step 5 Switch the physical standby database role to the primary role.

SQL>alter database commit to swithover to primary with session shutdown;

Step 6 Shut down and restart the new primary database.
Shut down the target standby instance and restart it using the appropriate initialization parameters for the primary role:
SQL> SHUTDOWN;
SQL> STARTUP;

Step 7 Start managed recovery operations and log apply services.
Issue the following statement to begin managed recovery operations on the new physical standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the new primary database
Step 8 Begin sending redo data to the standby databases.
Issue the following statement on the new primary database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;


---- practise of switchover to a physcical standby ended ---

Note: SQL> alter system set remote_archive_enable=send;
alter system set remote_archive_enable=send
*ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

DS: this is due to the remote_archive_enable is a static parameter