RMAN duplicate创建Dataguard standby数据库并使用QMonitor监控standby delay

    在Oracle 10g中，我们可以通过基于备份的rman DUPLICATE比较方便地实现Dataguard物理standby的搭建，首先对数据库进行备份并传送到standby所在主机，继而通过进行数据库的恢复来实现standby的搭建。
    而在Oracle 11g中，一个新特性令我们欣喜不已。Active database duplication 通过网络，直接copy target 库到auxiliary 库，然后创建复制库。 这种方法就不需要先用RMAN 备份数据库，然后将备份文件发送到auxiliary端。毕竟，对于比较大型的数据库来说，备份+传送+恢复消耗的时间远大于复制传送的时间。让我们来体验一下它有多方便吧


【在备库所在机器上创建与主库相同的目录】
主库文件目录：

[root@Qguard-1 /root/tree-1.6.0]
#./tree /opt/oracle/oradata
/opt/oracle/oradata
└── ADG
    ├── controlfile
    │   └── o1_mf_93fbzfnz_.ctl
    ├── datafile
    │   ├── o1_mf_sysaux_93fbzsjj_.dbf
    │   ├── o1_mf_system_93fbzkld_.dbf
    │   ├── o1_mf_temp_93fc03oy_.tmp
    │   ├── o1_mf_undotbs1_93fc000x_.dbf
    │   └── o1_mf_users_93fc0gd2_.dbf
    └── onlinelog
        ├── o1_mf_1_93fbzg3h_.log
        ├── o1_mf_2_93fbzg9b_.log
        ├── o1_mf_3_93fbzghz_.log
        ├── redosb01.log
        ├── redosb02.log
        └── redosb03.log

4 directories, 12 files

[root@Qguard-1 /root/tree-1.6.0]
#./tree /opt/oracle/fast_recovery_area/
/opt/oracle/fast_recovery_area/
└── ADG
    ├── autobackup
    │   ├── 2013_09_22
    │   │   ├── o1_mf_s_826819250_93x8xlop_.bkp
    │   │   ├── o1_mf_s_826822623_93xd6zpl_.bkp
    │   │   ├── o1_mf_s_826822732_93xdbf62_.bkp
    │   │   ├── o1_mf_s_826823246_93xdtghc_.bkp
    │   │   └── o1_mf_s_826823266_93xdv2gy_.bkp
    │   └── 2013_10_19
    │       └── o1_mf_s_829233970_964bolfc_.bkp
    ├── backupset
    │   ├── 2013_09_22
    │   │   ├── o1_mf_nnnd0_TAG20130922T165648_93xd6jdq_.bkp
    │   │   ├── o1_mf_nnnd0_TAG20130922T170710_93xdsz8h_.bkp
    │   │   ├── o1_mf_nnnd1_TAG20130922T165837_93xd9xxv_.bkp
    │   │   └── o1_mf_nnnd1_TAG20130922T170744_93xdv19z_.bkp
    │   └── 2013_10_19
    │       └── o1_mf_nnndf_TAG20131019T144544_964bnrxg_.bkp
    ├── controlfile
    │   └── o1_mf_93fbzfs8_.ctl
    └── onlinelog
        ├── o1_mf_1_93fbzg6x_.log
        ├── o1_mf_2_93fbzgfj_.log
        └── o1_mf_3_93fbzgmh_.log

9 directories, 15 files

主库文件目录：

[root@Qguard-2 /root/tree-1.6.0]
#./tree /opt/oracle/oradata
/opt/oracle/oradata
└── ADG
    ├── controlfile
    ├── datafile
    └── onlinelog

4 directories, 0 files

[root@Qguard-2 /root/tree-1.6.0]
#./tree /opt/oracle/fast_recovery_area/
/opt/oracle/fast_recovery_area/
└── ADG
    ├── controlfile
    └── onlinelog

3 directories, 0 files

【在备库配置监听】

oracle@Qguard-2:/opt/oracle/products/11.2.0/network/admin>cat listener.ora 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = adg)
      (ORACLE_HOME = /opt/oracle/products/11.2.0)
      (SID_NAME = adg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))
    )
  )

