【RMAN】利用备份片还原数据库
【RMAN】利用备份片还原数据库(上)： http://blog.itpub.net/26736162/viewspace-1621581/
在上一篇blog中我们介绍了采用dbms_backup_restore来找回控制文件并恢复整个数据库的方法，本篇blog我们介绍采用创建临时库来找回控制文件的方法。
1.1.1 方法二：尝试采用创建临时库来找回控制文件
由于RMAN必须工作在MOUNT模式，所有的数据文件都丢失，无法通过只重建控制文件将其启动到MOUNT模式，所以这里利用dbca创建一个临时数据库环境，数据库的名称与原有名称保持不变，文件存放到默认位置即可。
一、 搭建临时库来注册备份集
这里我们假设有一个临时环境供我们测试，ORACLE_SID=orcltest，由于是第二天做的测试，所以我重新对原库做了备份并传递到备份库
[root@rhel6_lhr backupset]# scp -r 2015_05_02 oracle@192.168.59.129:/tmp
oracle@192.168.59.129's password:
o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp 100% 9728 9.5KB/s 00:00
o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp 100% 236MB 6.6MB/s 00:36
o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp 100% 1096MB 54.8MB/s 00:20
o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp 100% 10MB 9.8MB/s 00:00
o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp 100% 9600KB 9.4MB/s 00:00
[root@rhel6_lhr backupset]#
[root@rhel6_lhr 2015_05_02]# ll -h
total 1.4G
-rw-r----- 1 oracle asmadmin 9.9M May 2 11:23 o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
-rw-r----- 1 oracle asmadmin 9.5K May 2 11:31 o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
-rw-r----- 1 oracle asmadmin 9.4M May 2 11:31 o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
-rw-r----- 1 oracle asmadmin 236M May 2 10:56 o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
-rw-r----- 1 oracle asmadmin 1.1G May 2 11:31 o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
由下边的方法（备份集中无控制文件的备份）的方法得知，这里创建一个临时库的方法比较麻烦，不太推荐：
[oracle@orcltest tmp]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname orcltest -sid orcltest -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination /u02/app/oracle/oradata -redoLogFileSize 50 -recoveryAreaDestination /u02/app/oracle/flash_recovery_area -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 30 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
10% complete
17% complete
24% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/orcltest/orcltest0.log" for further details.
[oracle@orcltest tmp]$ crsstat
Name Type Target State Host
------------------------------ -------------------------- ---------- --------- -------
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE orcltest
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE orcltest
ora.asm ora.asm.type ONLINE ONLINE orcltest
ora.cssd ora.cssd.type ONLINE ONLINE orcltest
ora.diskmon ora.diskmon.type OFFLINE OFFLINE
ora.evmd ora.evm.type ONLINE ONLINE orcltest
ora.ons ora.ons.type OFFLINE OFFLINE
ora.orcltest.db ora.database.type ONLINE ONLINE orcltest
[oracle@orcltest 2015_05_02]$ ll
total 1383664
-rw-r----- 1 oracle oinstall 10278400 May 2 11:34 o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
-rw-r----- 1 oracle oinstall 9728 May 2 11:33 o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
-rw-r----- 1 oracle oinstall 9830400 May 2 11:34 o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
-rw-r----- 1 oracle oinstall 247463936 May 2 11:34 o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
-rw-r----- 1 oracle oinstall 1149280256 May 2 11:34 o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
[oracle@orcltest 2015_05_02]$ ORACLE_SID=orcltest
[oracle@orcltest 2015_05_02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 11:36:51 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest 2015_05_02]$
[oracle@orcltest 2015_05_02]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 11:37:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLTEST (DBID=2626150865)
RMAN> list backupset;
specification does not match any backup in the repository
RMAN>
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
no files cataloged
List of Files Which Where Not Cataloged
=======================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN-07518: Reason: Foreign database file DBID: 4270446895 Database Name: ORA11G
RMAN>
RMAN> catalog backuppiece '/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp';
ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
ORA-19691: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp is from different database: id=4270446895, name=ORA11G
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of catalog command at 05/02/2015 11:40:26
RMAN-06209: List of failed objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
RMAN>
RMAN>
由此可以看出，命令catalog start with '/tmp/2015_05_02/'; 在异库注册可以查看到原库的信息：DBID: 4270446895 Database Name: ORA11G，（10g下测试也可以获取到这些信息）这里dbname和dbid都不一样所以不能注册在该数据库下，将备份片的信息加入到控制文件的时候报错，原因在于原有数据库和临时数据库的DBID不同，那么我们是否可以尝试修改临时数据库的DBID，使它与原有数据库DBID保持一致呢，试试呗。
1、 修改dbname和dbid
首先启动临时库到open read only状态，然后执行脚本：
[oracle@orcltest dbs]$ ORACLE_SID=orcltest
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 12:56:05 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 281021824 bytes
Database Buffers 117440512 bytes
Redo Buffers 8503296 bytes
Database mounted.
Database opened.
SQL> @?/dbs/change_dbid.sql
PL/SQL procedure successfully completed.
OLD_NAME
--------------------------------
ORCLTEST
Enter the new Database Name:ORA11G
Enter the new Database ID:4270446895
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Convert ORCLTEST(2626156129) to ORA11G(4270446895)
PL/SQL procedure successfully completed.
ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/system01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/sysaux01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/undotbs01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/users01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/example01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u02/app/oracle/oradata/orcltest/temp01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
PL/SQL procedure successfully completed.
SQL> create pfile from spfile;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest dbs]$ ll
total 52
-rw-r--r-- 1 oracle oinstall 2239 May 2 12:33 change_dbid.sql
-rw-rw---- 1 oracle oinstall 1544 May 2 10:56 hc_DBUA5452531.dat
-rw-rw---- 1 oracle oinstall 1544 May 2 12:45 hc_ORA11G.dat
-rw-rw---- 1 oracle oinstall 1544 May 2 12:56 hc_orcltest.dat
-rw-r----- 1 oracle oinstall 165 May 2 10:54 initDBUA5452531.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 640 May 2 12:10 initORA11G.ora
-rw-r--r-- 1 oracle oinstall 997 May 2 12:58 initorcltest.ora
-rw-r----- 1 oracle oinstall 0 May 2 10:55 lkinstDBUA5452531
-rw-r----- 1 oracle oinstall 24 May 2 12:10 lkORA11G
-rw-r----- 1 oracle oinstall 24 Apr 21 16:27 lkORCLTEST
-rw-r----- 1 oracle oinstall 1536 May 2 10:54 orapwDBUA5452531
-rw-r----- 1 oracle oinstall 1536 May 2 12:53 orapworcltest
-rw-r----- 1 oracle oinstall 3584 May 2 12:57 spfileorcltest.ora
[oracle@orcltest dbs]$ vi initorcltest.ora
。。。。
*.db_name='ORA11G'
。。。。
[oracle@orcltest dbs]$ mv initorcltest.ora initORA11G.ora
[oracle@orcltest dbs]$ ORACLE_SID=ORA11G
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 13:04:44 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup mount;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 272633216 bytes
Database Buffers 125829120 bytes
Redo Buffers 8503296 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
4270446895 ORA11G
SQL>
好了，和原库一样了，现在我们重新注册
2、 重新注册
[oracle@orcltest dbs]$ORACLE_SID=ORA11G
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:10:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895)
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN>
成功注册，接下来就简单了，查看备份集中含有哪些内容
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 9.00K DISK 00:00:00 02-MAY-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 9.80M DISK 00:00:00 02-MAY-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN>
我们可以看到，控制文件和spfile都在备份文件： /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp 中，有归档文件，有数据文件，此时我们就可以考虑还原策略了，我们可以使用备份中的spfile和控制文件，也可以只还原数据文件，这个根据自己的需要而定，这里我们测试的话就从spfile、control file，datafile都还原吧。
二、 还原操作 1、 还原spfile
RMAN> restore spfile from '/tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp';
Starting restore at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 02-MAY-15
RMAN>
[oracle@orcltest ~]$ cd $ORACLE_HOME/dbs
[oracle@orcltest dbs]$ ll spfile*
-rw-r----- 1 oracle oinstall 2560 May 2 13:21 spfileORA11G.ora
-rw-r----- 1 oracle oinstall 3584 May 2 12:57 spfileorcltest.ora
[oracle@orcltest dbs]$
2、 还原控制文件
RMAN> restore controlfile to '/tmp/2015_05_02/cont.ctl' from '/tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp';
Starting restore at 02-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-MAY-15
RMAN>
这里可以采用原数据库的控制文件，即现在还原出来的控制文件，也可以采用目前临时库的控制文件，都可以，当然，如果备份集中没有控制文件的备份的话我们可以考虑 采用临时库的控制文件，或者备份片段中没有控制文件的备份 这个方法。
3、 还原database
这里分情况，如果采用原库的控制文件的话，我们可以这样还原：
①、 方法一：采用原库控制文件--推荐
将原库控制文件拷贝到pfile定义的相关目录：
[oracle@orcltest orcltest]$ cp /tmp/2015_05_02/cont.ctl /u02/app/oracle/flash_recovery_area/orcltest/control02.ctl
[oracle@orcltest orcltest]$ cp /tmp/2015_05_02/cont.ctl /u02/app/oracle/oradata/orcltest/control01.ctl
[oracle@orcltest orcltest]$ cd /u01/app/oracle/oradata/ora11g/
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:32:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
6 9.80M DISK 00:00:00 02-MAY-15
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 1.07G DISK 00:07:46 02-MAY-15
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
RMAN> delete backupset;
Starting implicit crosscheck backup at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 02-MAY-15
Starting implicit crosscheck copy at 02-MAY-15
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-MAY-15
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_2_bn8o55o8_.arc
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_3_bn8o6tkw_.arc
File Name: /u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_1_bn8o4j7z_.arc
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
6 6 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
7 7 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp RECID=6 STAMP=878642633
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA11G/backupset/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp RECID=7 STAMP=878642635
Deleted 2 objects
RMAN> crosscheck backupset;
using channel ORA_DISK_1
specification does not match any backup in the repository
RMAN> list backupset;
specification does not match any backup in the repository
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 13:33:41 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> list backupset;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> catalog start with '/tmp/2015_05_02/';
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
8 9.00K DISK 00:00:00 02-MAY-15
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 8
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 9
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 10
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/system01.dbf
2 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u01/app/oracle/oradata/ora11g/users01.dbf
5 Full 1092435 30-APR-15 /u01/app/oracle/oradata/ora11g/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
11 9.80M DISK 00:00:00 02-MAY-15
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN> restore database;
Starting restore at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=125 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/example01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
ORA-19504: failed to create file "/u01/app/oracle/oradata/ora11g/example01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/02/2015 13:34:38
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
缺少目录，我们建立目录重试：
[oracle@orcltest orcltest]$ mkdir -p /u01/app/oracle/oradata/ora11g/
RMAN> restore database;
Starting restore at 02-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ora11g/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ora11g/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ora11g/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp tag=TAG20150502T112355
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp tag=TAG20150502T105103
channel ORA_DISK_1: restored backup piece 1
restore not complete
Restore did not complete for some files from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp (piecenum=1, pieces_done=1, done=FALSE, failover=FALSE)
Please check alert log for additional information.
failover to previous backup
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ora11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp tag=TAG20150502T112355
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 02-MAY-15
RMAN> recover database;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
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=15
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp tag=TAG20150502T113152
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8ro53m_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8ro53m_.arc RECID=10 STAMP=878650661
unable to find archived log
archived log thread=1 sequence=16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 13:37:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1145812
RMAN> recover database until sequence 16;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
datafile 5 not processed because file is read-only
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-MAY-15
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 13:40:07 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
SQL>
OK，一切搞定，其他的后续工作别忘记了。
②、 方法二：采用临时库的控制文件，或者备份片段中没有控制文件的备份
如果采用临时库的控制文件来还原数据库的，会出现这样的问题：
RMAN> restore database;
Starting restore at 02-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/02/2015 13:48:39
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN> list backupset;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 9.00K DISK 00:00:00 02-MAY-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 15 1145640 02-MAY-15 1145812 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 235.99M DISK 00:00:00 02-MAY-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.07G DISK 00:00:00 02-MAY-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 02-MAY-15 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 30-APR-15 /u02/app/oracle/oradata/orcltest/example01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 9.80M DISK 00:00:00 02-MAY-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 13 1122206 02-MAY-15 1145003 02-MAY-15
1 14 1145003 02-MAY-15 1145640 02-MAY-15
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 9.36M DISK 00:00:00 02-MAY-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 02-MAY-15
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 02-MAY-15
RMAN>
虽然能用list backup命令看到相关数据文件的元数据信息，但是执行restore database却无法找到相应数据文件的备份，这时可以用到强大的DBMS_BACKUP_RESTORE包执行恢复操作，执行如下PL/SQL命令，注意如果同一个数据文件在多个备份集中，只写一个备份集名称即可：
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'/u02/app/oracle/oradata/orcltest/system01.dbf');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'/u02/app/oracle/oradata/orcltest/sysaux01.dbf');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'/u02/app/oracle/oradata/orcltest/undotbs01.dbf');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'/u02/app/oracle/oradata/orcltest/users01.dbf');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/u02/app/oracle/oradata/orcltest/example01.dbf');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp', params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
PL/SQL procedure successfully completed.
[oracle@orcltest orcltest]$ ll -h
total 1.7G
-rw-r----- 1 oracle oinstall 9.3M May 2 14:00 control01.ctl
-rw-r----- 1 oracle oinstall 314M May 2 13:59 example01.dbf
-rw-r----- 1 oracle oinstall 551M May 2 13:59 sysaux01.dbf
-rw-r----- 1 oracle oinstall 721M May 2 13:59 system01.dbf
-rw-r----- 1 oracle oinstall 96M May 2 13:58 undotbs01.dbf
-rw-r----- 1 oracle oinstall 16M May 2 13:58 users01.dbf
[oracle@orcltest orcltest]$
好，我们看到数据文件已经到位，接下来就是recover 操作了。
RMAN> recover database;
Starting recover at 02-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:01:45
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/u02/app/oracle/oradata/orcltest/system01.dbf'
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 4270446895 PARENT 995548 02-MAY-15
3 3 ORA11G 4270446895 ORPHAN 995548 13-MAR-15
2 2 ORA11G 4270446895 CURRENT 1091863 02-MAY-15
RMAN> reset database to incarnation 3;
database reset to incarnation 3
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORA11G 4270446895 ORPHAN 995548 02-MAY-15
3 3 ORA11G 4270446895 CURRENT 995548 13-MAR-15
2 2 ORA11G 4270446895 ORPHAN 1091863 02-MAY-15
RMAN> recover database;
Starting recover at 02-MAY-15
using channel ORA_DISK_1
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:07:33
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start
ORA-00283: recovery session canceled due to errors
ORA-01206: file is not part of this database - wrong database id
ORA-01110: data file 5: '/u02/app/oracle/oradata/orcltest/example01.dbf'
[oracle@orcltest ~]$ oerr ora 1206
01206, 00000, "file is not part of this database - wrong database id"
// *Cause: The database ID in the file header does not match the database id
// in the control file. The file may be from a different database, or
// it may not be a database file at all. If the database was rebuilt,
// this may be a file from before the rebuild. Note that if you see
// this error when the file is supposed to be plugged in from another
// database via the Transportable Tablespace feature, it means the
// database ID in the file header does not match the one expected.
// *Action: Restore a copy of the correct data file and do recovery as needed.
[oracle@orcltest ~]$
查了下资料发现：
ORA-01110: data file 9: 'H:xxxxxxxxx.dbf'
ORA-01206: file is not part of this database - wrong database id
Datafile 9 was a read-only tablespace, and the source database was actually created with RMAN DUPLICATE. Datafile 9 was read-only during that duplicate operation, and the status hadn’t changed since then. As you can imagine, the read-only datafiles were not changed and their headers still contained the DBID of the database that was the source of the RMAN DUPLICATE. A similar situation could probably happen if tablespaces were imported using transportable tablespaces feature, and left read only.
The fix in this case is to make tablespaces read-write for a moment, and then change back to read-only. The read-write operation will write new datafile headers and, consequently, put there the “right” DBID. IMPORTANT — this has to be done before backup and not after a disaster strikes. This case just emphasizes again the most important rule of any backup/recovery strategy is to do regular test-restores.
If it’s too late and something hit the fan — well, you probably have a chance to offline drop those tablespaces and, hopefully, be able to import them back, if those are transportable tablespaces, and the metadata dump file is still available.
Another idea would be to offline datafiles and then online them after OPEN RESETLOGS. Should someone try that — let us know if it works
知道了，原来example表空间是只读的，这个有点麻烦了，本来没有只读文件的话， 直接recover做不完全恢复就ＯＫ了，这样一来我们就必须重建控制文件了，这里如果没有只读文件的话直接ｒｅｃｏｖｅｒ就可以成功了。
SQL> alter database backup controlfile to trace as '/tmp/aab.txt';
Database altered.
SQL>
拿出/tmp/aab.txt中创建控制文件的部分，直接在ｎｏｍｏｕｎｔ下创建控制文件， 注意这里去掉只读数据文件，关于只读数据文件的处理参考：http://blog.itpub.net/26736162/viewspace-1425283/：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/orcltest/system01.dbf',
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/users01.dbf'
-- '/u02/app/oracle/oradata/orcltest/example01.dbf'
CHARACTER SET ZHS16GBK
;
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 14:52:41 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL>
SQL> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u02/app/oracle/oradata/orcltest/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u02/app/oracle/oradata/orcltest/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u02/app/oracle/oradata/orcltest/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u02/app/oracle/oradata/orcltest/system01.dbf',
'/u02/app/oracle/oradata/orcltest/sysaux01.dbf',
'/u02/app/oracle/oradata/orcltest/undotbs01.dbf',
'/u02/app/oracle/oradata/orcltest/users01.dbf'
CHARACTER SET ZHS16GBK
;
ORACLE instance started.
Total System Global Area 409194496 bytes
Fixed Size 2228864 bytes
Variable Size 272633216 bytes
Database Buffers 125829120 bytes
Redo Buffers 8503296 bytes
SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Control file created.
SQL> SQL>
SQL>
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcltest ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 2 14:53:01 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895, not open)
RMAN> catalog start with '/tmp/2015_05_02/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/2015_05_02/
List of Files Unknown to the Database
=====================================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
File Name: /tmp/2015_05_02/cont.ctl
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
File Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 9.00K DISK 00:00:00 2015-05-02 11:31:52
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T113152
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 15 1145640 2015-05-02 11:23:48 1145812 2015-05-02 11:31:52
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 235.99M DISK 00:00:00 2015-05-02 10:51:03
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T105103
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T105103_bn8gxdob_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1122334 2015-05-02 10:51:03 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 2015-04-30 15:42:04
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 11:23:55
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T112355_bn8jtcsc_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/system01.dbf
2 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/sysaux01.dbf
3 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/undotbs01.dbf
4 Full 1145650 2015-05-02 11:23:55 /u02/app/oracle/oradata/orcltest/users01.dbf
5 Full 1092435 2015-04-30 15:42:04
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4 9.80M DISK 00:00:00 2015-05-02 11:23:53
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112352
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T112352_bn8jt975_.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 13 1122206 2015-05-02 10:50:47 1145003 2015-05-02 11:21:26
1 14 1145003 2015-05-02 11:21:26 1145640 2015-05-02 11:23:48
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
5 Full 9.36M DISK 00:00:00 2015-05-02 11:31:50
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20150502T112355
Piece Name: /tmp/2015_05_02/o1_mf_ncsnf_TAG20150502T112355_bn8k97pm_.bkp
SPFILE Included: Modification time: 2015-05-02 11:21:13
SPFILE db_unique_name: ORA11G
Control File Included: Ckp SCN: 1145807 Ckp time: 2015-05-02 11:31:50
RMAN> recover database;
Starting recover at 2015-05-02 14:58:31
using channel ORA_DISK_1
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=15
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T113152_bn8k98y5_.bkp tag=TAG20150502T113152
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8xdzo2_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u02/app/oracle/flash_recovery_area/ORA11G/archivelog/2015_05_02/o1_mf_1_15_bn8xdzo2_.arc RECID=1 STAMP=878655519
unable to find archived log
archived log thread=1 sequence=16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2015 14:58:46
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 1145812
RMAN> recover database until sequence 16;
Starting recover at 2015-05-02 14:59:16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-05-02 14:59:19
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
ｒｅｃｏｖｅｒ 过程中的告警日志：
Sat May 02 15:00:37 2015
[20103] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:13457914 end:13468244 diff:10330 (103 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Tablespace 'EXAMPLE' #6 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #5 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00005' in the controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Sat May 02 15:00:39 2015
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE <tablespace_name>ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
Sat May 02 15:00:55 2015
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Sat May 02 15:01:31 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_ora_20103.trc (incident=13353):
ORA-25319: Queue table repartitioning aborted
Sat May 02 15:01:31 2015
Checker run found 2 new persistent data failures
Incident details in: /u02/app/oracle/diag/rdbms/ora11g/ORA11G/incident/incdir_13353/ORA11G_ora_20103_i13353.trc
Sat May 02 15:01:48 2015
Dumping diagnostic data in directory=[cdmp_20150502150148], requested by (instance=1, osid=20103), summary=[incident=13353].
Sat May 02 15:01:50 2015
error 25319 happened during Queue table repartitioning
Starting background process QMNC
Sat May 02 15:01:50 2015
QMNC started with pid=26, OS id=20168
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Sat May 02 15:02:03 2015
Sweep [inc][13353]: completed
Sweep [inc2][13353]: completed
Sat May 02 15:02:24 2015
Completed: alter database open resetlogs
Sat May 02 15:02:24 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_mmon_20057.trc (incident=13321):
ORA-25319: Queue table repartitioning aborted
Incident details in: /u02/app/oracle/diag/rdbms/ora11g/ORA11G/incident/incdir_13321/ORA11G_mmon_20057_i13321.trc
error 25319 happened during Queue table repartitioning
Sat May 02 15:02:26 2015
Dumping diagnostic data in directory=[cdmp_20150502150226], requested by (instance=1, osid=20057 (MMON)), summary=[incident=13321].
Sat May 02 15:02:36 2015
db_recovery_file_dest_size of 4122 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat May 02 15:02:39 2015
Starting background process CJQ0
Sat May 02 15:02:39 2015
CJQ0 started with pid=30, OS id=20198
Sat May 02 15:02:44 2015
Errors in file /u02/app/oracle/diag/rdbms/ora11g/ORA11G/trace/ORA11G_m000_20194.trc:
ORA-25153: Temporary Tablespace is Empty
Setting Resource Manager plan SCHEDULER[0x318E]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat May 02 15:02:46 2015
Starting background process VKRM
Sat May 02 15:02:46 2015
VKRM started with pid=20, OS id=20204
Sat May 02 15:02:57 2015
Sweep [inc][13321]: completed
Sweep [inc2][13321]: completed
ＯＫ，基本恢复完成，还剩下只读文件了，我们看看只读文件如何恢复：
[oracle@orcltest ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 2 15:02:33 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSIN OFFLINE
G00005
SQL> alter database rename file '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00005' to '/u02/app/oracle/oradata/orcltest/example01.dbf';
Database altered.
SQL> set line 9999 pagesize 9999
SQL> col FILE_NAME format a50
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/oradata/orcltest/example01.dbf OFFLINE
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u02/app/oracle/oradata/orcltest/users01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/orcltest/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/orcltest/sysaux01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/orcltest/system01.dbf 1 SYSTEM ONLINE 754974720
/u02/app/oracle/oradata/orcltest/example01.dbf 5 EXAMPLE READ ONLY
SQL> alter tablespace EXAMPLE online;
Tablespace altered.
SQL> select file#,name FILE_NAME,status from v$datafile;
FILE# FILE_NAME STATUS
---------- -------------------------------------------------- -------
1 /u02/app/oracle/oradata/orcltest/system01.dbf SYSTEM
2 /u02/app/oracle/oradata/orcltest/sysaux01.dbf ONLINE
3 /u02/app/oracle/oradata/orcltest/undotbs01.dbf ONLINE
4 /u02/app/oracle/oradata/orcltest/users01.dbf ONLINE
5 /u02/app/oracle/oradata/orcltest/example01.dbf ONLINE
SQL> select FILE_NAME,FILE_ID,a.TABLESPACE_NAME, b.status ts_status ,BYTES from dba_data_files a,dba_tablespaces b where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
FILE_NAME FILE_ID TABLESPACE_NAME TS_STATUS BYTES
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/u02/app/oracle/oradata/orcltest/users01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/orcltest/undotbs01.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/orcltest/sysaux01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/orcltest/system01.dbf 1 SYSTEM ONLINE 754974720
/u02/app/oracle/oradata/orcltest/example01.dbf 5 EXAMPLE READ ONLY 328335360
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
临时文件的处理：
SQL>
SQL> select * from v$tempfile;
no rows selected
SQL> create temporary tablespace TEMP01 tempfile '/u02/app/oracle/oradata/orcltest/temp01.dbf' size 100m autoextend on next 10m;
Tablespace created.
SQL> alter database default temporary tablespace temp01;
Database altered.
SQL>
SQL> col name for a100
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------------------------------------------------------------------
1 /u02/app/oracle/oradata/orcltest/temp01.dbf
SQL>
好了，至此我们就恢复了数据库了。
..