DG實施方案（主庫為雙節點rac）--rman duplicate方式

環境信息：
操作系統版本： AIX6.1
數據庫版本：   ORACLE 11.2.0.3（psu5）
主庫為雙節點rac

1.檢查數據庫是否支持Data Guard（只有企業版才支持DG）
SQL> select * from v$option where parameter = 'Managed Standby';

2.修改主庫為歸檔模式及force logging狀態
1）SQL> archive log list;
如果未開歸檔，開啟歸檔模式

alter system set log_archive_dest_2='location=/archlog/egaa';
alter system set log_archive_format='egaa_%t_%s_%r.arch' scope=spfile; --靜態參數，重啟后生效
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;

2）打開force logging

SQL> alter database force logging;
Database altered.

3.創建備庫pfile文件
在主庫上創建pfile，修改，并添加DG備庫所有參數，然后傳至備庫
SQL> create pfile='/data01/pfileegaa' from spfile;
1）備庫必須要添加的參數
DB_UNIQUE_NAME;LOG_ARCHIVE_DEST_1;FAL_SERVER;FAL_CLIENT;STANDBY_FILE_MANAGEMENT=AUTO;DB_FILE_NAME_CONVERT;LOG_FILE_NAME_CONVERT
2）根據pfile中涉及到路徑需要提前在備庫主機上建好（）
如主庫*.audit_file_dest='/apps/oracle/admin/egaadr/adump'
我們在備庫需要建 mkdir -p /apps/oracle/admin/egaadr/adump
cd /apps/oracle/admin
chown -R oracle:oinstall egaadr
chmod -R 775 egaadr

修改前參數文件（此處就不列了）

修改后參數文件

*.audit_file_dest='/apps/oracle/admin/egaadr/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.0.0'
*.control_files='+DATA1/egaadr/controlfile/control01.ctl','+DATA1/egaadr/controlfile/control02.ctl'#Set by RMAN
*.core_dump_dest='/apps/oracle/diag/rdbms/egaadr/egaadr/cdump'
*.db_block_size=8192
*.db_cache_size=17179869184
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_file_name_convert='+DATA2/EGAADB/DATAFILE','+DATA1/EGAADR/DATAFILE','+DATA2/EGAADB/TEMPFILE','+DATA1/EGAADR/TEMPFILE'
*.log_file_name_convert='+DATA2/EGAADB/ONLINELOG','+DATA1/EGAADR/ONLINELOG','+RECODG/egAAdb/onlinelog','+DATA1/EGAADR/ONLINELOG1'
*.db_name='EGAA'
*.db_recovery_file_dest='+DATA1'
*.db_recovery_file_dest_size=307000M
*.db_unique_name='EGAADR'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/apps/oracle/'
*.fal_client='EGAADR'
*.fal_server='EGAADB1,EGAADB2'
*.instance_name='egaadr'
*.large_pool_size=536870912
*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.48.81)(PORT=1521))))'
*.log_archive_config='dg_config= (EGAADB,EGAADR)'
*.log_archive_dest_1='LOCATION=+DATA1/egaadr/archlog valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EGAADR'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='egaadr_%t_%s_%r.arc'
*.log_buffer=48857088# log buffer update
*.open_cursors=1000
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.parallel_max_servers=480
*.pga_aggregate_target=12884901888
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=2000
*.query_rewrite_enabled='TRUE'
*.remote_listener='drdb-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=52448K
*.sessions=3072
*.sga_max_size=25769803776
*.sga_target=25769803776
*.shared_pool_size=4294967296
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_retention=5400
*.undo_tablespace='UNDOTBS1'
##注意參數文件中指定的目錄在備庫要存在如：db_file_name_convert和log_file_name_convert參數指定的路徑要存在
##去掉rac數據庫的相關參數

4）根據修改后的pfile創建備庫spfile
export ORACLE_SID=egaadr
sqlplus / as sysdba
create spfile from pfile;
--用新生成的spfile看是否能夠成功啟動實例

4.生成備庫的密碼文件
scp主庫密碼文件到備庫，并改名
如果主庫沒有密碼文件，需要新建
orapwd file=/oracle/app/oracle/product/v11.2.0.3/db_1/dbs/orapwegaa password=oracle  entries=5 ignorecase=y
--如果新建后連接時報密碼錯誤，我們可以把主庫其中一個節點密碼文件scp到另一個節點和備庫并改名使用

5.配置主備庫監聽及net服務

1）listener

一般建庫后都會配置監聽我們無需再配置
--備庫（因為安裝了cluster，所以用的是cluster的監聽）
lsnrctl status 查看監聽文件位置，并在監聽文件中加入如下類容（注意兩個節點都進行配置）

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3))))                # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2))))                # line added by Agent
#LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON                # line added by Agent
##注意集群安裝完畢以后，上面部分內容在監聽中已經存在
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1)
(SID_NAME = egaa1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.1)(PORT = 1521)(IP = FIRST))
)
)

2）tns
主備庫tnsnames.ora文件中加入如下部分

EGAA1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.230)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egaa) (UR = A)
)
)
EGAA2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.231)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egaa) (UR = A)
)
)
EGAADR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.81)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egaa) (UR = A)
)
)
--也可以在主庫的兩個節點只配一個連接串，如下：
EGAA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.230)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.231)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = egaa) (UR = A)
)
)

