在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)