oracle-11g-Dataguard詳細(xì)配置手冊

1、Oracle 11G Dataguard詳細(xì)配置手冊版本： V1 作者：知行合一、環(huán)境描述：主庫：IP 地址： 7OS：RHEL 6.5 X64Hostname :lin-1Oracle 版本： Oracle_home: /u01/app/oracle/product/11.2.0/db_1SQL show parameter name;NAMETYPEVALUEdb_ db_namestringdb_unique_name global_names instance_name lock_name_space log_ service_namesstrin

2、g string boolean string stringorclorclFALSE orclstringstringorcl備庫：IP 地址： 8OS：RHEL 6.5 X64Hostname :lin-2Oracle 版本： Oracle_home: /u01/app/oracle/product/11.2.0/db_1SQL show parameter name;NAMETYPEVALUEdbstringdb_namedb_unique_nameglobal_namesstring string booleanorcl orcl2FALSEins

3、tance_name lock_name_space log_ service_namesstringstringorclstringstringorcl說明： 由于我采用的是 vmware 虛擬化環(huán)境進(jìn)行測試的。 測試時首先安裝好一臺數(shù)據(jù)庫， 然 后再克隆出另外一套。第二套根據(jù)需要，對主機(jī)名、 IP 地址進(jìn)行更改。另外不要忘記更換 oracle 用戶的環(huán)境變量、更改 /etc/hosts 解析文件 注意：主庫和備庫的 db_unique_name 不能相同二：修改主備庫 listener.ora ， tnsnames.ora2.1、主庫配置2.1.1 、配置主庫的監(jiān)聽：oraclelin-1

4、 admin$ vi listener.ora(G# listener.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST = (SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = o

5、rcl)LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521) )ADR_BASE_LISTENER = /u01/app/oracle2.1.2 、修改主庫的 TnsnameORCL =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521) (CONNECT_DATA =(SERV

6、ER = DEDICATED)(SERVICE_NAME = orcl)ORCL2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-2)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)2.2、備庫配置2.2.1 、配置備庫的監(jiān)聽監(jiān)聽： oraclelin-2 admin$ cat listener.oraFile:# listener.ora Network Configuration/u01/app/oracle/product/11.2.

7、0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST = (SID_DESC =(GLOBAL_DBNAME = orcl)(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = orcl)LISTENER =(DESCRIPTION_LIST = (DESCRIPTION =(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521) (

8、ADDRESS = (PROTOCOL = TCP)(HOST = lin-2)(PORT = 1521) )ADR_BASE_LISTENER = /u01/app/oracle2.1.2 、修改備庫的 TnsnameORCL = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = lin-1)(PORT = 1521)(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)ORCL2 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = l

9、in-2)(PORT = 1521) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl2)、主庫前期準(zhǔn)備設(shè)置強(qiáng)制寫日志SQL select FORCE_LOGGING from v$database; NOSQL alter database force logging;SQL select FORCE_LOGGING from v$database; YES四 、 創(chuàng)建口令文件orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5五、修改主庫初始化參數(shù)創(chuàng)建主

10、庫 pfilesql create pfile from spfile;修改主庫 pfile ： 在 pfile 中添加如下內(nèi)容： DB_UNIQUE_NAME=orclLOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcl2)VALID_FOR=(ALL_LOG)LOG_ARCHIVE_DEST_1=LOCATION=/u01/app/archiveDB_UNIQUE_NAME=orclLOG_ARCHIVE_DEST_2= SERVICE=orcl2DB_UNIQUE_NAME=orcl2LGWR ASYNC VALID_FOR=(ONLINE_LOG)No Arc

11、hive ModeDisabledArchive destinationUSE_DB_RECOVERY_LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLELOG_ARCHIVE_FORMAT=%t_%s_%r.arcFAL_SERVER=orcl2FAL_CLIENT=orclSTANDBY 把修改后的 pfile 也拷貝到備庫上：scp-p/uO1/a pp/oracle/product/11.2.0/db_1/dbs/i nitorcl.oralin-2:/u01/app/oracle/product/11.2.0/

12、db_1/dbs/ 注意：根據(jù)實(shí)際情況更改用戶的環(huán)境變量六.修改主庫運(yùn)行在歸檔模式下SQLarchive log list; Database log mode Automatic archivalOldest online log sequenceCurrent log sequenceSQLalter system set LOG_ARCHIVE_DEST_1= LOCATION=/u01/app/archive;System altered.SQLshutdown immediate;Database closed.Database dismounted.ORACLE instance

