Oracle 10gR2 Dataguard搭建(非duplicate方式)

標籤：bit   tempfile   port   ati   min   channel   ted   tab   map   

我的實驗環境：
源生產庫(主庫)：
IP地址：192.168.1.30
Oracle 10.2.0.5 單一實例

新DG庫（備庫）：
IP地址：192.168.1.31
Oracle 10.2.0.5 單一實例

• 1.源生產庫開啟歸檔
• 2.rman備份源生產庫
• 3.修改源生產庫參數
• 4.配置tnsnames.ora
• 5.同步密碼檔案
• 6.配置pfile檔案
• 7.建立備庫控制檔案
• 8.還原備庫
• 9.開啟日誌應用
• 10.switchover測試
• 11.建立還原點，啟用備庫測試
• 12.failover測試

1.源生產庫開啟歸檔

部署Dataguard環境，要求主庫必須開啟歸檔模式；如果沒有開啟，需要先申請停機開啟歸檔。

shutdown immediatestartup mountalter database archivelog;alter database open;archive log list;

注意：歸檔日誌存放位置，並制定歸檔日誌刪除策略；

歸檔日誌刪除舉例(刪除7天前歸檔)：

vi delarch.sqlcrosscheck archivelog all;delete noprompt archivelog all completed before "sysdate-7";vi delarch.sh#!/bin/bash#Oracle ENV (you may need to change it.)export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=ora10export PATH=$ORACLE_HOME/bin:$PATHrman target / @/home/oracle/rman/delarch.sql log=/home/oracle/rman/delarch.log 

如果之前主庫開啟了歸檔，且有合理的備份策略；則實際上很可能不需要此步驟單獨清除歸檔。

2.rman備份源生產庫

將主庫rman全備，視具體情況選擇是否壓縮備份組，我這裡空間有限選擇壓縮備份組。

備份指令碼：

[[email protected] rman]$ cat backup.sql run {allocate channel d1 type disk;allocate channel d2 type disk;backup as compressed backupset database format ‘/orabak/rman/data_%d_%T_%s.bak‘ plus archivelog format ‘/orabak/rman/log_%d_%T_%s.bak‘;release channel d1;release channel d2;}[[email protected] rman]$ cat backup.sh#!/bin/bashexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1export ORACLE_SID=ora10export PATH=$ORACLE_HOME/bin:$PATHrman target / @backup.sql log backup.log

執行備份：
[[email protected] rman]$ nohup sh backup.sh &

