ZT:在 RHEL3 上配置 Oracle 10g Data Guard

一、 環境配置

primary： IP：192.168.0.120
CPU：2個Intel(R) Xeon(TM) CPU 2.80GHz （HT）
Mem：2G Swap：4G
Disk：130G DB：

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS：Linux oracle 2.4.21-20.ELsmp #1 SMP
standby： IP：192.168.0.101 Cup：2個Intel(R) Xeon(TM) CPU 2.40GHz （HT） Mem：2G Swap：2G Disk：66G DB：
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod OS：Linux boss-3 2.4.21-15.ELsmp #1 SMP Primary

為正在使用的生產資料庫，standby安裝oracle軟體，但不建立資料庫。正在裝載資料……

二、 建立物理備用資料庫

1． 準備主庫的oracle環境：編輯oracle使用者的/HOME/.bash_profile檔案，oracle相關環境變數如下：

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280; export NLS_LANG

2． 修改主庫為歸檔模式建立歸檔目錄：

mkdir -p /u02/oradata/BOSS/arch

修改歸檔模式：

archive log list; create pfile from spfile;

編輯$ORACLE_HOME/dbs/initBOSS.ora 新增下面一行 log_archive_dest_1='location=/u02/oradata/BOSS/arch'

sqlplus /nolog conn sys as sysdba
shutdown immediate;
create spfile from pfile;
startup nomount; alter database mount;
alter database archivelog;
alter database open;

3. 對主資料庫做一次完整熱備份，獲得備用資料庫資料 RMAN>connect target

RMAN> backup database format='/home/oracle/%U_%s.bak';
RMAN> sql "Alter System Archive Log Current";
RMAN> Backup filesperset 10 ArchiveLog all format='/home/oracle/%U_%s.bak';

cd /home/oracle scp *.bak 192.168.0.101:/home/oracle/

4. 在standby伺服器準備環境與primary相同編輯oracle使用者的$HOME/.bash_profile檔案，oracle相關環境變數如下：
ORACLE_BASE=/u01/app/oracle;
export ORACLE_BASE ORACLE_SID=BOSS;
export ORACLE_SID ORACLE_HOME=$ORACLE_BASE/product/10.1.0/Db_1;
export ORACLE_HOME export PATH=$ORACLE_HOME/bin:$PATH:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib DISPLAY=10.1.9.59:0.0;
export DISPLAY NLS_LANG=american_america.ZHS16CGB231280;

export NLS_LANG

5. 準備相應目錄，如日誌檔案路徑，歸檔路徑，引數檔案路徑，資料檔案準備存放路徑等
$mkdir -p /u02/oradata/BOSS
$mkdir -p /u02/oradata/BOSS/arch
$mkdir -p $ORACLE_BASE/admin/BOSS
$mkdir -p $ORACLE_BASE/admin/BOSS/bdump
$mkdir -p $ORACLE_BASE/admin/BOSS/cdump
$mkdir -p $ORACLE_BASE/admin/BOSS/udump

6. 建立備用資料庫引數檔案主庫的引數如下：
BOSS.__db_cache_size=339738624
BOSS.__java_pool_size=33554432
BOSS.__large_pool_size=4194304
BOSS.__shared_pool_size=218103808
*.background_dump_dest='/u01/app/oracle/admin/BOSS/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/u02/oradata/BOSS/control01.ctl','/u02/oradata/BOSS/control02.ctl','/u02/oradata/BOSS/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/BOSS/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='BOSS'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_writer_processes=4
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BOSSXDB)'
*.global_names=FALSE
*.java_pool_size=32M
*.job_queue_processes=10
*.license_max_users=250
*.log_archive_dest_1='location=/u02/oradata/BOSS/arch'
*.log_archive_dest_2='SERVICE=dbstandby LGWR'
*.open_cursors=300
*.pga_aggregate_target=199229440
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=598736896
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/BOSS/udump'
*.utl_file_dir='/u01/app/oracle/admin/BOSS/bdump'

與主資料庫不一樣的引數如下：

#standby database parameter
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='/u02/oradata/BOSS/arch'
fal_server='DBPRIMARY'
fal_client='DBSTANDBY'

7. 從主伺服器複製口令檔案到備用伺服器

$cd $ORACLE_HOME/dbs/
$scp orapwBOSS 192.168.0.101: /u01/app/oracle/product/10.1.0/Db_1/dbs

8. 配置網路連線修改主服務名：
/u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora檔案如下：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) ) ) )

