模拟控制檔案丢失的資料庫恢複

一資料庫版本

[email protected]>showuser

USER is"SYS"

[email protected]>select* from v$version;

BANNER

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

Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux:Version 11.2.0.1.0 - Production

NLSRTL Version11.2.0.1.0 - Production

1.模拟控制檔案丢失後的資料庫恢複(完全恢複)。

今天的主題是備份與恢複，目的就是保護資料的安全性，衆所周知Oracle之是以在市場上占據了50%的份額，與它提供了強大的資料保護措施是分不開的，下面我們就來簡捷的介紹一下。

1）實體備份

冷備：這是最原始的一種備份方法，又是最簡單可行的，就和copy一份檔案一樣，直接把庫shutdown拷貝一份即可，操作簡單，恢複快。當在一個沒有專業人員的場合下，告訴他們這麼操作是簡單可行的，不是不可能隻是你沒遇到，一切皆有可能哦！

熱備：Oracle專業備份工具RMAN，這是在8i就有的東東，很強大，可以在很多元度層面進行備份恢複，利用RMAN可以在聯機的情況下進行線上備份與恢複。

2）邏輯備份

Exp/Imp：表級  使用者級  資料庫級進行邏輯備份，邏輯是對于業務層面而言的，例如我隻想備份person employment  address表的内容用這種方法将會非常簡單，它的亮點更在于備份出來的檔案非常好遷移，相容不同版本

Expdp/Impdp：這是上面2個工具的進階版，可壓縮速度更快傳輸表空間的最佳利器，但隻能用在伺服器端

3）執行個體恢複

執行個體是什麼，就是記憶體區+背景程序，那麼執行個體恢複也就是恢複記憶體資料，例如突然當機  掉電  強制關庫等，在你startup啟動時候背景會自動進行執行個體恢複。SMON程序負責執行

4）媒體恢複

就是恢複硬碟資料，例如  檔案被誤删除  壞塊等，需要手工恢複

在介紹了幾種備份恢複方法後，我們進入topic，如何進行控制檔案丢失恢複，先看一下資料庫各種狀态

5）[email protected]>archivelog list        資料庫處于非歸檔狀态

Database logmode              No Archive Mode

Automaticarchival             Disabled

Archivedestination            /u02/app/oracle/product/11.2.0/db_1/dbs/arch

Oldest online logsequence     71

Current logsequence           73

我們要先做一個RMAN全備，首先啟動歸檔功能

[[email protected]]$ pwd

/u02/app/oracle

[[email protected]]$ mkdir archdata        建立一個歸檔日志目錄

在ORACLE10g和11g版本，ORACLE預設的日志歸檔路徑為閃回恢複區，但我們也可以修改為自己指定的目錄路徑

[email protected]>altersystem set log_archive_dest_1='location=/u02/app/oracle/archdata' scope=both;

System altered.

[email protected]>setlinesize 300 pagesize 999

設定的歸檔日志儲存路徑已經生效

[email protected]>selectdest_name,destination,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_1';

DEST_NAME           DESTINATION               STATUS    ERROR

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

LOG_ARCHIVE_DEST_1   /u02/app/oracle/archdata    VALID

啟動到mount狀态，啟動歸檔模式

[email protected]>shutdownimmediate                 關庫

Database closed.

Databasedismounted.

ORACLE instanceshut down.

[email protected]>startupmount                       mount狀态

ORACLE instancestarted.

Total SystemGlobal Area  471830528 bytes

Fixed Size                  2214456 bytes

Variable Size             150996424 bytes

DatabaseBuffers          310378496 bytes

Redo Buffers                8241152 bytes

Database mounted.

[email protected]>alterdatabase archivelog;            啟動歸檔模式

Database altered.

[email protected]>alterdatabase open;                 打開資料庫

Database altered.

注：凡是alter database操作都是對控制檔案進行修改

   凡是alter system操作都是對參數檔案進行修改

[email protected]>altersystem switch logfile;            手工切換日志（不會觸發檢查點，自動切換會）

