RMAN恢复所有文件丢失的实验
如果datafile,controlfile,spfile,online redo logs,system file and undo file都丢失了,即数据相关的数据文件都丢失了,可以通过下面的操作恢复出来:
具体步骤如下:
RMAN> startup nomount;
RMAN>restore spfile from '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp';
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN>restore controlfile from '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp';
RMAN> sql 'alter database mount';
RMAN> restore database;
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
下面是具体的实验:
RMAN> startup nomount;
connected to target database (not started)
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/inittest.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 '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp';
Starting restore at 27-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 27-DEC-12
RMAN> shutdown abort;
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 109053520 bytes
Database Buffers 171966464 bytes
Redo Buffers 2973696 bytes
RMAN> restore controlfile from '/u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp';
Starting restore at 27-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 27-DEC-12
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 27-DEC-12
Starting implicit crosscheck backup at 27-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=154 devtype=DISK
Crosschecked 49 objects
Finished implicit crosscheck backup at 27-DEC-12
Starting implicit crosscheck copy at 27-DEC-12
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
Finished implicit crosscheck copy at 27-DEC-12
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2012_12_27/o1_mf_s_803148722_8fr31mb0_.bkp
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
creating datafile fno=10 name=/u01/app/oracle/oradata/test/MYTBS01.dbf
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/test/example02.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03tjd_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/smalltbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/test/cf_bck_test01.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03p1f_.bkp
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/smalltbs02.dbf
channel ORA_DISK_3: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr060wm_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03tjd_.bkp tag=TAG20121227T160155
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03mk9_.bkp
channel ORA_DISK_2: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03p1f_.bkp tag=TAG20121227T160155
channel ORA_DISK_2: restore complete, elapsed time: 00:00:37
channel ORA_DISK_3: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr060wm_.bkp tag=TAG20121227T160155
channel ORA_DISK_3: restore complete, elapsed time: 00:00:37
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_12_27/o1_mf_nnndf_TAG20121227T160155_8fr03mk9_.bkp tag=TAG20121227T160155
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 27-DEC-12
[oracle@even oradata]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Dec 27 17:23:17 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, Oracle Label Security, OLAP and Data Mining options
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 774674 generated at 12/27/2012 16:01:55 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_24_%u_.ar
c
ORA-00280: change 774674 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 774717 generated at 12/27/2012 16:03:28 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_25_%u_.ar
c
ORA-00280: change 774717 for thread 1 is in sequence #25
ORA-00278: log file
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_24_8fr1z
br3_.arc' no longer needed for this recovery
ORA-00279: change 775473 generated at 12/27/2012 16:33:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_1_%u_.arc
ORA-00280: change 775473 for thread 1 is in sequence #1
ORA-00279: change 776879 generated at 12/27/2012 16:51:59 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_1_%u_.arc
ORA-00280: change 776879 for thread 1 is in sequence #1
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/TEST/archivelog/2012_12_27/o1_mf_1_1_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> alter database open resetlogs;
Database altered.
SQL> conn hr/hr
Connected.
查看是否能够查询数据:
SQL> select COUNT(*) from T2;
COUNT(*)
----------
805728