2019-05-22发表 xbdba 12c新特性40 分钟读完 (大约5975个字)

12c新特性：Rman中通过网络恢复文件

从12.1版本开始，rman提供了一个新功能，RESOTORE/RECOVER DATBASE USING SERVICE，可以直接通过网络对包含所需文件的物理备库进行文件的还原和恢复。可以恢复整库，数据文件，控制文件，参数文件或者表空间等。当你需要将备库与主库同步时通过网络还原文件非常有用。

通过网络使用备份集来还原和恢复文件，所有你可以使用多部分备份，加密和压缩等来提高备份和恢复的效率。

一般可以用在这些常见的场景：

你需要前滚物理备库让它能与主库保持一致。

一般通过创建主库的增量备份，然后恢复到备库。
需要还原主库丢失的数据文件、控制文件或者表空间等，那么可以通过一致的物理备库上的文件来进行恢复。

12c以前，如果想要通过增量备份来让备库与主库同步成一致：

在主库上创建一个备库的控制文件
以备库的当前SCN号为起点，在主库上做一个增量备份
将增量备份拷贝到备库上，然后注册到RMAN的catalog
使用新的控制文件将备库启动到mount状态
取消备库的恢复应用，恢复增量备份
开启备库的恢复进程

而12c以后，步骤就简化了许多，只需要执行RECOVER … FROM SERVICE即可，这个命令则完成了下面这些过程：

以备库数据文件头里记录的SCN号为起点，在主库创建了增量备份
通过网络传输增量备份到备库机器
应用这些增量备份到备库

这样可以将备库的文件恢复到与主库保持一致，但是这种情况下备库的控制文件仍然记录的是旧的SCN号，要比现在备库数据文件里记录的SCN号要小。因此要完成整个同步工作，则需要更新备库的控制文件来使SCN号保持一致。

这里就模拟以前的一个例子处理一个dg gap同步问题，看看新功能带来的便利性。

查看主备库目前状态都是正常

--primary
sys@ORA12C> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
ora12c                         READ WRITE           PRIMARY

--standby
sys@ORA12C> select db_unique_name,OPEN_MODE,DATABASE_ROLE from v$database;

DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- ----------------
ORA12C_DG                      READ ONLY WITH APPLY PHYSICAL STANDBY

查看同步情况

DGMGRL> show configuration;

Configuration - ora12ccfg

  Protection Mode: MaxAvailability
  Members:
  ora12c    - Primary database
    ora12c_dg - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 43 seconds ago)

将备库重启到mount状态

1 2	SHUTDOWN IMMEDIATE; STARTUP MOUNT;

停止同步，切换几个日志文件，然后删除主库归档

1
2
3

edit database ora12c_dg set state='apply-off';

alter system swithc logfile;

检查日志出现告警

DGMGRL> show configuration

Configuration - ora12ccfg

  Protection Mode: MaxPerformance
  Members:
  ora12c    - Primary database
    ora12c_dg - Physical standby database 
      Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 19 seconds ago)

检查主备库数据文件的scn号

--primary
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM                                                        FHSCN
---------- ------------------------------------------------------------ --------------------
         1 /u01/app/oracle/oradata/ORA12C/datafile/                     11049071
         3 /u01/app/oracle/oradata/ORA12C/datafile/                     11049071
         4 /u01/app/oracle/oradata/ORA12C/datafile/                     11049071
         5 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         6 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         7 /u01/app/oracle/oradata/ORA12C/datafile/                     11049071
         8 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         9 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11049071
        10 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11049071
        11 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11049071
        12 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11049071
        43 /u01/app/oracle/oradata/ORA12C/8838793F1                     11049071
        44 /u01/app/oracle/oradata/ORA12C/8838793F1                     11049071
        45 /u01/app/oracle/oradata/ORA12C/8838793F1                     11049071

--standby
  FILE_NUM HXFNM                                                        FHSCN
---------- ------------------------------------------------------------ --------------------
         1 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11049003
         3 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11049003
         4 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11049003
         5 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         6 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         7 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11049003
         8 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         9 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11049003
        10 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11049003
        11 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11049003
        12 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11049003
        43 /u01/app/oracle/oradata/ORA12C_DG/883879                     11049003
        44 /u01/app/oracle/oradata/ORA12C_DG/883879                     11049003
        45 /u01/app/oracle/oradata/ORA12C_DG/883879                     11049003

这里看到除了5、6、8三个文件以外，其他的数据文件主备的scn号都不一致，检查备库当前的scn

sys@ORA12C> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   11049002

采用rman的新功能，recover standby using service，通过RMAN连接到target备库，然后用主库的service执行恢复备库命令。

语法：SYNTAX – RECOVER DATABASE FROM SERVICE < PRIMARY DB SERVICE NAME > NOREDO USING COMPRESSED BACKUPSET;

[oracle@stbyum ~]$ rman target /



RMAN>  recover database from service ora12c noredo using compressed backupset;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=619 device type=DISK
skipping datafile 5; already restored to SCN 1439827
skipping datafile 6; already restored to SCN 1439827
skipping datafile 8; already restored to SCN 1439827
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00009: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00010: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00011: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00012: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00043: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00044: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service ora12c
destination for restore of datafile 00045: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

可以看到这里只有数据文件被还原了，没有归档日志应用，没有控制文件，这表示目前备库不是一致的，

重新检查两边的数据文件的scn号

--primary
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM                                                        FHSCN
---------- ------------------------------------------------------------ --------------------
         1 /u01/app/oracle/oradata/ORA12C/datafile/                     11055068
         3 /u01/app/oracle/oradata/ORA12C/datafile/                     11055098
         4 /u01/app/oracle/oradata/ORA12C/datafile/                     11055131
         5 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         6 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         7 /u01/app/oracle/oradata/ORA12C/datafile/                     11055143
         8 /u01/app/oracle/oradata/ORA12C/datafile/                     1439827
         9 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11055152
        10 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11055166
        11 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11055181
        12 /u01/app/oracle/oradata/ORA12C/86DF3648F                     11055200
        43 /u01/app/oracle/oradata/ORA12C/8838793F1                     11055225
        44 /u01/app/oracle/oradata/ORA12C/8838793F1                     11055239
        45 /u01/app/oracle/oradata/ORA12C/8838793F1                     11055266

--standby
sys@ORA12C> select HXFIL File_num,substr(HXFNM,1,40) HXFNM,fhscn from x$kcvfh;

  FILE_NUM HXFNM                                                        FHSCN
---------- ------------------------------------------------------------ --------------------
         1 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11055068
         3 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11055098
         4 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11055131
         5 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         6 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         7 /u01/app/oracle/oradata/ORA12C_DG/datafi                     11055143
         8 /u01/app/oracle/oradata/ORA12C_DG/86DF14                     1439827
         9 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11055152
        10 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11055166
        11 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11055181
        12 /u01/app/oracle/oradata/ORA12C_DG/86DF36                     11055200
        43 /u01/app/oracle/oradata/ORA12C_DG/883879                     11055225
        44 /u01/app/oracle/oradata/ORA12C_DG/883879                     11055239
        45 /u01/app/oracle/oradata/ORA12C_DG/883879                     11055266

