Create Logical Standby Database

蔚落 2021-12-17 16:57 225阅读 0赞

**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

转载于:https://www.cnblogs.com/Richardzhu/articles/2796243.html