重建控制文件时 DBA 需要知道, 但是也许整个职业生涯都不会再生产系统上遇见
首先强调, 备份是最安全, 快捷, 有效的恢复方式, 一个 DBA, 如果没有规划好有效的备份, 终有一天, 他会被噩梦惊醒
梦醒之后, 就是无奈的, 繁杂的手工恢复而且, 恢复到什么程度, 就要看天, 看命, 反正不能看自己了, 因为没有备份, 你已经将主动权完全交个别人这里就记录一下 controlfile 的重建
在其他正常的数据库上创建 controlfile trace
SQL> alter database backup controlfile to trace;
指定 trace 文件的生成路径
SQL> alter database backup controlfile to trace as '/tmp/controlfile.trc'; trace 文件路径在 user_dump_dest 下
- SQL> show parameter user_dump_dest
- NAME TYPE VALUE
- -------------- ------ ------------------------------------------------
- user_dump_dest string /home/Oracle/admin/R10105/udump/
- After navigating to the directory locate the latest trace file by date/time by issuing: ls -ltr.
- % cd /home/oracle/admin/R10105/udump/
- % ls -ltr
一个完整的 trace 请参考附件
你可以修改 trace 文件的路径等具体参数值, 并提取其中的创建脚本
- STARTUP NOMOUNT
- CREATE CONTROLFILE REUSE DATABASE "R10105" NORESETLOGS ARCHIVELOG
- MAXLOGFILES 16
- MAXLOGMEMBERS 3
- MAXDATAFILES 100
- MAXINSTANCES 8
- MAXLOGHISTORY 454
- LOGFILE
- GROUP 1 '/opt/oracle/oradata/R10105/redo01.log' SIZE 10M,
- GROUP 2 '/opt/oracle/oradata/R10105/redo02.log' SIZE 10M,
- GROUP 3 '/opt/oracle/oradata/R10105/redo03.log' SIZE 10M
- -- STANDBY LOGFILE
- DATAFILE
- '/opt/oracle/oradata/R10105/system01.dbf',
- '/opt/oracle/oradata/R10105/undotbs01.dbf',
- '/opt/oracle/oradata/R10105/sysaux01.dbf',
- '/opt/oracle/oradata/R10105/users01.dbf',
- '/opt/oracle/oradata/R10105/example01.dbf'
- CHARACTER SET AL32UTF8
- ;
注册 archivelog
- -- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash_recovery_area/R10105/archivelog/2012_06_15/o1_mf_1_1_c';
- -- ALTER DATABASE REGISTER LOGFILE '/home/oracle/flash_recovery_area/R10105/archivelog/2012_06_15/o1_mf_1_1_c';
- -- Recovery is required if any of the datafiles are restored backups,
- -- or if the last shutdown was not normal or immediate.
- RECOVER DATABASE
- -- All logs need archiving and a log switch is needed.
- ALTER SYSTEM ARCHIVE LOG ALL;
- -- Database can now be opened normally.
- ALTER DATABASE OPEN;
- -- Commands to add tempfiles to temporary tablespaces.
- -- Online tempfiles have complete space information.
- -- Other tempfiles may require adjustment.
- ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/oradata/R10105/temp01.dbf'
- SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
- -- End of tempfile additions.
- --
其实重建控制文件, 就是相当于手动重新建库, 把现有的数据库资源, 逐一添加到 controlfile 中, 最后注册目前的 archivelog 文件
同时, 重建 controlfile 分为 NORESETLOGS 和 RESETLOGS 两种, 根据需求选择相应的脚本
最后的最后, 这一切, 都是噩梦, 还是做好有效备份, 有备无患, 才是王道!!
附件:
Oracle 重建控制文件 - Rebuild controlfile
来源: http://www.linuxidc.com/Linux/2018-03/151561.htm