一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九)

本文文档结构图：

本篇发布的有些晚，之前的8篇文章见 http://blog.itpub.net/26736162/viewspace-1290405/

1.1   测试DATAGUARDSWITCHOVER功能

1.1.1    RAC主库、ActiveDataguard角色切换

即rac主库切换为物理备库，物理备库切换为rac主库。

1.1.1.1   ①主库修改相关参数fal_client、fal_server：

fal_server 指定为主库即primary的网络服务名
fal_client 指定为备库即standby的网络服务名
通常来说，主库和备库是反过来的，便于主备库的切换。

 

首先需要确保主库采用spfile启动，然后在其中任何一个节点执行以下命令:

alter system set fal_client='racdb1' sid='racdb1';

alter system set fal_client='racdb2' sid='racdb2';

alter system set fal_server='phydb' sid='*';

 

 

SQL> show parameter spfile

 

NAME                  TYPE    VALUE

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

spfile                    string  +DATA/racdb/spfileracdb.ora

SQL> show parameter fal

 

NAME                  TYPE    VALUE

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

fal_client                string

fal_server                string

SQL> alter system set fal_client='racdb1' sid='racdb1';

 

System altered.

 

SQL> alter system set fal_client='racdb2' sid='racdb2';

 

System altered.

 

SQL> alter system set fal_server='phydb' sid='*';

 

System altered.

 

 

---rac1查看结果：

SQL> show parameter fal

 

NAME                  TYPE    VALUE

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

fal_client                string  racdb1

fal_server                string  phydb

SQL>

 

 

---rac2查看结果：

SQL> show parameter fal

 

NAME                  TYPE    VALUE

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

fal_client                string  racdb2

fal_server                string  phydb

SQL>

 

 

1.1.1.2   ②主库创建standbylogfile：

创建standby_logfile 之前查看可以得到已经存在4组日志，且无standby_logfile，每个大小为50M

 

SQL> col member for a60

SQL> select * from v$logfile;

 

    GROUP# STATUS         TYPE           MEMBER                                                       IS_REC

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

         2                ONLINE         +DATA/racdb/onlinelog/group_2.262.859849495                  NO

         2                ONLINE         +FRA/racdb/onlinelog/group_2.258.859849495                   YES

         1                ONLINE         +DATA/racdb/onlinelog/group_1.261.859849493                  NO

         1                ONLINE         +FRA/racdb/onlinelog/group_1.257.859849493                   YES

         3                ONLINE         +DATA/racdb/onlinelog/group_3.266.859850179                  NO

         3                ONLINE         +FRA/racdb/onlinelog/group_3.259.859850181                   YES

         4                ONLINE         +DATA/racdb/onlinelog/group_4.267.859850183                  NO

         4                ONLINE         +FRA/racdb/onlinelog/group_4.260.859850187                   YES

 

已选择8行。

 

SQL> select * from v$standby_log;

 

未选定行

 

SQL> select group#, bytes/1024/1024  from v$log;

 

    GROUP# BYTES/1024/1024

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

         1              50

         2              50

         3              50

         4              50

 

SQL>

 

开始创建 standby logfile：

alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

 

 

SQL> alter database add standby logfile thread 1 group 5 size 50M ,group 6 size 50M ,group 7 size 50M ;

 

数据库已更改。

 

SQL> alter database add standby logfile thread 2 group 8 size 50M ,group 9 size 50M ,group 10 size 50M ;

 

数据库已更改。

 

