所谓灾难性是指磁盘故障或其它原因导致数据库相关的文件全部丢失或损坏无法进行修复,必须通过备份进行还原恢复;以下实验模拟丢失所有数据库文件,然后通过备份进行还原恢复。
版本和数据库文件信息
OracleDatabase11g 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 forLinux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> columnnameformat a50
SQL> selectfile#,status,namefromv$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> columnmember format a50
SQL> select*fromv$Logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE /u01/oradata/sydb/REDO01.LOG NO
2 ONLINE /u01/oradata/sydb/REDO02.LOG NO
SQL> select*fromv$controlfile;
STATUS NAMEIS_ BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------- --- ---------- --------------
/u01/oradata/sydb/control01.ctl NO16384 668
备份数据库
注意:备份数据库时如果配置了 configure exclude for tablespace tbsname 的表空间是不会进行备份的,恢复表空间备份configure exclude for tablespace tbsname clear;如果没有设置自动备份控制文件和参数文件,要在备份脚本最后手动添加备份控制文件脚本;设置自动备份控制文件和参数文件:
CONFIGURE CONTROLFILE AUTOBACKUPOn;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FORDEVICE TYPE DISKTO'/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 level0databaseplus archivelogdeleteinput
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 at29-MAY-15
currentlog archived
channel dev: starting archived log backup set
channel dev: specifying archived log(s) inbackupset
input archived log thread=1 sequence=17 RECID=1 STAMP=880994007
channel dev: starting piece 1 at29-MAY-15
channel dev2: starting archived log backup set
channel dev2: specifying archived log(s) inbackupset
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 at29-MAY-15
channel dev: finished piece 1 at29-MAY-15
piece handle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev: backup setcomplete, elapsedtime: 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 at29-MAY-15
piece handle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev2: backup setcomplete, elapsedtime: 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 at29-MAY-15
Starting backup at29-MAY-15
channel dev: starting incremental level0 datafile backupset
channel dev: specifying datafile(s) inbackupset
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 at29-MAY-15
channel dev2: starting incremental level0 datafile backupset
channel dev2: specifying datafile(s) inbackupset
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 at29-MAY-15
channel dev: finished piece 1 at29-MAY-15
piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
channel dev: backup setcomplete, elapsedtime: 00:00:35
channel dev2: finished piece 1 at29-MAY-15
piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
channel dev2: backup setcomplete, elapsedtime: 00:00:35
Finished backup at29-MAY-15
Starting backup at29-MAY-15
currentlog archived
channel dev: starting archived log backup set
channel dev: specifying archived log(s) inbackupset
input archived log thread=1 sequence=20 RECID=4 STAMP=880994354
channel dev: starting piece 1 at29-MAY-15
channel dev: finished piece 1 at29-MAY-15
piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
channel dev: backup setcomplete, elapsedtime: 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 at29-MAY-15
Starting Control File andSPFILE Autobackupat29-MAY-15
piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE
Finished Control File andSPFILE Autobackupat29-MAY-15
released channel: dev
released channel: dev2
通过备份日志可以知道哪些数据文件被备份,哪些没有被备份或没有备份成功,比如:发现较多数据块损坏、表空间被排除备份(exclude)、表空间被skip;关注这些信息非常重要,因为一旦灾难发生,它意味着你的数据库恢复成功率,也有助于对备份脚本的优化和调整。