DataGuard(一主一备一级联之switchover)


DG(一主一备一级联之swithover)

         先说明一下dataguard一主一备一级联,意思是主库将日志传输给备库,然后备库在将日志传输给级联库,主库和级联库其实没有任何关系。另外,关于数据同步问题,后面也做了验证,主库上的操作一般情况下是可以实时同步到备库的,但是级联库必须等备库归档时,才能同步。如果主库切换日志,那么这时级联库也能及时同步。

 

现数据库环境如下

节点

网络ip地址

数据库名

unique name

数据库实例名

数据文件位置

zyx.test.com(主库)

192.168.11.111

orcl

orcl

test

/u01/app/oracle/oradata/orcl/

orcl.test.com(备库)

192.168.11.22

orcl

orclps

orclps

/u01/app/oracle/oradata/orcl/

dg2.orcl.com(级联库)

192.168.11.23

orcl

orclstd

orclstd

/u01/app/oracle/oradata/orcl/

 

主库切换为备库,备库切换为主库,级联库不变

1.1 主库参数添加

sys@ORCL>show parameter fal_server

sys@ORCL>show parameter fal_client

sys@ORCL>show parameter standby_file

sys@ORCL>alter system set standby_file_management=auto;

sys@ORCL>alter system set fal_client=orcl;

sys@ORCL>alter system set fal_server='orclps';

 

1.2 备库参数添加

sys@ORCL>show parameter log_archive_dest_2

sys@ORCL>show parameter log_archive_dest_3

NAME                                 TYPE        VALUE

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

log_archive_dest_3                   string      service=orclstd sync affirm ne

                                                 t_timeout=10 valid_for=(standb

                                                 y_logfile,standby_role) db_uni

                                                 que_name=orclstd

 

sys@ORCL>alter system set log_archive_dest_2='service=orcl sync affirm net_timeout=10 valid_for=(online_logfile,primary_role) db_unique_name=orcl';

 

1.3 switover之前检测环境

----主库上确认日志传输完整(no gap)

sys@ORCL>select status,gap_status from v$archive_dest_status where dest_id in (2,3);

STATUS    GAP_STATUS

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

ERROR     NO GAP

INACTIVE

------select db_unique_name,type,database_mode,synchronization_status,status,gap_status from v$archive_dest_status where dest_id in (2,3);

 

----备库上确定有两个lag

sys@ORCL>select * from v$dataguard_stats;

 

NAME   VALUE         UNIT     TIME_COMPUTED      DATUM_TIME

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

transport lag                    +00 00:00:00                                                     day(2) to second(0) interval   04/25/2016 19:31:55            04/25/2016 19:31:55

apply lag                        +00 00:00:00                                                     day(2) to second(0) interval   04/25/2016 19:31:55            04/25/2016 19:31:55

apply finish time                +00 00:00:00.000                                                 day(2) to second(3) interval   04/25/2016 19:31:55

estimated startup time           12                                                               second                         04/25/2016 19:31:55

 

----主库情况

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PRIMARY          TO STANDBY           orcl

 

----备库情况

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY  NOT ALLOWED          orclps                         orcl

 

----级联库情况

SQL> select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY   NOT ALLOWED          orclstd                        orcl

 

1.4 主库切换为备库

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PRIMARY          TO STANDBY           orcl

 

----主库转为物理备库

sys@ORCL>alter database commit to switchover to physical standby with session shutdown;

ERROR:

ORA-01034: ORACLE not available

Process ID: 2923

Session ID: 144 Serial number: 213

Database altered.

sys@ORCL>startup

 

1.5 备库转为主库

----此时备库情况

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY   TO PRIMARY           orclps                         orcl

 

----此时级联库情况

SQL> select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY    TO PRIMARY           orclstd                        orcl

 

----备库转为主库

sys@ORCL>alter database commit to switchover to primary with session shutdown;

sys@ORCL>alter database open;

 

----此时备库情况

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PRIMARY          RESOLVABLE GAP       orclps                         orcl

----此时主库情况

sys@ORCL>select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY  RECOVERY NEEDED      orcl                           orclps

----此时级联库情况

SQL> select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE  SWITCHOVER_STATUS  DB_UNIQUE_NAME  PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY   TO PRIMARY           orclstd                        orcl

 

1.6 现备库(原主库)应用日志

sys@ORCL>alter database recover managed standby database using current logfile disconnect;

alter database recover managed standby database using current logfile disconnect

*

ERROR at line 1:

ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

 

------没有添加备用日志了,下面添加

sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01.log' size 50m;

sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl02.log' size 50m;

sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl03.log' size 50m;

sys@ORCL>alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl04.log' size 50m;

 

----应用日志

sys@ORCL>alter database recover managed standby database using current logfile disconnect;

 

----查看同步情况

sys@ORCL>select sequence#, applied from v$archived_log;

 

1.7 级联库处理

------现在备库参数设置

sys@ORCL>alter system set log_archive_config='dg_config=(orcl,orclps,orclstd)';

sys@ORCL>alter system set log_archive_dest_3='service=orclstd sync affirm net_timeout=10 valid_for=(standby_logfile,standby_role) db_unique_name=orclstd';

 

------现备库TNS追加

[oracle@orcl ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCLSTD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.23)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstd)

    )

  )

 

