Oracle控制文件管理和恢复
oracle11g还原数据库步骤__概述说明以及解释
oracle11g还原数据库步骤概述说明以及解释引言部分的内容可以按照如下方式撰写：1. 引言1.1 概述引言部分将介绍本篇文章的主题，即Oracle 11g数据库还原步骤。
数据库还原是一项至关重要的任务，它可以帮助恢复丢失或损坏的数据，并确保系统的连续性和可靠性。
在本文中，我们将深入探讨Oracle 11g数据库还原的步骤和过程，以及执行还原操作前需要注意的准备工作。
1.2 文章结构在本文中，我们将按照以下顺序来讨论Oracle 11g数据库还原：- 首先，我们将介绍Oracle 11g数据库还原的重要性，阐述为什么必须进行数据库还原操作。
- 其次，我们将概述Oracle 11g数据库还原的步骤，并列出每个步骤的简要说明。
- 第三部分我们将详细描述执行数据库还原操作前所需进行的准备工作。
- 接下来，我们将提供执行数据库还原操作的详细步骤，包括必要时涉及到的命令和工具。
- 最后，我们将讨论完成数据库还原后进行验证和测试的方法与技巧。
1.3 目的本文旨在为读者提供有关Oracle 11g数据库还原的全面指南。
通过学习本文，读者将能够了解数据库还原的重要性、掌握进行数据库还原操作的步骤和技巧，并且能够有效地验证和测试还原后的数据库。
我们希望这篇文章能够帮助读者在数据库还原过程中避免常见错误，并提供相关提示和建议。
2. 正文:2.1 Oracle 11g数据库还原的重要性在数据库管理中，数据的安全性和完整性是至关重要的。
由于各种原因，比如硬件故障、用户误操作或者系统遭受攻击，数据库可能会丢失或损坏。
因此，在这些情况下，数据库还原变得非常重要。
Oracle 11g数据库还原是指恢复已经丢失或被损坏的数据到其先前可用状态的过程。
2.2 Oracle 11g数据库还原的步骤概述数据库还原通常包括以下主要步骤：- 备份介质准备：确定可用的备份介质，并确保其处于良好状态。
- 目标库环境准备：在目标库上创建必需的目录结构，并配置参数以适应还原操作。
第16章 Oracle备份与恢复
本 章 内 容
16.1
数 据 库 备 份 概 述
16.2
数 据 库 备 份 模 式
16.3
备 份 数 据 库
16.4
数 据 库 手 动 恢 复
采取各种措施来保证数据库的安全性和完整性， 但硬件故障、软件错误、病毒、误操作、故意破坏， 仍有可能发生，影响数据的正确性，甚至会破坏数据 库使数据部分或全部丢失。
（5）archieve log list; // 显示归档列表
16.3
备 份 数 据 库
（一）物理备份
备份：数据文件.dbf
控制文件.ctl
日志文件.log
1.脱机备份 （无成本复制 copy paste）
在数据库关闭后，备份物理文件。
脱机备份的步骤
（1）以DBA用户登录，列出各类文件 SQL>select name from v$datafile; SQL>select name from v$controlfile; SQL>select member from v$logfile;
小结
数据库的备份与恢复是保证数据库安全运 行的一项重要内容，也是数据库管理员的一项重要 职责。在实际的应用中，数据库可能会遇到一些意 外的破坏，导致数据库无法正常运行。数据库的一 个备份就是数据库中数据的一份复件，该复件包括 了数据库所有重要的组成部分，如控制文件、数据 文件、日志文件等。当数据库因意外事故而无法正 常运行时，就可以用该备份对数据进宪恢复，将意 外损失降低到最小。
（2）以DBA用户关闭数据库 SQL>connect / as sysdba SQL> shutdown immediate; （3）复制各个文件到硬盘上，“控制文件”互为镜像，复制一个即可。 SQL>host copy D:\oracle\product\10.1.0\oradata\*.dbf E:\backup\ SQL>host copy D:\oracle\product\10.1.0\oradata\*.ctl E:\backup\ SQL>host copy D:\oracle\product\10.1.0\oradata\*.log E:\backup\ （4）启动例程打开数据库 SQL> connect / as sysdba SQL> startup
Oracle11gR2RMAN配置控制文件自动备份（controlfileautobackup）
Oracle11gR2RMAN配置控制⽂件⾃动备份（controlfileautobackup）通过在 RMAN 中配置 control file autobackup，RMAN 会⾃动备份控制⽂件和参数⽂件到预先设定好的路径。
触发情况：1、当数据库的结构发⽣改变时（数据库运⾏在归档模式）2、当完成 RMAN 备份后好处：即使数据库丢失了控制⽂件和恢复⽬录(recovery catalog)，控制⽂件和参数⽂件也可以从⾃动备份的控制⽂件中进⾏恢复，让数据库多了⼀层保护，⽽且从autobackup中恢复控制⽂件和参数⽂件更加快速⽅便。
设置⽅法：查看现有的 RMAN 配置参数，可以看到没有启⽤控制⽂件⾃动备份，参数依旧是默认值[oracle@ATFDB1 admin]$ rman target /Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 9 09:19:07 2015Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: FIRE (DBID=917673527)RMAN> show all;using target database control file instead of recovery catalogRMAN configuration parameters for database with db_unique_name FIRE are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_fire.f'; # default启⽤控制⽂件⾃动备份特性RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters are successfully stored配置控制⽂件⾃动备份的路径和格式RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORADATA/controlfile/cf_%F';new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORADATA/controlfile/cf_%F';new RMAN configuration parameters are successfully stored查看重新设置的参数RMAN> show all;RMAN configuration parameters for database with db_unique_name FIRE are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/ORADATA/controlfile/cf_%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/app/oracle/product/11.2.0/db_1/dbs/snapcf_fire.f'; # default通过 RMAN 备份数据库，查看是否⽣成控制⽂件⾃动备份。
oracle数据库rman备份计划及恢复
oracle数据库rman备份计划及恢复1.rman完全恢复的前提条件：历史的datafile,controlfile和spfile备份，加上完整的archivelog和完好的redolog。
2.rman备份脚本： a.RMAN 0级备份命令:run{allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;backup incremental level 0 tag 'level0' format "E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\RFDB_level_0_%u_%s_%p" as compressed backupset database;sql "alter system archive log current";backup filesperset 3 format "E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\arch_%u_%s_%p_%c"archivelog all delete input; #备份归档可选，可以单独定期备份release channel c1;release channel c2;release channel c3;} b.RMAN 1级备份命令:run{allocate channel c1 type disk;allocate channel c2 type disk;allocate channel c3 type disk;backup incremental level 1 tag 'level1' format 'E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\RFDB_level_1_%u_%s_%p' as compressed backupset database;sql 'alter system archive log current';backup filesperset 3 format 'E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\arch_%u_%s_%p'archivelog all delete input; #备份归档可选，可以单独定期备份release channel c1;release channel c2;release channel c3;} c.rman删除备份命令（在保留最近⼀天备份的情况下，删除其他备份）:DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 1 DAYS; d.操作系统层⾯运⾏rman备份或删除命令(windows/linux)：rman target sys/rf4rfvbgt56yhn@rfdb nocatalog CMDFILE 'D:\app\rman\rman_file\level_0.txt' log=E:\recovery_area\rfdb\rfdb\AUTOBACKUP\rman_dir\log\rman_level_0.log export ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=/u01/app/oracle/product/11.2.0/db1export ORACLE_SID=atestexport PATH=$ORACLE_HOME/bin:$PATHrman target sys/123456@atest nocatalog CMDFILE '/u01/rman/rman.sh' log=/u01/rman/rman.log00 02 * * 1 bash /u01/rman_file/run_rman_0.sh00 02 * * 3 bash /u01/rman_file/run_rman_0.sh00 02 * * 5 bash /u01/rman_file/run_rman_0.sh00 04 * * * bash /u01/rman_file/run_delete.sh34 11 * * * bash /u01/rman_file/run_rman_0.sh e.rman参数设置：RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE disk TO 2;RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE disk TO 2;3.rman恢复a.拷贝datafile,controlfile和spfile的rman备份，以及完整的archivelog和完好的redolog⽂件到新的数据库。
oracle数据库备份与恢复方案
oracle数据库备份与恢复方案一、编写目的 (1)二、备份工具及备份方式 (1)三、软件备份 (1)四、软件恢复 (1)五、数据备份 (2)六、备份的存储 (2)七、备份数据的保存规定 (2)八、备份介质的格式 (3)九、数据恢复 (4)编写目的本文档主要说明公司项目在实施现场的软件及数据的备份和恢复方案。
二、备份工具及备份方式1.备份工具Oracle RMAN (Recovery Manager)：是一一种用于备份(backup)、还原(restore) 和恢复(recover)数据库的Oracle工具。
RMAN只能用于ORACLE8或更高的版本中。
它能够备份整个数据库或数据库部件，如表空间、数据文件、控制文件、归档文件以及Spfile参数文件。
RMAN也允许您进行增量数据块级别的备份，增量RMAN备份是时间和空间有效的，因为他们只备份自上次备份以来有变化的那些数据块.2.备份方式(1)自动备份：由Windows计划任务调度完成；(2)手工备份：完成特殊情况下的备份，分热备份和冷备份，热备份是指在不关闭数据库情况下进行备份，冷备份则需要停止Oracle实例服务。
三、软件备份1.以七天为一个周期每天23：00将所有软件拷贝到其他存储介质上2.超出七天的备份依次删除3.每月一号将上月最后7天的备份文件刻录到光盘上四、软件恢复1.找出最近的备份程序覆盖到正式运行环境的相应目录中1.业务系统或数据库在打重要补丁或升级的前后，必须按要求对业务系统进行停机备份或非停机备份,备份需包含应用和数据库的文件系统及数据，备份方式为手工备份，使用RMAN执行备份；2.系统日常备份:作为7*24运行的重要系统，必须最小化数据丢失的同时,还需要尽可能缩短恢复时间，数据库的日常备份策略如下：(1)确保数据库处于ArchiliveLog模式；(2)每日凌晨01：00执行数据库全备份,含控制文件备份；(3)每四小时间隔执行数据库归档日志备份，含控制文件备份；(4)每间隔两周执行数据库与应用系统文件备份，执行时间：数据库服务器在第一周的周六05：00，应用服务器在第一周的周六7：00。
控制文件丢失的恢复
一般情况下数据库的DBA都会对controlfile 进行多路复用，这样可以保证控制文件的安全性，对于数据库而言只要数据文件和redolog、archivelog不丢，数据都是可以恢复的，只是controlfile丢失，会比较麻烦。
环境：control01.ctl、control02.ctl、control03.ctl1、3个controlfile中的一个或者两个丢失，关闭数据库后，从没有没丢失的那个controlfile进行拷贝。
2、3个控制文件全丢失，可以进行重构控制文件，一般情况下我们会对控制文件进行二进制文件备份（alter database backup controlfile to trace as '*****' 备份成二进制文件）(alter database backup controlfile to '***' 是直接备份控制文件 )以下是备份出来的二进制文件的内容：有两种情况，1、完全恢复的时候用（红色）2、不完全恢复的时候用（紫红）-- 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=%t_%s_%r.dbf-- DB_UNIQUE_NAME="orcl"-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'-- LOG_ARCHIVE_MAX_PROCESSES=2-- STANDBY_FILE_MANAGEMENT=MANUAL-- STANDBY_ARCHIVE_DEST=?/dbs/arch-- FAL_CLIENT=''-- FAL_SERVER=''-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/orcl/arch'-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'-- 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 Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M-- STANDBY LOGFILEDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/users01.dbf','/u01/app/oracle/oradata/orcl/example01.dbf','/u01/app/oracle/oradata/orcl/app1_01.dbf','/u01/app/oracle/oradata/orcl/app02_01.dbf'CHARACTER SET AL32UTF8-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf'-- End of tempfile additions.-- Set #2. RESETLOGS case-- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M,GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M-- STANDBY LOGFILEDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/users01.dbf','/u01/app/oracle/oradata/orcl/example01.dbf','/u01/app/oracle/oradata/orcl/app1_01.dbf','/u01/app/oracle/oradata/orcl/app02_01.dbf'CHARACTER SET AL32UTF8-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- 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 TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'-- End of tempfile additions.执行以上脚本对controlfile进行重构时，是在数据库未启动的时候进行。
Oracle ramn 备份与恢复
ORACLE数据库的备份与恢复差异增量备份就是备份往前第一个备份级别小于或等于当前备份级别开始到现在的变化。
累计增量备份就是备份往前第一个备份级别小于当前备份级别开始到现在的变化。
0级备份也就是全备份，但0级备份可以在此基础之上再进行备份，但全备份就不可以。
Oracle rman 备份一、数据库全备份方法（nocatalog）1 注意rman有两个，要执行db_1/bin下那个（可以通过修改oracle变量来达到）2 在执行ramn之前要确认当前数据库是否处理Archive Mode模式，并处于Enabled下查看方式：如果不是可以通过如下方式打开并修改：SQL>startup mountSQL>alter database archivelogSQL>alter database open3全备份数据库：A先连接数据库HERMINGB查看备份列表中是否有以前备份的文件C执行全数据库备份4备份完之后可以查看一下备份出来的文件信息：5备份文件存放路径为：二、0级数据库备份A 以nocatalog方式使用rmanB连接数据库C执行0级数据库备份注：全备份与0级备份的相同点是都是将数据库全部备份下来。
全备份与0级备份的不同点是全备份不能作为增量备份，而0级可以作为增量备份。
1级备份就在0级备份的基础上进行的。
三、1级增量备份（基于0级备份之上）总结：可以将备份出来的文件考到其他介质上进行保存，并进行删除。
如果要恢复时请将该文件放回到这文件夹下，并要保存文件名不变。
说明：口令文件因为可以重新设置，所以不用备份，重做日志文件不能在非nocatalog中备份。
四、备份archivelog(参数文件+数据文件+控制文件+归档日志文件)先备份完再删除归档备份五、备份表空间A 查看有哪些表空间B备份名为TBS1的表空间六、备份控制文件七、镜像备份说明：5为TBS1表空间的编号，及对TBS1表空间进行镜像备份。
Oracle控制文件（CONTROLFILE）
Oracle控制⽂件（CONTROLFILE）⼀、Oracle 控制⽂件为⼆进制⽂件，初始化⼤⼩由CREATE DATABASE指定,可以使⽤RMAN备份记录了当前数据库的结构信息,同时也包含数据⽂件及⽇志⽂件的信息以及相关的状态,归档信息等等在参数⽂件中描述其位置，个数等等。
通常采⽤分散放开，多路复⽤的原则。
在mount阶段被读取，open阶段⼀直被使⽤维护数据库⼀致性(数据库启动时会⽐较控制⽂件与联机⽇志⽂件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)⼀个控制⽂件只能属于⼀个数据库控制⽂件的任意修改将写⼊到初始化参数中指定的所有控制⽂件中，读取时则仅读取第⼀个控制⽂件控制⽂件只能连接⼀个数据库，控制⽂件的⼤⼩⼀般不要超过MB,最多为个，最少⼀个，互为镜像控制⽂件中包含的内容数据库的名字、ID、创建的时间戳表空间的名字联机⽇志⽂件、数据⽂件的位置、个数、名字联机⽇志的Sequence号码检查点的信息撤销段的开始或结束归档信息备份信息⼆、查看控制⽂件的相关信息1.使⽤相关视图来查看V$CONTROLFILE --列出实例中所有控制⽂件的名字及状态信息V$PARAMETER --列出所有参数的位置及状态信息V$CONTROLFILE_RECORD_SECTION --列出控制⽂件中记录的部分信息SHOW PARAMETER CONTROL_FILES --列出控制⽂件的名字、状态、位置等SQL> select * from v$controlfile;STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS------- -------------------------------------------------- --- ---------- --------------/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 430/u01/app/oracle/oradata/orcl/control02.ctl NO 16384 430/u01/app/oracle/oradata/orcl/control03.ctl NO 16384 430SQL> select name,type,value from v$parameter where name like '%control%';NAME TYPE VALUEcontrol_files 2 /u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/oradata/orcl/control02.ctl,/u01/app/oracle/oradata/orcl/control03.ctlcontrol_file_record_keep_time 3 7SQL> select * from v$controlfile_record_section;TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------DATABASE 316 1 1 0 0 0CKPT PROGRESS 8180 11 0 0 0 0REDO THREAD 256 8 1 0 0 0REDO LOG 72 16 9 0 0 20DATAFILE 428 100 8 0 0 28FILENAME 524 2298 21 0 0 0TABLESPACE 68 100 7 0 0 7TEMPORARY FILENAME 56 100 1 0 0 1RMAN CONFIGURATION 1108 50 0 0 0 0LOG HISTORY 56 292 35 1 35 35OFFLINE RANGE 200 163 0 0 0 0TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------ARCHIVED LOG 584 28 20 1 20 20BACKUP SET 40 409 0 0 0 0BACKUP PIECE 736 200 0 0 0 0BACKUP DATAFILE 116 282 0 0 0 0BACKUP REDOLOG 76 215 0 0 0 0DATAFILE COPY 660 223 1 1 1 1BACKUP CORRUPTION 44 371 0 0 0 0COPY CORRUPTION 40 409 0 0 0 0DELETED OBJECT 20 818 3 1 3 3PROXY COPY 852 249 0 0 0 0BACKUP SPFILE 36 454 0 0 0 0TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------DATABASE INCARNATION 56 292 2 1 2 2FLASHBACK LOG 84 2048 0 0 0 0INSTANCE SPACE RESERVATION 28 1055 1 0 0 0REMOVABLE RECOVERY FILES 32 1000 0 0 0 0RMAN STATUS 116 141 0 0 0 0THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0MTTR 100 8 1 0 0 0DATAFILE HISTORY 568 57 0 0 0 0STANDBY DATABASE MATRIX 400 10 10 0 0 0GUARANTEED RESTORE POINT 212 2048 0 0 0 0TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------RESTORE POINT 212 2083 0 0 0 0SQL> show parameter control_files;NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_files string /u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctlSQL> select controlfile_sequence# from v$database;CONTROLFILE_SEQUENCE#---------------------9852.使⽤STRINGS命令来查看控制⽂件中的具体内容SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl | more}|{zJORCLL-+RG+ORCL+ORCLorclorcl-+-='/u01/app/oracle/oradata/orcl/redo03.log/u01/app/oracle/oradata/orcl/redo3.log/u01/app/oracle/oradata/orcl/redo02.log/u01/app/oracle/oradata/orcl/redo2.log/u01/app/oracle/oradata/orcl/redo01.log/u01/app/oracle/oradata/orcl/tbs1_2.dbf/u01/app/oracle/oradata/orcl/tbs1_1.dbf/u01/app/oracle/oradata/orcl/example01.dbf/u01/app/oracle/oradata/orcl/users01.dbf/u01/app/oracle/oradata/orcl/sysaux01.dbf/u01/app/oracle/oradata/orcl/undotbs01.dbf/u01/app/oracle/oradata/orcl/system01.dbfu01/app/oracle/oradata/orcl/undotbs02.dbf/u01/app/oracle/oradata/orcl/redo03.log/u01/app/oracle/oradata/orcl/redo3.log/u01/app/oracle/oradata/orcl/redo02.log/u01/app/oracle/oradata/orcl/redo2.log/u01/app/oracle/oradata/orcl/redo01.log/u01/app/oracle/oradata/orcl/tbs1_2.dbf/u01/app/oracle/oradata/orcl/tbs1_1.dbf/u01/app/oracle/oradata/orcl/example01.dbf/u01/app/oracle/oradata/orcl/users01.dbf/u01/app/oracle/oradata/orcl/sysaux01.dbf/u01/app/oracle/oradata/orcl/undotbs01.dbf/u01/app/oracle/oradata/orcl/system01.dbfu01/app/oracle/oradata/orcl/undotbs02.dbfSYSTEMUNDOTBS1SYSAUXUSERSEXAMPLETBS1TEMP3.备份控制⽂件到平⾯⽂件(然后查看控制⽂件中的具体内容)SQL> alter database backup controlfile to trace as '/u01/app/oracle/ctl.txt';--或者使⽤SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl > /u01/app/oracle/ctl.txtSQL> host cat /u01/app/oracle/ctl.txt;-- 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=%t_%s_%r.dbf---- DB_UNIQUE_NAME="orcl"---- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'-- LOG_ARCHIVE_MAX_PROCESSES=2-- STANDBY_FILE_MANAGEMENT=MANUAL-- STANDBY_ARCHIVE_DEST=?/dbs/arch-- FAL_CLIENT=''-- FAL_SERVER=''---- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'-- LOG_ARCHIVE_DEST_STATE_10=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-- Set #1. NORESETLOGS case---- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- Additional logs may be required for media recovery of offline-- Use this only if the current versions of all online logs are-- available.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log','/u01/app/oracle/oradata/orcl/redo2.log') SIZE 50M,GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log','/u01/app/oracle/oradata/orcl/redo3.log') SIZE 100M,GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log ','/u01/app/oracle/oradata/orcl/redo7.log ') SIZE 50M,GROUP 8 ('/u01/app/oracle/oradata/orcl/redo8.log') SIZE 50M-- STANDBY LOGFILEDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/users01.dbf','/u01/app/oracle/oradata/orcl/example01.dbf','/u01/app/oracle/oradata/orcl/tbs1_1.dbf','/u01/app/oracle/oradata/orcl/tbs1_2.dbf'CHARACTER SET WE8ISO8859P1;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';-- Recovery is required if any of the datafiles are restored backups,-- or if the last shutdown was not normal or immediate.RECOVER DATABASE-- 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 '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;-- End of tempfile additions.---- Set #2. RESETLOGS case-- The following commands will create a new control file and use it-- to open the database.-- Data used by Recovery Manager will be lost.-- The contents of online logs will be lost and all backups will-- be invalidated. Use this only if online logs are damaged.-- After mounting the created controlfile, the following SQL-- statement will place the database in the appropriate-- protection mode:-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log','/u01/app/oracle/oradata/orcl/redo2.log') SIZE 50M,GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log','/u01/app/oracle/oradata/orcl/redo3.log') SIZE 100M,GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log ','/u01/app/oracle/oradata/orcl/redo7.log ') SIZE 50M,GROUP 8 ('/u01/app/oracle/oradata/orcl/redo08.log','/u01/app/oracle/oradata/orcl/redo8.log') SIZE 50M-- STANDBY LOGFILEDATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf','/u01/app/oracle/oradata/orcl/undotbs01.dbf','/u01/app/oracle/oradata/orcl/sysaux01.dbf','/u01/app/oracle/oradata/orcl/users01.dbf','/u01/app/oracle/oradata/orcl/example01.dbf','/u01/app/oracle/oradata/orcl/tbs1_1.dbf','/u01/app/oracle/oradata/orcl/tbs1_2.dbf'CHARACTER SET WE8ISO8859P1;-- Commands to re-create incarnation table-- Below log names MUST be changed to existing filenames on-- disk. Any one log file from each branch can be used to-- re-create incarnation records.-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';-- 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 TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;-- End of tempfile additions.4.转储控制⽂件内容(查看控制⽂件中的具体内容)alter session set events 'immediate trace name CONTROLF level 12'; level表⽰级别或 --level1 块头的内容 --level2 数据⽂件内容 --levle 10 |12 所有内容oradebug setmypidoradebug dump controlf 12SQL> alter system set events 'immediate trace name controlf level 10';System altered.SQL> show parameter user_dump;NAME TYPE VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /u01/app/oracle/admin/orcl/udump--查看路径中最新的⽂件[root@robinson ~]# vim /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc--以下仅列出部分信息Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsORACLE_HOME = /u01/app/oracle/10gSystem name: LinuxNode name: Release: 2.6.18-164.el5xenVersion: #1 SMP Tue Aug 18 16:06:30 EDT 2009Machine: i686Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 19Unix process pid: 5110, image: oracle@ (TNS V1-V3)*** 2010-05-20 18:52:30.104*** SERVICE NAME:(SYS$USERS) 2010-05-20 18:52:30.084*** SESSION ID:(159.10) 2010-05-20 18:52:30.084DUMP OF CONTROL FILES, Seq # 985 = 0x3d9V10 STYLE FILE HEADER:Compatibility Vsn = 169869568=0xa200100Db ID=1242732291=0x4a129703, Db Name='ORCL'Activation ID=0=0x0Control Seq=985=0x3d9, File size=430=0x1aeFile Number=0, Blksiz=16384, File Type=1 CONTROLDump of memory from 0xB7EF7E00 to 0xB7EFBE00--使⽤oradebug setmypidSQL> oradebug setmypid --标记当前进程Statement processed.--获得当前的spidSQL> select spid from v$process where addr =2 (select paddr from v$session where sid =3 (select sid from v$mystat where rownum = 1));SPID------------5110SQL> oradebug dump controlf 3;--3表⽰包含头⽂件和数据⽂件内容Statement processed.--查看user_dump_dest 路径SQL> show parameter user_dNAME TYPE VALUE------------------------------------ ----------- ------------------------------user_dump_dest string /u01/app/oracle/admin/orcl/udu mp--找到当前spid的trc⽂件SQL> ! ls /u01/app/oracle/admin/orcl/udump/*5110.* -- *//u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trcSQL> ! cat /u01/app/oracle/admin/orcl/udump/orcl_ora_5110.trc--下⾯列出的为部分内容***************************************************************************RMAN CONFIGURATION RECORDS***************************************************************************(size = 1108, compat size = 1108, section max = 50, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 90, numrecs = 50)***************************************************************************FLASHBACK LOGFILE RECORDS(size = 84, compat size = 84, section max = 2048, section in-use = 0, last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 139, numrecs = 2048)***************************************************************************THREAD INSTANCE MAPPING RECORDS***************************************************************************(size = 80, compat size = 80, section max = 8, section in-use = 8,last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 156, numrecs = 8)orcl recno=1UNNAMED_INSTANCE_2 recno=2UNNAMED_INSTANCE_3 recno=3UNNAMED_INSTANCE_4 recno=4UNNAMED_INSTANCE_5 recno=5UNNAMED_INSTANCE_6 recno=6UNNAMED_INSTANCE_7 recno=7UNNAMED_INSTANCE_8 recno=8***************************************************************************MTTR RECORDS***************************************************************************(size = 100, compat size = 100, section max = 8, section in-use = 1, last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 157, numrecs = 8)MTTR record for thread 1MTTR statistics status: 3Init time: Avg: 47090974 us, Times measured: 4File open time: Avg: 11225 us, Times measured: 21Log block read time: Avg: 20 us, Times measured: 65536Data block read/claim time: Avg: 170 us, Times measured: 1000Data block write time: Avg: 390 us1000 change vector apply time: Avg: 112435 us, Times measured: 37 Ratio Information:# of log blocks measured: 207891# of data blocks measured: 12536# of change vectors measured: 564444STANDBY DATABASE MAP RECORDS***************************************************************************(size = 400, compat size = 400, section max = 10, section in-use = 10,last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 160, numrecs = 10)***************************************************************************RESTORE POINT RECORDS***************************************************************************(size = 212, compat size = 212, section max = 2048, section in-use = 0,last-recid= 0, old-recno = 0, last-recno = 0)(extent = 1, blkno = 161, numrecs = 2048)三、控制⽂件的管理规划原则：多路复⽤,建议存放到不同的磁盘或同⼀磁盘不同的分区个数与位置及状态管理:查看控制⽂件的状态是否与参数定义中的相吻合,当数据库发⽣结构修改时，将修改内容同时写⼊控制⽂件备份管理恢复管理新建控制⽂件语句spfile或pfile都可以实现对控制⽂件的个数及位置管理spfile步骤修改spfile参数中的control_files -- alter system ... scope = spfile | both |memory⼀致性关闭数据库增加或减少控制⽂件(cp or mv)启动数据库使⽤spfile验证结果pfile步骤⼀致性关闭数据库修改pfile参数(vi或vim) 修改*.control_files＝......这⼀段增加或减少控制⽂件(cp or mv)启动数据库使⽤pfile验证结果--演⽰spfile修改控制⽂件SQL> show parameter control_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string /u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctl--将控制⽂件减少到⼀个SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl' scope = spfile; System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 251658240 bytesFixed Size 1218796 bytesVariable Size 88082196 bytesDatabase Buffers 159383552 bytesRedo Buffers 2973696 bytesDatabase mounted.--再次查看参数⽂件,已显⽰为⼀个SQL> show parameter control_fileNAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7control_files string /u01/app/oracle/oradata/orcl/control01.ctl--增加控制⽂件(在nomount状态下即可修改)SQL> alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl',2 '/u01/app/oracle/oradata/orcl/control02.ctl',3 '/u01/app/oracle/oradata/orcl/control03.ctl'4 scope = spfile;System altered.--启动时可以看到在实例阶段出现了版本号不⼀致的问题SQL> startup forceORACLE instance started.Total System Global Area 251658240 bytesFixed Size 1218796 bytesVariable Size 88082196 bytesDatabase Buffers 159383552 bytesRedo Buffers 2973696 bytesORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version1051 inconsistent with file '/u01/app/oracle/oradata/orcl/control02.ctl'version 1049--处理办法,⽤版本号⾼的控制⽂件覆盖版本号低的控制⽂件SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl; SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl; SQL> alter database mount;Database altered.SQL> alter database open;Database altered.对于控制⽂件丢失的情况下,通过查看参数⽂件中设置,使⽤操作系统命令逐个查看这些⽂件是否存在SQL> host ls /u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/oradata/orcl/control01.ctlSQL> host ls /u01/app/oracle/oradata/orcl/control02.ctl/u01/app/oracle/oradata/orcl/control02.ctl查看控制⽂件所在的⽬录可⽤空间及控制⽂件的⼤⼩,建议不要超过100MBSQL> host ls /u01/app/oracle/oradata/orcl/c*.ctl -lh-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control01.ctl-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control02.ctl-rw-r----- 1 oracle oinstall 6.8M May 23 10:55 /u01/app/oracle/oradata/orcl/control03.ctlSQL> ! df -hFilesystem Size Used Avail Use% Mounted on/dev/sda2 6.4G 3.9G 2.2G 64% //dev/sdd1 6.8G 3.7G 2.8G 58% /u01/dev/sdc2 1.2G 34M 1.1G 3% /home/dev/sdc1 760M 17M 704M 3% /tmp/dev/sda1 456M 18M 415M 5% /boottmpfs 450M 0 450M 0% /dev/shmnone 450M 104K 450M 1% /var/lib/xenstored控制⽂件的备份热备:alter database backup controlfile to '<dir>'; --热备份控制⽂件alter database backup controlfile to trace as '<dir>' ;--得到建⽴控制⽂件的脚本RMAN:backup current controlfile;backup database include current controlfile;-- 或者设置RMAN 为⾃动备份RMAN > configure controlfile autobackup on;--演⽰备份--⽤于归档模式下的恢复，直接覆盖到控制⽂件SQL> alter database backup controlfile to '/u01/app/oracle/control.bak';Database altered.--⽤于重建控制⽂件SQL> alter database backup controlfile to trace as '/u01/app/oracle/recreate_controlfile.txt'; Database altered.--使⽤RMAN备份RMAN> connect target /;connected to target database: ORCL (DBID=1242732291)RMAN> backup current controlfile; --handle为备份⽂件的路径Starting backup at 23-MAY-10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=148 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetchannel ORA_DISK_1: starting piece 1 at 23-MAY-10channel ORA_DISK_1: finished piece 1 at 23-MAY-10piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_23/o1_mf_ncnnf_TAG20100523T131841_5zkgon2l_.bkp tag=TAG20100523T131841 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05Finished backup at 23-MAY-10RMAN> backup database include current controlfile;Starting backup at 23-MAY-10using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=141 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/orcl/tbs1_1.dbfinput datafile fno=00007 name=/u01/app/oracle/oradata/orcl/tbs1_2.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 23-MAY-10channel ORA_DISK_1: finished piece 1 at 23-MAY-10piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_23/o1_mf_nnndf_TAG20100523T132647_5zkh4sk2_.bkp tag=TAG20100523T132647 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:05:25channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 23-MAY-10channel ORA_DISK_1: finished piece 1 at 23-MAY-10piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_05_23/o1_mf_ncsnf_TAG20100523T132647_5zkhh5st_.bkp tag=TAG20100523T132647 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:14Finished backup at 23-MAY-10RMAN> show all;RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP OFF; # defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # defaultCONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # defaultCONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE MAXSETSIZE TO UNLIMITED; # defaultCONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/10g/dbs/snapcf_orcl.f'; # defaultRMAN> configure controlfile autobackup on; --将控制⽂件⾃动备份功能置为on;new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON;new RMAN configuration parameters are successfully stored控制⽂件的恢复管理控制⽂件版本不⼀致的问题⽤较新版本的控制⽂件覆盖旧版本的控制⽂件直接修改参数control_file丢失问题归档模式下当归档⽇志全的时候,先做全备,然后使⽤备份的控制⽂件恢复即可当归档⽇志不全的时候,先做全备,然后建⽴新的控制⽂件即可⾮归档模式下先做全备,然后建⽴新的控制⽂件即可新建控制⽂件语句数据库处于mount及open状态执⾏alter database backup controlfile to trace as '<dir>';得到建⽴语句注意[no]archievelog [no]resetlogs 两个参数的区别版本不⼀致演⽰SQL> startupORACLE instance started.Total System Global Area 251658240 bytesFixed Size 1218796 bytesVariable Size 88082196 bytesDatabase Buffers 159383552 bytesRedo Buffers 2973696 bytesORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version1051 inconsistent with file '/u01/app/oracle/oradata/orcl/control02.ctl'version 1049--处理办法,⽤版本号⾼的控制⽂件覆盖版本号低的控制⽂件SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl; SQL> host cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl; SQL> alter database mount;Database altered.SQL> alter database open;Database altered.--控制⽂件部分丢失的演⽰，原本有两个控制⽂件，丢失⼀个--处理办法：1.将存在的控制⽂件复制到⽬的路径并更改控制⽂件名字为正确的控制⽂件名称2.修改控控⽂件参数将丢失的控制⽂件去掉(⼀般不建议使⽤)SQL> alter system set control_files = ' /u01/app/oracle/oradata/orcl/c ontrol01.ctl',2 '/u01/app/oracle/oradata/orcl/control02.ctl' scope = spfile;System altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 251658240 bytesFixed Size 1218796 bytesVariable Size 75499284 bytesDatabase Buffers 171966464 bytesRedo Buffers 2973696 bytesORA-00205: error in identifying control file, check alert log for more info[oracle@robinson ~]$ tail -n 100 /u01/app/oracle/admin/orcl/bdump/alert_orcl.log..........................Tue Jun 8 19:03:42 2010starting up 1 shared server(s) ...MMON started with pid=11, OS id=4557CJQ0 started with pid=10, OS id=4555Tue Jun 8 19:03:44 2010ALTER DATABASE MOUNTTue Jun 8 19:03:44 2010ORA-00202: control file: '/u01/app/oracle/10g/dbs/ /u01/app/oracle/oradata/orcl/c ontrol01.ctl' ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Tue Jun 8 19:03:47 2010ORA-205 signalled during: ALTER DATABASE MOUNT...--从警告⽇志中得知，⽂件名为c ontrol01.ctl的⽂件不存在，故将其改为正确的⽂件名。
oracle8管理控制文件和重做日日志文件
2021/2/17
oracle8管理控制文件和重做日日志文件
移动重做日志文件
Open状态下移动redo文件 • 确保处于inactive状态 • Host copy • 执行alter database rename file A to B语句 • 备份控制文件或数据库
学习改变命运,知 识创造未来
2021/2/17
oracle8管理控制文件和重做日日志文件
重做日志文件管理
• 重做日志文件的作用
• 数据库恢复中起到非常重要的作用 • 例程恢复、介质恢复以及事务的撤销
• 重做日志文件组\成员
• Redo01.log\redo02_1.log\redo02_2.log • Maxlogfiles:重做日志文件组的最大数量 • Maxlogmembers：每个组成员的最大数量
• Alter tablespace temp add tempfile ‘%oracle_home%\oradata\orcl\temp01.dbf’
探讨Oracle数据库管理之控制文件
探讨Oracle数据库管理之控制文件杜战伟【摘要】控制文件作为Oracle数据库管理的物理文件之一，数据库的名字以及数据文件的位置信息由它记录。
控制文件一旦损坏，电脑将无法正常工作。
因此控制文件十分重要，其管理的重点在于防御。
本文将对控制文件的相关内容作介绍，为安全管理Oracle控制文件提出了相关的方法。
%The control file was one of the physical files of the Oracle database management,the name and loaction of data files were recorded by it.Once the control file is damaged,the computer will not work properly.So the control file is very important,the focus of its management lies in defense.This article will introduce the related content of the control file, and put forward the related methods for the safety management of Oracle control file.【期刊名称】《电子测试》【年(卷),期】2016(000)010【总页数】3页(P90-91,98)【关键词】Oracle;数据库管理;控制文件【作者】杜战伟【作者单位】西安秦皇医院信息部，西安，710016【正文语种】中文控制文件是一个很小的二进制文件，用于对数据库的维护和记录，无论何时打开Oracle数据库，在此之前Oracle服务器都要对控制文件进行访问。
控制文件一旦出现问题，会导致Oracle数据库无法正常工作。
数据库的启动以及正常运行都离不开控制文件。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1 查看控制文件信息SYS@ prod>select * from v$controlfile;
目前三个控制文件（里面信息都一样）在同一个目录下，都在/dev/sda2磁盘上，很不安全，根据df显示的磁盘信息可以将控制文件放到test1目录下。
2 修改参数文件
1）查看参数文件的位置，当前使用spfile启动
2）SYS@ prod>alter system set control_files='/u01/oradata/prod/control01.ctl',
2 '/u01/oradata/prod/control02.ctl','/u01/oradata/prod/control03.ctl' scope=spfile;
System altered.
3）修改参数文件
使用vi打开initprod.ora 文件在contol_file 后面添加‘/test/contorl04.ctl’并保存退出。
4）关闭数据库SYS@ prod>shutdown immediate;
5）复制控制文件
[root@cuug ~]# cd /u01/oradata/prod
[root@cuug prod]# cp ./control03.ctl /test1/control04.ctl
6）验证控制文件
7 ）注意事项：
a复制控制文件时应该是数据库关闭状态，否则在启库是容易造成数据库SCN号不一致导致数据库无法mount
b 复制时最好使用oracle操作，如果使用root记得更改新文件夹的属组和属主。
（一）只有一个控制文件丢失
1 删除一个控制文件
[root@cuug prod]# rm /u01/oradata/prod/control01.ctl
rm: remove regular file `/u01/oradata/prod/control01.ctl'? y
[root@cuug prod]#
2 关闭数据库SYS@ prod>shutdown immediate;
注：没用控制文件在关闭数据库是容易造成数据库未完全关闭重启时会报ora-01012错误，解决方法是在操作系统层杀掉数据库相关进程，重启即可。
3 重启数据库SYS@ prod>startup
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 385877728 bytes
Database Buffers 130023424 bytes
Redo Buffers 5869568 bytes
ORA-00205: ?????????, ??????, ???????
重启失败报错。
查看告警日志为丢失控制文件
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/prod/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Feb 17 07:14:08 2017
Checker run found 1 new persistent data failures
4 查看现在数据库状态
发现数据库处于nomount状态可以直接拷贝控制文件解决
5复制控制文件
[root@cuug prod]# cp /u01/oradata/prod/control02.ctl /u01/oradata/prod/control01.ctl
6 重启数据库
SYS@ prod>alter database mount;
Database altered.
SYS@ prod>alter database open;
Database altered.
（二）所有的控制文件丢失
1 为了安全期间可以先将所有的控制文件dump到trace中
SYS@ prod>alter database backup controlfile to trace;
Database altered.
将控制文件拷贝到其他位置
SYS@ prod>alter database backup controlfile to trace;
Database altered.
查看dunp文件位置
里面有控制文件的信息。
2 删除控制文件
3 重启数据库报错
SYS@ prod>startup force;
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 385877728 bytes Database Buffers 130023424 bytes
Redo Buffers 5869568 bytes
ORA-00205: ?????????, ??????, ???????
查看告警日志丢失控制文件
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/prod/control03.ctl' ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/prod/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/oradata/prod/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE MOUNT...
Fri Feb 17 07:55:28 2017
Checker run found 2 new persistent data failures
4 重建控制文件
[root@cuug u01]# mkdir script
[root@cuug u01]# cd script/
[root@cuug script]# vi recreate.sql
添加如下信息：
CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS ARCHIVELOG MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/oradata/prod/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/oradata/prod/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/oradata/prod/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/prod/system01.dbf',
'/u01/oradata/prod/sysaux01.dbf',
'/u01/oradata/prod/undotbs01.dbf',
'/u01/oradata/prod/users01.dbf',
'/u01/oradata/prod/example01.dbf'
CHARACTER SET AL32UTF8
重启数据库resetlogs 模式
SYS@ prod>alter database open resetlogs; --
Database altered.
-- --会生成全局检查点，把赃块写一下，会把日志清空，序列号从1开始记。