SID_LIST_LISTENER =
(SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1)
(PROGRAM = extproc) )
(SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1522)) ) ) )

SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS)
(ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

$lsnrctl start
$lsnrctl status
檢視監聽狀態. 修改主伺服器的/u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora檔案如下：
BOSS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)) (
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) )
(CONNECT_DATA = (SID = PLSExtProc) (divSENTATION = RO) ) )

DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

修改備用伺服器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/listener.ora檔案如下：

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521)) ) ) )
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )
LISTENERDB = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522)) ) ) )
SID_LIST_LISTENERDB = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = BOSS) (ORACLE_HOME = /u01/app/oracle/product/10.1.0/Db_1) (SID_NAME = BOSS) ) )

修改備用伺服器的 /u01/app/oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora檔案如下：
DBPRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.120)(PORT = 1521)) )
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

DBSTANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BOSS) ) )

在這裡配置兩個監聽，一個用於主伺服器到備用伺服器的連線，埠是1522，另外一個用於日後的切換需要，預設埠1521。啟動1522 的埠

$lsnrctl start listenerdb
$lsnrctl status listenerdb
檢視1522 埠上監聽的狀態.
測試: 在主和備用機上分別執行 tnsping dbprimary tnsping dbstandby

9. 在主資料庫建立備用伺服器控制檔案
alter database create standby controlfile as '/home/oracle/standby.ctl';

建立後將控制檔案cp(rcp or scp)到備用資料庫所在的控制檔案目錄下。
如$ scp control01.ctl 192.168.0.101:/u02/oradata/BOSS/
cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control02.ctl cp /u02/oradata/BOSS/control01.ctl /u02/oradata/BOSS/control03.ctl

10. 啟動備用資料庫
conn sys as sysdba

create spfile from pfile;
startup nomount;
alter database mount standby database;

恢復資料庫:
RMAN> connect target;
RMAN> restore database;
RMAN> restore archivelog all;
如果有恢復的日誌並想手工恢復，可以執行如下命令

SQL>recover automatic standby database;
如果過程中出現如下類似錯誤，則可以忽略 ORA-00279: change 50775 generated at 06/08/2004 21:57:21 needed for thread 1 ORA-00289:

suggestion : /u01/oracle/oradata/tbdb/archive/1_5.dbf

ORA-00280: change 50775 for thread 1 is in sequence #5
ORA-00278: log file '/u01/oracle/oradata/tbdb/archive/1_5.dbf' no longer needed for this recovery
ORA-00308: cannot open archived log '/u01/oracle/oradata/tbdb/archive/1_5.dbf'
ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information:

3 因為最後需要的日誌根本沒有從主資料庫送過來 進入到後臺管理恢復狀態

SQL>alter database recover managed standby database disconnect from session;

三、採用Lgwr程式傳遞聯日誌機的最大效能模式

1. 在備用資料庫上建立備用日誌

alter database recover managed standby database cancel;
alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;
alter database recover managed standby database disconnect from session;

2. 修改主庫的歸檔路徑
alter system set LOG_ARCHIVE_DEST_2='SERVICE=dbstandby LGWR' scope=both;
另外，如果考慮到以後該庫可能被切換到備用資料庫，也可以建立同樣的備用日誌組:

alter database add standby LOGFILE GROUP 5 ('/u02/oradata/BOSS/stdy_redo05.log') size 10m;
alter database add standby LOGFILE GROUP 6 ('/u02/oradata/BOSS/stdy_redo06.log') size 10m;
alter database add standby LOGFILE GROUP 7 ('/u02/oradata/BOSS/stdy_redo07.log') size 10m;
alter database add standby LOGFILE GROUP 8 ('/u02/oradata/BOSS/stdy_redo08.log') size 10m;

四、驗證備用伺服器是否工作在主庫上：
create user test identified by ftp123;
grant connect,resource to test;
conn ;
create table test(name varchar2(20));
insert into test values('hi, Data Guard');
commit;
conn / as sysdba;
alter system switch logfile;

檢視從庫日誌以只讀方式開啟從庫檢視 insert into test values('hi, Data Guard'); 已經生效。
conn / as sysdba;
alter database recover managed standby database cancel;
alter database open read only;
conn test/ftp123
select * from test;

再次設定從庫在恢復模式：
alter database recover managed standby database disconnect from session;

五、日常管理
1. 備用伺服器的管理模式與只讀模式
（1）啟動到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;

