oracle 查询 adg 状态,Oracle11gr2_ADG管理之在备库上模拟failover的过程实战

###技术建议和方案。 要求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