6. 測試網絡連接
1）查看監聽狀態是否正常
lsnrctl status
2) 測試連接串是否能正常連接到指定數據庫
tnsping EGaaDR
tnsping EGaa1
tnsping EGaa2
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaa1 as sysdba
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaa2 as sysdba
sqlplus sys/AE8pfChcG0BBGlL73DW0@egaadr as sysdba

7. 復制備庫

有兩種1:rman在線復制 2:rman備份異機恢復
此處因為庫比較小，我們第一種方法，這種方法比較簡單。

方法1：rman在線復制（不需要備份主庫）
此種方式僅適用于ORACLE 11G，可以自動備份datafile,control等文件到備庫，在復制過程主庫仍可正常運行，但復制過程時間較長，會占用一定的網絡資源。
1）將備庫啟動到nomount狀態
export ORACLE_SID=egaadr
sqlplus / as sysdba
startup nomount;

2)在備庫上執行如下命令

rman target sys/FWNgTA4XlcUuDXDiQAdT@egaa1 auxiliary sys/FWNgTA4XlcUuDXDiQAdT@EGAADR nocatalog
duplicate target database for standby from active database nofilenamecheck;

--如果主備庫文件路徑不變，要加nofilenamecheck（否則會報錯）

--我們可以寫一個腳本放在后臺運行，在預計需要時間過后查看相關日志復制是否成功就行，腳本如下：

#/bin/sh
export ORACLE_SID=EGMMDR
rman target sys/AE8pfChcG0BBGlL73DW0@EGAA1 auxiliary sys/AE8pfChcG0BBGlL73DW0@EGAADR nocatalog log '/home/oracle/rman.log' <<EOF
run {
allocate channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database for standby from active database;
}
EOF

后臺執行腳本 nohup rman.sh &

8. 添加standby log

--為了日后切換，建議為主庫也添加standby log
--注意stnadby log的大小（同主庫redolog相同大小）
--注意添加合適數量的standby log

--standy log 的推薦數目為=（每個線程的日志文件的最大數目＋1）×線程最大數目

--ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 3('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;
--alter database drop logfile group 7;--刪除日志組

alter database add standby logfile thread 1
GROUP 26('+data1') SIZE 500M,
GROUP 27('+data1') SIZE 500M,
GROUP 28('+data1') SIZE 500M,
GROUP 29('+data1') SIZE 500M,
GROUP 30('+data1') SIZE 500M,
GROUP 31('+data1') SIZE 500M,
GROUP 32('+data1') SIZE 500M,
GROUP 33('+data1') SIZE 500M,
GROUP 34('+data1') SIZE 500M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
檢查是否成功創建

9.配置主庫DG參數
主庫需要配置的參數為DB_UNIQUE_NAME;LOG_ARCHIVE_CONFIG;LOG_ARCHIVE_DEST_2;REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
--為了方便以后切主庫也應該添加備庫所需要的參數

*.DB_UNIQUE_NAME=EGAA
*.FAL_SERVER=EGAADR
egaa1.FAL_CLIENT=egaa1
egaa2.FAL_CLIENT=egaa2
*.STANDBY_FILE_MANAGEMENT=AUTO
*.DB_FILE_NAME_CONVERT='+DATA1/EGAADR/DATAFILE','+DATA2/EGAA/DATAFILE','+DATA1/EGAADR/TEMPFILE','+DATA2/EGAA/TEMPFILE'
*.LOG_FILE_NAME_CONVERT='+DATA1/EGAADR/ONLINELOG','+DATA2/EGAA/ONLINELOG','+DATA1/EGAADR/ONLINELOG1','+RECODG/EGAA/onlinelog'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(EGAA,EGAADR)'
*.log_archive_dest_1='location=+RECODG/egaa/archivelog'
*.log_archive_dest_2='SERVICE=EGAADR LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=EGAADR'
alter system set fal_server='EGAADR';
alter system set fal_client='EGAA1' sid='EGAA1';
alter system set fal_client='EGAA2' sid='EGAA2';
alter system set standby_file_management=auto;
alter system set db_file_name_convert='+DATA1/EGAADR/DATAFILE','+DATA2/EGAA/DATAFILE','+DATA1/EGAADR/TEMPFILE','+DATA2/EGAA/TEMPFILE' scope=spfile;
alter system set log_file_name_convert='+DATA1/EGAADR/ONLINELOG','+DATA2/EGAA/ONLINELOG','+DATA1/EGAADR/ONLINELOG1','+RECODG/EGAA/onlinelog' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(EGAA,EGAADR)';
alter system set log_archive_dest_2='SERVICE=EGAADR LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=EGAADR';

10.在備庫上啟動恢復
啟動恢復

alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database cancel;

11.檢查DG狀態是否正常
--主庫切換日志，觀察DG能否正常應用日志****切換前檢查主備庫所有參數
1）在備庫查看 data guard 為哪種日志接受方式，以及當前被應用的日志
select process,client_process,sequence#,status from v$managed_standby;
2）在備庫查看新的歸檔日志有沒有正常傳輸過來，并被應用
select THREAD#,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS from v$archived_log order by 1,2;
3）查看主備庫的alert日志，是否正常
4）查看延時
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';                               
select name,value from v$dataguard_stats where name in ('apply lag','apply finish time');
5)查看日志是否有gap
select * from V$ARCHIVE_GAP;

--注意：
過程中遇到主庫不往log_archive_dest_2傳日志，alert里也沒有告警
后來在主庫上執行如下命令后，恢復日志傳送
alter system set log_archive_dest_state_2 = 'defer' sid='*' scope=both;
alter system set log_archive_dest_state_2='enable' sid='*' scope=both;