非归档数据库，在线日志未覆盖时的恢复
先冷备（不包括日志文件）
sys@FOX> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 10485760 1 YES UNUSED 0
2 1 0 10485760 1 YES UNUSED 0
3 1 1 10485760 1 NO CURRENT 2460210340 15-NOV-10
sys@FOX> create table t_create as select * from dual;
Table created.
sys@FOX> create table t_normal (i int);
Table created.
sys@FOX> insert into t_normal values (1);
1 row created.
sys@FOX> select count(*) from t_create;
COUNT(*)
----------
1
sys@FOX> select count(*) from t_normal;
COUNT(*)
----------
1
sys@FOX> commit;
Commit complete.
sys@FOX> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 10485760 1 YES UNUSED 0
2 1 0 10485760 1 YES UNUSED 0
3 1 1 10485760 1 NO CURRENT 2460210340 15-NOV-10
sys@FOX> alter system switch logfile;
System altered.
sys@FOX> shutdown abort
ORACLE instance shut down.
--用备份恢复
sys@FOX> @d:\coldrecover
sys@FOX> startup mount
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 117440512 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.
sys@FOX> alter database open;
alter database open
*
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\FOX\REDO01.LOG'
sys@FOX> recover database using backup controlfile;
ORA-00279: change 2460231053 generated at 11/16/2010 09:34:29 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FOX\ARCHIVE\ARC00001.001
ORA-00280: change 2460231053 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
D:\ORACLE\ORADATA\FOX\REDO03.LOG
ORA-00279: change 2460231206 generated at 11/16/2010 11:15:42 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FOX\ARCHIVE\ARC00002.001
ORA-00280: change 2460231206 for thread 1 is in sequence #2
ORA-00278: log file 'D:\ORACLE\ORADATA\FOX\REDO03.LOG' no longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
D:\ORACLE\ORADATA\FOX\REDO01.LOG
Log applied.
Media recovery complete.
sys@FOX> alter databaes open resetlogs;
alter databaes open resetlogs
*
ERROR at line 1:
ORA-00940: invalid ALTER command
sys@FOX> alter database open resetlogs;
Database altered.
sys@FOX> select count(*) from t_create;
select count(*) from t_create
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 1, block # 26722)
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\FOX\SYSTEM01.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option
sys@FOX> select count(*) from t_normal;
COUNT(*)
----------
1
sys@FOX> desc t_create
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------
DUMMY VARCHAR2(1)
sys@FOX> spool off
从实验中可以看出，非归档数据库在在线日志没被覆盖的情况下是可以完全恢复的。但是如果是create table as select ...这样加载数据的方式由于加载本身不记录redo，所以恢复后会报错。而使用create table +insert table加载数据的表数据可以正常恢复。
阅读(851) | 评论(0) | 转发(0) |
