最简单的11g Active DataGuard（ADG）搭建配置过程（项目步骤）

一、环境介绍:
我在db01和db02两台Linux虚拟机上首先分别安装了一套数据库软件，在db01主机上创建了名为woo的数据库；我们这次的实验是要搭建了一套Oracle 11g Active DataGuard；目的是为了实现数据库同步的功能，并且了解Oracle 11g DG的基本功能。db01:192.168.1.50
db02:192.168.1.51Oracle 11g Active DataGuard初探 http://www.linuxidc.com/Linux/2015-06/118359.htmOracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htmOracle Data Guard （RAC+DG）归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm二、11g ADG部署：1、pri端和sty端配置静态监听[oracle@sty admin]$ cat listener.ora

# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = （SID_LIST = （SID_DESC = （SID_NAME = PLSExtProc）（ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1）（PROGRAM = extproc））（SID_DESC = （SID_NAME = Woo ）（ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1））） [oracle@sty admin]$cat tnsname.ora# tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora# Generated by Oracle configuration tools.STY = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.51）（PORT = 1521）））（CONNECT_DATA = （SERVICE_NAME = woo）））PRI = （DESCRIPTION = （ADDRESS_LIST = （ADDRESS = （PROTOCOL = TCP）（HOST = 192.168.1.50）（PORT = 1521）））（CONNECT_DATA = （SERVICE_NAME = woo）））2、修改primary端初始化参数文件startup mount;

