ITPub博客

首页 > 数据库 > Oracle > Oracle 11g重建控制文件——如何获取创建控制文件脚本

Oracle 11g重建控制文件——如何获取创建控制文件脚本

原创 Oracle 作者:Hoegh 时间:2015-05-30 18:52:23 0 删除 编辑
      控制文件对于数据库来说是非常重要,在进行数据恢复时通常是必不可少的;丢失控制文件并不是致命的,但是会使恢复变得困难很多。因此,dba除了多路保存控制文件外,还需要备份控制文件。
     备份控制文件也有多种办法,例如cp拷贝、rman自动备份等,今天给大家介绍如何获取创建控制文件的脚本,这样数据库一旦出现意外,即使找不到可用的备份控制文件,也能通过重建控制文件来启动数据库。
     Oracle提供了如下命令来实现这个功能:
      alter database backup controlfile to trace;

1.环境准备

我们在Oracle11g中进行测试。

点击(此处)折叠或打开

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

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

  10. SQL>

2.生成trace文件

打开数据库,执行‘alter database backup controlfile to trace;’语句来生成trace文件。

点击(此处)折叠或打开

  1. [oracle@hoegh ~]$ sqlplus / as sysdba

  2. SQL*Plus: Release 11.2.0.3.0 Production on Sat May 30 12:31:02 2015

  3. Copyright (c) 1982, 2011, Oracle. All rights reserved.

  4. Connected to an idle instance.

  5. SQL>
  6. SQL> startup
  7. ORACLE instance started.

  8. Total System Global Area 941600768 bytes
  9. Fixed Size 1348860 bytes
  10. Variable Size 515902212 bytes
  11. Database Buffers 419430400 bytes
  12. Redo Buffers 4919296 bytes
  13. Database mounted.
  14. Database opened.
  15. SQL>
  16. SQL>
  17. SQL> alter database backup controlfile to trace;

  18. Database altered.

  19. SQL>

3.查找trace文件路径

trace文件的路径可以通过一个多表查询来获得,其中涉及v$processv、$sessionv和$mystat等三个动态视图。

点击(此处)折叠或打开

  1. SQL>
  2. SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

  3. TRACEFILE
  4. --------------------------------------------------------------------------------
  5. /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc

  6. SQL>

4.查看trace文件

通过cat命令查看trace文件,内容如下:

