SERVER_2008_64_oracle_11g DG搭建筆記

1、oracle DG主庫、備庫安裝路勁一樣C:UsersAdministrator>sqlplus /nolog SQL> conn /as sysdba 已連接。#關(guān)閉數(shù)據(jù)庫，將數(shù)據(jù)庫修改為日志記錄模式SQL> shutdown immediate;ORACLE 例程已經(jīng)關(guān)閉。SQL> startup mount; 數(shù)據(jù)庫裝載完畢。SQL> alter database archivelog; 數(shù)據(jù)庫已更改。SQL> alter database force logging; 數(shù)據(jù)庫已更改。 #添加數(shù)據(jù)庫日子記錄文件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; 數(shù)據(jù)庫已更改。#創(chuàng)建控制文件SQL> alter database create standby controlfile as 'D:APPADMINISTRATORORADATAYKTCMSstandby.ctl'

4、; 數(shù)據(jù)庫已更改。#創(chuàng)建參數(shù)文件SQL> create pfile='d:initora.ora' from spfile; 文件已創(chuàng)建。#編輯參數(shù)文件，initora.ora 添加DG 參數(shù)。添加內(nèi)容如下。#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#修改后，通過參數(shù)文件啟動(dòng)主庫SQL> shutdown immediate;

6、 ORA-01109: 數(shù)據(jù)庫未打開已經(jīng)卸載數(shù)據(jù)庫。ORACLE 例程已經(jīng)關(guān)閉。SQL> startup pfile='d:initora.ora' nomount;ORACLE 例程已經(jīng)啟動(dòng)。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 例程已經(jīng)關(guān)閉。SQL> startup;ORACLE 例程已經(jīng)啟動(dòng)。Total System Global Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytes Redo Buffers 5021696 bytes 數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。#創(chuàng)建密碼文件,文件在D:appAdministrat

8、orproduct11.2.0dbhome_1databasePWD(DBname).ora#如果不存在就用CMD 執(zhí)行命令創(chuàng)建,這個(gè)備庫會(huì)用到。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)聽里面的數(shù)據(jù)庫服務(wù) (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) ) )#啟動(dòng)監(jiān)聽程序,測試連接C:ls

11、nrctlLSNRCTL>start sqlplus sys/123456PRIMARY as sysdba#測試鏈接成功沒問題后，關(guān)閉數(shù)據(jù)庫。備庫配置好啟動(dòng)后。再啟動(dòng)主庫。SQL> shutdown immediate;-這里可以將數(shù)據(jù)庫文件夾先復(fù)制到備庫-#備庫操作：將主庫的文件復(fù)制備庫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 復(fù)制到d盤6、 PWD(DBname).ora 復(fù)制到對應(yīng)路徑里面去#創(chuàng)建主庫的監(jiān)聽LISTENER (跟主庫的一樣）#創(chuàng)建主庫的tnsnames(跟主庫的一樣）#啟動(dòng)監(jiān)聽程序,測試連接C:lsnrctlLSNRCTL>start#在windows上注冊oracle實(shí)例到服務(wù)C:>oradim -new -sid dg #修initora.ora改文件，注意將文件里面的控制文件路徑改成復(fù)制過來的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 例程已經(jīng)啟動(dòng)。Total System Global Area 263049

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

16、Area 263049216 bytesFixed Size 2174840 bytesVariable Size 230686856 bytesDatabase Buffers 25165824 bytesRedo Buffers 5021696 bytes#加截?cái)?shù)據(jù)庫為備用庫模式SQL> alter database mount standby database; 數(shù)據(jù)庫已更改。#關(guān)閉應(yīng)用重做SQL>#alter database recover managed standby database cancel; 數(shù)據(jù)庫已更改。 #開啟備用庫應(yīng)用重做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; 數(shù)據(jù)庫已更改。#已經(jīng)配置完成，并啟動(dòng)了備庫-已經(jīng)配置完成#切換到主庫。并啟動(dòng)SQL> startup-測試#在主庫截?cái)嗳罩?。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試在主、備庫之間是否能連接通。進(jìn)行排查問題。C:sqlplus sys/123456standby as sysdbaC:sqlplus sys/123456primary as sysdba-日常維護(hù)-#查看DG 狀態(tài)select name,open_mode,database_ro

19、le,protection_mode,SWITCHOVER_STATUS From v$database; -注意Data Guard 啟動(dòng)順序：啟動(dòng)順序：先standby ,后primary; 關(guān)閉順序：先primary 后standby;在備庫將實(shí)例啟動(dòng)到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; 在主庫將實(shí)例啟動(dòng)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' 最大保護(hù)模式：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' ; 最大保護(hù)模式：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; -#刪