alter database archivelog;alter database force logging;alter database open;alter system set log_archive_config = "DG_CONFIG=（pri,sty）" scope=spfile;alter system set log_archive_dest_1 = "LOCATION=/DBBackup/Archive VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=pri" scope=spfile;alter system set log_archive_dest_2 = "SERVICE=sty LGWR SYNC VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=sty" scope=spfile;alter system set log_archive_dest_state_1 = ENABLE;alter system set log_archive_dest_state_2 = ENABLE;alter system set fal_server=sty scope=spfile;alter system set fal_client=pri scope=spfile;alter system set standby_file_management=AUTO scope=spfile;3、在primary端pfile参数文件和密码文件，并且拷贝到standby段相应位置SQL> create pfile from spfile;
File created.[oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbsoracle@192.168.1.51"s password:
initwoo.ora 100% 1260 1.2KB/s 00:00
orapwwoo 100% 1536 1.5KB/s 00:00 [oracle@db01 oracle]$ scp -r admin/ diag/ fast_recovery_area/ oradata/ 192.168.1.51:$ORACLE_BASE
oracle@192.168.1.51"s password:
init.ora.512201522543 100% 1778 1.7KB/s 00:01
dp.log 100% 116 0.1KB/s 00:00
........ 4、修改standby端的监听文件及初始化参数文件--修改监听文件

[oracle@db02 ~]$ cd $ORACLE_HOME/network/admin[oracle@db02 admin]$ vi listener.ora# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = （DESCRIPTION_LIST = （DESCRIPTION = （ADDRESS = （PROTOCOL = IPC）（KEY = EXTPROC1521））（ADDRESS = （PROTOCOL = TCP）（HOST = db02）（PORT = 1521））））SID_LIST_LISTENER = （SID_LIST = （SID_DESC = （SID_NAME = PLSExtProc）（ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1）（PROGRAM = extproc））（SID_DESC = （GLOBAL_DBNAME = woo）（ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1）（SID_NAME = woo）））ADR_BASE_LISTENER = /DBSoft/oracle--启动监听[oracle@db02 dbs]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:29:57Copyright （c） 1991, 2013, Oracle. All rights reserved.Starting /DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionSystem parameter file is /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.oraLog messages written to /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xmlListening on: （DESCRIPTION=（ADDRESS=（PROTOCOL=ipc）（KEY=EXTPROC1521）））Listening on: （DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=db02）（PORT=1521）））Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=IPC）（KEY=EXTPROC1521）））STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 17-JUN-2015 21:29:57Uptime 0 days 0 hr. 0 min. 1 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.oraListener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xmlListening Endpoints Summary... （DESCRIPTION=（ADDRESS=（PROTOCOL=ipc）（KEY=EXTPROC1521）））（DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=db02）（PORT=1521）））Services Summary...Service "PLSExtProc" has 1 instance（s）. Instance "PLSExtProc", status UNKNOWN, has 1 handler（s） for this service...Service "woo" has 1 instance（s）. Instance "woo", status UNKNOWN, has 1 handler（s） for this service...The command completed successfully--查看监听状态[oracle@db02 dbs]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:30:02Copyright （c） 1991, 2013, Oracle. All rights reserved.Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=IPC）（KEY=EXTPROC1521）））STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 17-JUN-2015 21:29:57Uptime 0 days 0 hr. 0 min. 4 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.oraListener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xmlListening Endpoints Summary... （DESCRIPTION=（ADDRESS=（PROTOCOL=ipc）（KEY=EXTPROC1521）））（DESCRIPTION=（ADDRESS=（PROTOCOL=tcp）（HOST=db02）（PORT=1521）））Services Summary...Service "PLSExtProc" has 1 instance（s）. Instance "PLSExtProc", status UNKNOWN, has 1 handler（s） for this service...Service "woo" has 1 instance（s）. Instance "woo", status UNKNOWN, has 1 handler（s） for this service...The command completed successfully[oracle@db02 dbs]$--修改参数文件[oracle@db02 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015Copyright （c） 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL> startup nomount;ORACLE instance started.Total System Global Area 1188511744 bytesFixed Size 1364228 bytesVariable Size 754978556 bytesDatabase Buffers 419430400 bytesRedo Buffers 12738560 bytesSQL> create spfile from pfile="/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora";File created.SQL>SQL> shutdown abort;ORACLE instance shut down.SQL> startup nomount;ORACLE instance started.Total System Global Area 1188511744 bytesFixed Size 1364228 bytesVariable Size 754978556 bytesDatabase Buffers 419430400 bytesRedo Buffers 12738560 bytesSQL>alter system set db_unique_name=sty scope=spfile;alter system set log_archive_config="DG_CONFIG=（pri,dg）" scope=spfile;alter system set log_archive_dest_1 ="LOCATION=/DBBackup/Archive VALID_FOR=（ALL_LOGFILES,ALL_ROLES） DB_UNIQUE_NAME=sty" scope=spfile;alter system set log_archive_dest_2 ="SERVICE=pri LGWR SYNC VALID_FOR=（ONLINE_LOGFILES,PRIMARY_ROLE） DB_UNIQUE_NAME=pri" scope=spfile;alter system set fal_server=pri scope=spfile;alter system set fal_client=sty scope=spfile;SQL> shutdown abort;ORACLE instance shut down.SQL> startup nomountORACLE instance started.Total System Global Area 1188511744 bytesFixed Size 1364228 bytesVariable Size 754978556 bytesDatabase Buffers 419430400 bytesRedo Buffers 12738560 bytesSQL>SQL> 5、在primary端通过Rman Duplicate创建备库,在db01上执行如下命令rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
duplicate target database for standby from active database nofilenamecheck; [oracle@db01 ~]$ rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 17 22:33:54 2015 Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: WOO （DBID=4221729487）
using target database control file instead of recovery catalog
connected to auxiliary database: WOO （not mounted） RMAN> RMAN> duplicate target database for standby from active database nofilenamecheck; Starting Duplicate Db at 17-JUN-15
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 "/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo" auxiliary format
"/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo" ;
}
executing Memory Script Starting backup at 17-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Finished backup at 17-JUN-15 contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format "/DBSoft/oracle/oradata/woo/control01.ctl";
restore clone controlfile to "/DBSoft/oracle/fast_recovery_area/woo/control02.ctl" from
"/DBSoft/oracle/oradata/woo/control01.ctl";
}
executing Memory Script Starting backup at 17-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150617T223502 RECID=1 STAMP=882657308
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 17-JUN-15 Starting restore at 17-JUN-15
using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-JUN-15 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
"/DBSoft/oracle/oradata/woo/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/DBSoft/oracle/oradata/woo/system01.dbf";
set newname for datafile 2 to
"/DBSoft/oracle/oradata/woo/sysaux01.dbf";
set newname for datafile 3 to
"/DBSoft/oracle/oradata/woo/undotbs01.dbf";
set newname for datafile 4 to
"/DBSoft/oracle/oradata/woo/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/DBSoft/oracle/oradata/woo/system01.dbf" datafile
2 auxiliary format
"/DBSoft/oracle/oradata/woo/sysaux01.dbf" datafile
3 auxiliary format
"/DBSoft/oracle/oradata/woo/undotbs01.dbf" datafile
4 auxiliary format
"/DBSoft/oracle/oradata/woo/users01.dbf" ;
sql "alter system archive log current";
}
executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 17-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:58
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:30
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 17-JUN-15 sql statement: alter system archive log current contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/users01.dbf
Finished Duplicate Db at 17-JUN-15#至此已经恢复完成6、在primary 和standby端添加standby日志SQL> alter database add standby logfile

group 4 （"/DBSoft/oracle/oradata/woo/styredo04.log"） size 50m,group 5 （"/DBSoft/oracle/oradata/woo/styredo05.log"） size 50m,group 6 （"/DBSoft/oracle/oradata/woo/styredo06.log"） size 50m,group 7 （"/DBSoft/oracle/oradata/woo/styredo07.log"） size 50m;SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; GROUP# THREAD# SEQUENCE# ARC STATUS---------- ---------- ---------- --- ---------- 4 0 0 YES UNASSIGNED 5 0 0 YES UNASSIGNED 6 0 0 YES UNASSIGNED 7 0 0 YES UNASSIGNED7、在standby端开启实时日志应用SQL> recover managed standby database using current logfile disconnect from session;

Media recovery complete.SQL>

Linux的Docker上如何安装MySQL添加ASM磁盘报错ORA-02097和ORA-15014相关资讯 DataGuard Active Dataguard

DataGuard如何传送Redo到Standby （05月04日）
RAC环境下DataGuard到单机（04月08日）
Oracle 11G R2 DataGuard日常维护（02月20日）

MRP0- Background Media Recovery （04月16日）
DataGuard环境中的密码维护（03月17日）
Oracle 11G R2利用RMAN搭建（02月20日）

本文评论查看全部评论（0）