ITPub博客

首页 > 数据库 > Oracle > 【RMAN】利用备份片还原数据库(下)

【RMAN】利用备份片还原数据库(下)

Oracle 作者:Appleses 时间:2016-01-30 16:46:11 0 删除 编辑

【RMAN】利用备份片还原数据库

 

BLOG文档结构图

wpsC540.tmp

 

【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_nameORA11G,其实这些都是没有必要的,哪个dba不晓得数据库名呢?好吧,我们将pfile文件中的db_name修改一下,采用RESETLOGS创建控制文件,注意必须是RESETLOGSNORESETLOGS需要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

 

 

由备份集我们可以看出,有1617号的归档文件备份,有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 包括archivelogbackuppiece,然后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_NAMEpfile文件,密码文件、tnsnames文件,这些都是基本功了,这里就不赘述了。

1.2  总结

1. 只有备份片段的情况下,我们可以尝试将备份片注册到其它数据库这样来获取dbnamedbid

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-01-30

  • 博文量
    120
  • 访问量
    27016