Oracle 11g 手工不完全恢復 場景1：被動的不完全恢復（日誌缺失）

--檢視emp表原始資料

scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        810                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1610        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1260        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2985                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1260       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2860                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2460                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3010                    20

      7839 KING       PRESIDENT            17-NOV-81       5010                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1510          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1110                    20

      7900 JAMES      CLERK           7698 03-DEC-81        960                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3010                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1310                    10

14 rows selected.

--檢視當前的日誌序號(10)

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

        10          1 CURRENT

         8          2 INACTIVE

         9          3 INACTIVE

3 rows selected.

--修改資料並提交(10)

scott@TESTDB11>update emp set sal = 100 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--再次修改資料並提交(11)

scott@TESTDB11>update emp set sal = 200 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.   

--再次修改並提交(12)

scott@TESTDB11>update emp set sal = 300 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--再次修改並提交(13)

scott@TESTDB11>update emp set sal = 400 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--再次修改並提交(14)

scott@TESTDB11>update emp set sal = 500 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--再次修改並提交(15)

scott@TESTDB11>update emp set sal = 600 where empno = 7369;

1 row updated.

scott@TESTDB11>commit;

Commit complete.

sys@TESTDB11>alter system switch logfile;

System altered.

--檢視當前日誌組(16)

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

        16          1 CURRENT

        14          2 INACTIVE

        15          3 INACTIVE

3 rows selected.

--關庫     

sys@TESTDB11>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

--檔案損壞

sys@TESTDB11>!rm /oradata/TestDB11/users01.dbf

sys@TESTDB11>startup

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: '/oradata/TestDB11/users01.dbf'

sys@TESTDB11>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME

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

         4 ONLINE  ONLINE  FILE NOT FOUND                0

--還原

sys@TESTDB11>!cp /pooldisk02/backup01/inconsistent/users01.dbf /oradata/TestDB11

sys@TESTDB11>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME

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

         4 ONLINE  ONLINE                          2521567 11-AUG-13

--確定一下，恢復需要的所有日誌檔案（大概判斷一下，讓12號日誌損壞，則可以恢復到11號, 即sal = 200

sys@TESTDB11>col archive_name for a30

sys@TESTDB11>select * from v$recovery_log;

   THREAD#  SEQUENCE# TIME      ARCHIVE_NAME

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

         1         10 12-AUG-13 /archive2/1_10_823217125.dbf

         1         11 12-AUG-13 /archive2/1_11_823217125.dbf

         1         12 12-AUG-13 /archive2/1_12_823217125.dbf

         1         13 12-AUG-13 /archive2/1_13_823217125.dbf

4 rows selected.

23 rows selected.

--檢視一下歸檔日誌的詳細資訊

sys@TESTDB11>select sequence#, name, first_change#, next_change# from v$archived_log;

SEQUENCE# NAME                           FIRST_CHANGE# NEXT_CHANGE#

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

         9 /archive1/1_9_823217125.dbf          2651179      2675262

         9 /archive2/1_9_823217125.dbf          2651179      2675262

        10 /archive1/1_10_823217125.dbf         2675262      2679106

        10 /archive2/1_10_823217125.dbf         2675262      2679106

        11 /archive1/1_11_823217125.dbf         2679106      2679128

        11 /archive2/1_11_823217125.dbf         2679106      2679128

        12 /archive1/1_12_823217125.dbf         2679128      2679157

        12 /archive2/1_12_823217125.dbf         2679128      2679157

        13 /archive1/1_13_823217125.dbf         2679157      2679357

        13 /archive2/1_13_823217125.dbf         2679157      2679357

        14 /archive1/1_14_823217125.dbf         2679357      2679389

        14 /archive2/1_14_823217125.dbf         2679357      2679389

        15 /archive1/1_15_823217125.dbf         2679389      2679408

        15 /archive2/1_15_823217125.dbf         2679389      2679408

112 rows selected.

--日誌歸檔12損壞

sys@TESTDB11>!rm /archive1/1_12_823217125.dbf

sys@TESTDB11>!rm /archive2/1_12_823217125.dbf

--執行恢復,當需要歸檔12時出錯

sys@TESTDB11>recover datafile 4;

ORA-00279: change 2678693 generated at 08/12/2013 07:14:28 needed for thread 1

ORA-00289: suggestion : /archive2/1_10_823217125.dbf

ORA-00280: change 2678693 for thread 1 is in sequence #10

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2679106 generated at 08/12/2013 07:21:56 needed for thread 1

ORA-00289: suggestion : /archive2/1_11_823217125.dbf

ORA-00280: change 2679106 for thread 1 is in sequence #11

ORA-00279: change 2679128 generated at 08/12/2013 07:22:28 needed for thread 1

ORA-00289: suggestion : /archive2/1_12_823217125.dbf

ORA-00280: change 2679128 for thread 1 is in sequence #12

ORA-00308: cannot open archived log '/archive2/1_12_823217125.dbf'

ORA-27037: unable to obtain file status