SQL> select * from v$logfile;

 

    GROUP# STATUS         TYPE           MEMBER                                                       IS_REC

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

         2                ONLINE         +DATA/racdb/onlinelog/group_2.262.859849495                  NO

         2                ONLINE         +FRA/racdb/onlinelog/group_2.258.859849495                   YES

         1                ONLINE         +DATA/racdb/onlinelog/group_1.261.859849493                  NO

         1                ONLINE         +FRA/racdb/onlinelog/group_1.257.859849493                   YES

         3                ONLINE         +DATA/racdb/onlinelog/group_3.266.859850179                  NO

         3                ONLINE         +FRA/racdb/onlinelog/group_3.259.859850181                   YES

         4                ONLINE         +DATA/racdb/onlinelog/group_4.267.859850183                  NO

         4                ONLINE         +FRA/racdb/onlinelog/group_4.260.859850187                   YES

         5                STANDBY        +DATA/racdb/onlinelog/group_5.269.863272613                  NO

         5                STANDBY        +FRA/racdb/onlinelog/group_5.368.863272615                   YES

         6                STANDBY        +DATA/racdb/onlinelog/group_6.270.863272617                  NO

         6                STANDBY        +FRA/racdb/onlinelog/group_6.369.863272619                   YES

         7                STANDBY        +DATA/racdb/onlinelog/group_7.271.863272621                  NO

         7                STANDBY        +FRA/racdb/onlinelog/group_7.372.863272621                   YES

         8                STANDBY        +DATA/racdb/onlinelog/group_8.272.863272637                  NO

         8                STANDBY        +FRA/racdb/onlinelog/group_8.375.863272637                   YES

         9                STANDBY        +DATA/racdb/onlinelog/group_9.273.863272639                  NO

         9                STANDBY        +FRA/racdb/onlinelog/group_9.379.863272641                   YES

        10                STANDBY        +DATA/racdb/onlinelog/group_10.274.863272643                 NO

        10                STANDBY        +FRA/racdb/onlinelog/group_10.381.863272643                  YES

 

已选择20行。

 

SQL> select * from v$standby_log;

 

    GROUP# DBID                                                                                THREAD#  SEQUENCE#      BYTES  BLOCKSIZE       USED ARCHIV STATUS               FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME      LAST_CHANGE# LAST_TIME

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

         5 UNASSIGNED                                                                                1      0   52428800            512          0 YES    UNASSIGNED

         6 UNASSIGNED                                                                                1      0   52428800            512          0 YES    UNASSIGNED

         7 UNASSIGNED                                                                                1      0   52428800            512          0 YES    UNASSIGNED

         8 UNASSIGNED                                                                                2      0   52428800            512          0 YES    UNASSIGNED

         9 UNASSIGNED                                                                                2      0   52428800            512          0 YES    UNASSIGNED

        10 UNASSIGNED                                                                                2      0   52428800            512          0 YES    UNASSIGNED

 

已选择6行。

 

SQL>

 

 

1.1.1.3   ③主库修改相关参数standby_file_management、db_file_name_convert 、log_filename_convert

修改前：

SQL> show parameter standby_file

 

NAME                                 TYPE                   VALUE

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

standby_file_management              string                 MANUAL

SQL> show parameter db_file_name

 

NAME                                 TYPE                   VALUE

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

db_file_name_convert                 string

SQL> show parameter log_file_name

 

NAME                                 TYPE                   VALUE

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

log_file_name_convert                string

SQL>

 

修改：

 

SQL> alter system set db_file_name_convert='+DATA/phydb/','+DATA/racdb/' sid='*' scope=spfile;

 

系统已更改。

 

SQL> alter system set  log_file_name_convert='+DATA/phydb/','+DATA/racdb/' sid='*' scope=spfile;

 

系统已更改。

 

 

修改后查看：

SELECT * FROM v$spparameter a WHERE a.NAME like '%file_name_convert';

 

 

1.1.1.4   ④停止RAC节点2：

主库状态：

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2809285 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 857466254 RACDB 2809285 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 

SQL>

 

备库状态：

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2813423 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

SQL>

 

停掉rac2主库：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

 

再次查看rac主库：

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2814040 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 

SQL>

 

1.1.1.5   ⑤RAC 节点 1 切换原 RAC 主库到备库：

rac主库执行：

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  racdb1

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2814245 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 

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

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2814358 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ WRITE       RECOVERY NEEDED

 

SQL>

 

 

 

----原物理备库查看：

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2834309 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY SESSIONS ACTIVE

 

SQL>

 

 

 

1.1.1.6   ⑥   切换原物理备库到主库角色：

原物理备库下执行，注意执行该步骤的时候不能有其它的回话连接到dg库，否则报错：

SQL> alter database commit to switchover to primary;

alter database commit to switchover to primary

*

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

 

 

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  phydb

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2834309 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY    TO PRIMARY

 

