前提:
主库执行:
1.放在Oracle内部schema下的用户表将不会被复制到备库,并且DBA_LOGSTDBY_UNSUPPORTED这里也查询不到
--查询Oracle内部的schema
SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA';
2.
--查询sql Apply不支持的表
SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;
3. 为了确保sql Apply能有效应用update的redo数据到备库,建议主库的表都有主键或非空唯一索引
--查询没有唯一逻辑标识符的表
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';
如果上面的语句非常慢,建议直接执行:
SELECT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE BAD_COLUMN = 'Y';
步骤:
一 、 首先创建好物理备库
参考上一篇Blog: http://www.jb51.cc/article/p-yzlwrmsq-bqb.html
二、原物理备库上停止日志应用
sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
三、主库上设置支持逻辑备库的操作
1. 更改LOG_ARCHIVE_DEST_1,只归档在线日志,而不归档standby redo
sql> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/bak2/archivelog/
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=r5' scope=both;
2. 设置LOG_ARCHIVE_DEST_3,此参数仅当主库转换为逻辑备库角色时使用
sql> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/bak2/arch2/
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=r5' scope=both;
sql> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
注: 不能共用/bak2/archivelog/这个归档日志目录,应新建一个目录,否则会报错:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16033: parameter LOG_ARCHIVE_DEST_3 destination cannot be the same as
parameter LOG_ARCHIVE_DEST_1 destination
3. 在Redo数据中建立LogMiner字典
sql> EXECUTE DBMS_LOGSTDBY.BUILD;注: 此命令会等主库所有当前的事务完成后才执行
四、 备库上执行操作,以将物理备库转为逻辑备库
sql> ALTER DATABASE RECOVER TO LOGICAL STANDBY r5standby;
注: 如果执行此命令报ORA-19953: database should not be open,那么需要先shutdown,然后startup mount,
如果执行此命令卡住,则可执行ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL来取消
sql> SHUTDOWN;
sql> STARTUP MOUNT;
sql>alter system set log_archive_config='dg_config=(r5,r5standby)';
sql>ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
'LOCATION=+DATA/R5STANDBY/ARC1
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=r5standby' scope=both;
sql>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
'SERVICE=r5 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=r5' scope=both;
sql> alter diskgroup DATA add directory '+DATA/R5STANDBY/ARC2';
sql>ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=
'LOCATION=+DATA/R5STANDBY/ARC2
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=r5standby' scope=both;
sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
sql> ALTER DATABASE OPEN RESETLOGS;
sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
五、验证
备库上查询:
sql> select protection_mode,database_role,protection_level,open_mode from v$database;
PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE LOGICAL STANDBY MAXIMUM PERFORMANCE READ WRITE
sql> SELECT SESSION_ID,STATE FROM V$LOGSTDBY_STATE;
SESSION_ID STATE
---------- -------------
1 INITIALIZING
sql> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
注: 状态为INITIALIZING时,备库正在初始化,此时,你在主库的DML操作等,是还不会应用到备库来的,alter日志如下:
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3,Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M,Checkpoint interval = 150M
LOGMINER: SpillScn 0,ResetLogScn 0
Starting background process SMCO
Tue Jan 10 10:21:13 2017
SMCO started with pid=21,OS id=2803
[oracle@r5standby trace]$ tail -100 alert_r5standby.log
Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ENC$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_KOPM$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_NTAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_OPQTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_PARTOBJ$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_PROPS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_REFCON$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_SEED$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
Tue Jan 10 10:22:59 2017
LOGMINER: End mining logfiles during dictionary load for session 1
Tue Jan 10 10:22:59 2017
LSP2 started with pid=56,OS id=2859
Tue Jan 10 10:23:00 2017
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 1064,+DATA/r5standby/arc2/1_1064_865010252.dbf
LOGMINER: End mining logfile during commit scan for session 1 thread 1 sequence 1064,+DATA/r5standby/arc2/1_1064_865010252.dbf
LOGMINER: Begin mining logfile during commit scan for session 1 thread 1 sequence 1065,+DATA/r5standby/arc2/1_1065_865010252.dbf
LOGMINER: End mining logfiles during commit scan for session 1
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1064,+DATA/r5standby/arc2/1_1064_865010252.dbf
Tue Jan 10 10:23:02 2017
LOGSTDBY Analyzer process AS00 started with server id=0 pid=56 OS id=2862
Tue Jan 10 10:23:02 2017
LOGSTDBY Apply process AS05 started with server id=5 pid=61 OS id=2872
Tue Jan 10 10:23:02 2017
Tue Jan 10 10:23:02 2017
LOGSTDBY Apply process AS03 started with server id=3 pid=59 OS id=2868LOGSTDBY Apply process AS04 started with server id=4 pid=60 OS id=2870
Tue Jan 10 10:23:02 2017
LOGSTDBY Apply process AS02 started with server id=2 pid=58 OS id=2866
Tue Jan 10 10:23:02 2017
LOGSTDBY Apply process AS01 started with server id=1 pid=57 OS id=2864
LOGMINER: End mining logfile for session 1 thread 1 sequence 1064,+DATA/r5standby/arc2/1_1064_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1065,+DATA/r5standby/arc2/1_1065_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1065,+DATA/r5standby/arc2/1_1065_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1066,+DATA/r5standby/arc2/1_1066_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1066,+DATA/r5standby/arc2/1_1066_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1067,+DATA/r5standby/arc2/1_1067_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1067,+DATA/r5standby/arc2/1_1067_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1068,+DATA/r5standby/arc2/1_1068_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1068,+DATA/r5standby/arc2/1_1068_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1069,+DATA/r5standby/arc2/1_1069_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1069,+DATA/r5standby/arc2/1_1069_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1070,+DATA/r5standby/arc2/1_1070_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1070,+DATA/r5standby/arc2/1_1070_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1071,+DATA/r5standby/arc2/1_1071_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1071,+DATA/r5standby/arc2/1_1071_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1072,+DATA/r5standby/arc2/1_1072_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1072,+DATA/r5standby/arc2/1_1072_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1073,+DATA/r5standby/arc2/1_1073_865010252.dbf
LOGMINER: End mining logfile for session 1 thread 1 sequence 1073,+DATA/r5standby/arc2/1_1073_865010252.dbf
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 1074,+DATA/r5standby/onlinelog/sredo09.log
Tue Jan 10 10:23:12 2017
RFS LogMiner: RFS id [2810] assigned as thread [1] PING handler
此时说明正常了,然后我们可以测试同步,并且可以在备库上执行创建表等操作了
原文链接：https://www.f2er.com/oracle/210912.html