探索Oracle 11gR2 DataGuard_02配置

来源：互联网发布：android 无网络提示编辑：程序博客网时间：2024/06/05 16:08

作者:吴伟龙

配置步骤：

一、开启强制日志

二、配置pri端传输参数

三、备份pri端数据库

四、拷贝文件到sty端

五、配置sty端传输参数

六、将sty端启动到mount状态并恢复数据

七、在pri端应用发送归档日志文件

八、在sty端开启强制redo应用

九、配置standby日志

十、在pri端启动强制日志应用

11gR2DG传输架构

图：

clip_image002[4]

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密码文件：orapwWoosty控制文件：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]$ lslistener.ora  samples shrept.lst  tnsnames.ora[oracle@pri admin]$ scp *.orasty:$ORACLE_HOME/network/adminoracle@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]$ lshc_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/dbsoracle@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]$ lsfull_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]$ lsadmin 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 bytesFixed Size                  2217992 bytesVariable Size             507512824 bytesDatabase Buffers          327155712 bytesRedo Buffers                2396160 bytesDatabase 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-13using target database control file insteadof recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=25 device type=DISKFinished implicit crosscheck backup at14-MAY-13 Starting implicit crosscheck copy at14-MAY-13using channel ORA_DISK_1Finished implicit crosscheck copy at14-MAY-13 searching for all files in the recoveryareacataloging 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_1File Name:/DBBackup/Phycal/full_db_02o9j17b_1_1 Do you really want to catalog the abovefiles (enter YES or NO)? yescataloging files...cataloging done List of Cataloged Files=======================File Name:/DBBackup/Phycal/full_db_01o9j16h_1_1File Name:/DBBackup/Phycal/full_db_02o9j17b_1_1 RMAN> restore database; Starting restore at 14-MAY-13using target database control file insteadof recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: starting datafilebackup set restorechannel ORA_DISK_1: specifying datafile(s)to restore from backup setchannel ORA_DISK_1: restoring datafile00001 to /DBData/Woo/Woo/system01.dbfchannel ORA_DISK_1: restoring datafile00002 to /DBData/Woo/Woo/sysaux01.dbfchannel ORA_DISK_1: restoring datafile00003 to /DBData/Woo/Woo/undotbs01.dbfchannel ORA_DISK_1: restoring datafile00004 to /DBData/Woo/Woo/users01.dbfchannel ORA_DISK_1: reading from backuppiece /DBBackup/Phycal/full_db_01o9j16h_1_1channel ORA_DISK_1: piecehandle=/DBBackup/Phycal/full_db_01o9j16h_1_1 tag=TAG20130514T025617channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete,elapsed time: 00:00:13Finished 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 logfilegroup 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started. Total System Global Area  839282688 bytesFixed Size                  2217992 bytesVariable Size             507512824 bytesDatabase Buffers          327155712 bytesRedo Buffers                2396160 bytesDatabase 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)    )  )