20220319-Oracle RAC DG手动切换

chenoracle

于 2025-03-25 21:47:55 发布

阅读量755

点赞数 22

分类专栏： Oracle 文章标签： oracle 数据库

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/146513599

版权

Oracle 专栏收录该内容

31 篇文章

订阅专栏

在这里插入图片描述

环境说明：

主库：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,就会自动去主库上拿。这样就可以避免了人工干预。

欢迎关注我的公众号《IT小Chen》