探索Oracle 11gR2 DataGuard_02配置 作者:吳偉龍

wuweilong發表於2013-08-13
Oracle

探索Oracle 11gR2 DataGuard_02配置

作者:吳偉龍

配置步驟：

一、開啟強制日誌

二、配置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
密碼檔案：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)
    )
  )


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20674423/viewspace-768331/，如需轉載，請註明出處，否則將追究法律責任。

相關文章