开源中文网

您的位置: 首页 > 数据库应用 > SQLite > 正文

Oracle11g逻辑DG搭建

来源: 流云追风的博客  作者: 流云追风

前提:

主库执行:

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://blog.csdn.net/edcvf3/article/details/54288336



二、原物理备库上停止日志应用

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

此时说明正常了, 然后我们可以测试同步,并且可以在备库上执行创建表等操作了。

Tags:逻辑
相关文章列表:
关于开源中文网 - 联系我们 - 广告服务 - 网站地图 - 版权声明