dataguard 物理备库转换成逻辑备库

转换准备：已经拥有搭建好的dataguard。

我的环境：VBox、oracle11R2、oracle linux、主库：dg1、备库：dg2

主库VBox镜像文件：http://yunpan.cn/QCBPHgnWqQDVU   访问密码 0582

备库VBox镜像文件：http://yunpan.cn/QCBPSMwhp4cev   访问密码 5f5c

转换步骤：

1、primary数据库初始化参数(在搭建完物理备库之后)

DB_NAME=dg1
DB_UNIQUE_NAME=dg1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
CONTROL_FILES='/u01/app/oradata/dg1/control01.ctl', '/u01/app/oracle/flash_recovery_area/control02.ctl'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1'
LOG_ARCHIVE_DEST_2='SERVICE=dg2 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=arch_%r_%t_%s.arc

2、关闭physical standby database的日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3、在primary数据库添加参数

alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg1' scope=both;
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dg1' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

4、开启primary数据库的supplemental log，并建立Dictionary

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
EXECUTE DBMS_LOGSTDBY.BUILD;

5、将physical standby database转换成logical standby database

ALTER DATABASE RECOVER TO LOGICAL STANDBY dg2;
SHUTDOWN immediate;
STARTUP MOUNT;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg2' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=dg1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg1'	scope=both;	
alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dg2' scope=both;	
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;

6、使用resetlogs打开数据库

ALTER DATABASE OPEN RESETLOGS;

7、应用redo file

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

8、重新配置Broker

dgmgrl sys/oracle@dg1
remove database dg1;
remove CONFIGURATION;	
create configuration CONTACT as primary database is dg1 connect identifier is dg1;	
add database dg2 as connect identifier is dg2 maintained as logical;	
enable configuration;
show configuration;