可以看到现在两边的scn都同步到了一致状态。

因为数据文件都进行了恢复，控制文件还是老的，所以要从主库还原最新的备库控制文件

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area    5083496448 bytes

Fixed Size                     8803264 bytes
Variable Size               1207962688 bytes
Database Buffers            3858759680 bytes
Redo Buffers                   7970816 bytes

RMAN> restore standby controlfile from service ora12c;

Starting restore at 22-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=743 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ora12c
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl
Finished restore at 22-MAY-19

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

这一步完成以后，备库控制文件里记录的文件名是与主库一样，如果本来主备库路径和名称就是一致的，那么就不用做其他处理了，否则需要对控制文件的内容做更新。

修正控制文件里记录的数据文件和临时文件的名称

RMAN> catalog start with '/u01/app/oracle/oradata/ORA12C_DG';

Starting implicit crosscheck backup at 22-MAY-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 22-MAY-19

Starting implicit crosscheck copy at 22-MAY-19
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 22-MAY-19

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_23/o1_mf_s_1006353206_gcxdzml6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_24/o1_mf_s_1006442710_gd03trx4_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_04_24/o1_mf_s_1006454159_gd0hmrfs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_05_06/o1_mf_s_1007567615_gdztcxvr_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ora12c/ORA12C/ORA12C_DG/autobackup/2019_05_06/o1_mf_s_1007571825_gdzy5vg5_.bkp

searching for all files that match the pattern /u01/app/oracle/oradata/ORA12C_DG

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gcxbppbo_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gcxbq4jy_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gcxbqfcq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gcxbsd21_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gcxbvd7s_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gfnonot8_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gcxbqvmf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gcxbsf50_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gcxbvg7d_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gfnodpls_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gfnonr04_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gcxbsgb6_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gcxbsknh_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gcxbsnt4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gcxbt3z2_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_temp_gczp8ojm_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_noco_gf2fxhlt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_comp_gf2fxkmm_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gcxbtmj1_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gcxbtnfs_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gcxbtopn_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gcxbtq2x_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gcxbtsh9_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gcxbttm1_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gcxbtvgx_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gfnojotd_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gfnojtn0_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gfnojzln_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gfnok35r_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gfnok7fj_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gfnol2wo_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gfnolyr2_.log
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fcdjn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fcmgq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fcpow_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fp8hg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fpcmw_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fpgxn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_system_gdzoy61r_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_sysaux_gdzoy61s_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_undotbs1_gdzoy61t_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_temp_gdzst0jh_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_system_gdzxf5td_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_sysaux_gdzxf5tf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_undotbs1_gdzxf5tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_temp_gdzxmo4z_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gf0c32gr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gf0c32gs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gf0c32gt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gcxbppbo_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gcxbq4jy_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gcxbqfcq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gcxbsd21_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gcxbvd7s_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_temp_gfnonot8_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gcxbqvmf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gcxbsf50_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gcxbvg7d_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gfnodpls_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_temp_gfnonr04_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gcxbsgb6_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gcxbsknh_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gcxbsnt4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gcxbt3z2_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_temp_gczp8ojm_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_noco_gf2fxhlt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_tbs_comp_gf2fxkmm_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fcdjn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fcmgq_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fcpow_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_system_gd0fp8hg_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_sysaux_gd0fpcmw_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8741EB39B020CBF3E0536892A8C0E24F/datafile/o1_mf_undotbs1_gd0fpgxn_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_system_gdzoy61r_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_sysaux_gdzoy61s_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_undotbs1_gdzoy61t_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/88336E2F7C204DA0E0536892A8C0C724/datafile/o1_mf_temp_gdzst0jh_.tmp
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gf0c32gr_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gf0c32gs_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gf0c32gt_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf
File Name: /u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gcxbtmj1_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gcxbtnfs_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gcxbtopn_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gcxbtq2x_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gcxbtsh9_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gcxbttm1_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gcxbtvgx_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_1_gfnojotd_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_2_gfnojtn0_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_3_gfnojzln_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_4_gfnok35r_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_5_gfnok7fj_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_6_gfnol2wo_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/onlinelog/o1_mf_7_gfnolyr2_.log
  RMAN-07529: Reason: catalog is not supported for this file type
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_system_gdzxf5td_.dbf
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_sysaux_gdzxf5tf_.dbf
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_undotbs1_gdzxf5tg_.dbf
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: /u01/app/oracle/oradata/ORA12C_DG/8835360767C07549E0536892A8C06926/datafile/o1_mf_temp_gdzxmo4z_.tmp
  RMAN-07518: Reason: Foreign database file DBID: 980071166  Database Name: CDB1

Switch to cataloged copy

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_system_gfnobxo0_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_sysaux_gfnoc19w_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_undotbs1_gfnod4tg_.dbf"
datafile 5 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_system_gfnodmhj_.dbf"
datafile 6 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_sysaux_gcxbqyr5_.dbf"
datafile 7 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/datafile/o1_mf_users_gfnog41g_.dbf"
datafile 8 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF1439DC9B8E60E0536892A8C0ECA0/datafile/o1_mf_undotbs1_gfnog77f_.dbf"
datafile 9 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_system_gfnog8nr_.dbf"
datafile 10 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_sysaux_gfnogcrs_.dbf"
datafile 11 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_undotbs1_gfnogmbq_.dbf"
datafile 12 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/86DF3648F8F79A5AE0536892A8C00070/datafile/o1_mf_users_gfnoh25p_.dbf"
datafile 43 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_system_gfnohkg4_.dbf"
datafile 44 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_sysaux_gfnohrhf_.dbf"
datafile 45 switched to datafile copy "/u01/app/oracle/oradata/ORA12C_DG/8838793F133511C4E0536892A8C01690/datafile/o1_mf_undotbs1_gfnoj06c_.dbf"

如果在备库进行recover from service的过程中，主库新增了数据文件，那么需要单独将这个文件恢复到备库，也就是之前步骤查询的数据库SCN号

SELECT file# FROM V$DATAFILE WHERE creation_change# >= v$database.current_scn(STANDBY);

-- NON-CATALOG：
RUN
{
SET NEWNAME FOR DATABASE TO '/u01/app/oracle/oradata/ORA12C_DG';
RESTORE DATAFILE XX FROM SERVICE ORA12C;
}

-- CATALOG:
RESTORE DATAFILE XX FROM SERVICE ORA12C;

更新备库控制文件中redo日志文件的名称,这里直接clear备库日志，等下RMAN会自动重建

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;

主库做几个日志切换

1	ALTER SYSTEM ARCHIVE LOG CURRENT;

备库开启恢复进程

RMAN> recover database;

Starting recover at 22-MAY-19
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 175 is already on disk as file /u01/arch/ora12c/1_175_1006450609.dbf
archived log for thread 1 with sequence 176 is already on disk as file /u01/arch/ora12c/1_176_1006450609.dbf
archived log for thread 1 with sequence 177 is already on disk as file /u01/arch/ora12c/1_177_1006450609.dbf
archived log file name=/u01/arch/ora12c/1_175_1006450609.dbf thread=1 sequence=175
archived log file name=/u01/arch/ora12c/1_176_1006450609.dbf thread=1 sequence=176
archived log file name=/u01/arch/ora12c/1_177_1006450609.dbf thread=1 sequence=177
media recovery complete, elapsed time: 00:00:59
Finished recover at 22-MAY-19

