Use sqlplus to implement Data Guard's swtichover

1. Environment Introduction

	Site	Unique_db_name
Primary	192.168.1.102	Stydb
Physical standby	192.168.1.101	Ql

SQL> select protection_level from v $ database;

PROTECTION_LEVEL
--------------------
MAXIMUM PERFORMANCE

The content of tnsnames is as follows:
STYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.102) (PORT = 1521 ))
)
(CONNECT_DATA = (SERVICE_NAME = stydb ))
)

PRIDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.101) (PORT = 1521 ))
)
(CONNECT_DATA = (SERVICE_NAME = ql ))
)

2. Prerequisites

Run the following command on primary:

SQL> select status, gap_status from v $ archive_dest_status where dest_id = 2;

STATUS GAP_STATUS
---------------------------------
VALID NO GAP

Run the following command on primary standby:
SQL> select name, value, datum_time from v $ dataguard_stats;

NAMEVALUE DATUM_TIME
----------------------------------------------------------------
Transport lag + 00 00:00:00 06/24/2012 10:29:57
Apply lag + 00 00:00:00 06/24/2012 10:29:57
Apply finish time
Estimated startup time18

3. Verify that the master database can run switchover
SQL> select switchover_status from v $ database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

The result is 'to standby' or 'sessionactivity', which indicates switchover is supported.

4. switchover on the master database and change the master database to physical standby

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

The alert Log is as follows:
Primary will check for some target standby to have received balls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also encoded ed all redo
Backup controlfile written to trace file/u01/Oracle11R2/diag/rdbms/stydb/trace/stydb_ora_951.trc
Clearing standby activation ID 2282061935 (0x8805806f)
The primary database controlfile was created using
'Maxlogfiles 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
Standby redo logfiles that match the primary database:
Alter database add standby logfile 'srl1. F' SIZE 52428800;
Alter database add standby logfile 'srl2. F' SIZE 52428800;
Alter database add standby logfile 'srl3. F' SIZE 52428800;
Alter database add standby logfile 'srl4. f'size 52428800;
Archivelog for thread 1 sequence 49 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete-Database shutdown required
Completed: alter database commit to switchover to physical standby with session shutdown

5. Restart the original master database

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
Database mounted.

6. Verify that the original physical standby can be switchover to primary

SQL> select switchover_status from v $ database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

7. Turn the original physical standby switchover into the master database

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

Database altered.

8. Verify Role Changes

101 machines:
SQL> select database_role from v $ database;

DATABASE_ROLE
----------------
PRIMARY

102 machines:
SQL> select database_role from v $ database;

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

9. View or modify log_archive_dest_2

SQL> alter system set log_archive_dest_2 = 'service = stydb ';

System altered.

10. Restart the primary standby machine.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
Database mounted.
Database opened.

SQL> select open_mode from v $ database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

11. Verify Data Synchronization

Primary:

SQL> create user t identified by t;

User created.

SQL> grant connect to t;

Grant succeeded.

Physical standby:

SQL> conn t/t;
Connected.

12. Summary
When we configure the Data Guard environment, we should think that standby will become primary one day. Therefore, we should synchronize the configuration of parameters such as log_archive_dest_2 with the current primary database.