此Data Guard Step by Step共分五篇
第一篇 本地資料庫建置
第二篇 本地Data Guard建置
第三篇 異地Data Guard建置
第四篇 驗證DATA GUARD
第五篇 本地資料庫切換到異地DATA GUARD
版主盡量做到照這文件就可以快快樂樂建置Data Guard，但中間還是有許多相關知識須具備，若有任何問題歡迎提出討論
--第一篇 本地資料庫建置
--資料庫環境配置
--主要資料庫：IP 192.168.1.122，電腦名稱 LOCALDB，db_unique_name=myorcl
--本地Data Guard資料庫：IP 192.168.1.123，電腦名稱 DGLOCAL，db_unique_name=myorcl_STBY
--異地Data Guard資料庫：IP 192.168.1.124，電腦名稱 DGDR，db_unique_name=DR_myorcl_STBY
--ORACLE軟體安裝在c:\oracle\ora10g
--ORACLE DB在d:\oracledb
--確認archive log是否開啟(本環境已開啟)
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
--確認FORCE LOGGING模式是否開啟(本環境已開啟)
SQL> SELECT force_logging FROM v$database;
FOR
---
YES
--建立STANDBY LOGFILE(依據REDO LOG建立相同數量的STANDBY REDO LOG檔案)
--查目前REDO LOG數量
SQL> select group#,member from v$logfile order by group#;
GROUP# MEMBER
--------------------------------------------------------------------------------
1 D:\ORACLEDB\ORADATA\myorcl\REDO01.LOG
2 D:\ORACLEDB\ORADATA\myorcl\REDO02.LOG
3 D:\ORACLEDB\ORADATA\myorcl\REDO03.LOG
4 D:\ORACLEDB\ORADATA\myorcl\REDO04.LOG
--建立STANDBY REDO LOG
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5('D:\ORACLEDB\ORADATA\REDO05.LOG')size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6('D:\ORACLEDB\ORADATA\REDO06.LOG')size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7('D:\ORACLEDB\ORADATA\REDO07.LOG')size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 8('D:\ORACLEDB\ORADATA\REDO08.LOG')size 50M;
--初始化參數
--檢查db_name參數(主要與備援的db_name需相同)
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ----------
db_name string myorcl
--檢查db_unique_name參數
SQL> show parameter db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ---------
db_unique_name string myorcl
--設定log_archive_config參數(主要與備援的db_unique_name需不同，主要:myorcl，本地備援:myorcl_STBY，異地備援:DR_myorcl_STBY)
SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(myorcl,myorcl_STBY,DR_myorcl_STBY)';
--設定本地備援remote archive log參數，若有快速恢復區(fast recovery area)建議可使用FRA
SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=myorcl_STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=myorcl_STBY';
--設定異地備援remote archive log參數，若有快速恢復區(fast recovery area)建議可使用FRA
SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE=DR_myorcl_STBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DR_myorcl_STBY';
--檢查log_archive_dest_state_2參數(VALUE需為enable)
SQL> show parameter log_archive_dest_state_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string enable
--檢查log_archive_dest_state_3參數(VALUE需為enable)
SQL> show parameter log_archive_dest_state_3;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3 string enable
--檢查log_archive_format參數
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
log_archive_format string ARC%S_%R.%T
--變更log_archive_max_processes參數由2改為10(10g預設為2，11g預設為4，最大可設定到30)
SQL> alter system set log_archive_max_processes=10;
--檢查remote_login_passwordfile參數(remote_login_passwordfile必須設定為獨佔(EXCLUSIVE))
SQL> show parameter remote_login_passwordfile;
NAME TYPE VALUE
------------------------------------ ----------- -----------
remote_login_passwordfile string EXCLUSIVE
--設定FAL_SERVER參數
SQL> ALTER SYSTEM SET FAL_SERVER=myorcl_STBY,DR_myorcl_STBY;
--設定FAL_CLIENT參數
SQL> ALTER SYSTEM SET FAL_CLIENT=myorcl;
--變更STANDBY_FILE_MANAGEMENT參數(MANUAL改為AUTO)
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
--資料庫服務下上，讓設定的參數生效
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
--主要資料庫tnsnames配置(tnsnames.ora)
# tnsnames.ora Network Configuration File: C:\oracle\ora10g\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
myorcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myorcl)
)
)
DR_myorcl_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.124)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myorcl)
)
)
myorcl_STBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.123)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myorcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--主要資料庫listener配置(listener.ora)
# listener.ora Network Configuration File: C:\oracle\ora10g\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = myorcl)
(ORACLE_HOME = C:\oracle\ora10g)
(SID_NAME = myorcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.122)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--LISTENER服務下上
C:\Windows\system32>lsnrctl stop
C:\Windows\system32>lsnrctl start
--在主要資料庫上建立備援機所需的Controlfile
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'C:\DG_TEMP\CONTROLxx.CTL';
--在主要資料庫上建立備援機所需的PFILE
SQL> CREATE PFILE='C:\DG_TEMP\PFILEmyorcl.ORA' FROM SPFILE;
--備份資料庫
C:\Windows\system32>rman target /
RMAN> BACKUP DATABASE FORMAT='C:\DG_TEMP\%d_%s.bak' PLUS ARCHIVELOG;
第二篇 本地Data Guard建置
第三篇 異地Data Guard建置
第四篇 驗證DATA GUARD
第五篇 本地資料庫切換到異地DATA GUARD
沒有留言:
張貼留言