RMAN> ALTER DATABASE OPEN READ ONLY;

Statement processed

DGMGRL> edit database ora12c_dg set state='apply-on';
Succeeded.
DGMGRL> show configuration

Configuration - ora12ccfg

  Protection Mode: MaxPerformance
  Members:
  ora12c    - Primary database
    ora12c_dg - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 1 second ago)

2019-03-26发表 xbdba 备份恢复25 分钟读完 (大约3816个字)

12c中rman恢复表和分区

在12c以前的版本中，如果想恢复单表到某个时间点之前是一件非常麻烦的事情，一般方法可能只能通过把全库备份基于某个时间点恢复到别处，然后再从新库当中把数据导出来插入回原库中，如果源库很大的话整个时间可想而知。而在12c新的rman特性中，提供了一个非常棒的功能，可以直接通过简单的命令就能恢复单表或者分区到某个时间点，还可以直接恢复到其他的schema下面。

如果想通过rman备份当中恢复出表或者分区时，主要是做了以下步骤：

根据recover指定的目标时间，来判断哪些备份包含需要恢复的表或者分区
判断目标主机上是否含有足够的空间来创建辅助实例，用于恢复过程使用。如果空间不足，rman进程会报错然后退出recover
在目标主机上创建辅助实例，基于指定的恢复时间点，恢复指定的表或分区到辅助实例当中
创建包含需要恢复的表或分区的数据泵导出文件，可以指定文件的名称和路径，主要用于存放表和分区的元数据信息
(可选)将数据泵文件导入目标库，这个操作你可以之后手动操作
(可选)可以将恢复的表或分区导入新库当中的不同用户和不同表空间下

查看当前的数据文件和备份

[oracle@xb ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Mar 26 13:14:19 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA12C (DBID=380590835)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORA12C

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    800      SYSTEM               YES     /u01/app/oracle/oradata/ora12c/system01.dbf
3    500      SYSAUX               NO      /u01/app/oracle/oradata/ora12c/sysaux01.dbf
4    70       UNDOTBS1             YES     /u01/app/oracle/oradata/ora12c/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      NO      /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
6    330      PDB$SEED:SYSAUX      NO      /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/ora12c/users01.dbf
8    100      PDB$SEED:UNDOTBS1    NO      /u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
9    250      PDB12C:SYSTEM        YES     /u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
10   360      PDB12C:SYSAUX        NO      /u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
11   100      PDB12C:UNDOTBS1      YES     /u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
12   5        PDB12C:USERS         NO      /u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
13   70       PDB12C:TBS1          NO      /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
15   10       PDB12C:TBS2          NO      /u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    131      TEMP                 32767       /u01/app/oracle/oradata/ora12c/temp01.dbf
2    64       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ora12c/pdbseed/temp012019-03-13_09-14-08-192-AM.dbf
3    130      PDB12C:TEMP          32767       /u01/app/oracle/oradata/ora12c/pdb12c/temp01.dbf

RMAN> list backup;

specification does not match any backup in the repository




-- 当前并没有可用备份，对CDB和它的PDBs做一个全备

RMAN> backup database plus archivelog;


Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1002984384
input archived log thread=1 sequence=4 RECID=2 STAMP=1002984802
input archived log thread=1 sequence=5 RECID=3 STAMP=1003929462
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/02ttdfrm_1_1 tag=TAG20190326T131742 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora12c/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora12c/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ora12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/ora12c/pdb12c/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/ora12c/pdb12c/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/ora12c/pdb12c/undotbs01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g8kd5pwo_.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/ora12c/pdb12c/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs2_g8pl709m_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ora12c/pdb12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/ora12c/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/05ttdfsc_1_1 tag=TAG20190326T131745 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 26-MAR-19

Starting backup at 26-MAR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=1003929491
channel ORA_DISK_1: starting piece 1 at 26-MAR-19
channel ORA_DISK_1: finished piece 1 at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/06ttdfsj_1_1 tag=TAG20190326T131811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-MAR-19

Starting Control File and SPFILE Autobackup at 26-MAR-19
piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-19

构建测试表

drop table t1 purge;

create table t1 (id number);
insert into t1 values(1);
commit;

-- 检查当前scn
xb@PDB12C> select current_scn from v$database;

CURRENT_SCN
-----------
    1612170

insert into t1 values(2);
commit;

执行恢复，指定一个辅助目录，时间的指定可以通过UNTIL SCN,UNTIL TIME和UNTIL SEQUENCE，可以只导出dmp，并不立即做导入表的操作。

recover table xb.t1 of pluggable database PDB12C
  until scn 1612170
  auxiliary destination '/u01/aux'                      >>>>==== 指定辅助目录，用于建立辅助实例
  datapump destination '/u01/aux'                       >>>>==== 指定datapump目录
  dump file 't1_meta.dmp'                               >>>>==== 指定datapump文件名
  remap table 'XB'.'T1':'XBDBA'.'T2'                    >>>>==== 12.2中可以恢复到不同schema
  remap tablespace 'TBS1':'TBS2';                       >>>>==== 恢复到不同tablespace


Starting recover at 26-MAR-19
using channel ORA_DISK_1
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace PDB12C:SYSTEM
Tablespace UNDOTBS1
Tablespace PDB12C:UNDOTBS1

Creating automatic instance, with SID='AEuy'

initialization parameters used for automatic instance:
db_name=ORA12C
db_unique_name=AEuy_pitr_PDB12C_ORA12C
compatible=12.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=504M
processes=200
db_create_file_dest=/u01/aux
log_archive_dest_1='location=/u01/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used


starting up automatic instance ORA12C

Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log 
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/c-380590835-20190326-00 tag=TAG20190326T131812
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl
Finished restore at 26-MAR-19

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  11 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  10 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 9, 4, 11, 3, 10;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/aux/ORA12C/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 26-MAR-19
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/aux/ORA12C/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/aux/ORA12C/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/aux/ORA12C/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/03ttdfrp_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 26-MAR-19

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=14 STAMP=1003929916 file name=/u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=15 STAMP=1003929916 file name=/u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDB12C' "alter database datafile 
 9 online";
sql clone "alter database datafile  4 online";
sql clone 'PDB12C' "alter database datafile 
 11 online";
sql clone "alter database datafile  3 online";
sql clone 'PDB12C' "alter database datafile 
 10 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  10 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

sql statement: alter database open read only

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB12C open read only';
}
executing Memory Script

sql statement: alter pluggable database  PDB12C open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files = 
  ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes

sql statement: alter system set  control_files =   ''/u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     528482304 bytes

Fixed Size                     8794744 bytes
Variable Size                167775624 bytes
Database Buffers             348127232 bytes
Redo Buffers                   3784704 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  13 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  13;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 26-MAR-19
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/04ttdfs8_1_1 tag=TAG20190326T131745
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-MAR-19

