SERVER_2008_64_oracle_11g DG搭建筆記

1、oracle DG主庫、備庫安裝路勁一樣C:UsersAdministrator>sqlplus /nolog SQL> conn /as sysdba 已連接。#關閉數據庫，將數據庫修改為日志記錄模式SQL> shutdown immediate;ORACLE 例程已經關閉。SQL> startup mount; 數據庫裝載完畢。SQL> alter database archivelog; 數據庫已更改。SQL> alter database force logging; 數據庫已更改。 #添加數據庫日子記錄文件SQL> select group#

2、,member from v$logfile order by group#;GROUP#-MEMBER1 D:ORACLEORADATADGREDO01.LOG2 D:ORACLEORADATADGREDO02.LOG3 D:ORACLEORADATADGREDO03.LOGSQL> alter database add standby logfile group 4 ('D:APPADMINISTRATORORADATAYKTCMSSTAN04.LOG') size 50m;SQL> alter database add standby logfile grou

3、p 5 ('D:APPADMINISTRATORORADATAYKTCMSSTAN05.LOG') size 50m;SQL> alter database add standby logfile group 6 ('D:APPADMINISTRATORORADATAYKTCMSSTAN06.LOG') size 50m; 數據庫已更改。#創(chuàng)建控制文件SQL> alter database create standby controlfile as 'D:APPADMINISTRATORORADATAYKTCMSstandby.ctl'

4、; 數據庫已更改。#創(chuàng)建參數文件SQL> create pfile='d:initora.ora' from spfile; 文件已創(chuàng)建。#編輯參數文件，initora.ora 添加DG 參數。添加內容如下。#DG_configdb_unique_name='primary' archive_lag_target=1800 FAL_SERVER=primary FAL_CLIENT=standbylog_archive_config='DG_CONFIG=(primary,standby)'log_archive_dest_1='L

5、OCATION=D:log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' LOG_ARCHIVE_DEST_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLE#修改后，通過參數文件啟動主庫SQL> shutdown immediate;

6、 ORA-01109: 數據庫未打開已經卸載數據庫。ORACLE 例程已經關閉。SQL> startup pfile='d:initora.ora' nomount;ORACLE 例程已經啟動。Total System Global Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytesSQL> create spfile from pfile='d:in

7、itora.ora' 文件已創(chuàng)建。SQL> shutdown immediate; ORA-01507: ?ORACLE 例程已經關閉。SQL> startup;ORACLE 例程已經啟動。Total System Global Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytes Redo Buffers 5021696 bytes 數據庫裝載完畢。數據庫已經打開。#創(chuàng)建密碼文件,文件在D:appAdministrat

8、orproduct11.2.0dbhome_1databasePWD(DBname).ora#如果不存在就用CMD 執(zhí)行命令創(chuàng)建,這個備庫會用到。C:>orapwd file=D:appAdministratorproduct11.2.0dbhome_1databasePWDprimary.ora password=123456 entries=10#創(chuàng)建主庫的監(jiān)聽LISTENERSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = D:ORACLEproduct11.2.0d

9、bhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:D:ORACLEproduct11.2.0dbhome_1binoraclr11.dll") )#主要用作監(jiān)聽里面的數據庫服務 (SID_DESC = (GLOBAL_DBNAME = dg) (ORACLE_HOME = D:ORACLEproduct11.2.0dbhome_1) (SID_NAME = dg) ) )#創(chuàng)建主庫的tnsnames(主庫備庫都填寫上)STANDBY = (DESCRIPTION = (ADDRESS_LIST = (ADDRES

10、S = (PROTOCOL = TCP)(HOST = 192.168.18.1)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg) ) )PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.18.253)(PORT = 1521) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dg) ) )#啟動監(jiān)聽程序,測試連接C:ls

11、nrctlLSNRCTL>start sqlplus sys/123456PRIMARY as sysdba#測試鏈接成功沒問題后，關閉數據庫。備庫配置好啟動后。再啟動主庫。SQL> shutdown immediate;-這里可以將數據庫文件夾先復制到備庫-#備庫操作：將主庫的文件復制備庫1、 將D:oracle目錄下admin、cfgtollogs、 diag和flash_recovery_area拷至備用庫 的D:ORACLE目錄下2、 將D:oracleoradatadg 拷貝到D:ORACLEoradatadg3、 將e:log目錄拷至d盤下4、 將standby.ctl

