Oracle 10g RAC 配置物理dataguard系列4:switchover及功能测试

在上一篇文章Oracle 10g RAC 配置物理dataguard系列3:物理备库配置步骤中,我们成功地给双节点的RAC主库成功配置Physical Standby database。在本篇中,我们开始对这套由双节点RAC主库+单实例Physical Standby database的dataguard环境做下述3点的功能测试。

  • 主库创建数据文件,确认物理备库是否自动生成对应的数据文件?

  • 主库做事务操作,在物理备库上确认是否可以看到对应的记录?

  • 主备库完成一次switchover的Role transaction。

测试一:主库创建数据文件,备库确认能否看到对应的数据文件?

  • 1 首先,在主库上创建一个测试表空间TEST:

[oracle@oracle-rac1 arch1]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 13:58:23 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba;
Connected.
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      glndb
SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +ORADATA
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL> create tablespace TEST datafile size 5m autoextend on;

Tablespace created.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL> 
  • 2 然后,在物理备库上查看是否生成该文件?

[oracle@ora10grac-dg arch2]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 14:50:43 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL> 

发现,在备库上并没有看到我们预期的TEST表空间被自动创建出来,说明数据数据并没有同步到备库上来。其实,原因是备库并没有启动redo apply,也没有相应的MRP0后台进程。

  • 3 其次,我们在备库上启动Redo Apply:

SQL> show parameter instance_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
instance_name                        string   pridb
SQL> show parameter db_unique_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_unique_name                       string   pridb
SQL> show parameter db_create_file

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_create_file_dest                  string   +ORADATA
SQL> show parameter db_file_name_convert

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_file_name_convert                 string   +ORADATA/glndb/, +ORADATA/pridb/
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

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

Database altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         2 +ORADATA/pridb/datafile/test.274.775925977
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

7 rows selected.

SQL> 

此时,我们可以看到一旦在备库启用redo apply之后,我们几乎可以立即看到备库上自动创建出预期的TEST表空间,说明测试成功!因为,我们主库上log_archive_dest_1参数是是:service=pridb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=pridb 同时,我们在物理备库的alert日志文件里也看到了下述信息:

Wed Feb 22 14:59:29 CST 2012
alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:29 CST 2012
Attempt to start background Managed Standby Recovery process (pridb)
MRP0 started with pid=14, OS id=18837
Wed Feb 22 14:59:29 CST 2012
MRP0: Background Managed Standby Recovery process started (pridb)
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 3 processes
Wed Feb 22 14:59:35 CST 2012
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 2 sequence 3297 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 2 Group 8 Seq 3297 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_8.266.775479229
Media Recovery Waiting for thread 1 sequence 7745 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7745 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_3.263.775479203
Wed Feb 22 14:59:35 CST 2012
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:38 CST 2012
Successfully added datafile 2 to media recovery
Datafile #2: '+ORADATA/pridb/datafile/test.274.775925977'
  • 4 最后,我们在主库上删除TEST表空间后,同样在备库上看到TEST表空间自动被删除!至此,说明我们的Dataguard功能测试一完成。

主库删TEST:

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL> 

备库确认:

Wed Feb 22 15:11:07 CST 2012
Recovery deleting file #2:'+ORADATA/pridb/datafile/test.274.775925977' from controlfile.
Deleted Oracle managed file +ORADATA/pridb/datafile/test.274.775925977
Recovery dropped tablespace 'TEST'
.....
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL> 

测试二:主库创建测试数据,到备库上去查看,query是否同步?

  • 1 主库创建测试数据:

SQL> conn human/hr
Connected.
SQL> create table test(id number,name varchar2(20));

Table created.

SQL> insert into test values(1,'WWW.OracleOnLinux.CN');

1 row created.

SQL> insert into test select * from test;

1 row created.

SQL> insert into test select * from test;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL> 
  • 2 备库确认:

SQL> select name,open_mode from v$database;

NAME                OPEN_MODE
------------------- ----------
GLNDB               MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY

