预备:
确认对象和语句能被standby
支撑
确保primary库中各表的行可被唯一标识
环境:
操作系统:RED HAT LINUX ENTERPRISE 5
ORACLE: 11.2.0.1.0
PRIMARY:
IP: 192.168.1.11
SID: test
DB_UNIQUE_NAME:test
安装路径:/oracle/oracle/product/11.2.0/dbhome_1
当地归档路径:/oracle/oradata/test/archive
PHYSICS STANDBY:
IP: 192.168.1.12
SID: dgtest
DB_UNIQUE_NAME:dgtest
安装路径:/oracle/oracle/product/11.2.0/dbhome_1
当地归档路径:/oracle/oradata/dgtest/dgtest/archive
LOGICAL STANDBY:
IP: 192.168.1.15
SID: logicdg
DB_UNIQUE_NAME: logicdg
安装路径:/oracle/oracle/product/11.2.0/dbhome_1
当地归档路径:/oracle/oradata/logicdg/local-archive
本例测试采纳物理standby转逻辑standby的
模式在一个已经存在的dataguard环境中新增一台逻辑standby.
原dataguard环境可参考:
http://xin23.blog.51cto.com/1827266/504066
缔造新物理standby的过程亦可参照此文 。此处不再赘述 。
则当前环境为:
primary:192.168.1.11
physics: 192.168.1.12
logical: 192.168.1.15 (当前为物理standby.待转换)
1.
批改primary初始化参数文件(仅列出
批改
部分)
*.log_archive_dest_state_2=defer
*.log_archive_dest_state_3=defer
*.log_archive_config='dg_config=(test,dgtest,logicdg)'
*.log_archive_dest_2='service=test12 arch valid_for=(online_logfiles,primary_role) db_unique_name=dgtest'
*.log_archive_dest_3='service=test15 arch valid_for=(online_logfiles,primary_role) db_unique_name=logicdg'
*.fal_server=test11
*.fal_client=test12
*.standby_file_management=auto
*.db_file_name_convert='/oracle/oradata/test','/oracle/oradata/dgtest/dgtest','/oracle/oradata/test','/
oracle/oradata/logicdg'
*.log_file_name_convert='/oracle/oradata/test','/oracle/oradata/dgtest/dgtest','/oracle/oradata/test','/
oracle/oradata/logicdg'
2.查看两台物理standby同步状态
physics> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
66 YES
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
7 rows selected.
-----------------------------------------------------------------------------
logical> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
67 YES
68 YES
69 YES
70 YES
71 YES
72 YES
6 rows selected.
3.
取缔待转换物理standby的redo
利用
logical> alter database recover managed standby database cancel;
Database altered.
4.primary生成数据字典
primary> execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
5.将物理standby转换为逻辑standby
logical> alter database recover to logical standby logicdg;
Database altered.
logical> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
logical> startup mount
ORACLE instance started.
Total System Global Area 2488635392 bytes
Fixed Size 2215904 bytes
Variable Size 1392508960 bytes
Database Buffers 1090519040 bytes
Redo Buffers 3391488 bytes
Database mounted.
6.调整standby初始化参数
logical> alter system set log_archive_dest_1='location=/oracle/oradata/logicdg/local-archive valid_for=(online_logfiles,all_roles)
2 db_unique_name=logicdg';
System altered.
logical> alter system set log_archive_dest_5='location=/oracle/oradata/logicdg/archive valid_for=(standby_logfiles,standby_role)
2 db_unique_name=logicdg';
System altered.
7.resetlogs
模式
打开数据库
logical> alter database open resetlogs;
Database altered.
8.
利用redo
缔造standby redologs
logical> alter database add standby logfile group 11 '/oracle/oradata/logicdg/standbyredo11.log' size 100m;
Database altered.
logical> alter database add standby logfile group 12 '/oracle/oradata/logicdg/standbyredo12.log' size 100m;
Database altered.
logical> alter database add standby logfile group 13 '/oracle/oradata/logicdg/standbyredo13.log' size 100m;
Database altered.
启动redo实时
利用
logical> alter database start logical standby apply immediate;
Database altered.