ITPub博客

首页 > 数据库 > Oracle > 一步一步搭建11gR2 rac+dg之DG SWITCHOVER功能(九)

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

Oracle 作者:q418441117 时间:2016-11-20 19:03:20 0 删除 编辑

本文文档结构图:

image

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

1.1   测试DATAGUARDSWITCHOVER功能

1.1.1    RAC主库、ActiveDataguard角色切换

rac主库切换为物理备库,物理备库切换为rac主库。

1.1.1.1   ①主库修改相关参数fal_clientfal_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>

 

clip_image001

clip_image002

 

1.1.1.3   ③主库修改相关参数standby_file_managementdb_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';

clip_image003

 

 

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>

 

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26655292/viewspace-2128762/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-09-11

  • 博文量
    202
  • 访问量
    380442