Oracle 11g 表空間的誤刪除前傳, 有關控制檔案的備份和恢復（預熱）

--控制檔案備份到跟蹤檔案

sys@TESTDB11>alter database backup controlfile to trace;

Database altered.

--檢視匯出到哪個跟蹤檔案了

sys@TESTDB11>select value from v$diag_info where name = 'Default Trace File';

VALUE

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

/u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/TestDB11_ora_1656.trc

--檢視跟蹤檔案的內容

[oracle@S1011:/export/home/oracle]$ more /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/TestDB11_ora_1656.trc

CREATE CONTROLFILE REUSE DATABASE "TESTDB11" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/TestDB11/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/TestDB11/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/TestDB11/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/oradata/system01.dbf',

  '/oradata/sysaux01.dbf',

  '/oradata/undotbs01.dbf',

  '/oradata/users01.dbf',

  '/oradata/example01.dbf'

CHARACTER SET AL32UTF8

;

--檢視庫中所有的控制檔案

sys@TESTDB11>select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/TestDB11/control01.ctl

/u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

--關庫

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--將所有控制檔案移走

[oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/oradata/TestDB11/control01.ctl /tmp

[oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl /tmp

--現在只能啟動到nomount狀態

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

ORA-00205: error in identifying control file, check alert log for more info

sys@TESTDB11>select status from v$instance;

STATUS

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

STARTED

--重建控制檔案

sys@TESTDB11>CREATE CONTROLFILE REUSE DATABASE "TESTDB11" NORESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/TestDB11/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/TestDB11/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/TestDB11/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/oradata/system01.dbf',

  '/oradata/sysaux01.dbf',

  '/oradata/undotbs01.dbf',

  '/oradata/users01.dbf',

  '/oradata/example01.dbf'

CHARACTER SET AL32UTF8

 19  ;

Control file created.

--此時狀態已經為mount了

sys@TESTDB11>select status from v$instance;

STATUS

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

MOUNTED

--啟庫

sys@TESTDB11>alter database open;

Database altered.

--還有一種方式就是熱備份指令碼的最後一句，備份為二進位制

sys@TESTDB11>alter database backup controlfile to '/pooldisk02/backup01/inconsistent/control01.bak1' reuse;

Database altered.

--檢視備份出來的這個二進位制檔案的內容

[oracle@S1011:/export/home/oracle]$ strings /pooldisk02/backup01/inconsistent/control01.bak1

}|{z

TESTDB11

TAG20130813T041706

1TESTDB11

1TESTDB11

TestDB11

1,}&

1,}&

:+v-

a+v-

CEv-

:+v-

a+v-

CEv-

/u01/app/oracle/oradata/TestDB11/redo02.log

/u01/app/oracle/oradata/TestDB11/redo01.log

/u01/app/oracle/oradata/TestDB11/redo03.log

/oradata/example01.dbf

/oradata/users01.dbf

/oradata/undotbs01.dbf

/oradata/sysaux01.dbf

/oradata/system01.dbf

pooldisk02/backup01/inconsistent/control01.bak1

/u01/app/oracle/oradata/TestDB11/redo02.log

/u01/app/oracle/oradata/TestDB11/redo01.log

/u01/app/oracle/oradata/TestDB11/redo03.log

/oradata/example01.dbf

/oradata/users01.dbf

/oradata/undotbs01.dbf

/oradata/sysaux01.dbf

/oradata/system01.dbf

/pooldisk02/backup01/inconsistent/control01.bak1

SYSTEM

SYSAUX

UNDOTBS1

USERS

EXAMPLE

TEMP

SYSTEM

SYSAUX

UNDOTBS1

USERS

EXAMPLE

1,}&

/archive1/1_93_813665348.dbf

1,}&

/archive2/1_93_813665348.dbf

0,}&

/archive1/1_94_813665348.dbf

0,}&

/archive2/1_94_813665348.dbf

1,}&

/archive1/1_93_813665348.dbf

1,}&

/archive2/1_93_813665348.dbf

0,}&

/archive1/1_94_813665348.dbf

0,}&

/archive2/1_94_813665348.dbf

/archive1/1_95_813665348.dbf

/archive2/1_95_813665348.dbf

TestDB11

ORACLE_HOME

UNNAMED_INSTANCE_2

UNNAMED_INSTANCE_3

UNNAMED_INSTANCE_4

UNNAMED_INSTANCE_5

UNNAMED_INSTANCE_6

UNNAMED_INSTANCE_7

UNNAMED_INSTANCE_8

UNNAMED_INSTANCE_1

UNNAMED_INSTANCE_2

