星期一 七月 09, 2012

如何针对物理DataGuard设置延时日志应用?

     为了设置延时Apply,需要在log_archive_dest_n 参数上使用“DELAY=",比如:DELAY=360(单位为分钟),表示延时360分钟(6小时)。比如:
SQL〉alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM DELAY=360 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE  DB_UNIQUE_NAME=standby';


   如果只指定了DELAY参数,但是没有指定具体的值,默认是30分钟。


   需要注意的是,如果已经启用了实时日志应用(real-time apply ),DELAY这个参数会被忽略,因此不会启用延时日志应用,同时,在备库的alert log中您可以看到类似下面的信息:
WARNING: Managed Standby Recovery started with USING CURRENT LOGFILE
DELAY 360 minutes specified at primary ignored <<<<<<<<<


   这种情况下,需要停止实时日志应用,按照非实时模式启动MRP,比如:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


请参考下面的具体的例子:
1. 查看主库的参数设置:
SQL> show parameter log_archive_dest_2


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=STANDBY LGWR SYNC AFFI
                                                RM VALID_FOR=(ONLINE_LOGFILES,
                                                PRIMARY_ROLE) DB_UNIQUE_NAME=S
                                                TANDBY


2. 在主库设置延时5分钟:
SQL> alter system set log_archive_dest_2='SERVICE=STANDBY LGWR SYNC AFFIRM delay=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY';


3. 验证是否延时:


主库执行:
SQL> alter system switch logfile;
System altered.


SQL>  select max(sequence#) from v$archived_log;


MAX(SEQUENCE#)
--------------
           28


备库:
Wed Jun 13 19:48:53 2012
Archived Log entry 14 added for thread 1 sequence 28 ID 0x4c9d8928 dest 1:
ARCb: Archive log thread 1 sequence 28 available in 5 minute(s)
Wed Jun 13 19:48:54 2012
Media Recovery Delayed for 5 minute(s) (thread 1 sequence 28) <<<<<<<<延时生效
Wed Jun 13 19:53:54 2012
Media Recovery Log /home/oracle/arch1/standby/1_28_757620395.arc<<<<<5分钟后才应用这个归档
Media Recovery Waiting for thread 1 sequence 29 (in transit)



更多的信息,请参考官方文档:
http://docs.oracle.com/cd/E11882_01/server.112/e25608/log_apply.htm
Oracle® Data Guard Concepts and Administration
11g Release 2 (11.2)
Part Number E25608-03

星期四 五月 03, 2012

11g 新特性—— Active Database Duplication for A standby database

简介
------------
从11G开始oracle提供了一个新功能Active Database Duplication for A standby database来创建配置物理standby 数据库。
Active Database Duplication for A standby database这个功能主要从一个正在运行的数据库复制数据文件,控制文件等到一个物理备库(physical standby database)。
这个功能简化了创建standby database过程中在主库备份和备库恢复的环节,实现了自动拷贝主库的控制文件,数据文件等到备库,对比基于备份集的创建standby database过程中需要手动在主库备份,然后将备份集拷贝到备库再手动恢复来说,减少了dba的大量工作。

创建物理standby database过程中的其他环节,如打开主库的force logging,主库init参数的修改等,以及备库开始应用redo log这些环节还需手动来完成的,与以往的创建配置物理standby database 是相同的。

备库的init参数需要在duplicate命令中指定,没有特殊指定的,就会默认使用主库的init参数值。

下面以一个测试例子来具体说明
-----------------------------------
主库:11.2.0.1 单机数据库,db_unique_name=orcl,数据文件存放在文件系统
备库:11.2.0.1 单机数据库,db_unique_name=orabak,使用ASM存储
Data Guard保护模式(Protection mode)采用最大性能模式(MAXIMIZE PERFORMANCE)
redo log传输采用LGWR进程的异步传输方式

1. 打开主库force logging:
SQL>  ALTER DATABASE FORCE LOGGING;

查看主库当前是否为force logging:
SQL> select force_logging from v$database;
FOR
---
YES

2. 修改主库的初始化参数如下:
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orabak)'
LOG_ARCHIVE_DEST_1=
  'LOCATION=/home/oracle/app/archdir
  VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
  DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2=
  'SERVICE=orabak LGWR ASYNC
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   
   DB_UNIQUE_NAME=orabak'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_MAX_PROCESSES=10

3. 在备库创建ASM 磁盘组 '+DATA',用来存放备库的数据文件,控制文件,standby redo文件等。

4. 在备库创建 adump 路径,与参数audit_file_dest的路径一致
$ mkdir -p /u01/app/admin/orabak/adump

5. 在备库创建init参数文件$ORACLE_HOME/dbs/initorabak.ora',
有了这个临时的init参数文件,我们就可以在duplicate命令运行前将备库的AUXILIARY实例启动到nomount状态,这也是运行duplicate命令的一个必要条件。

initorabak.ora文件只有一行信息:
DB_NAME=orcl

6. 将密码文件从主库服务器拷贝到备库服务器,并且重命名:
$ mv orapworcl orapworabak

7. 在主库服务器和备库服务器, 编辑添加下面信息到$ORACLE_HOME/network/tnsnames.ora
orabak =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orabak)
   ))

