Oracle RAC DG手動切換
環境說明：
主庫：Oracle 11.2.0.4.0 RAC(兩節點) 備庫：Oracle 11.2.0.4.0 RAC(兩節點) OS：AIX 7.1
IP資訊：
主庫IP：
#public ip 10.10.1.11chenjch1 10.10.1.12chenjch2 #private ip 192.100.10.11chenjch1-priv 192.100.10.12chenjch2-priv #vip 10.10.1.13chenjch1-vip 10.10.1.14chenjch2-vip #scan ip 10.10.1.15flashdb-scan
從庫IP：
# public 10.100.1.11 du-chenj-cdb15 10.100.1.13 du-chenj-cdb16 # private 10.200.10.11 du-chenj-cdb15-priv 10.200.10.13 du-chenj-cdb16-priv # virtual 10.100.1.12 du-chenj-cdb15-vip 10.100.1.14 du-chenj-cdb16-vip # scan 10.100.1.15 sy-CJCDB-scan
切換前檢查
檢視活動會話分佈
set lin 200 pages 200 select inst_id,username,status,count(*) from gv$session group by inst_id,username,status order by 3,4;
檢視長事務
set linesize 200 set pagesize 5000 col transaction_duration format a45 with transaction_details as ( select inst_id , ses_addr , sysdate - start_date as diff from gv$transaction ) select s.username , to_char(trunc(t.diff)) || ' days, ' || to_char(trunc(mod(t.diff * 24,24))) || ' hours, ' || to_char(trunc(mod(t.diff * 24 * 60,24))) || ' minutes, ' || to_char(trunc(mod(t.diff * 24 * 60 * 60,60))) || ' seconds' as transaction_duration , s.program , s.terminal , s.status , s.sid , s.serial# from gv$session s , transaction_details t where s.inst_id = t.inst_id and s.saddr = t.ses_addr order by t.diff desc /
檢查事務
select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$fast_start_transactions;
檢查JOB
col schedule_owner for a20 col schedule_name for a30 col owner for a15 col job_name for a30 col job_creator for a15 set line 180 col JOB_ACTION for a40 col COMMENTS for a40 select owner,job_name,enabled,JOB_ACTION,LAST_START_DATE,LAST_RUN_DURATION --,comments from dba_scheduler_jobs where owner <> 'SYS';
檢查DG引數
set linesize 500 pages 100
col value for a70
col name for a30
select name, value
from v$parameter
where name in ('db_name','db_unique_name',
'log_archive_config',
'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1', 'log_archive_dest_3',
'log_archive_dest_state_3',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server','db_file_name_convert',
'log_file_name_convert',
'standby_file_management')
/
檢查DG程式狀態
col dest_name for a30 col error for a50 set lin 200 pages 100 col applied_scn for 9999999999999 select dest_id,error,status,log_sequence,applied_scn,MAX_CONNECTIONS,NET_TIMEOUT,COMPRESSION from v$archive_dest where dest_id<5; --select process,status,thread#,sequence#,block#,blocks from v$managed_standby; select INST_ID,process,status,thread#,sequence#,block#,blocks from gv$managed_standby order by INST_ID ;
檢查主庫狀態
ssh 10.10.1.11 su - oracle export ORACLE_SID=CJCDB1 sqlplus / as sysdba SELECT DATABASE_ROLE,SWITCHOVER_STATUS FROM v$DATABASE; /* NOT ALLOWED 當前的資料庫不是帶有備用資料庫的主資料庫 PREPARING DICTIONARY 該邏輯備用資料庫正在向一個主資料庫和其他備用資料庫傳送它的重做資料，以便為切換做準備 PREPARING SWITCHOVER 接受用於切換的重做資料時，邏輯備用配置會使用它 RECOVERY NEEDED 備用資料庫還沒有接收到切換請求 SESSIONS ACTIVE 在主資料庫中存在活動的SQL會話；在繼續執行之前必須斷開這些會話 SWITCHOVER PENDING 適用於那些已收到主資料庫切換請求但是還沒有處理該請求的備用資料庫 SWITCHOVER LATENT 切換沒有完成並返回到主資料庫 TO LOGICAL STANDBY 主資料庫已經收到了來自邏輯備用資料庫的完整的字典 TO PRIMARY 該備用資料庫可以轉換為主資料庫 TO STANDBY 該主資料庫可以轉換為備用資料庫 */
停用備庫2節點
ssh 10.100.1.13 su - oracle sqlplus / as sysdba show parameter instance_name shutdown immediate
主庫驗證GAP
sqlplus / as sysdba SELECT LOG_ARCHIVED - LOG_APPLIED + 1 LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID = 1 AND ARCHIVED = 'YES' AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG)), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND APPLIED = 'YES' AND RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG));
從庫節點一操作
#驗證從庫是否是RTA模式，減少GAP set lines 200 col dest_name for a30 select DEST_ID,DEST_NAME,RECOVERY_MODE from gv$archive_dest_status where RECOVERY_MODE <>'IDLE'; #RECOVERY_MODE=MANAGED REAL TIME APPLY 為 real time apply，否則不是 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
配置閃回
主庫資料庫任意一個節點操作。
set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 col name for a20 select name,current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database;
檢視asm磁碟組空間 CJCDB_ARCH
select name,total_mb,free_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup; NAME TOTAL_MB FREE_MBUsed% -------------------- ---------- ---------- ---------- ANLZ_DATA 409600 17086758.28 FLASHDB_OCR 102400 102004 .39 MGNT_ARCH 204800 199758 2.46 MGNT_DATA1331200 14942588.78 TRNT_ARCH 204800 196148 4.22 TRNT_DATA1024000 1216798.81 CJCDB_DATA 819200 12233385.07 CJCDB_ARCH 102400 100731 1.63 EPCC_DATA 307200 878397.14 EPCC_ARCH 102400 102049 .34 SCM_DATA 614400 32613546.92 SCM_ARCH 102400 102271 .13 12 rows selected. 配置閃迴路徑 alter system set db_recovery_file_dest_size=50g; alter system set db_recovery_file_dest='+CJCDB_DATA'; --CJCDB_ARCH 開啟閃回 alter database flashback on; create restore point prytosty; 檢查閃回點 col name for a30 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select scn,time,name from V$RESTORE_POINT; SCN TIME NAME ---------- --------------------------------------------------------------------------- -------------------- 4.2276E+11 27-MAR-21 11.48.59.000000000 PM PRYTOSTY
開始切換
主切備
ssh 10.10.1.11 export ORACLE_SID=CJCDB1 sqlplus / as sysdba SELECT trim(DATABASE_ROLE) DBROLE FROM v$DATABASE; alter system switch logfile; alter system flush SHARED_POOL; ALTER SYSTEM SET log_archive_trace=8191 sid='*'; 執行命令後，原主庫自動關閉例項 alter database commit to switchover to physical standby with session shutdown; startup mount
備切主
ssh 10.100.1.11 su - oracle sqlplus / as sysdba show parameter instance_name SELECT trim(DATABASE_ROLE) DBROLE FROM v$DATABASE; ALTER SYSTEM SET log_archive_trace=8191 sid='*';
執行命令後，原備庫自動啟動到mount
alter database commit to switchover to primary with session shutdown; shutdown immediate; startup; set lin 200 pages 100 set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database; ALTER SYSTEM SET log_archive_trace=0 sid='*';
新備庫啟動mrp
ssh 10.10.1.11 export ORACLE_SID=CJCDB1 sqlplus / as sysdba alter database open; ALTER SYSTEM SET log_archive_trace=0 sid='*'; recover managed standby database using current logfile disconnect from session;
新備庫啟動節點2
ssh 10.10.1.12 export ORACLE_SID=CJCDB1 startup
新主庫啟動節點2
ssh 10.100.1.13 export ORACLE_SID=CJCDB2 sqlplus / as sysdba show parameter instance_name startup
新備庫檢查
ssh 10.10.1.11 export ORACLE_SID=CJCDB1 sqlplus / as sysdba SELECT status from v$instance; set lin 200 pages 200 select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
set lin 200 pages 100 col FLASHBACK_ON for a10 col current_scn for 99999999999999 col open_mode for a10 col SWITCHOVER_STATUS for a20 col PROTECTION_MODE for a20 select current_scn, protection_mode, database_role, force_logging, FLASHBACK_ON, open_mode, switchover_status from v$database;
新主庫檢查
ssh 10.100.1.11 export ORACLE_SID=CJCDB1 sqlplus / as sysdba SELECT status from v$instance;
DG常用命令
檢查DG程式狀態
###主庫傳輸狀態檢查：一般不需要，備庫看不到rfs程式時可以檢視。
col dest_name for a30
col error for a50
set lin 200 pages 100
select dest_id,error,status,log_sequence,applied_scn,MAX_CONNECTIONS,NET_TIMEOUT,COMPRESSION from v$archive_dest where dest_id<5;
如有必要重新啟用傳輸
###alter system set log_archive_dest_state_2=defer;
###alter system set log_archive_dest_state_2=enable;
檢查DG同步關係
set lin 200 pages 100
col FLASHBACK_ON for a10
col current_scn for 99999999999999
col open_mode for a10
col SWITCHOVER_STATUS for a20
col PROTECTION_MODE for a20
select current_scn,protection_mode,database_role,force_logging,FLASHBACK_ON,open_mode,switchover_status from v$database;
檢視程式狀態
set lin 200 pages 200
select INST_ID,process,status,thread#,sequence#,block#,blocks from gv$managed_standby order by INST_ID ;
/*
STATUS：程式的當前狀態，值較多，常見的有：
ALLOCATED：正準備連線Primary資料庫
ATTACHED：正在連線Primary資料庫
CONNECTED：已連線至Primary資料庫
IDLE：空閒中
RECEIVING：歸檔檔案接收中
OPENING：歸檔檔案處理中
CLOSING：歸檔檔案處理完，收尾中
WRITING：Redo資料庫寫向歸檔檔案中
WAIT_FOR_LOG：等待新的Redo資料中
WAIT_FOR_GAP：歸檔有中斷，正等待中斷的那部分Redo資料
APPLYING_LOG：應用Redo資料中
*/
驗證mrp啟動實時還是非實時
#驗證是否是RTA模式
set lines 200
col dest_name for a30
select DEST_ID,DEST_NAME,RECOVERY_MODE from v$archive_dest_status where RECOVERY_MODE <>'IDLE';
#RECOVERY_MODE=MANAGED REAL TIME APPLY 為 real time apply，否則不是
啟動實時應用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
###RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
啟動非實時應用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
###RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
更改保護模式
###alter database set standby database to maximize protection;
###alter database set standby database to maximize availability;
###alter database set standby database to maximize performancen;
檢視服務
cd /oracle/crs/bin
./crsctl stat res -t
###如有問題，手動啟動 srvctl start service -d syfront -s front
主切備
###alter database commit to switchover to physical standby with session shutdown;
備切主
###alter database commit to switchover to primary with session shutdown;
V$DATAGUARD_STATS(備庫檢視)
該動態效能檢視顯示出在主庫上產生了多少重做日誌資料
gap檢查：
SELECT LOG_ARCHIVED-LOG_APPLIED+1 LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)
FROM V$ARCHIVED_LOG)), (SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG));
SELECT THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
COL NAME FOR A100
SET LINESIZE 9999 PAGESIZE 9999
COL NEXT_CHANGE# FOR 999999999999999
SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
FROM V$ARCHIVED_LOG A
WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
FROM V$ARCHIVED_LOG B
WHERE B.THREAD# = A.THREAD#
AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
AND B.RESETLOGS_CHANGE# =
(SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
AND B.APPLIED = 'YES'
GROUP BY B.THREAD#)
ORDER BY A.THREAD#, A.SEQUENCE#;
在備庫手工註冊複製過來的的歸檔日誌
alter database register logfile '/opt/oracle/archive_log/1_27293_750772439.log'
檢查當前job_queue_processes值
show parameter job_queue_processes
修改job_queue_processes為0，恢復job執行
alter system set job_queue_processes=1000;
檢查job
col what for a50
col schema_user for a20
col interval for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select schema_user,job,what,broken,LAST_DATE,this_date,NEXT_DATE,TOTAL_TIME,INTERVAL,FAILURES
from dba_jobs
where schema_user not in ('APEX_030200');
SELECT * FROM DBA_JOBS_RUNNING;
檢查DG引數
set linesize 500 pages 100
col value for a70
col name for a30
select name, value
from v$parameter
where name in ('db_name','db_unique_name',
'log_archive_config',
'log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1', 'log_archive_dest_3',
'log_archive_dest_state_3',
'log_archive_dest_state_2',
'remote_login_passwordfile',
'log_archive_format',
'log_archive_max_processes',
'fal_server','db_file_name_convert',
'log_file_name_convert',
'standby_file_management')
/
檢視隱含引數
驗證修改的DRM引數以及_ktb引數 ##和平里一節點
set lin 200 pages 100
col name for a30
col value for a80
select nam.ksppinm name,val.ksppstvl value from x$ksppi nam,x$ksppsv val where nam.indx=val.indx and nam.ksppinm in
('_gc_undo_affinity','_gc_policy_time', '_ktb_debug_flags') order by 1
/
引數說明：
3.LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_CONFIG='DG_CONFIG=(db_unique_name, db_unique_name, ...)'
5.LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_n = {LOCATION=path_name| SERVICE=service_name, attribute, attribute, ... }
歸檔檔案的生成路徑，location代表本地機上，service指明在另一臺機器上。
主庫：
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/chicago VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston'
備庫：
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/boston VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2='SERVICE=chicago LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
8.LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
指定歸檔檔案格式，這裡在主備端應保持一樣的格式。
%t -thread number
%s -log sequence number
%r -resetlogs ID
主庫：
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
備庫：
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_STATE_n = {alternate | reset | defer | enable}
FAL_SERVER And FAL_CLIENT Settings For Cascaded Standby (Doc ID 358767.1)
How to configure the FAL_CLIENT and FAL_SERVER parameters in cascaded standby setup.
FAL_SERVER specifies the FAL (fetch archive log) server for a standby database.
The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service,
configured through the FAL_SERVER parameter, to refer to the FAL client.
The value is an Oracle Net service name,
which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
Given the dependency ofFAL_CLIENTonFAL_SERVER, the two parameters should be configured or changed at the same time.
You can read about the cascaded redo log solution in
<Note 409013.1>: Cascaded Standby Databases in Oracle 10g/11g
FAL_CLIENT和FAL_SERVER是配置dataguard用到的兩個引數,FAL指獲取歸檔日誌(Fetch Archived Log)
FAL簡單的說就是當standby db發現gaps in archived redo logs，即發現缺少archive log,就會自動去主庫上拿。這樣就可以避免了人工干預。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2874066/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle dg切換操作示例Oracle
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- RAC+單例項DG的切換單例
- oracle DG VCS切換異常問題Oracle
- DG的切換操作
- 5分鐘，徹底精通Oracle DG切換Oracle
- Oracle 11g dg switchover切換操作流程Oracle
- dg切換操作文件
- 【DG】三大模式切換模式
- oracle DG資料庫切換步驟筆記Oracle資料庫筆記
- ORA-16139 Oracle DG無法正常切換Oracle
- oracle 10g dg切換步驟記錄Oracle 10g
- Oracle DG管理模式和只讀模式相互切換Oracle模式
- Oracle 18c&19c physical dg切換總結Oracle
- 使用Broker實現DG切換
- DG_三大模式切換模式
- oracle goldengate for oracle rac 的安裝和切換OracleGo
- Oracle RAC+DG搭建Oracle
- scan ip的手動切換
- ostgreSQL主從切換-手動SQL
- Oracle RAC 歸檔與非歸檔切換Oracle
- 【DATAGUARD】物理dg的switchover切換（五）
- oracle 11g dg broker 開啟fast-start failover自動故障切換OracleASTAI
- Mysql MHA部署-06手動切換MySql
- ORACLE RAC 手動建庫Oracle
- oracle RAC手動配置互信Oracle
- 【DG】Data Guard主備庫Switchover切換
- 【DATAGUARD】物理dg的failover切換(六)AI
- dataguard 手動切換，檢查指令碼指令碼
- WPF手動實現切換頁面
- ORACLE19C RAC+DGOracle
- oracle 跨小版本dg切換應用補丁報錯處理Oracle
- 【DG】Data Guard主備庫Failove切換AI
- 單節點DG的switchover切換介紹
- RAC的VIP切換測試
- View手動切換焦點注意事項View
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- ORACLE RAC+DG調整redo大小Oracle