ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在所有文件丢失的情况下使用RMAN恢复数据库

在所有文件丢失的情况下使用RMAN恢复数据库

原创 Linux操作系统 作者:xuyongw2000 时间:2012-02-10 22:19:46 0 删除 编辑

1、实验场景

      在数据库的spfile文件、控制文件、所有数据文件、重做日志文件全部丢失的情况下,使用RMAN对数据库进行不完全恢复(由于在线日志也丢失,所以只能进行不完全恢复)

2、备份数据库

     [oracle@test backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 20 21:10:59 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: XU (DBID=3272830258)

RMAN> backup format='/usr/local/oracle/backup/%d_%s.bak'
2> database include current controlfile
3> spfile;

Starting backup at 20-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/usr/local/oracle/oradata/xu/system01.dbf
input datafile fno=00003 name=/usr/local/oracle/oradata/xu/sysaux01.dbf
input datafile fno=00002 name=/usr/local/oracle/oradata/xu/undotbs01.dbf
input datafile fno=00004 name=/usr/local/oracle/oradata/xu/users01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_14.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_15.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-JAN-12
channel ORA_DISK_1: finished piece 1 at 20-JAN-12
piece handle=/usr/local/oracle/backup/XU_16.bak tag=TAG20120120T211123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JAN-12

Starting Control File and SPFILE Autobackup at 20-JAN-12
piece handle=/usr/local/oracle/10g/dbs/c-3272830258-20120120-04 comment=NONE
Finished Control File and SPFILE Autobackup at 20-JAN-12


3、确认控制文件及spfile文件所在的备份集

  RMAN> list backup of spfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    6.80M      DISK        00:00:00     20-JAN-12     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T184420
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-01
  SPFILE Included: Modification time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.80M      DISK        00:00:01     20-JAN-12     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T185350
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-03
  SPFILE Included: Modification time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    80.00K     DISK        00:00:00     20-JAN-12     
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T211123
        Piece Name: /usr/local/oracle/backup/XU_16.bak
  SPFILE Included: Modification time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    6.80M      DISK        00:00:00     20-JAN-12     
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T211141
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-04
  SPFILE Included: Modification time: 20-JAN-12

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    6.80M      DISK        00:00:00     20-JAN-12     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T184420
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-01
  Control File Included: Ckp SCN: 454954       Ckp time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.80M      DISK        00:00:01     20-JAN-12     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T185350
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-03
  Control File Included: Ckp SCN: 455272       Ckp time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    6.77M      DISK        00:00:00     20-JAN-12     
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T211123
        Piece Name: /usr/local/oracle/backup/XU_15.bak
  Control File Included: Ckp SCN: 459426       Ckp time: 20-JAN-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    6.80M      DISK        00:00:00     20-JAN-12     
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20120120T211141
        Piece Name: /usr/local/oracle/10g/dbs/c-3272830258-20120120-04
  Control File Included: Ckp SCN: 459431       Ckp time: 20-JAN-12


4、创建测试表

 [oracle@test backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:15:59 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /usr/local/oracle/arch
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> create table t_xu ( a number);

Table created.

SQL> insert into t_xu values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into t_xu values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into t_xu values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> select * from t_xu;

         A
----------
         1
         2
         3

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /usr/local/oracle/arch
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4
SQL>

 

5、删除spfile文件及数据库文件

[oracle@test backup]$ rm -rf /usr/local/oracle/10g/dbs/spfilexu.ora
[oracle@test backup]$ rm -rf /usr/local/oracle/oradata/xu/*

[oracle@test backup]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:18:19 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit

 

6、恢复spfile文件

      由于spfile文件丢失,所以使用sqlplus无法启动实例,这时需要使用rman来启动实例,或者手工创建一个只包含db_name的init文件

     在正式恢复时,如果使用restore spfile from autobackup命令则需要首先set dbid,否则RMAN无法确定自动备份文件,如果使用restore spfile from ....则不需要set dbid

[oracle@test dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:19:43 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/10g/dbs/initxu.ora'
SQL> exit
Disconnected
[oracle@test dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 20 21:20:48 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/10g/dbs/initxu.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> restore spfile from autobackup;

Starting restore at 20-JAN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/20/2012 21:21:10
RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN> set dbid=3272830258

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 20-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20120120
channel ORA_DISK_1: autobackup found: c-3272830258-20120120-04
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-JAN-12

RMAN> restore spfile from '/usr/local/oracle/backup/XU_16.bak';

Starting restore at 20-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: /usr/local/oracle/backup/XU_16.bak
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 20-JAN-12

RMAN>

恢复完成后可重启实例,使用恢复后的spfile文件

RMAN> shutdown abort

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area    1090519040 bytes

Fixed Size                     1218920 bytes
Variable Size                301991576 bytes
Database Buffers             771751936 bytes
Redo Buffers                  15556608 bytes

 


7、恢复控制文件

   和恢复spfile文件一样,如果从自动备份中恢复则需要先set dbid,否则不需要     
  RMAN> set dbid=3272830258

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 20-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20120120
channel ORA_DISK_1: autobackup found: c-3272830258-20120120-04
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/usr/local/oracle/oradata/xu/control01.ctl
output filename=/usr/local/oracle/oradata/xu/control02.ctl
output filename=/usr/local/oracle/oradata/xu/control03.ctl
Finished restore at 20-JAN-12

 

RMAN>

RMAN> restore controlfile from '/usr/local/oracle/backup/XU_15.bak';

Starting restore at 20-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/usr/local/oracle/oradata/xu/control01.ctl
output filename=/usr/local/oracle/oradata/xu/control02.ctl
output filename=/usr/local/oracle/oradata/xu/control03.ctl
Finished restore at 20-JAN-12

RMAN>


8、mount数据库,并把归档日志信息追加到控制文件

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> catalog start with '/usr/local/oracle/arch';

searching for all files that match the pattern /usr/local/oracle/arch

List of Files Unknown to the Database
=====================================
File Name: /usr/local/oracle/arch/1_2_773088821.dbf
File Name: /usr/local/oracle/arch/1_1_773088821.dbf
File Name: /usr/local/oracle/arch/1_3_773088821.dbf

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /usr/local/oracle/arch/1_2_773088821.dbf
File Name: /usr/local/oracle/arch/1_1_773088821.dbf
File Name: /usr/local/oracle/arch/1_3_773088821.dbf

RMAN>

9、对数据库进行不完全恢复,并以resetlogs方式打开数据库

RMAN> restore database;

Starting restore at 20-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /usr/local/oracle/oradata/xu/system01.dbf
restoring datafile 00002 to /usr/local/oracle/oradata/xu/undotbs01.dbf
restoring datafile 00003 to /usr/local/oracle/oradata/xu/sysaux01.dbf
restoring datafile 00004 to /usr/local/oracle/oradata/xu/users01.dbf
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/backup/XU_14.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/usr/local/oracle/backup/XU_14.bak tag=TAG20120120T211123
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 20-JAN-12


RMAN> recover database until sequence=4;

Starting recover at 20-JAN-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 1 is already on disk as file /usr/local/oracle/arch/1_1_773088821.dbf
archive log thread 1 sequence 2 is already on disk as file /usr/local/oracle/arch/1_2_773088821.dbf
archive log thread 1 sequence 3 is already on disk as file /usr/local/oracle/arch/1_3_773088821.dbf
archive log filename=/usr/local/oracle/arch/1_1_773088821.dbf thread=1 sequence=1
archive log filename=/usr/local/oracle/arch/1_2_773088821.dbf thread=1 sequence=2
archive log filename=/usr/local/oracle/arch/1_3_773088821.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 20-JAN-12

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

RMAN>

10、检查数据恢复情况
   
    在这种情况下,已经归档的数据是可以得到恢复的,对于已经提交的事务,但由于在线日志没有归档,且日志也丢失,所以这部分数据公丢失
   
    [oracle@test dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 20 21:29:00 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
SQL>
SQL> select * from t_xu;

         A
----------
         1
         2
         3

SQL>

11、以上步骤只是一个实验,用以记录此场景的大致恢复步骤,在实际操作中会有所不同,难度也会比这大
   


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/593077/viewspace-716054/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2012-01-09

  • 博文量
    6
  • 访问量
    28747