System altered.

[email protected]>selectsequence#,name,archived,applied from v$archived_log;檢視已經歸檔的日志資訊

SEQUENCE#  NAME                                     ARC   APPLIED

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

73            /u02/app/oracle/archdata/1_73_813654649.dbf   YES    NO

作業系統層面檢視，沒有問題也生成了

[[email protected]]$ ll

total 5624

-rw-r----- 1oracle asmadmin 5757952 Apr 25 21:28 1_73_813654649.dbf

[email protected]>archivelog list

Database logmode              Archive Mode         歸檔模式

Automaticarchival             Enabled               自動歸檔啟動

Archivedestination           /u02/app/oracle/archdata歸檔日志目錄

Oldest online logsequence     72                     舊線上日志序号

Next log sequenceto archive   74                     下一個歸檔日志序号

Current logsequence         74                     目前日志序号

下面我們就要進行RMAN全庫備份了，在此之前還需要設定一下RMAN的環境變量

6）登陸RMAN

[[email protected]]$ rman target sys/oracle

Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 06:05:24 2013

Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.

connectedto target database: LEO1 (DBID=1692458681)隻有連接配接到目标庫才能顯示環境變量，這些中繼資料是存放在控制檔案中的

顯示目前RMAN的環境變量

RMAN> show all;

using targetdatabase control file instead of recovery catalog

RMAN configurationparameters for database with db_unique_name LEO1 are:

CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUPOPTIMIZATION OFF; # default

CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default

CONFIGURECONTROLFILE AUTOBACKUP OFF; # default

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOGBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGUREMAXSETSIZE TO UNLIMITED; # default

CONFIGUREENCRYPTION FOR DATABASE OFF; # default

CONFIGUREENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default

CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default

建立RMAN預設備份媒體儲存目錄/u02/app/oracle/backup

[[email protected]]$ mkdir backup

RMAN>configure channel device type disk format'/u02/app/oracle/backup/DB_%U';

new RMANconfiguration parameters:

CONFIGURE CHANNELDEVICE TYPE DISK FORMAT   '/u02/app/oracle/backup/DB_%U';

new RMANconfiguration parameters are successfully stored   新man配置參數生效

配置控制檔案自動備份并儲存到/u02/app/oracle/backup/control目錄

[[email protected]]$ mkdir control

[[email protected]]$ pwd

/u02/app/oracle/backup/control

RMAN>configure controlfile autobackup on;               啟動控制檔案自動備份

new RMANconfiguration parameters:

CONFIGURECONTROLFILE AUTOBACKUP ON;

new RMANconfiguration parameters are successfully stored

RMAN>configure controlfile autobackup format for device type diskto '/u02/app/oracle/backup/control/cf_%F';

new RMANconfiguration parameters:      配置控制檔案自動備份目錄和格式

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';

new RMANconfiguration parameters are successfully stored

調整備份媒體保留期為7天

RMAN>configureretention policy to recovery window of 7 days;

new RMANconfiguration parameters:

CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMANconfiguration parameters are successfully stored

顯示配置後RMAN環境變量

RMAN> show all;

RMAN configurationparameters for database with db_unique_name LEO1 are:

CONFIGURERETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUPOPTIMIZATION OFF; # default

CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default

CONFIGURECONTROLFILE AUTOBACKUP ON;

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u02/app/oracle/backup/control/cf_%F';

CONFIGURE DEVICETYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILEBACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGUREARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURECHANNEL DEVICE TYPE DISK FORMAT  '/u02/app/oracle/backup/DB_%U';

CONFIGUREMAXSETSIZE TO UNLIMITED; # default

CONFIGUREENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTIONALGORITHM 'AES128'; # default

CONFIGURECOMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;# default

CONFIGUREARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOTCONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_LEO1.f'; #default

帶顔色的就是我們剛剛修改過的變量

7）啟動RMAN的壓縮備份功能對資料庫進行全備Oracle10g隻壓縮RMAN中繼資料11g真正壓縮了資料

