一、版本和数据库文件信息
1.Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2.PL/SQL Release 11.2.0.3.0 - Production
3.CORE 11.2.0.3.0 Production
4.TNS for Linux: Version 11.2.0.3.0 - Production
5.NLSRTL Version 11.2.0.3.0 - Production
6.
7.SQL> column name format a50
8.SQL> select file#,status,name from v$datafile;
9.
10.FILE# STATUS NAME
11.---------- ------- ------------------
12.1 SYSTEM /u01/oradata/sydb/system01.dbf
13.2 ONLINE /u01/oradata/sydb/sysaux01.dbf
14.3 ONLINE /u01/oradata/sydb/undotbs01.dbf
15.4 ONLINE /u01/oradata/sydb/users01.dbf
16.5 ONLINE /u01/oradata/sydb/tbs01.dbf
17.
18.SQL> column member format a50
19.SQL> select * from v$Logfile;
20.
21. GROUP# STATUS TYPE MEMBER IS_
22.----------------
23.1 ONLINE /u01/oradata/sydb/REDO01.LOG NO
24.2 ONLINE /u01/oradata/sydb/REDO02.LOG NO
25.
26.SQL> select * from v$controlfile;
27.
28.STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
29.---------------------
30./u01/oradata/sydb/control01.ctl NO 16384 668
二、备份数据库
注意:备份数据库时如果配置了 configure exclude fortablespace tbsname 的表空间是不会进行备份的,恢复表空间备份configure exclude for tablespacetbsname clear;如果没有设置自动备份控制文件和参数文件,要在备份脚本最后手动添加备份控制文件脚本;设置自动备份控制文件和参数文件:
1.CONFIGURE CONTROLFILE AUTOBACKUP On;
2.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。开始备份数据
1. run
2. {
3. allocate channel dev type disk;
4. allocate channel dev2 type disk;
5. backup incremental level 0 database plus archivelog delete input
6. tag 'sydb_incr_level0'
7. format '/u01/backup/%d_%s_%U';
8. release channel dev;
9. release channel dev2;
10. }
11.
12.allocated channel: dev
13.channel dev: SID=181 device type=DISK
14.
15.allocated channel: dev2
16.channel dev2: SID=18 device type=DISK
17.
18.
19.Starting backup at 29-MAY-15
20.current log archived
21.channel dev: starting archived log backup set
22.channel dev: specifying archived log(s) in backup set
23.input archived log thread=1 sequence=17 RECID=1 STAMP=880994007
24.channel dev: starting piece 1 at 29-MAY-15
25.channel dev2: starting archived log backup set
26.channel dev2: specifying archived log(s) in backup set
27.input archived log thread=1 sequence=18 RECID=2 STAMP=880994016
28.input archived log thread=1 sequence=19 RECID=3 STAMP=880994311
29.channel dev2: starting piece 1 at 29-MAY-15
30.channel dev: finished piece 1 at 29-MAY-15
31.piece handle=/u01/backup/SYDB_1_01q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
32.channel dev: backup set complete, elapsed time: 00:00:07
33.channel dev: deleting archived log(s)
34.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_17_880905808.dbf RECID=1 STAMP=880994007
35.channel dev2: finished piece 1 at 29-MAY-15
36.piece handle=/u01/backup/SYDB_2_02q85q07_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
37.channel dev2: backup set complete, elapsed time: 00:00:08
38.channel dev2: deleting archived log(s)
39.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_18_880905808.dbf RECID=2 STAMP=880994016
40.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_19_880905808.dbf RECID=3 STAMP=880994311
41.Finished backup at 29-MAY-15
42.
43.Starting backup at 29-MAY-15
44.channel dev: starting incremental level 0 datafile backup set
45.channel dev: specifying datafile(s) in backup set
46.input datafile file number=00001 name=/u01/oradata/sydb/system01.dbf
47.input datafile file number=00004 name=/u01/oradata/sydb/users01.dbf
48.input datafile file number=00005 name=/u01/oradata/sydb/tbs01.dbf
49.channel dev: starting piece 1 at 29-MAY-15
50.channel dev2: starting incremental level 0 datafile backup set
51.channel dev2: specifying datafile(s) in backup set
52.input datafile file number=00003 name=/u01/oradata/sydb/undotbs01.dbf
53.input datafile file number=00002 name=/u01/oradata/sydb/sysaux01.dbf
54.channel dev2: starting piece 1 at 29-MAY-15
55.channel dev: finished piece 1 at 29-MAY-15
56.piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
57.channel dev: backup set complete, elapsed time: 00:00:35
58.channel dev2: finished piece 1 at 29-MAY-15
59.piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839 comment=NONE
60.channel dev2: backup set complete, elapsed time: 00:00:35
61.Finished backup at 29-MAY-15
62.
63.Starting backup at 29-MAY-15
64.current log archived
65.channel dev: starting archived log backup set
66.channel dev: specifying archived log(s) in backup set
67.input archived log thread=1 sequence=20 RECID=4 STAMP=880994354
68.channel dev: starting piece 1 at 29-MAY-15
69.channel dev: finished piece 1 at 29-MAY-15
70.piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0 comment=NONE
71.channel dev: backup set complete, elapsed time: 00:00:01
72.channel dev: deleting archived log(s)
73.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf RECID=4 STAMP=880994354
74.Finished backup at 29-MAY-15
75.
76.Starting Control File and SPFILE Autobackup at 29-MAY-15
77.piece handle=/u01/backup/c-3634177744-20150529-00_control.bkp comment=NONE
78.Finished Control File and SPFILE Autobackup at 29-MAY-15
79.
80.released channel: dev
81.
82.released channel: dev2
通过备份日志可以知道哪些数据文件被备份,哪些没有被备份或没有备份成功,比如:发现较多数据块损坏、表空间被排除备份(exclude)、表空间被skip;关注这些信息非常重要,因为一旦灾难发生,它意味着你的数据库恢复成功率,也有助于对备份脚本的优化和调整。
三、验证数据库可恢复性
验证可恢复性可以发现一些忽略的问题,及时处理;
1.rm /u01/oradata/sydb/system01.dbf
2.rm /u01/oradata/sydb/sysaux01.dbf
3.rm /u01/oradata/sydb/undotbs01.dbf
4.rm /u01/oradata/sydb/tbs01.dbf
5.rm /u01/oradata/sydb/control01.ctl
6.rm /u01/oradata/sydb/REDO01.LOG
7.rm /u01/oradata/sydb/REDO02.LOG
8.rm /u01/app/product/11.2.3/db_1/dbs/spfilesydb.ora
四、数据库恢复
恢复参数文件和控制文件
数据库启动时会先查找spfile,然后在找spfile.ora,再找文件initpfile,如果这些文件都没有找到报错并停止启动;so 先创建一个基本的init pfile;
1.$ vim /u01/app/product/11.2.3/db_1/dbs/initsydb.ora
2.db_name='sydb'
3.memory_target=200m
4.control_files='/u01/oradata/sydb/control01.ctl'
5.db_block_size=32768
如果你的数据块大小为非8kb,你又想在恢复控制文件后完成其它操作,如mount控制文件,则你必须在init 文件中指定数据块大小;
1.SQL> startup nomount
2.ORACLE instance started.
3.
4.Total System Global Area 208769024 bytes
5.Fixed Size 2226936 bytes
6.Variable Size 109053192 bytes
7.Database Buffers 92274688 bytes
8.Redo Buffers 5214208 bytes
9.
10.$ rman target /
11.RMAN> restore spfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';
12.
13.Starting restore at 29-MAY-15
14.using channel ORA_DISK_1
15.
16.channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/c-3634177744-20150529-00_control.bkp
17.channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
18.Finished restore at 29-MAY-15
19.
20.RMAN> restore controlfile from '/u01/backup/c-3634177744-20150529-00_control.bkp';
21.
22.Starting restore at 29-MAY-15
23.using channel ORA_DISK_1
24.
25.channel ORA_DISK_1: restoring control file
26.channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
27.output file name=/u01/oradata/sydb/control01.ctl
28.Finished restore at 29-MAY-15
29.RMAN> alter database mount;
30.
31.database mounted
32.released channel: ORA_DISK_1
查看备份文件和确定可恢复的最大归档日志序列
注:如果目录名发生改变或文件发生位置移动,先使用 catalog start with '/dir' 将文件catalog;
1.RMAN> list backup of database;
2.
3.
4.List of Backup Sets
5.===================
6.
7.
8.BS Key Type LV Size Device Type Elapsed Time Completion Time
9.------- ---- -- ---------- -----------
10.3 Incr 0 180.53M DISK 00:00:29 29-MAY-15
11.BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839
12.Piece Name:/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1
13. List of Datafiles in backup set 3
14. File LV Type Ckp SCN Ckp Time Name
15. ---- -- ---- ---------- ---------
16.2 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/sysaux01.dbf
17.3 0 Incr 436655 29-MAY-15 /u01/oradata/sydb/undotbs01.dbf
18.
19.BS Key Type LV Size Device Type Elapsed Time Completion Time
20.------- ---- -- ---------- -----------
21.4 Incr 0 380.94M DISK 00:00:29 29-MAY-15
22.BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20150529T163839
23.Piece Name: /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1
24.List of Datafiles in backup set 4
25.File LV Type Ckp SCN Ckp Time Name
26. ---- -- ---- ---------- ---------
27.1 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/system01.dbf
28.4 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/users01.dbf
29.5 0 Incr 436654 29-MAY-15 /u01/oradata/sydb/tbs01.dbf
30.
31.RMAN> list backup of archivelog all;
32.
33.
34.List of Backup Sets
35.===================
36.
37.
38.BS Key Size Device Type Elapsed Time Completion Time
39.------- ---------- -----------
40.1 45.49M DISK 00:00:04 29-MAY-15
41.BP Key: 1 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
42.Piece Name: /u01/backup/SYDB_1_01q85q07_1_1
43.
44. List of Archived Logs in backup set 1
45. Thrd Seq Low SCN Low Time Next SCN Next Time
46. ---- ------- ---------- --------
47.1 17 427739 29-MAY-15 436110 29-MAY-15
48.
49.BS Key Size Device Type Elapsed Time Completion Time
50.------- ---------- -----------
51.2 43.37M DISK 00:00:04 29-MAY-15
52.BP Key: 2 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
53.Piece Name: /u01/backup/SYDB_2_02q85q07_1_1
54.
55. List of Archived Logs in backup set 2
56. Thrd Seq Low SCN Low Time Next SCN Next Time
57. ---- ------- ---------- -------
58.1 18 436110 29-MAY-15 436484 29-MAY-15
59.1 19 436484 29-MAY-15 436643 29-MAY-15
60.
61.BS Key Size Device Type Elapsed Time Completion Time
62.------- ---------- ----------- ---
63.5 90.00K DISK 00:00:00 29-MAY-15
64.BP Key: 5 Status: AVAILABLE Compressed: NO Tag: SYDB_INCR_LEVEL0
65.Piece Name: /u01/backup/SYDB_5_05q85q1i_1_1
66.
67.List of Archived Logs in backup set 5
68.Thrd Seq Low SCN Low Time Next SCN Next Time
69. ---- ------- ---------- ---------
70.1 20 436643 29-MAY-15 436756 29-MAY-15
从上面的信息中可以看出最大可恢复到的日志序列为20,恢复时设置until sequence 21,数据库即可应用所有的归档日志;
1.RMAN> restore database until sequence 21;
2.
3.Starting restore at 29-MAY-15
4.using channel ORA_DISK_1
5.
6.channel ORA_DISK_1: starting datafile backup set restore
7.channel ORA_DISK_1: specifying datafile(s) to restore from backup set
8.channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/sydb/system01.dbf
9.channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/sydb/users01.dbf
10.channel ORA_DISK_1: restoring datafile 00005 to /u01/oradata/sydb/tbs01.dbf
11.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1
12.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/03q85q0f_1_1 tag=TAG20150529T163839
13.channel ORA_DISK_1: restored backup piece 1
14.channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
15.channel ORA_DISK_1: starting datafile backup set restore
16.channel ORA_DISK_1: specifying datafile(s) to restore from backup set
17.channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/sydb/sysaux01.dbf
18.channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/sydb/undotbs01.dbf
19.channel ORA_DISK_1: reading from backup piece /u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1
20.channel ORA_DISK_1: piece handle=/u01/app/product/11.2.3/db_1/dbs/04q85q0f_1_1 tag=TAG20150529T163839
21.channel ORA_DISK_1: restored backup piece 1
22.channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
23.Finished restore at 29-MAY-15
24.
25.RMAN> recover database until sequence 21;
26.
27.Starting recover at 29-MAY-15
28.using channel ORA_DISK_1
29.
30.starting media recovery
31.
32.channel ORA_DISK_1: starting archived log restore to default destination
33.channel ORA_DISK_1: restoring archived log
34.archived log thread=1 sequence=20
35.channel ORA_DISK_1: reading from backup piece /u01/backup/SYDB_5_05q85q1i_1_1
36.channel ORA_DISK_1: piece handle=/u01/backup/SYDB_5_05q85q1i_1_1 tag=SYDB_INCR_LEVEL0
37.channel ORA_DISK_1: restored backup piece 1
38.channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
39.archived log file name=/u01/app/product/11.2.3/db_1/dbs/arch1_20_880905808.dbf thread=1 sequence=20
40.media recovery complete, elapsed time: 00:00:01
41.Finished recover at 29-MAY-15
使用resetlogs 方式打开数据库
1.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.2(create_bytes/1024/1024)create_bytes_mb,block_size
3.3 from v$datafile d left join v$tablespace t
4.4 on d.ts#=t.ts#;
5.
6.FILE# FILE_NAME TABLESPACE_NAME STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOIN
BYTES_MB BLOCKS CREATE_BYTES_MB BLOCK_SIZE
7.------------------------------ -------
8.1 /u01/oradata/sydb/system01.dbf SYSTEM SYSTEM READ WRITE 436756 29-MAY-15 400.8125 12826 100 32768
9.2 /u01/oradata/sydb/sysaux01.dbf SYSAUX ONLINE READ WRITE 436756 29-MAY-15 227.6875 7286 100 32768
10.3 /u01/oradata/sydb/undotbs01.dbf UNDOTBS01 ONLINE READ WRITE 436756 29-MAY-15 310 9920 100 32768
11.4 /u01/oradata/sydb/users01.dbf USERS ONLINE READ WRITE 436756 29-MAY-15 100 3200 100 32768
12.5 /u01/oradata/sydb/tbs01.dbf TBS01 ONLINE READ WRITE 436756 29-MAY-15 98 3136 10 32768
13.
14.Elapsed: 00:00:00.02
15.SYS@sydb>alter database open resetlogs;
16.
17.Database altered.
18.
19.Elapsed: 00:00:07.41
五、总结
任何时候数据库的还原和恢复因环境和错误的不一而不同,认真有效的阅读文档,理解工作原理,然后在加上重复的实验方能起到良好的效果,实验本身是动手实践的步骤,但同时也是理论实践结合的强有效方式。
本文来自云栖社区合作伙伴“DBGEEK”