[zt] Oracle 11g DataGuard 配置

Oracle Database 11g EnterPRise Edition Release 11.1.0.6.0 - 64bit Production
Red Hat Enterprise linux Server release 5.3 (Tikanga)

一、測試環境準備
1.主庫和備庫oracle版本一致，執行在相同平臺如linux,伺服器硬體可以不一樣。
2.Primary 資料庫必須執行於歸檔模式，並且務必確保在primary 資料庫上開啟FORCE LOGGING，以避免使用者透過nologging 等方式避免寫redo 造成對應的操作無法傳輸到standby 資料庫。
3.Primary 和standby 資料庫均可應用於單例項或RAC 架構下，並且同一個data guard 配置可以混合使用邏輯standby 和物理standby.
4.建議資料庫必須採用相同的儲存架構。比如儲存採用ASM/OMF 的話，那不分primarty 或是standby也都需要採用ASM/OMF。
5.standby庫的環境和primary一致。

二、調整主庫
1.將主資料庫改為強制產生日誌模式
alter database force logging;
2. 建立密碼
orapwd file=$ORACLE_HOME/dbs/orapwtest passWord=test entries=3
3. 修改主庫的初始化引數
db_unique_name ='test01'
db_name指定資料庫的名稱，db_unique_name指定了資料庫（主庫和備庫）的不同名稱，是對db_name的唯一的標識。其實是為了區分db_name名一樣的資料庫，方便應用和管理。是10g以後的引數。
log_archive_config='DG_CONFIG=(test01,test02)'
test01,test02是.db_unique_name
log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test01'
log_archive_dest_2='SERVICE=test_db_02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test02'
確定log_archive_dest_state_1
log_archive_dest_state_1= enable #預設就是enable
log_archive_dest_state_2= enable
/*以下部分為主機切換為備庫使用*/
fal_server=test_db_02
fal_client=test_db_01
standby_file_management=auto
#如果主備庫檔案或路徑不同
db_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'
log_file_name_convert='/u01/app/oracle/oradata/test','/u01/app/oracle/oradata/test'

建議引數檔案使用spfile，這樣可以在不停庫使用命令修改引數。
4. 確保資料庫執行在archivelog模式下
SQL> archive log list     
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Current log sequence           4
把資料庫調整為archivelog模式下
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog ;
SQL> alter database open;

5.在主庫上建立備用日誌 （為切換而用）
alter database recover managed standby database cancel;#未執行
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

mkdir -p /u01/app/oracle/standby/test
SQL>alter database add standby logfile group 4('/u01/app/oracle/standby/test/stdby_redo04.log') size 50M;
SQL>alter database add standby logfile group 5('/u01/app/oracle/standby/test/stdby_redo05.log') size 50M;
SQL>alter database add standby logfile group 6('/u01/app/oracle/standby/test/stdby_redo06.log') size 50M;

SQL>alter database recover managed standby database disconnect from session;#未執行

三、建立standby database
1. 在standby database 建立pwdfile
orapwd file=$ORACLE_HOME/dbs/orapwtest password=test entries=3
密碼要與primary database相同
2. 修改standby database初始化引數
與primary database不同的引數如下:
db_unique_name=test02
log_archive_dest_1='location=/u01/app/oracle/arch/test valid_for=(all_logfiles,all_roles) db_unique_name=test02'
log_archive_dest_2='service=test_db_01 ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=test01'
fal_client='test_db_02'
fal_server='test_db_01'
db_file_name_convert='/u01/app/oracle/oradata/TEST01/datafile','/u01/app/oracle/oradata/TEST02/datafile'
log_file_name_convert='/u01/app/oracle/oradata/TEST01/onlinelog','/u01/app/oracle/oradata/TEST02/onlinelog', '/u01/app/oracle/Flash_recovery_area/TEST01/onlinelog','/u01/app/oracle/flash_recovery_area/TEST01/onlinelog' 

修改完之後用如下語句檢視
SQL> select * from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME
------------------------------------------------------------------------------------------
test01
test02

4.建立好相應目錄，dump檔案目錄，資料檔案目錄，歸檔目錄
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/flash_recovery_area
mkdir -p /u01/app/oracle/arch/test

5. 配置standby database的listener.ora 和tnsnames.ora檔案同主庫
重起監聽並檢驗是否可以tnsping通
6. 啟動standby database到nomount狀態
SQL> startup nomount
7. 用RMAN的duplicate功能建立standby database，11g可以不做備份直接複製到備庫：
RMAN> rman auxiliary /
connect target
duplicate target database for standby dorecover nofilenamecheck;

檢查standby database的狀態
SQL> select database_role,protection_mode,protection_level from v$database;
SQL> select sequence# ,applied from v$archived_log order by sequence#;
8.開始Redo應用
主庫
SQL> alter system archive log current;
備庫，11g可以只讀開啟庫應用日誌
SQL> alter database open read only;
SQL> alter database recover managed standby database disconnect from session;

也可以使用alter database recover managed standby database using current logfile disconnect from session;應用日誌。
沒有using current logfile是去apply standby archived log.
有using current logfile是當日志傳到standby redo log的時候就去應用，是real-time的，必須要有standby redo log.

四.備用伺服器的管理模式與只讀模式
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/test/temp01.dbf' size 100M;
5.從只讀方式到管理恢復方式
SQL> recover managed standby database disconnect from session;

五.主備切換
主庫
lsnrctl stop

SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;

lsnrctl start

備庫

SQL> alter database commit to switchover to primary;
SQL> shutdown immediate;
SQL> startup

六.附件
以下是配置檔案內容：
listener.ora

LISTENER =
  (DESCRipTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle6)(PORT = 1521))
  )

tnsnames.ora

TEST_DB_02 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.5)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test02)
    )
  )

TEST_DB_01 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.202.6)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test01)
    )
  )

pfile

test.__db_cache_size=1526726656
test.__java_pool_size=16777216
test.__large_pool_size=16777216
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=1342177280
test.__sga_target=2013265920
test.__shared_io_pool_size=0
test.__shared_pool_size=436207616
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl','/u01/app/oracle/oradata/test/control03.ctl'
*.db_block_size=8192
*.db_domain='greatsky.com'
*.db_name='test'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='test01'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.fal_client='TEST_DB_01'
*.fal_server='TEST_DB_02'
*.log_archive_config='dg_config=(test01,test02)'
*.log_archive_dest_1='location=/u01/app/oracle/arch/test/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test01'
*.log_archive_dest_2='SERVICE=test_db_02 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test02'
*.memory_target=3347054592
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='test.greatsky.com','test01.greatsky.com'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
-

資料引用: