物理备份恢

物理备份恢复实验

查看数据库的当前归档模式：

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival            Disabled
Archive destination            USE_DB_RECOVERY_FILE_DESTOldest online log sequence     8
Current log sequence           10

将数据库设置为归档模式：

SQL> shu immediateDatabase closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

Database mounted.
SQL> alter database archivelog;

Database altered.
SQL> archive log list
Database log mode              Archive Mode

Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     8

Next log sequence to archive   10

Current log sequence           10

SQL>

首先创建一个用于恢复实验的表空间xbc，有一个数据文件c:\xbc01.dbf' 构成：

SQL> alter database open;Database altered.
SQL> create tablespace xbc 

2   datafile 'c:\xbc01.dbf' size 5m  

3   /
Tablespace created.

SQL>

关闭数据库，复制xbc01.dbf到另外的目录,注意这个数据文件显然不包含任何用户数据，如下：
SQL> shutdown immediateDatabase closed.
Database dismounted.
ORACLE instance shut down.

SQL> host copy c:\xbc01.dbf d:\xbc01.dbf        

 已复制1  个文件。

打开数据库，以scott用户连接数据库，在xbc表空间创建测试表test，如下：

SQL> startup nomountORACLE instance started.
Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

SQL> alter database mount  2  .
SQL> alter database mount;Database altered.

SQL> alter database open;Database altered.
SQL> alter user scott account unlock;User altered.
SQL> conn scott/tigerERROR:
ORA-28001: the password has expired
Changing password for scottNew password:
Retype new password:Password changedConnected.
SQL> create table test 

 2  tablespace xbc  

 3  as
 4  select * from dept;Table created.
SQL> select * from test;
   DEPTNO DNAME        LOC

---------- -------------- -------------       

 10 ACCOUNTING     NEW YORK        

 20 RESEARCH       DALLAS        

 30 SALES          CHICAGO        

 40 OPERATIONS     BOSTONSQL>

切换从做日志组，强制oracle生成几个归档日志，目的是为以后备份恢复数据文件时有应用归档日志的需要，如下：

SQL> conn / as sysdbaConnected.
SQL> alter system switch logfile;System altered.
SQL> alter system switch logfile;System altered.
SQL> alter system switch logfile;System altered.

关闭数据库，删除数据文件xbc01.dbf，模拟磁盘损坏或文件丢失（不关闭数据库oracle不应许删除文件），如下：

SQL> shutdown immediateDatabase closed.
Database dismounted.
ORACLE instance shut down.

SQL> host del c:\xbc01.dbf

重启数据库，由于数据文件丢失，oracle报错，如下：

SQL> startup
ORACLE instance started.
Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: 'C:\XBC01.DBF'

SQL>

把先前复制的数据文件复制回源目录再打开数据库，这时oracle依然报错，但是不是找不到 数据文件，而是说XBC01.DBF较旧，需要恢复，如下：

SQL> host copy d:\xbc01.dbf c:\xbc01.dbf         已复制1 个文件。
SQL> alter database open;alter database open*
ERROR at line 1:
ORA-01113: file 6 needs media recoveryORA-01110: data file 6: 'C:\XBC01.DBF'
SQL> recover datafile 6;
ORA-00279: change 399312 generated at 12/08/2011 10:44:28 needed for thread 1ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2011_12_08\O1_MF_1_10_%U_.ARC
ORA-00280: change 399312 for thread 1 is in sequence #10

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto
Log applied.
Media recovery complete.SQL>
SQL> alter database open;Database altered.SQL> conn scott/tigerConnected.
SQL> select * from test;
    DEPTNO DNAME          LOC

---------- -------------- -------------        

10 ACCOUNTING     NEW YORK        

20 RESEARCH       DALLAS        

30 SALES          CHICAGO       

40 OPERATIONS     BOSTONSQL>

可以看到，虽然当初的备份不包含任何数据，但是经过归档日志文件及联机重做日志文件，在备份完成后，产生的数据没有丢失；