创建逻辑dg
来源:互联网 发布:网络运营的工作内容 编辑:程序博客网 时间:2024/03/29 14:07
逻辑备用DG
今天是2014-04-29,最近一直忙的事情,也没来的急写点东西。今天继续整理dg的相关内容,要说的是逻辑dg的创建过程和注意事项。
什么是逻辑dg呢?物理dg类似于主库的完整副本,是块对块的重做应用。但是对于逻辑dg,是类似采用日志挖掘技术将主库传来的redo数据,进行构造sql进而在备库进行应用。且备库可以正常打开,并能维护其他不是主库传送的需要维护的对象。
那么就存在一个问题,如果在备库采用rowid获得的数据,有可能和主库的完全不一致。那么当在主库修改数据的时候,我们建议在所修改的对象上,创建非空索引或是主键条件,那么在传送到备库的时候,继而能够应用这些约束,避免数据更新的错误性。但有时候并不是非要说一定要在主库建立对象表的主键或是非空索引,如果没有创建了,那么将会产生大量的无用redo,这就是在所有字段上都进行了日志补充导致。
The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database.
Oracle uses primary-key or unique-constraint/index supplemental logging to logically identify a modified row in the logical standby database. When database-wide primary-key and unique-constraint/index supplemental logging is enabled, eachUPDATE
statement also writes the column values necessary in the redo log to uniquely identify the modified row in the logical standby database.
If a table has a primary key defined, then the primary key is logged along with the modified columns as part of the
UPDATE
statement to identify the modified row.If there is no primary key, then the shortest nonnull unique-constraint/index is logged along with the modified columns as part of the
UPDATE
statement to identify the modified row.If there is no primary key and no nonnull unique constraint/index, then all columns of bounded size are logged as part of the UPDATE statement to identify the modified row. All columns are logged except the following: LONG, LOB, LONG RAW, object type, and collections.
A function-based index, even though it is declared as unique, cannot be used to uniquely identify a modified row. However, logical standby databases support replication of tables that have function-based indexes defined, as long as modified rows can be uniquely identified.
Oracle recommends that you add a primary key or a nonnull unique index to tables in the primary database, whenever possible, to ensure that SQL Apply can efficiently apply redo data updates to the logical standby database.
那么哪些是sql apply支持的类型和不支持哪些类型呢?查看手册如下:
C.1.1 Supported Datatypes in a Logical Standby Database
Logical standby databases support the following datatypes:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CHAR
CLOB
andNCLOB
DATE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
LONG
LONG RAW
NCHAR
NUMBER
NVARCHAR2
RAW
TIMESTAMP
TIMESTAMP WITH TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
VARCHAR
andVARCHAR2
LOBs stored as SecureFiles (requires that the primary database be run at a compatibility of 11.2 or higher.
XMLType
data for all storage models, assuming the following primary database compatibility requirements:XMLType
stored inCLOB
format requires primary database to run at a compatibility of 11.1 or higherXMLType
stored in object-relational format or as binary XML requires that the primary database be running Oracle Database 11g Release 2 (11.2.0.3) or higher with a redo compatibility setting of 11.2.0.3 or higher
C.1.1.1 Compatibility Requirements
SQL Apply support for the following has compatibility requirements on the primary database:
Multibyte
CLOB
support requires primary database to run at a compatibility of 10.1 or higher.IOT support without
LOB
s and Overflows requires primary database to run at a compatibility of 10.1 or higher.IOT support with
LOB
and Overflow requires primary database to run at a compatibility of 10.2 or higher.TDE support requires primary database to run at a compatibility of 11.1 or higher.
Segment compression requires primary database to run at a compatibility of 11.1 or higher.
Hybrid Columnar Compression support is dependent on the underlying storage system.
通过视图查看主库中的哪些表不能被sql apply :
[oracle@dg-one ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 29 21:30:01 2014Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> SQL> desc dba_logstdby_unsupported Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) COLUMN_NAME VARCHAR2(30) ATTRIBUTES VARCHAR2(39) DATA_TYPE VARCHAR2(32)SQL> select owner,table_name,column_name,attributes,data_type from dba_logstdby_unsupported;no rows selected查看存在唯一性问题的表,如下:SQL> select * from dba_logstdby_not_unique;OWNER TABLE_NAME B------------------------------ ------------------------------ -SCOTT BONUS NSCOTT SALGRADE NSTDBYPERF STATS$MANAGED_STANDBY NSTDBYPERF STATS$RECOVERY_PROGRESS NSQL> desc dba_logstdby_not_unique Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) BAD_COLUMN VARCHAR2(1)
好了,现在开始创建逻辑dg,因为我已经创建了物理 dg,那么我们从该基础上转换成逻辑dg(注:不能从逻辑dg转为物理dg)。如果没有创建物理dg,那么可以
直接去创建逻辑dg,但是注意参数的修改。
第一步:停止备库redo应用:
查看数据库模式:
SQL> set linesize 200 SQL> col dest_name for a50SQL> select database_mode,recovery_mode,protection_mode,dest_name from v$archive_dest_status where database_mode!='UNKNOWN';DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DEST_NAME--------------- ----------------------- -------------------- --------------------------------------------------OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE LOG_ARCHIVE_DEST_1SQL>
停止redo应用:
SQL> alter database recover managed standby database cancel;Database altered.SQL>
第二步:在主库build逻辑dg所需元数据。
SQL> exec dbms_logstdby.build;PL/SQL procedure successfully completed.SQL>
注:这个过程主要是做了什么操作呢?
A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building the LogMiner dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.
To build the LogMiner dictionary, issue the following statement:
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
The DBMS_LOGSTDBY.BUILD
procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.
Note:
In databases created using Oracle Database 11g release 2 (11.2) or later, supplemental logging information is automatically propagated to any existing physical standby databases. However, for databases in earlier releases, or if the database was created using an earlier release and then upgraded to 11.2, you must check whether supplemental logging is enabled at the physical standby(s) if it is also enabled at the primary database. If it is not enabled at the physical standby(s), then before performing a switchover or failover, you must enable supplemental logging on all existing physical standby databases. To do so, issue the following SQL command on each physical standby:SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;
If you do not do this, then any logical standby that is also in the same Data Guard configuration will be unusable if a switchover or failover is performed to one of the physical standby databases. If a switchover or failover has already occurred and supplemental logging was not enabled, then you must recreate all logical standby databases
第三步:在备库告诉mrp继续应用redo,达到字典构建时的scn;
SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 171970536 bytesDatabase Buffers 134217728 bytesRedo Buffers 4718592 bytesDatabase mounted.SQL> alter database recover to logical standby Amy;Database altered.
这时我们修改几个地方,首先是db_name;
SQL> alter system set db_name=Amy scope=spfile;System altered.SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
另外还有log_archive_dest_n 参数配置:
SQL> show parameter log_archive_dest_1NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_ DEST valid_for=(all_logfiles,a ll_roles)log_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 stringlog_archive_dest_17 stringNAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_18 stringlog_archive_dest_19 stringSQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service=dg1 lgwr sync valid_fo r=(online_logfiles,primary_rol e) db_unique_name=dg1log_archive_dest_20 string
这里需要说明第一个log_archive_dest_1 为什么是all_logfiles呢?因为我们是创建的逻辑备库,逻辑备库除了维护主库的对象外,还可以自己进行其他对象的修改操作那么这些对象的redo,是需要归档的,逻辑备库是一个真实数据库啊,另外如果不指定快速闪回区,那么应该在加一个log_archive_dest_3,将日志分开,另外如果是快速闪回区,可以省略valid_for,因为默认就是all_logfiles,all_roles。
另外要注意:如果逻辑备库和主库在一个相同的数据存储管理上,那么需要使用dbms_logstdby.skip去跳过alter tablespace dml的操作,不然可能产生误修改主库的表空间问题。
第四步:完成逻辑dg的创建:
SQL> shutdown immediat;SP2-0717: illegal SHUTDOWN optionSQL> shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area 313159680 bytesFixed Size 2252824 bytesVariable Size 171970536 bytesDatabase Buffers 134217728 bytesRedo Buffers 4718592 bytesDatabase mounted.SQL> alter database open resetlogs;Database altered.SQL> alter database start logical standby apply immediate;Database altered.SQL> SQL> set linesize 200SQL> select * from v$log GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- 1 1 1 52428800 512 1 NO CURRENT 1335271 29-APR-14 2.8147E+14 2 1 0 52428800 512 1 YES UNUSED 0 0 3 1 0 52428800 512 1 YES UNUSED 0 0
注:resetlogs做什么的?
resetlogs打开讲会对没有归档的redo日志文件中的内容进行覆盖,sequence重新从1开始计算,相当于数据库进入了一个新的化身或是新的生命周期,进入incarnation的目的就是为了不在去应用resetlog之后的日志的lcr。经常有朋友问我resetlog打开数据库了是否就是一个不完全恢复呢?这是一个非常错误的观点,完全恢复和不完全恢复不是依据resetlog定的。而是我们在前滚的时候是否全部应用了日志。如果全部应用了,那么就是一次完全恢复过程。
我们可以查看数据库的原型列表如下;
[oracle@dg-two dbs]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 29 22:20:46 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: AMY (DBID=1205246033)RMAN> list incarnation2> ;using target database control file instead of recovery catalogList of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------1 1 AMY 1205246033 PARENT 1335270 29-APR-142 2 AMY 1205246033 CURRENT 1335271 29-APR-14
第五步:验证逻辑dg:
SQL> select * from v$logstdby_progress;APPLIED_SCN APPLIED_T RESTART_SCN RESTART_T LATEST_SCN LATEST_TI MINING_SCN MINING_TI RESETLOGS_ID----------- --------- ----------- --------- ---------- --------- ---------- --------- ------------ 1335268 1334503 1334503 SQL> select process,status,sequence# from v$managed_standby;PROCESS STATUS SEQUENCE#--------- ------------ ----------ARCH CONNECTED 0ARCH CONNECTED 0ARCH CONNECTED 0ARCH CONNECTED 0ARCH CONNECTED 0SQL>
咦?发现问题了,查看主数据库日志:
Error 1017 received logging on to the standby------------------------------------------------------------Check that the primary and standby are using a password fileand remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191------------------------------------------------------------PING[ARC1]: Heartbeat failed to connect to standby 'dg2'. Error is 16191.
密码文件的问题咯
重建密码文件?那应该是在10G但是到11g不能重建,我们要做的是将主库的密码 文件copy到备库,然后mv。
之后查看主库日志 状态如下(可以发现主库每隔1分钟去主动ping一下备库):
------------------------------------------------------------PING[ARC1]: Heartbeat failed to connect to standby 'dg2'. Error is 16191.Tue Apr 29 22:39:42 2014Error 1017 received logging on to the standby------------------------------------------------------------Check that the primary and standby are using a password fileand remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191------------------------------------------------------------PING[ARC1]: Heartbeat failed to connect to standby 'dg2'. Error is 16191.Tue Apr 29 22:40:46 2014******************************************************************LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2******************************************************************LGWR: Standby redo logfile selected for thread 1 sequence 84 for destination LOG_ARCHIVE_DEST_2Thread 1 advanced to log sequence 84 (LGWR switch) Current log# 3 seq# 84 mem# 0: /u01/app/oracle/oradata/dg/redo03.logTue Apr 29 22:40:49 2014Archived Log entry 138 added for thread 1 sequence 83 ID 0x68b85302 dest 1:Tue Apr 29 22:40:49 2014ARC3: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2
查看备库日志:
LOGMINER: Read buffers: 16LOGMINER: Memory LWM: limit 10M, LWM 24M, 80%LOGMINER: Memory Release Limit: 1MRFS LogMiner: Registered logfile [/u01/app/oracle/fast_recovery_area/DG2/foreign_archivelog/DG1/2014_04_29/o1_mf_1_82_9ozghfdo_.arc] to LogMiner session id [1]RFS LogMiner: Registered logfile [/u01/app/oracle/fast_recovery_area/DG2/foreign_archivelog/DG1/2014_04_29/o1_mf_1_81_9ozghffw_.arc] to LogMiner session id [1]Tue Apr 29 22:40:47 2014Primary database is in MAXIMUM PERFORMANCE modeRFS[4]: Assigned to RFS process 3779RFS[4]: Selected log 4 for thread 1 sequence 84 dbid 1756848898 branch 843517891Tue Apr 29 22:40:48 2014RFS[5]: Assigned to RFS process 3777RFS[5]: Opened log for thread 1 sequence 79 dbid 1756848898 branch 843517891RFS LogMiner: Registered logfile [/u01/app/oracle/fast_recovery_area/DG2/foreign_archivelog/DG1/2014_04_29/o1_mf_1_79_9ozghhy9_.arc] to LogMiner session id [1]Tue Apr 29 22:40:50 2014LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=38 OS id=3781 sid=44 startedTue Apr 29 22:40:50 2014LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=39 OS id=3783 sid=45 startedTue Apr 29 22:40:50 2014LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=40 OS id=3785 sid=48 startedTue Apr 29 22:40:50 2014RFS LogMiner: RFS id [3787] assigned as thread [1] PING handlerTue Apr 29 22:40:51 2014RFS[6]: Assigned to RFS process 3789RFS[6]: Selected log 5 for thread 1 sequence 83 dbid 1756848898 branch 843517891Tue Apr 29 22:40:55 2014RFS LogMiner: Registered logfile [/u01/app/oracle/fast_recovery_area/DG2/foreign_archivelog/DG1/2014_04_29/o1_mf_1_83_9ozghp29_.arc] to LogMiner session id [1]LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 79, /u01/app/oracle/fast_recovery_area/DG2/foreign_archivelog/DG1/2014_04_29/o1_mf_1_79_9ozghhy9_.arcTue Apr 29 22:41:09 2014Thread 1 advanced to log sequence 2 (LGWR switch) Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/dg/DG2/onlinelog/o1_mf_2_9ozd8zc1_.logTue Apr 29 22:41:11 2014Archived Log entry 1 added for thread 1 sequence 1 ID 0x47d6f0d0 dest 1:Tue Apr 29 22:41:20 2014Thread 1 advanced to log sequence 3 (LGWR switch) Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/dg/DG2/onlinelog/o1_mf_3_9ozd904o_.logTue Apr 29 22:41:22 2014Archived Log entry 2 added for thread 1 sequence 2 ID 0x47d6f0d0 dest 1:Thread 1 cannot allocate new log, sequence 4Checkpoint not complete Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/dg/DG2/onlinelog/o1_mf_3_9ozd904o_.logThread 1 advanced to log sequence 4 (LGWR switch) Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/dg/DG2/onlinelog/o1_mf_1_9ozd8yhq_.logTue Apr 29 22:41:31 2014Archived Log entry 3 added for thread 1 sequence 3 ID 0x47d6f0d0 dest 1:Tue Apr 29 22:41:47 2014RFS LogMiner: RFS id [3787] assigned as thread [1] PING handler
确认进程:
SQL> select process,status,sequence# from v$managed_standby;PROCESS STATUS SEQUENCE#--------- ------------ ----------ARCH CLOSING 2ARCH CLOSING 3ARCH CONNECTED 0ARCH CLOSING 83ARCH CLOSING 1RFS IDLE 0RFS IDLE 0RFS IDLE 0RFS IDLE 0RFS IDLE 8410 rows selected.SQL>
至此完成逻辑dg的搭建过程:
- DG - 逻辑Standby创建
- 创建逻辑dg
- DG - 逻辑standby管理
- dg broker 创建dg &switchover
- DG - 逻辑standby角色转换
- DG搭建逻辑备库
- Rman创建DG环境
- DG创建 -rman
- oracle10g dg 创建闪回点报错
- 物理DG与逻辑DG的区别与逻辑DG同步异常处理方法
- 配置DG为逻辑standby模式
- 逻辑DG之Standby角色转换
- Oracle DG 之逻辑备库--Switchover
- [oracle]Oracle 11g 逻辑DG搭建
- 一键DG脚本(静默安装创建实例创建DG)
- 用duplicate创建物理DG
- oracle 之物理DG 创建
- 在线创建dg环境 adg
- SQL2008或者SQL2005里ROW_NUMBER分页,SQL2000怎么实现等同的效果
- NYOJ117 求逆序数
- 浅谈C++中指针和引用的区别
- 用 jQuery 实现 Ajax 跨域
- js 判断Null Undefined 与NaN
- 创建逻辑dg
- Uploadify 插件 “Call to SetButtonDisabled failed” 错误解决方法
- Mybatis 自动生成代码工具
- ubuntu永久修改主机名
- 如何学好C++
- SQL语句还原数据库
- javasctipt显示几分钟前、几天前等
- 当你扛不住的时候,迷茫的时候看看这些!
- java计算器 图形用户界面 升级版 v1.02