数据库备份与恢复测试2

第一种：
如果损坏的是下列文件，必须在关闭状态下作恢复:系统数据文件，回滚段(Undo)表空间的数据文件，整个数据库大多数文件损坏。


SQL> startup
orACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             180358020 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
Database mounted.
orA-01113: file 1 needs media recovery
orA-01110: data file 1:
'D:\ORACLE\10.2.0\ORADATA\NERO\DATAFILE\O1_MF_SYSTEM_56X0H67B_.DBF'


SQL> recover datafile 1;
Media recovery complete.
SQL> alter database open;

第二种：
适合场景：数据库当前是关闭的，某个文件丢失损坏，介质故障的文件不包括系统表空间和Undo表空间的文件，为了减少恢复对业务的影响，在恢复过程之前首先将数据库打开。

SQL> startup
orACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             180358020 bytes
Database Buffers          423624704 bytes
Redo Buffers                7135232 bytes
Database mounted.
orA-01113: file 4 needs media recovery
orA-01110: data file 4:
'D:\ORACLE\10.2.0\ORADATA\NERO\DATAFILE\O1_MF_USERS_56X0J0SD_.DBF'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
------------
         4 ONLINE  ONLINE
FILE NOT FOUND                                                             0


SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> recover tablespace users;
orA-00279: change 3782263 generated at 08/24/2009 21:06:54 needed for thread 1
orA-00289: suggestion :
D:\ORACLE\10.2.0\FLASH_RECOVERY_AREA\NERO\ARCHIVELOG\2009_08_26\O1_MF_1_146_%U_.

ARC
orA-00280: change 3782263 for thread 1 is in sequence #146


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter tablespace users online;

Tablespace altered.

SQL>

第三种：
恢复一个从未备份过的数据文件
适合场景：丢失的数据文件从未备份过，恢复没备份的数据文件有严格的要求：需要恢复的不能是系统表空间的数据文件，必须存在从数据文件创建以来所有的归档日志，在创建文件之后控制文件没有被重建过
上面的要求都必须满足，否则不能恢复

SQL> create tablespace test datafile 'D:\oracle\10.2.0\oradata\NERO\DATAFILE\test01.dbf' size 5m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1        151   52428800          3 NO  CURRENT
      3822169 26-AUG-09

         2          1        149   52428800          3 YES ACTIVE
      3815287 25-AUG-09

         3          1        150   52428800          3 YES ACTIVE
      3822167 26-AUG-09



SQL> alter system switch logfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
orACLE instance shut down.
SQL> startup mount;
orACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             184552324 bytes
Database Buffers          419430400 bytes
Redo Buffers                7135232 bytes
Database mounted.
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
------------
        11 ONLINE  ONLINE
FILE NOT FOUND                                                             0



SQL> alter database datafile 11 offline;

Database altered.

SQL> alter database open;

Database altered.
SQL> alter database create datafile  'D:\oracle\10.2.0\oradata\NERO\DATAFILE\tes
t01.dbf'as  'D:\oracle\10.2.0\oradata\NERO\DATAFILE\test01.dbf';

Database altered.

SQL> recover datafile 11;
orA-00279: change 3822136 generated at 08/26/2009 13:36:50 needed for thread 1
orA-00289: suggestion :
D:\ORACLE\10.2.0\FLASH_RECOVERY_AREA\NERO\ARCHIVELOG\2009_08_26\O1_MF_1_149_%U_.

ARC
orA-00280: change 3822136 for thread 1 is in sequence #149


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.

文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags: ORA-600讲师ORA-600讲师