backup ascompressedbackupset full database format               指令行中直接指定壓縮選項即可

'/u02/app/oracle/backup/full_bk1_%u%p%s.rmn'include current controlfile

plus

archivelog format'/u02/app/oracle/backup/arch_bk1_%u%p%s.rmn' delete all input;全部備份完之後，删除備份過的舊歸檔日志

如果我們想使用預設通道預設配置備份一次資料庫，同時删除備份過的歸檔日志，那麼指令為

RMAN> backup ascompressed backupset full database include current controlfile plus archivelogdelete all input;

Starting backup at26-APR-13      備份時間

current logarchived

allocated channel:ORA_DISK_1     預設通道磁盤

channelORA_DISK_1: SID=140 device type=DISK

channelORA_DISK_1: starting compressed archived log backup set   先壓縮備份的歸檔日志

channel ORA_DISK_1:specifying archived log(s) in backup set      備份了73 74歸檔日志

input archived logthread=1 sequence=73 RECID=1 STAMP=813706084

input archived logthread=1 sequence=74 RECID=2 STAMP=813739820

channelORA_DISK_1: starting piece 1 at 26-APR-13                    啟動備份片

channelORA_DISK_1: finished piece 1 at 26-APR-13                    完成備份片

piece handle=/u02/app/oracle/backup/DB_01o81bpd_1_1tag=TAG20130426T065020 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01   備份完成用時1秒

channelORA_DISK_1: deleting archived log(s)               删除已備份的歸檔日志73 74

archived log filename=/u02/app/oracle/archdata/1_73_813654649.dbf RECID=1 STAMP=813706084

archived log filename=/u02/app/oracle/archdata/1_74_813654649.dbf RECID=2 STAMP=813739820

Finished backup at26-APR-13

Starting backup at26-APR-13

using channelORA_DISK_1

channelORA_DISK_1: starting compressed full datafile backup set   再壓縮備份資料檔案

channelORA_DISK_1: specifying datafile(s) in backup set        指定備份如下資料檔案

input datafilefile number=00001 name=/u02/app/oracle/oradata/LEO1/system01.dbf

input datafilefile number=00002 name=/u02/app/oracle/oradata/LEO1/sysaux01.dbf

input datafilefile number=00003 name=/u02/app/oracle/oradata/LEO1/undotbs01.dbf

input datafilefile number=00005 name=/u02/app/oracle/oradata/LEO1/leo1.dbf

input datafilefile number=00004 name=/u02/app/oracle/oradata/LEO1/users01.dbf

channelORA_DISK_1: starting piece 1 at 26-APR-13            啟動備份片

channelORA_DISK_1: finished piece 1 at 26-APR-13            完成備份片

piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1tag=TAG20130426T065022 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:01:45備份完成用時1分45秒

channelORA_DISK_1: starting compressed full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

includingcurrent control file inbackupset         這個是備份資料檔案的同時包含備份控制檔案

channelORA_DISK_1: starting piece 1 at 26-APR-13

channel ORA_DISK_1:finished piece 1 at 26-APR-13

piece handle=/u02/app/oracle/backup/DB_03o81bso_1_1tag=TAG20130426T065022 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01備份完成用時1秒

Finished backup at26-APR-13

Starting backup at26-APR-13

current logarchived

using channelORA_DISK_1

channelORA_DISK_1: starting compressed archived log backup set

channelORA_DISK_1: specifying archived log(s) in backup set

input archived logthread=1 sequence=75 RECID=3 STAMP=813739930  備份75歸檔日志

channel ORA_DISK_1:starting piece 1 at 26-APR-13

channelORA_DISK_1: finished piece 1 at 26-APR-13

piece handle=/u02/app/oracle/backup/DB_04o81bsq_1_1tag=TAG20130426T065210 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01     備份完成用時1秒

channelORA_DISK_1: deleting archived log(s)               删除已備份的歸檔日志75

archived log filename=/u02/app/oracle/archdata/1_75_813654649.dbf RECID=3 STAMP=813739930

Finished backup at26-APR-13