【主备库配置tnsnames.ora】

oracle@Qguard-2:/opt/oracle/products/11.2.0/network/admin>cat tnsnames.ora 
adg =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid= adg)
    )
  )

adgsb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))
    )
    (CONNECT_DATA =
      (sid= adg)
    )
  )

【备库启动监听】

oracle@Qguard-2:/opt/oracle/products/11.2.0/network/admin>lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 19-OCT-2013 15:48:48

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /opt/oracle/products/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /opt/oracle/products/11.2.0/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/Qguard-2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.202)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.202)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                19-OCT-2013 15:48:48
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/products/11.2.0/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/Qguard-2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.202)(PORT=1521)))
Services Summary...
Service "adg" has 1 instance(s).
  Instance "adg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

【备库启动到nomount状态】

oracle@Qguard-2:/opt/oracle/products/11.2.0/dbs>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 19 15:52:04 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  313196544 bytes
Fixed Size                  2227864 bytes
Variable Size             255852904 bytes
Database Buffers           50331648 bytes
Redo Buffers                4784128 bytes
SQL> 

【主库端】

oracle@Qguard-1:/opt/oracle/products/11.2.0/rdbms/admin>rman target sys/oracle auxiliary sys/oracle@adgsb

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Oct 19 16:08:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ADG (DBID=1384626989)
connected to auxiliary database: ADG (not mounted)

RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;

Starting Duplicate Db at 2013-10-19 16:09:12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=189 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/opt/oracle/products/11.2.0/dbs/orapwadg' auxiliary format 
 '/opt/oracle/products/11.2.0/dbs/orapwadg'   ;
}
executing Memory Script

Starting backup at 2013-10-19 16:09:13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
Finished backup at 2013-10-19 16:09:14

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl'', ''/opt/oracle/fast_recovery_area/ADG/controlfile/o1_mf_93fbzfs8_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl';
   restore clone controlfile to  '/opt/oracle/fast_recovery_area/ADG/controlfile/o1_mf_93fbzfs8_.ctl' from 
 '/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl';
   sql clone "alter system set  control_files = 
  ''/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl'', ''/opt/oracle/fast_recovery_area/ADG/controlfile/o1_mf_93fbzfs8_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl'', ''/opt/oracle/fast_recovery_area/ADG/controlfile/o1_mf_93fbzfs8_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 2013-10-19 16:09:14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/oracle/products/11.2.0/dbs/snapcf_adg.f tag=TAG20131019T160914 RECID=13 STAMP=829238955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2013-10-19 16:09:16

Starting restore at 2013-10-19 16:09:16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 2013-10-19 16:09:17

sql statement: alter system set  control_files =   ''/opt/oracle/oradata/ADG/controlfile/o1_mf_93fbzfnz_.ctl'', ''/opt/oracle/fast_recovery_area/ADG/controlfile/o1_mf_93fbzfs8_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    2471931904 bytes

Fixed Size                     2230872 bytes
Variable Size                570426792 bytes
Database Buffers            1879048192 bytes
Redo Buffers                  20226048 bytes

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 clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /opt/oracle/oradata/ADG/datafile/o1_mf_temp_%u_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 2013-10-19 16:09:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/opt/oracle/oradata/ADG/datafile/o1_mf_undotbs1_93fc000x_.dbf
output file name=/opt/oracle/oradata/ADG/datafile/o1_mf_undotbs1_1domqbls_.dbf tag=TAG20131019T160932
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/opt/oracle/oradata/ADG/datafile/o1_mf_system_93fbzkld_.dbf
output file name=/opt/oracle/oradata/ADG/datafile/o1_mf_system_1eomqbml_.dbf tag=TAG20131019T160932
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/opt/oracle/oradata/ADG/datafile/o1_mf_sysaux_93fbzsjj_.dbf
output file name=/opt/oracle/oradata/ADG/datafile/o1_mf_sysaux_1fomqbn4_.dbf tag=TAG20131019T160932
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/opt/oracle/oradata/ADG/datafile/o1_mf_users_93fc0gd2_.dbf
output file name=/opt/oracle/oradata/ADG/datafile/o1_mf_users_1gomqbnj_.dbf tag=TAG20131019T160932
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 2013-10-19 16:10:34

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/opt/oracle/archive/1_148_826297069.dbf" auxiliary format 
 "/opt/oracle/archive/1_148_826297069.dbf"   ;
   catalog clone archivelog  "/opt/oracle/archive/1_148_826297069.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 2013-10-19 16:10:35
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=148 RECID=133 STAMP=829239035
output file name=/opt/oracle/archive/1_148_826297069.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 2013-10-19 16:10:36