orcl =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVICE_NAME = orcl)
   ))

8. 在备库服务器, 添加静态注册信息到 $GRID_HOME/network/listener.ora文件,
这主要是由于AUXILIARY实例启动到nomount状态时,listener无法注册AUXILIARY实例,listener会标志Auxiliary实例为'blocked'状态,因此duplicate命令就无法通过TNS的方式连接到Auxiliary实例,为了解决这个问题,需要先手动静态注册数据库实例到listener上。
当Data Guard配置完成后,就可以删除静态注册的配置信息。
(本测试使用ASM,因此需要安装GRID,有效的listener.ora文件是在GRID_HOME下面)

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = ANYTHING))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
   ) )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (GLOBAL_DBNAME = orabak)
     (ORACLE_HOME = /home/oracle/app/product/11.2)
     (SID_NAME = orabak)
   ) )

9. 在备库服务器,执行duplicate命令前,先启动AUXILIARY实例到 nomount 状态:
$ export ORACLE_SID=orabak
$ sqlplus / as sysdba
SQL> startup nomount;

10. 在备库测试与AUXILIARY实例的连接和与主库的连接,如果连接成功,继续执行下面的步骤,
很多时候duplicate命令失败都是由于连接失败导致的。
$ sqlplus sys/oracle@orabak as sysdba
$ sqlplus sys/oracle@orcl as sysdba


11. 创建备库
在duplicate命令中指定关键字'FOR STANDBY'和'FROM ACTIVE DATABASE'表示从一个active的数据库来复制创建物理备库
参数'DORECOVER'表示duplicate命令会执行recover动作,否则duplicate命令只执行restore,
备库与主库不同的初始化参数,需要在duplicate命令中特殊指定,这样创建备库的spfile时就会用指定的值替换主库的参数值,
主库与备库的数据文件的路径不同,因此设置参数'DB_FILE_NAME_CONVERT'

% rman target sys/oracle@orcl AUXILIARY SYS/oracle@orabak
RMAN>
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET "db_unique_name"="orabak"
SET FAL_SERVER="orcl"
SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/archdir_sbredo
                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'
SET CONTROL_FILES '+DATA/orabak/control01.ctl'
set diagnostic_dest='/u01/app'
set audit_file_dest='/u01/app/admin/orabak/adump'
set DB_FILE_NAME_CONVERT='/home/oracle/app/oradata/orcl','+DATA/orabak/datafile'
NOFILENAMECHECK;


以下是duplicate standby database过程中屏幕的log信息
-----------------------------------------------------
Starting Duplicate Db at 15-APR-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:

{
   backup as copy reuse
   targetfile  '/home/oracle/app/product/11.2/dbs/orapworcl' auxiliary format
 '/u01/app/product/11.2/dbs/orapworabak'   targetfile
 '/home/oracle/app/product/11.2/dbs/spfileorcl.ora' auxiliary format
 '/u01/app/product/11.2/dbs/spfileorabak.ora'   ;
   sql clone "alter system set spfile= ''/u01/app/product/11.2/dbs/spfileorabak.ora''";
}
executing Memory Script

Starting backup at 15-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 15-APR-12