（2）啟動到只讀方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;

（3）如果在管理恢復模式下到只讀模式
SQL>recover managed standby database cancel;
SQL>alter database open read only;

這個時候，可以給資料庫增加臨時資料檔案（這個在熱備份的時候是沒有備份過來的）如
alter tablespace temp add tempfile '/u02/oradata/BOSS/temp01.dbf' size 100M;
（4）從只讀方式到管理恢復方式
SQL>recover managed standby database disconnect from session;

2. 備用伺服器日誌刪除備用伺服器的日誌刪除也必須小心，因為如果有些日誌還沒有被備用伺服器應用而該日誌被刪除的話，
將引起備用資料庫無法往下應用新的日誌。刪除備用伺服器的日誌的指令碼為：
#!/bin/sh
# set env
cd $HOME .
.bash_profile
# start remove cd
$HOME/dbbat grep "Media Recovery Log" $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log | awk '{print $4}'|sed -e 's/^/rm /' > rmarch log.sh
chmod +x ./rmarchlog.sh
./rmarchlog.sh

cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
cat alert_${ORACLE_SID}.log >>alert_${ORACLE_SID}.log.bak
echo ''>alert_${ORACLE_SID}.log rm -f ./rmarchlog.sh

3. 日誌延遲檢查備用伺服器可能有這樣的情況發生，因為日誌塊邏輯損壞，所以必須對日誌應用進行檢查，防止日誌應用被停止，防患於未然，
當然我們可以手工檢查，但是以下指令碼則可以實現自動檢查（放到cron中）
#!/bin/bash
# set env
cd $HOME .
.bash_profile
# start check
DATE=`date +%Y-%m-%d:%H:%M:%S`
filepath=/u02/oradata/$ORACLE_SID/arch/
logpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump
remotefile=`ssh oracle@192.168.0.120 "ls -t /u02/oradata/BOSS/arch/*|head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'"`
echo "CHECK TIME:"${DATE} echo echo "remote file : "$remotefile cd $filepath varfile=`ls -t | head -1|sed -e 's/.*_1_//g' |sed -e 's/.arc$//g'`
echo "archive file : "$filepath$varfile cd $logpath varlog=`grep "Media Recovery Log" alert_${ORACLE_SID}.log | awk '{print $4}' | tail -1 |sed -e 's/.*_1_//g' | sed -e 's/.arc$//g'` echo "applice file : "$varlog echo echo >> $HOME/dblog/check_DG_log.log
echo "CHECK TIME:"${DATE} >> $HOME/dblog/check_DG_log.log
echo >> $HOME/dblog/check_DG_log.log echo "remote file : "$remotefile >> $HOME/dblog/check_DG_log.log
echo "archive file : "$filepath$varfile >> $HOME/dblog/check_DG_log.log
echo "applice file : "$varlog >> $HOME/dblog/check_DG_log.log echo >> $HOME/dblog/check_DG_log.log

六、主庫與備庫的正常切換注意：
Swithover時只能先從Primary切到Standby，再從Standby切到Primary. 以下順序不能顛倒，如果採用standby redo log的需要注意在切換前在主資料庫建立同樣的standby redo log。

1．切換之前先要準備init引數檔案最簡單的辦法就是把兩個資料庫的檔案互換，在一個機器上同時保留主資料庫的初始化檔案與備用資料庫的初始化檔案。
2. 從Primary切換到standby的指令碼： [
worksh]$ more swithstandby.sh
#!/bin/bash cd $HOME
. .bash_profile
sqlplus /nolog ALTER DATABASE REGISTER LOGFILE '/u01/oracle/oradata/tbdb/archive/1_87.dbf';
SQL>recover standby database;

（2）如果有活動日誌，必須用 alter database recover managed standby database finish;
否則用 alter database recover managed standby database finish skip standby logfile;
這樣切換的備用伺服器可以避免最小的資料丟失和不用resetlogs，特別是對於用多個備用伺服器的時候，該伺服器可以馬上作為主伺服器而不用重新建立備用伺服器。

2. 強行切換（啟用）這樣的切換是以激和備用伺服器來完成的，在重新啟動資料庫的時候，備用機會 resetlogs，
這樣會影響到其它備用伺服器而且必須重新在主伺服器上重新構造備用伺服器，一般不建議這樣做。

$ more activeprimary.sh
#!/bin/bash
#swith to primary with cancel
cd $HOME
. .bash_profile
#cancel and startup database sqlplus /nolog