The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.
Step 6 Convert to a Logical Standby Database
--转换物理standby到逻辑standby,db_name是要指定一个新的逻辑standby 的db_name
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;
For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.
Step 7 Create a New Password File for Logical Standby Database
--新建密码文件,这个在10g需要执行,11g原有的就可以
$ORAPWD FILE=<filename> PASSWORD=<password> ENTRIES=<max_users>
This step is required in 10.2 only and should not be performed in 11g.
Step 8 Shutdown and Startup Logical Standby Database in Mount Stage
--关闭逻辑standby并开户到mount状态
SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
Step 9 Adjust Initialization Parameter on Logical Standby Database
--转换为逻辑standby后,修改原有的日志归档目录
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
Step 10 Open the Logical Standby Database
--以resetlogs方式打开数据库
SQL> ALTER DATABASE OPEN RESETLOGS;
Step 11 Start Logical Apply on Standby
--开启sql apply,这里的immediate是要实时应用,需要有standby redo log支持,如果不需要实时应用,可不加immediate.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
二.逻辑standby创建完成后的一些设置
--在逻辑standby上将guard设置为standby,实现能在逻辑standby创建BI相关的用户
SQL>alter database guard standby;
--在逻辑standby上使用dbms_logstdby.skip跳过不需要同步的用户的表
SQL>alter database stop logical standby apply;
SQL>exec dbms_logstdby.skip(stmt=>'SCHEMA_DDL',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
SQL>exec dbms_logstdby.skip(stmt=>'DML',SCHEMA_NAME=>'HR',OBJECT_NAME=>'%');
SQL>alter database start logical standby apply;
如果将来需要将跳过的表重新添加回来
SQL>alter database stop logical standby apply;
通过exec dbms_logstdby.unskip添加回来
通过dbms_logstdby.instantiate_table同步,这里需要建dblink
SQL>alter database start logical standby apply;