探索Oracle 11gR2 DataGuard_02配置

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@stys 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@stys 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@stys 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

附录：

十二、需修改添加的参数介绍:

[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)

prudentwoo 10g/11g OCP 11g OCM，ITPUB和CSDN专家及专家讲师；有着多年数据库从业经验，资深Oracle数据库专家，现就职于北京海量数据技术股份有限公司担任高级dba职务，为央视，银行，电信等各行业及企业提供过技术支持服务