oracle控制文件

一、基本含义

控制文件时数据库里边非常重要的一类文件，它记录了当前实例连接的数据库的结构和行为，并维护数据库的一致性。初始化文件中描述其位置信息。很小的二进制文件，一般不超过100M，mount情况下读，open下使用，每个文件都不能丢失，否则需要恢复。最多8个，最少1个。初始化大小有create database定义

包含内容：数据库名字和标识、数据库创建的时间戳，表空间名字，数据文件和联机重做日志文件的位置和名字，当前联机重做日志文件的sequence号码，检查点信息，回滚段的开始和结束，联机重做日志的归档信息，备份信息。

二、查看控制文件信息

1、

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/vion/controlfile/current
                                                 .271.844042135, +FRA/vion/cont
                                                 rolfile/current.260.844042139

 

2、参数文件的位置和状态

SQL> select name,value from v$parameter where name='control_files';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
control_files
+DATA/vion/controlfile/current.271.844042135, +FRA/vion/controlfile/current.260.
844042139

 

3、控制文件的名字和状态

SQL> select name,status from v$controlfile;

NAME
--------------------------------------------------------------------------------
STATUS
-------
+DATA/vion/controlfile/current.271.844042135

+FRA/vion/controlfile/current.260.844042139

4、控制文件的记录信息，详细内容

SQL> select type,record_size from v$controlfile_record_section;

TYPE                         RECORD_SIZE
---------------------------- -----------
DATABASE                             316
CKPT PROGRESS                       8180
REDO THREAD                          256
REDO LOG                              72
DATAFILE                             520
FILENAME                             524
TABLESPACE                            68
TEMPORARY FILENAME                    56
RMAN CONFIGURATION                  1108
LOG HISTORY                           56
OFFLINE RANGE                        200

TYPE                         RECORD_SIZE
---------------------------- -----------
ARCHIVED LOG                         584
BACKUP SET                            40
BACKUP PIECE                         736
BACKUP DATAFILE                      200
BACKUP REDOLOG                        76
DATAFILE COPY                        736
BACKUP CORRUPTION                     44
COPY CORRUPTION                       40
DELETED OBJECT                        20
PROXY COPY                           928
BACKUP SPFILE                        124

TYPE                         RECORD_SIZE
---------------------------- -----------
DATABASE INCARNATION                  56
FLASHBACK LOG                         84
RECOVERY DESTINATION                 180
INSTANCE SPACE RESERVATION            28
REMOVABLE RECOVERY FILES              32
RMAN STATUS                          116
THREAD INSTANCE NAME MAPPING          80
MTTR                                 100
DATAFILE HISTORY                     568
STANDBY DATABASE MATRIX              400
GUARANTEED RESTORE POINT             212

TYPE                         RECORD_SIZE
---------------------------- -----------
RESTORE POINT                        212
DATABASE BLOCK CORRUPTION             80
ACM OPERATION                        104
FOREIGN ARCHIVED LOG                 604

37 rows selected.

 

三、文本，转储

文本

SQL> alter database backup controlfile to trace as '/u01/app/oracle/ctl01.ctl';

Database altered

二进制转储

SQL> alter system set events 'immediate trace name controlf level 10';

System altered

SQL> show parameter dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/vio
                                                 n/vion/trace
core_dump_dest                       string      /u01/app/oracle/diag/rdbms/vio
                                                 n/vion/cdump
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/vio
                                                 n/vion/trace

然后可以在user_dump_dest可以查看转出的文件

另外可以查看会话的pid

SQL> select spid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

SPID
------------------------
11120

使用oradebug怎么转储

SQL> oradebug setmypid
Statement processed.

然后

SQL> oradebug dump controlf 3
Statement processed.

注意如果多次转出，名字就都是一样的，比如vion_ora_11120.trc