SQL> alter database commit to switchover to primary;

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB        0 MAXIMUM PERFORMANCE  PRIMARY        YES MOUNTED       NOT ALLOWED

 

SQL> alter database open;

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2834936 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        FAILED DESTINATION

 

SQL>

 

1.1.1.7   ⑦   将原 RAC 主库 2 个实例都启动到 MOUNT 状态：

[grid@rac1 ~]$ crs_stat -t

Name           Type           Target    State     Host       

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

ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1       

ora.FRA.dg     ora....up.type ONLINE    ONLINE    rac1       

ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1       

ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1       

ora.OCR.dg     ora....up.type ONLINE    ONLINE    rac1       

ora.asm        ora.asm.type   ONLINE    ONLINE    rac1       

ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac2       

ora.gsd        ora.gsd.type   ONLINE    OFFLINE              

ora....network ora....rk.type ONLINE    ONLINE    rac1       

ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac2       

ora.ons        ora.ons.type   ONLINE    ONLINE    rac1       

ora....SM1.asm application    ONLINE    ONLINE    rac1       

ora....C1.lsnr application    ONLINE    ONLINE    rac1       

ora.rac1.gsd   application    ONLINE    OFFLINE              

ora.rac1.ons   application    ONLINE    ONLINE    rac1       

ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1       

ora....SM2.asm application    ONLINE    ONLINE    rac2       

ora....C2.lsnr application    ONLINE    ONLINE    rac2       

ora.rac2.gsd   application    ONLINE    OFFLINE              

ora.rac2.ons   application    ONLINE    ONLINE    rac2       

ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2       

ora.racdb.db   ora....se.type OFFLINE   OFFLINE              

ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1

[grid@rac1 ~]$ srvctl status database -d racdb

Instance racdb1 is not running on node rac1

Instance racdb2 is not running on node rac2

[grid@rac1 ~]$ srvctl start database -d racdb -o mount

[grid@rac1 ~]$ crs_stat -t

Name           Type           Target    State     Host       

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

ora.DATA.dg    ora....up.type ONLINE    ONLINE    rac1       

ora.FRA.dg     ora....up.type ONLINE    ONLINE    rac1       

ora....ER.lsnr ora....er.type ONLINE    ONLINE    rac1       

ora....N1.lsnr ora....er.type ONLINE    ONLINE    rac1       

ora.OCR.dg     ora....up.type ONLINE    ONLINE    rac1       

ora.asm        ora.asm.type   ONLINE    ONLINE    rac1       

ora.cvu        ora.cvu.type   ONLINE    ONLINE    rac2       

ora.gsd        ora.gsd.type   ONLINE    OFFLINE              

ora....network ora....rk.type ONLINE    ONLINE    rac1       

ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    rac2       

ora.ons        ora.ons.type   ONLINE    ONLINE    rac1       

ora....SM1.asm application    ONLINE    ONLINE    rac1       

ora....C1.lsnr application    ONLINE    ONLINE    rac1       

ora.rac1.gsd   application    ONLINE    OFFLINE              

ora.rac1.ons   application    ONLINE    ONLINE    rac1       

ora.rac1.vip   ora....t1.type ONLINE    ONLINE    rac1       

ora....SM2.asm application    ONLINE    ONLINE    rac2       

ora....C2.lsnr application    ONLINE    ONLINE    rac2       

ora.rac2.gsd   application    ONLINE    OFFLINE              

ora.rac2.ons   application    ONLINE    ONLINE    rac2       

ora.rac2.vip   ora....t1.type ONLINE    ONLINE    rac2       

ora.racdb.db   ora....se.type ONLINE    ONLINE    rac1       

ora.scan1.vip  ora....ip.type ONLINE    ONLINE    rac1       

[grid@rac1 ~]$

 

 

 

-------查看状态

SQL> set line 9999

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2834308 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED      RECOVERY NEEDED

 857466254 RACDB 2834308 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED      RECOVERY NEEDED

 

SQL>

 

1.1.1.8   ⑧   原 RAC 主库启动 redo apply：

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

 

Database altered.

 

