rebuild controlfile 控制文件创建脚本

最新推荐文章于 2024-06-03 09:36:44 发布
最新推荐文章于 2024-06-03 09:36:44 发布
阅读量106 收藏
点赞数
文章标签： 数据库

经常特殊情况，控制文件损坏，需要重建控制文件。

1：在数据库OPEN情况下到处创建控制文件的脚本

  1. alter database backup controlfile to trace;

2：trace 文件的路径：

$ORACLE_BASE/admin/mis/udump

3:查看

$cat mis_ora_3618.trc|more

/oracle/admin/mis/udump/mis_ora_3618.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/product/database
System name: Linux
Node name: DELL
Release: 2.4.21-32.EL
Version: #1 SMP Fri Apr 15 21:02:58 EDT 2005
Machine: x86_64
Instance name: mis
Redo thread mounted by this instance: 1
Oracle process number: 52
Unix process pid: 3618, image: oracle@DELL (TNS V1-V3)

*** SESSION ID:(121.51596) 2011-03-08 17:32:41.076
*** 2011-03-08 17:32:41.076
# The following are current System-scope REDO Log Archival related
# parameters and can be included in the database initialization file.
#
# LOG_ARCHIVE_DEST=''
# LOG_ARCHIVE_DUPLEX_DEST=''
#
# LOG_ARCHIVE_FORMAT=arch%s.arc
# REMOTE_ARCHIVE_ENABLE=TRUE
# LOG_ARCHIVE_START=TRUE
# LOG_ARCHIVE_MAX_PROCESSES=2
# STANDBY_FILE_MANAGEMENT=MANUAL
# STANDBY_ARCHIVE_DEST=?/dbs/arch
# FAL_CLIENT=''
# FAL_SERVER=''
#
# LOG_ARCHIVE_DEST_1='LOCATION=/oracle/product/database/dbs/arch'
# LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
# LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM SYNC'
# LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
# LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
# LOG_ARCHIVE_DEST_STATE_1=ENABLE
#
# Below are two sets of SQL statements, each of which creates a new
# control file and uses it to open the database. The first set opens
# the database with the NORESETLOGS option and should be used only if
# the current versions of all online logs are available. The second
# set opens the database with the RESETLOGS option and should be used
# if online logs are unavailable.
# The appropriate set of statements can be copied from the trace into
# a script file, edited as necessary, and executed when there is a
# need to re-create the control file.
#
# Set #1. NORESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MIS" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 (
'/oracle/oradata/mis/redo01.log',
'/oracle/oradata/mis/log_01.log'
) SIZE 10M,
GROUP 2 (
'/oracle/oradata/mis/redo02.log',
'/oracle/oradata/mis/redo_02.log'
) SIZE 10M,
GROUP 3 (
'/oracle/oradata/mis/redo03.log',
'/oracle/oradata/mis/redo_03.log'
) SIZE 10M,
GROUP 4 (
'/oracle/oradata/mis/log04.log',
'/oracle/oradata/mis/redo04.log'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/mis/system01.dbf',
'/oracle/oradata/mis/undotbs01.dbf',
'/oracle/oradata/mis/indx01.dbf',
'/oracle/oradata/mis/tools01.dbf',
'/oracle/oradata/mis/users01.dbf',
'/oracle/oradata/mis/mis01.dbf',
'/oracle/oradata/mis/mis02.dbf',
'/oracle/oradata/mis/mis03.dbf',
'/oracle/oradata/mis/undotbs02.dbf',
'/oracle/oradata/mis/mis04.dbf',
'/oracle/oradata/mis/TEMP01.dbf',
'/oracle/oradata/mis/mis05.dbf',
'/oracle/oradata/mis/mis06.dbf',
'/oracle/oradata/mis/mis07.dbf',
'/oracle/oradata/mis/DISCOVER01.dbf',
'/oracle/oradata/mis/mis08.dbf',
'/oracle/oradata/mis/mis09.dbf',
'/oracle/oradata/mis/system02.dbf',
'/oracle/oradata/mis/mis10.dbf',
'/oracle/oradata/mis/mis11.dbf',
'/oracle/oradata/mis/mis12.dbf',
'/oracle/oradata/mis/misidx01.dbf',
'/oracle/oradata/mis/temp02.dbf',
'/oracle/oradata/mis/mis13.dbf',
'/oracle/oradata/mis/misidx02.dbf',
'/oracle/oradata/mis/discover02.dbf',
'/oracle/oradata/mis/perfstat01.dbf',
'/oracle/oradata/mis/mis14.dbf',
'/oracle/oradata/mis/misidx03.dbf',
'/oracle/oradata/mis/misidx04.dbf'
CHARACTER SET ZHS16GBK
;
# Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/misbak/control%F''');
# Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
# 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 TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_3.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_02.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/TEMP101.dbf'
SIZE 1024M REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#
# Set #2. RESETLOGS case
#
# The following commands will create a new control file and use it
# to open the database.
# The contents of online logs will be lost and all backups will
# be invalidated. Use this only if online logs are damaged.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MIS" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 (
'/oracle/oradata/mis/redo01.log',
'/oracle/oradata/mis/log_01.log'
) SIZE 10M,
GROUP 2 (
'/oracle/oradata/mis/redo02.log',
'/oracle/oradata/mis/redo_02.log'
) SIZE 10M,
GROUP 3 (
'/oracle/oradata/mis/redo03.log',
'/oracle/oradata/mis/redo_03.log'
) SIZE 10M,
GROUP 4 (
'/oracle/oradata/mis/log04.log',
'/oracle/oradata/mis/redo04.log'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/mis/system01.dbf',
'/oracle/oradata/mis/undotbs01.dbf',
'/oracle/oradata/mis/indx01.dbf',
'/oracle/oradata/mis/tools01.dbf',
'/oracle/oradata/mis/users01.dbf',
'/oracle/oradata/mis/mis01.dbf',
'/oracle/oradata/mis/mis02.dbf',
'/oracle/oradata/mis/mis03.dbf',
'/oracle/oradata/mis/undotbs02.dbf',
'/oracle/oradata/mis/mis04.dbf',
'/oracle/oradata/mis/TEMP01.dbf',
'/oracle/oradata/mis/mis05.dbf',
'/oracle/oradata/mis/mis06.dbf',
'/oracle/oradata/mis/mis07.dbf',
'/oracle/oradata/mis/DISCOVER01.dbf',
'/oracle/oradata/mis/mis08.dbf',
'/oracle/oradata/mis/mis09.dbf',
'/oracle/oradata/mis/system02.dbf',
'/oracle/oradata/mis/mis10.dbf',
'/oracle/oradata/mis/mis11.dbf',
'/oracle/oradata/mis/mis12.dbf',
'/oracle/oradata/mis/misidx01.dbf',
'/oracle/oradata/mis/temp02.dbf',
'/oracle/oradata/mis/mis13.dbf',
'/oracle/oradata/mis/misidx02.dbf',
'/oracle/oradata/mis/discover02.dbf',
'/oracle/oradata/mis/perfstat01.dbf',
'/oracle/oradata/mis/mis14.dbf',
'/oracle/oradata/mis/misidx03.dbf',
'/oracle/oradata/mis/misidx04.dbf'
CHARACTER SET ZHS16GBK
;
# Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/misbak/control%F''');
# Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_3.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_02.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/TEMP101.dbf'
SIZE 1024M REUSE AUTOEXTEND OFF;
# End of tempfile additions.
#

5：脚本有两种情况的创建语句：

1：noresetlogs case

2: resetlogs case

第一段sql是在所有online redolog没有丢失，以noresetlogs选项打开数据库的情况下使用的。第二段则是在丢失了online redolog需要resetlogs的情况下使用。

6:一般是用 noresetlogs

创建一个脚本（将这段code 复制过来就可以了）

vi re_bulid.sql

CREATE CONTROLFILE REUSE DATABASE "MIS" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 454
LOGFILE
GROUP 1 (
'/oracle/oradata/mis/redo01.log',
'/oracle/oradata/mis/log_01.log'
) SIZE 10M,
GROUP 2 (
'/oracle/oradata/mis/redo02.log',
'/oracle/oradata/mis/redo_02.log'
) SIZE 10M,
GROUP 3 (
'/oracle/oradata/mis/redo03.log',
'/oracle/oradata/mis/redo_03.log'
) SIZE 10M,
GROUP 4 (
'/oracle/oradata/mis/log04.log',
'/oracle/oradata/mis/redo04.log'
) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/mis/system01.dbf',
'/oracle/oradata/mis/undotbs01.dbf',
'/oracle/oradata/mis/indx01.dbf',
'/oracle/oradata/mis/tools01.dbf',
'/oracle/oradata/mis/users01.dbf',
'/oracle/oradata/mis/mis01.dbf',
'/oracle/oradata/mis/mis02.dbf',
'/oracle/oradata/mis/mis03.dbf',
'/oracle/oradata/mis/undotbs02.dbf',
'/oracle/oradata/mis/mis04.dbf',
'/oracle/oradata/mis/TEMP01.dbf',
'/oracle/oradata/mis/mis05.dbf',
'/oracle/oradata/mis/mis06.dbf',
'/oracle/oradata/mis/mis07.dbf',
'/oracle/oradata/mis/DISCOVER01.dbf',
'/oracle/oradata/mis/mis08.dbf',
'/oracle/oradata/mis/mis09.dbf',
'/oracle/oradata/mis/system02.dbf',
'/oracle/oradata/mis/mis10.dbf',
'/oracle/oradata/mis/mis11.dbf',
'/oracle/oradata/mis/mis12.dbf',
'/oracle/oradata/mis/misidx01.dbf',
'/oracle/oradata/mis/temp02.dbf',
'/oracle/oradata/mis/mis13.dbf',
'/oracle/oradata/mis/misidx02.dbf',
'/oracle/oradata/mis/discover02.dbf',
'/oracle/oradata/mis/perfstat01.dbf',
'/oracle/oradata/mis/mis14.dbf',
'/oracle/oradata/mis/misidx03.dbf',
'/oracle/oradata/mis/misidx04.dbf'
CHARACTER SET ZHS16GBK

7：重建控制文件条件：

startup nomount

8:Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_3.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/temp101_02.dbf'
SIZE 4096M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP101 ADD TEMPFILE '/oracle/oradata/mis/TEMP101.dbf'
SIZE 1024M REUSE AUTOEXTEND OFF;

这个情况就是临时表空间需要在OPEN以后手动创建。

9：也许还需要restore datafile 和recovery datafile

[@more@]

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

转载于:http://blog.itpub.net/22934571/viewspace-1046995/

csb47658
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
RTU_scripts:游戏 Rebuild The Universe 的附加脚本
06-26
RTU_scripts 游戏 Rebuild The Universe 的附加脚本用法：目前您必须将 rtu_scripts.js 内容复制并粘贴到您的控制台中，然后发出： init(false) 仅用于添加到单元面板的附加购买信息init(true) 同上，加上可以访问...
Toad 使用快速入门
wfskiki的专栏
09-05 1187
 Toad 使用快速入门目录一．Toad功能综述二．系统需求三．安装指南四．快速入门1． Schema browser的用法简介2． SQL Editor的使用介绍3． Procedure Editor的用法介绍4． 如何进行PLSQL的debug5． 如何使用SQLab Xpert优化SQL6． 如何使用SQL Modeler来编写查询7． 如何使用Toad的DBA方面的功能一、 T
不完全恢复-ctl-create_script
hugh 's Oracle 10g blog
11-04 743
--不完全恢复-ctl-create_script controlfile 备份的方法 1. 冷备份 2. RMAN可以实时备份控制文件 3. 备份控制文件的生成脚本 S1. alter database backup controlfile to trace noresetlogs; --生成在UDUMP中，创建控制文件生成脚本 或者 alter database backup contr
控制文件恢复（一） 版本不一致
12-15 289
恢复控制文件（一） 版本不一致 1. 模拟错误 备份3号控制文件 [oracle@host01 PROD1]$ ls control01.ctl example01.dbf redo02.log sysau...
重建控制文件--Rebuild controlfile
weixin_34413065的博客
06-25 71
    重建控制文件时DBA需要知道，但是也许整个职业生涯都不会再生产系统上遇见。    首先强调，备份是最安全，快捷，有效的恢复方式，一个DBA，如果没有规划好有效的备份，终有一天，他会被噩梦惊醒。       梦醒之后，就是无奈的，繁杂的手工恢复。而且，恢复到什么程度，就要看天，看命，反正不能看自己了，因为没有备份，你已经将主动权完全交个别人。这里就记录一下controlfile的重建    ...
toad for oracle(导入导出实例)
huaxiaoyou的专栏
10-29 8605
例：create user his identified by his default tablespace users temporary tablespace temp;grant connect,resource,dba to his;create tablespace hisloggingdatafile d:/oracle/product/10.2.0/oradata/zjxsh/
BAT批处理脚本-系统设置查询修改硬件相关-rebuild.zip
12-26
为避免误触发，有些改为了txt格式，使用时请自行查看业务然后改为bat格式即可。
rebuild.f8422299368079bde8e21a7595e8291f_rebuild_电机_永磁同步电机双闭环控制_
10-02
永磁同步电机双闭环控制系统，更好的控制转速以及转矩性能
Rebuild_rebuild_duomino_
10-03
小牛牛对多米诺骨牌有很大兴趣，然而她的骨牌比较特别，只有黑色和白色的两种。她觉得如果存在连续三个骨牌是同一种颜色，那么这个骨牌排列便是不美观的。现在她有n个骨牌要来排列，她想知道不美观的排列的个数。...
KsDumper_rebuild_Kernel_
09-29
Kernelmode dumperFeatures Dump any process main module using a kernel driver (both x86 and x64) Rebuild PE32/PE64 header and sections Works on protected system processes & processes with stripped ...
Oracle 11g重建控制文件——如何获取创建控制文件脚本
congjiumi6955的博客
05-30 276
控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。 备份控制文件也有多种办法，...
如何获得创建控制文件的脚本并重建控制文件
01-25 935
作者：eygle链接：http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的.Oracle提供两种方式备份控制文件:1.生成可以重建控制文件的脚本2.备份二进制的控制文件我们看一下如何获得可以重建控制文件的脚本
oracle控制文件的恢复——有备份、有控制文件创建脚本、都没有3种情况
xiaoxiaoyuzhou88的专栏
05-17 2155
先介绍四种恢复方式的区别: 1.
专属编程笔记
u014397365的博客
06-01 1074
在软件开发中，Utils（或Utilities）目录通常用于存放一些。这些工具类或函数为整个应用程序或多个模块提供便利的功能支持，使得代码更加模块化、易于维护和重用。UtilsUtils目录的设计目的在于避免代码重复，并确保在整个项目中以一致的方式处理共通任务。这样做可以提高开发效率，降低维护成本，并确保代码的整洁性和可读性。
项目更换服务器时间少8小时
记录点滴
06-01 278
可以参考这位大佬的修正方法传送门。但是改完后记得重启项目！在项目的数据库连接加入。查看当前的linux系统时间。查看当前linux硬件时间。把硬件时间同步到系统时间。
chat4-Server端保存聊天消息到mysql
gulanga5的博客
06-02 796
本文档描述了Server端接收到Client的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql服务端为当前客户端创建一个线程，此线程接收当前客户端的消息并转发给所有客户端或私发给某个客户端同时将聊天消息保存到mysql本文档主要总结了将聊天消息保存到mysql!!!),;import;import;import;/**用druid连接池来连接数据库的工具类*/DBUtil。
【数据库复习】第一章 绪论
最新发布
AClerk的博客
06-03 609
想你了，牢大
调用greenhills对gpj文件进行rebuild
05-13
要使用Green Hills的MULTI IDE对.gpj文件进行重新构建，您可以按照以下步骤进行操作： 1. 打开MULTI IDE，并将.gpj文件添加到项目中。 2. 确认您的项目设置正确，包括正确的目标设备和编译器选项。 3. 单击...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值