【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

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

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

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

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

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

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版：提取碼：af2d

QQ：642808185 註明：ITPUB的文章標題

...........................................................................................................................................................................................

【RMAN】利用備份片還原資料庫(下)

1.1 備份集中無控制檔案情況下的資料庫恢復

1.2 總結

相關文章