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/