Starting ControlFile and SPFILE Autobackup at 26-APR-13  啟動控制檔案和參數檔案自動備份

piece handle=/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00comment=NONE

Finished ControlFile and SPFILE Autobackup at 26-APR-13  完成自動備份

在作業系統上都可以找到對應的備份集并且已經删除了備份過的舊歸檔日志

[[email protected]]$ pwd

/u02/app/oracle/backup

[[email protected]]$ ll

total 249468

drwxr-xr-x 2oracle oinstall      4096 Apr 26 06:52control

-rw-r-----1 oracle asmadmin   2790912 Apr 26 06:50DB_01o81bpd_1_1

-rw-r-----1 oracle asmadmin 251551744 Apr 26 06:52 DB_02o81bpf_1_1

-rw-r-----1 oracle asmadmin   1097728 Apr 26 06:52DB_03o81bso_1_1

-rw-r-----1 oracle asmadmin      7168 Apr 26 06:52DB_04o81bsq_1_1

[[email protected] backup]$ cd control/

[[email protected]]$ ll

total 9600

-rw-r-----1 oracle asmadmin 9830400 Apr 26 06:52 cf_c-1692458681-20130426-00

[[email protected]]$ pwd

/u02/app/oracle/archdata

[[email protected]]$ ll      歸檔日志全沒有了

total 0

新的歸檔日志是從76号開始，75号之前都已經備份并删除

[email protected]>archivelog list

Database logmode             Archive Mode

Automaticarchival             Enabled

Archivedestination           /u02/app/oracle/archdata

Oldest online logsequence      74

Next log sequenceto archive    76

Current logsequence          76

到此我們的備份準備已經完成，稍微休息一下：）坐車上班班

8）[email protected]>selectstatus from v$instance;         檢查資料庫狀态

STATUS

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

OPEN

[email protected]>showparameter control_files        我們檢查一下控制檔案個數

NAME                                 TYPE        VALUE

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

control_files                        string      /u02/app/oracle/oradata/LEO1/control01.ctl,

/u02/app/oracle/oradata/LEO1/control02.ctl

[[email protected]]$ ll                  作業系統上也是2個沒有問題

total 2618136

-rw-r----- 1oracle asmadmin   9748480 Apr 26 09:01control01.ctl

-rw-r----- 1oracle asmadmin   9748480 Apr 26 09:01control02.ctl

一般控制檔案丢失大多數都是被誤删除了，用rm指令删除control01.ctl檔案

[[email protected]]$ pwd

/u02/app/oracle/diag/rdbms/leo1/LEO1/trace

[[email protected] trace]$ tail -10falert_LEO1.log      實時監控告警日志看看有什麼變化

Fri Apr 2606:50:20 2013

Thread 1 advancedto log sequence 75 (LGWR switch)

  Current log# 3 seq# 75 mem# 0:/u02/app/oracle/oradata/LEO1/redo03.log

Archived Log entry2 added for thread 1 sequence 74 ID 0x64e13fb9 dest 1:

Fri Apr 2606:52:10 2013

ALTER SYSTEMARCHIVE LOG

Fri Apr 2606:52:10 2013

Thread 1 advancedto log sequence 76 (LGWR switch)

  Current log# 1 seq# 76 mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log

Archived Log entry3 added for thread 1 sequence 75 ID 0x64e13fb9 dest 1:

[[email protected]]$ rm control01.ctl            模拟control01檔案丢失的場景

[email protected]>createtablespace test datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10mautoextend off;

create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off

l       我們建立一個表空間，此時突然報錯

ERROR at line 1:

ORA-00210:cannot open the specified control file  不能打開指定的控制檔案

ORA-00202:control file: '/u02/app/oracle/oradata/LEO1/control01.ctl'   控制檔案丢失

ORA-27041:unable to open file                 無法打開這個檔案

Linux-x86_64Error: 2: No such file or directory     找不到這個檔案，好恐怖bless，趕緊看看alert日志

Additionalinformation: 3

Alert_LEO1.log日志内容

