技术建议和方案。
要求failover后不重建备库，并能够把failover的数据库重新切换回备库
主库为newtest,备库为snewtest
备库上已经开启了闪回
得到一个参考的SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4491930
查看闪回数据库特性是打开的。
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------
YES
然后我们在备库上开始failover
DGMGRL> failover to snewtest;
Performing failover NOW, please wait...
Failover succeeded, new primary is "snewtest"
操作很快完成，我们查看备库此时的状态和角色
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
当然这个步骤可以做一些读写操作之类的.
然后我们开始计划切回备库。
SQL> shutdown immediate
SQL> startup mount
闪回数据库到指定的SCN,
SQL> flashback database to scn 4491930;
Flashback complete.
切换这个新主库为备库
SQL> alter database convert to physical standby;
Database altered.
需要重启备库
SQL> shutdown immediate
SQL> startup mount
最关键的步骤,重新配置DG Broker
主库上删除DG Broker配置
SQL> alter system set dg_broker_start = false;
System altered.
SQL> !ps -ef |grep dmon
oracle 24648 24644 0 00:13 pts/3 00:00:00 /bin/bash -c ps -ef |grep dmon
oracle 24650 24648 0 00:13 pts/3 00:00:00 grep dmon
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ll
total 10048
-rw-r-----. 1 oracle dba 8192 Mar 30 00:07 dr1newtest.dat
-rw-r-----. 1 oracle dba 8192 Mar 30 00:07 dr2newtest.dat
-rw-rw----. 1 oracle dba 1544 Mar 12 00:02 hc_DBUA1321268.dat
-rw-rw----. 1 oracle dba 1544 Mar 27 21:10 hc_newtest.dat
-rw-r--r--. 1 oracle dba 982 Mar 21 23:12 initnewtest.ora
-rw-r--r--. 1 oracle dba 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle dba 24 Jan 31 20:16 lkNEWTEST
-rw-r-----. 1 oracle dba 1536 Jan 31 20:18 orapwnewtest
-rw-r-----. 1 oracle dba 10240000 Mar 27 23:53 snapcf_newtest.f
-rw-r-----. 1 oracle dba 3584 Mar 30 00:12 spfilenewtest.ora
[oracle@localhost dbs]$ rm -rf dr*newtest.dat
[oracle@localhost dbs]$ ll
total 10032
-rw-rw----. 1 oracle dba 1544 Mar 12 00:02 hc_DBUA1321268.dat
-rw-rw----. 1 oracle dba 1544 Mar 27 21:10 hc_newtest.dat
-rw-r--r--. 1 oracle dba 982 Mar 21 23:12 initnewtest.ora
-rw-r--r--. 1 oracle dba 2851 May 15 2009 init.ora
-rw-r-----. 1 oracle dba 24 Jan 31 20:16 lkNEWTEST
-rw-r-----. 1 oracle dba 1536 Jan 31 20:18 orapwnewtest
-rw-r-----. 1 oracle dba 10240000 Mar 27 23:53 snapcf_newtest.f
-rw-r-----. 1 oracle dba 3584 Mar 30 00:12 spfilenewtest.ora
备库上删除DG Broker配置
SQL> alter system set dg_broker_start = false;
System altered.
SQL> !ps -ef |grep dmon
oracle 15200 15198 0 00:15 pts/2 00:00:00 /bin/bash -c ps -ef |grep dmon
oracle 15202 15200 0 00:15 pts/2 00:00:00 grep dmon
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost backup_stage]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ll
total 10064
-rw-r-----. 1 oracle dba 20480 Mar 30 00:13 dr1snewtest.dat
-rw-r-----. 1 oracle dba 20480 Mar 30 00:08 dr2snewtest.dat
-rw-rw----. 1 oracle dba 1544 Mar 30 00:12 hc_newtest.dat
-rw-r--r--. 1 oracle dba 1062 Mar 21 23:30 initnewtest.ora
-rw-r-----. 1 oracle dba 24 Mar 21 23:38 lkSNEWTEST
-rw-r-----. 1 oracle dba 1536 Mar 27 23:34 orapwnewtest
-rw-r-----. 1 oracle dba 10240000 Mar 29 06:45 snapcf_newtest.f
-rw-r-----. 1 oracle dba 4608 Mar 30 00:15 spfilenewtest.ora
[oracle@localhost dbs]$ rm -rf dr*snewtest.dat
[oracle@localhost dbs]$ ll
total 10024
-rw-rw----. 1 oracle dba 1544 Mar 30 00:12 hc_newtest.dat
-rw-r--r--. 1 oracle dba 1062 Mar 21 23:30 initnewtest.ora
-rw-r-----. 1 oracle dba 24 Mar 21 23:38 lkSNEWTEST
-rw-r-----. 1 oracle dba 1536 Mar 27 23:34 orapwnewtest
-rw-r-----. 1 oracle dba 10240000 Mar 29 06:45 snapcf_newtest.f
-rw-r-----. 1 oracle dba 4608 Mar 30 00:15 spfilenewtest.ora
主库上重新配置 DG Broker
SQL> alter system set dg_broker_start =true;
System altered.
[oracle@localhost dbs]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> create configuration dg_newtest as primary database is newtest connect identifier is newtest;
Configuration "dg_newtest" created with primary database "newtest"
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration ;
Enabled.
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> add database snewtest as connect identifier is snewtest maintained as physical;
Database "snewtest" added
DGMGRL> enable database snewtest;
Enabled.
DGMGRL> show configuration;
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
snewtest - Physical standby database
Error: ORA-16525: the Data Guard broker is not yet available
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
此时提示备库的 Data Guard broker不可用
配置备库的DG Broker
SQL> alter system set dg_broker_start = true;
System altered.
在主库上再次查看dg broke 的状态
DGMGRL> show configuration
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
snewtest - Physical standby database
Error: ORA-16613: initialization in progress for database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
此时是初始化状态
open 备库
SQL> alter database open;
Database altered.
在主库上再次查看dg broke 的状态
DGMGRL> show configuration
Configuration - dg_newtest
Protection Mode: MaxPerformance
Databases:
newtest - Primary database
snewtest - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
查看备库的状态
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY