探索Oracle 11gR2 DataGuard_02配置
作者:吴伟龙
配置步骤：
一、开启强制日志
二、配置pri端传输参数
三、备份pri端数据库
四、拷贝文件到sty端
五、配置sty端传输参数
六、将sty端启动到mount状态并恢复数据
七、在pri端应用发送归档日志文件
八、在sty端开启强制redo应用
九、配置standby日志
十、在pri端启动强制日志应用
11gR2DG传输架构
图：
|
DBName |
Role |
DB_UNIQUE_NAME |
Oracle Net Service Name |
|
Woo |
Primary |
Pri |
PRI |
|
Physical standby |
Sty |
STY |
一、两节点分别输入如下命令开启强制日志模式：
SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> alter database force logging;
二、修改Pri端参数文件：
SQL> alter system set db_unique_name =pri scope=spfile; SQL> alter system set log_archive_config= 'DG_CONFIG=(pri,sty)' scope=spfile; SQL>alter system set log_archive_dest_1= 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=pri' scope=spfile; SQL> alter system set log_archive_dest_2= 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=sty' scope=spfile; SQL> alter system setlog_archive_dest_state_2 = DEFER; alter system set fal_server=styscope=spfile; SQL> alter system set fal_client=priscope=spfile; SQL> alter system setstandby_file_management=AUTO scope=spfile;
三、在Pri端输入如下命令，创建standby控制文件
SQL> alter database create standbycontrolfile as '/DBBackup/Phycal/stycontrol.ctl';
四、通过rman备份pri端数据库
Rman>backup database format'/DBBackup/Phycal/full_db_%U'; #copy file to standby 监听文件：listener.oratnsnames.ora 参数文件：initWoo.ora 密码文件：orapwWoo sty控制文件：stycontrol.ctl 全库备份文件：full_db_* 日志输出目录：$ORACLE_BASE/admin$ORACLE_BASE/diag
五、拷贝监听文件，参数文件，密码文件，sty端控制文件，rman备份文件，admin/目录，diag诊断目录，flash_recovery_area目录，归档目录 到sty端用于恢复及起库
[oracle@pri ~]$ cd$ORACLE_HOME/network/admin --拷贝监听文件 [oracle@pri admin]$ ls listener.ora samples shrept.lst tnsnames.ora [oracle@pri admin]$ scp *.orasty:$ORACLE_HOME/network/admin oracle@sty's password: listener.ora 100% 294 0.3KB/s 00:00 tnsnames.ora 100% 669 0.7KB/s 00:00 [oracle@pri admin]$ cd $ORACLE_HOME/dbs --拷贝密码文件及pfile参数文件 [oracle@pri dbs]$ ls hc_DBUA0.dat hc_Woo.dat init.ora initWoo.ora lkPRI lkWOO orapwWoo snapcf_Woo.f spfileWoo.ora [oracle@pri dbs]$ scp initWoo.ora orapwWoosty:$ORACLE_HOME/dbs oracle@sty's password: initWoo.ora 100% 999 1.0KB/s 00:00 orapwWoo 100% 1536 1.5KB/s 00:00 [oracle@pri dbs]$ cd /DBBackup/Phycal/ --拷贝备份文件 [oracle@pri Phycal]$ ls full_db_01o9j16h_1_1 full_db_02o9j17b_1_1 stycontrol.ctl [oracle@pri Phycal]$ scp full_db_0*stycontrol.ctl sty:/DBBackup/Phycal/ oracle@sty's password: full_db_01o9j16h_1_1 100% 943MB 20.1MB/s 00:47 full_db_02o9j17b_1_1 100% 9600KB 9.4MB/s 00:01 stycontrol.ctl 100% 9520KB 9.3MB/s 00:00 [oracle@pri ~]$ cd $ORACLE_BASE --拷贝admin/，diag/，flash_recovery_area三目录 [oracle@pri DBSoft]$ ls admin cfgtoollogs checkpoints diag flash_recovery_area oraInventory Product [oracle@pri DBSoft]$ scp -r admin/ diag/admin/ flash_recovery_area/ sty:$ORACLE_BASE
六、修改sty端pfile参数文件，添加和修改如下内容
vi /DBSoft/Product/11.2.0/db_1/dbs/initWoo.ora *.db_unique_name=sty *.log_archive_config='DG_CONFIG=(pri,dg)' *.log_archive_dest_1='LOCATION=/DBBackup/ArchiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' *.log_archive_dest_2='SERVICE=pri LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' *.fal_server=pri *.fal_client=sty *.standby_file_management=AUTO #*.db_file_name_convert='pri','pri' #*.log_file_name_convert='pri','pri' *.control_files='/DBBackup/Phycal/control01.ctl'
七、启动sty端数据库到mount状态，并恢复pri端数据库到sty端
SQL> startup mountpfile='/DBSoft/Product/11.2.0/db_1/dbs/initWoo.ora'; ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2217992 bytes Variable Size 507512824 bytes Database Buffers 327155712 bytes Redo Buffers 2396160 bytes Database mounted. SQL> exit [oracle@sty Phycal]$ export ORACLE_SID=Woo [oracle@sty Phycal]$ rman target / --进入rman开始恢复数据库 Recovery Manager: Release 11.2.0.1.0 -Production on Tue May 14 03:22:47 2013 Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved. connected to target database: WOO(DBID=4154863782, not open) RMAN> catalog start with'/DBBackup/Phycal/'; Starting implicit crosscheck backup at14-MAY-13 using target database control file insteadof recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK Finished implicit crosscheck backup at14-MAY-13 Starting implicit crosscheck copy at14-MAY-13 using channel ORA_DISK_1 Finished implicit crosscheck copy at14-MAY-13 searching for all files in the recoveryarea cataloging files... no files cataloged searching for all files that match thepattern /DBBackup/Phycal/ List of Files Unknown to the Database ===================================== File Name:/DBBackup/Phycal/full_db_01o9j16h_1_1 File Name:/DBBackup/Phycal/full_db_02o9j17b_1_1 Do you really want to catalog the abovefiles (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name:/DBBackup/Phycal/full_db_01o9j16h_1_1 File Name:/DBBackup/Phycal/full_db_02o9j17b_1_1 RMAN> restore database; Starting restore at 14-MAY-13 using target database control file insteadof recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: starting datafilebackup set restore channel ORA_DISK_1: specifying datafile(s)to restore from backup set channel ORA_DISK_1: restoring datafile00001 to /DBData/Woo/Woo/system01.dbf channel ORA_DISK_1: restoring datafile00002 to /DBData/Woo/Woo/sysaux01.dbf channel ORA_DISK_1: restoring datafile00003 to /DBData/Woo/Woo/undotbs01.dbf channel ORA_DISK_1: restoring datafile00004 to /DBData/Woo/Woo/users01.dbf channel ORA_DISK_1: reading from backuppiece /DBBackup/Phycal/full_db_01o9j16h_1_1 channel ORA_DISK_1: piecehandle=/DBBackup/Phycal/full_db_01o9j16h_1_1 tag=TAG20130514T025617 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete,elapsed time: 00:00:13 Finished restore at 14-MAY-13 RMAN> ---至此恢复工作已经完成
八、在pri端应用接受归档日志文件
SQL> ALTER SYSTEM SETLOG_ARCHIVE_DEST_STATE_2=ENABLE; System altered.
九、再sty上启动日志应用
SQL> alter database recover managedstandby database disconnect from session; Database altered.
十、在sty端配置standby 日志，并使其进入active状态生效，通常需要重启一遍备库：
SQL> alter database add standby logfile
group 4 ('/DBData/Woo/Woo/styredo04.log')size 50m,
group 5 ('/DBData/Woo/Woo/styredo05.log')size 50m,
group 6 ('/DBData/Woo/Woo/styredo06.log')size 50m,
group 7 ('/DBData/Woo/Woo/styredo07.log')size 50m;
SQL> SELECTGROUP#,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 UNASSIGNED
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2217992 bytes
Variable Size 507512824 bytes
Database Buffers 327155712 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> SELECTGROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- -------------
4 1 11 YES ACTIVE
5 1 0 NO UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
SQL>
十一、在pri端启动redo应用
SQL> recover managed standby database usingcurrent logfile disconnect from session;
查看DG数据保护模式：
SQL> select protection_mode,protection_level from v$database; PROTECTION_MODE PROTECTION_LEVEL -------------------- -------------------- MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
附录：
十二、需修改添加的参数介绍:
|
角色 |
参数名称 |
介绍 |
|
Pri |
DB_NAME |
数据库名称，primary端和standby端相同 |
|
指定唯一名称，区别pri 端和 sty端 |
||
|
LOG_ARCHIVE_CONFIG |
指定DG的全局日志配置，包含所有数据库的名称，及归档路径 |
|
|
CONTROL_FILES |
控制文件路径及名称 |
|
|
LOG_ARCHIVE_DEST_n |
指定主备库的归档路径 |
|
|
LOG_ARCHIVE_DEST_STATE_n |
配置是否允许通过redo进行日志传输及路径 |
|
|
REMOTE_LOGIN_PASSWORDFILE |
配置远程登陆模式，是否独享还是共享 |
|
|
LOG_ARCHIVE_FORMAT |
配置归档日志文件存储格式规范 |
|
|
LOG_ARCHIVE_MAX_PROCESS=integer |
配置归档进程数量，默认为4 |
|
|
FAL_SERVER |
||
|
DB_FILE_NAME_CONVERT |
||
|
LOG_FILE_NAME,_CONVERT |
配置数据库redo日志文件转换，用在主备库redo日志文件路径不一致 |
|
|
STANDBY_FILE_MANAGEMENT |
配置备库是否同步主库的表空间添加或数据文件添加。 |
|
|
sty |
DB_UNIQUE_NAME |
指定唯一名称，区别pri 端和 sty端 |
|
CONTROL_FILES |
控制文件路径及名称 |
|
|
DB_FILE_NAME,_CONVERT |
配置数据库数据文件转换，用在主备库数据文件路径不一致 |
|
|
LOG_FILE_NAME_CONVERT |
配置数据库数据文件转换，用在主备库数据文件路径不一致 |
|
|
LOG_ARCHIVE_DEST_n |
指定主备库的归档路径 |
|
|
FAL_SERVERS |
配置服务器角色 |
十三、监听信息:
listener.ora
[oracle@sty admin]$ cat listener.ora
# listener.ora Network Configuration File:/DBSoft/Product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /DBSoft/Product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = Woo )
(ORACLE_HOME = /DBSoft/Product/11.2.0/db_1)
)
)
-----------------------------------------------------------------------------------------------------------------------------------------------------
[oracle@sty admin]$cat tnsname.ora
# tnsnames.ora Network Configuration File:/DBSoft/Product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
STY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = woo)
)
)
PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = woo)
)
)