create tablespacetest datafile '/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off

ORA-210 signalledduring: create tablespace test datafile'/u02/app/oracle/oradata/LEO1/test01.dbf' size 10m autoextend off...

Fri Apr 2609:14:15 2013

Errors in file/u02/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_m000_7975.trc:

ORA-00210: cannotopen the specified control file

ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unableto open file

Linux-x86_64Error: 2: No such file or directory

Additionalinformation: 3

是不是和上面報的錯誤資訊一樣啊，由于是我們自己搞的鬼，是以我們明白是怎麼回事，如果在生産庫上就要首先檢視日志資訊進行分析啦，好了現在我們開始修複吧->start on

思路：我們首先要清楚oracle為了保證其穩定性，控制檔案都是多路複用的，如果使用dbca安裝10g預設有3個控制檔案11g有兩個，我們删除了其中一個，可能有的朋友會說，我們鏡像出來一個控制檔案不就好了麼，沒錯思路很正确，但前提是要關閉資料庫使所有的SCN号一緻，也有可能你會遇上無法立即關閉的情況，木有辦法隻能強制關閉了，

[email protected]>shutdownimmediate

ORA-00210: cannotopen the specified control file

ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'

ORA-27041: unableto open file

Linux-x86_64Error: 2: No such file or directory

Additionalinformation: 3

[email protected]>shutdownabort

ORACLE instanceshut down.

[[email protected]]$ cp control02.ctl control01.ctl  使用完好的控制檔案恢複被删除的控制檔案

[[email protected]]$ ll

total 2618136

-rw-r----- 1oracle oinstall   9748480 Apr 26 09:34control01.ctl

-rw-r----- 1oracle asmadmin   9748480 Apr 26 09:28control02.ctl

[email protected]>startupmount   啟動到mount狀态沒有報錯，說明我們恢複成功了

ORACLE instancestarted.

Total SystemGlobal Area  471830528 bytes

Fixed Size                  2214456 bytes

Variable Size             150996424 bytes

DatabaseBuffers          310378496 bytes

Redo Buffers                8241152 bytes

Database mounted.

[email protected]>selectcheckpoint_change# from v$database;  資料庫全局SCN号，放在控制檔案裡

CHECKPOINT_CHANGE#

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

            909922

[email protected]>selectname,checkpoint_change# from v$datafile;資料檔案SCN号，放在控制檔案裡

NAME                                       CHECKPOINT_CHANGE#

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

/u02/app/oracle/oradata/LEO1/system01.dbf        909922

/u02/app/oracle/oradata/LEO1/sysaux01.dbf        909922

/u02/app/oracle/oradata/LEO1/undotbs01.dbf       909922

/u02/app/oracle/oradata/LEO1/users01.dbf         909922

/u02/app/oracle/oradata/LEO1/leo1.dbf            909922

[email protected]>selectname,checkpoint_change# from v$datafile_header;資料檔案頭SCN号，放在資料檔案頭裡

NAME                                       CHECKPOINT_CHANGE#

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

/u02/app/oracle/oradata/LEO1/system01.dbf        909922

/u02/app/oracle/oradata/LEO1/sysaux01.dbf        909922

/u02/app/oracle/oradata/LEO1/undotbs01.dbf       909922

/u02/app/oracle/oradata/LEO1/users01.dbf         909922

/u02/app/oracle/oradata/LEO1/leo1.dbf            909922

[email protected]>selectname,last_change# from v$datafile;   資料檔案結束SCN号，放在控制檔案裡

NAME                                       LAST_CHANGE#

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

/u02/app/oracle/oradata/LEO1/system01.dbf

/u02/app/oracle/oradata/LEO1/sysaux01.dbf

/u02/app/oracle/oradata/LEO1/undotbs01.dbf

/u02/app/oracle/oradata/LEO1/users01.dbf

/u02/app/oracle/oradata/LEO1/leo1.dbf

這個LAST_CHANGE為NULL，我們要知道Oracle做不做執行個體恢複就是看這個SCN是否為NULL