13、shut down.SQL startup mount;ORACLE instance started.Total System Global Area 1570009088 bytes Database mounted.Fixed SizeVariable SizeDatabase BuffersRedo Buffers2213696 bytes922749120 bytes637534208 bytes7512064 bytesSQL alter database archivelog;Database altered.SQLalter database open;Database alt

14、ered.SQL七 . 創(chuàng)建備份庫需要的控制文件在主庫創(chuàng)建備庫控制文件 注：以下操作在主庫上進(jìn)行SQLShutdown immediate;SQLSTARTUP MOUNT;SQLALTER DATABASE CREATE STANDBY CONTROLFILE AS /tmp/orcl.ctl;SQLALTER DATABASE OPEN;創(chuàng)建主庫 spfileSQLShutdown immediateSQLstartup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.orasql create sp pfile=/u01/ap

15、p/oracle/product/11.2.0/db_1/dbs/initorcl.ora;關(guān)閉數(shù)據(jù)庫sql shutdown immediate;八 . 備份生產(chǎn)數(shù)據(jù)庫并把數(shù)據(jù)復(fù)制到對端說明： 這里采用關(guān)閉主庫數(shù)據(jù)庫， 進(jìn)行冷備份的方法進(jìn)行備份， 數(shù)據(jù)庫的控制文件和數(shù)據(jù)文 件全部位于 /u01/app/oracle/oradata/orcl/ 目錄scp -rp /u01/app/oracle/oradata/orcl/* lin-2:/u01/app/oracle/oradata/orcl/九 .修改備庫 pfileoraclelin-2 dbs$ cat initorcl.ora orc

16、l._db_cache_size=637534208 orcl._java_pool_size=16777216orcl._large_pool_size=16777216orcl._oracle_base=/u01/app/oracle#ORACLE_BASE set from environment orcl._pga_aggregate_target=637534208orcl._sga_target=939524096orcl._shared_io_pool_size=0orcl._shared_pool_size=251658240orcl._streams_pool_size=0

17、*.audit_/u01/app/oracle/admin/orcl/adump*.audit_trail=db *.compatible=.0*.control_files=/u01/app/oracle/oradata/orcl/control01.ctl*.db_block_size=8192 *.db_domain= *.db_name=orcl *.db_recovery_/u01/app/oracle/flash_recovery_area *.db_recovery_*.diagnostic_dest=/u01/app/oracle *.dispatchers=(

18、PROTOCOL=TCP) (SERVICE=orclXDB)*.memory_target=1562378240 *.open_cursors=300*.processes=150*.remote_login_passwordfile=EXCLUSIVE *.undo_tablespace=UNDOTBS1 *.DB_UNIQUE_NAME=orcl2 *.LOG_ARCHIVE_CONFIG=DG_CONFIG=(orcl,orcl2)*.LOG_ARCHIVE_DEST_1=DB_UNIQUE_NAME=orcl2VALID_FOR=(ALL_LOG)*.LOG_ARCHIVE_DEST

19、_2=DB_UNIQUE_NAME=orclSERVICE=orclLGWR ASYNC VALID_FOR=(ONLINE_LOG)*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc*.FAL_SERVER=orcl*.FAL_CLIENT=orcl2*.STANDBY、將控制文件傳輸?shù)綄Χ嗣婷钤谥鲙焐蠄?zhí)行：scp -rp /tmp/orcl.ctl lin-2:/u01/app/oracle/oradata/orcl/傳輸?shù)綄Χ撕螅⒏目刂?/p>

20、文件的名稱LOCATION=/u01/app/archive在備庫上修改名稱為 control01.ctoraclelin-2 orcl$ mv orcl.ctl control01.ctloraclelin-2 orcl$ lscontrol01.ctl redo02.log sysaux01.dbf temp01.dbf users01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf、在備庫上創(chuàng)建口令文件orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle entries=5、

21、在備庫上創(chuàng)建 spfile在備庫上，根據(jù)第九章創(chuàng)建的 pfile 生成 spfileSQLShutdown immediateSQLstartup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.oraSQLCREATE SPFILE FROM PFILE;三、啟動物理備用數(shù)據(jù)庫SQLSTARTUP MOUNT;四、配置 Standby Redo Log在兩邊都配置 standby redo log 在主庫查看日志組的數(shù)量和每個日志文件的大小 SQL SELECT GROUP#, BYTES FROM V$LOG;在備庫庫查看日志組

22、的數(shù)量和每個日志文件的大小SQL SELECT GROUP#, BYTES FROM V$STANDBY_LOG;在主備庫上創(chuàng)建日志組和 redo log 文件SIZE 50M;SIZE 50M;SIZE 50M;SQLALTERDATABASEADDSTANDBYLOG4(/u01/app/oracle/oradata/orcl/stdby_redo04.log)SQLALTERDATABASEADDSTANDBYLOG5(/u01/app/oracle/oradata/orcl/stdby_redo05.log)SQLALTERDATABASEADDSTANDBYLOG6(/u01/app

23、/oracle/oradata/orcl/stdby_redo06.log)五、配置閃回?cái)?shù)據(jù)庫說明： 這一步為可選操作， 但強(qiáng)烈建議開啟數(shù)據(jù)庫閃回功能。 閃回允許你將數(shù)據(jù)庫還原到以 前的某一時間點(diǎn)。 當(dāng)發(fā)生故障轉(zhuǎn)移時， 這個功能非常有用， 它能讓你將老的主庫閃回到故障 前，然后將其轉(zhuǎn)換為備庫。如果沒有啟用閃回功能，你就必須重建備庫，意味著要再復(fù)制一 次數(shù)據(jù)文件。除了這個好處，閃回還能在某些情況下讓你避免從備份恢復(fù)數(shù)據(jù)。新路徑 ;(1) 快速恢復(fù)區(qū) (Flash/Fast Recovery Area) ，默認(rèn)是配置的 , 但是需要確認(rèn)這個區(qū)域的磁盤 夠大，至少 50G 以上(默認(rèn) 3G) sql

24、 show parameter db_recovery_ 可以修改位置： sql alter system set db_recovery_ 更改大?。?sql alter system set db_recovery_G;(2) 查看是否啟用，默認(rèn)是不開啟的 sql select flashback_on from v$database; 開啟閃回： sql alter database flashback on; 如果你碰到 ORA-01153 報(bào)錯，那一定是在備庫進(jìn)行此操作。你需要先取消重做日志應(yīng)用， 啟用閃回日志，然后重新啟用日志應(yīng)用。 在主庫啟用閃回日志，不會同步備庫也啟用。你必須手

25、動在主庫和備庫上均啟用閃回日志。 如果不啟用閃回日志，當(dāng)出現(xiàn)故障轉(zhuǎn)移時，你將需要完全重新開始創(chuàng)建一個備庫。六、Start Redo Apply在備庫上執(zhí)行：sql ALTERDATABASERECOVERMANAGESDTANDBYDATABASEUSINGCURRENLTOGFROMSESSION;查看哪些歸檔日志被 APPLY了在備庫上執(zhí)行：sql SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;在主庫強(qiáng)制日志切換到當(dāng)前的 online redo log file. sql ALTER SYSTEM ARCHI

26、VE LOG CURRENT; 在備庫查看新的被歸檔的 redo data sql SELECTSEQUENCE#F,IRST_TIME, NEXT_TIMEFROMV$ARCHIVED_LOOGRDERBY SEQUENCE#; 在備庫查看接收到的被應(yīng)用的 redoSQL SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; 查看數(shù)據(jù)庫的角色sql select database_role,protection_mode,protection_level from v$database;附件：日常維護(hù)主備庫切換1.

27、查看主庫的狀態(tài)SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;2. 將主庫切換至備用模式SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH - SESSION SHUTDOWN;3. 關(guān)閉、裝載主數(shù)據(jù)庫SQL SHUTDOWN ABORT;SQL STARTUP MOUNT;4. 查看備庫準(zhǔn)備向主庫模式切換SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUSTO_PRIMARY 1 row select

28、ed5. 切換備庫至主庫模式SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;6. 打開新的主數(shù)據(jù)庫SQL ALTER DATABASE OPEN;7. 在新的備庫服務(wù)器上啟動 REDO apply 。SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE - DISCONNECT FROM SESSION;災(zāi)難恢復(fù)( failover )Step 1 Flush any unsent redo from t

29、he primary database to the target standby databaseSQL ALTER SYSTEM FLUSH REDO TO target_db_name;Step 2 Verify that the standby database has the most recently archived redo log each primary database redo thread.SQL SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) - OVER (PARTITION BY thread#) AS LAST

30、from V$ARCHIVED_LOG;SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE ;Step 3 Identify and resolve any archived redo log gaps.SQL SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;SQL ALTER DATABASE REGISTER PHYSICAL LOGFILE ;Step 4 Repeat Step 3 until all gaps are resolved.Step 5 Stop Red

31、o Apply.Issue the following SQL statement on the target standby database:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;Step 6 Finish applying all received redo data.Issue the following SQL statement on the target standby database:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;Step 7 Verify that the target standby database is ready