Oracle11gR2下搭建DataGuard主备同步详解
一 前言：
本文主要流程基于blog.itpub.net上博友oracle_zsx所著文章：《手把手教你安装Data Guard》，并稍作修改和添加细节说明，并且文章参考了大量网上资料和文章整合而成，十分感谢他们的作者，而不足之处还望大家斧正，转载请注明出处，谢谢！
PS：以下正文中以“//“开头的为注释行
二 环境基本信息：
主库信息：
操作系统版本：CentOS release 6.5 (Final)
操作系统内核版本：2.6.32-431.el6.x86_64
Oracle版本信息：
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
IP地址：172.16.3.89
主机名：wmcrent
oracle_sid：wmccustomer
db_name：wmccusto
db_unique_name：wmccustomer
service_name：wmccustomer
global_name：wmccustomer
监听名，端口：listener，1521
备库信息：
操作系统版本：CentOS release 6.5 (Final)
操作系统内核版本：2.6.32-431.el6.x86_64
Oracle版本信息：
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
IP地址：172.16.3.209
主机名：wmc-db
oracle_sid：wmccustomer
db_name：wmccusto
db_unique_name：wmccustomerdg1
service_name：wmccustomerdg1
global_name：wmccustomerdg1
监听名，端口：listener，1521
三 配置部分：
PS:本文所搭建的为物理standby
1 由于使用了主机名所以需要在主库和从库服务器上设置好dns解析，修改/etc/hosts文件，添加以下两行即可：
172.16.3.89 wmcrent
172.16.3.209 wmc-db
2 主库:
步骤（2.1）
[oracle@wmcrent ~]$lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-MAY-2016 09:52:59
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 05-MAY-2016 14:35:27
Uptime 3 days 19 hr. 17 min. 32 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/ora11g/product/11.2.0.2.0/db_1/network/admin/listener.ora
Listener Log File /opt/ora11g/diag/tnslsnr/wmcrent/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wmcrent)(PORT=1521)))
Services Summary...
Service "wmccustomer" has 2 instance(s).
Instance "wmccustomer", status UNKNOWN, has 1 handler(s) for this service...
Instance "wmccustomer", status READY, has 1 handler(s) for this service...
Service "wmccustomerXDB" has 1 instance(s).
Instance "wmccustomer", status READY, has 1 handler(s) for this service...
The command completed successfully
//确定数据库监听处于开启状态，上述信息中状态为UNKNOWN的为静态监听，而READY为动态监听
步骤（2.2）
SQL>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/szscpdb
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
或者（字典视图法）
SQL>select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
WMCCUSTO ARCHIVELOG
//确认主库处于归档模式，启用DataGuard同步服务要求数据库必需处在归档模式下，以上是两种方法，至于如何在归档和非归档模式下切换可以参考这里
步骤（2.3）
SQL>alter database force logging;
Database altered.
//更改主库为force logging 强制记录日志模式，配置DataGuard要求必须处在该日志记录模式下，该模式详细解析可参考这里
步骤（2.4）
[oracle@wmcrent ~]$ cd $ORACLE_HOME/dbs/
[oracle@wmcrent dbs]$orapwd file=$ORACLE_HOME/dbs/orapwwmccustomer password=syspassword entries=30;
//创建主库的密码文件为orapwwmccustomer，文件命名规则默认为orapw+[实例名]，该文件记录了该实例下所有用户的密码，entries=30意为可存放30个允许以SYSDBA或者SYSOPER权限登陆的用户，此外也可以直接拷贝现有密码文件到备库对应目录下，关于工具orapwd和密码文件详解可参考这里
步骤（2.5）
[oracle@wmcrent ~]$vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/ora11g/product/11.2.0.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = wmccustomer)
(SID_NAME = wmccustomer)
(ORACLE_HOME = /opt/ora11g/product/11.2.0.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wmcrent)(PORT = 1521))
)
)ADR_BASE_LISTENER = /opt/ora11g
//修改主库的监听文件listener.ora，确保存在静态监听模块SID_LIST_LISTENER，模块里的参数根据实际修改
步骤（2.6）
[oracle@wmcrent ~]$vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/ora11g/product/11.2.0.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
WMCCUSTOMER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wmcrent)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wmccustomer)
)
)
WMCCUSTOMERDG1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = wmc-db)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = wmccustomerdg1)
)
)
//修改主库的oracle网络服务名配置文件tnsname.ora
步骤（2.7）
[oracle@wmcrent ~]$export ORACLE_SID=wmccustomer
[oracle@wmcrent ~]$sqlplus / as sysdba
SQL>create pfile from spfile;
SQL>quit
[oracle@wmcrent ~]$vi $ORACLE_HOME/dbs/initwmccustomer.ora
wmccustomer.__db_cache_size=3690987520
wmccustomer.__java_pool_size=134217728
wmccustomer.__large_pool_size=67108864
wmccustomer.__oracle_base='/opt/ora11g'#ORACLE_BASE set from environment
wmccustomer.__pga_aggregate_target=4697620480
wmccustomer.__sga_target=8724152320
wmccustomer.__shared_io_pool_size=0
wmccustomer.__shared_pool_size=4630511616
wmccustomer.__streams_pool_size=134217728
*.audit_file_dest='/opt/ora11g/admin/wmccustomer/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/ora11g/oradata/wmccustomer/control01.ctl','/opt/ora11g/flash_recovery_area/wmccustomer/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/opt/ora11g/oradata/wmccustomerdg1/','/opt/ora11g/oradata/wmccustomer/'
*.db_name='wmccusto'
*.db_recovery_file_dest='/opt/ora11g/flash_recovery_area'
*.db_recovery_file_dest_size=53687091200
*.DB_UNIQUE_NAME='wmccustomer'
*.diagnostic_dest='/opt/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wmccustomerXDB)'
*.fal_client='WMCCUSTOMER'
*.fal_server='WMCCUSTOMERDG1'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(wmccustomer,wmccustomerdg1)'
*.log_archive_dest_1='LOCATION=/opt/ora11g/flash_recovery_area/WMCCUSTOMER/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wmccustomer'
*.log_archive_dest_2='SERVICE=WMCCUSTOMERDG1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wmccustomerdg1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=6
*.log_file_name_convert='/opt/ora11g/oradata/wmccustomerdg1/','/opt/ora11g/oradata/wmccustomer/'
*.memory_max_target=20971520000
*.memory_target=20971520000
*.open_cursors=300
*.open_links=8
*.open_links_per_instance=8
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=665
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/logmnr'
//从当前生效配置文件spfile生成可修改的主库参数文件pfile，pfile的命名规则是init+[实例名]，上述条目中标注红色的参数为需要修改的关键参数，以下为说明：
//db_file_name_convert='dir1','dir2'意为当前主库发生主备角色切换时，原备库目录dir1下的数据文件会转换并生成到原主库的dir2目录下
//DB_UNIQUE_NAME='wmccustomer'意为当前实例的独有名称为wmccustomer，该参数用来区分不同的数据库，因为主库和备库实例名ORACLE_SID可以相同
//fal_client='WMCCUSTOMER'为当需要进行主备切换时充当新备库的service_name（可在SQL交互模式下用show parameter service_name查看）：WMCCUSTOMER，即切换前的主库的service_name，即client客户端
//fal_server='WMCCUSTOMERDG1'为当需要进行主备切换时充当新主库的service_name：WMCCUSTOMERDG1，即切换前的从库的service_name，即server服务端
//log_archive_config='DG_CONFIG=(wmccustomer,wmccustomerdg1)'为当前需要配置DataGuard服务的两个实例的DB_UNIQUE_NAME，主库wmccustomer，备库wmccustomerdg1
//log_archive_dest_1='LOCATION=/opt/ora11g/flash_recovery_area/WMCCUSTOMER/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wmccustomer'为本地归档生成日志目录，LGWR意为之传输redo日志，VALID_FOR=（ALL_LOGFILES，ALL_ROLES）定义所有类型的日志文件和不论主库备库角色一律传递归档到此目录，最后一个选项DB_UNIQUE_NAME=wmccustomer意为针对数据库专有名为wmccustomer使用本地日志目录/opt/ora11g/flash_recovery_area/WMCCUSTOMER/archivelog
//log_archive_dest_2='SERVICE=WMCCUSTOMERDG1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wmccustomerdg1'为远程归档日志生成路径，对应远程数据库的tnsname.ora中每个模块开头第一个名称：对应实例的网络服务名即WMCCUSTOMERDG1，ASYNC意为对远程目录归档日志采用启用异步传输，VALID_FOR=（ONLINE_LOGFILES，PRIMARY_ROLE）定义当前数据库角色为主库时传递归档联机重做日志到此目录。
//log_archive_dest_state_n='ENABLE'启用对应上述条目的单个归档目录，n为对应编号
//log_file_name_convert='dir1','dir2'意为当前主库发生主备橘色切换时，原备库目录dir1下的日志文件会转换并生成到原主库的dir2目录下
//standby_file_management='AUTO'该参数只在当前库为备库时生效，提前设置以便需要进行主备角色切换时使用。建议将其值设置为AUTO，这样当主库添加或删除数据文件时，会自动在备库上完成相应的更改。
//关于参数文件pfile更多的说明可参考这里
步骤（2.8）
[oracle@wmcrent ~]$scp ${ORACLE_HOME}/dbs/initwmccustomer.ora ${ORACLE_HOME}/dbs/orapwwmccustomer wmc-db:$ORACLE_HOME/dbs/
//拷贝主库的参数文件initwmccustomer.ora和密码文件orapwwmccustomer到备库上
3 备库：
步骤（3.1）
[root@wmc-db ~]#su - oracle
[oracle@wmc-db ~]$vi $ORACLE_HOME/dbs/initwmccustomer.ora
wmccustomer.__db_cache_size=3556769792
wmccustomer.__java_pool_size=134217728
wmccustomer.__large_pool_size=67108864
wmccustomer.__oracle_base='/opt/ora11g'#ORACLE_BASE set from environment
wmccustomer.__pga_aggregate_target=4697620480
wmccustomer.__sga_target=8724152320
wmccustomer.__shared_io_pool_size=0
wmccustomer.__shared_pool_size=4764729344
wmccustomer.__streams_pool_size=134217728
*.audit_file_dest='/opt/ora11g/admin/wmccustomerdg1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/ora11g/oradata/wmccustomerdg1/control01.ctl','/opt/ora11g/flash_recovery_area/wmccustomerdg1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/opt/ora11g/oradata/wmccustomer/','/opt/ora11g/oradata/wmccustomerdg1/'
*.db_name='wmccusto'
*.db_recovery_file_dest='/opt/ora11g/flash_recovery_area'
*.db_recovery_file_dest_size=53687091200
*.DB_UNIQUE_NAME='wmccustomerdg1'
*.diagnostic_dest='/opt/ora11g'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=wmccustomerXDB)'
*.fal_client='WMCCUSTOMERDG1'
*.fal_server='WMCCUSTOMER'
*.global_names=FALSE
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(wmccustomer,wmccustomerdg1)'
*.log_archive_dest_1='LOCATION=/opt/ora11g/flash_recovery_area/WMCCUSTOMERDG1/archivelog LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wmccustomerdg1'
*.log_archive_dest_2='SERVICE=WMCCUSTOMER ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=wmccustomer'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=6
*.log_file_name_convert='/opt/ora11g/oradata/wmccustomer/','/opt/ora11g/oradata/wmccustomerdg1/'
*.memory_target=13401849856
*.open_cursors=300
*.open_links=8
*.open_links_per_instance=8
*.processes=600
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=665
*.standby_file_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='/logmnr'
//修改上一步中从主库拷贝过来的作为备库的参数文件，与主库类似，重点修改红色字体标示的参数项，关键参数项注意根据备库实际情况修改，还有就是与主备切换过程中相关的几个参数，如db_file_name_convert，log_file_name_convert，fal_client，fal_server等。其他参数可按需修改，各项参数作用参考主库处配置注释，此处不再赘述
步骤（3.2）
[oracle@wmc-db dbs]$mkdir -p /opt/ora11g/admin/wmccustomerdg1/adump
//根据上一步中参数文件initwmccustomer.ora中罗列的参数audit_file_dest去创建备库中实际所缺少的物理目录，下同
[oracle@wmc-db dbs]$mkdir -p /opt/ora11g/oradata/wmccustomerdg1
[oracle@wmc-db dbs]$mkdir -p /opt/ora11g/flash_recovery_area/wmccustomerdg1
//根据control_files参数项创建存放控制文件目录
[oracle@wmc-db dbs]$exit
[root@wmc-db ~]#mkdir -p /opt/oraclebackup/{controlfile,datafile}
[root@wmc-db ~]#chown -R oracle:oinstall /opt/oraclebackup/{controlfile,datafile}
//创建一个存放rman所备份的数据库文件的目录，controlfile存放控制文件备份，datafile存放数据文件（表空间等等）
[root@wmc-db ~]#su - oracle
[oracle@wmc-db ~]$mkdir -p ${ORACLE_BASE}/flash_recovery_area/WMCCUSTOMERDG1/archivelog
//创建实例存放归档日志的目录，同时也接收主库传递过来的归档日志
4，主库：
步骤（4.1）
[root@wmcrent ~]#mkdir -p /opt/oraclebackup/{controlfile,datafile}
[root@wmcrent ~]#chown -R oracle:oinstall /opt/oraclebackup/{controlfile,datafile}
//主库上创建一个备份目录用来做rman全备存放路径
[root@wmcrent ~]#su - oracle
[oracle@wmcrent ~]$export ORACLE_SID=wmccustomer
[oracle@wmcrent ~]$rman target /
RMAN>configure controlfile autobackup on;
RMAN>configure controlfile autobackup format for device type disk to '/opt/oraclebackup/controlfile/%F'
//将RMAN的控制文件自动备份功能开关打开并将其备份目录设置为/opt/oraclebackup/controlfile/，%F为特定格式，关于RMAN中的选项设置可以用show all;查看
RMAN>backup device type disk format '/opt/oraclebackup/datafile/%U' database plus archivelog;
RMAN>quit
[oracle@wmcrent ~]$scp /opt/oraclebackup/datafile/* wmc-db:/opt/oraclebackup/datafile/
[oracle@wmcrent ~]$scp /opt/oraclebackup/controlfile/* wmc-db:/opt/oraclebackup/controlfile/
//使用rman全库备份该实例并传输相关备份文件到备库目录/opt/oraclebackup/datafile下
5，备库：
步骤（5.1）
[oracle@wmc-db ~]$export ORACLE_SID=wmccustomer
[oracle@wmc-db ~]$sqlplus / as sysdba
SQL>startup nomount;
SQL>quit
//读取刚从主库传递过来的参数文件$ORACLE_HOME/dbs/initwmccustomer.ora，即pfile并把备库启动到非挂载状态，使用export定义环境变量ORACLE_SID确保启动的实例是wmccustomer
步骤（5.2）
[oracle@wmc-db ~]$vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /opt/ora11g/product/11.2.0.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = wmccustomerdg1)
(SID_NAME = wmccustomer)
(ORACLE_HOME = /opt/ora11g/product/11.2.0.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = wmc-db)(PORT = 1521))
)
)ADR_BASE_LISTENER = /opt/ora11g
//修改备库的监听文件，添加静态监听SID_LIST_LISTENER模块，参考主库监听文件修改相应参数即可
步骤（5.3）
[oracle@wmc-db ~]$vi $ORACLE_HOME/network/admin/tnsnames.ora
//修改备库网络服务名文件$ORACLE_HOME/network/admin/tnsnames.ora，可在备库原有配置基础上根据主库配置文件增加新的配置，以便不影响原始配置。
步骤（5.4）
[oracle@wmc-db ~]$lsnrctl start
[oracle@wmc-db ~]$lsnrctl status
或
[oracle@wmc-db ~]$lsnrctl reload
[oracle@wmc-db ~]$lsnrctl status
//启动监听服务，确保服务名注册无误，或者在监听服务已启动的情况下重新加载reload已变更的设置，并使用status命令查看监听状态信息
步骤（5.5）
[oracle@wmc-db ~]$tnsping wmccustomer
[oracle@wmc-db ~]$tnsping wmccustomerdg1
//在备库上测试一下刚在$ORACLE_HOME/network/admin/tnsnames.ora文件配置的网络服务名tnsname看看是否连接畅通
6，主库：
步骤（6.1）
[oracle@wmcrent ~]$tnsping wmccustomer
[oracle@wmcrent ~]$tnsping wmccustomerdg1
//同样在主库上测试一下网络服务名确认畅通
步骤（6.2）
[oracle@wmcrent ~]$export ORACLE_SID=wmccustomer
[oracle@wmcrent ~]$rman target /
RMAN>connect auxiliary sys/syspassword@wmccustomerdg1
connected to auxiliary database: wmccustomerdg1 (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 12-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script.:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 12-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/oraclebackup/controlfile/6ar5baag_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/oraclebackup/controlfile/6ar5baag_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/opt/ora11g/oradata/wmccustomerdg1/control01.ctl
output file name=/opt/ora11g/oradata/wmccustomerdg1/control02.ctl
Finished restore at 12-MAY-16
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
"/opt/ora11g/oradata/wmccustomerdg1/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/opt/ora11g/oradata/wmccustomerdg1/system01.dbf";
set newname for datafile 2 to
"/opt/ora11g/oradata/wmccustomerdg1/sysaux01.dbf";
set newname for datafile 3 to
"/opt/ora11g/oradata/wmccustomerdg1/undotbs01.dbf";
set newname for datafile 4 to
"/opt/ora11g/oradata/wmccustomerdg1/users01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /opt/ora11g/oradata/wmccustomerdg1/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-MAY-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/ora11g/oradata/wmccustomerdg1/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/ora11g/oradata/wmccustomerdg1/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/ora11g/oradata/wmccustomerdg1/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/ora11g/oradata/wmccustomerdg1/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/oraclebackup/datafile/66r5b9v7_1_1
channel ORA_AUX_DISK_1: piece handle=/opt/oraclebackup/datafile/66r5b9v7_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 12-MAY-16
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=809491977 file name=/opt/ora11g/oradata/wmccustomerdg1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=809491977 file name=/opt/ora11g/oradata/wmccustomerdg1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=809491977 file name=/opt/ora11g/oradata/wmccustomerdg1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=809491977 file name=/opt/ora11g/oradata/wmccustomerdg1/users01.dbf
Finished Duplicate Db at 12-MAY-16
（省略部分过程信息）
//在主库上应用备份文件到备库上，使用duplicate方式（切记在主库上执行）
7，备库：
步骤（7.1）
[oracle@wmc-db ~]$export ORACLE_SID=wmccustomer
[oracle@wmc-db ~]$sqlplus / as sysdba
SQL>select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------- ----------------
wmccustomer MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
//执行备库恢复模式
步骤（7.2）
SQL>SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
------------------- -------------- --------------- --------------- ---------
408160 RFS 12-MAY-16 12-MAY-16 YES
408161 RFS 12-MAY-16 12-MAY-16 YES
408162 RFS 12-MAY-16 12-MAY-16 YES
408163 RFS 12-MAY-16 12-MAY-16 NO
408164 RFS 12-MAY-16 12-MAY-16 NO
408165 RFS 12-MAY-16 12-MAY-16 NO
（以上为最新日志编号，从上往下递增排序，省略前面部分信息）
//查看备库的日志同步情况，可见编号为408163，408164，408165的日志还没有得到应用，确保日志都应用了，在主库上也执行一遍同样的语句，联动检查同步情况（检查主库日志是否都已经同步到最新序列号，检查备库所有日志是否得到应用，即APPLIED字段值为“YES”，当该字段值为“IN-MEMORY”时意正在写入内存并同步中，当字段值为“NO”时意为日志还未得到同步）。
步骤（7.3）
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomerdg1/redo01_dg1.log' size 512M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomerdg1/redo02_dg1.log' size 512M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomerdg1/redo03_dg1.log' size 512M;
//创建standby logfile备库归档日志
8，主库：
步骤（8.1）
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomer/redo01_dg1.log' size 512M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomer/redo02_dg1.log' size 512M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '/opt/ora11g/oradata/wmccustomer/redo03_dg1.log' size 512M;
//同样地，在主库上创建standby logfile
9，备库：
步骤（9.1）
SQL> alter database recover managed standby database disconnect from session;
//实现日志同步，此处操作意为开启一个同步应用操作的进程，开启之后可自动提交应用来自主库传递过来的归档日志，直到接收到的所有归档日志应用完成之后（APPLIED字段为”YES“，或者执行下面的操作语句之后结束停止
SQL> alter database recover managed standby database cancel;
//同步完成之后（备库所有日志APPLIED字段值为“YES”，参考步骤(9)中的说明），可结束这个恢复进程，结束后还可以自动接收来自主库的归档日志，但不会提交和应用，即APPLIED字段显示为“NO"，等到执行上一步操作中开始新的进程的语句之后继续提交，即提交后归档日志的状态变更过程为”NO“→”IN-MEMORY“→”YES“
SQL> alter database open read only;
//将备库启动到open read only，即把数据库启动到打开状态并设置成只读
//归档日志应用提交状态可用SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;查看
10，主库：
步骤（10.1）
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/ora11g/flash_recovery_area/WMCCUSTOMER/archivelog
Oldest online log sequence 408167
Next log sequence to archive 408169
Current log sequence 408169
//确认主库日志序列情况
11，备库：
步骤（11.1）
SQL>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/ora11g/flash_recovery_area/WMCCUSTOMERDG1/archivelog
Oldest online log sequence 408167
Next log sequence to archive 0
Current log sequence 408169
//确认备库日志序列情况，与上一步中主库情况比对，确认同步，如没有同步，重做前面第9步中的对备库进行日志同步的操作
SQL>SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
------------------- ------------- --------------- ---------------- ---------
408160 RFS 12-MAY-16 12-MAY-16 YES
408161 RFS 12-MAY-16 12-MAY-16 YES
408162 RFS 12-MAY-16 12-MAY-16 YES
408163 RFS 12-MAY-16 12-MAY-16 YES
408164 RFS 12-MAY-16 12-MAY-16 YES
408165 RFS 12-MAY-16 12-MAY-16 YES
408166 RFS 12-MAY-16 12-MAY-16 YES
408167 RFS 12-MAY-16 12-MAY-16 YES
408168 RFS 12-MAY-16 12-MAY-16 YES
408169 RFS 12-MAY-16 12-MAY-16 YES
408170 RFS 12-MAY-16 12-MAY-16 YES
SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
------------------- ------------- -------------- ---------------- ---------
408171 RFS 12-MAY-16 12-MAY-16 YES
//再次在备库上确认日志同步应用情况，可见当前日志已全数同步应用成功
12，主库：
步骤（12.1）
SQL>create table test (id number);
Table created.
SQL>desc test;
Name Null? Type
--------------------- -------- ----------------
ID NUMBER
//在主库上创建一张表test，确认备库上是否可以查看到，以便检测是否已经进入数据同步状态
13，备库：
步骤（13.1）
SQL>desc test;
ERRO:
ORA-04043: object test does not exist
//发现数据没有同步，继续执行下一个步骤操作，接收数据
SQL>alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>desc test;
Name Null? Type
--------------------- -------- ----------------
ID NUMBER
//备库上开始接收数据，并自动同步，已在备库中检测到主库变更，如上
14，主库：
步骤（14.1）
SQL>insert into test values(123);
1 row created.
SQL>commit;
Commit complete.
SQL>select * from test;
ID
------------
123
//在主库上向上一步中创建的表插入数据并提交commit，再次验证数据是否同步
15，备库：
步骤（15.1）
SQL>select * from test;
ID
------------
123
//此时在备库执行select查询，获得从主库同步过来的数据，则至此DataGuard主备搭建成功
//假如上一步主库在插入数据等对表中数据进行更改的操作中没有执行commit提交操作，则备库会查找不到同步数据。
四，参考文章：
Oracle 11g搭建DataGuard详细步骤（物理standby所有操作总结）
LOGGING 、FORCE LOGGING 、NOLOGGING、归档模式、非归档模式
ORACLE初始化参数：LOG_ARCHIVE_CONFIG
ORACLE中的DB_NAME,SERVICE_NAME,INSTANCE_NAME,ORACLE_SID,GLOBAL_DBNAME介绍
2846
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