sql statement: alter system set spfile= ''/u01/app/product/11.2/dbs/spfileorabak.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''orabak'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER =
 ''orcl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_1 =
 ''LOCATION=/u01/app/archdir_sbredo                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'' comment=
 '''' scope=spfile";
   sql clone "alter system set  CONTROL_FILES =
 ''+DATA/orabak/control01.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  diagnostic_dest =
 ''/u01/app'' comment=
 '''' scope=spfile";
   sql clone "alter system set  audit_file_dest =
 ''/u01/app/admin/orabak/adump'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/home/oracle/app/oradata/orcl'', ''+DATA/orabak/datafile'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_unique_name =  ''orabak'' comment= '''' scope=spfile

sql statement: alter system set  FAL_SERVER =  ''orcl'' comment= '''' scope=spfile

sql statement: alter system set  LOG_ARCHIVE_DEST_1 =  ''LOCATION=/u01/app/archdir_sbredo                       VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=orabak'' comment= '''' scope=spfile

sql statement: alter system set  CONTROL_FILES =  ''+DATA/orabak/control01.ctl'' comment= '''' scope=spfile

sql statement: alter system set  diagnostic_dest =  ''/u01/app'' comment= '''' scope=spfile

sql statement: alter system set  audit_file_dest =  ''/u01/app/admin/orabak/adump'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/home/oracle/app/oradata/orcl'', ''+DATA/orabak/datafile'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     238530560 bytes

Fixed Size                     1335724 bytes
Variable Size                 83889748 bytes
Database Buffers             150994944 bytes
Redo Buffers                   2310144 bytes

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '+DATA/orabak/control01.ctl';
}
executing Memory Script

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/app/product/11.2/dbs/snapcf_orcl.f tag=TAG20120415T185859 RECID=8 STAMP=780692341
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
Finished backup at 15-APR-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "+DATA/orabak/datafile/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "+DATA/orabak/datafile/system01.dbf";
   set newname for datafile  2 to
 "+DATA/orabak/datafile/sysaux01.dbf";
   set newname for datafile  3 to
 "+DATA/orabak/datafile/undotbs01.dbf";
   set newname for datafile  4 to
 "+DATA/orabak/datafile/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "+DATA/orabak/datafile/system01.dbf"   datafile
 2 auxiliary format
 "+DATA/orabak/datafile/sysaux01.dbf"   datafile
 3 auxiliary format
 "+DATA/orabak/datafile/undotbs01.dbf"   datafile
 4 auxiliary format
 "+DATA/orabak/datafile/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/orabak/datafile/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oradata/orcl/system01.dbf
output file name=+DATA/orabak/datafile/system01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:24
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oradata/orcl/undotbs01.dbf
output file name=+DATA/orabak/datafile/undotbs01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:13
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oradata/orcl/sysaux01.dbf
output file name=+DATA/orabak/datafile/sysaux01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:13
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oradata/orcl/users01.dbf
output file name=+DATA/orabak/datafile/users01.dbf tag=TAG20120415T185922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-APR-12

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/home/oracle/app/archdir/1_46_778869623.dbf" auxiliary format
 "/u01/app/archdir_sbredo/1_46_778869623.dbf"   archivelog like
 "/home/oracle/app/archdir/1_47_778869623.dbf" auxiliary format
 "/u01/app/archdir_sbredo/1_47_778869623.dbf"   ;
   catalog clone archivelog  "/u01/app/archdir_sbredo/1_46_778869623.dbf";
   catalog clone archivelog  "/u01/app/archdir_sbredo/1_47_778869623.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 15-APR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=46 RECID=45 STAMP=780692365
output file name=/u01/app/archdir_sbredo/1_46_778869623.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=47 RECID=46 STAMP=780692416
output file name=/u01/app/archdir_sbredo/1_47_778869623.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 15-APR-12

cataloged archived log
archived log file name=/u01/app/archdir_sbredo/1_46_778869623.dbf RECID=1 STAMP=780696505

cataloged archived log
archived log file name=/u01/app/archdir_sbredo/1_47_778869623.dbf RECID=2 STAMP=780696505

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=780696506 file name=+DATA/orabak/datafile/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=780696506 file name=+DATA/orabak/datafile/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=780696506 file name=+DATA/orabak/datafile/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=780696506 file name=+DATA/orabak/datafile/users01.dbf

contents of Memory Script:
{
   set until scn  303787;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 15-APR-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=25 device type=DISK

starting media recovery

archived log for thread 1 with sequence 46 is already on disk as file /u01/app/archdir_sbredo/1_46_778869623.dbf
archived log for thread 1 with sequence 47 is already on disk as file /u01/app/archdir_sbredo/1_47_778869623.dbf
archived log file name=/u01/app/archdir_sbredo/1_46_778869623.dbf thread=1 sequence=46
archived log file name=/u01/app/archdir_sbredo/1_47_778869623.dbf thread=1 sequence=47
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-APR-12


ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/home/oracle/app/oradata/orcl/redo02.log'

RMAN-05535: WARNING: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/home/oracle/app/oradata/orcl/redo03.log'


RMAN-05535: WARNING: All redo log files were not defined properly.
Finished Duplicate Db at 15-APR-12
---------------------------------------------------------------

以上创建完成了物理standby数据库(最后的错误警告信息可以忽略,我们在后面的附录1详细解释这个错误信息)。但是备库数据库还没有开始应用redo log。

12. 创建standby redo log,standby redo log大小等于主库online redo log大小:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
   ('+data/redo_sb_01.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
   ('+data/redo_sb_02.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 13
   ('+data/redo_sb_03.log') SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 14
   ('+data/redo_sb_04.log') SIZE 52428800;

13. 运行下面的命令开始应用redo log:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


以上的全部步骤就配置完成了物理standby数据库,并且开始应用redo log。

附录1
----------
在创建备库时最后报出的错误警告信息ORA-19527和在MRP开始应用redo log时,alert log中报出下面的错误信息:

Errors in file /u01/app/diag/rdbms/orabak/orabak/trace/orabak_mrp0_7644.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
.....
Errors in file /u01/app/diag/rdbms/orabak/orabak/trace/orabak_mrp0_7644.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/home/oracle/app/oradata/orcl/redo01.log'

以上错误的原因:
oracle为了加快备库与主库switchover的速度,从10.2开始增加了一个增强的功能,就是当MRP启动时会去清理备库上online redo log。造成以上2个错误有2个原因,第一个是备库没有创建online redo log,第二个是备库没有设置log_file_name_convert参数。

解决方法:
方法#1:如果不考虑switchover(备库上不创建online reod log),那么可以忽略这个错误,因为这个错只是一个提示性的信息,不会影响备库的MRP的工作。
方法#2:如果考虑switchover,在备库上创建online reod log,并且设置log_file_name_convert参数:
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA/redo01.log') SIZE 52428800;
SQL> ALTER DATABASE ADD LOGFILE GROUP 5 ('+DATA/redo02.log') SIZE 52428800;
SQL> ALTER DATABASE ADD LOGFILE GROUP 6 ('+DATA/redo03.log') SIZE 52428800;
SQL> alter system set log_file_name_convert='/home/oracle/app/oradata/orcl','+data' scope=spfile;
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


星期六 三月 31, 2012

物理Data Guard中哪个进程处理Redo GAP

  在Oracle Data Guard中,Redo Gap的产生是由于一些网络或者其他问题导致redo的传输中断。当故障消除后,这些没有传输过去的redo文件会由一些进程发现,并且将它们传输到备库。

术语:

ARC:归档进程
MRP:Media Recovery Process,在备库上负责应用redo
RFS:Remote File Server ,在备库上接收发送过来的redo文件
FAL:Fetch Archive Log

测试目的:由于网络问题发生了gap后,确定哪个进程负责处理gap。

测试环境:Oracle 11.2.0.2 on Linux 5.

测试过程:

1.确保当前主库和备库是同步的:
Primary:
MAX(SEQUENCE#)
--------------
           86

Standby:
MAX(SEQUENCE#)
--------------
           86

2. 模拟网络中断,导致gap:
在主库将网卡停掉: #ifconfig eth0 down

将主库执行数次switch logfile:
SQL>alter system switch logfile;
SQL>alter system switch logfile;
...

Primary:
MAX(SEQUENCE#)
--------------
           96

这时主库alert log报出了与备库连接不通的错误:
TNS-00513: Destination host unreachable
   nt secondary err code: 101
   nt OS err code: 0
Error 12543 received logging on to the standby
FAL[server, ARCp]: Error 12543 creating remote archivelog file 'STANDBY'
FAL[server, ARCp]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance orcl - Archival Error. Archiver continuing.



3.将主库的这些归档临时换个目录,保证这些归档无法传到备库:
mv *.arc ../

4. 启动主库的网卡:
#ifconfig eth0 up

5.这时,主库的ARC并没有把缺少的日志传到备库。最终备库的MRP发现了gap并把gap fetching.

备库alert log:
Thu Mar 29 19:58:49 2012
Media Recovery Waiting for thread 1 sequence 87 (in transit) <====  网络中断时,等待87
...
Thu Mar 29 20:08:45 2012
...
Media Recovery Waiting for thread 1 sequence 94
Thu Mar 29 20:11:01 2012
RFS[61]: Assigned to RFS process 13643
RFS[61]: Opened log for thread 1 sequence 97 dbid 1285401128 branch 757620395
Archived Log entry 80 added for thread 1 sequence 97 rlc 757620395 ID 0x4c9d8928 dest 2:
Thu Mar 29 20:11:02 2012
RFS[62]: Assigned to RFS process 13645
RFS[62]: Selected log 4 for thread 1 sequence 98 dbid 1285401128 branch 757620395
Thu Mar 29 20:11:02 2012
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 4 thread 1 sequence 98
Thu Mar 29 20:11:02 2012
Archived Log entry 81 added for thread 1 sequence 98 ID 0x4c9d8928 dest 1:
RFS[63]: Assigned to RFS process 13647
RFS[63]: Selected log 4 for thread 1 sequence 99 dbid 1285401128 branch 757620395
Thu Mar 29 20:11:05 2012
Fetching gap sequence in thread 1, gap sequence 94-96 <===========取gap
...

6.通过MRP的trace,可以确定是MRP 作了fetching gap:

MRP trace:

*** 2012-03-29 20:08:45.375 4265 krsh.c
Media Recovery Waiting for thread 1 sequence 94

*** 2012-03-29 20:11:05.543
*** 2012-03-29 20:11:05.543 4265 krsh.c
Fetching gap sequence in thread 1, gap sequence 94-96 <==========MRP取gap.
Redo shipping client performing standby login
*** 2012-03-29 20:11:05.593 4595 krsu.c
Logged on to standby successfully
Client logon and security negotiation successful!

7.将移走的归档日志移回之后,备库的RFS接收到了这些日志, MRP 将这些日志进行了apply.

Thu Mar 29 20:12:06 2012
RFS[64]: Assigned to RFS process 13649
RFS[64]: Opened log for thread 1 sequence 94 dbid 1285401128 branch 757620395
Archived Log entry 82 added for thread 1 sequence 94 rlc 757620395 ID 0x4c9d8928 dest 2:
Thu Mar 29 20:12:06 2012
RFS[65]: Assigned to RFS process 13651
RFS[65]: Opened log for thread 1 sequence 95 dbid 1285401128 branch 757620395
Thu Mar 29 20:12:06 2012
RFS[66]: Assigned to RFS process 13653
RFS[66]: Opened log for thread 1 sequence 96 dbid 1285401128 branch 757620395
Archived Log entry 83 added for thread 1 sequence 95 rlc 757620395 ID 0x4c9d8928 dest 2:
Archived Log entry 84 added for thread 1 sequence 96 rlc 757620395 ID 0x4c9d8928 dest 2:
Thu Mar 29 20:12:16 2012
Media Recovery Log /home/oracle/arch1/standby/1_94_757620395.arc
Media Recovery Log /home/oracle/arch1/standby/1_95_757620395.arc
Media Recovery Log /home/oracle/arch1/standby/1_96_757620395.arc
Media Recovery Log /home/oracle/arch1/standby/1_97_757620395.arc
Media Recovery Log /home/oracle/arch1/standby/1_98_757620395.arc


测试结论:
通过这个例子说明,对于这种gap的处理,主库的ARC进程对于以前产生的gap文件,并没有进行处理。是备库的MRP进程在apply log的时候发现了gap,将这些文件通过FAL进程取回。


注:在11g,理论上主库的ARC进程和备库的RFS、MRP进程在某些情况都有可能处理gap.


8. 为了进一步确定是MRP通过FAL取了gap文件,我将主库的密码修改了一下,结果MRP的trace中报错:FAL[client, MRP0],说明是通过FAL取的。

 *** 2012-03-29 21:18:15.964 4265 krsh.c
Error 1031 received logging on to the standby
*** 2012-03-29 21:18:15.964 4265 krsh.c
FAL[client, MRP0]: Error 1031 connecting to PRIMARY for fetching gap sequence

11g 新特性:Active Data Guard

  在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,是不可以打开的,只可以mount。从11g开始,在应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard 。通过Active Data Guard,可以在物理备库进行查询或者导出数据,从而减少对主库的访问和压力。

   Active Data Guard适用于一些只读性的应用,比如,有的应用程序只是查询数据,进行一些报表业务,不会产生redo数据,这些应用可以转移到备库上,避免对主库资源的争用。


 Oracle Active Data Guard 是Oracle Database Enterprise Edition的一个功能,需要额外付费来使用这个功能。

   如需启用Active Data Guard, 只需要将备库以 read-only 模式打开,而且执行 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE语句就可以。需要注意的是:主库和备库的COMPATIBLE 参数至少要设置为11.0.0。


   如下:在备库执行:  
   SQL>startup mount;
   SQL>alter database open read only;
   SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


  如果已经启用了Active Data Guard,备库的V$DATABASE会显示为"READ ONLY WITH APPLY':

      SQL> SELECT open_mode FROM V$DATABASE;
      OPEN_MODE
      --------------------
      READ ONLY WITH APPLY


  注意:使用Active
Data Guard
要求主库和备库的COMPATIBLE 参数至少设置为11.0.0


  为了保证备库数据的实时性,需要在备库启动real-time apply:
   SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

  下面的功能是允许在read-only的数据库上执行的:
    • Issue SELECT statements, including queries that require multiple sorts that leverage TEMP
segments
    • Use ALTER SESSION and ALTER SYSTEM statements
    • Use SET ROLE
    • Call stored procedures
    • Use database links (dblinks) to write to remote databases
    • Use stored procedures to call remote procedures via dblinks
    • Use SET TRANSACTION READ ONLY for transaction level read consistency
    • Issue complex queries (such as grouping SET queries and WITH CLAUSE queries)

  下面的功能是不允许在read-only的数据库上执行的:
    • Any DMLs (excluding simple SELECT statements) or DDLs
    • Query accessing local sequences
    • DMLs to local temporary tables

    比较典型的Active Data Guard 分为:
  • 单实例的物理主库和单实例的物理备库
  • 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例
  • RAC主库和RAC备库

    Oracle Data Guard 的配置方法,,请参考下面的文档:


   * 单实例的物理主库和单实例的物理备库:
     http://docs.oracle.com/cd/B28359_01/server.111/b28294/create_ps.htm

  * 主库为Oracle Real Application Clusters (Oracle RAC) ,备库为单实例:
     http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimarysingleinstance-131970.pdf

 * RAC 主库和RAC 备库:
   10g: http://www.oracle.com/technetwork/database/features/availability/maa-wp-10g-racprimaryracphysicalsta-131940.pdf
   11g: http://www.oracle.com/technetwork/database/features/availability/dataguard11g-rac-maa-1-134639.pdf

* 关于Active Data Guard的最佳实践经验,请参考文档:
    http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf


* 关于Oracle Maximum Availability Architecture Best Practices的更多文档,请参考:
   http://www.oracle.com/goto/maa


About

本博客由Oracle全球技术支持中国区的工程师维护。为中文用户提供数据库相关的技术支持信息,包括常用的诊断工具、诊断方法、产品新特性、案例分析等。此外,MOS也陆续推出各类中文内容:技术通讯统一发布在Note 1529795.1 中,中文文档列表更新在Note 1533057.1 中,网上讲座请查看MOS文档 1456176.1,在"Archived"中可以下载历史的录音和文档。

Search

Archives
« 四月 2014
星期日星期一星期二星期三星期四星期五星期六
  
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
今天