TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
18c & 19c Physical Standby Switchover Best Practices using SQL*Plus (Doc ID 2485237.1)
1.主备库应该是同样的数据库版本
2.检查确认数据库日志中没有异常报错,查看数据库日志或者查询v$diag_alert_ext视图
3.查询v$database_block_corruption & v$nonlogged_block没有坏块
4.检查主备之间日志传送和应用状态
主库:
- col DEST_NAME for a20
- col DESTINATION for a25
- col ERROR for a15
- col ALTERNATE for a20
- set lines 1000
- select DEST_NAME,DESTINATION,ERROR,ALTERNATE,TYPE,status,VALID_TYPE,VALID_ROLE from V$ARCHIVE_DEST where STATUS <>'INACTIVE';
复制代码- SELECT thread#
- ,max(sequence#) "Last Primary Seq Generated"
- FROM gv$archived_log val
- ,gv$database vdb
- WHERE val.resetlogs_change# = vdb.resetlogs_change#
- GROUP BY thread#
- ORDER BY 1;
复制代码 备库:
- SELECT thread#
- ,max(sequence#) "Last Standby Seq Received"
- FROM gv$archived_log val
- ,gv$database vdb
- WHERE val.resetlogs_change# = vdb.resetlogs_change#
- GROUP BY thread#
- ORDER BY 1;
复制代码- select thread#, max(sequence#) "Last Standby Seq Applied"
- from gv$archived_log val, gv$database vdb
- where val.resetlogs_change# = vdb.resetlogs_change#
- and val.applied in ('YES','IN-MEMORY')
- group by thread# order by 1;
复制代码 5.参数设置检查
- set lines 180 pages 2000 feedback off
- col name for a40
- col value for a60
- SELECT name
- ,value
- ,DECODE(isdefault, 'FALSE', isdefault, isdefault) isdefault
- ,DECODE(isdefault, 'FALSE', issys_modifiable, issys_modifiable) issys_modifiable
- FROM v$parameter
- WHERE name IN (
- 'db_unique_name'
- ,'db_name'
- ,'service_names'
- ,'fal_client'
- ,'fal_server'
- ,'standby_file_management'
- ,'log_archive_config'
- ,'remote_login_passwordfile'
- ,'db_file_name_convert'
- ,'log_file_name_convert'
- ,'log_archive_format'
- ,'instance_name'
- ,'compatiable'
- ,'db_create_file_dest'
- ,'db_create_online_log_dest_1'
- ,'db_recovery_file_dest'
- ,'db_recovery_file_dest_size'
- );
复制代码 5.确认没有gap
- SELECT thread#
- ,max(sequence#) "Last Standby Seq Applied"
- FROM gv$archived_log val
- ,gv$database vdb
- WHERE val.resetlogs_change# = vdb.resetlogs_change#
- AND val.applied IN (
- 'YES'
- ,'IN-MEMORY'
- )
- GROUP BY thread#
- ORDER BY 1;
复制代码 6.确认mrp状态
- select process,status,thread#,sequence# from v$managed_standby where process like 'MRP%';
复制代码 启停mrp命令参考
- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
复制代码 7.检查数据文件状态
- SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';
复制代码 如果有offline的需要online
- ALTER DATABASE DATAFILE 'datafile-name' ONLINE;
复制代码 8.检查临时数据文件
- select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;
复制代码 如果有必要,增加临时数据文件
9.检查redo和standby redo
- set lines 150
- col member for a50
- select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
复制代码- set lines 150
- col member for a50
- select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$standby_log a,v$logfile b where a.group#=b.group#;
复制代码
主库redo的状态为INACTIVE,ACTIVE,CURRENT,备库redo的状态为UNUSED, CLEARING or CLEARING_CURRENT,standby redo的状态为 UNASSIGNED或者ACTIVE
如果输出有不同的结果,则需要手动清空ORL,如果需要在待机状态下清除ORL或SRL,则必须停止托管恢复进程。在切换期间,如果之前没有清除orl,则orl将被清除,但切换将需要花费大量时间来完成。切换会话将等待15个min完成,否则将发生超时。如果切换因超时而终止,则重试,直到切换成功。
清空命令为
- ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# >;
复制代码 如果数据库配置为使用OMF文件进行重做日志文件或设置log_file_name_转换,则在线重做日志文件将自动清除与管理恢复过程启动。
10.verify switchover
主库执行,如果有报错,则要检查alert日志排查报错信息,如果返回’database altered'则表示可以切换
- ALTER DATABASE SWITCHOVER TO <standby db_name> VERIFY;
复制代码 11.switchover
最好把主库job关闭,job_queue_processes设置为0可以关闭job,scheduler
- alter system set job_queue_processes=0 sid='*';
复制代码 如果dg监听和业务监听是分开的,最好停止业务监听,同时杀掉local=no的进程
- ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs -i kill -9 {}
复制代码 主库执行
- ALTER DATABASE SWITCHOVER TO <standby db_name>;
复制代码 新主库执行
新备库执行
- startup;
- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
复制代码 12.再次检查切换后主备之间的同步状态
|
|