ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 使用物理备份恢复SYSTEM表空间

使用物理备份恢复SYSTEM表空间

原创 Oracle 作者：Hoegh 时间：2015-07-11 10:06:54 0 删除 编辑
    只要存在有效的备份，恢复SYSTEM表空间数据文件丢失故障是比较容易的。这里演示的是最基本的使用物理备份恢复SYSTEM表空间丢失的方法。

1.环境准备

我们在Oracle11g中进行测试，数据库处于非归档状态。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> select * from v$version;

  4. BANNER
  5. --------------------------------------------------------------------------------
  6. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
  7. PL/SQL Release 11.2.0.3.0 - Production
  8. CORE 11.2.0.3.0 Production
  9. TNS for Linux: Version 11.2.0.3.0 - Production
  10. NLSRTL Version 11.2.0.3.0 - Production

  12. SQL>
  13. SQL> archive log list;
  14. Database log mode No Archive Mode
  15. Automatic archival Disabled
  16. Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
  17. Oldest online log sequence 6
  18. Current log sequence 8
  19. SQL>

2.打tar包，进行物理备份

首先要弄清两个概念：打包和压缩。打包是指将一大堆文件或目录变成一个总的文件；压缩则是将一个大的文件通过一些压缩算法变成一个小文件。linux下最常用的打包程序就是tar了，使用tar程序打出来的包我们常称为tar包，tar包文件的命令通常都是以.tar结尾的。生成tar包后，就可以用其它的程序来进行压缩。
我们使用tar命令将HOEGH数据库的物理文件打tar包，命名为HOEGH.tar.gz。注意，物理备份必须是在数据库关停阶段进行。

点击(此处)折叠或打开

  1. [oracle@hoegh oradata]$ tar -zcvf HOEGH.tar.gz HOEGH
  2. HOEGH/
  3. HOEGH/redo03.log
  4. HOEGH/temp01.dbf
  5. HOEGH/control01.ctl
  6. HOEGH/control02.ctl
  7. HOEGH/system01.dbf
  8. HOEGH/sysaux01.dbf
  9. HOEGH/users01.dbf
  10. HOEGH/undotbs01.dbf
  11. HOEGH/example01.dbf
  12. HOEGH/redo02.log
  13. HOEGH/redo01.log

3.启动数据库，删除system数据文件

下面，我们来模拟system数据文件丢失的故障场景。
首先，启动数据库。

点击(此处)折叠或打开

  1. SQL> startup
  2. ORACLE instance started.

  4. Total System Global Area 941600768 bytes
  5. Fixed Size 1348860 bytes
  6. Variable Size 515902212 bytes
  7. Database Buffers 419430400 bytes
  8. Redo Buffers 4919296 bytes
  9. Database mounted.
  10. Database opened.
  11. SQL>
接下来，删除system01.dbf数据文件。

点击(此处)折叠或打开

  1. [oracle@hoegh HOEGH]$ rm system01.dbf
  2. [oracle@hoegh HOEGH]$

4.重启数据库报错ORA-01157和ORA-01110

删除数据文件后，我们重启数据库，数据库在尝试启动到open状态时，由于找不到system表空间的数据文件，报错。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> shu immediate
  3. Database closed.
  4. Database dismounted.
  5. ORACLE instance shut down.
  6. SQL>
  7. SQL> startup
  8. ORACLE instance started.

  10. Total System Global Area 941600768 bytes
  11. Fixed Size 1348860 bytes
  12. Variable Size 515902212 bytes
  13. Database Buffers 419430400 bytes
  14. Redo Buffers 4919296 bytes
  15. Database mounted.
  16. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
  17. ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'


  20. SQL>
  21. SQL> select status from v$instance;

  23. STATUS
  24. ------------
  25. MOUNTED

  27. SQL>
我们看到，这个时候数据库处于mount状态。查看报警日志文件，我们可以更清晰的看到整个过程。

点击(此处)折叠或打开

  1. [oracle@hoegh trace]$ pwd
  2. /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace
  3. [oracle@hoegh trace]$
  4. [oracle@hoegh trace]$ tailf alert_HOEGH.log
  5. ……
  6. ALTER DATABASE MOUNT
  7. Successful mount of redo thread 1, with mount id 2106090167
  8. Database mounted in Exclusive Mode
  9. Lost write protection disabled
  10. Completed: ALTER DATABASE MOUNT
  11. Sat Jul 11 09:01:47 2015
  12. ALTER DATABASE OPEN
  13. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_dbw0_6016.trc:
  14. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
  15. ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
  16. ORA-27037: unable to obtain file status
  17. Linux Error: 2: No such file or directory
  18. Additional information: 3
  19. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:
  20. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
  21. ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
  22. ORA-1157 signalled during: ALTER DATABASE OPEN...

5.恢复数据文件

我们需要把之前的数据备份恢复到数据库当中，因此，首先我们就要解tar包，恢复之前备份的数据文件；然后，将备份的system数据文件拷贝到HOEGH数据文件目录当中。