SQL> conn human/hr
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL> 
  • 3 最后,发现备库停止Redo Apply后,并将数据库以read only方式打开后,我们可以看到数据已经同步到物理备库!说明功能测试二已经达到预期目的!

测试三:角色转换之switchover

  • 1 物理备库置于MOUNT状态。在switchover过程中,如果目标库是物理备库的话,最好是将物理备库置于MOUNT状态,当然也可以置于OPEN READ ONLY方式,不过官方文档说处于OPEN READ ONLY方式时,可能会延长switchover的时间。

另:在physical standby database配置下如需switchover时,如果主库、物理备库都是RAC数据库时,需要先将主库的其它实例停止,只留一个实例运行;然后,将物理备库的其它实例停止,只留一个实例运行。即:如果主、备库都是RAC数据库的话,要确保主、备库均留一个实例运行。

在这里,我们的物理备库是单实例数据库,所以只需将该库置于MOUNT状态:

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> 
  • 2 RAC主库停止glndb2实例,只留glndb1运行:

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    OFFLINE   OFFLINE               
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac1 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ 
  • 3 确认主库switchover状态。如果是to standby状态,说明一切正常。当然,也有可能是SESSIONS ACTIVE状态,不过也不要紧,这只是说明在主库上还有其他会话连接中,我们可以断开这些会话,或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326322 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE SESSIONS ACTIVE

SQL> 
  • 4 切换原RAC主库到备库角色。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> select * from v$log;
select * from v$log
              *
ERROR at line 1:
ORA-01507: database not mounted


SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database
                                                                                                *
ERROR at line 1:
ORA-01507: database not mounted


SQL> !ps  -ef |grep ora_
oracle   18271     1  0 14:04 ?        00:00:00 ora_o000_glndb1
oracle   22321     1  0 11:31 ?        00:00:01 ora_pmon_glndb1
oracle   22323     1  0 11:31 ?        00:00:00 ora_diag_glndb1
oracle   22325     1  0 11:31 ?        00:00:00 ora_psp0_glndb1
oracle   22332     1  0 11:31 ?        00:00:14 ora_lmon_glndb1
oracle   22334     1  0 11:31 ?        00:00:10 ora_lmd0_glndb1
oracle   22341     1  0 11:31 ?        00:00:07 ora_lms0_glndb1
oracle   22345     1  0 11:31 ?        00:00:07 ora_lms1_glndb1
oracle   22350     1  0 11:31 ?        00:00:02 ora_mman_glndb1
oracle   22356     1  0 11:31 ?        00:00:01 ora_dbw0_glndb1
oracle   22360     1  0 11:31 ?        00:00:02 ora_lgwr_glndb1
oracle   22370     1  0 11:31 ?        00:00:04 ora_ckpt_glndb1
oracle   22378     1  0 11:31 ?        00:00:10 ora_smon_glndb1
oracle   22391     1  0 11:31 ?        00:00:00 ora_reco_glndb1
oracle   22452     1  0 11:31 ?        00:00:03 ora_lck0_glndb1
oracle   22468     1  0 11:31 ?        00:00:00 ora_asmb_glndb1
oracle   22482     1  0 11:31 ?        00:00:00 ora_rbal_glndb1
oracle   30712     1  0 17:20 ?        00:00:00 ora_s001_glndb1
oracle   30730     1  0 17:20 ?        00:00:00 ora_o001_glndb1
oracle   30797     1  0 17:20 ?        00:00:00 ora_o002_glndb1
oracle   31131     1  0 17:21 ?        00:00:00 ora_d000_glndb1
oracle   31135     1  0 17:21 ?        00:00:00 ora_mmon_glndb1
oracle   31144     1  0 17:21 ?        00:00:00 ora_mmnl_glndb1
oracle   31683  3106  0 17:22 pts/2    00:00:00 /bin/bash -c ps  -ef |grep ora_
oracle   31685 31683  0 17:22 pts/2    00:00:00 grep ora_