Solaris-AMD64 Error: 2: No such file or directory

Additional information: 3

--檢視資料檔案頭，檢查點不一致

sys@TESTDB11>col name for a40

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                     CHECKPOINT_CHANGE#

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

/oradata/TestDB11/system01.dbf                      2679518

/oradata/TestDB11/sysaux01.dbf                      2679518

/oradata/TestDB11/undotbs01.dbf                     2679518

/oradata/TestDB11/users01.dbf                       2679128

/oradata/TestDB11/example01.dbf                     2679518

/oradata/TestDB11/newundotbs01.dbf                  2679518

6 rows selected.

--還原

sys@TESTDB11>shutdown abort

ORACLE instance shut down.

sys@TESTDB11>!cp /pooldisk02/backup01/inconsistent/*.dbf /oradata/TestDB11

sys@TESTDB11>startup mount;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

--檢查點不一致（因為這是非一致性備份)，但所有的檢查點都比12號日誌first_change#小

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                     CHECKPOINT_CHANGE#

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

/oradata/TestDB11/system01.dbf                      2678611

/oradata/TestDB11/sysaux01.dbf                      2678638

/oradata/TestDB11/undotbs01.dbf                     2678672

/oradata/TestDB11/users01.dbf                       2678693

/oradata/TestDB11/example01.dbf                     2678712

/oradata/TestDB11/newundotbs01.dbf                  2678734

6 rows selected.

--恢復

sys@TESTDB11>recover database until cancel;

sys@TESTDB11>recover database until cancel;

ORA-00279: change 2678611 generated at 08/12/2013 07:13:05 needed for thread 1

ORA-00289: suggestion : /archive2/1_10_823217125.dbf

ORA-00280: change 2678611 for thread 1 is in sequence #10

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2679106 generated at 08/12/2013 07:21:56 needed for thread 1

ORA-00289: suggestion : /archive2/1_11_823217125.dbf

ORA-00280: change 2679106 for thread 1 is in sequence #11

ORA-00278: log file '/archive2/1_10_823217125.dbf' no longer needed for this recovery

ORA-00279: change 2679128 generated at 08/12/2013 07:22:28 needed for thread 1

ORA-00289: suggestion : /archive2/1_12_823217125.dbf

ORA-00280: change 2679128 for thread 1 is in sequence #12

ORA-00278: log file '/archive2/1_11_823217125.dbf' no longer needed for this recovery

ORA-00308: cannot open archived log '/archive2/1_12_823217125.dbf'

ORA-27037: unable to obtain file status

Solaris-AMD64 Error: 2: No such file or directory

Additional information: 3

--再檢視檔案中

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                     CHECKPOINT_CHANGE#

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

/oradata/TestDB11/system01.dbf                      2679128

/oradata/TestDB11/sysaux01.dbf                      2679128

/oradata/TestDB11/undotbs01.dbf                     2679128

/oradata/TestDB11/users01.dbf                       2679128

/oradata/TestDB11/example01.dbf                     2679128

/oradata/TestDB11/newundotbs01.dbf                  2679128

6 rows selected.

--檢視當前日誌組, 與11脫節了

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

        16          1 CURRENT

        15          3 INACTIVE

        14          2 INACTIVE

3 rows selected.

-- 開庫時需要使用RESETLOGS選項

sys@TESTDB11>alter database open resetlogs;

Database altered.

--日誌序號從1開始從頭記

sys@TESTDB11>select sequence#, group#, status from v$log;

 SEQUENCE#     GROUP# STATUS

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

         1          1 CURRENT

         0          2 UNUSED

         0          3 UNUSED

3 rows selected.

--確認資料

scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        200                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1610        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1260        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2985                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1260       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2860                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2460                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3010                    20

      7839 KING       PRESIDENT            17-NOV-81       5010                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1510          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1110                    20

      7900 JAMES      CLERK           7698 03-DEC-81        960                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3010                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1310                    10

14 rows selected.

--刪除日誌

[oracle@S1011:/archive1]$ rm *

[oracle@S1011:/archive1]$ cd /archive2

[oracle@S1011:/archive2]$ rm *

--重新做整庫非一致性備份

[oracle@S1011:/pooldisk02/backup01/inconsistent]$ rm *

sys@TESTDB11>@backup_script/backup02.sql

alter tablespace SYSTEM begin backup;

!cp /oradata/TestDB11/system01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

!cp /oradata/TestDB11/sysaux01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace SYSAUX end backup;

alter tablespace UNDOTBS1 begin backup;

!cp /oradata/TestDB11/undotbs01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;

!cp /oradata/TestDB11/users01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace USERS end backup;

alter tablespace EXAMPLE begin backup;

!cp /oradata/TestDB11/example01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace EXAMPLE end backup;

alter tablespace NEWUNDOTBS begin backup;

!cp /oradata/TestDB11/newundotbs01.dbf /pooldisk02/backup01/inconsistent/

alter tablespace NEWUNDOTBS end backup;

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.

Tablespace altered.