Trace file /u01/app/oracle/diag/rdbms/vion/vion/trace/vion_ora_11120.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/11.2.0/db_1
System name:    Linux
Node name:      loge0001.localdomain
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: vion
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 11120, image: oracle@loge0001.localdomain (TNS V1-V3)
*** 2014-12-27 17:17:28.048
*** SESSION ID:(51.368) 2014-12-27 17:17:28.048
*** CLIENT ID:() 2014-12-27 17:17:28.048
*** SERVICE NAME:(SYS$USERS) 2014-12-27 17:17:28.048
*** MODULE NAME:(sqlplus@loge0001.localdomain (TNS V1-V3)) 2014-12-27 17:17:28.048
*** ACTION NAME:() 2014-12-27 17:17:28.048
*** TRACE FILE RECREATED AFTER BEING REMOVED ***
*** END OF DUMP ***
*** 2014-12-27 17:17:28.048
Oradebug command 'dump controlf 3' console output: <none>

四、控制文件多功

 

增加控制文件，copy物理文件，然后设置参数即可

alter system set control_files='XXX','XXXctl2.ctl';

然后重启数据库即可

如果是asm存储控制文件的话，可以借助于rman

1、修改控制参数

SQL> alter system set control_files='+DATA/vion/controlfile/current.271.844042135','+FRA/vion/controlfile/current.260.844042139','/u01/app/oracle/vion/control03.ctl' scope=spfile;

System altered.

2、重新启动数据库到nomount

3、登陆rman执行

[oracle@loge0001 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 27 17:44:08 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: VION (not mounted)

RMAN> restore controlfile to '/u01/app/oracle/vion/control03.ctl'  from '+FRA/vion/controlfile/current.260.844042139'
2> ;

Starting restore at 27-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 27-DEC-14

4、打开数据库可

SQL> alter database open;

Database altered.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/vion/controlfile/current
                                                 .271.844042135, +FRA/vion/cont
                                                 rolfile/current.260.844042139,
                                                  /u01/app/oracle/vion/control0
                                                 3.ctl

五、控制文件备份恢复

备份

冷备：关闭数据库备份

热备：

alter database backup controlfile to 'XXX';

alter database backup controlfile to trace as 'XXX';得到的是重建控制文件的脚本

rman备：

backup current controlfiie;归档 模式下，否则恢复时不可用，将自动备份在闪回区

backup database include current controlfile;

另外可以设置控制文件自动备份功能

恢复

1、如果是版本不一致，使用新的覆盖旧的，或者控制参数直接修改为新的控制文件来恢复

2、如果全部丢失了的话

a、非归档，先全备，然后建立新的控制文件（可以从转储文件或者文本文件查看），然后重建日志文件

b、归档模式日志并且在，先全备，使用备份控制文件恢复

   这个时候使用备份的控制文件恢复的时候，如果提示需要使用resetlogs时候，先使用

   alter database recover database using backup controlfile [until change XXX];看看

   不行的话使用alter database open resetlogs;

   最后使用隐藏参数恢复下，或者重建控制文件来恢复

  使用隐藏参数：alter system set  '_allow_resetlogs_corruption'=true scope=spfile;

   然后reset打开数据库，然后关闭隐藏参数，

   alter system reset "_allow_resetlogs_corruption" scoope=spfile sid="*"

   另外，recover database using backup controlfile;这个情况下是，数据文件和控制文件不一致造成的，如果有归档或者日志文件按需要一个一个试了，然后reset打开数据库

  注意，如果不能使用日志和归档恢复的话，最后才使用隐藏参数

c、归档但日志不全，先全备，后建立新的控制文件

 

全丢了，非归档全丢失恢复创建,我这里便是用我转出的脚本里边copy的

--STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "VION"  noarchivelog noresetlogs  
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DATA/vion/onlinelog/group_1.272.844042145',
    '+FRA/vion/onlinelog/group_1.261.844042151'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '+DATA/vion/onlinelog/group_2.273.844042151',
    '+FRA/vion/onlinelog/group_2.262.844042159'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '+DATA/vion/onlinelog/group_3.274.844042161',
    '+FRA/vion/onlinelog/group_3.263.844042167'
  ) SIZE 50M BLOCKSIZE 512

DATAFILE
  '+DATA/vion/datafile/system.267.844041861',
  '+DATA/vion/datafile/sysaux.268.844041863',
  '+DATA/vion/datafile/undotbs1.269.844041863',
  '+DATA/vion/datafile/users.270.844041863',
  '+DATA/vion/datafile/example.276.844042275'
CHARACTER SET AL32UTF8
;

然后alter database open

如果需要恢复则执行recover database;

如果日志丢了，则需要加隐藏参数