敗家貓

os: rhce 4.0
oracle: 9iR2

目標: 大多數的資料都是需要停止db的運作才建置起data guard的，但真實情況中，大多數的企業中是不太能忍受長時間的停止運作
           為了符合現實情況，這次需要二個pc來完成所需要的環境

作用: 做異地備援、縮短移轉機器的停止運作時間

我所用到的vmware的環境

角色	sid	版本	ip
primary	binhu	9iR2	192.168.200.101
sandby	binhu	9iR2	192.168.200.102
catalog	rman10g	10g	192.168.200.101

我的catalog db是使用10g的版本，並且是跟primary的機器放同一台上面

---

1、先建置初始環境

一、分別在二台機器的os上建置oracle，在primary上需建立二個資料庫，分別為data guard測試用的db跟catalog用的db 二、在做standby的機器上只要建置oracle就夠了，不需要建置資料庫 三、設定機器的ip到 /etc/hosts 裏面 四、二台機器上的目錄都是相同的 五、primary db必需是archive mode

2、primary需要的設定
一、先設定網路相關的

listener.ora LOG_DIRECTORY_LISTENER = /u01/app/oracle/product/9.2.0/network/log LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))       )       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1521))       )     )   ) SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /u01/app/oracle/product/9.2.0)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = binhu)       (ORACLE_HOME = /u01/app/oracle/product/9.2.0)       (SID_NAME = binhu)     )   ) tnsnames.ora NB_BINHU =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = binhu)     )   ) #ip是指primary SNB_BINHU =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = binhu)     )   ) # ip是standby rman10g =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1522))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = rman10g)     )   )

二、primary parameter 的內容

*.aq_tm_processes=1 *.background_dump_dest='/u01/app/oracle/admin/binhu/bdump' *.compatible='9.2.0.0.0' *.control_files='/u01/app/oracle/oradata/binhu/control01.ctl','/u01/app/oracle/oradata/binhu/control02.ctl','/u01/app/oracle/oradata/binhu/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/binhu/cdump' *.db_block_size=4096 *.db_cache_size=102760448 *.db_domain='' *.db_file_multiblock_read_count=8 *.db_name='binhu' --預設值 *.dispatchers='(PROTOCOL=TCP) (SERVICE=binhuXDB)' *.fal_client='NB_BINHU'  --指自已的機器 *.fal_server='SNB_BINHU'  --指standby的機器 *.fast_start_mttr_target=300 *.hash_join_enabled=FALSE *.instance_name='binhu' --預設值 *.java_pool_size=20971520 *.job_queue_processes=10 *.large_pool_size=19922944 *.log_archive_dest='' --這是空白 *.log_archive_dest_1='location=/u01/arch' --設定本機archive log的目錄 *.log_archive_dest_2='service=snb_binhu' --設定standby機器，這時是會查tnsnames.ora的設定值，所以一定要設對 *.log_archive_dest_state_2='ENABLE' --在standby機器還沒有好之前是設defer *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=45088768 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=33554432 *.sort_area_size=524288 *.standby_archive_dest='/u01/arch' --設定standby 的archive log的目錄，為了日後機器角色交換方便，可以先設定 *.standby_file_management='AUTO' --我是讓oracle自動管理archive log file 傳送的流程 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=900 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/binhu/udump'

3、standby  相關設定
一、密碼檔:

預設檔案是在 $ORACLE_HOME/dbs/orapw<$ORACLE_SID> 看你是要把primary上的orapw<$ORACLE_SID>檔  copy到standby機器上的相同目錄下，不然也可去orapw指立去建立

二、
pfile parameter 的內容(是跟primary相同，但有一些地方要特別注意，fal_cline、fal_server的參數一定要設定， log_archive_dest_state_2的值為'DEFER'(primary值為enable)，裏面所用到的目錄都需要全建立起來跟給於 oracle能讀寫的權限。簡單的說，把環境用成二台都相同

*.aq_tm_processes=1 *.background_dump_dest='/u01/app/oracle/admin/binhu/bdump' *.compatible='9.2.0.0.0' *.control_files='/u01/app/oracle/oradata/binhu/control01.ctl','/u01/app/oracle/oradata/binhu/control02.ctl','/u01/app/oracle/oradata/binhu/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/binhu/cdump' *.db_block_size=4096 *.db_cache_size=102760448 *.db_domain='' *.db_file_multiblock_read_count=8 *.db_name='binhu' *.dispatchers='(PROTOCOL=TCP) (SERVICE=binhuXDB)' *.fast_start_mttr_target=300 *.hash_join_enabled=FALSE *.instance_name='binhu' *.java_pool_size=20971520 *.job_queue_processes=10 *.large_pool_size=19922944 *.log_archive_dest='' *.log_archive_dest_1='location=/u01/arch' *.log_archive_dest_2='service=snb_binhu' *.log_archive_dest_state_2='DEFER' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=45088768 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=33554432 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=900 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/binhu/udump' *.fal_server=snb_binhu *.fal_client=nb_binhu *.standby_file_management='AUTO' *.standby_archive_dest='/u01/arch'

三、網路設定

listener.ora SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /u01/app/oracle/product/9.2.0)       (PROGRAM = extproc)     )     (SID_DESC =       (GLOBAL_DBNAME = binhu)       (ORACLE_HOME = /u01/app/oracle/product/9.2.0)       (SID_NAME = binhu)     )   ) LOG_DIRECTORY_LISTENER=/u01/app/oracle/product/9.2.0/network/log LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))       )       (ADDRESS_LIST =         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))       )     )   ) tnsnames.ora nb_binhu =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.102)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = binhu)     )   ) # 這時的ip是指standby自已的ip哦，可別指錯了 snb_binhu =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = binhu)     )   ) #這時的ip是指primary的ip哦，別指錯了 rman10g =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.101)(PORT = 1522))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = rman10g)     )   )

