11g adg 环境搭建实施手册

 

################################

简介

################################

 

从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. 检查主库和备库数据库软件版本

      

       select * from v$version;

 

              BANNER

       ---------------------------------------------------------------------------

       Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

       PL/SQL Release 11.2.0.3.0 - Production

       CORE    11.2.0.3.0      Production

       TNS for Linux: Version 11.2.0.3.0 - Production

       NLSRTL Version 11.2.0.3.0 – Production

 

2. 检查操作系统平台是否兼容

      

select * from v$transportable_platform order by endian_format,platform_name;

 

PLATFORM_ID PLATFORM_NAME            ENDIAN_FORMAT

----------- ---------------------------------------- --------------

          6 AIX-Based Systems (64-bit)               Big

         16 Apple Mac OS                             Big

          3 HP-UX (64-bit)                           Big

          4 HP-UX IA (64-bit)                        Big

         18 IBM Power Based Linux                    Big

          9 IBM zSeries Based Linux                  Big

          1 Solaris[tm] OE (32-bit)                  Big

          2 Solaris[tm] OE (64-bit)                  Big

         21 Apple Mac OS (x86-64)                    Little

         19 HP IA Open VMS                           Little

         15 HP Open VMS                              Little

          5 HP Tru64 UNIX                            Little

         10 Linux IA (32-bit)                        Little

         11 Linux IA (64-bit)                        Little

         13 Linux x86 64-bit                         Little

          7 Microsoft Windows IA (32-bit)            Little

          8 Microsoft Windows IA (64-bit)            Little

         12 Microsoft Windows x86 64-bit             Little

         17 Solaris Operating System (x86)           Little

         20 Solaris Operating System (x86-64)        Little

          

3. 检查主库是否开启数据库归档

SQL> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            /u01/app/oracle/product/11.2.0/db_1/dbs/arch Oldest online log sequence     198 Current log sequence           200     【要求】  主库和备库必须是相同版本及位数并且为企业版（Enterprise Edition Release） 主库和备库所在的操作系统最好是相同平台，相同操作系统版本，必须是相同 ENDIAN_FORMAT 的平台 主库、备库必须开启归档模式

 

 

 

#####################################################################

 

• 安装前环境准备

 

1. 1. 打开主库force logging:

14:44:58 idle> ALTER DATABASE FORCE LOGGING; Database altered. Elapsed: 00:00:00.01 14:47:01 idle> select force_logging from v$database; FOR --- YES

 

1. 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

 

 

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

与参数audit_file_dest的路径一致

$ mkdir -p /u01/app/admin/orabak/adump

 

1. 5. 在备库创建init参数文件

$ORACLE_HOME/dbs/initorabak.ora

有了这个临时的init参数文件，我们就可以在duplicate命令运行前将备库的AUXILIARY实例启动到nomount状态，这也是运行duplicate命令的一个必要条件。

 

initorabak.ora文件只有一行信息：

DB_NAME=orcl

 

1. 6. 将密码文件从主库服务器拷贝到备库服务器

并且重命名：

$ mv orapworcl orapworabak

 

1. 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)    ))

 

1. 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)    ) )

 

 

1. 9. 在备库，执行duplicate命令

先启动AUXILIARY实例到 nomount 状态： $ export ORACLE_SID=orabak $ sqlplus / as sysdba SQL> startup nomount;

 

1. 10. 在备库测试与AUXILIARY实例和与主库的连接

如果连接成功，继续执行下面的步骤， 很多时候duplicate命令失败都是由于连接失败导致的。 $ sqlplus sys/oracle@orabak as sysdba $ sqlplus sys/oracle@orcl as sysdba

1. 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"="fpyjbak" SET FAL_SERVER="fpyj"  SET LOG_ARCHIVE_DEST_1='LOCATION= /home/oracle/arch_dir/fpyj                        VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE) DB_UNIQUE_NAME=fpyjbak' SET CONTROL_FILES ' /u01/app/oracle/oradata/fpyjbak/control01.ctl' set diagnostic_dest='/u01/app/oracle' set audit_file_dest=' /u01/app/oracle/admin/fpyjbak/adump' set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/fpyj', '/u01/app/oracle/oradata/fpyjbak' 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;

 

