归档模式下，offline表空间备份与恢复

1.查看数据库运行模式
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11

2.scott用户默认表空间为users,在scott用户下创建一个表
SQL> desc book;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(10)
 插入数据
SQL> select * from book;

        ID NAME
---------- ----------
         1 oracle
        13 jsp
        14 java
3.做一个users表空间offline备份
SQL> select tablespace_name,file_name from dba_data_files
  2  where tablespace_name='USERS';

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------
USERS                          D:\ORACLE\ORADATA\ORCL\USERS01.DBF
SQL> alter tablespace users offline;
D:\>copy D:\ORACLE\ORADATA\ORCL\USERS01.DBF d:\hotbak\users01%date:~0,10%.dbf
SQL> alter tablespace users online;
继续插入数据(15-19)
SQL> select * from book;

        ID NAME
---------- ----------
         1 oracle
        13 jsp
        14 java
        15 net
        16 ocp
        17 ccie
        18 ocm
        19 ccnp
4.恢复(数据库关闭情况下恢复)
 1)模拟删除user01.dbf
    shutdown immediate;
    删除user01.dbf
 2)
 SQL> startup
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1248768 bytes
Variable Size              88080896 bytes
Database Buffers          218103808 bytes
Redo Buffers                7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE\ORADATA\ORCL\USERS01.DBF'
由于数据文件损坏不能打开数据库
3)查看要恢复的文件
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
         4 ONLINE  ONLINE  FILE NOT FOUND                                                             0
 
4)把之前offline备份的数据文件拷回原来位置并重命名为user01.dbf
  或copy d:\hotbak\users012014-02-22.dbf D:\ORACLE\ORADATA\ORCL\USERS01.DBF
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------
         4 ONLINE  ONLINE                                                                       7284383 22-FEB-14
再查看要恢复的文件错误信息消失
5)查看需要哪些归档日志文件
SQL> select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME         ARCHIVE_NAME
---------- ---------- ------------ ----------------------------------------------------------------------------------------------------
         1         15 22-FEB-14    C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_9JJ3QMVC_.ARC
         1         16 22-FEB-14    C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_16_9JJ3RGWB_.ARC

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1         17   52428800          1 YES INACTIVE               7285006 22-FEB-14
         3          1         19   52428800          1 NO  CURRENT                7285144 22-FEB-14
         2          1         18   52428800          1 YES INACTIVE               7285016 22-FEB-14
SQL> select name,first_change#,next_change# from v$archived_log;
6)恢复
SQL> recover tablespace users;
ORA-00279: change 7284519 generated at 02/22/2014 10:43:44 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_%U_.ARC
ORA-00280: change 7284519 for thread 1 is in sequence #15

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 7284986 generated at 02/22/2014 10:44:03 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_16_%U_.ARC
ORA-00280: change 7284986 for thread 1 is in sequence #16
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_02_22\O1_MF_1_15_9JJ3QMVC_.ARC' no longer needed for this recov
ery

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
虽然17, 18已经归档，但在恢复的时候没有找归档日志，而是找在线日志文件
SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1         17   52428800          1 YES INACTIVE               7285006 22-FEB-14
         2          1         18   52428800          1 YES INACTIVE               7285016 22-FEB-14
         3          1         19   52428800          1 NO  CURRENT                7285144 22-FEB-14
7)打开数据库
SQL> alter database open;

Database altered.
8)scott用户查看数据
SQL> select * from book;

        ID NAME
---------- ----------
         1 oracle
        13 jsp
        14 java
        15 net
        16 ocp
        17 ccie
        18 ocm
        19 ccnp

8 rows selected.

总结：
  1.mount状态下，表空间是不能offline;
  2.(数据开打开情况下恢复--&gt数据库运行过程中，数据库处于打开状态数据文件被误删除,为了能让数据库继续运行，把被误删除的表空间offline进行恢复)
问题:
  3.如果归档文件不在原来的位置，要怎么恢复?
  4.一般生产库都要对外工作，表空间都不能offline,什么情况下使用offline表空间备份?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26937943/viewspace-1086078/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26937943/viewspace-1086078/