[[email protected] rman]$ tail -200f backup.log Recovery Manager: Release 10.2.0.5.0 - Production on Wed Jan 4 21:47:23 2017Copyright (c) 1982, 2007, Oracle.  All rights reserved.connected to target database: ORA10 (DBID=914008358)RMAN> run {2> allocate channel d1 type disk;3> allocate channel d2 type disk;4> backup as compressed backupset database format ‘/orabak/rman/data_%d_%T_%s.bak‘ plus archivelog format ‘/orabak/rman/log_%d_%T_%s.bak‘;5> release channel d1;6> release channel d2;7> }8> using target database control file instead of recovery catalogallocated channel: d1channel d1: sid=143 devtype=DISKallocated channel: d2channel d2: sid=142 devtype=DISKStarting backup at 04-JAN-17current log archivedchannel d1: starting compressed archive log backupsetchannel d1: specifying archive log(s) in backup setinput archive log thread=1 sequence=46 recid=45 stamp=932415090input archive log thread=1 sequence=47 recid=46 stamp=932420845channel d1: starting piece 1 at 04-JAN-17channel d2: starting compressed archive log backupsetchannel d2: specifying archive log(s) in backup setinput archive log thread=1 sequence=43 recid=42 stamp=932414314input archive log thread=1 sequence=44 recid=43 stamp=932414362input archive log thread=1 sequence=45 recid=44 stamp=932415036channel d2: starting piece 1 at 04-JAN-17channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_102.bak tag=TAG20170104T214725 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:02channel d2: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_103.bak tag=TAG20170104T214725 comment=NONEchannel d2: backup set complete, elapsed time: 00:00:02Finished backup at 04-JAN-17Starting backup at 04-JAN-17channel d1: starting compressed full datafile backupsetchannel d1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oradata/ora10/ORA10/datafile/o1_mf_system_d5tw48bw_.dbfinput datafile fno=00002 name=/oradata/ora10/ORA10/datafile/o1_mf_undotbs1_d5tw48d8_.dbfinput datafile fno=00004 name=/oradata/ora10/ORA10/datafile/o1_mf_users_d5tw48dg_.dbfchannel d1: starting piece 1 at 04-JAN-17channel d2: starting compressed full datafile backupsetchannel d2: specifying datafile(s) in backupsetinput datafile fno=00003 name=/oradata/ora10/ORA10/datafile/o1_mf_sysaux_d5tw48c3_.dbfinput datafile fno=00007 name=/oradata/ora10/ORA10/datafile/o1_mf_forhapoc_d5x5bm2b_.dbfinput datafile fno=00005 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_d_ji_d5x45hbj_.dbfinput datafile fno=00006 name=/oradata/ora10/ORA10/datafile/o1_mf_dbs_i_ji_d5x45jd9_.dbfchannel d2: starting piece 1 at 04-JAN-17channel d2: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/data_ORA10_20170104_105.bak tag=TAG20170104T214728 comment=NONEchannel d2: backup set complete, elapsed time: 00:00:35channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/data_ORA10_20170104_104.bak tag=TAG20170104T214728 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:42Finished backup at 04-JAN-17Starting backup at 04-JAN-17current log archivedchannel d1: starting compressed archive log backupsetchannel d1: specifying archive log(s) in backup setinput archive log thread=1 sequence=48 recid=47 stamp=932420890channel d1: starting piece 1 at 04-JAN-17channel d1: finished piece 1 at 04-JAN-17piece handle=/orabak/rman/log_ORA10_20170104_106.bak tag=TAG20170104T214810 comment=NONEchannel d1: backup set complete, elapsed time: 00:00:02Finished backup at 04-JAN-17Starting Control File and SPFILE Autobackup at 04-JAN-17piece handle=/orabak/rman/20170104/controlfilec-914008358-20170104-06 comment=NONEFinished Control File and SPFILE Autobackup at 04-JAN-17released channel: d1released channel: d2Recovery Manager complete.[1]+  Done                    nohup sh backup.sh[[email protected] rman]$ 

備份完成後,將備份組拷貝到備機。

3.修改源生產庫參數

查看主庫檔案(資料檔案、臨時檔案、重做記錄檔)存放目錄：

select name from v$datafile union allselect name from v$tempfile union allselect member from v$logfile;

修改源生產庫參數：

--設定convert參數（我這裡源端和目標端都設定了db_create_file_dest，沒有設定這兩個參數）alter system set log_file_name_convert=‘‘,‘‘ scope=spfile;alter system set db_file_name_convert=‘‘,‘‘ scope=spfile;--設定資料庫為force loggingalter database force logging;--設定db_unique_name, log_archive_config(這裡主庫的db_unique_name，出於對現有的生產環境最小影響考慮沒有改)alter system set db_unique_name=‘ora10‘ scope=spfile;alter system set log_archive_config=‘DG_CONFIG=(ora10,ora10dg)‘; --歸檔日誌目錄alter system set log_archive_dest_1=‘LOCATION=/orabak/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10‘;alter system set log_archive_dest_2=‘SERVICE=ora10dg ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora10dg‘;--歸檔記錄檔命名規則alter system set log_archive_format=‘arch_%r_%t_%s.arc‘ scope=spfile;--DG的fal_server和fal_clientalter system set fal_server=‘ora10dg‘;alter system set fal_client=‘ora10‘;--設定standby_file_management為自動alter system set standby_file_management=AUTO; --設定備庫記錄檔組，數量一般為目標庫記錄檔組+1alter database add standby logfile group 11 size 52428800; alter database add standby logfile group 12 size 52428800; alter database add standby logfile group 13 size 52428800; alter database add standby logfile group 14 size 52428800; 

4.配置tnsnames.ora

Dataguard環境，log_archive_config的配置，就是用到tnsnames.ora設定檔中的別名。

--配置tnsnames.ora在主庫所有節點上的tnsnames.ora添加相應的串連串，並傳給備庫。---主庫tnsnames.ora添加vi $ORACLE_HOME/network/admin/tnsnames.oraORA10 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.30)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ora10)    )  )ORA10DG =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.31)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = ora10dg)    )  )  