按照上面方法搭建的dataguard，应用日志 open read only后，alert中出现如下内容

 

Error 12154 received logging on to the standby

FAL[client, MRP0]: Error 12154 connecting to host for fetching gap sequence

 

 

原因分析

这是由于备库没有设置 fal_client参数原因造成

 

解决方法

设置备库的fal_client参数，例如本示例中 主库db_unique_name    orcl  备库 db_unique_name orabak

那么将备库的fal_client设置为orabak即可

Alter system set fal_client='orabak' scope=both;

 

 

主备库可用以下SQL检测log sequence是否一致：

select name,replace(database_role,' ','') as database_role,thread,seq from  v$database,(select max(sequence#) seq,THREAD# thread  from v$log_history  group by THREAD#);

 

1. 验证dataguard

 

 

验证日志是否从主库传输过来，最后一个栏位为yes表示日志已经传输过来

SELECT RESETLOGS_ID,THREAD#,SEQUENCE#,STATUS,ARCHIVED,APPLIED FROM V$ARCHIVED_LOG;

 

如果日志传输失败，请用以下命令查看主备库日志传输路径是否valid的

set linesize 160 col dest_name for a20 col error for a50 select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2'; DEST_NAME            STATUS             ERROR -------------------- ------------------ -------------------------------------------------- LOG_ARCHIVE_DEST_2   VALID

如果status为其他，则查看是什么原因导致无法归档到备库，调整完后用以下命令重启远程归档进程

alter system set log_archive_dest_state_2 = 'defer'; alter system set log_archive_dest_state_2 = 'enable';

 

备库查看lag 延时，正常所有lag应该接近0或者为0

 

col name for a30 col value for a30 col datum_time for a30 col TIME_COMPUTED for a20 set line 160 SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;       NAME                           VALUE                          DATUM_TIME                     TIME_COMPUTED ------------------------------ ------------------------------ ------------------------------ -------------------- transport lag                  +00 00:00:00                   11/16/2014 19:25:27            11/16/2014 19:25:32 apply lag                      +00 04:15:21                   11/16/2014 19:25:27            11/16/2014 19:25:32 apply finish time              +00 00:01:49.861                                              11/16/2014 19:25:32 estimated startup time         161                                                           11/16/2014 19:25:32

查看DataGuard message

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;   MESSAGE -------------------------------------------------------- ARC4: Completed archiving thread 1 sequence 164 (0-0) ARC5: Completed archiving thread 1 sequence 165 (0-0) ARC6: Beginning to archive thread 1 sequence 166 (2665439-2665478) ARC6: Completed archiving thread 1 sequence 166 (0-0) ARC7: Beginning to archive thread 1 sequence 167 (2665478-2665495) ARC7: Completed archiving thread 1 sequence 167 (0-0) ARC8: Beginning to archive thread 1 sequence 168 (2665495-2665790) ARC8: Completed archiving thread 1 sequence 168 (0-0) ARC9: Beginning to archive thread 1 sequence 169 (2665790-2665797) ARC9: Completed archiving thread 1 sequence 169 (0-0) ARC0: Beginning to archive thread 1 sequence 170 (2665797-2665809)

查看日志应用状态

select thread#,sequence#,applied from v$archived_log;

 

 

 

 

 

 

在备库开启实时日志应用

recover managed standby database using current logfile disconnect from session;

 

 

2）取消备库的自动恢复

sys@ora11gdg@> alter database recover managed standby database cancel;

Database altered.

 

 

恶心人的参数，注意如下：log_archive_config   必须是先主后备

 

 

主库：

19:59:32 SYS(125_11)@fpyj> show parameter log_archive_config;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(fpyj,fpyjbak)

 

 

备库：

 

20:00:19 SYS(125_9)@fpyjbak> show parameter log_archive_config;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(fpyj,fpyjbak)

 

备库检查日志的apply情况

SYS(125_9)@fpyjbak> select sequence#,applied from v$archived_log;