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--
--具体步骤参见前面创建物理备库部分
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--