1.1.1.9   ⑨   原 RAC 主库停止 redo apply，并将 RAC 主库所有节点以 READ ONLY 打开：

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED      NOT ALLOWED

 857466254 RACDB 2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED      NOT ALLOWED

 

SQL> alter database open;

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED      NOT ALLOWED

 857466254 RACDB 2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY    NOT ALLOWED

 

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

 

      DBID    INST_ID NAME    CURRENT_SCN PROTECTION_MODE DATABASE_ROLE    FOR OPEN_MODE    SWITCHOVER_STATUS

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

 857466254       2 RACDB      2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY    NOT ALLOWED

 857466254       1 RACDB      2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED           NOT ALLOWED

 

SQL>

 

---- 启动第二个节点后查看：

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

 

      DBID    INST_ID NAME    CURRENT_SCN PROTECTION_MODE DATABASE_ROLE    FOR OPEN_MODE    SWITCHOVER_STATUS

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

 857466254       2 RACDB      2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY    NOT ALLOWED

 857466254       1 RACDB      2838735 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY    NOT ALLOWED

 

SQL>

 

 

 

 

----dg库查看：

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2839188 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 

SQL>

 

 

1.1.1.10  ⑩测试切换后的效果

dg主机建表：

SQL> create table test_dg(id number);

 

Table created.

 

rac主机库查看：

SQL> desc test_dg

ERROR:

ORA-04043: object test_dg does not exist

 

 

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

 

Database altered.

 

SQL> desc test_dg

 Name                       Null?    Type

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

 ID                          NUMBER

 

dg主机插入数据：

 

SQL> insert into test_dg values(100);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL>

 

rac库查看：

SQL> select * from test_dg;

 

ID

----------

       100

 

SQL>

 

---dg库删除

SQL> drop table test_dg purge;

 

Table dropped.

 

SQL>

 

 

--新备库查看

SQL> select * from test_dg;

select * from test_dg

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

 

SQL>

 

 

至此，说明切换成功完成。

1.1.2    单实例主库，RAC备库角色切换

即，将新 RAC 备库切换为主库，新单实例主库切换为备库：

1.1.2.1   ① 新主库（单实例库）状态查看：

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  phydb

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2840504 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE        TO STANDBY

 

SQL>

 

1.1.2.2   ②   新备库（RAC 库）状态查看：

 

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

 

   INST_ID   DBID NAME  CURRENT_SCN PROTECTION_MODE   DATABASE_ROLE    FOR OPEN_MODE     SWITCHOVER_STATUS

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

 1  857466254 RACDB      2840572 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 2  857466254 RACDB      2840572 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

SQL>

 

 

1.1.2.3   ③ 新备库（RAC 库）停止节点 2：

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  racdb2

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

 

      DBID    INST_ID NAME    CURRENT_SCN PROTECTION_MODE DATABASE_ROLE    FOR OPEN_MODE    SWITCHOVER_STATUS

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

 857466254       2 RACDB      2840675 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 857466254       1 RACDB      2840675 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

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

 

   INST_ID   DBID NAME  CURRENT_SCN PROTECTION_MODE   DATABASE_ROLE    FOR OPEN_MODE     SWITCHOVER_STATUS

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

 1  857466254 RACDB      2861126 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY TO PRIMARY

 

SQL>

 

1.1.2.4   ④   新主库（单实例库）切换到备库：

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  phydb

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2841031 MAXIMUM PERFORMANCE  PRIMARY         YES READ WRITE        TO STANDBY

 

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

 

Database altered.

 

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2841153 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ WRITE       RECOVERY NEEDED

 

SQL>

 

 

1.1.2.5   ⑤新备库（RAC 库）节点 1 切换到主库：

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

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

 

   INST_ID   DBID NAME  CURRENT_SCN PROTECTION_MODE   DATABASE_ROLE    FOR OPEN_MODE     SWITCHOVER_STATUS

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

 1  857466254 RACDB      2861126 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY     TO PRIMARY

 

 

--- 这里最好先把库修改为mount状态再切换，不然事务很大的话会非常的慢

SQL>  alter database commit to switchover to primary;

 

