分类目录归档:备份恢复

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 TIMEUNTIL 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表或分区的时候,因为需要创建辅助实例,所以会提前检查可以空间,如果指定的辅助目录空间不足,则操作无法进行。

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号

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.

至此恢复完毕。

归档模式下恢复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

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

场景:

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

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

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 ….的方式,重建这个丢失的数据文件:

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

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备份

[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