4、準備primay的rman備份跟搬到standby機器上

1、rman  sys/oracle@nb_binhu catalog  binhu/oracle@rman10g 2、backup full format '/u01/backup/full_%u_%s_%p' database include current controlfile for standby  plus archivelog; (我本來是用 plus archive delete all input的，但最後面老是回報少一個archive log檔，所以我就手動再搬一次archive log檔到 standby機器上) 3、把備份好的檔案放上standby機器上 4、把parimary上的archive的檔 copy一份到standby對應的目錄裏 scp /u01/arch/*  oracle@snb_binhu:/u01/arch/.

5、
開起二台的listener，並且用tnsping看是否能正常回應

6、開始做standby db了

登到standby機器上，使用修改過的pfile內容開到nomount SQL> startup  nomount pfile='/tmp/pfile'; SQL> exit 一定要離開，不然有可能會發生lock的情況發生 要做duplicate的動作了 rman target sys/oracle@snb_binhu  catalog binhu/oracle@rman10g  auxiliary sys/oracle@nb_binhu (在執行之前你要確定primary上面的archive log 檔案都有 copy到standby機器上了，不然到最後執行dorecover時將產生錯誤，執行的log就不貼了，因為很長=.=") RMAN> duplicate target  database  for standby  nofilenamecheck dorecover; RMAN>exit

7、開始驗證

進standby資料庫 SQL> select dbid from v$database; --這時的dbid值將跟primary上面的相同 SQL>select status from v$instance; --這時應該是在mount SQL>alter database recover managed standby database disconnect; --9i 只能開到recover的狀態 這時可看alter log的資料，應該會出現等待primary的archive log檔送過來的訊習才對 Tue Jul 31 23:41:56 2007 Completed: alter database recover managed standby database di Tue Jul 31 23:42:25 2007 Media Recovery Log /u01/arch/1_53.dbf Media Recovery Waiting for thread 1 seq# 54 Tue Jul 31 23:43:13 2007 Restarting dead background process QMN0 QMN0 started with pid=9 登到primary的機器 SQL> alter system set  log_archive_dest_state_2=ENABLE; --開起data guard 的運作 在alter log裏應該可看到開始傳送primary跟standby機器之間相差的archive log檔的訊習 Tue Jul 31 23:40:40 2007 ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; Tue Jul 31 23:42:23 2007 Thread 1 advanced to log sequence 54   Current log# 2 seq# 54 mem# 0: /u01/app/oracle/oradata/binhu/redo02.log Tue Jul 31 23:42:23 2007 ARC1: Evaluating archive   log 1 thread 1 sequence 53 ARC1: Beginning to archive log 1 thread 1 sequence 53 Creating archive destination LOG_ARCHIVE_DEST_2: 'snb_binhu' Creating archive destination LOG_ARCHIVE_DEST_1: '/u01/arch/1_53.dbf' ARC1: Completed archiving  log 1 thread 1 sequence 53 Tue Jul 31 23:43:31 2007 create tablespace t2 datafile '/u01/app/oracle/oradata/binhu/t2.dbf' size 1M Tue Jul 31 23:43:32 2007 Starting control autobackup Control autobackup written to DISK device         handle '/u02/backup/c-649538103-20070731-08' Completed: create tablespace t2 datafile '/u01/app/oracle/ora 如alter log中都沒有看到錯誤訊習的話，那就可以建立一些資料測看看了，範列如下: 1、在primary建立一個tablespace，看standby的機器上是否有建立起對應的檔案 2、可在primary上新增一些資料，然後手動執行 alter system switch logfile / alter system archive log current都可 如沒有錯誤的話，應該可看到standby也會增加archive log file 3、如standby機器要查看資料的話，需要注意

 
小結:
1、確定二台的環境、os、oracle、路行都相同，就可由上列最簡單的方法實做
2、oracle必需要為archive log mode
3、要確認網路的設定要正確
4、standby要有必要的archive log file，不然會在log中一直看到需要recvoer


心得:
利用rman來做data guard的很方便，如移轉db沒有很長的down time的話，可考慮這種方法移轉db