如果資料庫非正常關閉值為NULL

如果資料庫正常關閉值為xxxxxx

特例：資料庫為open狀态時LAST_CHANGE也為NULL，但現在我們是mount狀态

[email protected]>selectstatus from v$instance;

STATUS

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

MOUNTED

我們在開打資料庫的一霎那就會啟動執行個體恢複，看alert日志即可

[email protected]>alterdatabase open;

Database altered.

Alert日志内容

Fri Apr 2609:50:08 2013

alter databaseopen                          打開資料庫

Beginningcrash recovery of 1 threads           進行執行個體恢複

parallel recovery started with 2 processes        啟動2個恢複程序

Started redo scan

Completed redoscan

read 30 KB redo, 31 data blocks need recovery

Started redoapplication at

Thread 1: logseq 76, block 23074

Recovery of OnlineRedo Log: Thread 1 Group 1 Seq 76 Reading mem 0從76号日志為起始位置

  Mem# 0:/u02/app/oracle/oradata/LEO1/redo01.log  應用redo日志進行恢複

Completed redoapplication of 0.02MB

Completed crashrecovery at

Thread 1: logseq 76, block 23134, scn 934394   一直應用到redo最後一個SCN号

31 data blocks read, 31 data blocks written,30 redo k-bytes read  恢複了31個資料塊，讀取了30K redo

Fri Apr 2609:50:08 2013

LGWR: STARTINGARCH PROCESSES           啟動歸檔程序進行歸檔

Fri Apr 2609:50:08 2013

ARC0 started withpid=22, OS id=8396

ARC0: Archivalstarted

LGWR: STARTINGARCH PROCESSES COMPLETE

ARC0: STARTINGARCH PROCESSES

Fri Apr 2609:50:09 2013

ARC1 started withpid=23, OS id=8400

Fri Apr 2609:50:09 2013

ARC2 started withpid=24, OS id=8404

ARC1: Archivalstarted

Fri Apr 2609:50:09 2013

ARC3 started withpid=25, OS id=8408

ARC2: Archivalstarted

ARC1: Becoming the'no FAL' ARCH

ARC1: Becoming the'no SRL' ARCH

ARC2: Becoming theheartbeat ARCH

Thread 1 advancedto log sequence 77 (thread open)

Thread 1 opened atlog sequence 77

  Current log# 2 seq# 77 mem# 0:/u02/app/oracle/oradata/LEO1/redo02.log

Successful open ofredo thread 1

MTTR advisory isdisabled because FAST_START_MTTR_TARGET is not set

Fri Apr 2609:50:09 2013

SMON: enablingcache recovery          SMON程序負責執行個體的恢複

Archived Log entry4 added for thread 1 sequence 76 ID 0x64e13fb9 dest 1:

ARC3: Archivalstarted

ARC0: STARTINGARCH PROCESSES COMPLETE

Successfullyonlined Undo Tablespace 2.

Verifying fileheader compatibility for 11g tablespace encryption..

Verifying 11g fileheader compatibility for tablespace encryption completed

SMON: enabling txrecovery

DatabaseCharacterset is ZHS16GBK

No ResourceManager plan active

replication_dependency_trackingturned off (no async multimaster replication found)

Startingbackground process QMNC

Fri Apr 2609:50:14 2013

QMNC started withpid=26, OS id=8412

Completed: alterdatabase open         到此我們完成資料庫的open動作，執行個體恢複完畢

Fri Apr 2609:50:18 2013

Startingbackground process CJQ0

Fri Apr 2609:50:18 2013

CJQ0 started withpid=30, OS id=8436

從alter日志的流程上我們就可以看出，oracle執行個體恢複的内容過程是什麼樣的，通過以上案例我們即了解了控制檔案的恢複過程又了解了資料庫執行個體的恢複過程，可謂一舉兩得，大家好好的消化消化，休息一下，該上班工作啦：）

9）下了班我們繼續，上次講到了，使用copy方式來恢複控制檔案，下面再講一種使用備份集來恢複控制檔案的方法。

