redhat6 + 11G DG部署
在主庫中netca配置
?
[oracle@HE3dbs]$?cat?/u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora #listener.ora?Network?Configuration?File:/u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora #Generated?by?Oracle?configuration?tools. SID_LIST_LISTENER=?(SID_LIST?=(SID_DESC?=(GLOBAL_DBNAME?=?pri)(ORACLE_HOME?=/u01/app/oracle/product/11gr2/db_1)(SID_NAME?=?orcl)))LISTENER=(DESCRIPTION?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE3)(PORT?=?1521)))ADR_BASE_LISTENER=?/u01/app/oracle[oracle@HE3dbs]$?cat?/u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora #tnsnames.ora?Network?Configuration?File:/u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora #Generated?by?Oracle?configuration?tools. PRIME?=(DESCRIPTION?=(ADDRESS_LIST?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE3)(PORT?=?1521)))(CONNECT_DATA?=(SERVICE_NAME?=?pri)))STDBY?=(DESCRIPTION?=(ADDRESS_LIST?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE4)(PORT?=?1521)))(CONNECT_DATA?=(SERVICE_NAME?=?std)))?
在備庫中netca配置
[oracle@HE4dbs]$?cat?/u01/app/oracle/product/11gr2/db_1/network/admin/listener.ora SID_LIST_LISTENER=(SID_LIST?=(SID_DESC?=(GLOBAL_DBNAME?=?std)(ORACLE_HOME?=/u01/app/oracle/product/11gr2/db_1)(SID_NAME?=?orcl)))LISTENER=(DESCRIPTION?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE4)(PORT?=?1521)))ADR_BASE_LISTENER=?/u01/app/oracle[oracle@HE4dbs]$?cat?/u01/app/oracle/product/11gr2/db_1/network/admin/tnsnames.ora PRIME?=(DESCRIPTION?=(ADDRESS_LIST?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE3)(PORT?=?1521)))(CONNECT_DATA?=(SERVICE_NAME?=?pri)))STDBY?=(DESCRIPTION?=(ADDRESS_LIST?=(ADDRESS?=?(PROTOCOL?=?TCP)(HOST?=HE4)(PORT?=?1521)))(CONNECT_DATA?=(SERVICE_NAME?=?std)))?
?
在主備庫分別執行
[oracle@HE3dbs]$?tnsping?prime [oracle@HE3dbs]$?tnsping?stdby [oracle@HE4dbs]$?tnsping?prime [oracle@HE4dbs]$?tnsping?stdby若ping不通，重啟監聽服務試試
配置主庫為歸檔模式
SQL>?archive?loglist Database?logmode???????????????Archive?Mode Automaticarchival???????????????Enabled Archivedestination??????????????/u01/app/oracle/product/11gr2/db_1/dbs/arch Oldest?online?logsequence?????52 Next?log?sequence?toarchive???54 Current?logsequence???????????????54更改主庫為強制寫日志
SQL>?alter?databaseforce?logging; 數據庫已更改。SQL>?selectforce_logging?from?v$database; FORCE_LOGGING --------------- YES主庫中創建initorcl.ora文件 SQL>?create?pfilefrom?spfile;? 文件已創建。 pfile文件位于/u01/app/oracle/product/11gr2/db_1/dbs 編輯initdb1.ora中的參數 [oracle@HE3?dbs]$cat?initorcl.ora orcl.__db_cache_size=440401920 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset?from?environment orcl.__pga_aggregate_target=213909504 orcl.__sga_target=633339904 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=176160768 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)' *.open_cursors=300 *.pga_aggregate_target=210763776 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=633339904 *.undo_tablespace='UNDOTBS1'*.DB_UNIQUE_NAME=pri? *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,std)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/11gr2/db_1/dbs/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)?DB_UNIQUE_NAME=pri' *.LOG_ARCHIVE_DEST_2='SERVICE=stdby?LGWR?SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)?DB_UNIQUE_NAME=std' *.LOG_ARCHIVE_MAX_PROCESSES=5 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER=stdby *.FAL_CLIENT=prime *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl' *.STANDBY_FILE_MANAGEMENT=AUTO?
上一步中pfile的內容改變了，相應地要改變spfile的內容。方法是新建一個spfile。
SQL>?shutdownimmediate SQL>?startuppfile='/u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora' SQL>?createspfile?from?pfile;?
在主庫上創建備庫需要的控制文件
SQL>?shutdownimmediate SQL>?startupmount? SQL>?alterdatabase?create?standby?controlfile?as?'/tmp/standby.ctl'; SQL>?shutdownimmediate?
備份生產數據庫
（1）在備庫中創建oradata目錄和admin目錄
[oracle@HE4~]$ mkdir /u01/app/oracle/oradata
[oracle@HE4~]$ mkdir /u01/app/oracle/admin
（2）將主庫數據庫文件拷貝到備庫機器上
[oracle@HE3~]$ scp -rp /u01/app/oracle/oradata/orcl HE4:/u01/app/oracle/oradata
[oracle@HE3~]$ scp -rp /u01/app/oracle/admin/orcl HE4:/u01/app/oracle/admin
（3）將主庫的密碼文件和pfile文件拷貝到備庫
[oracle@HE3~]$ cd $ORACLE_HOME/dbs
[oracle@HE3dbs]$orapwd?file=orapwdb1?password=MANAGER?force=y?ignorecase=y
[oracle@HE3dbs]$ scp -rp /u01/app/oracle/product/11gr2/db_1/dbs/orapworcl initorcl.oraHE4:/u01/app/oracle/product/11gr2/db_1/dbs
替換備庫的控制文件
（1）在備庫上刪掉數據庫所有的控制文件
[oracle@HE4~]$ rm -rf /u01/app/oracle/oradata/orcl/control0*.ctl
（2）將主庫備份的控制文件拷貝到備份機器上,并復制一個（要與pfile中的*.control_files參數一致）
[oracle@HE3dbs]$ scp -rp /tmp/standby.ctl HE4:/u01/app/oracle/oradata/orcl/control01.ctl
[oracle@HE4orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@HE4orcl]$ cp control01.ctl control02.ctl
修改備庫pfile
[oracle@HE4orcl]$?cat?/u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora orcl.__db_cache_size=440401920 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASEset?from?environment orcl.__pga_aggregate_target=213909504 orcl.__sga_target=633339904 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=176160768 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='orcl' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)' *.open_cursors=300 *.pga_aggregate_target=210763776 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=633339904 *.undo_tablespace='UNDOTBS1'*.DB_UNIQUE_NAME=std *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(pri,std)' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/11gr2/db_1/dbs/archVALID_FOR=(ALL_LOGFILES,ALL_ROLES)?DB_UNIQUE_NAME=std' *.LOG_ARCHIVE_DEST_2='SERVICE=prime?LGWR?SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)?DB_UNIQUE_NAME=pri' *.LOG_ARCHIVE_MAX_PROCESSES=5 *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER=prime *.FAL_CLIENT=stdby *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl' *.STANDBY_FILE_MANAGEMENT=AUTO?
注：
#*.DB_FILE_NAME_CONVERT='/路徑','路徑'??? （前為切換后的主庫路徑，后為切換后的備庫路徑，這里主備庫目錄結構完全一樣，則無需設定）
#*.LOG_FILE_NAME_CONVERT='/路徑','/路徑'?????（同上，這兩個名字轉換參數是主備庫的路徑映射關系，可能會是路徑全名，看情況而定）
?
在備庫上創建spfile
[oracle@HE4?dbs]$?sqlplus?/?as?sysdba?(如果報錯看右邊,有可能是備庫沒停干凈或者沒卸載干凈導致） 創建備庫spfile? SQL>startup?pfile='/u01/app/oracle/product/11gr2/db_1/dbs/initorcl.ora' SQL>create?spfile?from?pfile;配置standby redolog
（最佳性能模式可以忽略，如果要轉為其它兩種模式則要建立）
[oracle@HE3dbs]$ mkdir /u01/app/oracle/oradata/orcl/standby
[oracle@HE4dbs]$ mkdir /u01/app/oracle/oradata/orcl/standby
在主庫查看日志組的數量和每個日志文件的大小
SQL> SELECT GROUP#, BYTESFROM V$LOG; (這里已經有三個了，所以從group4開始加）
在備庫查看日志組的數量和每個日志文件的大小
SQL> SELECT GROUP#,BYTES FROM V$STANDBY_LOG;
?
在主庫和備庫中創建日志組和redo log文件
SQL>ALTER?DATABASE?ADD?STANDBY?LOGFILE?group?4('/u01/app/oracle/oradata/orcl/standby/slog1.rdo')?SIZE?50M;SQL>?ALTER?DATABASE?ADD?STANDBY?LOGFILEgroup?5?('/u01/app/oracle/oradata/orcl/standby/slog2.rdo')?SIZE?50M; SQL>ALTER?DATABASE?ADD?STANDBY?LOGFILE?group?6('/u01/app/oracle/oradata/orcl/standby/slog3.rdo')?SIZE?50M; SQL>ALTER?DATABASE?ADD?STANDBY?LOGFILE?group?7('/u01/app/oracle/oradata/orcl/standby/slog4.rdo')?SIZE?50M;注：standby redolog的組數參考公式：(online redolog組數 + 1) * 數據庫線程數；單機線程數為1，RAC一般為2。
standby redolog的組成員數和大小也盡量和online redolog一樣。
?
在備庫上執行備庫恢復模式
SQL>?selectinstance_name,status?from?v$instance;? INSTANCE_NAME???STATUS? ----------------------------? db1??????????????OPEN?SQL>?alter?databaserecover?managed?standby?database?disconnect?from?session;?或者 ALTER?DATABASERECOVER?MANAGED?STANDBY?DATABASE?USING?CURRENT?LOGFILE?DISCONNECT?FROM?SESSION;將備庫啟動到open?read?only的狀態? SQL>?shutdownimmediate? SQL>?startup?mount? SQL>?alter?databaseopen?read?only;? 如需結束 結束這個恢復進程：? SQL>?alter?databaserecover?managed?standby?database?cancel;?將備庫啟動到open?read?only的狀態? SQL>?shutdownimmediate? SQL>?startup?mount? SQL>?alter?databaseopen?read?only;查看主庫和備庫的日志同步情況，確保已經同步
主庫：
SQL> archive loglist
數據庫日志模式???????????存檔模式
自動存檔????????????啟用
存檔終點???????????/u01/app/oracle/oradata/archive
最早的聯機日志序列????14
下一個存檔日志序列?? 16
當前日志序列??????????16
?
備庫：
SQL> archive loglist
數據庫日志模式???????????存檔模式
自動存檔????????????啟用
存檔終點???????????/u01/app/oracle/oradata/archive
最早的聯機日志序列????14
下一個存檔日志序列?? 0
當前日志序列??????????16
?
檢驗
檢驗方法：在主庫中創建一張表，看在備庫中是否可以查到。
?
1 在主庫中創建一張表
SQL>
?create?tabletest_table(id?number);? 表已創建?SQL>?alter?databaserecover?managed?standby?database?using?current?logfile?disconnect?from?session; 數據庫已更改? 因為是max?performance模式，所以有歸檔才能看到，可以手動切歸檔。看備庫上是否可以查到SQL>?desctest_table;?名稱??????????????????????????????????????是否為空??類型?-------------------------------------------------?----------------------------?ID????????????????????????????????????????????????NUMBER?3?再次驗證數據是否同步。? 主庫：? SQL>?insert?intotest_table?values(1);? 已創建?1?行。? SQL>?commit;? 提交完成。?備庫：? SQL>?select?*?fromtest_table;?ID? ----------?1?
這里數據實現了同步，說明dataguard配置成功了。
?
總結
以上是生活随笔為你收集整理的redhat6 + 11G DG部署的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 将EXCEL中的列拼接成SQL inse
- 下一篇： PHPExcel 常用方法