將tnsnames.ora上傳到備庫，如果之前tnsnames有其他內容，可以選擇新增內容，只要最終保證主備庫可以互相訪問即可。

5.同步密碼檔案

--密碼檔案從主庫拷貝到備庫[[email protected] dbs]$ scp orapwora10 192.168.1.31:/u01/app/oracle/product/10.2.0/db_1/dbs/[email protected]‘s password: orapwora10                                                                                                                                                  100% 1536     1.5KB/s   00:00    [[email protected] dbs]$ 

6.配置pfile檔案

Dataguard環境下，需要將主庫的參數檔案傳到備庫，進行修改，尤其注意路徑在備庫都存在且有相應許可權。

--建立pfile檔案從主庫的spfile中匯出pfile檔案，上傳到備庫，並做適當的修改調整。--在主庫上建立pfile檔案，並上傳到備庫create pfile=‘/tmp/init.ora‘ from spfile;SQL> create pfile=‘/tmp/init.ora‘ from spfile;File created.SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[[email protected] dbs]$ scp /tmp/init.ora 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT![email protected]‘s password: init.ora                                                                                                                                                    100% 1430     1.4KB/s   00:00    [[email protected] dbs]$ 

--備庫最終pfile參數檔案
[[email protected] admin]$ vi /tmp/init.ora

*.audit_file_dest=‘/u01/app/oracle/admin/ora10/adump‘*.background_dump_dest=‘/u01/app/oracle/admin/ora10/bdump‘*.compatible=‘10.2.0.5.0‘*.core_dump_dest=‘/u01/app/oracle/admin/ora10/cdump‘*.db_block_size=8192*.db_create_file_dest=‘/oradata‘*.db_domain=‘‘*.db_file_multiblock_read_count=16*.db_name=‘ora10‘*.db_recovery_file_dest=‘/orabak/flash_recovery_area‘*.db_recovery_file_dest_size=2147483648*.db_unique_name=‘ora10dg‘*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=ora10XDB)‘*.fal_client=‘ora10dg‘*.fal_server=‘ora10‘*.job_queue_processes=10*.log_archive_config=‘DG_CONFIG=(ora10dg,ora10)‘*.log_archive_dest_1=‘LOCATION=/orabak/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ora10dg‘*.log_archive_dest_2=‘SERVICE=ora10 ASYNC LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ora10‘*.log_archive_format=‘arch_%r_%t_%s.arc‘*.open_cursors=300*.pga_aggregate_target=96468992*.processes=150*.remote_login_passwordfile=‘EXCLUSIVE‘*.sga_target=290455552*.standby_file_management=‘AUTO‘*.undo_management=‘AUTO‘*.undo_tablespace=‘UNDOTBS1‘*.user_dump_dest=‘/u01/app/oracle/admin/ora10/udump‘

啟動備庫到nomount狀態：

SQL> startup nomount pfile=‘/tmp/init.ora‘;ORA-01261: Parameter db_recovery_file_dest destination string cannot be translatedORA-01262: Stat failed on a file destination directoryLinux-x86_64 Error: 2: No such file or directorySQL> exitDisconnected[[email protected] admin]$ mkdir -p /orabak/flash_recovery_area[[email protected] admin]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 4 23:24:25 2017Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.Connected to an idle instance.SQL>  startup nomount pfile=‘/tmp/init.ora‘;ORACLE instance started.Total System Global Area  293601280 bytesFixed Size                  2095768 bytesVariable Size             146802024 bytesDatabase Buffers          138412032 bytesRedo Buffers                6291456 bytesSQL> exit

7.建立備庫控制檔案

Dataguard環境下，需要在主庫建立備庫的控制檔案並傳輸到備庫。

--建立控制檔案
主庫執行，建立備庫控制檔案

alter database create standby controlfile as ‘/tmp/control01.ctlbak‘;[[email protected] dbs]$ scp /tmp/control01.ctlbak 192.168.1.31:/tmp/reverse mapping checking getaddrinfo for bogon failed - POSSIBLE BREAK-IN ATTEMPT![email protected]‘s password: control01.ctlbak                                                                                                                                            100% 6928KB   6.8MB/s   00:00    

8.還原備庫8.1 在備庫上啟動資料庫到nomount狀態