【參】Books-> Backupand Recovery Reference ->RESTORE和RECOVER  參考官方文檔是個好習慣

[email protected]>shutdownimmediate                  我們先關閉資料庫

Database closed.

Databasedismounted.

ORACLE instanceshut down.

[[email protected]]$ rm -rf control01.ctl     删除控制檔案

[email protected]>startup

ORACLE instancestarted.

Total SystemGlobal Area  471830528 bytes

Fixed Size                  2214456 bytes

Variable Size             150996424 bytes

DatabaseBuffers          310378496 bytes

Redo Buffers                8241152 bytes

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

指定的控制檔案錯誤，檢查alert日志擷取更多資訊

[[email protected]]$ tail -20f alert_LEO1.log

ALTERDATABASE   MOUNT

ORA-00210: cannotopen the specified control file

ORA-00202: controlfile: '/u02/app/oracle/oradata/LEO1/control01.ctl'  這寫着1号控制檔案丢失

ORA-27037: unableto obtain file status

Linux-x86_64Error: 2: No such file or directory      找不到這個檔案

Additionalinformation: 3

ORA-205 signalledduring: ALTER DATABASE   MOUNT...

Fri Apr 2619:44:05 2013

Checker run found1 new persistent data failures

[email protected]>shutdownimmediate                  關庫

ORA-01507:database not mounted

ORACLE instanceshut down.

[[email protected]~]$ rman target sys/oracle        連結RMAN

Recovery Manager:Release 11.2.0.1.0 - Production on Fri Apr 26 19:49:00 2013

Copyright (c)1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected totarget database (not started)   庫沒有啟動

RMAN> startupnomount                 把庫啟動到nomount狀态

Oracle instancestarted

Total System GlobalArea     471830528 bytes

Fixed Size                     2214456 bytes

Variable Size                150996424 bytes

DatabaseBuffers             310378496 bytes

Redo Buffers                   8241152 bytes

有一個地方容易出錯，大家都喜歡用這條語句來恢複

RMAN> RESTORECONTROLFILE FROM AUTOBACKUP;

Starting restoreat 26-APR-13

using channelORA_DISK_1

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130426

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130425

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130424

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130423

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130422

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130421

channelORA_DISK_1: looking for AUTOBACKUP on day: 20130420

channelORA_DISK_1: no AUTOBACKUP in 7 days found

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of restore command at 04/26/2013 20:01:04

RMAN-06172: noAUTOBACKUP found or specified handle is not a valid copy or piece

報錯：說找不到指定的備份集

RMAN> show all;

RMAN configurationparameters for database with db_unique_name LEO1 are:

CONFIGURERETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUPOPTIMIZATION OFF; # default

CONFIGURE DEFAULTDEVICE TYPE TO DISK; # default

CONFIGURECONTROLFILE AUTOBACKUP OFF; # default

CONFIGURECONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

原來我們配置的控制檔案自動備份儲存目錄是不是失效啦，這是為什麼呢，原來rman中繼資料資訊是寫在controlfile中的，而現在控制檔案又損壞了不能打開，一個不能打開的檔案我們是不是讀不到裡面的内容啊，我們隻啟動到了nomount狀态隻讀取參數檔案資訊，是以我們在恢複控制檔案的時候指定一下“原來備份的儲存目錄”告訴rman從哪個路徑下可以找到備份集就可以了。

RMAN>restore controlfile from'/u02/app/oracle/backup/control/cf_c-1692458681-20130426-00';

Starting restoreat 26-APR-13

using channelORA_DISK_1

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:04

output filename=/u02/app/oracle/oradata/LEO1/control01.ctl

output filename=/u02/app/oracle/oradata/LEO1/control02.ctl

Finished restoreat 26-APR-13

[[email protected]]$ ll

total 2618136

-rw-r----- 1oracle asmadmin   9748480 Apr 26 20:17control01.ctl

-rw-r----- 1oracle asmadmin   9748480 Apr 26 20:17control02.ctl

我們一起恢複了所有的控制檔案

