歸檔模式下，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/，如需轉載，請註明出處，否則將追究法律責任。

歸檔模式下，offline表空間備份與恢復

相關文章