Oracle Data Guard physical 转换成logical备库

以下是通过测试环境验证后的结果：
primary :
   db_name=dg  db_unique_name=pdb  
   log_archive_dest_1='location=d:\oracle\archive'

   log_archive_dest_2='service=sdb lgwr async affirm valid_for=
(online_logfiles,primary_role)    db_unique_name=sdb'

log_archive_dest_3='LOCATION=D:\oracle\archive2 VALID_FOR= (STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=pdb'

standby:  
   db_name=dg  db_unique_name=sdb
  log_archive_dest_1='location=d:\oracle\archive'

   log_archive_dest_2='service=pdb lgwr async affirm valid_for=
(online_logfiles,primary_role)    db_unique_name=pdb'

log_archive_dest_3='LOCATION=D:\oracle\archive2 VALID_FOR= (STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=sdb'


二、转换物理备库为逻辑备库
******************************************************************************************
******************************************************************************************
首先停止物理备库的redo apply
alter database recover managed standby database cancel;             --备库执行
alter system set log_archive_dest_3='LOCATION=D:\oracle\archive2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pdb' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE;

然后在主库为logminer生成数据字典信息到日志中，这个步骤需要使用闪回查询技术对数据字典信息执行一致性读，故undo_retention需要设置时间长一些 ，不然可能碰到ora-01555。
主、备库设置初始化参数：
SQL>alter system set UNDO_RETENTION=3600;

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

归档主库当前日志，确保包含数据字典信息的归档传递到备库
SQL> alter system archive log current;
********************************************************************************************
********************************************************************************************


执行转换语句，逻辑备库的db_name不需要和主库保持一致，所以这里可以根据需要选择备库的名字。如果使用的是spfile，还会自动修改db_name参数。
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY dg;    --备库执行
修改初始化参数：
alter system set log_archive_dest_3='LOCATION=D:\oracle\archive2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=sdb' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE;

重启逻辑备库
shutdown immediate;
startup mount;
alter database open resetlogs;

启动sql apply
SQL>  ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


跳过每个对象的应用
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'herbert',object_name => 'testtab', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL',schema_name => 'herbert', object_name => 'testtab', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'herbert',object_name => 'TRANSFERMAP_VALUES', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL',schema_name => 'herbert', object_name => 'TRANSFERMAP_VALUES', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'herbert',object_name => 'TRANSFER_ERRORLOG', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL',schema_name => 'herbert', object_name => 'TRANSFER_ERRORLOG', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'herbert',object_name => 'TRANSFER_RECV', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL',schema_name => 'herbert', object_name => 'TRANSFER_RECV', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name => 'herbert',object_name => 'TRANSFER_SEND', proc_name => null);
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'SCHEMA_DDL',schema_name => 'herbert', object_name => 'TRANSFER_SEND', proc_name => null);