**导读**
> 作者:杨漆
> 16年关系型数据库管理,从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM;运维路上不平坦,跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿,希望帮到大家少走弯路、少熬夜。
技术升级,带来运维更加便利,时效性越高。
12c可以通过Server name直连主库,Online修复,省去Rman基于scn备份后再传输到备库恢复的冗繁步骤
## 方法一:
## 直接主库修复standby控制文件
run{
startup force nomount;
restore standby controlfile from service orcl;
alter database mount;
}
## 查询备库上数据文件头最小的scn
select min(fhscn) from x$kcvfh;
51251230669
## 查询备库上控制文件的scn
select to_char(current_scn) from v$database;
51251468805
## 在主库上查询low scn后新增加的数据文件
select file# from v$datafile where creation_change# >=51251230669; ###例如 low scn为51251230669,查询得到缺失的datafile号为 58
## 添加新的数据文件 (备库执行)
run{
SET NEWNAME FOR DATABASE TO '/u01/oradata/datafile/%f_%U';
RESTORE DATAFILE 58 FROM SERVICE orcl;
}
## 主、备数据文件目录不一致时 需修改控制文件中数据文件位置
rman> catalog start with '/u01/oradata/datafile';
RMAN> switch database to copy;
## rename tempfile && logfile 由于主备的数据文件目录不一致,需要修改controlfile中数据文件位置
/***************************************************
alter system set standby_file_management=MANUAL;
## logfile
alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 8;
alter database clear logfile group 9;
alter database rename file '/oradata/ORCL/redo01.log' to '/u01/oradata/datafile/redo01.log';
alter database rename file '/oradata/ORCL/redo02.log' to '/u01/oradata/datafile/redo02.log';
alter database rename file '/oradata/ORCL/redo03.log' to '/u01/oradata/datafile/redo03.log';
alter database rename file '/oradata/ORCL/redo04.log' to '/u01/oradata/datafile/redo04.log';
alter database rename file '/oradata/ORCL/standby_01.log' to '/u01/oradata/datafile/standby_01.log';
alter database rename file '/oradata/ORCL/standby_02.log' to '/u01/oradata/datafile/standby_02.log';
alter database rename file '/oradata/ORCL/standby_03.log' to '/u01/oradata/datafile/standby_03.log';
alter database rename file '/oradata/ORCL/standby_04.log' to '/u01/oradata/datafile/standby_04.log';
alter database rename file '/oradata/ORCL/standby_05.log' to '/u01/oradata/datafile/standby_05.log';
## tempfile
alter database rename file '/oradata/ORCL/temp01.dbf' to '/u01/oradata/datafile/temp01.dbf';
alter database rename file '/oradata/ORCL/temp02.dbf' to '/u01/oradata/datafile/temp02.dbf';
alter database rename file '/oradata/ORCL/temp03.dbf' to '/u01/oradata/datafile/temp03.dbf';
alter system set standby_file_management=AUTO;
*****************************************************/
## 备注 :SECTION SIZE (在传输时使用并发备份集传输);USING COMPRESSED BACKUPSET (在传输时使用压缩,减轻网络压力)
run{
recover database from service orcl noredo SECTION SIZE 1G USING COMPRESSED BACKUPSET;
}
## 验证,在主、备库上各执行一次:
set pages 300 linesize 300
col hxfnm for a100
select HXFIL File_num,substr(HXFNM,1,40) hxfnm,fhscn from x$kcvfh;
## 方法二: 通过网络将service指定的数据库的增量备份拉过来在本地做recover从而让本地数据库跟上远程数据库的SCN
CONNECT TARGET “sys/<password>@orcldg3 as sysdba” RECOVER DATABASE FROM SERVICE orcl;
## 加压缩参数,减少网络带宽
CONNECT TARGET “sys/<password>@orcldg3 as sysdba”
SET COMPRESSION ALGORITHM ‘HIGH’;
RECOVER DATABASE FROM SERVICE orcl
USING COMPRESSED BACKUPSET;
## 压缩的四个级别:
SET COMPRESSION ALGORITHM ‘BASIC’;
SET COMPRESSION ALGORITHM ‘LOW’;
SET COMPRESSION ALGORITHM ‘MEDIUM’;
SET COMPRESSION ALGORITHM ‘HIGH’;
--diag
跟踪备库alert日志,可观察到恢复情况。
修复完成后备库端启动到恢复管理模式,应用归档。
## 检查归档在备库端应用情况,直到count(*) 为0表示,追平主库日志。
select count(*),to_char(min(first_time),'yyyymmdd hh24:mi:ss') from v$archived_log where applied='NO';
## 12C sqlnet.ora中需要加的参数,以向下兼容11g、10g的客户端;网络不好的环境下、延迟设置(否者会报出TNS-12535 TNS-00505的)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
#SQLNET.AUTHENTICATION_SERVICES=ALL
#SQLNET.AUTHENTICATION_SERVICES=(NONE)
DIAG_ADR_ENABLED = OFF
DIAG_ADR_ENABLED_LISTENER=OFF
sqlnet.expire_time=30 ##30分钟
配置完后重启监听,生效!
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。