使用sqlplus实现Data Guard的swtichover

当我们配置Data Guard环境时就应该想到，standby是有一天会成为primary的，因此在配置如log_archive_dest_2之类的参数上应该和目

1. 环境介绍

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

tnsnames内容如下：
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. 前提条件

在primary上执行：

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

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

在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. 验证主库可执行switchover
SQL> select switchover_status from v$database;

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

结果为‘TO STANDBY’或者‘SESSIONS ACTIVE’代表可以switchover

4.在主库上switchover，将主库变为physical standby

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

Database altered.

alert日志如下：
Primary will check for some target standby to have received alls 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 received all redo
Backup controlfile written to trace file /u01/Oracle11R2/diag/rdbms/stydb/stydb/trace/stydb_ora_951.trc
Clearing standby activation ID 2282061935 (0x8805806f)
The primary database controlfile was created using the
'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. 重启原主库

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. 验证原physical standby可以switchover至primary

SQL> select switchover_status from v$database;

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

7. 将原physical standby switchover成主库

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

Database altered.

8. 验证角色的更改

101机器：
SQL> select database_role from v$database;

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

102机器：
SQL> select database_role from v$database;

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

9. 查看或者修改log_archive_dest_2

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

System altered.

10. 重启primary standby机器

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.验证数据的同步

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.小结
当我们配置Data Guard环境时就应该想到，，standby是有一天会成为primary的，因此在配置如log_archive_dest_2之类的参数上应该和目前的primary库同步配置。