SQL> 
  • 5 关闭原主库实例glndb1,重新启动glndb1到MOUNT状态:

SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show user;
USER is "SYS"
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL> 
  • 6 确认原备库switchover状态。如果是TO PRIMARY状态,说明一切正常。当然,也有可能是SESSIONS ACTIVE状态,不过也不要紧,这只是说明在备库上还有其他会话连接中,我们可以断开这些会话,或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO  PRIMARY ]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL>select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL> 
  • 7 切换原备库至主库角色。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select open_mode,switchover_status,database_role from v$database;
select open_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> 

此时,原备库alert日志信息如下:

......
......
Wed Feb 22 17:35:52 CST 2012
alter database commit to switchover to primary
Wed Feb 22 17:35:52 CST 2012
ALTER DATABASE SWITCHOVER TO PRIMARY (pridb)
Wed Feb 22 17:35:52 CST 2012
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 447326597
Online log +ORADATA/pridb/onlinelog/group_1.269.775479753: Thread 1 Group 1 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_2.270.775479755: Thread 1 Group 2 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_4.271.775479755: Thread 2 Group 4 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_5.272.775479757: Thread 2 Group 5 was previously cleared
Standby became primary SCN: 447326595
Wed Feb 22 17:35:53 CST 2012
Switchover: Complete - Database shutdown required (pridb)
Completed: alter database commit to switchover to primary
Wed Feb 22 17:35:58 CST 2012
SUCCESS: diskgroup ORADATA was dismounted
Wed Feb 22 17:36:05 CST 2012
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Wed Feb 22 17:36:15 CST 2012
ARCH shutting down
ARC0: Archival stopped
Wed Feb 22 17:36:20 CST 2012
ARCH shutting down
ARC1: Archival stopped
.....
.....
  • 8 完成原主库到新主库的角色转换。

这里需要注意:

  • ①如果该原备库自从上次启动以来,从来没有以READ ONLY方式打开过,那么可以直接通过ALTER DATABASE OPEN命令来打开;
  • ②如果该原备库自从上次启动以来,曾经以READ ONLY方式打开过,那么需要先SHUTDOWN,然后STARTUP。这里,我们的原物理备库复合第②中情况,需要先SHUTDOWN,然后STARTUP。
SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
READ WRITE SESSIONS ACTIVE      PRIMARY

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
         1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
         2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES          UNUSED                 0
         5          2          0   52428800          1 YES          UNUSED                 0

SQL> 
  • 9 在新备库上[原RAC主库]启动glndb2实例至MOUNT状态,并启用redo apply,确认数据是否能与新主库[原单实例备库]同步?

SQL> show user;
USER is "SYS"
SQL> show parameter instance;
ORA-01034: ORACLE not available


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             520094216 bytes
Database Buffers         1073741824 bytes
Redo Buffers               14680064 bytes
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    OFFLINE   OFFLINE               
ora....db1.srv application    OFFLINE   OFFLINE               
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    OFFLINE   OFFLINE               
ora....db2.srv application    OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ ps -ef | grep ora_
oracle   26363     1  0 17:42 ?        00:00:00 ora_pmon_glndb2
oracle   26370     1  0 17:42 ?        00:00:00 ora_diag_glndb2
oracle   26377     1  0 17:42 ?        00:00:00 ora_psp0_glndb2
oracle   26384     1  0 17:42 ?        00:00:00 ora_lmon_glndb2
oracle   26387     1  2 17:42 ?        00:00:01 ora_lmd0_glndb2
oracle   26393     1  2 17:42 ?        00:00:01 ora_lms0_glndb2
oracle   26402     1  2 17:42 ?        00:00:01 ora_lms1_glndb2
oracle   26406     1  6 17:42 ?        00:00:03 ora_mman_glndb2
oracle   26415     1  0 17:42 ?        00:00:00 ora_dbw0_glndb2
oracle   26417     1  0 17:42 ?        00:00:00 ora_lgwr_glndb2
oracle   26424     1  0 17:42 ?        00:00:00 ora_ckpt_glndb2
oracle   26426     1  0 17:42 ?        00:00:00 ora_smon_glndb2
oracle   26433     1  0 17:42 ?        00:00:00 ora_reco_glndb2
oracle   26435     1  0 17:43 ?        00:00:00 ora_cjq0_glndb2
oracle   26442     1  0 17:43 ?        00:00:00 ora_mmon_glndb2
oracle   26445     1  0 17:43 ?        00:00:00 ora_mmnl_glndb2
oracle   26451     1  0 17:43 ?        00:00:00 ora_d000_glndb2
oracle   26457     1  0 17:43 ?        00:00:00 ora_s000_glndb2
oracle   26506     1  0 17:43 ?        00:00:00 ora_lck0_glndb2
oracle   26706     1  0 17:43 ?        00:00:00 ora_pz99_glndb2
oracle   26860 26712  0 17:43 pts/1    00:00:00 grep ora_
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> alter database mount;

