【RMAN】利用備份片還原資料庫(中)
【RMAN】利用備份片還原資料庫
blog文件結構圖：
【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>
好了，至此我們就恢復了資料庫了。
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1621661/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 註明：ITPUB的文章標題
<版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984452/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【RMAN】利用備份片還原資料庫(中)-附加資料庫
- 【RMAN】利用備份片還原資料庫資料庫
- 【RMAN】利用備份片還原資料庫(上)資料庫
- 【RMAN】利用備份片還原資料庫(下)資料庫
- 利用RMAN備份重建資料庫資料庫
- MSSQL 備份資料庫還原SQL資料庫
- 【原】Oracle學習系列—資料庫備份—RMAN備份Oracle資料庫
- 【Mongodb】資料庫備份與還原MongoDB資料庫
- sqlserver資料庫的備份還原SQLServer資料庫
- 「MySQL」資料庫備份和還原MySql資料庫
- 「Oracle」Oracle 資料庫備份還原Oracle資料庫
- 批量備份和還原資料庫資料庫
- MySQL資料庫備份與還原MySql資料庫
- ORACLE RMAN備份及還原Oracle
- Mysql備份和還原資料庫-mysqldumpMySql資料庫
- 資料庫單表備份還原shell資料庫
- 如何利用Rman對Oracle資料庫進行備份Oracle資料庫
- OS和資料庫版本不同對RMAN備份還原的影響資料庫
- 使用RMAN備份資料庫資料庫
- mysql資料庫-備份與還原實操MySql資料庫
- java mysql 資料庫備份和還原操作JavaMySql資料庫
- Oracle資料庫備份還原詳解XKUSOracle資料庫
- exp/imp備份與還原oracle資料庫Oracle資料庫
- WPF C#開發中利用SQLDMO進行資料庫備份還原操作中的注意點C#SQL資料庫
- 利用RMAN建立備用資料庫資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- RMAN資料庫還原測試資料庫
- Mysql資料備份和還原MySql
- oracle資料還原與備份Oracle
- 資料庫的備份和還原不成功資料庫
- Centos-Mysql複製備份還原資料庫CentOSMySql資料庫
- 啟明星資料庫批量備份與還原工具資料庫
- 使用mysqldump進行mysql資料庫備份還原MySql資料庫
- 只存在RMAN備份片的資料庫恢復過程資料庫
- 【RMAN】使用增量備份更新資料庫備份映象資料庫
- 利用T-SQL語句,實現資料庫的備份與還原的功能SQL資料庫
- 華納雲 sqlserver資料庫備份及還原的方法SQLServer資料庫
- SQL Server 2008還原資料庫備份SQLServer資料庫