Oracle控制文件时一个很小的二进制文件,不能手动修改。oracle数据库通过控制文件保持数据库的完整性,一旦控制文件被破坏数据库将无法启动;因此建议采用多路控制文件或者备份控制文件的方法。
一、控制文件主要包括下面内容:
1.数据库名称(控制文件所属数据库的名字,一个控制文件只能属一个数据库)
2.数据库创建时间
3.数据文件的名称,位置,联机,脱机状态信息
4.所有表空间信息
5.当前日志序列号
6.检查点信息
7. undo段的起始点和结束点
8. redo日志归档信息(日志归档时记录)
9. 备份信息(由RMAN负责维护)
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /oracle/products/diag/rdbms/wo
lf/wolf/trace
core_dump_dest string /oracle/products/diag/rdbms/wo
lf/wolf/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /oracle/products/diag/rdbms/wo
lf/wolf/trace
SQL> alter database backup controlfile to trace;
Database altered.
下面是控制文件信息
日志文件的信息下面可以看
二、备份控制文件
备份控制文件的原则是:在数据库物理结构发生变化后备份控制文件
1、利用初始化参数文件多元化控制文件
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/products/oradata/wolf/control01.ctl
/oracle/products/flash_recovery_area/wolf/control02.ctl
SQL> alter system set control_files='/oracle/products/oradata/wolf/control01.ctl','/oracle/products/flash_recovery_area/wolf/control02.ctl','/oracle/products/flash_recovery_area/wolf/control03.ctl' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host cp /oracle/products/oradata/wolf/control01.ctl /oracle/products/flash_recovery_area/wolf/control03.ctl
如果这里为cp过去,会报错
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2207128 bytes
Variable Size 1090519656 bytes
Database Buffers 469762048 bytes
Redo Buffers 7520256 bytes
Database mounted.
Database opened.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/products/oradata/wolf/control01.ctl
/oracle/products/flash_recovery_area/wolf/control02.ctl
/oracle/products/flash_recovery_area/wolf/control03.ctl
2、直接备份
SQL> alter database backup controlfile to '/oracle/products/control.bak';
Database altered.
oracle:/oracle/products# ls
11 cfgtoollogs control.bak flash_recovery_area oradiag_oracle
admin checkpoints diag oradata
oracle:/oracle/products# pwd
/oracle/products
3、将控制文件备份为文本文件(备份到oracle\base\admin\sid\udump目录下的跟踪文件中,将在跟踪文件中生成一个SQL脚本)
恢复也很简单,找到trace文件,打开找到类似下面内容的:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
直接创建为sql文件,然后通过@调用。
然后alter database open rsetlogs;
Database altered.
oracle:/oracle/products/diag/rdbms/wolf/wolf/trace# pwd
/oracle/products/diag/rdbms/wolf/wolf/trace
oracle:/oracle/products/diag/rdbms/wolf/wolf/trace# cat wolf_ora_9306154.trc
Trace file /oracle/products/diag/rdbms/wolf/wolf/trace/wolf_ora_9306154.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/products//11/db
System name: AIX
Node name: oracle
Release: 1
Version: 6
Machine: 00CA4DFF4C00
Instance name: wolf
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 9306154, image: oracle@oracle (TNS V1-V3)
*** 2012-07-15 16:36:22.668
*** SESSION ID:(191.3) 2012-07-15 16:36:22.668
*** CLIENT ID:() 2012-07-15 16:36:22.668
*** SERVICE NAME:(SYS$USERS) 2012-07-15 16:36:22.668
*** MODULE NAME:(sqlplus@oracle (TNS V1-V3)) 2012-07-15 16:36:22.668
*** ACTION NAME:() 2012-07-15 16:36:22.668
kwqmnich: current time:: 21: 36: 22: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure
*** 2012-07-15 16:36:23.269
kwqinfy: Call kwqrNondurSubInstTsk
*** 2012-07-15 17:14:00.020
-- 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=/oracle/arc_%s%t%r.log
--
-- DB_UNIQUE_NAME="wolf"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER 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 NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WOLF" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/products/oradata/wolf/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/products/oradata/wolf/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/products/oradata/wolf/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/products/oradata/wolf/system01.dbf',
'/oracle/products/oradata/wolf/sysaux01.dbf',
'/oracle/products/oradata/wolf/undotbs01.dbf',
'/oracle/products/oradata/wolf/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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 '/oracle/products/flash_recovery_area/WOLF/archivelog/2012_07_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/products/flash_recovery_area/WOLF/archivelog/2012_07_15/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 '/oracle/products/oradata/wolf/temp01.dbf'
SIZE 20971520 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 PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WOLF" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/products/oradata/wolf/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/oracle/products/oradata/wolf/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/oracle/products/oradata/wolf/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oracle/products/oradata/wolf/system01.dbf',
'/oracle/products/oradata/wolf/sysaux01.dbf',
'/oracle/products/oradata/wolf/undotbs01.dbf',
'/oracle/products/oradata/wolf/users01.dbf'
CHARACTER SET WE8MSWIN1252
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- 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 '/oracle/products/flash_recovery_area/WOLF/archivelog/2012_07_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/products/flash_recovery_area/WOLF/archivelog/2012_07_15/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 '/oracle/products/oradata/wolf/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
oracle:/oracle/products/diag/
4、在rman中可以开启自动备份控制文件
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 7.42M DISK 00:00:00 10-JUL-12
BP Key: 8 Status: AVAILABLE Compressed: NO Tag: TAG20120710T163424
Piece Name: /oracle/products/flash_recovery_area/WOLF/autobackup/2012_07_10/o1_mf_s_788286864_7zs7z0fr_.bkp
SPFILE Included: Modification time: 09-JUL-12
SPFILE db_unique_name: WOLF
Control File Included: Ckp SCN: 1347269 Ckp time: 10-JUL-12
RMAN> show all
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name WOLF are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/products/11/db/dbs/snapcf_wolf.f'; # default
Oracle推荐使用第二种方式来备份控制文件。第一种方式备份的控制文件,一般用于全库一致性恢复。而第三种方式备份控制文件会丢失归档日志历史等许多信息。在重建控制文件时,只读表空间和脱机的数据文件的处理相对比较复杂(这段话网上看到的,觉得很有道理)
三、控制文件的管理方式
对控制文件的管理原则:
1.明确控制文件的名称和存储路径
参数设置错误将无法打开数据库,数据库打开以后,实例将同时写入所有的控制文件但是只会读取第一个控制文件的内容。
2.为数据库创建多路控制文件
a.多路控制文件内容必须完全一样,oracle实例同时将内容写入到control_files变量所设置的控制文件中。
b.初始化参数control_files中列出的第一个文件是数据库运行期间唯一可读取的控制文件。
c.创建,恢复和备份控制文件必须在数据库关闭的状态下运行,这样才能保证操作过程中控制文件不被修改。
d.数据库运行期间如果一个控制文件变为不可用,那么实例将不再运行,应该终止这个实例,并对破坏的控制文件进行修复。
3.将多路控制文件放在不同的硬盘上
4.采用操作系统镜像方式备份控制文件
5.手工方式备份控制文件
应该及时备份特别是发生了如下的操作的时候:
添加删除重命名数据文件
添加删除表空间,改变表空间读写状态
添加删除重做日志文件