所謂災難性是指磁盤故障或其它原因導致數據庫相關的文件全部丟失或損壞無法進行修復,必須通過備份進行還原恢復;以下實驗模擬丟失所有數據庫文件,然後通過備份進行還原恢復。
版本和數據庫文件信息
- 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;關注這些信息非常重要,因為一旦災難發生,它意味著你的數據庫恢復成功率,也有助於對備份腳本的優化和調整。
