搭建Oracle11gR2 DataGuard

安装好两台Oracle11gR2后,简单的建了个测试库,然后搭建了一套物理备库。整个过程非常简单,和以前的版本也没多大区别。不过因为11g密码以及密码文件名大小写的问题折腾了一下。

按照以前9i和10g的文档,使用如下语句创建了passwordfile:

orapwd file=$ORACLE_HOME/dbs/orapwTEST password=pass entries=30

结果发现主库的归档一直无法传递到备库,报:

ORA-16191: Primary log shipping client not logged on standby

根据以前的经验,重新创建了住备库的password file,根据metalink,修改了sec_case_sensitive_logon参数为false,都不行。最后才发现,必须将passwordfile中的sid改成小写才行。

orapwd file=$ORACLE_HOME/dbs/orapwtest password=pass entries=30

Oracle11g,最吸引人的特性是Active Data Guard,也就是物理备库在open read only的同时,可以apply log。启用该特性非常简单,首先要确保compatible=11.0.0.0以上,然后在open read only的状态下执行recover managed standby database disconnect即可。开启该特性最好能同时开启实时日志应用,以减少主备库之间数据的延迟,所以在备库添加好standby logfile:

recover managed standby database cancel;

alter database open;

alter database add standby logfile group 10 ('/u01/oracle/oradata/test/stbredo01.dbf') size 100m reuse; 
alter database add standby logfile group 11 ('/u01/oracle/oradata/test/stbredo02.dbf') size 100m reuse;
alter database add standby logfile group 12 ('/u01/oracle/oradata/test/stbredo03.dbf') size 100m reuse;
alter database add standby logfile group 13 ('/u01/oracle/oradata/test/stbredo04.dbf') size 100m reuse;

然后将备库置于实时日志应用模式:

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect; 
Database altered.

SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY

在主库创建测试表并插入数据:

SQL> create table ningoo.test_table(i int);
Table created.

SQL> insert into ningoo.test_table values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> alter system archive log current;
System altered.

在备库查询:

SQL> select * from ningoo.test_table;
         I
----------
         1

DataGuard环境慎用alter tablespace drop datafile

Oracle10g开始提供alter tablespace drop datafile的特性,允许删除没有数据的空文件。本来这是一个还有那么点用的功能,可是,慎用,有bug。

ALTER TABLESPACE ... DROP DATAFILE ... produces bad
redo which typically does not then drop the datafile
either during recovery or at a standby. This can lead
to subsequent errors such as ORA-600 [3689] of 
subsequent redo tries to add new datafile.

如果在data guard环境中,可能你在主库这么一执行,备库就挂了,报ora-600

ORA-00600: internal error code, arguments: [3689], [28], [], [], [], [], [], []

这个bug 5623467,从10.2.0.1一直影响到10.2.0.4,好在10.2.0.4后出了一个 Data Guard Physical Recommended Patch Bundle #1(Doc ID: 7936993.8),总算是修复了这个问题,已经升级或者准备升级到10.2.0.4的朋友,一定要主机打上这个patch bundle,Oracle总是在一些你不注意的地方留个坑,一不小心就让你灰头土脸。

Workaround
  Avoid the use of the ALTER TABLESPACE .. DROP DATAFILE ..
  command on the primary.

10.2.0.4 Data Guard Physical Recommended Patch Bundle #1主要修复的bug如下:

Bug:3934160 Intermittent OERI[ksfdgfnm1]
Bug:5126719 LGWR may terminate the instance due to ORA-16164
Bug:5404871 Local first not used archiving closed thread at higher protection mode
Bug:5476236 RFS logminer client inoperative after physical standby switchover to primary
Bug:5623467 Corrupt redo from ALTER TABLESPACE DROP DATAFILE
Bug:6024730 V$BACKUP . STATUS shows “UNKNOWN ERROR”
Bug:6070225 Terminal recovery encounters ORA-308 when RMAN is active
Bug:6074620 LGWR unconditionally writes to trace file
Bug:6345573 OERI [krsmseqa.2] during terminal recovery
Bug:6469211 Slow startup with many log files on ASM
Bug:6490140 LGWR may crash the instance
Bug:6711853 V$ARCHIVE_DEST does not show new ‘binding’ on the physical standby
Bug:6840740 Switchover to physical standby allowed even if there are no physical standbys
Bug:6874522 V$DATAGUARD_STATS . VALUE may be NULL in logical standby
Bug:6882739 Controlfile enqueue contention from DataGuard
Bug:6919819 Flashback database may not correctly flashback some data blocks
Bug:6941717 Logical standby considered synchronous during dictionary load
Bug:6954829 OERI[kcramr_8] during media recovery
Bug:6955744 Archive log is not automatically registered when second CDC is built
Bug:6976005 CF enqueue held for longer than needed with flashback enabled
Bug:6980597 Heartbeat redo is not generated for single instance primary with RTA logical
Bug:7013124 30 second delay for 2nd pass instance recovery
Bug:7044551 ALTER TABLESPACE BACKUP CONTROLFILE TO TRACE can be slow
Bug:7136489 Unwanted trace lines “FAIL: tkrsf_al_read: Invalid archive log file contents”
Bug:7140204 OERI[ktupdb_0] undo block corruption on readable standby
Bug:7159505 Standby MRP stuck when no recovery area space left
Bug:7197445 Standby Recovery session cancelled due to ORA-600 [3020]
Bug:7257461 ORA-16146 during block media recovery on a primary with no standby destination
Bug:7257770 OERI:krffReserveCFEntry_1 / flashback disabled in RAC
Bug:7276960 Intermittent ORA-16014 errors in Streams environment
Bug:7432601 Hang holding FAL queue latch
Bug:7460818 Flashback logs are not balanced between redo threads
Bug:7494333 MRP may request wrong (too old) log
Bug:7568556 Slow standby recovery
Bug:7593835 Write failure when “selecting” new SRL is not handle properly
Bug:7643632 High log file sync in Data Guard maximum availability (sync) mode
Bug:8230457 Physical standby media recovery gets OERI[krr_media_12]
Bug:8283650 A dump can occur under krsmchksl with trace enabled
Bug:8287155 Final gap error (ORA-16416) reported during switchover to standby
Bug:8304589 Terminal recovery may hang

无觅相关文章插件,快速提升流量