Data Guard之Logical Standby Database

1、首先搭建Physical Standby Database，具体可参考"Data Guard之Physical Standby Database"

2、查询列出主数据库表中逻辑数据库不支持的表的方案名和表名
SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
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';

3、查询不支持的表中的列名和数据类型
SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='owner' AND TABLE_NAME = 'table_name';

4、确保主库中的表行能被唯一标志
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);

如果可以确认表中有唯一标示的字段，但是又不想真正创建主键或者索引来增加更新的开销，那么可以创建disabled RELY类型的约束
SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

5、检查supplemental logging是否已经被激活
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

用以下方法激活Primary库的supplemental logging属性
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

注意，如果在一个有物理备用数据库的Data Guard配置中，你在主数据库激活追加日志功能，之后你必须在每一个物理备用数据库执行
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
这个语句，来确保将来的switchover能够正确工作

6、创建一个新的表空间，用于LogMiner，否则Logical Standby需要的对象将默认创建在SYSTEM表空间中
SQL> CREATE TABLESPACE logmnrts DATAFILE '/data/app/oracle/oradata/boss/logmnrts.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');

查看日志是否都已传入到standby database
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG 
WHERE DICTIONARY_BEGIN = 'YES' AND STANDBY_DEST= 'NO'));
如能找到，例如找到如下记录
NAME
--------------------------------------------------------------------------------
/data/archive_dets/march/1_29.dbf

在standby上，先注册primary库还未传到standby库的日志
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE GUARD ALL;
SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE ‘/data/archive_dets/march/1_29.dbf‘;

7、确保待转换的物理Standby数据库已经停止REDO应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

8、修改主库LOG_ARCHIVE_DEST_n参数
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/archive_dest/march VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boss';
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/data/archive_dest/sarch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=boss';
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
注：LOG_ARCHIVE_DEST_STATE_3仅仅只在主库切换时使用

9、Primary数据库生成数据字典
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

10、转换物理Standby为逻辑Standby
SQL> SHOW PARAMETER DB_NAME
SQL> SHOW PARAMETER SPFILE
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY bosstdy;

注意：此时standby数据库参数文件最好是spfile，否则会报错
ORA-16254: change db_name to STDBYDB in the client-side parameter file (pfile)

11、standby数据库重置密码文件
由于数据库更名，包括DBID、INCARNATION等均已被重新初始化，此时需要重新创建备库的密码文件，注意保持和主库相同
[oracle@oracle-db2~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

12、重启standby数据库
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
为什么要重启？因为上述操作涉及逻辑Standby数据库更名，包括DBID、INCARNATION等均已被重新初始化

SQL> SHOW PARAMETER DB_NAME;

13、调整逻辑Standby数据库初始化参数
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/data/archive_dest/march VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bosstdy';
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=/data/archive_dest/sarch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bosstdy';
SQL> Alter SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

14、打开逻辑Standby
由于逻辑Standby与Primary数据库事务并不一致，因此第一次打开时必须指定RESETLOGS子句
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;
或者
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
或者
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

如果要启用实时应用，建议首先创建Standby Redo logs，如有则无需创建
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/data/app/oracle/oradata/boss/sredo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/data/app/oracle/oradata/boss/sredo05.log') size 50M;
注意：至少要比ORL多一组

重新执行启动REDO应用的命令，附加APPLY IMMEDIATE子句，以打开实时应用
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

若创建了standby redologs 后就必须用下面的SQL语句开启实时应用
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

如要停止SQL Apply应用，输入以下命令
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

15、验证环境
所有配置完成，接下来尝试在Primary数据库端执行修改操作，看看是否能够分别在逻辑Standby和物理Standby端应用

验证重做数据是否被正确应用
在逻辑备用数据库，查询V$LOGSTDBY_STATS视图，例如：
SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
NAME VALUE
------------------------------ ------------------------------
coordinator state INITIALIZING
了解协调进程的状态非常重要，因为它是指示其他逻辑备用进程的LSP后台进程。

查看V$LOGSTDBY视图来观察当前sql apply的行为
初始化阶段V$LOGSTDBY的输出：
SQL> COLUMN STATUS FORMAT A50
SQL> COLUMN TYPE FORMAT A12
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR ORA-16115: loading Log Miner dictionary data
READER ORA-16127: stalled waiting for additional transact
ions to be applied
BUILDER ORA-16117: processing
PREPARER ORA-16116: no work available
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR ORA-16126: loading table or sequence object number
READER ORA-16116: no work available
BUILDER ORA-16116: no work available
PREPARER ORA-16116: no work available

应用阶段的V$LOGSTDBY的输出：
SQL> COLUMN NAME FORMAT A30
SQL> COLUMN VALUE FORMAT A30
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
NAME VALUE
------------------------------ ------------------------------
coordinator state APPLYING
SQL> COLUMN STATUS FORMAT A50
SQL> COLUMN TYPE FORMAT A12
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;
TYPE HIGH_SCN STATUS
------------ ---------- --------------------------------------------------
COORDINATOR ORA-16117: processing
READER ORA-16127: stalled waiting for additional transact
ions to be applied
BUILDER 191896 ORA-16116: no work available
PREPARER 191902 ORA-16117: processing
ANALYZER 191820 ORA-16120: dependencies being computed for transaction at SCN 0x0000.0002ed4e
APPLIER 191209 ORA-16124: transaction 1 16 1598 is waiting on another transaction
APPLIER 191205 ORA-16116: no work available
APPLIER 191206 ORA-16124: transaction 1 5 1603 is waiting on another transaction
APPLIER 191213 ORA-16117: processing
APPLIER 191212 ORA-16124: transaction 1 20 1601 is waiting on another transaction
APPLIER 191216 ORA-16124: transaction 1 4 1602 is waiting on another transaction

检查sql apply的整个处理过程。
SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;
APPLIED_SCN NEWEST_SCN
----------- ----------
180702 180702
如果备用重做日志文件没有配置，APPLIED_SCN 和NEWEST_SCN列的值是相等的，表明在归档重做日志文件中所有可用数据都被应用了。
这些值可以与DBA_LOGSTDBY_LOG视图中的FIRST_CHANGE#列的值比较，查看有多少日志文件信息被应用，多少剩余。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26052382/viewspace-1098224/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26052382/viewspace-1098224/