点击(此处)折叠或打开

  1. [oracle@hoegh oradata]$ mkdir -p back
  2. [oracle@hoegh oradata]$
  3. [oracle@hoegh oradata]$ tar -zxvf HOEGH.tar.gz -C back/
  4. HOEGH/
  5. HOEGH/redo03.log
  6. HOEGH/temp01.dbf
  7. HOEGH/control01.ctl
  8. HOEGH/control02.ctl
  9. HOEGH/system01.dbf
  10. HOEGH/sysaux01.dbf
  11. HOEGH/users01.dbf
  12. HOEGH/undotbs01.dbf
  13. HOEGH/example01.dbf
  14. HOEGH/redo02.log
  15. HOEGH/redo01.log
  16. [oracle@hoegh oradata]$
  17. [oracle@hoegh oradata]$ cp back/HOEGH/system01.dbf HOEGH/
  18. [oracle@hoegh oradata]$

6.恢复数据库

首先，尝试使用alter database open;命令打开数据库，我们看到系统提示需要进行介质恢复。
接下来，使用recover database;命令恢复数据库；
最后，再次使用alter database open;命令打开数据库。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> alter database open;
  3. alter database open
  4. *
  5. ERROR at line 1:
  6. ORA-01113: file 1 needs media recovery
  7. ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'


  10. SQL>
  11. SQL> recover database;
  12. Media recovery complete.
  13. SQL>
  14. SQL> alter database open;

  16. Database altered.

  18. SQL>
  19. SQL> select status from v$instance;

  21. STATUS
  22. ------------
  23. OPEN

  25. SQL>
此时我们看到数据库已经处于open状态了，至此我们成功地使用物理备份恢复了之前“丢失”的system数据文件。通过alert报警日志我们再来看一下介质恢复以及打开数据库的整个过程。

点击(此处)折叠或打开

  1. Sat Jul 11 09:02:46 2015
  2. alter database open
  3. Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:
  4. ORA-01113: file 1 needs media recovery
  5. ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'
  6. ORA-1113 signalled during: alter database open...
  7. ALTER DATABASE RECOVER database
  8. Media Recovery Start
  9.  started logmerger process
  10. Sat Jul 11 09:02:53 2015
  11.  Recovering data file 1 from a fuzzy backup. It might be an online
  12. backup taken without entering the begin backup command.
  13. Parallel Media Recovery started with 2 slaves
  14. Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
  15.   Mem# 0: /u01/app/oracle/oradata/HOEGH/redo01.log
  16. Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
  17.   Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log
  18. Media Recovery Complete (HOEGH)
  19. Completed: ALTER DATABASE RECOVER database
  20. Sat Jul 11 09:03:23 2015
  21. alter database open
  22. Beginning crash recovery of 1 threads
  23.  parallel recovery started with 2 processes
  24. Started redo scan
  25. Completed redo scan
  26.  read 0 KB redo, 0 data blocks need recovery
  27. Started redo application at
  28.  Thread 1: logseq 8, block 878, scn 919739
  29. Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
  30.   Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log
  31. Completed redo application of 0.00MB
  32. Completed crash recovery at
  33.  Thread 1: logseq 8, block 878, scn 939740
  34.  0 data blocks read, 0 data blocks written, 0 redo k-bytes read
  35. Sat Jul 11 09:03:24 2015
  36. Thread 1 advanced to log sequence 9 (thread open)
  37. Thread 1 opened at log sequence 9
  38.   Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/HOEGH/redo03.log
  39. Successful open of redo thread 1
  40. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
  41. Sat Jul 11 09:03:24 2015
  42. SMON: enabling cache recovery
  43. [6135] Successfully onlined Undo Tablespace 2.
  44. Undo initialization finished serial:0 start:1328894 end:1328914 diff:20 (0 seconds)
  45. Verifying file header compatibility for 11g tablespace encryption..
  46. Verifying 11g file header compatibility for tablespace encryption completed
  47. SMON: enabling tx recovery
  48. Database Characterset is AL32UTF8
  49. No Resource Manager plan active
  50. replication_dependency_tracking turned off (no async multimaster replication found)
  51. Starting background process QMNC
  52. Sat Jul 11 09:03:24 2015
  53. QMNC started with pid=22, OS id=6188
  54. Completed: alter database open
从报警日志我们看到，数据库通过redo重做日志文件完成了介质恢复；然后，检查redo、undo等正常后，顺利打开数据库。

备份第一

   备份是系统中需要考虑的最重要的事项，虽然它在系统的整个规划、开发和测试过程中甚至占不到1%，看似不太重要且默默无闻的工作只有到恢复的时候才能真正体现出其重要性，任何数据的丢失与长时间的数据down机,都是不可以被接受的。当然，仅仅依靠物理备份做不到这一点，RMAN的功能更为强大。


hoegh
15.07.11
-- The End --







来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30162081/viewspace-1729512/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 误删重做日志文件组导致启动数据库报错ORA-03113
下一篇： Oracle11g维护分区概述
请登录后发表评论 登录
全部评论
Hoegh

注册时间：2015-03-13

  • 博文量
    132
  • 访问量
    2219942

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593