oracle ogg dg adg,案例:Oracle11G RAC环境TO RAC+ADG主备库切换过程+日志问题分析

天萃荷净

Oracle 11G RAC TO 11G RAC ADG 主备库切换SWITCHOVER过程

Oracle 11G RAC主库环境配置准备工作

SQL> select inst_id,database_role,OPEN_MODE from gv$database;

INST_ID DATABASE_ROLE OPEN_MODE

---------- ---------------- --------------------

2 PRIMARY READ WRITE

1 PRIMARY READ WRITE

[oracle@q9db02 ~]$ srvctl stop instance -d q9db -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from gv$database;

INST_ID DATABASE_ROLE OPEN_MODE

---------- ---------------- --------------------

1 PRIMARY READ WRITE

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

Oracle11G ADG备库环境配置准备工作

SQL> select inst_id,database_role,OPEN_MODE from gv$database;

INST_ID DATABASE_ROLE OPEN_MODE

---------- ---------------- --------------------

2 PHYSICAL STANDBY READ ONLY WITH APPLY

1 PHYSICAL STANDBY READ ONLY WITH APPL

[oracle@q9adg02 ~]$ srvctl stop instance -d q9db_adg -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from gv$database;

INST_ID DATABASE_ROLE OPEN_MODE

---------- ---------------- --------------------

1 PHYSICAL STANDBY READ ONLY WITH APPL

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

Oracle11G RAC主库切换日志,观察ADG备库

--主库

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

--备库

[oracle@q9adg01 trace]$ tail -f alert_q9db1.log

Tue Jun 25 15:35:27 2013

RFS[10]: Selected log 52 for thread 1 sequence 4777 dbid 844605368 branch 817913807

Tue Jun 25 15:35:28 2013

Archived Log entry 4889 added for thread 1 sequence 4776 ID 0x3545ffea dest 1:

Tue Jun 25 15:35:28 2013

Media Recovery Waiting for thread 1 sequence 4777 (in transit)

Tue Jun 25 15:35:28 2013

RFS[11]: Selected log 72 for thread 2 sequence 1630 dbid 844605368 branch 817913807

Recovery of Online Redo Log: Thread 1 Group 52 Seq 4777 Reading mem 0

Mem# 0: +DATA/q9db_adg/onlinelog/group_52.1564.818724635

Media Recovery Waiting for thread 2 sequence 1630 (in transit)

Recovery of Online Redo Log: Thread 2 Group 72 Seq 1630 Reading mem 0

Mem# 0: +DATA/q9db_adg/onlinelog/group_72.1575.818724653

Tue Jun 25 15:35:30 2013

Archived Log entry 4890 added for thread 2 sequence 1629 ID 0x3545ffea dest 1:

几乎同步进行表示主备日志传输应用正常

Oracle11G RAC主库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Database altered.

Oracle11G RAC ADG备库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Oracle研究中心继续处理主库

SQL> shutdown immediate

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, Data Mining

and Real Application Testing options

[oracle@q9db01 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 25 14:13:58 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.6034E+11 bytes

Fixed Size 2236968 bytes

Variable Size 2.5770E+10 bytes

Database Buffers 1.3422E+11 bytes

Redo Buffers 352468992 bytes

Database mounted.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

Oracle研究中心清理快照

--主库

SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

--备库

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.6034E+11 bytes

Fixed Size 2236968 bytes

Variable Size 2.7380E+10 bytes

Database Buffers 1.3261E+11 bytes

Redo Buffers 352468992 bytes

Database mounted.

SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

启动主备另外节点

--主库

[oracle@q9db01 ~]$ srvctl start instance -d q9db -i q9db2

--备库

[oracle@q9adg02 ~]$ srvctl start instance -d q9db_adg -i q9db2

补充说明:如果出现日志切换暂时不能传输

备库执行(因为重启动态监听没有马上别识别)

alter system register;

主库执行

alter system set log_archive_dest_state_2=enable;

至此Oracle研究中心案例操作Oracle 11G RAC TO 11G RAC ADG 主备库切换完成。

-----------------温馨提示--------------------

操作有风险，动手需谨慎

Oracle研究中心

www.oracleplus.net

本文由大师惜分飞分享，转载请尽量保留本站网址。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之案例:Oracle11G RAC环境TO RAC+ADG主备库切换过程+日志问题分析