Oracle 数据库灾难性环境下恢复实例

简介:

一、版本和数据库文件信息


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”

目录
相关文章
|
8天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
44 7
|
25天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解(3)
Oracle数据库基本概念理解(3)
18 2
|
8天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
35 5
|
25天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解(2)
Oracle数据库基本概念理解(2)
13 1
|
8天前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
32 7
|
Oracle 关系型数据库 网络协议
|
1月前
|
Oracle 关系型数据库 数据库
|
2月前
|
开发框架 Oracle 关系型数据库
ASP.NET实验室LIS系统源码 Oracle数据库
LIS是HIS的一个组成部分,通过与HIS的无缝连接可以共享HIS中的信息资源,使检验科能与门诊部、住院部、财务科和临床科室等全院各部门之间协同工作。 
35 4
|
1月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。