-----级联库现在状态,TNS追加及参数设置

SQL> select database_role,switchover_status,db_unique_name,primary_db_unique_name from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME                 PRIMARY_DB_UNIQUE_NAME

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

PHYSICAL STANDBY TO PRIMARY           orclstd                        orcl

 

SQL> select sequence#, applied from v$archived_log;

 SEQUENCE# APPLIED

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

        13 YES

        14 YES

6 rows selected.

 

[oracle@dg2 ~]$ vim /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.111)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl.test.com)

    )

  )

 

SQL> alter system set fal_server=orclps,orcl;

SQL> alter database recover managed standby database cancel;

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

SQL> select sequence#, applied from v$archived_log;

 SEQUENCE# APPLIED

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

        18 YES

        19 YES

11 rows selected.

 

1.8 数据同步测试

----新主库上删除shall表

sys@ORCL>drop table shall purge;

Table dropped.

 

----此时新备库shall表已经无法查询

sys@ORCL>select count(*) from shall;

select count(*) from shall

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

----此时级联库还能查询数据

 

SQL> select count(*) from shall;

  COUNT(*)

----------

    100000

 

----新主库切换日志

sys@ORCL>alter system switch logfile;

System altered.

 

----级联库无法查询数据

SQL> select count(*) from shall;

select count(*) from shall

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

1.9 新主库online redo位置调整

----前面备库使用duplicate创建时,没有使用convert路径转换,因此,有的文件路径格式看起来,并不是特别好管理,下面调整一下redo位置

set linesize 200

set pagesize 999

col member for a80

select group#,type,member from v$logfile;

sys@ORCL>select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER

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

3 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_3_ckvx3jm7_.log

2 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_2_ckvx3h8m_.log

1 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_1_ckvx3fv3_.log

4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log

5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log

6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log

7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log

7 rows selected.

 

sys@ORCL> alter database add logfile group 8 ('/u01/app/oracle/oradata/orcl/redo08_1.log') size 50m;

sys@ORCL> alter database add logfile group 9 ('/u01/app/oracle/oradata/orcl/redo09_1.log') size 50m;

 

sys@ORCL>select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER

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

3 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_3_ckvx3jm7_.log

2 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_2_ckvx3h8m_.log

1 ONLINE  /u01/app/oracle/fast_recovery_area/ORCLPS/onlinelog/o1_mf_1_ckvx3fv3_.log

4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log

5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log

6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log

7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log

8 ONLINE  /u01/app/oracle/oradata/orcl/redo08_1.log

9 ONLINE  /u01/app/oracle/oradata/orcl/redo09_1.log

9 rows selected.

sys@ORCL>select group#,sequence#,status,archived from v$log;

    GROUP#  SEQUENCE# STATUS           ARC

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

         1         21 CURRENT          NO

         2         19 INACTIVE         YES

         3         20 INACTIVE         YES

         8          0 UNUSED           YES

         9          0 UNUSED           YES

 

sys@ORCL>alter system switch logfile;

sys@ORCL>alter system switch logfile;

sys@ORCL>select group#,sequence#,status,archived from v$log;

------如果脏数据还未写磁盘,手动再触发一次检查点

SQL> alter system checkpoint;         ----脏数据写盘

 

----删除日志组1、2、3

SQL> alter system checkpoint;         ----脏数据写盘

SQL> select group#,sequence#,status,archived from v$log;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

 

sys@ORCL>select group#,sequence#,status,archived from v$log;

    GROUP#  SEQUENCE# STATUS           ARC

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

         8         22 INACTIVE         YES

         9         23 CURRENT          NO

 

----创建日志组1、2、3

SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01_1.log') size 50m;

SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/orcl/redo02_1.log') size 50m;

SQL> alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/redo03_1.log') size 50m;

 

----切换日志组

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

SQL> alter system checkpoint;

 

----删除中间过渡用的日志组8、9

SQL> select group#,sequence#,status,archived from v$log;

alter database drop logfile group 8;

alter database drop logfile group 9;

 

sys@ORCL> select group#,sequence#,status,archived,bytes/1024/1024 "size(M)" from v$log;

    GROUP#  SEQUENCE# STATUS           ARC    size(M)

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

         1         24 INACTIVE         YES         50

         2         25 INACTIVE         YES         50

         3         26 CURRENT          NO          50

SQL> select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER

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

         1 ONLINE  /u01/app/oracle/oradata/orcl/redo01_1.log

         2 ONLINE  /u01/app/oracle/oradata/orcl/redo02_1.log

         3 ONLINE  /u01/app/oracle/oradata/orcl/redo03_1.log

         4 STANDBY /u01/app/oracle/oradata/orcl/srl01.log

         5 STANDBY /u01/app/oracle/oradata/orcl/srl02.log

         6 STANDBY /u01/app/oracle/oradata/orcl/srl03.log

         7 STANDBY /u01/app/oracle/oradata/orcl/srl04.log

7 rows selected.

 

----手动清理日志(在fast_recovery_area的视乎不用手动清理,oracle自动删除了)

[oracle@orcl onlinelog]$ cd /u01/app/oracle/oradata/orcl/

[oracle@orcl orcl]$ rm redo08_1.log

[oracle@orcl orcl]$ rm redo09_1.log

 

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2119320/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值