Database altered.

SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1       7750   52428800          1 YES CLEARING             447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2       3297   52428800          1 YES INACTIVE             447302887 2012/02/22 14:07:29
         5          2       3298   52428800          1 YES ACTIVE               447326021 2012/02/22 17:11:49

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

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          0   52428800          1 YES UNUSED               447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES UNUSED               447302887 2012/02/22 14:07:29
         5          2          0   52428800          1 YES UNUSED               447326021 2012/02/22 17:11:49

SQL>

新主库切换日志:

SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
     2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7751   52428800          1 NO           CURRENT        447336270 2012/02/22 20:42:28
     2          1       7750   52428800          1 YES          ACTIVE         447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7753   52428800          1 YES          INACTIVE       447336484 2012/02/22 20:47:35
     2          1       7754   52428800          1 NO           CURRENT        447336486 2012/02/22 20:47:39
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> 

最后,从新备库[原RAC主库]的alert日志中可以看到:

[root@oracle-rac1 ~]# tail -f /u01/app/oracle/admin/glndb/bdump/alert_glndb1.log 
 All grantable enqueues granted
Wed Feb 22 17:43:15 CST 2012
 LMS 1: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 LMS 0: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Wed Feb 22 20:38:32 CST 2012
Managed Standby Recovery starting Real Time Apply
Wed Feb 22 20:40:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:55 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'

同时,在新备库中可以看到下述信息

SQL> select thread#,sequence#,name,first_time,next_time,applied from v$archived_log where thread#=1 order by 1;
   THREAD#  SEQUENCE# NAME                                               FIRST_TIM NEXT_TIME APP
---------- ---------- -------------------------------------------------- --------- --------- ---
         1       7751 /home/oracle/arch1/ARC_1_0000007751_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7752 /home/oracle/arch1/ARC_1_0000007752_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7753 /home/oracle/arch1/ARC_1_0000007753_726057844.arc  22-FEB-12 22-FEB-12 YES

从上,可以确定我们的新备库同新主库已经同步!至此,我们的switchover角色切换成功

评论 (2)

  • sam wang| 2012年3月12日

    根据客户需求,我们的环境需要在一个RAC节点(比如:oracle-rac1)上配置Datagurad。

    我的问题是:

    如果物理备库和RAC节点1(oracle-rac1)是同一台机器,并且备库采用local disk方式存储,哪些步骤需要变化?如何变化?

    可否就我的问题按照你的Oracle 10g RAC 配置物理dataguard系列1-4分别指出有哪些不同,能给出详细的步骤就再好不过了!

    首先在此谢过!

  • orababy| 2012年12月26日

    按照你的配置我监听都起来了,相互也能访问
    为什么不能传输日志呢
    而且我关闭日志应用open数据库的时候报错
    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-16004: backup database requires recovery
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: ‘+DATADG/standby/datafile/system.257.803059147’

  • orababy进行回复 取消回复

    邮箱地址不会被公开。 必填项已用*标注