Create Logical Standby Database
1.Create a Logical Standby Database
1.1Verify the Primary Datbase
以下SQL可以确定哪些表不支持(不被支持的表通常是由于列的定义包含了不支持的数据类型):
1 SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y'; 2 OWNER TABLE_NAME 3 ------------------------------ ------------------------------ 4 TSMSYS SRS$
假如某张表没有主键或唯一约束，可以通过以下方式来给表增加唯一特征:
1 SQL> alter table tsmsys.srs$ add primary key (cursor,LOGICALSESSID) rely disable; 2 Table altered. 3 SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y'; 4 no rows selected
查看主库是否启用了Supplemental logging特征:
1 SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database; 2 SUP SUP 3 --- --- 4 NO NO
如果未启动，可以这样开启:
1 SQL> alter database add supplemental log data (PRIMARY KEY,unique INDEX) columns; 2 Database altered. 3 SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database; 4 SUP SUP 5 --- --- 6 YES YES
1.2 Stop the Redo Apply on the Physical Standby Database—node2
1 SQL> RECOVER MANAGED STANDBY DATABASE CANCEL; 2 Media recovery complete.
1.3 Create LogMiner to Redo on the Primary Database and archiving
Build过程会等到所有事务都完成，这个步骤需要使用闪回查询技术对数据字典信息执行一致性读，故undo_retention需要设置时间长一些，3600以上，不然可能碰到ora-1555。
1 SQL> show parameter undo_retention; 2 NAME TYPE VALUE 3 ------------------------------------ ----------- ------------------------------ 4 undo_retention integer 900 5 SQL> alter system set undo_retention=3800 scope=both; 6 System altered. 7 8 SQL> EXECUTE DBMS_LOGSTDBY.BUILD; 9 PL/SQL procedure successfully completed. 10 SQL> alter system archive log current; 11 System altered.
1.4 Switch to the Logical Standby Database and rename the db_name
1 SQL> recover to logical standby YFTLOG; 2 Media recovery complete. 3 SQL> exit
1.5 Create a Password file
1 [oracle@node3 dbs]$ rm orapwYFT3 2 [oracle@node3 dbs]$ orapwd file=orapwYFT3 password=oracle entries=5
1.6 Startup mount the Logical Standby Database
1 SQL> alter system set db_file_name_convert='' scope=both; 2 SQL> alter system set log_file_name_convert='' scope=both; 3 SQL> shutdown immediate 4 SQL> STARTUP MOUNT
1.7 Restlogs on the Logical Standby Database
1 SQL> alter database open resetlogs; 2 Database altered.
1.8 Start the Sql Apply
1 SQL> alter database start logical standby apply immediate; 2 Database altered.
1.9 Verify the Logical Standby Database
1 SQL> select name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database; 2 3 SQL> archive log list; 4 Database log mode Archive Mode 5 Automatic archival Enabled 6 Archive destination /u01/arch 7 Oldest online log sequence 0 8 Next log sequence to archive 1 9 Current log sequence 1
