|
###################################### 搭建備庫（DataGard） author:guoyJoe; createdate:2012-12-14 ###################################### 物理Standby創(chuàng)建時的操作步驟 1、創(chuàng)建主庫的備份 2、創(chuàng)建Standby數(shù)據(jù)庫控件文件 3、配置主備庫的監(jiān)聽和網(wǎng)絡(luò)服務(wù)名 4、配置主備庫的初始化參數(shù)文件 5、復(fù)制備份集到Standby服務(wù)器 6、恢復(fù)Standby數(shù)據(jù)庫 7、啟動物理Standby數(shù)據(jù)庫REDO應(yīng)用 8、打開Standby數(shù)據(jù)庫 9、切換主庫的日志檢測日志的傳輸與應(yīng)用是否正常 ********************************************* 物理Standby實際創(chuàng)建過程演示 ********************************************* 一、庫配置和相關(guān)操 1、配置vi /etc/hosts 192.168.0.158 bxdb 192.168.0.165 dg 2、確認主庫處于歸檔模式：archive log list; 3、將主庫置為Force Logging模式: select force_logging from v$database; alter database force logging; 4、配置主庫的初始化參數(shù) *.db_unique_name=ocp_pri *.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)' *.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ocp_pri' *.log_archive_dest_2='SERVICE=standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ocp_dg' *.log_archive_dest_state_2=DEFER --主庫切為備庫時要用到 *.fal_server=standby *.fal_client=primary *.standby_file_management=AUTO #*.db_file_name_convert='bxdb','bxdb' #*.log_file_name_convert='bxdb','bxdb' 5、配置主庫的listener.ora與tnsnames.ora文件 vi listener.ora LISTENER= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=bxdb)(PORT=1521)) (ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=bxdb) (ORACLE_HOME=/u01/app/oracle/product/11g) (SID_NAME=bxdb))) vi tnsnames.ora bxdb = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = bxdb)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bxdb) ) ) bxdb_dg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = bxdb) ) ) 6、主庫備份 backup database format '/backup/rman/full_db_%U'; 7、創(chuàng)建Standby數(shù)據(jù)庫的控制文件 alter database create standby controlfile as '/backup/std01.ctl'; 8、創(chuàng)建密碼文件(關(guān)閉強密碼認證) ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE; orapwd file=orapwbxdb password=oracle force=y ignorecase=y; 9、復(fù)制備份集、控制文件、參數(shù)文件到Standby服務(wù)器 scp *.ora oracle@dg:$ORACLE_HOME/network/admin/ scp pfile.ora oracle@dg:/backup scp std01.ctl oracle@dg:/backup scp full_db* oracle@dg:/backup 二、物理Standby數(shù)據(jù)庫配置和相關(guān)操作 1、配置/etc/hosts 192.168.0.158 bxdb 192.168.0.165 dg 2、創(chuàng)建密碼文件(關(guān)閉強密碼認證) ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE; orapwd file=orapwbxdb password=oracle force=y ignorecase=y; 3、創(chuàng)建日志輸出文件相關(guān)目錄 mkdir -p /arch mkdir -p /u01/app/oracle/oradata/oca mkdir -p /u01/app/oracle/admin/oca/adump --mkdir -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/trace //自動生成無需創(chuàng)建 --mkdir -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/cdump //自動生成無需創(chuàng)建 4、配置備庫的listener.ora與tnsnames.ora文件 修改listener.ora主機名或IP 5、配置備庫的初始化參數(shù) *.audit_file_dest='/u01/app/oracle/admin/bxdb/adump' *.db_unique_name=bxdb_dg *.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)' *.control_files='/opt/oracle/oradata/standby/control1.ctl' *.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bxdb_dg' *.log_archive_dest_2='SERVICE=bxdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bxdb' *.fal_server=bxdb *.fal_client=bxdb_dg *.standby_file_management=AUTO #*.db_file_name_convert='bxdb','bxdb' #*.log_file_name_convert='bxdb','bxdb' #*.background_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace' #*.core_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/cdump' #*.user_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace' 6、啟動物理Standby數(shù)據(jù)庫到MOUNT狀態(tài) startup mount pfile='/backup/pfile.ora'; 7、恢復(fù)數(shù)據(jù)庫 export ORACLE_SID=bxdb rman target / catalog start with '/backup/'; restore database; 8、接收歸檔文件(在主庫上操作) 在主庫上啟動發(fā)送日志：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 9.應(yīng)用日志 alter database recover managed standby database disconnect from session; 10.查看一下告警日志:Media Recovery Waiting for thread 1 sequence 8 (in transit) 如這個提示:recover managed standby database cancel; 11.打開數(shù)據(jù)庫 alter database open; 12、建standby redo log;--創(chuàng)建的大小要與主庫的redolog一樣 alter database add standby logfile group 20 ('/u01/app/oracle/oradata/oca/stb1redo20.log') size 100m, group 21 ('/u01/app/oracle/oradata/oca/stb1redo21.log') size 100m, group 22 ('/u01/app/oracle/oradata/oca/stb1redo22.log') size 100m, group 23('/u01/app/oracle/oradata/oca/stb1redo23.log') size 100m; SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; 假如狀態(tài)全是:UNASSIGNED--->shutdown immediate,startup;--> ACTIVE 13、啟動REDO應(yīng)用 recover managed standby database using current logfile disconnect from session; **************************************** 物理備用數(shù)據(jù)庫的日常管理 **************************************** 1.查看日志應(yīng)用情況： select sequence#,applied from v$archived_log; 2.通過V$STANDBY_LOG視圖驗證standby redo log文件組是否成功創(chuàng)建 告警錯誤1 Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; 解方法1:在備庫重standby redolog file,創(chuàng)建的大小要與主庫的redolog一樣!!!!! 如果還是不行,那只能得啟備庫試試!!!!!!! 3.查詢V$MANAGED_STANDBY視圖，可以確定其是否處于重做應(yīng)用狀態(tài) 如果包含MRP0，則表示處于重做應(yīng)用狀態(tài)；所以必須先取消重做應(yīng)用狀態(tài)，然后才能關(guān)閉該備用數(shù)據(jù)庫。 select process, status from v$managed_standby; 4.可通過檢查，主數(shù)據(jù)庫的歸檔日志狀態(tài)，檢查DATAGUARD的運行是否正常 SELECT DEST_ID,ERROR FROM V$ARCHIVE_DEST; 如果查詢結(jié)果，顯示STATUS＝VALID，并且ERROR為空，則表示主數(shù)據(jù)庫向備用數(shù)據(jù)庫傳遞日志狀態(tài)正常。否則，可能存在問題。 常見故障（1）－網(wǎng)絡(luò)故障：可能由于網(wǎng)絡(luò)原因，造成日志文件傳遞失敗。此時，可首先可利用ping命令檢查網(wǎng)絡(luò)狀態(tài)，然后利用Oralce的tnsping 命令檢查tnsnames.ora文件解析情況。 常見故障（2）－密碼問題：主、備數(shù)據(jù)必須保持sys用戶密碼的一致性。即保持orapwdSID.ora文件中sys密碼的一致性。如果密碼不同，則可能造成傳輸失敗。 告警錯誤1 returning error ORA-16191: Primary log shipping client not logged on standby --密碼文件是否一樣:兩節(jié)點執(zhí)行如下 ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH; orapwd file=orapwbxdb password=oracle force=y ignorecase=y; 告警錯誤2 ORA-16047: DGID mismatch between destination setting and target database --查參數(shù)log_archive_dest_2 5.備用數(shù)據(jù)庫，獲取備用數(shù)據(jù)庫中最后應(yīng)用的日志的序列號。 暫記錄為laseq SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES'; 6.應(yīng)先打開庫再做日志應(yīng)用,就不會報錯 SQL> STARTUP MOUNT; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; SQL> ALTER DATABASE OPEN; alter database open * ERROR at line 1: ORA-10456: cannot open standby database; media recovery session may be in progress SQL> shutdown immediate; SQL> startup SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered. 7.取消應(yīng)用日志 SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ************************************* 物理Standby的角色轉(zhuǎn)換 ************************************* switchover操作步驟 主庫上操作:(主-->備) Step 1 Verify that the primary database can be switched to the standby role. SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO STANDBY Step 2 Initiate the switchover on the primary database. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; Step 3 Shut down and then mount the former primary database. SQL> SHUTDOWN ABORT; SQL> STARTUP MOUNT; 備庫上操作:(備-->主) Step 4 Verify that the switchover target is ready to be switched to the primary role. SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS ----------------- TO_PRIMARY Step 5 Switch the target physical standby database role to the primary role. SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; Step 6 Open the new primary database. SQL> ALTER DATABASE OPEN; 主庫上操作:(主-->備) Step 7 Start Redo Apply on the new physical standby database. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; |
|
|
來自： 昵稱10504424 > 《C#》