cataloged archived log
archived log file name=/opt/oracle/archive/1_148_826297069.dbf RECID=1 STAMP=829239101

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=829239101 file name=/opt/oracle/oradata/ADG/datafile/o1_mf_system_1eomqbml_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=829239101 file name=/opt/oracle/oradata/ADG/datafile/o1_mf_sysaux_1fomqbn4_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=829239101 file name=/opt/oracle/oradata/ADG/datafile/o1_mf_undotbs1_1domqbls_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=829239101 file name=/opt/oracle/oradata/ADG/datafile/o1_mf_users_1gomqbnj_.dbf

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

executing command: SET until clause

Starting recover at 2013-10-19 16:10:37
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=127 device type=DISK

starting media recovery

archived log for thread 1 with sequence 148 is already on disk as file /opt/oracle/archive/1_148_826297069.dbf
archived log file name=/opt/oracle/archive/1_148_826297069.dbf thread=1 sequence=148
media recovery complete, elapsed time: 00:00:00
Finished recover at 2013-10-19 16:10:38
Finished Duplicate Db at 2013-10-19 16:10:49

【备库端】

SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> select open_mode,DATABASE_ROLE from v$database;

OPEN_MODE                                DATABASE_ROLE
---------------------------------------- --------------------------------
READ ONLY WITH APPLY                     PHYSICAL STANDBY

DataGuard搭建完毕！

通过QMonitor监控standby delay：

对于standby的延迟计算，以前采用过一种方法，就是通过v$datafile_header. checkpoint_time与系统时间比较，获取这个差异，这个方法会夸大实际的延迟，因为checkpoint time的更新还是比较缓慢的，受db cache脏页的影响较大，从11G的real time query也可以发现，延迟远没有checkpoint_time这么大，这里介绍下我们QMonitor下使用的计算方法：

第一步：获取standby数据库的当前scn

 SQL> select current_scn from v$database;
 CURRENT_SCN
-----------
     827796

第二步：获取primary数据库的当前scn

SQL> select current_scn from v$database;
 CURRENT_SCN
-----------
     833869

第三步：如果两个数字相等，则延迟为0s，否则继续第四步；

第四步：在主库上使用如下语句查询standby的scn对应的时间。

SQL> SELECT TO_CHAR(scn_to_timestamp('827796'),'YYYY-MM-DD HH24:MI:SS') FROM dual;
 TO_CHAR(SCN_TO_TIMESTAMP('827796'),'YY
--------------------------------------
2013-09-17 14:08:04

第五步：在主库上计算主库SCN的对应的时间。

SQL> SELECT TO_CHAR(scn_to_timestamp('833869'),'YYYY-MM-DD HH24:MI:SS') FROM dual;
 TO_CHAR(SCN_TO_TIMESTAMP('833869'),'YY
--------------------------------------
2013-09-17 15:22:56

第六步：计算第四步计算出来的时间和第五步计算出来的时间差，即为standby的延迟，单位为秒。

监控结果：

16:28分在主库执行切换日志操作：

SQL> alter system switch logfile;

System altered.

备库alert日志：

Sat Oct 19 16:28:46 2013
Media Recovery Log /opt/oracle/archive/1_150_826297069.dbf
Media Recovery Waiting for thread 1 sequence 151 (in transit)