Database altered.

 

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

 

   INST_ID   DBID NAME  CURRENT_SCN PROTECTION_MODE   DATABASE_ROLE    FOR OPEN_MODE     SWITCHOVER_STATUS

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

 1  857466254 RACDB        0 MAXIMUM PERFORMANCE  PRIMARY       YES MOUNTED          NOT ALLOWED

 

SQL>

 

1.1.2.6     ⑥ 新备库(RAC 库)全部启动所有节点：

节点一：

SQL> alter database open;

 

Database altered.

 

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  racdb1

SQL>

 

节点二：

SQL> startup

ORACLE instance started.

 

Total System Global Area 1336176640 bytes

Fixed Size           2228144 bytes

Variable Size        1107296336 bytes

Database Buffers   218103808 bytes

Redo Buffers         8548352 bytes

Database mounted.

Database opened.

SQL> show parameter instance_name

 

NAME                  TYPE    VALUE

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

instance_name                 string  racdb2

SQL>

 

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

 

      DBID    INST_ID NAME    CURRENT_SCN PROTECTION_MODE DATABASE_ROLE    FOR OPEN_MODE    SWITCHOVER_STATUS

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

 857466254       1 RACDB      2864440 MAXIMUM PERFORMANCE  PRIMARY   YES READ WRITE    RESOLVABLE GAP

 857466254       2 RACDB      2864440 MAXIMUM PERFORMANCE  PRIMARY   YES READ WRITE    RESOLVABLE GAP

 

SQL>

 

1.1.2.7   ⑦   新备库（单实例库）重新启动并开始 redo apply：

注意备库必须关闭然后重启：

[oracle@dg ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 10 17:18:12 2014

 

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

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  839282688 bytes

Fixed Size           2233000 bytes

Variable Size          574623064 bytes

Database Buffers   260046848 bytes

Redo Buffers         2379776 bytes

Database mounted.

Database opened.

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

 

Database altered.

 

SQL> host ps -ef|grep mrp

oracle   31367     1  0 17:19 ?        00:00:00 ora_mrp0_phydb

oracle   31375 31168  0 17:20 pts/3    00:00:00 /bin/bash -c ps -ef|grep mrp

oracle   31377 31375  0 17:20 pts/3    00:00:00 grep mrp

 

SQL>

 

至此，完成一次角色切换，即将新的备库（RAC 库）切换成主库，新的备库（单实例库）又重新切换为物理备库，回到最初的状态！！！

 

1.1.2.8   ⑧   测试切换后的效果

 

--rac主库

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

 

   INST_ID   DBID NAME  CURRENT_SCN PROTECTION_MODE   DATABASE_ROLE    FOR OPEN_MODE     SWITCHOVER_STATUS

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

 1  857466254 RACDB      2865147 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE       TO STANDBY

 2  857466254 RACDB      2865147 MAXIMUM PERFORMANCE  PRIMARY       YES READ WRITE       TO STANDBY

 

SQL>

 

--dg备库

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

 

      DBID NAME      CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE        SWITCHOVER_STATUS

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

 857466254 RACDB 2861129 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED

 

SQL>

 

 

 

-------rac主库建表

SQL> create table test_dg(id number);

 

Table created.

 

SQL> select * from test_dg;

 

no rows selected

 

 

----dg备库查看，由于是刚切换过来的原因，这里可能需要等待几分钟才可以查询到：

SQL>  select * from test_dg;

 

no rows selected

 

SQL>

 

 

---rac主库插入数据

SQL> insert into test_dg values(200);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from test_dg;

 

ID

----------

       200

 

 

--dg库查看：

SQL> select * from test_dg;

 

ID

----------

       200

 

SQL>

 

 

 

时间： 2024-09-15 16:30:19

一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九)的相关文章

一步一步搭建11gR2 rac+dg之结尾篇(十)

     一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + 物理 dg 系列blog已基本完成,前9篇文章见以下链接,本篇为终结篇:   一步一步搭建 oracle 11gR2 rac + dg 之前传(一) http://blog.itpub.net/26736162/viewspace-1290405/  一步一步搭建oracle 11gR2 rac+dg之环境准备(二)  http://blog.itpub.net/26736

一步一步搭建11gR2 rac+dg之安装rac出现问题解决(六)

