Oracle12c修复GAP新方法

**导读**

> 作者：杨漆

> 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分钟

配置完后重启监听，生效！