12、拷至D:ORACLEoradatadg目錄下.5、 initora.ora 復制到d盤6、 PWD(DBname).ora 復制到對應路徑里面去#創(chuàng)建主庫的監(jiān)聽LISTENER (跟主庫的一樣）#創(chuàng)建主庫的tnsnames(跟主庫的一樣）#啟動監(jiān)聽程序,測試連接C:lsnrctlLSNRCTL>start#在windows上注冊oracle實例到服務C:>oradim -new -sid dg #修initora.ora改文件，注意將文件里面的控制文件路徑改成復制過來的standby.ctl#DG_config db_unique_name='standby' ar

13、chive_lag_target=1800 FAL_SERVER=standby FAL_CLIENT=primarylog_archive_config='DG_CONFIG=(primary,standby)'log_archive_dest_1='LOCATION=D:log VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'LOG_ARCHIVE_DEST_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMAR

14、Y_ROLE) DB_UNIQUE_NAME=primary'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLESTANDBY_FILE_MANAGEMENT=AUTOC:>set oracle_sid=dgC:>sqlplus /nolog SQL> conn /as sysdba 已連接到空閑例程。SQL> startup nomount pfile='D:initora.ora'ORACLE 例程已經啟動。Total System Global Area 263049

15、216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytes#啟動節(jié)點SQL> create spfile from pfile='d:initora.ora' 文件已創(chuàng)建。SQL> shutdown immediate; ORA-01507: ?ORACLE 例程已經關閉。 SQL> startup nomount;ORACLE 例程已經啟動。Total System Global

16、Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytes#加截數據庫為備用庫模式SQL> alter database mount standby database; 數據庫已更改。#關閉應用重做SQL>#alter database recover managed standby database cancel; 數據庫已更改。 #開啟備用庫應用重做SQL>#alter d

17、atabase recover managed standby database disconnect from session;SQL>alter database recover managed standby database using current logfile disconnect from session; 數據庫已更改。#已經配置完成，并啟動了備庫-已經配置完成#切換到主庫。并啟動SQL> startup-測試#在主庫截斷日志。SQL> alter system switch logfile; #查詢主庫的日志SEQSQL> select max(s

18、equence#) from v$archived_log;MAX(SEQUENCE#)- 21#切換到備庫執(zhí)行，看SEQ是否同步。SQL> select max(sequence#) from v$archived_log;MAX(SEQUENCE#)- 21#如果雙方日子文件d:log文件夾不同步?？蓽y試在主、備庫之間是否能連接通。進行排查問題。C:sqlplus sys/123456standby as sysdbaC:sqlplus sys/123456primary as sysdba-日常維護-#查看DG 狀態(tài)select name,open_mode,database_ro

19、le,protection_mode,SWITCHOVER_STATUS From v$database; -注意Data Guard 啟動順序：啟動順序：先standby ,后primary; 關閉順序：先primary 后standby;在備庫將實例啟動到mount 狀態(tài)：SQL> startup nomount;SQL>alter database mount standby database ; SQL>alter database recover managed standby database using current logfile disconnect fr

20、om session; 在主庫將實例啟動SQL> startup-#查詢dg模式SQL> select protection_mode,protection_level from v$database; #模式切換SQL> shutdown immediateSQL> startup mount; SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=

21、standby' 最大保護模式：alter database set standby database to maximize protection; 最大可用模式：alter database set standby database to maximize availability; 最大性能模式：alter database set standby database to maximize PERFORMANCE; SQL> alter database open; #備庫SQL> shutdown immediateSQL> startup nomount;

22、SQL> alter database mount standby database;SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' ; 最大保護模式：alter database set standby database to maximize protection; 最大可用模式：alter database set standby database to maximize availability; 最大性能模式：alter database set standby database to maximize PERFORMANCE; SQL>alter database recover managed standby database disconnect from session; -#刪