点击(此处)折叠或打开

  1. Trace file /u01/app/oracle/diag/rdbms/hoegh/HOEGH/trace/HOEGH_ora_4831.trc
  2. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
  3. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  4. ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
  5. System name: Linux
  6. Node name: hoegh
  7. Release: 2.6.18-164.el5PAE
  8. Version: #1 SMP Thu Sep 3 02:28:20 EDT 2009
  9. Machine: i686
  10. VM name: VMWare Version: 6
  11. Instance name: HOEGH
  12. Redo thread mounted by this instance: 1
  13. Oracle process number: 19
  14. Unix process pid: 4831, image: oracle@hoegh (TNS V1-V3)


  15. *** 2015-05-30 12:31:12.588
  16. *** SESSION ID:(125.5) 2015-05-30 12:31:12.588
  17. *** CLIENT ID:() 2015-05-30 12:31:12.588
  18. *** SERVICE NAME:() 2015-05-30 12:31:12.588
  19. *** MODULE NAME:(sqlplus@hoegh (TNS V1-V3)) 2015-05-30 12:31:12.588
  20. *** ACTION NAME:() 2015-05-30 12:31:12.588
  21.  
  22. Successfully allocated 2 recovery slaves
  23. Using 66 overflow buffers per recovery slave
  24. Thread 1 checkpoint: logseq 7, block 2, scn 898139
  25.   cache-low rba: logseq 7, block 118
  26.     on-disk rba: logseq 7, block 143, scn 898639
  27.   start recovery at logseq 7, block 118, scn 0

  28. *** 2015-05-30 12:31:12.690
  29. Started writing zeroblks thread 1 seq 7 blocks 143-150

  30. *** 2015-05-30 12:31:12.690
  31. Completed writing zeroblks thread 1 seq 7
  32. ==== Redo read statistics for thread 1 ====
  33. Total physical reads (from disk and memory): 4096Kb
  34. -- Redo read_disk statistics --
  35. Read rate (ASYNC): 12Kb in 0.08s => 0.15 Mb/sec
  36. Longest record: 1Kb, moves: 0/51 (0%)
  37. Change moves: 2/35 (5%), moved: 0Mb
  38. Longest LWN: 4Kb, moves: 0/9 (0%), moved: 0Mb
  39. Last redo scn: 0x0000.000db64e (898638)
  40. ----------------------------------------------
  41. ----- Recovery Hash Table Statistics ---------
  42. Hash table buckets = 262144
  43. Longest hash chain = 1
  44. Average hash chain = 9/9 = 1.0
  45. Max compares per lookup = 1
  46. Avg compares per lookup = 29/75 = 0.4
  47. ----------------------------------------------

  48. *** 2015-05-30 12:31:12.692
  49. KCRA: start recovery claims for 9 data blocks

  50. *** 2015-05-30 12:31:12.703
  51. KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0

  52. *** 2015-05-30 12:31:12.704
  53. Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

  54. *** 2015-05-30 12:31:12.704
  55. Completed redo application of 0.00MB

  56. *** 2015-05-30 12:31:12.709
  57. Completed recovery checkpoint
  58. ----- Recovery Hash Table Statistics ---------
  59. Hash table buckets = 262144
  60. Longest hash chain = 1
  61. Average hash chain = 9/9 = 1.0
  62. Max compares per lookup = 1
  63. Avg compares per lookup = 22/38 = 0.6
  64. ----------------------------------------------
  65. Recovery sets nab of thread 1 seq 7 to 143 with 8 zeroblks

  66. *** 2015-05-30 12:31:13.044
  67. kwqmnich: current time:: 4: 31: 12: 0
  68. kwqmnich: instance no 0 repartition flag 1
  69. kwqmnich: initialized job cache structure
  70. kwqinfy: Call kwqrNondurSubInstTsk

  71. *** 2015-05-30 12:31:30.897
  72. -- The following are current System-scope REDO Log Archival related
  73. -- parameters and can be included in the database initialization file.
  74. --
  75. -- LOG_ARCHIVE_DEST=\'\'
  76. -- LOG_ARCHIVE_DUPLEX_DEST=\'\'
  77. --
  78. -- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
  79. --
  80. -- DB_UNIQUE_NAME=\"HOEGH\"
  81. --
  82. -- LOG_ARCHIVE_CONFIG=\'SEND, RECEIVE, NODG_CONFIG\'
  83. -- LOG_ARCHIVE_MAX_PROCESSES=4
  84. -- STANDBY_FILE_MANAGEMENT=MANUAL
  85. -- STANDBY_ARCHIVE_DEST=?/dbs/arch
  86. -- FAL_CLIENT=\'\'
  87. -- FAL_SERVER=\'\'
  88. --
  89. -- LOG_ARCHIVE_DEST_1=\'LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch\'
  90. -- LOG_ARCHIVE_DEST_1=\'MANDATORY NOREOPEN NODELAY\'
  91. -- LOG_ARCHIVE_DEST_1=\'ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC\'
  92. -- LOG_ARCHIVE_DEST_1=\'NOREGISTER NOALTERNATE NODEPENDENCY\'
  93. -- LOG_ARCHIVE_DEST_1=\'NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME\'
  94. -- LOG_ARCHIVE_DEST_1=\'VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)\'
  95. -- LOG_ARCHIVE_DEST_STATE_1=ENABLE
  96. --
  97. -- Below are two sets of SQL statements, each of which creates a new
  98. -- control file and uses it to open the database. The first set opens
  99. -- the database with the NORESETLOGS option and should be used only if
  100. -- the current versions of all online logs are available. The second
  101. -- set opens the database with the RESETLOGS option and should be used
  102. -- if online logs are unavailable.
  103. -- The appropriate set of statements can be copied from the trace into
  104. -- a script file, edited as necessary, and executed when there is a
  105. -- need to re-create the control file.
  106. --
  107. -- Set #1. NORESETLOGS case
  108. --
  109. -- The following commands will create a new control file and use it
  110. -- to open the database.
  111. -- Data used by Recovery Manager will be lost.
  112. -- Additional logs may be required for media recovery of offline
  113. -- Use this only if the current versions of all online logs are
  114. -- available.
  115. -- After mounting the created controlfile, the following SQL
  116. -- statement will place the database in the appropriate
  117. -- protection mode:
  118. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  119. STARTUP NOMOUNT
  120. CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" NORESETLOGS NOARCHIVELOG
  121.     MAXLOGFILES 16
  122.     MAXLOGMEMBERS 3
  123.     MAXDATAFILES 100
  124.     MAXINSTANCES 8
  125.     MAXLOGHISTORY 292
  126. LOGFILE
  127.   GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  128.   GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  129.   GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
  130. -- STANDBY LOGFILE
  131. DATAFILE
  132.   \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  133.   \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  134.   \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  135.   \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  136.   \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
  137. CHARACTER SET AL32UTF8
  138. ;
  139. -- Commands to re-create incarnation table
  140. -- Below log names MUST be changed to existing filenames on
  141. -- disk. Any one log file from each branch can be used to
  142. -- re-create incarnation records.
  143. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
  144. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\';
  145. -- Recovery is required if any of the datafiles are restored backups,
  146. -- or if the last shutdown was not normal or immediate.
  147. RECOVER DATABASE
  148. -- Database can now be opened normally.
  149. ALTER DATABASE OPEN;
  150. -- Commands to add tempfiles to temporary tablespaces.
  151. -- Online tempfiles have complete space information.
  152. -- Other tempfiles may require adjustment.
  153. ALTER TABLESPACE TEMP ADD TEMPFILE \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\'
  154.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  155. -- End of tempfile additions.
  156. --
  157. -- Set #2. RESETLOGS case
  158. --
  159. -- The following commands will create a new control file and use it
  160. -- to open the database.
  161. -- Data used by Recovery Manager will be lost.
  162. -- The contents of online logs will be lost and all backups will
  163. -- be invalidated. Use this only if online logs are damaged.
  164. -- After mounting the created controlfile, the following SQL
  165. -- statement will place the database in the appropriate
  166. -- protection mode:
  167. -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
  168. STARTUP NOMOUNT
  169. CREATE CONTROLFILE REUSE DATABASE \"HOEGH\" RESETLOGS NOARCHIVELOG
  170.     MAXLOGFILES 16
  171.     MAXLOGMEMBERS 3
  172.     MAXDATAFILES 100
  173.     MAXINSTANCES 8
  174.     MAXLOGHISTORY 292
  175. LOGFILE
  176.   GROUP 1 \'/u01/app/oracle/oradata/HOEGH/redo01.log\' SIZE 50M BLOCKSIZE 512,
  177.   GROUP 2 \'/u01/app/oracle/oradata/HOEGH/redo02.log\' SIZE 50M BLOCKSIZE 512,
  178.   GROUP 3 \'/u01/app/oracle/oradata/HOEGH/redo03.log\' SIZE 50M BLOCKSIZE 512
  179. -- STANDBY LOGFILE
  180. DATAFILE
  181.   \'/u01/app/oracle/oradata/HOEGH/system01.dbf\',
  182.   \'/u01/app/oracle/oradata/HOEGH/sysaux01.dbf\',
  183.   \'/u01/app/oracle/oradata/HOEGH/undotbs01.dbf\',
  184.   \'/u01/app/oracle/oradata/HOEGH/users01.dbf\',
  185.   \'/u01/app/oracle/oradata/HOEGH/example01.dbf\'
  186. CHARACTER SET AL32UTF8
  187. ;
  188. -- Commands to re-create incarnation table
  189. -- Below log names MUST be changed to existing filenames on
  190. -- disk. Any one log file from each branch can be used to
  191. -- re-create incarnation records.
  192. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_762197622.dbf\';
  193. -- ALTER DATABASE REGISTER LOGFILE \'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_1_860888149.dbf\';
  194. -- Recovery is required if any of the datafiles are restored backups,
  195. -- or if the last shutdown was not normal or immediate.
  196. RECOVER DATABASE USING BACKUP CONTROLFILE
  197. -- Database can now be opened zeroing the online logs.
  198. ALTER DATABASE OPEN RESETLOGS;
  199. -- Commands to add tempfiles to temporary tablespaces.
  200. -- Online tempfiles have complete space information.
  201. -- Other tempfiles may require adjustment.
  202. ALTER TABLESPACE TEMP ADD TEMPFILE \'/u01/app/oracle/oradata/HOEGH/temp01.dbf\'
  203.      SIZE 30408704 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
  204. -- End of tempfile additions.

5.NORESETLOGS VS RESETLOGS

从trace文件可以看出,提供了两个创建控制文件的脚本,分别针对两种不同的应用场景:NORESETLOGS适用于当前redo log可用,而RESETLOGS适用于当前redo log不可用

NORESETLOGS重建步骤

1.执行脚本,重建控制文件(CREATE CONTROLFILE REUSE DATABASE "HOEGH" NORESETLOGS NOARCHIVELOG......
2.执行介质恢复
RECOVER DATABASE
3.打开数据库
ALTER DATABASE OPEN;
4.添加临时表空间

RESETLOGS重建步骤

1.执行脚本,重建控制文件CREATE CONTROLFILE REUSE DATABASE "HOEGH" RESETLOGS NOARCHIVELOG......
2.执行介质恢复
RECOVER DATABASE USING BACKUP CONTROLFILE
3.打开数据库ALTER DATABASE OPEN RESETLOGS;
4.添加临时表空间

hoegh
15.05.30
-- The End --

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

请登录后发表评论 登录
全部评论

注册时间:2015-03-13

  • 博文量
    132
  • 访问量
    1999746