非归档方式数据库的冷备恢复（日志未被覆盖）

非归档数据库，在线日志未覆盖时的恢复

先冷备（不包括日志文件）
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: {<RET>=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: {<RET>=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加载数据的表数据可以正常恢复。