UNNAMED_INSTANCE_3

UNNAMED_INSTANCE_4

UNNAMED_INSTANCE_5

UNNAMED_INSTANCE_6

UNNAMED_INSTANCE_7

UNNAMED_INSTANCE_8

ACM unit testing operation

LSB Database Guard

Supplemental Log Data DDL

LSB Role Change Support

RFS block and kill across RAC

RAC-wide SGA

--關庫

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--移除2個控制檔案

[oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/oradata/TestDB11/control01.ctl /tmp

[oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl /tmp

--啟庫

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

ORA-00205: error in identifying control file, check alert log for more info

sys@TESTDB11>select status from v$instance;

STATUS

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

STARTED

--看一下控制檔案的位置

sys@TESTDB11>show parameter control_file

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/TestDB

                                                 11/control01.ctl, /u01/app/ora

                                                 cle/fast_recovery_area/TestDB1

                                                 1/control02.ctl

--還原回來

sys@TESTDB11>! cp /pooldisk02/backup01/inconsistent/control01.bak1 /u01/app/oracle/oradata/TestDB11/control01.ctl

sys@TESTDB11>! cp /pooldisk02/backup01/inconsistent/control01.bak1 /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl

--用歷史的控制檔案mount

sys@TESTDB11>alter database mount;

Database altered.

--檢視控制控制檔案中記錄的檢查點的資訊

sys@TESTDB11>col name for a30

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

NAME                           CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                     2525453

/oradata/sysaux01.dbf                     2525453

/oradata/undotbs01.dbf                    2525453

/oradata/users01.dbf                      2525453

/oradata/example01.dbf                    2525453

--檢視資料檔案中記錄的檢查點資訊（比控制檔案中的新）

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

NAME                           CHECKPOINT_CHANGE#

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

/oradata/system01.dbf                     2526071

/oradata/sysaux01.dbf                     2526071

/oradata/undotbs01.dbf                    2526071

/oradata/users01.dbf                      2526071

/oradata/example01.dbf                    2526071

--此時開庫提示

sys@TESTDB11>alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

--嘗試使用resetlogs開庫

sys@TESTDB11>alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oradata/system01.dbf'

--嘗試直接恢復

sys@TESTDB11>recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

--指定使用歷史的控制檔案進行恢復,但歸檔日誌沒歸檔出來

sys@TESTDB11>recover database using backup controlfile;

ORA-00279: change 2525716 generated at 08/13/2013 04:13:38 needed for thread 1

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

ORA-00280: change 2525716 for thread 1 is in sequence #96

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

auto

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

ORA-27037: unable to obtain file status

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

Additional information: 3

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

ORA-27037: unable to obtain file status

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

Additional information: 3

--確定需要的96號日誌還沒有歸檔出來

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

 SEQUENCE#     GROUP# STATUS

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

        94          1 INACTIVE

        96          3 CURRENT

        95          2 INACTIVE

--確定是哪個聯機重做日誌檔案

sys@TESTDB11>col member for a50

sys@TESTDB11>select group#, member from v$logfile;

    GROUP# MEMBER

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

         2 /u01/app/oracle/oradata/TestDB11/redo02.log

         1 /u01/app/oracle/oradata/TestDB11/redo01.log

         3 /u01/app/oracle/oradata/TestDB11/redo03.log

--再次進行恢復,指定使用特定的日誌檔案

sys@TESTDB11>recover database using backup controlfile;

ORA-00279: change 2525716 generated at 08/13/2013 04:13:38 needed for thread 1

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

ORA-00280: change 2525716 for thread 1 is in sequence #96

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

/u01/app/oracle/oradata/TestDB11/redo03.log                     -- 直接輸入需要的聯機重做日誌

Log applied.

Media recovery complete.

sys@TESTDB11>alter database open resetlogs;

Database altered.

--檢視已經備份的歸檔日誌

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

 SEQUENCE# NAME

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

        93 /archive1/1_93_813665348.dbf

        93 /archive2/1_93_813665348.dbf

        94 /archive1/1_94_813665348.dbf

        94 /archive2/1_94_813665348.dbf

        95 /archive1/1_95_813665348.dbf

        95 /archive2/1_95_813665348.dbf

        94 /archive1/1_94_813665348.dbf

        94 /archive2/1_94_813665348.dbf

        95 /archive1/1_95_813665348.dbf

        95 /archive2/1_95_813665348.dbf

        96 /archive1/1_96_813665348.dbf

        96 /archive2/1_96_813665348.dbf

12 rows selected.

--對整個庫進行非一致性備份

sys@TESTDB11>@backup_script/backup02.sql