datafile 13 switched to datafile copy
input datafile copy RECID=17 STAMP=1003929974 file name=/u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf

contents of Memory Script:
{
# set requested point in time
set until  scn 1612170;
# online the datafiles restored or switched
sql clone 'PDB12C' "alter database datafile 
 13 online";
# recover and open resetlogs
recover clone database tablespace  "PDB12C":"TBS1", "SYSTEM", "PDB12C":"SYSTEM", "UNDOTBS1", "PDB12C":"UNDOTBS1", "SYSAUX", "PDB12C":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  13 online

Starting recover at 26-MAR-19
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/arch/1_6_1002791605.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/arch/1_7_1002791605.dbf
archived log file name=/u01/arch/1_6_1002791605.dbf thread=1 sequence=6
archived log file name=/u01/arch/1_7_1002791605.dbf thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAR-19

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  PDB12C open';
}
executing Memory Script

sql statement: alter pluggable database  PDB12C open

contents of Memory Script:
{
# create directory for datapump import
sql 'PDB12C' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
# create directory for datapump export
sql clone 'PDB12C' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/u01/aux''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/aux''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_AEuy_Aopu":  
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> . . exported "XB"."T1"                                   5.046 KB       1 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_AEuy_Aopu" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_AEuy_Aopu is:
   EXPDP>   /u01/aux/t1_meta.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_AEuy_Aopu" successfully completed at Tue Mar 26 13:28:07 2019 elapsed 0 00:00:57
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_AEuy_ovnl" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_AEuy_ovnl":  
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "XBDBA"."T2"                                5.046 KB       1 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Job "SYS"."TSPITR_IMP_AEuy_ovnl" successfully completed at Tue Mar 26 13:28:21 2019 elapsed 0 00:00:05
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_temp_g9mfy1gj_.tmp deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_temp_g9mfxz6f_.tmp deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_3_g9mfzxjf_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_2_g9mfzrc3_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/onlinelog/o1_mf_1_g9mfzr7d_.log deleted
auxiliary instance file /u01/aux/AEUY_PITR_PDB12C_ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_tbs1_g9mfzojn_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_sysaux_g9mfxnvc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_sysaux_g9mfx5jc_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_undotbs1_g9mfxnvf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_undotbs1_g9mfx5jf_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/83F09DB5C9E42BE4E0556597A8E5428B/datafile/o1_mf_system_g9mfxnvd_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/datafile/o1_mf_system_g9mfx5j9_.dbf deleted
auxiliary instance file /u01/aux/ORA12C/controlfile/o1_mf_g9mfwx6s_.ctl deleted
auxiliary instance file t1_meta.dmp deleted
Finished recover at 26-MAR-19

重新检查当前数据

xb@PDB12C> select * from xbdba.t2;

        ID
----------
         1

12.2中，在做recover表或分区的时候，因为需要创建辅助实例，所以会提前检查可以空间，如果指定的辅助目录空间不足，则操作无法进行。

2019-03-18发表 xbdba Dataguard3 分钟读完 (大约507个字)

处理一个dg gap同步问题

有一套10g的DG发现不同步，备库显示MRP进程正在等待gap的归档日志，需要从387105-387676

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING       1     387677    2033665       1047
ARCH      CLOSING       1     387676    2033665       1048
MRP0      WAIT_FOR_GAP      1     387105          0      0
RFS   IDLE          0      0          0      0
RFS   IDLE          1     387678     638776      1

SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
    387104

而主库的归档日志已经被删除，则只能通过增量备份的方式

根据当前scn对主库进行增量备份

-- 查询备库当前scn
SQL> select  to_char(current_scn) from v$database;

TO_CHAR(CURRENT_SCN)
----------------------------------------
9164156262

run{
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
allocate channel t4 type disk;
allocate channel t5 type disk;
allocate channel t6 type disk;
backup INCREMENTAL from scn 9164156262 database format '/u01/arch/incre_%U';
release channel t1;
release channel t2;
release channel t3;
release channel t4;
release channel t5;
release channel t6;
}

创建备库控制文件

1
2
3

SQL> alter database create standby controlfile as '/u01/arch/standby.ctl';

Database altered.

然后替换掉备库的控制文件

1
2
3

SQL> startup nomount

RMAN> restore standby controlfile from '/u01/backup/standby.ctl';

备库恢复增量备份

RMAN> catalog start with '/u01/backup';

searching for all files that match the pattern /u01/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/backup/incre_1itso2at_1_1
File Name: /u01/backup/incre_1jtso2av_1_1
File Name: /u01/backup/incre_1htso1vf_1_1
File Name: /u01/backup/incre_1etso1vf_1_1
File Name: /u01/backup/incre_1ftso1vf_1_1
File Name: /u01/backup/incre_1ctso1vf_1_1
File Name: /u01/backup/incre_1dtso1vf_1_1
File Name: /u01/backup/incre_1gtso1vf_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/backup/incre_1itso2at_1_1
File Name: /u01/backup/incre_1jtso2av_1_1
File Name: /u01/backup/incre_1htso1vf_1_1
File Name: /u01/backup/incre_1etso1vf_1_1
File Name: /u01/backup/incre_1ftso1vf_1_1
File Name: /u01/backup/incre_1ctso1vf_1_1
File Name: /u01/backup/incre_1dtso1vf_1_1
File Name: /u01/backup/incre_1gtso1vf_1_1

RMAN> recover database noredo;

备库应用日志

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

-- 备库
SQL> SELECT max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
    387684                                  >>>>====现在与主库一致

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING       1     387684          1       1004
ARCH      CLOSING       1     387683      30721       1068
RFS   IDLE          0      0          0      0
RFS   IDLE          1     387685     944650      1
MRP0      APPLYING_LOG      1     387685     944649    2097152          >>>>====状态正常

基本上碰到gap处理的步骤就是这样简单几步就行。

2019-03-02发表 xbdba Troubleshooting14 分钟读完 (大约2104个字)

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

在做一个rman异机恢复完成后，数据库无法打开，报错提示

Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'

用的源库一个rman 0级备份以及后来的部分归档日志，拷贝到这个测试环境，恢复完controlfile之后，进行了基于scn的recover，scn则是restore之后控制文件里显示的scn号

1	recover database until scn 75238614094;

尝试open时报错

16:54:07 sys. >alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'

但是检查scn均一致，有点奇怪,因为一般来说如果这三个视图的checkpoint_change#一致的话，数据库是应该可以正常打开的。

sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;

     DFILE_CKP         DB_CKP         DH_CKP
------------------ ------------------ ------------------
       75238614094    75238614094        75238614094

检测数据文件的状态

set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

sys.>select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME          RESETLOGS_CHANGE# RESETLOGS_TIME                COUNT(*) FUZ
------- ------------------ ----------------------- ------------------------------ ----------------------- ------------------------------ ---
ONLINE         75238614094 23-FEB-2019 00:15:46               67622732054 02-NOV-2018 22:00:36                    45 NO
ONLINE         75238614094 23-FEB-2019 00:15:46               67622732054 02-NOV-2018 22:00:36                     8 YES       >>>>====有8个文件的状态不一致

fuzzy值为yes表示数据文件在checkpoint以后仍然有些写入的动作，比如有一些比存储在v$datafile_header.checkpoint_change#字段里scn更高的scn事务对数据文件进行了修改操作，所以为了让数据文件保持一致性，则需要前滚应用日志

查看需要哪些归档日志来恢复数据文件

select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;

        MIN(FHRBA_SEQ)             MAX(FHRBA_SEQ)
------------------------------ ------------------------------
             21201              21201

这个查询结果表示所需要的最小和最大日志文件，为了让数据文件保持一致，你必须要应用这之间所有的日志。

查看当前使用的controlfile

select name, controlfile_type from v$database ;

NAME                   CONTROL
------------------------------ -------
XXX                BACKUP               >>>>====使用恢复出来的控制文件

查看当前redo log

select     substr(L.GROUP#,1,6)       GROUP#
        ,substr(L.THREAD#,1,7)         THREAD#
        ,substr(L.SEQUENCE#,1,10)      SEQUENCE#
        ,substr(L.MEMBERS,1,7)         MEMBERS
        ,substr(L.ARCHIVED,1,8)        ARCHIVED
        ,substr(L.STATUS,1,10)         STATUS
        ,substr(L.FIRST_CHANGE#,1,16)  FIRST_CHANGE#
        ,substr(LF.member,1,60)        REDO_LOGFILE
  from GV$LOG L, GV$LOGFILE LF
 where L.GROUP# = LF.GROUP# ;

GROUP#       THREAD#        SEQUENCE#        MEMBERS    ARCHIV STATUS           FIRST_CHANGE#            REDO_LOGFILE
------------ -------------- -------------------- -------------- ------ -------------------- -------------------------------- ----------------------------------
1        1          21201        1      YES    ACTIVE           75238614094              /home/oradata/xxx/redo01.log                
2        1          21199        1      YES    INACTIVE         75238587795              /home/oradata/xxx/redo02.log
11       1          21203        1      NO     CURRENT          75238632711              /home/oradata/xxx/redo11.log               >>>>====这是当前日志，包含最近的redo
10       1          21202        1      YES    ACTIVE           75238624057              /home/oradata/xxx/redo10.log
3        1          21200        1      YES    INACTIVE         75238601713              /home/oradata/xxx/redo03.log

通过前面查询的数据文件头的最小checkpoint_change#，用于查询所要从哪个日志开始恢复，最小的日志号一直到current redo中间的所有日志都必须可用，才能保证恢复成功。

 select thread#, sequence#, substr(name,1,80) from v$Archived_log
  where 75238614094 between first_change# and next_change#;


               THREAD#              SEQUENCE# SUBSTR(NAME,1,80)
------------------------------ ------------------------------ ----------------------------------------------------------------------
                 1              21200 xxxx
                 1              21200

根据以上结果表示需要恢复21200到21203之间的日志，我们当前已经到了21201，所以总共需要恢复21201和21202 2个文件。

将备份集中的归档日志恢复出来

RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 21201 UNTIL SEQUENCE 21202;

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21201
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21202
channel ORA_DISK_1: reading from backup piece /bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: piece handle=/bak/backup/arch_u8tqihpn_1_1 
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:10:35

这里先处理控制文件里的redo路径问题，原路径是文件系统，新环境是asm，所有对所有的redo日志进行了rename

select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                 IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
     3     ONLINE  +REDO/xxx/redo03.log                           NO
     2     ONLINE  +REDO/xxx/redo02.log                           NO
     1     ONLINE  +REDO/xxx/redo01.log                           NO
     4     STANDBY +REDO/xxx/stdredo01.log                        NO
     5     STANDBY +REDO/xxx/stdredo02.log                        NO
     6     STANDBY +REDO/xxx/stdredo03.log                        NO
     7     STANDBY +REDO/xxx/stdredo04.log                        NO
    10     ONLINE  +REDO/xxx/redo10.log                           NO
    11     ONLINE  +REDO/xxx/redo11.log                           NO

ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo03.log' to '+REDO/xxx/redo03.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo02.log' to '+REDO/xxx/redo02.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo01.log' to '+REDO/xxx/redo01.log';

ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo01.log' to '+REDO/xxx/stdredo01.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo02.log' to '+REDO/xxx/stdredo02.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo03.log' to '+REDO/xxx/stdredo03.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/stdredo04.log' to '+REDO/xxx/stdredo04.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo10.log' to '+REDO/xxx/redo10.log';
ALTER DATABASE RENAME FILE '/home/oradata/xxx/redo11.log' to '+REDO/xxx/redo11.log';

当使用的是备份控制文件时，可以使用基于CANCEL的恢复

22:52:18 sys. xxx>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL ;
ORA-00279: change 75238614094 generated at 02/23/2019 00:15:46 needed for thread 1
ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429
ORA-00280: change 75238614094 for thread 1 is in sequence #21201


22:52:35 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}
+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429
ORA-00279: change 75238624057 generated at 02/23/2019 00:19:25 needed for thread 1
ORA-00289: suggestion : +NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179
ORA-00280: change 75238624057 for thread 1 is in sequence #21202
ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21201.664.1001796429' no longer needed for this recovery


22:52:49 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}
+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179
ORA-00279: change 75238632711 generated at 02/23/2019 00:22:37 needed for thread 1
ORA-00289: suggestion : +nvmedg
ORA-00280: change 75238632711 for thread 1 is in sequence #21203
ORA-00278: log file '+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21202.663.1001796179' no longer needed for this recovery


22:53:03 Spexxxy log: {>RET>=suggested | filename | AUTO | CANCEL}        >>>>====缺少进一步的日志用于恢复
auto
ORA-00308: cannot open archived log '+nvmedg'
ORA-17503: ksfdopn:2 Failed to open file +nvmedg
ORA-15045: ASM file name '+nvmedg' is not in reference form


ORA-00308: cannot open archived log '+nvmedg'
ORA-17503: ksfdopn:2 Failed to open file +nvmedg
ORA-15045: ASM file name '+nvmedg' is not in reference form


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+NVMEDG/xxx/datafile/system01.dbf'

当我恢复完21201和21202两个文件以后，发现依然无法打开数据库。

sys. >alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DG/xxx/datafile/system01.dbf'

那就表示目前数据文件仍然有不一致的地方，最后需要的部分则在current redo当中，因为我这个是测试环境，所以虽然没有current redo，但是我有21203号之后的归档日志，所以直接采用这些归档日志来进行recover，那具体要恢复到哪个scn呢，可以通过下面语句查询出来

select  min(FHSCN) "LOW FILEHDR SCN"
           , max(FHSCN) "MAX FILEHDR SCN"
           , max(FHAFS) "Min PITR ABSSCN"
        from X$KCVFH ;

LOW FILEHDR SCN  MAX FILEHDR SCN  Min PITR ABSSCN
---------------- ---------------- ----------------
75238632711  75238632711      75238770594

“LOW FILEHDR SCN” – 恢复进程开始的scn

“MAX FILEHDR SCN” – 为了使所有数据文件保持一致必须要恢复到的scn

IF “Min PITR ABSSCN” != 0 AND > “MAX FILEHDR SCN”

THEN “Min PITR ABSSCN” 则是为了使所有数据文件保持一致必须要恢复到的scn

所以根据上述原则必须要恢复到75238770594

RMAN> recover database until scn 75238770594;

Starting recover at 2019-03-01 23:16:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2065 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21203
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21204
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21205
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21206
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21207
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=21208
channel ORA_DISK_1: reading from backup piece /bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: piece handle=/bak/backup/arch_u8tqihpn_1_1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:13:25
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21203.677.1001805529 thread=1 sequence=21203
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21203.677.1001805529 RECID=66515 STAMP=1001805655
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21204.678.1001805657 thread=1 sequence=21204
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21204.678.1001805657 RECID=66516 STAMP=1001805790
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21205.679.1001806059 thread=1 sequence=21205
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21205.679.1001806059 RECID=66517 STAMP=1001806190
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21206.675.1001805395 thread=1 sequence=21206
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21206.675.1001805395 RECID=66513 STAMP=1001805528
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21207.676.1001805395 thread=1 sequence=21207
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21207.676.1001805395 RECID=66512 STAMP=1001805524
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21208.674.1001805393 thread=1 sequence=21208
channel default: deleting archived log(s)
archived log file name=+NVMEDG/xxx/archivelog/2019_03_01/thread_1_seq_21208.674.1001805393 RECID=66514 STAMP=1001805536
media recovery complete, elapsed time: 00:00:17
Finished recover at 2019-03-01 23:30:16

这次的恢复就正常没有报错，检查数据文件的scn和状态等

sys >select (select distinct checkpoint_change# from v$datafile) dfile_ckp,(select checkpoint_change# from v$database) db_ckp,(select distinct checkpoint_change# from v$datafile_header) dh_ckp from dual;

     DFILE_CKP         DB_CKP         DH_CKP
------------------ ------------------ ------------------
       75238770595    75238770595        75238770595

set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';

select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;

STATUS          CHECKPOINT_CHANGE# CHECKPOINT_TIME              RESETLOGS_CHANGE# RESETLOGS_TIME                    COUNT(*) FUZ
------- ------------------------------ ----------------------- ------------------------------ ----------------------- ------------------------------ ---
ONLINE             75238770595 01-MAR-2019 23:36:12           75238770595 01-MAR-2019 23:36:12                53 NO

尝试打开数据库

alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 97239
Session ID: 1921 Serial number: 1

重建控制文件

CREATE CONTROLFILE REUSE DATABASE "XXX" NORESETLOGS  ARCHIVELOG
......省略

select name, controlfile_type from v$database ;

NAME                   CONTROLFILE_TY
------------------------------ --------------
XXX                CREATED

alter database open;

Database altered.

至此恢复完毕。

2018-10-26发表 xbdba 备份恢复4 分钟读完 (大约546个字)

归档模式下恢复offline drop的datafile

先说一下常见的offline drop 和offline之间的区别

Alter database datafile offline

一般如下场景下会选择这种方式

离线备份数据文件
重命名或更换文件路径，必须要offline datafile或者tablespace
数据文件写入出现问题时，会自动offline这个datafile，当解决问题后得手动online
数据文件丢失或出错时，你必须手动offline才能open database

Alter database datafile offline drop

这里的关键字_DROP_并不是真正删除文件，只是标记为不用了，可能未来会删除，数据文件仍然存在于数据字典中，如果你非要删除可以通过第三种方式

ALTER TABLESPACE … DROP DATAFILE 或 DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES

前者删除datafile也有不少限制

数据库必须为打开状态
数据datafile非空，你不能直接删除这个文件，必须要先移除对象或者直接删除这个datafile所在的表空间
不能删除表空间里的第一个文件或者唯一的一个文件，意味着不能删除bigfile tablespace下的数据文件
不能删除read-only的数据文件
不能删除system表空间下的数据文件
不能删除offline状态的locally managed tablespace管理的数据文件

SYS@xb> alter database datafile 6 offline drop;

数据库已更改。

SYS@xb> select file#,status from v$datafile where file#=6;

     FILE# STATUS
---------- -------
     6 RECOVER

SYS@xb> c/datafile/datafile_header
  1* select file#,status from v$datafile_header where file#=6
SYS@xb> /

     FILE# STATUS
---------- -------
     6 OFFLINE

SYS@xb> alter system switch logfile;

系统已更改。

SYS@xb> recover datafile 6;                                >>>>====这里涉及到recover操作，如果恢复的够快，redo未被覆盖则不需要归档，否则必须要archivelog模式下
完成介质恢复。
SYS@xb> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME     NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------- ------------ --------------
     1      1        125 1073741824    512      1 YES ACTIVE             1276783 23-10月-18   1278142 23-10月-18
     2      1        124 1073741824    512      1 YES INACTIVE           1255472 23-10月-18   1276783 23-10月-18
     3      1        126 1073741824    512      1 NO  CURRENT            1278142 23-10月-18       2.8147E+14

SYS@xb> select file#,status from v$datafile where file#=6;

     FILE# STATUS
---------- -------
     6 OFFLINE

SYS@xb> select file#,status from v$datafile_header where file#=6;

     FILE# STATUS
---------- -------
     6 OFFLINE

SYS@xb> alter database datafile 6 online;

数据库已更改。

SYS@xb> select file#,status from v$datafile_header where file#=6;

     FILE# STATUS
---------- -------
     6 ONLINE

SYS@xb> select file#,status from v$datafile where file#=6;

     FILE# STATUS
---------- -------
     6 ONLINE

2018-10-25发表 xbdba 备份恢复3 分钟读完 (大约456个字)

归档模式下恢复无备份的lost datafile

场景:

你在os层面丢失了数据文件，并且没有相应的备份
数据库处于archivelog模式
从数据文件创建到目前所有的归档日志都完好

由于没有备份，数据库无法正常打开，除非将数据文件或表空间删除，这样就会造成数据的丢失

SYS@xb> archive log list;
数据库日志模式       存档模式
自动存档         启用
存档终点        /u01/arch
最早的联机日志序列     121
下一个存档日志序列   123
当前日志序列         123

SYS@xb> create tablespace tbs_ts datafile '/u01/app/oracle/oradata/xb/tbs_ts01.dbf' size 20m;

表空间已创建。

SYS@xb> create table tb_test (id number) tablespace tbs_ts;

表已创建。

SYS@xb> insert into tb_test values (1);

已创建 1 行。

SYS@xb> commit;

提交完成。

SYS@xb> select file_id,file_name from dba_data_files where tablespace_name='TBS_TS';

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
     6 /u01/app/oracle/oradata/xb/tbs_ts01.dbf

删除物理文件，模拟文件丢失

[oracle@xb xb]$ mv tbs_ts01.dbf tbs_ts01.dbf.bak

SYS@xb> shutdown immediate;
ORA-01116: 打开数据库文件 6 时出错
ORA-01110: 数据文件 6: '/u01/app/oracle/oradata/xb/tbs_ts01.dbf'
ORA-27041: 无法打开文件
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@xb> select status from v$instance;

STATUS
------------
OPEN

SYS@xb> shutdown abort;
ORACLE 例程已经关闭。
SYS@xb> startup mount
ORACLE 例程已经启动。

Total System Global Area  943669248 bytes
Fixed Size          2258880 bytes
Variable Size         666896448 bytes
Database Buffers      268435456 bytes
Redo Buffers            6078464 bytes
数据库装载完毕。

SYS@xb> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- --------------
     6 ONLINE  ONLINE  FILE NOT FOUND                                 0

使用alter database create datafile <> as ….的方式，重建这个丢失的数据文件：

1
2
3

SYS@xb> alter database create datafile 6;

数据库已更改。

使用归档日志和redo恢复datafile

SYS@xb> recover datafile 6;
完成介质恢复。
SYS@xb> alter database open;

数据库已更改。

SYS@xb> select * from tb_test;

    ID
----------
     1

2018-10-22发表 xbdba 备份恢复20 分钟读完 (大约2951个字)

TSPITR恢复删除的表空间

表空间时间点恢复，是Oracle在基于冷备，热备恢复以外的一种以表空间为粒度的，不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMAN以及DataPump这2个备份恢复工具来实现时间点恢复。

TSPITR试用的场景

恢复单独的逻辑对象而不影响数据库的其他对象
可以恢复table到DDL操作之前，这是flashback table无法做到的
可以恢复 drop purge的表
恢复逻辑错误的表
恢复被drop的表空间，即使没有catalog也可以

TSPITR限制条件

上面说了一些很方便的场景，但是也有一些限制条件

必须要在archivelog模式下
如果要恢复表空间到其rename之前，则在恢复的时候必须要用之前的名称（这样在恢复完成之后，会发现数据库中存在两个一样的表空间）
如果table A同时用到了tablespace B和C，则你必须要同时对两个表空间做TSPITR
如果表和索引分别出在不同的表空间，则必须先将索引drop叼
你不能对现有的default tablespace做恢复，要先修改default tablespace
你不能恢复含有以下对象的表空间
1. 对象含有下级对象（例如物化视图），容器对象（分区表）
2. 回滚段
3. 属于SYS用户的对象

TSPITR过程中自动做了哪些操作

如果要被恢复的表空间目前仍存在于数据库中，则检查这个表空间是否有其他依赖的对象，如果是则需要解决这些依赖关系
检查是否可以连到辅助实例，如果没有则会新建一个
如果表空间目前没有被drop的话，则会在目标库先将表空间offline
restore一个目标时间之前的控制文件到辅助实例
将备份集里的数据文件恢复到辅助实例（包含system,sysaux,undo和要恢复的数据文件）
recover辅助实例的数据文件到指定时间
用resetlogs打开辅助实例
将辅助实例的tablespace置于read-only
用数据泵的方式将辅助实例的表空间导出生成一个用于传输表空间的dump file
关闭辅助实例
drop源库的表空间（如果存在的话）
数据泵工具读取导出的dump file，然后将表空间插入到源库
将插入的表空间置于read-write，然后立刻offline之
删除所有的辅助实例文件

现在做个小测试

创建表空间

SYS@xb> create tablespace tbs_xb datafile '/u01/app/oracle/oradata/xb/tbs_xb01.dbf' size 10m reuse;

表空间已创建。

SYS@xb> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_XB

创建用户和表

SYS@xb> create user xb identified by xb;

用户已创建。

SYS@xb> grant dba to xb;

授权成功。

SYS@xb> conn xb/xb
已连接。

XB@xb> create table tb_xb tablespace tbs_xb as select * from dba_tables;

表已创建。

XB@xb> select count(1) from tb_xb;

  COUNT(1)
----------
      2797

查看当前logfile sequence

XB@xb> select sequence# from v$log where status='CURRENT';

 SEQUENCE#
----------
       117

rman备份

1
2
3

[oracle@xb ContentsXML]$ rman target /

RMAN> backup database plus archivelog;

drop tablespace

SYS@xb> alter system switch logfile;

系统已更改。

SYS@xb> /

系统已更改。

SYS@xb> /

系统已更改。

SYS@xb> drop tablespace tbs_xb including contents and datafiles;

表空间已删除。

SYS@xb> select sequence# from v$log where status='CURRENT';

 SEQUENCE#
----------
       122

当前我们删除完表空间以后所在的sequence no为122，如果我们要恢复到表空间删除之前，那么必须要恢复到122之前

TSPITR

RMAN> recover tablespace tbs_xb until logseq 122 auxiliary destination '/u01/app/oracle/oradata/';

启动 recover 于 20-10月-18
使用通道 ORA_DISK_1
RMAN-05026: 警告: 假定以下表空间集适用于指定的时间点

表空间列表要求具有 UNDO 段
表空间 SYSTEM
表空间 UNDOTBS1

使用 SID='phsf' 创建自动实例

供自动实例使用的初始化参数:
db_name=XB
db_unique_name=phsf_tspitr_XB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u01/app/oracle/oradata/                     >>>>====这里就是设置的辅助目标路径
log_archive_dest_1='location=/u01/app/oracle/oradata/'
#No auxiliary parameter file used


启动自动实例 XB

Oracle 实例已启动

系统全局区域总计    1068937216 字节

Fixed Size                     2260088 字节
Variable Size                281019272 字节
Database Buffers             780140544 字节
Redo Buffers                   5517312 字节
自动实例已创建


已从目标数据库中删除的表空间的列表:
表空间 tbs_xb

内存脚本的内容:
{
# set requested point in time
set until  logseq 122 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
正在执行内存脚本

正在执行命令: SET until clause

启动 restore 于 20-10月-18
分配的通道: ORA_AUX_DISK_1
通道 ORA_AUX_DISK_1: SID=18 设备类型=DISK

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在还原控制文件
通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_ncsnf_TAG20181020T132437_fwoh15g3_.bkp
通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_ncsnf_TAG20181020T132437_fwoh15g3_.bkp 标记 = TAG20181020T132437
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:01
输出文件名=/u01/app/oracle/oradata/XB/controlfile/o1_mf_fwohcoyp_.ctl
完成 restore 于 20-10月-18

sql 语句: alter database mount clone database

sql 语句: alter system archive log current

sql 语句: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

内存脚本的内容:
{
# set requested point in time
set until  logseq 122 thread 1;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to 
 "/u01/app/oracle/oradata/xb/tbs_xb01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
正在执行内存脚本

正在执行命令: SET until clause

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

正在执行命令: SET NEWNAME

临时文件 1 在控制文件中已重命名为 /u01/app/oracle/oradata/XB/datafile/o1_mf_temp_%u_.tmp

启动 restore 于 20-10月-18
使用通道 ORA_AUX_DISK_1

通道 ORA_AUX_DISK_1: 正在开始还原数据文件备份集
通道 ORA_AUX_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_AUX_DISK_1: 将数据文件 00001 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_system_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00003 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00002 还原到 /u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_%u_.dbf
通道 ORA_AUX_DISK_1: 将数据文件 00005 还原到 /u01/app/oracle/oradata/xb/tbs_xb01.dbf
通道 ORA_AUX_DISK_1: 正在读取备份片段 /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_nnndf_TAG20181020T132437_fwoh0och_.bkp
通道 ORA_AUX_DISK_1: 段句柄 = /u01/app/oracle/fast_recovery_area/XB/backupset/2018_10_20/o1_mf_nnndf_TAG20181020T132437_fwoh0och_.bkp 标记 = TAG20181020T132437
通道 ORA_AUX_DISK_1: 已还原备份片段 1
通道 ORA_AUX_DISK_1: 还原完成, 用时: 00:00:15
完成 restore 于 20-10月-18

数据文件 1 已转换成数据文件副本
输入数据文件副本 RECID=5 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_system_fwohcvqx_.dbf
数据文件 3 已转换成数据文件副本
输入数据文件副本 RECID=6 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_fwohcvr1_.dbf
数据文件 2 已转换成数据文件副本
输入数据文件副本 RECID=7 STAMP=990019850 文件名=/u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_fwohcvqz_.dbf

内存脚本的内容:
{
# set requested point in time
set until  logseq 122 thread 1;
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TBS_XB", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
正在执行内存脚本

正在执行命令: SET until clause

sql 语句: alter database datafile  1 online

sql 语句: alter database datafile  3 online

sql 语句: alter database datafile  2 online

sql 语句: alter database datafile  5 online

启动 recover 于 20-10月-18
使用通道 ORA_AUX_DISK_1

正在开始介质的恢复

线程 1 序列 118 的归档日志已作为文件 /u01/arch/1_118_984998420.dbf 存在于磁盘上
线程 1 序列 119 的归档日志已作为文件 /u01/arch/1_119_984998420.dbf 存在于磁盘上
线程 1 序列 120 的归档日志已作为文件 /u01/arch/1_120_984998420.dbf 存在于磁盘上
线程 1 序列 121 的归档日志已作为文件 /u01/arch/1_121_984998420.dbf 存在于磁盘上
归档日志文件名=/u01/arch/1_118_984998420.dbf 线程=1 序列=118
归档日志文件名=/u01/arch/1_119_984998420.dbf 线程=1 序列=119
归档日志文件名=/u01/arch/1_120_984998420.dbf 线程=1 序列=120
归档日志文件名=/u01/arch/1_121_984998420.dbf 线程=1 序列=121
介质恢复完成, 用时: 00:00:00
完成 recover 于 20-10月-18

数据库已打开

内存脚本的内容:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TBS_XB read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/oradata/''";
}
正在执行内存脚本

sql 语句: alter tablespace  TBS_XB read only

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''

sql 语句: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/oradata/''

正在执行元数据导出...
   EXPDP> 启动 "SYS"."TSPITR_EXP_phsf":  
   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
   EXPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_EXP_phsf" 
   EXPDP> ******************************************************************************
   EXPDP> SYS.TSPITR_EXP_phsf 的转储文件集为:
   EXPDP>   /u01/app/oracle/oradata/tspitr_phsf_68494.dmp
   EXPDP> ******************************************************************************
   EXPDP> 可传输表空间 TBS_XB 所需的数据文件:
   EXPDP>   /u01/app/oracle/oradata/xb/tbs_xb01.dbf
   EXPDP> 作业 "SYS"."TSPITR_EXP_phsf" 已于 星期六 10月 20 13:31:26 2018 elapsed 0 00:00:22 成功完成
导出完毕


内存脚本的内容:
{
# shutdown clone before import
shutdown clone immediate
}
正在执行内存脚本

数据库已关闭
数据库已卸装
Oracle 实例已关闭

正在执行元数据导入...
   IMPDP> 已成功加载/卸载了主表 "SYS"."TSPITR_IMP_phsf" 
   IMPDP> 启动 "SYS"."TSPITR_IMP_phsf":  
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
   IMPDP> 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> 作业 "SYS"."TSPITR_IMP_phsf" 已于 星期六 10月 20 13:31:43 2018 elapsed 0 00:00:02 成功完成
导入完毕


内存脚本的内容:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  TBS_XB read write';
sql 'alter tablespace  TBS_XB offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
正在执行内存脚本

sql 语句: alter tablespace  TBS_XB read write

sql 语句: alter tablespace  TBS_XB offline

sql 语句: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

删除自动实例
自动实例已删除
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_temp_fwohdmto_.tmp
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_3_fwohdjsz_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_2_fwohdg6h_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/onlinelog/o1_mf_1_fwohdcgm_.log
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_sysaux_fwohcvqz_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_undotbs1_fwohcvr1_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/datafile/o1_mf_system_fwohcvqx_.dbf
已删除辅助实例文件 /u01/app/oracle/oradata/XB/controlfile/o1_mf_fwohcoyp_.ctl
完成 recover 于 20-10月-18

rman 其实是用了transportable tablespace机制将删除的表空间重新插回database

SYS@xb> select tablespace_name,status,plugged_in from dba_tablespaces;

TABLESPACE_NAME          STATUS  PLU
------------------------------ --------- ---
SYSTEM             ONLINE  NO
SYSAUX             ONLINE  NO
UNDOTBS1           ONLINE  NO
TEMP             ONLINE  NO
USERS            ONLINE  NO
TBS_XB             OFFLINE   YES                    >>>>====表示plug

已选择6行。

恢复表

SYS@xb> alter tablespace tbs_xb online;

表空间已更改。

SYS@xb> conn xb/xb

XB@xb> select count(1) from tb_xb;

  COUNT(1)
----------
      2797

12c新特性：Rman中通过网络恢复文件

12c中rman恢复表和分区

处理一个dg gap同步问题

根据当前scn对主库进行增量备份

创建备库控制文件

备库恢复增量备份

备库应用日志

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

归档模式下恢复offline drop的datafile

Alter database datafile offline

Alter database datafile offline drop

ALTER TABLESPACE … DROP DATAFILE 或 DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES

归档模式下恢复无备份的lost datafile

TSPITR恢复删除的表空间

TSPITR试用的场景

TSPITR限制条件

TSPITR过程中自动做了哪些操作

创建表空间

创建用户和表

查看当前logfile sequence

rman备份

drop tablespace

TSPITR

恢复表

链接

分类

最新文章

归档

标签

12c新特性：Rman中通过网络恢复文件

12c中rman恢复表和分区

处理一个dg gap同步问题

根据当前scn对主库进行增量备份

创建备库控制文件

备库恢复增量备份

备库应用日志

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

归档模式下恢复offline drop的datafile

Alter database datafile offline

Alter database datafile offline drop

ALTER TABLESPACE &#8230; DROP DATAFILE 或 DROP TABLESPACE &#8230; INCLUDING CONTENTS AND DATAFILES

归档模式下恢复无备份的lost datafile

TSPITR恢复删除的表空间

TSPITR试用的场景

TSPITR限制条件

TSPITR过程中自动做了哪些操作

创建表空间

创建用户和表

查看当前logfile sequence

rman备份

drop tablespace

TSPITR

恢复表

链接

分类

最新文章

归档

标签

ALTER TABLESPACE … DROP DATAFILE 或 DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES