冷备份还原数据库是不是不能前滚恢复？

shangym

测试一也说明了恢复的时候，并不需要冷备下来的在线日志
仔细看了一遍zalbb的测试，发现我前面看漏了，他确实是指定了灾难时刻的在线日志

cyr1974

[QUOTE]最初由 shangym 发布
[B]测试一：
冷备份前test用户有表AA ，冷备份后删除表AA
做恢复时，仅恢复了所有的数据文件，未覆盖当前的控制文件和在线日志文件
恢复命令：recover database until cancel
恢复中指定了当前的在线日志，apply完成后用open resetlogs打开数据库
打开后表AA不存在，结果正确

这个恢复和用热备数据文件的恢复操作基本类似


你的这个恢复是建立在控制文件没有丢的情况下，如果控制文件也丢了呢？我想是否可有以下两种情况来恢复？
1、在数据库关闭状态下，restore所有的冷备份时候的文件，当然包括冷备控制文件，然后使用recover database using backup controlfile untill cancel恢复，resetlog方式打开数据库。
2、在数据库关闭的状态下，restore冷备份时候的文件，控制文件除外，然后重建控制文件，使用recover database untill cancel恢复，最后在使用resetlog方式打开数据库。

ZALBB

2、在数据库关闭的状态下，restore冷备份时候的文件，控制文件除外，然后重建控制文件，使用recover database untill cancel恢复，最后在使用resetlog方式打开数据库。

---对于这种情况，谁能测试一下，是否还可以使用备份的归档日志恢复？

biti_rainy

重新创建控制文件是没有问题的

比使用旧的还方便，自动恢复模式下直接recover database再打开就可以了

shangym

我继续测重建控制文件情况下的恢复，稍等

cyr1974

最初由 biti_rainy 发布
[B]重新创建控制文件是没有问题的

比使用旧的还方便，自动恢复模式下直接recover database再打开就可以了 [/B]

老大：重新创建控制文件这种情况是否还需要最后的重作日至才能完全恢复？另外如果恢复到cancel，没有最后的重做日至，这样的话肯定要丢失部分数据。这样理解是否准确？

shangym

here，测试二
[php]
SQL> connect test/test
Connected.
SQL> create table aa (id number);

Table created.

SQL> insert into aa values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
$ ls
1_23.dbf       control02.ctl  drsys01.dbf    indx01.dbf     redo02.log     system01.dbf   tools01.dbf    user01.dbf
control01.ctl  control03.ctl  example01.dbf  redo01.log     redo03.log     temp01.dbf     undotbs01.dbf  xdb01.dbf
$ pwd
/oracle/oradata/sdb
$ cp * ../bak/
$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 11 13:54:18 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  739740648 bytes
Fixed Size                   732136 bytes
Variable Size             301989888 bytes
Database Buffers          436207616 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.
SQL> connect test/test
Connected.
SQL> drop table aa;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
$ cd $ORACLE_BASE/admin/sdb/udump
$ ls
sdb_ora_15399.trc  sdb_ora_23065.trc  sdb_ora_4342.trc   sdb_ora_4496.trc
sdb_ora_22897.trc  sdb_ora_23294.trc  sdb_ora_4493.trc
$ ls -l
total 508
-rw-r-----   1 oracle   dba         1381 Apr 26 09:42 sdb_ora_15399.trc
-rw-r-----   1 oracle   dba         1573 May 11 12:46 sdb_ora_22897.trc
-rw-r-----   1 oracle   dba          741 May 11 13:01 sdb_ora_23065.trc
-rw-r-----   1 oracle   dba         4994 May 11 13:55 sdb_ora_23294.trc
-rw-r-----   1 oracle   dba       210163 Feb 26 14:22 sdb_ora_4342.trc
-rw-r-----   1 oracle   dba        15768 Feb 26 14:35 sdb_ora_4493.trc
-rw-r-----   1 oracle   dba        11703 Feb 26 14:37 sdb_ora_4496.trc
$ vi sdb_ora_23294.trc
"sdb_ora_23294.trc" 136 lines, 4994 characters
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SDB" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
LOGFILE
  GROUP 1 '/oracle/oradata/sdb/redo01.log'  SIZE 100M,
  GROUP 2 '/oracle/oradata/sdb/redo02.log'  SIZE 100M,
  GROUP 3 '/oracle/oradata/sdb/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/sdb/system01.dbf',
  '/oracle/oradata/sdb/undotbs01.dbf',
  '/oracle/oradata/sdb/drsys01.dbf',
  '/oracle/oradata/sdb/example01.dbf',
  '/oracle/oradata/sdb/indx01.dbf',
  '/oracle/oradata/sdb/tools01.dbf',
  '/oracle/oradata/sdb/user01.dbf',
  '/oracle/oradata/sdb/xdb01.dbf'
CHARACTER SET UTF8
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/sdb/temp01.dbf'
     SIZE 41943040  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 16383M;
:q
$ cd
$ cd /oracle/oradata/sdb
$ ls
1_23.dbf       control02.ctl  drsys01.dbf    indx01.dbf     redo02.log     system01.dbf   tools01.dbf    user01.dbf
control01.ctl  control03.ctl  example01.dbf  redo01.log     redo03.log     temp01.dbf     undotbs01.dbf  xdb01.dbf
$ vi control.sql
"control.sql" [New file]
CREATE CONTROLFILE REUSE DATABASE "SDB" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 113
LOGFILE
  GROUP 1 '/oracle/oradata/sdb/redo01.log'  SIZE 100M,
  GROUP 2 '/oracle/oradata/sdb/redo02.log'  SIZE 100M,
  GROUP 3 '/oracle/oradata/sdb/redo03.log'  SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/sdb/system01.dbf',
  '/oracle/oradata/sdb/undotbs01.dbf',
  '/oracle/oradata/sdb/drsys01.dbf',
  '/oracle/oradata/sdb/example01.dbf',
  '/oracle/oradata/sdb/indx01.dbf',
  '/oracle/oradata/sdb/tools01.dbf',
  '/oracle/oradata/sdb/user01.dbf',
  '/oracle/oradata/sdb/xdb01.dbf'
CHARACTER SET UTF8
;
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"control.sql" [New file] 24 lines, 737 characters
$ ls
1_23.dbf       control02.ctl  example01.dbf  redo02.log     temp01.dbf     user01.dbf
control.sql    control03.ctl  indx01.dbf     redo03.log     tools01.dbf    xdb01.dbf
control01.ctl  drsys01.dbf    redo01.log     system01.dbf   undotbs01.dbf
$ mv control01.ctl control01.ctl.bak
$ rm control02.ctl control03.ctl
$ ls
1_23.dbf           drsys01.dbf        redo01.log         system01.dbf       undotbs01.dbf
control.sql        example01.dbf      redo02.log         temp01.dbf         user01.dbf
control01.ctl.bak  indx01.dbf         redo03.log         tools01.dbf        xdb01.dbf
[/php]

shangym

$ cp ../bak/*.dbf ./
$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.4.0 - Production on Tue May 11 13:58:21 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> @control
ORACLE instance started.

Total System Global Area  739740648 bytes
Fixed Size                   732136 bytes
Variable Size             301989888 bytes
Database Buffers          436207616 bytes
Redo Buffers                 811008 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 19678954 generated at 05/11/2004 13:26:00 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/bak/1_1.dbf
ORA-00280: change 19678954 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/oracle/oradata/bak/1_1.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log '/oracle/oradata/bak/1_1.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ---------------
         1          1          0  104857600          1 YES UNUSED
            0

         2          1          0  104857600          1 YES UNUSED
            0

         3          1          1  104857600          1 NO  INVALIDATED
     19677729 11-MAY-04


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 19678954 generated at 05/11/2004 13:26:00 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/bak/1_1.dbf
ORA-00280: change 19678954 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/oradata/sdb/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> connect test/test
Connected.
SQL> select * from aa;
select * from aa
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

shangym

测试二的说明
冷备份前test用户有表AA，冷备后重启数据库，删除表AA
shutdown abort之后，重建了控制文件，并用冷备的数据文件和当前的在线日志恢复成功，这里做的时候漏了归档了，所以有归档日志的话，恢复过程中也会应用归档日志

ZALBB

你的结论是，即使是新创建的控制文件，也可以使用来恢复到数据库关闭的最后一刻？