RMAN> alterdatabase mount;                  現在資料庫可以正常加載了對吧

database mounted

released channel:ORA_DISK_1

那我們可以alter database open來打開資料庫嗎，顯然是不行的，大家知道為什麼嗎？

你想想如果這個控制檔案是從10天之前的一個備份還原的與目前的資料庫實體結構能一緻嗎！

顯然是不可以的，我們隻有用備份在重新同步資料庫

RMAN> restoredatabase;              RMAN備份還原restore（複制）資料檔案

Starting restoreat 26-APR-13

allocated channel:ORA_DISK_1

channel ORA_DISK_1:SID=134 device type=DISK

channelORA_DISK_1: starting datafile backup set restore

channelORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1:restoring datafile 00001 to /u02/app/oracle/oradata/LEO1/system01.dbf

channelORA_DISK_1: restoring datafile 00002 to/u02/app/oracle/oradata/LEO1/sysaux01.dbf

channelORA_DISK_1: restoring datafile 00003 to/u02/app/oracle/oradata/LEO1/undotbs01.dbf

channelORA_DISK_1: restoring datafile 00004 to/u02/app/oracle/oradata/LEO1/users01.dbf

channelORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/LEO1/leo1.dbf

channelORA_DISK_1: reading from backup piece /u02/app/oracle/backup/DB_02o81bpf_1_1

channelORA_DISK_1: piece handle=/u02/app/oracle/backup/DB_02o81bpf_1_1tag=TAG20130426T065022

channelORA_DISK_1: restored backup piece 1

channelORA_DISK_1: restore complete, elapsed time: 00:02:36

Finished restoreat 26-APR-13

RMAN> recoverdatabase;               應用redo日志恢複recover（同步）資料庫

Starting recoverat 26-APR-13

using channelORA_DISK_1

starting mediarecovery

archived log forthread 1 with sequence 75 is already on disk as file/u02/app/oracle/oradata/LEO1/redo03.log

archived log forthread 1 with sequence 76 is already on disk as file/u02/app/oracle/oradata/LEO1/redo01.log

archived log forthread 1 with sequence 77 is already on disk as file/u02/app/oracle/oradata/LEO1/redo02.log

archived log filename=/u02/app/oracle/oradata/LEO1/redo03.log thread=1 sequence=75

archived log filename=/u02/app/oracle/oradata/LEO1/redo01.log thread=1 sequence=76

archived log filename=/u02/app/oracle/oradata/LEO1/redo02.log thread=1 sequence=77

mediarecovery complete, elapsed time: 00:00:14   這就是媒體恢複，要把資料檔案同步到損壞的前一刻

因為這是不完全恢複，是以我們不能用alter database open來打開資料庫

RMAN> alterdatabase open resetlogs;

database opened

[email protected]>selectstatus from v$instance;           資料庫已打開，可以正常使用

STATUS

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

OPEN

小結：因為我們進行了不完全恢複，恢複到之前的某一點，現在資料庫以這點為一個新的起點（相當是一個煥然一新的庫），resetlogs就是重置歸檔日志從1開始編碼，之前的歸檔全部無效。

[email protected]>altersystem switch logfile;            我們重新切換一次

System altered.

[[email protected]]$ cd archdata

[[email protected]]$ ll

total 14028

-rw-r-----1 oracle asmadmin   222720 Apr 26 21:051_1_813790699.dbf

-rw-r----- 1oracle asmadmin     5632 Apr 26 20:581_75_813654649.dbf

-rw-r----- 1oracle asmadmin 11844608 Apr 26 20:58 1_76_813654649.dbf

-rw-r----- 1oracle asmadmin  2284032 Apr 26 20:581_77_813654649.dbf

注意：歸檔日志編碼重置了，從1開始了，75 76 77歸檔日志全部無效了，為了不礙眼你可以删除掉。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/163177/viewspace-759531/，如需轉載，請注明出處，否則将追究法律責任。

轉載于:http://blog.itpub.net/163177/viewspace-759531/