【RMAN】利用備份片還原資料庫(下)
【RMAN】利用備份片還原資料庫
BLOG文件結構圖
【RMAN】利用備份片還原資料庫(上)： http://blog.itpub.net/26736162/viewspace-1621581/
【RMAN】利用備份片還原資料庫(中)：http://blog.itpub.net/26736162/viewspace-1621661/
在上2篇blog中介紹了備份片中含有控制檔案的備份的情況下，如何從備份集中找回控制檔案的備份並恢復資料庫，本篇blog來介紹下在備份片中沒有控制檔案的備份的情況下如何恢復資料庫。
1.1 備份集中無控制檔案情況下的資料庫恢復
如果採用本文中所描述的3種方式均判斷沒有控制檔案的備份的時候，那麼我們唯一能做的就是重建控制檔案了，而resetlogs方式重建控制檔案之前需要控制檔案指令碼中的所有資料檔案到位才能重建控制檔案。
[root@orcltest 2015_05_02]# ll -h
total 1.2G
-rw-r----- 1 oracle oinstall 30M May 2 15:49 o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
-rw-r----- 1 oracle oinstall 4.0K May 2 15:49 o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
-rw-r----- 1 oracle oinstall 1.1G May 2 15:49 o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
[root@orcltest 2015_05_02]# pwd
/tmp/2015_05_02
[oracle@orcltest dbs]$ cd $ORACLE_HOME/dbs
[oracle@orcltest dbs]$ more inittmp.ora
db_name=tmp
[oracle@orcltest dbs]$ ORACLE_SID=tmp
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 14:45:45 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
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=>'/tmp/2015_05_02/datafile1.dbf');
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp', params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> SQL> ! ls -lh /tmp/2015_05_02/
total 1.9G
-rw-r----- 1 oracle asmadmin 721M May 3 14:46 datafile1.dbf
-rw-r----- 1 oracle oinstall 30M May 2 15:49 o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
-rw-r----- 1 oracle oinstall 4.0K May 2 15:49 o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
-rw-r----- 1 oracle oinstall 1.1G May 2 15:49 o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
SQL> CREATE CONTROLFILE REUSE DATABASE "tmp" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M,
9 GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M
10 DATAFILE
11 '/tmp/2015_05_02/datafile1.dbf'
12 CHARACTER SET ZHS16GBK
13 ;
CREATE CONTROLFILE REUSE DATABASE "tmp" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name ORA11G in file header does not match given name of TMP
ORA-01110: data file 1: '/tmp/2015_05_02/datafile1.dbf'
由此可以看出備份集中的db_name為ORA11G，其實這些都是沒有必要的，哪個dba不曉得資料庫名呢？好吧，我們將pfile檔案中的db_name修改一下，採用RESETLOGS建立控制檔案，注意必須是RESETLOGS，NORESETLOGS需要online log檔案在位，如下：
[oracle@orcltest dbs]$ more inittmp.ora
db_name=ORA11G
[oracle@orcltest dbs]$ echo $ORACLE_SID
tmp
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 14:56:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS DATAFILE '/tmp/2015_05_02/datafile1.dbf';
Control file created.
SQL>
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/cntrltmp.dbf
SQL>
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 /tmp/2015_05_02/datafile1.dbf SYSTEM
SQL>
catalog 所有的backuppiece
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 3 15:40:32 2015
Copyright (c) 1982, 2009, 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_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.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_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
RMAN>
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 3.50K DISK 00:00:00 2015-05-02 15:44:51
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154451
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 17 1166181 2015-05-02 15:44:02 1166209 2015-05-02 15:44:51
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
2 29.82M DISK 00:00:00 2015-05-02 15:44:02
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154402
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 16 1145812 2015-05-02 11:31:52 1166181 2015-05-02 15:44:02
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 15:44:04
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154404
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1166189 2015-05-02 15:44:04 /tmp/2015_05_02/datafile1.dbf
2 Full 1166189 2015-05-02 15:44:04
3 Full 1166189 2015-05-02 15:44:04
4 Full 1166189 2015-05-02 15:44:04
5 Full 1166189 2015-05-02 15:44:04
6 Full 1166189 2015-05-02 15:44:04
RMAN>
RMAN> list backupset of spfile;
specification does not match any backup in the repository
RMAN> list backupset of controlfile;
specification does not match any backup in the repository
RMAN> list backupset of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1 3.50K DISK 00:00:00 2015-05-02 15:44:51
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154451
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 17 1166181 2015-05-02 15:44:02 1166209 2015-05-02 15:44:51
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
2 29.82M DISK 00:00:00 2015-05-02 15:44:02
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154402
Piece Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 16 1145812 2015-05-02 11:31:52 1166181 2015-05-02 15:44:02
RMAN> list backupset of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 1.07G DISK 00:00:00 2015-05-02 15:44:04
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150502T154404
Piece Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1166189 2015-05-02 15:44:04 /tmp/2015_05_02/datafile1.dbf
2 Full 1166189 2015-05-02 15:44:04
3 Full 1166189 2015-05-02 15:44:04
4 Full 1166189 2015-05-02 15:44:04
5 Full 1166189 2015-05-02 15:44:04
6 Full 1166189 2015-05-02 15:44:04
由備份集我們可以看出，有16和17號的歸檔檔案備份，有6個資料檔案備份，這裡我們使用dbms_backup_restore package 來restore datafile。請注意：datafile的名字不重要，只要對於要恢復的datafile，是唯一的名字即可。
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun May 3 15:59:43 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
6 dbms_backup_restore.RestoreSetDatafile;
7 --dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/tmp/2015_05_02/datafile1.dbf');
8 dbms_backup_restore.RestoreDatafileTo(dfnumber => 2,toname => '/tmp/2015_05_02/datafile2.dbf');
9 dbms_backup_restore.RestoreDatafileTo(dfnumber => 3,toname => '/tmp/2015_05_02/datafile3.dbf');
10 dbms_backup_restore.RestoreDatafileTo(dfnumber => 4,toname => '/tmp/2015_05_02/datafile4.dbf');
11 dbms_backup_restore.RestoreDatafileTo(dfnumber => 5,toname => '/tmp/2015_05_02/datafile5.dbf');
12 dbms_backup_restore.RestoreDatafileTo(dfnumber => 6,toname => '/tmp/2015_05_02/datafile6.dbf');
13 dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp', params => null);
14 dbms_backup_restore.DeviceDeallocate;
15 END;
16 /
PL/SQL procedure successfully completed.
SQL>
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 /tmp/2015_05_02/datafile1.dbf SYSTEM
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
告警日誌：
Sun May 03 15:59:44 2015
Full restore complete of datafile 6 to datafile copy /tmp/2015_05_02/datafile6.dbf. Elapsed time: 0:00:00
checkpoint is 1166189
last deallocation scn is 995550
Full restore complete of datafile 4 to datafile copy /tmp/2015_05_02/datafile4.dbf. Elapsed time: 0:00:02
checkpoint is 1166189
last deallocation scn is 3
Full restore complete of datafile 3 to datafile copy /tmp/2015_05_02/datafile3.dbf. Elapsed time: 0:00:02
checkpoint is 1166189
last deallocation scn is 1157819
Undo Optimization current scn is 1157346
Sun May 03 16:00:30 2015
Full restore complete of datafile 5 to datafile copy /tmp/2015_05_02/datafile5.dbf. Elapsed time: 0:00:44
checkpoint is 1166189
last deallocation scn is 1015098
Sun May 03 16:01:03 2015
Full restore complete of datafile 2 to datafile copy /tmp/2015_05_02/datafile2.dbf. Elapsed time: 0:01:18
checkpoint is 1166189
last deallocation scn is 1090388
後設資料顯示只有datafile 1，不奇怪，剛剛建立控制檔案的時候只帶了一個sysem檔案，那麼我們就需要重建控制檔案以便帶上所有需要恢復的datafiles
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/tmp/2015_05_02/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/tmp/2015_05_02/datafile1.dbf',
'/tmp/2015_05_02/datafile2.dbf',
'/tmp/2015_05_02/datafile3.dbf',
'/tmp/2015_05_02/datafile4.dbf',
'/tmp/2015_05_02/datafile5.dbf',
'/tmp/2015_05_02/datafile6.dbf'
CHARACTER SET ZHS16GBK
;
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL>
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/tmp/2015_05_02/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/tmp/2015_05_02/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/tmp/2015_05_02/redo03.log' SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/tmp/2015_05_02/datafile1.dbf',
14 '/tmp/2015_05_02/datafile2.dbf',
15 '/tmp/2015_05_02/datafile3.dbf',
16 '/tmp/2015_05_02/datafile4.dbf',
17 '/tmp/2015_05_02/datafile5.dbf',
18 '/tmp/2015_05_02/datafile6.dbf'
19 CHARACTER SET ZHS16GBK
20 ;
Control file created.
SQL>
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 /tmp/2015_05_02/datafile1.dbf SYSTEM
2 /tmp/2015_05_02/datafile2.dbf RECOVER
3 /tmp/2015_05_02/datafile3.dbf RECOVER
4 /tmp/2015_05_02/datafile4.dbf RECOVER
5 /tmp/2015_05_02/datafile5.dbf RECOVER
6 /tmp/2015_05_02/datafile6.dbf RECOVER
6 rows selected.
SQL>
若是該備份不是冷備份，那麼我們需要recover database，我們需要catalog 包括archivelog的backuppiece，然後restore archivelog，然後再recover
[oracle@orcltest dbs]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sun May 3 16:21:53 2015
Copyright (c) 1982, 2009, 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_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.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_TAG20150502T154451_bn903mln_.bkp
File Name: /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154402_bn9022tb_.bkp
File Name: /tmp/2015_05_02/o1_mf_nnndf_TAG20150502T154404_bn9025w1_.bkp
RMAN> recover database;
Starting recover at 2015-05-03 16:23:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=17
channel ORA_DISK_1: reading from backup piece /tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp
channel ORA_DISK_1: piece handle=/tmp/2015_05_02/o1_mf_annnn_TAG20150502T154451_bn903mln_.bkp tag=TAG20150502T154451
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf thread=1 sequence=17
unable to find archived log
archived log thread=1 sequence=18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/03/2015 16:23:34
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 18 and starting SCN of 1166209
RMAN> recover database until sequence 18;
Starting recover at 2015-05-03 16:23:44
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 2015-05-03 16:23:44
RMAN> alter database open resetlogs;
database opened
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@orcltest dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 3 16:41:58 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> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ORA11G
db_unique_name string ORA11G
global_names boolean FALSE
instance_name string tmp
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string ORA11G
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 250560512 bytes
Fixed Size 2227256 bytes
Variable Size 192938952 bytes
Database Buffers 50331648 bytes
Redo Buffers 5062656 bytes
Database mounted.
Database opened.
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 /tmp/2015_05_02/datafile1.dbf SYSTEM
2 /tmp/2015_05_02/datafile2.dbf ONLINE
3 /tmp/2015_05_02/datafile3.dbf ONLINE
4 /tmp/2015_05_02/datafile4.dbf ONLINE
5 /tmp/2015_05_02/datafile5.dbf ONLINE
6 /tmp/2015_05_02/datafile6.dbf ONLINE
6 rows selected.
SQL>
SQL> col FILE_NAME format a50
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
-------------------------------------------------- ---------- ------------------------------ --------- ----------
/tmp/2015_05_02/datafile6.dbf 6 AA ONLINE 5242880
/tmp/2015_05_02/datafile5.dbf 5 EXAMPLE ONLINE 328335360
/tmp/2015_05_02/datafile4.dbf 4 USERS ONLINE 15728640
/tmp/2015_05_02/datafile3.dbf 3 UNDOTBS1 ONLINE 99614720
/tmp/2015_05_02/datafile2.dbf 2 SYSAUX ONLINE 576716800
/tmp/2015_05_02/datafile1.dbf 1 SYSTEM ONLINE 754974720
6 rows selected.
SQL> select count(1) from lhr.test;
COUNT(1)
----------
75204
recover 過程告警日誌：
Sun May 03 16:39:56 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start until cancel using backup controlfile
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover if needed
start until cancel using backup controlfile
...
alter database recover logfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf'
Media Recovery Log /u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf
ORA-279 signalled during: alter database recover logfile '/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_17_874246769.dbf'...
alter database recover cancel
Media Recovery Canceled
Completed: alter database recover cancel
臨時檔案的處理：
SQL>
SQL> select * from v$tempfile;
no rows selected
SQL> create temporary tablespace TEMP01 tempfile '/tmp/2015_05_02/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 /tmp/2015_05_02/temp01.dbf
SQL>
接下來就是把資料檔案命名格式化，執行如下程式碼：
shutdown immediate;
cp /tmp/2015_05_02/*.dbf /u02/app/oracle/oradata/ORA11G/
cp /tmp/2015_05_02/*.log /u02/app/oracle/oradata/ORA11G/
mv datafile1.dbf AA.dbf
mv datafile5.dbf EXAMPLE01.dbf
mv AA.dbf SYSTEM01.dbf
mv datafile4.dbf USERS01.dbf
mv datafile3.dbf UNDOTBS1.dbf
mv datafile2.dbf SYSAUX01.dbf
mv datafile6.dbf AA.dbf
startup mount
alter database rename file'/tmp/2015_05_02/datafile6.dbf' to '/u02/app/oracle/oradata/ORA11G/AA.dbf';
alter database rename file'/tmp/2015_05_02/datafile5.dbf' to '/u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf';
alter database rename file'/tmp/2015_05_02/datafile4.dbf' to '/u02/app/oracle/oradata/ORA11G/USERS01.dbf';
alter database rename file'/tmp/2015_05_02/datafile3.dbf' to '/u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf';
alter database rename file'/tmp/2015_05_02/datafile2.dbf' to '/u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf';
alter database rename file'/tmp/2015_05_02/datafile1.dbf' to '/u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf';
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/ORA11G/SYSTEM01.dbf SYSTEM
2 /u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf ONLINE
3 /u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf ONLINE
4 /u02/app/oracle/oradata/ORA11G/USERS01.dbf ONLINE
5 /u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf ONLINE
6 /u02/app/oracle/oradata/ORA11G/AA.dbf ONLINE
6 rows selected.
SQL> alter database open;
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/ORA11G/SYSTEM01.dbf SYSTEM
2 /u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf ONLINE
3 /u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf ONLINE
4 /u02/app/oracle/oradata/ORA11G/USERS01.dbf ONLINE
5 /u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf ONLINE
6 /u02/app/oracle/oradata/ORA11G/AA.dbf ONLINE
6 rows selected.
SQL> col FILE_NAME format a50
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/ORA11G/AA.dbf 6 AA ONLINE 5242880
/u02/app/oracle/oradata/ORA11G/EXAMPLE01.dbf 5 EXAMPLE ONLINE 328335360
/u02/app/oracle/oradata/ORA11G/USERS01.dbf 4 USERS ONLINE 15728640
/u02/app/oracle/oradata/ORA11G/UNDOTBS1.dbf 3 UNDOTBS1 ONLINE 99614720
/u02/app/oracle/oradata/ORA11G/SYSAUX01.dbf 2 SYSAUX ONLINE 576716800
/u02/app/oracle/oradata/ORA11G/SYSTEM01.dbf 1 SYSTEM ONLINE 754974720
6 rows selected.
SQL>
好了，至此，整個資料庫恢復完成，至於修改INSTANCE_NAME，pfile檔案，密碼檔案、tnsnames檔案，這些都是基本功了，這裡就不贅述了。
1.2 總結
1. 只有備份片段的情況下，我們可以嘗試將備份片註冊到其它資料庫這樣來獲取dbname和dbid
2. 控制檔案備份不存在的情況下，我們可以利用dbms_backup_restore先把1號檔案恢復，然後重建控制檔案後再註冊備份集來獲取其它資料檔案。
3. 可以使用包dbms_backup_restore 在nomount狀態下來嘗試獲取控制檔案的備份
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1621672/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 註明：ITPUB的文章標題
<版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984448/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【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資料庫
- 【RAC】rac環境下的資料庫備份與還原資料庫
- OS和資料庫版本不同對RMAN備份還原的影響資料庫
- 使用RMAN備份資料庫資料庫
- mysql資料庫-備份與還原實操MySql資料庫
- java mysql 資料庫備份和還原操作JavaMySql資料庫
- Oracle資料庫備份還原詳解XKUSOracle資料庫
- exp/imp備份與還原oracle資料庫Oracle資料庫
- 利用RMAN建立備用資料庫資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- RMAN資料庫還原測試資料庫
- Mysql資料備份和還原MySql
- oracle資料還原與備份Oracle
- 資料庫的備份和還原不成功資料庫
- Centos-Mysql複製備份還原資料庫CentOSMySql資料庫
- 啟明星資料庫批量備份與還原工具資料庫
- 使用mysqldump進行mysql資料庫備份還原MySql資料庫
- Aix下使用rman備份Oracle RAC資料庫(轉)AIOracle資料庫
- 只存在RMAN備份片的資料庫恢復過程資料庫
- 【RMAN】使用增量備份更新資料庫備份映象資料庫
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 利用T-SQL語句,實現資料庫的備份與還原的功能SQL資料庫