#建立SPFILESQL>create SPFILE from pfile=‘/tmp/init.ora‘;#啟動到nomount狀態startup nomount

8.2 啟動資料庫到mount狀態

restore standby controlfile from ‘/tmp/control01.ctlbak‘;
alter database mount;

可能需要手工註冊備份組；
crosscheck backupset;
catalog start with ‘/orabak/rman/‘;

8.3 還原備份

vi /home/oracle/scripts/restore.sh

rman target / <<EOF! > ora10_restore.logrun {allocate channel d1 type disk;allocate channel d2 type disk;restore database;release channel d1;release channel d2;}exit;EOF!

nohup sh restore.sh &

vi /home/oracle/scripts/recover.sh

rman target / <<EOF! > ora10_recover.logrun {allocate channel d1 type disk;allocate channel d2 type disk;recover database;release channel d1;release channel d2;}exit;EOF!

nohup sh recover.sh &

9.開啟日誌應用

備庫開啟日誌應用

SQL> alter database recover managed standby database using current logfile disconnect from session;

10. switchover測試

switchover,主備角色互換。
注意，主機的crontab 定時任務確認也正確遷移。

--源生產庫：SELECT SWITCHOVER_STATUS FROM V$DATABASE;alter database commit to switchover to physical standby with session shutdown;--備庫（switchover為新生產庫）：SELECT SWITCHOVER_STATUS FROM V$DATABASE;ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;ALTER DATABASE OPEN;--返回源生產庫執行（switchover為新備庫）：shutdown immediatestartup mountALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

如果發現備庫沒有即時恢複，重設鏈路的狀態再次切換日誌試試：

SQL> alter system set log_archive_dest_state_2=defer;SQL> alter system set log_archive_dest_state_2=enable;SQL> alter system switch logfile;

11. 建立還原點，啟用備庫測試11.1 建立restore point

1) 在備庫上設定flashback地區及大小

SQL> alter system set db_recovery_file_dest=‘/orabak/flash_recovery_area‘;SQL> alter system set db_recovery_file_dest_size=1000g;SQL> select current_scn||‘‘ from v$database;

2) Standby庫取消redo日誌應用並建立一個資料庫閃回點

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;--這裡注意下，如果你的備庫環境是RAC，需要關閉其他節點，然後才可以建立restore point：SQL> drop restore point before_application_test;SQL> CREATE RESTORE POINT before_application_test GUARANTEE FLASHBACK DATABASE;Restore point created.

11.2 啟用備庫

1) 停止主庫的日誌傳到備庫

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=defer;

2) 啟用備庫

SQL> alter system set job_queue_processes = 0 scope=both sid=‘*‘;SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;SQL> shutdown immediateSQL> startup

3) 檢查dblinks
select * from dba_db_links;
如果有dblink需要刪除或其他方式禁用；

4）通知新的應用IP，開始測試應用工作

注意：為了預防測試過程中有job或者dblink修改了其他資料庫資訊，一般要修改job_queue_processes參數，刪除dblink。
--參數
alter system set job_queue_processes = 0 scope=both sid=‘‘;
--dblinks
select from dba_db_links;

11.3 閃回恢複備庫

1) 恢複備庫

SQL> shutdown immediateSQL> startup mount;SQL> FLASHBACK DATABASE TO RESTORE POINT before_application_test;SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;SQL> shutdown immediateSQL> startup mountSQL> alter database recover managed standby database using current logfile disconnect;恢複參數job_queue_processes設定值：alter system set job_queue_processes = 10 scope=both sid=‘*‘;恢複參數db_recovery_file_dest_size設定值：alter system set db_recovery_file_dest_size=2g;

2) 恢複日誌傳輸
恢複主庫的日誌傳到備庫

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=enable;

12. failover測試

failover，源生產庫不再可用，備庫強制啟用為主庫；

#取消DG應用ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;#重啟下資料庫（建議）shutdown immediate;startup#操作無法復原，確定實際情況需要failoverALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force;SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; #嘗試常規切換為主庫ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;如果這一步的常規切換失敗，提示需要介質恢複，那麼： 1）恢複備庫 recover standby database until cancel; 2）啟用備庫 alter database activate standby database;#最後重新啟動資料庫shutdown immediate;startup

failover之後，即便原主庫恢複也要重新搭建DG了。

Oracle 10gR2 Dataguard搭建(非duplicate方式)