备份恢复实验之十九
当前控制文件损坏_备份时下线user表空间完全恢复_用控制文件脚本_不需备份(看到脚本就看到了希望)之前实验十五做的是备份时是ONLINE的,现在做一个备份时是下线的,细心的人就会发现,下线与只读实际上恢复的方法是一模一样的。
SQL> alter userhr default tablespace users;
User altered.
SQL> conn hr/hr
Connected.
SQL> createtable test(a varchar2(10));
Table created.
SQL> insertinto test values('liujie');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
liujie
SQL> conn / assysdba
Connected.
SQL> altertablespace users offline;
Tablespacealtered.
SQL> alterdatabase backup controlfile to trace;
Database altered.
[oracle@ocpdbudump]$ cp orcl_ora_19848.trc /home/oracle/c1.sql
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@ocpdborcl]$ ls
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
redo01.log redo03.log system01.dbf undotbs01.dbf
(编辑控制文件我就不写了,前面写得很清楚了)
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 130024080 bytes
DatabaseBuffers 318767104 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
SQL>@/home/oracle/c1.sql
Control filecreated.
SQL> recoverdatabase;
Media recoverycomplete.
SQL> alterdatabase open;
Database altered.
(敲到这里总是让人心情愉快啊,直接OPEN!)
SQL> select *from hr.test;
select * fromhr.test
*
ERROR at line 1:
ORA-00376: file 4cannot be read at this time
ORA-01111: namefor data file 4 is unknown - rename to correct file
ORA-01110: datafile 4:
'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/MISSING00004'
SQL> alterdatabase rename file'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/MISSING00004' to'/home/oracle/app/oracle/oradata/orcl/users01.dbf';
Database altered.
SQL> altertablespace users online;
Tablespacealtered.
SQL> select *from hr.test;
A
----------
liujie
备份恢复实验之二十
当前控制文件损坏_备份时只读user表空间不完全恢复_用控制文件二进制备份_数据不丢_不需备份(备份时只读)跟实验十六区别开来
SQL> conn / assysdba
Connected.
SQL> alter userhr default tablespace users;
User altered.
SQL> conn hr/hr
Connected.
SQL> createtable test(a varchar2(10));
Table created.
SQL> insertinto test values('liujie');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
liujie
SQL> conn / assysdba
Connected.
SQL> altertablespace users read only;
Tablespacealtered.
RMAN> backupcurrent controlfile;
Starting backup at18-JAN-11
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=143 devtype=DISK
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
including currentcontrol file in backupset
channelORA_DISK_1: starting piece 1 at 18-JAN-11
channelORA_DISK_1: finished piece 1 at 18-JAN-11
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T154345_6mbk5lg7_.bkptag=TAG20110118T154345 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at18-JAN-11
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@ocpdborcl]$ ls
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
redo01.log redo03.log system01.dbf undotbs01.dbf
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total System GlobalArea 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 134218384 bytes
DatabaseBuffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
RMAN> restorecontrolfile from'/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T154345_6mbk5lg7_.bkp';
Starting restoreat 18-JAN-11
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=155 devtype=DISK
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:04
outputfilename=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output filename=/home/oracle/app/oracle/oradata/orcl/control02.ctl
outputfilename=/home/oracle/app/oracle/oradata/orcl/control03.ctl
Finished restoreat 18-JAN-11
RMAN> alterdatabase mount;
database mounted
released channel:ORA_DISK_1
RMAN> recoverdatabase;
Starting recoverat 18-JAN-11
Starting implicitcrosscheck backup at 18-JAN-11
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=155 devtype=DISK
Finished implicitcrosscheck backup at 18-JAN-11
Starting implicitcrosscheck copy at 18-JAN-11
using channelORA_DISK_1
Finished implicitcrosscheck copy at 18-JAN-11
searching for allfiles in the recovery area
catalogingfiles...
cataloging done
List of CatalogedFiles
=======================
File Name:/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T154345_6mbk5lg7_.bkp
usingchannel ORA_DISK_1
datafile4 not processed because file is read-only
starting mediarecovery
archive log thread1 sequence 2 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log
archive logfilename=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=2
media recoverycomplete, elapsed time: 00:00:05
Finished recoverat 18-JAN-11
RMAN> alterdatabase open;
RMAN-00571:===========================================================
RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571:===========================================================
RMAN-03002:failure of alter db command at 01/18/2011 15:50:32
ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open
RMAN> alterdatabase open resetlogs;
database opened
SQL> select *from hr.test;
A
----------
liujie
SQL> selectstatus from dba_tablespaces where tablespace_name='USERS';
STATUS
------------------
READ ONLY
要不要把它READ WRITE,没关系,需要你就READWRITE,不需要那就让它READ ONLY。
最关键的是,数据没丢,这是最重要的。
RMAN> listincarnation of database;
List of DatabaseIncarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- --------------- ---------------- --- ---------- ----------
1 1 ORCL 1268541905 PARENT 1 30-JUN-05
2 2 ORCL 1268541905 PARENT 446075 18-JAN-11
3 3 ORCL 1268541905 CURRENT 479501 18-JAN-11
(很正常嘛,你RESETLOGS后,INCARNATION自然多了一个)
备份恢复实验之二十一
当前控制文件损坏_备份时只读user表空间完全恢复_用控制文件脚本_不需备份(为了跟实验十七区别,我这里做一个在只读前先备份控制文件脚本)
SQL> conn hr/hr
Connected.
SQL> createtable test(a varchar2(10));
Table created.
SQL> insertinto test values('liujie');
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
liujie
SQL> conn / assysdba
Connected.
SQL> alterdatabase backup controlfile to trace;
Database altered.
SQL> altertablespace users read only;
Tablespace altered.
[oracle@ocpdb udump]$cp orcl_ora_24351.trc /home/oracle/c1.sql
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ ls
example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@ocpdborcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 130024080 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
[oracle@ocpdb ~]$vi c1.sql
CREATE CONTROLFILEREUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1'/home/oracle/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2'/home/oracle/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/home/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/app/oracle/oradata/orcl/system01.dbf',
'/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',
'/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',
'/home/oracle/app/oracle/oradata/orcl/users01.dbf',
'/home/oracle/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SETZHS16GBK
;
在READ ONLY之前备份控制文件二进制脚本的话,它会认为你这个表空间是需要恢复的,会帮你写进去。
SQL>@/home/oracle/c1.sql
Control filecreated.
SQL> recoverdatabase;
Media recoverycomplete.
SQL> alterdatabase open;
Database altered.
SQL> select *from hr.test;
A
----------
liujie (数据还在)
SQL> selectstatus from dba_tablespaces where tablespace_name='USERS';
STATUS
---------
READ ONLY
备份恢复实验之二十二
当前控制文件和日志文件全部损坏_不完全恢复_用控制文件二进制备份
(控制文件和日志文件全部损坏,那么肯定是不完全恢复,数据肯定会丢,因为日志,最关键的日志损坏了!这里的日志指的是在线日志,不是归档日志。日志坏了那肯定数据要有备份了。)
RMAN> backupdatabase;
Starting backup at20-JAN-11
using targetdatabase control file instead of recovery catalog
RMAN> backupcurrent controlfile;
Starting backup at20-JAN-11
using channelORA_DISK_1
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
including currentcontrol file in backupset
channelORA_DISK_1: starting piece 1 at 20-JAN-11
channel ORA_DISK_1:finished piece 1 at 20-JAN-11
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncnnf_TAG20110120T142253_6mho5xh2_.bkptag=TAG20110120T142253 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at20-JAN-11
SQL> conn hr/hr
Connected.
SQL> createtable test(a number);
Table created.
SQL> insertinto test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn / assysdba
Connected.
SQL> altersystem switch logfile;
System altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
-------------------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE#FIRST_TIME
-------------------------
1 1 5 52428800 1 YES INACTIVE
532375 20-JAN-11
2 1 6 52428800 1 YES INACTIVE
557724 20-JAN-11
3 1 7 52428800 1 NO CURRENT
558214 20-JAN-11
值1在SEQUENCE 6上那么我现在把值2放在SEQUENCE 7上,待会我们删除所有在线日志,看看值2是否还会在。当前的日志删除,之前的在线日志已经归档了,删不删其实也无所谓了,这里我就全删了,明白?
SQL> connhr/hr
Connected.
SQL> insertinto test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
1
2
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ rm -rf r*
[oracle@ocpdborcl]$ ls
example01.dbf system01.dbf undotbs01.dbf
sysaux01.dbf temp01.dbf users01.dbf
[oracle@ocpdborcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
SQL> conn / assysdba
Connected.
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 142606992 bytes
DatabaseBuffers 306184192 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
[oracle@ocpdb ~]$rman target /
Recovery Manager:Release 10.2.0.1.0 - Production on Thu Jan 20 14:55:27 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database: orcl (not mounted)
RMAN> restorecontrolfile from'/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncnnf_TAG20110120T142253_6mho5xh2_.bkp';
RMAN> alterdatabase mount;
database mounted
released channel:ORA_DISK_1
RMAN> restoredatabase;
SQL> recoverdatabase until cancel using backup controlfile;(在SQLPLUS里面敲)
ORA-00279: change558025 generated at 01/20/2011 14:20:04 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_6
_%u_.arc
ORA-00280: change558025 for thread 1 is in sequence #6
Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
(敲回车,应用)
ORA-00279: change558214 generated at 01/20/2011 14:25:54 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_7
_%u_.arc
ORA-00280: change558214 for thread 1 is in sequence #7
ORA-00278: log file
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_
6_6mhocld1_.arc'no longer needed for this recovery
Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannotopen archived log
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_
7_%u_.arc'
ORA-27037: unableto obtain file status
Linux Error: 2: Nosuch file or directory
Additionalinformation: 3
ORA-27091: unableto queue I/O
ORA-27072: FileI/O error
Linux Error: 22:Invalid argument
Additionalinformation: 4
Additionalinformation: 1
ORA-27047: unableto read the header block of file
Linux Error: 22:Invalid argument
Additionalinformation: 1
ORA-27047: unableto read the header block of file
Linux Error: 22:Invalid argument
Additionalinformation: 1
(敲下去报错,只能CANCEL掉了,之前被删的日志)
SQL>recover database until cancel using backup controlfile;
ORA-00279:change 558214 generated at 01/20/2011 14:25:54 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_7
_%u_.arc
ORA-00280:change 558214 for thread 1 is in sequence #7
Specifylog: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Mediarecovery cancelled.
SQL> alterdatabase open;
alter databaseopen
*
ERROR at line 1:
ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open
SQL> alterdatabase open resetlogs;
Database altered.
SQL> select *from hr.test;
A
----------
1
(有了,只有1,没有2)
RMAN> listincarnation of database;
List of DatabaseIncarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- --------------- ---------------- --- ---------- ----------
1 1 ORCL 1268548172 PARENT 1 30-JUN-05
2 2 ORCL 1268548172 PARENT 446075 18-JAN-11
3 3 ORCL 1268548172 CURRENT 558215 20-JAN-11
备份恢复实验之二十三
当前控制文件和日志文件全部损坏_不完全恢复_用控制文件脚本(跟上面唯一的差别就是这里是控制文件脚本,用脚本也是不完全恢复?是啊,因为你日志损坏了啊)--日志文件丢了,就必须把旧的数据文件拷回来了!
RMAN> backupdatabase;
Starting backup at20-JAN-11
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=148 devtype=DISK
channelORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1:specifying datafile(s) in backupset
input datafilefno=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafilefno=00003 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafilefno=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafilefno=00002 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafilefno=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
channelORA_DISK_1: starting piece 1 at 20-JAN-11
channelORA_DISK_1: finished piece 1 at 20-JAN-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T152428_6mhrsfbo_.bkptag=TAG20110120T152428 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:55
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
including currentcontrol file in backupset
including currentSPFILE in backupset
channelORA_DISK_1: starting piece 1 at 20-JAN-11
channel ORA_DISK_1:finished piece 1 at 20-JAN-11
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncsnf_TAG20110120T152428_6mhrv5yv_.bkptag=TAG20110120T152428 comment=NONE
channelORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at20-JAN-11 SQL> conn / as sysdba
Connected.
SQL> alterdatabase backup controlfile to trace;
Database altered.
[oracle@ocpdbudump]$ cp orcl_ora_7458.trc /home/oracle/c2.sql
SQL> conn hr/hr
Connected.
SQL> create tabletest(a number);
Table created.
SQL> insertinto test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
1
SQL> conn /assysdba
Connected.
SQL> altersystem switch logfile;
System altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
-------------------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE#FIRST_TIME
-------------------------
1 1 2 52428800 1 YES ACTIVE
472434 18-JAN-11
2 1 3 52428800 1 NO CURRENT
479631 20-JAN-11
3 1 1 52428800 1 YES INACTIVE
446075 18-JAN-11
SQL> altersystem checkpoint;
System altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
-------------------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE#FIRST_TIME
-------------------------
1 1 2 52428800 1 YES INACTIVE
472434 18-JAN-11
2 1 3 52428800 1 NO CURRENT
479631 20-JAN-11
3 1 1 52428800 1 YES INACTIVE
446075 18-JAN-11
(发检查点,是不想把问题搞得太复杂,我们不讨论ACTIVE的情况,以后再讲,你可以自己实验一下。)
接下来,再插一个值2,插在SEQUENCE3上。
SQL> conn hr/hr
Connected.
SQL> insertinto test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
1
2
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ rm -rf r*
[oracle@ocpdborcl]$ pwd
/home/oracle/app/oracle/oradata/orcl
[oracle@ocpdborcl]$ ls
example01.dbf system01.dbf undotbs01.dbf
sysaux01.dbf temp01.dbf users01.dbf
SQL> conn /assysdba
Connected.
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 138412688 bytes
DatabaseBuffers 310378496 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
[oracle@ocpdb ~]$vi c2.sql
CREATE CONTROLFILEREUSE DATABASE "ORCL"RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1'/home/oracle/app/oracle/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2'/home/oracle/app/oracle/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/home/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/app/oracle/oradata/orcl/system01.dbf',
'/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',
'/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',
'/home/oracle/app/oracle/oradata/orcl/users01.dbf',
'/home/oracle/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SETZHS16GBK
;
(注意这里,是选SET #2,RESETLOGS,这就是最直接的区别!)
SQL>@/home/oracle/c2.sql
Control filecreated.
[oracle@ocpdbbackup]$ rman target /
Recovery Manager:Release 10.2.0.1.0 - Production on Thu Jan 20 15:43:45 2011
Copyright (c)1982, 2005, Oracle. All rights reserved.
connected totarget database: ORCL (DBID=1268548172, not open)
RMAN> listbackup;
using targetdatabase control file instead of recovery catalog
(这里识别不了任何的备份,因为它是控制文件的脚本,它是一个新人)
所以说,我们要把这些备份集注册进RMAN中。
RMAN> catalogbackuppiece '/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T152428_6mhrsfbo_.bkp';
catalogedbackuppiece
backup piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T152428_6mhrsfbo_.bkprecid=1 stamp=740936928
RMAN> restoredatabase;
Starting restoreat 20-JAN-11
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=155 devtype=DISK
channelORA_DISK_1: starting datafile backupset restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
restoring datafile00002 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile00003 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
restoring datafile00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channelORA_DISK_1: reading from backup piece/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T161206_6mhvlqjk_.bkp
channelORA_DISK_1: restored backup piece 1
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T161206_6mhvlqjk_.bkptag=TAG20110120T161206
channelORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restoreat 20-JAN-11
SQL> recoverdatabase until cancel using backup controlfile;
ORA-00279: change478790 generated at 01/20/2011 16:12:06 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_2
_%u_.arc
ORA-00280: change478790 for thread 1 is in sequence #2
Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change478873 generated at 01/20/2011 16:16:28 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_3
_%u_.arc
ORA-00280: change478873 for thread 1 is in sequence #3
ORA-00278: logfile
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_
2_6mhvtwfo_.arc'no longer needed for this recovery
ORA-27091: unableto queue I/O
ORA-27072: FileI/O error
Linux Error: 22:Invalid argument
Additionalinformation: 4
Additionalinformation: 1
ORA-27047: unableto read the header block of file
Linux Error: 22:Invalid argument
Additionalinformation: 1
ORA-27047: unableto read the header block of file
Linux Error: 22:Invalid argument
Additionalinformation: 1
Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannotopen archived log
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_
3_%u_.arc'
ORA-27037: unableto obtain file status
Linux Error: 2: Nosuch file or directory
Additionalinformation: 3
SQL> recover databaseuntil cancel using backup controlfile;
ORA-00279: change478873 generated at 01/20/2011 16:16:28 needed for thread 1
ORA-00289:suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_20/o1_mf_1_3
_%u_.arc
ORA-00280: change 478873for thread 1 is in sequence #3
Specify log:{<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recoverycancelled.
SQL> alterdatabase open;
alter databaseopen
*
ERROR at line 1:
ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open
SQL> alterdatabase open resetlogs;
Database altered.
SQL> select *from hr.test;
A
----------
1
(有了,也是1有,2没有,很正常,所以为什么要把日志归档就是这个原因,日志太重要了!)
备份恢复实验之二十四
当前控制文件和日志文件全部损坏_备份时下线user表空间不完全恢复_用控制文件二进制备份(比之前的实验复杂在于这里日志文件也损坏了,备份控制文件时USERS表空间已经下线了。)
RMAN> backupdatabase;
Starting backup at20-JAN-11
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=142 devtype=DISK
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
input datafilefno=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafilefno=00003 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafilefno=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
input datafilefno=00002 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafilefno=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
channelORA_DISK_1: starting piece 1 at 20-JAN-11
channelORA_DISK_1: finished piece 1 at 20-JAN-11
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T163931_6mhx63oj_.bkptag=TAG20110120T163931 comment=NONE
SQL> alter userhr default tablespace users;
User altered.
SQL> conn hr/hr
Connected.
SQL> createtable test(a number);
Table created.
SQL> insertinto test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
1
SQL> conn / assysdba
Connected.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
-------------------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE#FIRST_TIME
-------------------------
1 1 2 52428800 1 NO CURRENT
472620 20-JAN-11
2 1 0 52428800 1 YES UNUSED
0
3 1 1 52428800 1 YES INACTIVE
446075 20-JAN-11
SQL> altersystem switch logfile;
System altered.
SQL> altersystem checkpoint;
System altered.
SQL> select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
-------------------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE#FIRST_TIME
-------------------------
1 1 2 52428800 1 YES INACTIVE
472620 20-JAN-11
2 1 3 52428800 1 NO CURRENT
479030 20-JAN-11
3 1 1 52428800 1 YES INACTIVE
446075 20-JAN-11
SQL> conn hr/hr
Connected.
SQL> insertinto test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select *from test;
A
----------
1
2
SQL> conn /assysdba
Connected.
SQL> altertablespace users offline;
Tablespacealtered.
RMAN> backupcurrent controlfile;
Starting backup at20-JAN-11
using channelORA_DISK_1
channelORA_DISK_1: starting full datafile backupset
channelORA_DISK_1: specifying datafile(s) in backupset
including currentcontrol file in backupset
channelORA_DISK_1: starting piece 1 at 20-JAN-11
channelORA_DISK_1: finished piece 1 at 20-JAN-11
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncnnf_TAG20110120T165638_6mhy6728_.bkptag=TAG20110120T165638 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:00:02
Finished backup at20-JAN-11
[oracle@ocpdborcl]$ rm -rf c*
[oracle@ocpdborcl]$ rm -rf r*
[oracle@ocpdborcl]$ ls
example01.dbf system01.dbf undotbs01.dbf
sysaux01.dbf temp01.dbf users01.dbf
SQL> conn /assysdba
Connected.
SQL> altertablespace users offline;
Tablespacealtered.
SQL> shutdownabort
ORACLE instanceshut down.
SQL> startup
ORACLE instancestarted.
Total SystemGlobal Area 452984832 bytes
Fixed Size 1219952 bytes
Variable Size 134218384 bytes
DatabaseBuffers 314572800 bytes
Redo Buffers 2973696 bytes
ORA-00205: errorin identifying control file, check alert log for more info
RMAN> restorecontrolfile from '/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncnnf_TAG20110120T165638_6mhy6728_.bkp';
Starting restoreat 20-JAN-11
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=155 devtype=DISK
channelORA_DISK_1: restoring control file
channelORA_DISK_1: restore complete, elapsed time: 00:00:04
outputfilename=/home/oracle/app/oracle/oradata/orcl/control01.ctl
outputfilename=/home/oracle/app/oracle/oradata/orcl/control02.ctl
outputfilename=/home/oracle/app/oracle/oradata/orcl/control03.ctl
Finished restoreat 20-JAN-11
RMAN> alterdatabase mount;
database mounted
released channel:ORA_DISK_1
RMAN> restoredatabase;
Starting restoreat 20-JAN-11
Starting implicitcrosscheck backup at 20-JAN-11
allocated channel:ORA_DISK_1
channelORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2objects
Finished implicitcrosscheck backup at 20-JAN-11
Starting implicitcrosscheck copy at 20-JAN-11
using channelORA_DISK_1
Finished implicitcrosscheck copy at 20-JAN-11
searching for allfiles in the recovery area
catalogingfiles...
cataloging done
List of CatalogedFiles
=======================
File Name:/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_ncnnf_TAG20110120T165638_6mhy6728_.bkp
using channelORA_DISK_1
datafile 4 notprocessed because file is offline
channelORA_DISK_1: starting datafile backupset restore
channelORA_DISK_1: specifying datafile(s) to restore from backup set
restoringdatafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
restoringdatafile 00002 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
restoringdatafile 00003 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
restoringdatafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channelORA_DISK_1: reading from backup piece/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T163931_6mhx63oj_.bkp
channelORA_DISK_1: restored backup piece 1
piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_20/o1_mf_nnndf_TAG20110120T163931_6mhx63oj_.bkptag=TAG20110120T163931
channelORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restoreat 20-JAN-11
(红色的字体中没有USERS表空间,看到了吧,因为他/她认为你OFFLINE,它认为你不需要恢复)
SQL> alter databasedatafile 4 online;
Database altered.(如果不ONLINE一下,它会认为你不需要恢复,你可以不ONLINE,看看恢复的时候会发生什么)这里你最好试一下不ONLINE是什么情况,我写的不一定对,而且貌似之前要把USERS拷回来,你可以测试一下。
SQL> recover database until cancel usingbackup controlfile;
ORA-00279: change 478678 generated at01/21/2011 20:49:34 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_21/o1_mf_1_2
_%u_.arc
ORA-00280: change 478678 for thread 1 is insequence #2
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
ORA-00279: change 479160 generated at01/21/2011 21:02:42 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_21/o1_mf_1_3
_%u_.arc
ORA-00280: change 479160 for thread 1 is insequence #3
ORA-00278: log file
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_21/o1_mf_1_
2_6mm0zlo2_.arc' no longer needed for thisrecovery
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_21/o1_mf_1_
3_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux Error: 22: Invalid argument
Additional information: 4
Additional information: 1
ORA-27047: unable to read the header blockof file
Linux Error: 22: Invalid argument
Additional information: 1
ORA-27047: unable to read the header blockof file
Linux Error: 22: Invalid argument
Additional information: 1
SQL> recover database until cancel usingbackup controlfile;
ORA-00279: change 479160 generated at01/21/2011 21:02:42 needed for thread 1
ORA-00289: suggestion :
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2011_01_21/o1_mf_1_3
_%u_.arc
ORA-00280: change 479160 for thread 1 is insequence #3
Specify log: {<RET>=suggested |filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select * from hr.test;
A
----------
1
SQL>
(值2在在线日志上,随着在线日志的丢失,值也跟着丢失了。)