所謂災難性是指磁盤故障或其它原因導致數據庫相關的文件全部丟失或損壞無法進行修復,必須通過備份進行還原恢復;以下實驗模擬丟失所有數據庫文件,然後通過備份進行還原恢復。
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> column name format a50
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/oradata/sydb/system01.dbf
2 ONLINE /u01/oradata/sydb/sysaux01.dbf
3 ONLINE /u01/oradata/sydb/undotbs01.dbf
4 ONLINE /u01/oradata/sydb/users01.dbf
5 ONLINE /u01/oradata/sydb/tbs01.dbf
SQL> column member format a50
SQL> select * from v$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/oradata/sydb/REDO01.LOG NO
2 ONLINE /u01/oradata/sydb/REDO02.LOG NO
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
/u01/oradata/sydb/control01.ctl NO 16384 668
注意:備份數據庫時如果配置了 configure exclude for tablespace tbsname 的表空間是不會進行備份的,恢復表空間備份configure exclude for tablespace tbsname clear;如果沒有設置自動備份控制文件和參數文件,要在備份腳本最後手動添加備份控制文件腳本;設置自動備份控制文件和參數文件:
CONFIGURE CONTROLFILE AUTOBACKUP On;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F_%d_controlfile.bkp';
控制文件名格式必須包含%F,%F的格式為:c-IIIIIIIIII-YYYYMMDD-QQ,IIIIIIIIII(10位)代表DBID發生災難時可以通過它知道數據庫ID,YYYYMMDD代表自動備份時的timestamp,QQ代表是十六進制的序列號,起始值為:00,最大值為:FF。 開始備份數據
run
{
allocate channel dev type disk;
allocate channel dev2 type disk;
backup incremental level 0 database plus archivelog delete input
tag 'sydb_incr_level0'
format '/u01/backup/%d_%s_%U';
release channel dev;
release channel dev2;
}
allocated channel: dev
channel dev: SID=181 device type=DISK
allocated channel: dev2
channel dev2: SID=18 device type=DISK
Starting backup at 29-MAY-15
current log archived
channel dev: starting archived log backup set
channel dev: specifying archived log(s) in backup set
input archived log thread=1 sequence=17 RECID=1 STAMP=880994007
channel dev: starting piece 1 at 29-MAY-15
channel dev2: starting archived log backup set
channel dev2: specifying archived log(s) in backup set
input archived log thread=1 sequence=18 RECID=2 STAMP=880994016
input archived log thread=1 sequence=19 RECID=3 STAMP=880994311
channel dev2: starting piece 1 at 29-MAY-15
channel dev: finished piece 1 at 29-MAY-15
piece handle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev: backup set complete, elapsed time: 00:00:07
channel dev: deleting archived log(s)
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=880994007
channel dev2: finished piece 1 at 29-MAY-15
piece handle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev2: backup set complete, elapsed time: 00:00:08
channel dev2: deleting archived log(s)
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=880994016
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=880994311
Finished backup at 29-MAY-15
Starting backup at 29-MAY-15
channel dev: starting incremental level 0 datafile backup set
channel dev: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/sydb/system01.dbf
input datafile file number=00004 name=/u01/oradata/sydb/users01.dbf
input datafile file number=00005 name=/u01/oradata/sydb/tbs01.dbf
channel dev: starting piece 1 at 29-MAY-15
channel dev2: starting incremental level 0 datafile backup set
channel dev2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbf
input datafile file number=00002 name=/u01/oradata/sydb/sysaux01.dbf
channel dev2: starting piece 1 at 29-MAY-15
channel dev: finished piece 1 at 29-MAY-15
piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
channel dev: backup set complete, elapsed time: 00:00:35
channel dev2: finished piece 1 at 29-MAY-15
piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
channel dev2: backup set complete, elapsed time: 00:00:35
Finished backup at 29-MAY-15
Starting backup at 29-MAY-15
current log archived
channel dev: starting archived log backup set
channel dev: specifying archived log(s) in backup set
input archived log thread=1 sequence=20 RECID=4 STAMP=880994354
channel dev: starting piece 1 at 29-MAY-15
channel dev: finished piece 1 at 29-MAY-15
piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev: backup set complete, elapsed time: 00:00:01
channel dev: deleting archived log(s)
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=880994354
Finished backup at 29-MAY-15
Starting Control File and SPFILE Autobackup at 29-MAY-15
piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 29-MAY-15
released channel: dev
released channel: dev2
通過備份日志可以知道哪些數據文件被備份,哪些沒有被備份或沒有備份成功,比如:發現較多數據塊損壞、表空間被排除備份(exclude)、表空間被skip;關注這些信息非常重要,因為一旦災難發生,它意味著你的數據庫恢復成功率,也有助於對備份腳本的優化和調整。
驗證可恢復性可以發現一些忽略的問題,及時處理;
RMAN> restore database validate;
Starting restore at 29-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=181 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 29-MAY-15
rm /u01/oradata/sydb/system01.dbf
rm /u01/oradata/sydb/sysaux01.dbf
rm /u01/oradata/sydb/undotbs01.dbf
rm /u01/oradata/sydb/tbs01.dbf
rm /u01/oradata/sydb/control01.ctl
rm /u01/oradata/sydb/REDO01.LOG
rm /u01/oradata/sydb/REDO02.LOG
rm /u01/app/product/11.2.3/db_1/dbs/spfilesydb.ora
數據庫啟動時會先查找spfile,然後在找spfile.ora,再找文件initpfile,如果這些文件都沒有找到報錯並停止啟動;so 先創建一個基本的init pfile;
$ vim /u01/app/product/11.2.3/db_1/dbs/initsydb.ora
db_name='sydb'
memory_target=200m
control_files='/u01/oradata/sydb/control01.ctl'
db_block_size=32768
如果你的數據塊大小為非8kb,你又想在恢復控制文件後完成其它操作,如mount控制文件,則你必須在init 文件中指定數據塊大小;
SQL> startup nomount
ORACLE instance started.
Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 109053192 bytes
Database Buffers 92274688 bytes
Redo Buffers 5214208 bytes
$ rman target /
RMAN> restore spfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';
Starting restore at 29-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/c-3634177744-20150529-00_control.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 29-MAY-15
RMAN> restore controlfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';
Starting restore at 29-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oradata/sydb/control01.ctl
Finished restore at 29-MAY-15
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
注:如果目錄名發生改變或文件發生位置移動,先使用 catalog start with '/dir' 將文件catalog;
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Incr 0 180.53M DISK 00:00:29 29-MAY-15
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839
Piece Name: /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/sysaux01.dbf
3 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Incr 0 380.94M DISK 00:00:29 29-MAY-15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839
Piece Name: /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/system01.dbf
4 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/users01.dbf
5 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/tbs01.dbf
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 45.49M DISK 00:00:04 29-MAY-15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
Piece Name: /u01/backup/SYDB_1_01q85q07_1_1
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 17 427739 29-MAY-15 436110 29-MAY-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
2 43.37M DISK 00:00:04 29-MAY-15
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
Piece Name: /u01/backup/SYDB_2_02q85q07_1_1
List of Archived Logs in backup set 2
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 18 436110 29-MAY-15 436484 29-MAY-15
1 19 436484 29-MAY-15 436643 29-MAY-15
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5 90.00K DISK 00:00:00 29-MAY-15
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
Piece Name: /u01/backup/SYDB_5_05q85q1i_1_1
List of Archived Logs in backup set 5
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 20 436643 29-MAY-15 436756 29-MAY-15
從上面的信息中可以看出最大可恢復到的日志序列為20,恢復時設置until sequence 21,數據庫即可應用所有的歸檔日志;
RMAN> restore database until sequence 21;
Starting restore at 29-MAY-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/sydb/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/sydb/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1
channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 29-MAY-15
RMAN> recover database until sequence 21;
Starting recover at 29-MAY-15
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_1
channel ORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-MAY-15
使用resetlogs 方式打開數據庫
SYS@sydb>select file#,d.name as "FILE_NAME",t.name as "TABLESPACE_NAME",status,enabled,checkpoint_change#,checkpoint_time,(bytes/1024/1024)"BYTES_MB",blocks,
2 (create_bytes/1024/1024)create_bytes_mb,block_size
3 from v$datafile d left join v$tablespace t
4 on d.ts#=t.ts#;
FILE# FILE_NAME TABLESPACE_NAME STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN BYTES_MB BLOCKS CREATE_BYTES_MB BLOCK_SIZE
------------------------------ ------- ---------- ------------------ --------- ---------- ---------- --------------- ----------
1 /u01/oradata/sydb/system01.dbf SYSTEM SYSTEM READ WRITE 436756 29-MAY-15 400.8125 12826 100 32768
2 /u01/oradata/sydb/sysaux01.dbf SYSAUX ONLINE READ WRITE 436756 29-MAY-15 227.6875 7286 100 32768
3 /u01/oradata/sydb/undotbs01.dbf UNDOTBS01 ONLINE READ WRITE 436756 29-MAY-15 310 9920 100 32768
4 /u01/oradata/sydb/users01.dbf USERS ONLINE READ WRITE 436756 29-MAY-15 100 3200 100 32768
5 /u01/oradata/sydb/tbs01.dbf TBS01 ONLINE READ WRITE 436756 29-MAY-15 98 3136 10 32768
Elapsed: 00:00:00.02
SYS@sydb>alter database open resetlogs;
Database altered.
Elapsed: 00:00:07.41
任何時候數據庫的還原和恢復因環境和錯誤的不一而不同,認真有效的閱讀文檔,理解工作原理,然後在加上重復的實驗方能起到良好的效果,實驗本身是動手實踐的步驟,但同時也是理論實踐結合的強有效方式。
--The end(2015-05-30)
