oracle學(xué)習(xí)資料.doc

OSDBSIDIP主庫windows2003ORACLE 10GORCL01備庫windows2003ORACLE 10G無02ORACLE 10G DATAGUARD 配置物理STANDBY 1、設(shè)置主數(shù)據(jù)庫為force logging 模式cmd sqlplus / nolog;SQL conn / as sysdba;SQLalter database force logging;2、設(shè)置主數(shù)據(jù)庫為歸檔模式SQL archive log listSQL shutdown immediateSQL startup mountSQL alter database archivelog;SQL archive log list3、數(shù)據(jù)文件拷貝數(shù)據(jù)文件的拷貝可以分為兩種模式：rman 和文件的直接拷貝，由于我們的系統(tǒng)可以停機(jī)，所以會采用第二種方式。注意不用拷貝RED 文件，備用機(jī)在啟動的時候會自動的創(chuàng)建RED 文件。3.1、拷貝主庫的數(shù)據(jù)文件到備庫中SQL SELECT NAME FROM v$datafile;SQL select * from v$tempfile;把主庫的以上數(shù)據(jù)文件（D:oracleproduct10.2.0oradataorac）拷貝到備庫上同樣目錄3.2、創(chuàng)建備庫的控制文件在主庫上為從庫創(chuàng)建控制文件和初始化參數(shù)文件,然后拷貝到從庫D:oracleproduct10.2.0db_1database目錄下。（記住這個目錄，在下面?zhèn)鋷斓膕pfile 配置中要用到）。SQLALTER DATABASE CREATE STANDBY CONTROLFILE AS d:backupSTANDBY.CTL;SQLcreate pfile from spfile;在D:oracleproduct10.2.0db_1database目錄下找到pfile（initorac.ora）;修改其參數(shù)orac._db_cache_size=335544320orac._java_pool_size=4194304orac._large_pool_size=4194304orac._shared_pool_size=251658240orac._streams_pool_size=0*.audit_file_dest=d:oracleproduct10.2.0/admin/orac/adump*.background_dump_dest=d:oracleproduct10.2.0/admin/orac/bdump*.compatible=.0*.control_files=d:oracleproduct10.2.0oradataoraccontrol01.ctl,d:oracleproduct10.2.0oradataoraccontrol02.ctl,d:oracleproduct10.2.0oradataoraccontrol03.ctl*.core_dump_dest=d:oracleproduct10.2.0/admin/orac/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orac*.db_recovery_file_dest=d:oracleproduct10.2.0/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.DB_UNIQUE_NAME=primary*.FAL_CLIENT=primary*.FAL_SERVER=standby*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=C:archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary*.log_archive_dest_2=SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.log_archive_format=%T%S%r.ARC*.open_cursors=300*.pga_aggregate_target=201326592*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=603979776*.STANDBY_FILE_MANAGEMENT=AUTO*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=d:oracleproduct10.2.0/admin/orac/udump然后保存,在主庫上執(zhí)行SQL shutdown immediateSQLstartup pfile=D:oracleproduct10.2.0db_1databaseinitorac.oraSQL create spfile from pfile;SQL startup3.3 在備庫上創(chuàng)建一個Windows服務(wù)和密碼文件創(chuàng)建服務(wù)C:Documents and SettingsAdministratororadim -new -sidorac -startmode auto創(chuàng)建密碼文件C:Documents and SettingsAdministratororapwd file=D:oracleproduct10.2.0db_1databasePWDorac.ora password=123456 entries=5;3.4 備庫創(chuàng)建admin目錄將主庫的d:oracleproduct10.2.0adminorac 目錄及文件夾(adump,bdump,cdump,dpdump,pfile,udump)拷貝到備庫的相同目錄。3.5 拷貝控制文件和參數(shù)文件備庫的控制文件（D:backupstandby。ctl）已經(jīng)創(chuàng)建， 只需要從主庫拷貝到備庫的（D:oracleproduct10.2.0oradataorac）下此時備庫已經(jīng)存在 數(shù)據(jù)文件和控制文件。3.6 拷貝初始化參數(shù)文件到備庫將主庫下的初始化參數(shù)文件（D:oracleproduct10.2.0db_1databaseinitorac.ora）拷貝到備庫D:oracleproduct10.2.0db_1databaseinitorac.ora下4、修改備庫pfile文件修改備庫的D:oracleproduct10.2.0db_1databaseinitorac.ora文件如下-standby.ora-orac._db_cache_size=335544320orac._java_pool_size=4194304orac._large_pool_size=4194304orac._shared_pool_size=251658240orac._streams_pool_size=0*.audit_file_dest=d:oracleproduct10.2.0/admin/orac/adump*.background_dump_dest=d:oracleproduct10.2.0/admin/orac/bdump*.compatible=.0*.control_files=d:oracleproduct10.2.0oradataoracSTANDBY.CTL*.core_dump_dest=d:oracleproduct10.2.0/admin/orac/cdump*.db_block_size=8192*.db_domain=*.db_file_multiblock_read_count=16*.db_name=orac*.db_recovery_file_dest=d:oracleproduct10.2.0/flash_recovery_area*.db_recovery_file_dest_size=2147483648*.DB_UNIQUE_NAME=standby*.FAL_CLIENT=standby*.FAL_SERVER=primary*.log_archive_config=DG_CONFIG=(primary,standby)*.log_archive_dest_1=location=C:archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby*.log_archive_dest_2=SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary*.LOG_ARCHIVE_DEST_STATE_1=ENABLE*.LOG_ARCHIVE_DEST_STATE_2=ENABLE*.log_archive_format=%T%S%r.ARC*.open_cursors=300*.pga_aggregate_target=201326592*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=603979776*.STANDBY_FILE_MANAGEMENT=AUTO*.undo_management=AUTO*.undo_tablespace=UNDOTBS1*.user_dump_dest=d:oracleproduct10.2.0/admin/orac/udump-在備庫上創(chuàng)建spfile文件,在備庫上執(zhí)行以下命令：C:Documents and SettingsAdministratorsqlplus /standby as sysdba;SQL startup pfile=D:oracleproduct10.2.0db_1databaseinitorac.oraSQL CREATE SPFILE FROM PFILE=D:oracleproduct10.2.0db_1databaseinitorac.ora;SQLstartup 5、配置網(wǎng)絡(luò)，修改listener.ora ,tnsnames.ora5.1 配置先在主從庫上分別用netca 命令配置監(jiān)聽和本地服務(wù)名，再分別編輯主、從庫的listener.ora,tnsnames.ora，參數(shù)以下主庫的listener.ora 文件如下：# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORAC) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (SID_NAME = ORAC) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = acct-zhangpei)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )#主庫的tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.ORAC = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orac) ) )#從庫的listeners.ora 如下# listener.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadminlistener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORAC) (ORACLE_HOME = d:oracleproduct10.2.0db_1) (SID_NAME = ORAC) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = sss-f115w6wz6ge)(PORT = 1521) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )#從庫的tnsnames.ora 文件如下：# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1networkadmintnsnames.ora# Generated by Oracle configuration tools.PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 4)(PORT = 1521) ) (CONNECT_DATA = (SERVICE_NAME = ORAC) ) )STANDBY = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 7)(PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORAC) ) )EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) )#在上面配置完成之后，需要在主、備機(jī)上重啟監(jiān)聽服務(wù)CMD Lsnrctl stopCMD lsnrctl start可以使用lsnrctl status 命令查看監(jiān)聽器狀態(tài)5.2 測試網(wǎng)絡(luò)配置如果配置成功，在主機(jī)上測試SQL conn sys/123456standby as sysdba;如果配置成功，在備庫上測試SQL conn sys/123456primary as sysdba; 也能得到相似結(jié)果則說明網(wǎng)絡(luò)配置成功。6、啟動主備服務(wù)6.1 主庫:SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;6.2 備庫:SQL STARTUP MOUNT;SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;到次為止，最關(guān)鍵的時刻來了，我們要測試看是否配置成功，如果成功的話在主機(jī)上的歸檔就能順利傳到6.3 測試歸檔1. 測試主庫產(chǎn)生的歸檔日志是否能正常傳送到歸檔日志在主機(jī)上SQLarchive log list;SQLalter system archive log current;SQLarchive log list;主庫進(jìn)行日志切換:SQLAlter system switch logfile;然后分別查看主庫和備庫的D:arch 目錄下是否產(chǎn)生了同樣的歸檔日志文件。select max(sequence#) from v$archived_log;select max(sequence#) from v$log_history;select group#,sequence#,archived,status from v$log;select name,sequence#,applied from v$archived_log;select sequence#,applied from v$archived_log;若不同步，1. 看log 日志， archive 是否有丟失2. 可以在備庫做如下操作：alter database recover managed standby database cancel;alter database recover managed standby database disconnect from session;7、日常維護(hù)7.1、正確打開主庫和備庫主庫:SQL STARTUP MOUNT;SQL ALTER DATABASE ARCHIVELOG;SQL ALTER DATABASE OPEN;備庫:SQL STARTUP MOUNT;SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASEDISCONNECT FROM SESSION;7.2、正確關(guān)閉順序備庫:SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;SQLSHUTDOWN IMMEDIATE;主庫SQLSHUTDOWN IMMEDIATE;7.3、備庫Read-Only模式打開當(dāng)前主庫正常OPEN 狀態(tài)、備庫處于日志傳送狀態(tài).1 在備庫停止日志傳送SQL recover managed standby database cancel;2 備庫Read-only 模式打開SQL alter database open read only;3 備庫回到日志傳送模式SQL recover managed standby database disconnect from session;SQL select status from v$instance;7.4、日志傳送狀態(tài)監(jiān)控1 主庫察看當(dāng)前日志狀況SQL select sequence#,status from v$log;2 備庫察看RFS(Remote File Service)接收日志情況和MRP 應(yīng)用日志同步主庫情況SQL SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKSFROM V$MANAGED_STANDBY;3 察看備庫是否和主庫同步SQL SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;4 察看備庫已經(jīng)歸檔的redoSQL SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;5 察看備庫已經(jīng)應(yīng)用的redoSQL SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#FROM V$LOG_HISTORY;6 察看備庫接收,應(yīng)用redo 數(shù)據(jù)過程.SQL SELECT MESSAGE FROM V$DATAGUARD_STATUS;7.5 備庫歸檔目錄維護(hù)1 找到備庫歸檔目錄SQL show parameter log_archive_dest_12 維護(hù)策略每周2,4,7 刪除已經(jīng)應(yīng)用的歸檔文件具體參見附錄二8、主庫正常切換8.1人工干預(yù)主庫正常切換1 在主庫端檢驗(yàn)數(shù)據(jù)庫可切換狀態(tài)SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS:TO STANDBY 表示可以正常切換.如果SWITCHOVER_STATUS 的值為SESSIONS ACTIVE,表示當(dāng)前有會話處于ACTIVE狀態(tài)2 開始主庫正常切換如果SWITCHOVER_STATUS 的值為TO STANDBY 則:SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;如果SWITCHOVER_STATUS 的值為SESSIONS ACTIVE 則:SQL ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSIONSHUTDOWN;成功運(yùn)行這個命令后，主庫被修改為備庫3 重啟先前的主庫SQL SHUTDOWN IMMEDIATE;SQL STARTUP MOUNT;4 在備庫驗(yàn)證可切換狀態(tài)SQL SELECT SWITCHOVER_STATUS FROM V$DATABASE;SWITCHOVER_STATUS-TO_PRIMARY1 row selected5 將目標(biāo)備庫轉(zhuǎn)換為主庫如果SWI