探索Oracle 11gR2 DataGuard_02配置

探索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端相同
	DB_UNIQUE_NAME	指定唯一名称，区别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)
    )
  )