一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg 之安装rac出现的问题 (六)   本章主要是搜集了一些安装rac的过程中出现的问题及解决办法,如果没有出现问题的话那么这一章可以不看的       目录结构: crs安装出现的问题 Oracle 11g RAC CRS-4535/ORA-15077 新安装了Oracle 11g rac之后,不知道是什么原因导致第二个节点上的crsd无法启动?其错误消息是CRS-4535: C

一步一步搭建11gR2 rac+dg之DG 机器配置(七)

DG 机器配置   本文文档结构: 这一步的DG机器配置就相当于安装单实例的DB+ASM 一样,这里我把之前的文档内容直接拷贝过来了,也不做过多的解释了   安装之前的配置工作   检查硬件 在正式安装开始前,请先检查你的软硬件条件是否满足安装需要. 硬件上可以使用命令查看内存情况和 CPU 特性: #more /proc/meminfo #more /proc/cpuinfo 其中内存的要求是不低于 1G 下面的不是必须的,可选使用 #df –k /dev/shm 检查共享内存 #df –k

【RAC安装】 AIX下安装Oracle 11gR2 RAC

[RAC安装] AIX下安装Oracle 11gR2 RAC   1.1  BLOG文档结构图       1.2  前言部分   1.2.1  导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 基于aix安装rac(重点) ② 静默安装rac软件 ③ dbca静默创建rac数据库     Tips:        ① 若文章代码格式有错乱,推荐使用QQ.搜狗或360浏览器,也可以下载pdf格式的文档来查看,pdf文档

一步一步搭建 oracle 11gR2 rac+dg之grid安装(四)

一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg 之grid安装 (四) 本章目录结构:   这一步也比较重要,主要是安装ASM,如果前一步的共享磁盘没有准备好的话,执行root脚本的时候可能会报错,不过不要紧的,,,一定可以解决的,,,,     本章目录结构   Grid安装过程 下载软件,上传软件,解压软件: [root@rac1 share]# ll total 3398288 -rwxrwxrwx 1 root ro

一步一步搭建oracle 11gR2 rac+dg之环境准备(二)

  一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg 之环境准备 (二) 本篇目录结构:   Linux 环境准备 安装linux的环境,我就不介绍了,这一部分如果不会的童鞋就去百度吧,一百度一大堆,如果还是不会的话就直接下载我已经安装好的系统吧,下载下来直接可用(http://yunpan.cn/cgkEsf8wpHC2G (提取码:90f5)),复制3份,直接命名为rac1.rac2和dg即可,如图:     前期环境准备

一步一步搭建oracle 11gR2 rac+dg之共享磁盘设置(三)

  一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg 之共享磁盘准备 (三) 注意:这一步是配置rac的过程中非常重要的一步,很多童鞋多次安装rac都不成功,主要原因就是失败在共享磁盘的配置上,包括小麦苗我自己,多次安装才懂的这个道理,所以,这一步大家一定要睁大眼睛多看多想,如有不懂的地方就直接联系小麦苗吧.   本部分目录截图: 配置共享存储 这个是重点,也是最容易出错的地方,我最初安装的时候就是在这里老报错,大家看仔细了哟

一步一步搭建oracle 11gR2 rac+dg之database安装(五)

一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg 之database安装 (五) 本章目录结构: 这一步主要可能安装的时候找不见磁盘组,这个也不要急,一步一步肯定可以解决的,,,,,Database安装与配置   安装数据库 日志:tail -f /u01/app/oraInventory/logs/installActions2014-06-05_01-30-25AM.log   解压文件: [oracle@localhos

一步一步搭建 oracle 11gR2 rac + dg 之前传 （一）

      一步一步在RHEL6.5+VMware Workstation 10上搭建 oracle 11gR2 rac + dg  之前传 (一)       2014年国庆放七天假,但对于我来说放不放假都一样,单身的我也不愿意多出去转转了,觉得没意思,看着人家一对一对的,我出去岂不是太煞风景,,,那做些什么好呢?那就搭建一套rac+dg玩玩呗,总不能荒废时光吧,,,好了,废话少说了,下边就进入正题了.       搭建过程比较长,我就多分几个章节来发布吧,这样显得比较条理化,该搭建过程比较适