创建Oracle dataguard logical standby database

1.1 创建physical standby数据库

--具体步骤参见前面创建物理备库部分

1.2 停止physical standby的redo apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

1.3 准备主库的角色切换

$ mkdir /u01/app/flash_recovery_area/GUARD1/arch2

SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/flash_recovery_area/GUARD1/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=guard1' scope=both;

--该参数为将来主库转换为逻辑备库而设置，当guard1为主库时该参数忽略；为逻辑备库是接收主库的重做数据放在location下。

1.4 找出主库中没有唯一逻辑标识的表，添加主键约束

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'

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

--RELY DISABLE主键约束假定表的主键是唯一的，但插入数据时并不会效验约束的完整性。

1.5 建立重做数据字典

--SQL Apply时LogMiner必须要使用字典
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

--因为逻辑STANDBY对象的默认表空间是SYSTEM表空间，为了减少对系统表空间的影响，也可以另外创建一个表空间存放字典数据。
SQL> CREATE TABLESPACE logminer DATAFILE '/u01/app/oradata/guard1/logminer01.dbf' size 50M;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logminer');

2 创建逻辑备库

2.1 转换到physical standby到logical standby数据库

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY guard3;

Database altered.

--当然guard3也可以和原db_unique_name相同，如相同2.2步可以省略

2.2 创建一个新的密码文件

2.3 为logical standby调整初始化参数

SQL> SHUTDOWN;
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  205520896 bytes
Fixed Size                  1218532 bytes
Variable Size              67110940 bytes
Database Buffers          134217728 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL> alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/flash_recovery_area/GUARD2/arch VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=guard2' scope=both;

SQL> alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=guard1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=guard1' scope=both;

SQL> alter system set LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/app/flash_recovery_area/GUARD2/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=guard2' scope=both;

--数据库为主库时，LOG_ARCHIVE_DEST_1为主库在线日志归档路径；为备库时用作本地在线日志归档路径。逻辑备库自己也产生重做日志，因此不论主备库该路径都接收本地在线日志的归档。
--数据库为主库时，LOG_ARCHIVE_DEST_2指示日志传输到远端的standby数据库；为备库时该参数忽略。
--数据库为主库时，LOG_ARCHIVE_DEST_3该参数忽略；为备库时该路径为接收从主库归档的在线日志。

2.4 打开逻辑备库

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

--因为前面2.1步生成了新的db_name，第一次打开备库时需要加上resetlogs选项。

3 主备库切换

3.1 确认主库是否能够切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

3.2 准备将主库切换为逻辑备库

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

--这时的状态已经变为准备切换

3.3 准备将逻辑备库切换为主库

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

Database altered.

--该语句将会在逻辑备库上开启重做日志传输服务。

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

3.4 确认当前的主库已经准备好接收新主库的重做日志

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY

3.5 将主库切换成逻辑备库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

3.6 将逻辑备库切换成主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

3.7 在备库上开启SQL Apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.


4 插入数据

4.1 在主库上插入数据

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> insert into scott.dept values(50, 'TEST', 'BEIJING');

1 row created.

SQL> commit;

Commit complete.

4.2 模拟日志切换

SQL> alter system archive log current;

System altered.

4.3 查看告警日志文件

Sun Nov  2 02:19:15 2008
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, reader process P000 started with pid=25 OS id=6296
LOGMINER: session# = 1, builder process P001 started with pid=26 OS id=6298
LOGMINER: session# = 1, preparer process P002 started with pid=27 OS id=6300
Sun Nov  2 02:19:15 2008
LOGMINER: Begin mining logfile: /u01/app/flash_recovery_area/GUARD1/arch2/1_6_669689482.dbf
Sun Nov  2 02:19:15 2008
LOGMINER: Turning ON Log Auto Delete
Sun Nov  2 02:19:15 2008
LOGMINER: End mining logfile: /u01/app/flash_recovery_area/GUARD1/arch2/1_6_669689482.dbf
LOGSTDBY Analyzer process P003 started with pid=28 OS id=6306
LOGSTDBY Apply process P004 started with pid=29 OS id=6308
LOGSTDBY Apply process P007 started with pid=31 OS id=6314
LOGSTDBY Apply process P006 started with pid=30 OS id=6312
LOGSTDBY Apply process P008 started with pid=32 OS id=6316
LOGSTDBY Apply process P005 started with pid=22 OS id=6310
Sun Nov  2 02:19:16 2008
Primary database is in MAXIMUM PERFORMANCE mode
RFS[10]: Successfully opened standby log 4: '/u01/app/oradata/guard1/redo04.log'
Sun Nov  2 02:19:16 2008
RFS[7]: Archived Log: '/u01/app/flash_recovery_area/GUARD1/arch2/1_7_669689482.dbf'
Sun Nov  2 02:19:16 2008
RFS LogMiner: Registered logfile [/u01/app/flash_recovery_area/GUARD1/arch2/1_7_669689482.dbf] to LogMiner session id [1]
Sun Nov  2 02:19:17 2008
LOGMINER: Begin mining logfile: /u01/app/flash_recovery_area/GUARD1/arch2/1_7_669689482.dbf
Sun Nov  2 02:19:17 2008
LOGMINER: End mining logfile: /u01/app/flash_recovery_area/GUARD1/arch2/1_7_669689482.dbf
Sun Nov  2 02:19:18 2008
LOGMINER: Log Auto Delete - deleting: /u01/app/flash_recovery_area/GUARD1/arch2/1_6_669689482.dbf
Deleted file /u01/app/flash_recovery_area/GUARD1/arch2/1_6_669689482.dbf

--这里摘录一段备库应用归档日志记录。

4.3 在备库上验证数据应用

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 TEST           BEIJING


--end--