控制文件-control file

最新推荐文章于 2021-08-08 10:26:01 发布

cuandan3585

最新推荐文章于 2021-08-08 10:26:01 发布

阅读量198

点赞数

什么是控制文件

Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:

The database name
Names and locations of associated datafiles and online redo log files
The timestamp of the database creation
The current log sequence number
Checkpoint information

The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You might also need to create control files later, if you lose control files or want to change particular settings in the control files.

管理控制文件的准则

1）提供控制文件的路径和名称，需要查看参数文件中的control_files值

SYS@PROD>show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/PROD/d
                                                 isk1/control01.ctl, /u01/app/o
                                                 racle/oradata/PROD/disk1/contr
                                                 ol02.ctl, /u01/app/oracle/orad
                                                 ata/PROD/disk1/control03.ctl

2）控制文件需要在不同的物理磁盘做镜像

3）控制文件的备份，这个是重中之重。在下面操作后，需要进行控制文件的备份

Adding, dropping, or renaming datafiles
Adding or dropping a tablespace, or altering the read-write state of the tablespace
Adding or dropping redo log files or groups

备份控制文件

1）备份控制文件到一个二进制文件

SYS@PROD>alter database backup controlfile to '/home/oracle/controlfile_20140113.ora' ;

Database altered.

[oracle@ocm1 ~]$ strings controlfile_20140113.ora | more
}|{z
PROD
TAG20140113T154832
1PROD
1PROD
PROD
PROD
/u01/app/oracle/oradata/PROD/disk1/redo01.log
/u01/app/oracle/oradata/PROD/disk1/redo02.log
/u01/app/oracle/oradata/PROD/disk1/redo03.log
/u01/app/oracle/oradata/PROD/disk1/system01.dbf
/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
/u01/app/oracle/oradata/PROD/disk1/temp01.dbf
/u01/app/oracle/oradata/PROD/disk1/users01.dbf
/u01/app/oracle/oradata/PROD/disk1/undotbs02.dbf
/u01/app/oracle/oradata/PROD/disk1/bigtbs01.dbf
/u01/app/oracle/oradata/PROD/disk1/redo04.log
/u01/app/oracle/oradata/PROD/disk1/redo05.log
/u01/app/oracle/oradata/PROD/disk1/redo06.log
/u01/app/oracle/oradata/PROD/disk1/redo01.log
/u01/app/oracle/oradata/PROD/disk1/redo02.log
/u01/app/oracle/oradata/PROD/disk1/redo03.log
/u01/app/oracle/oradata/PROD/disk1/system01.dbf
/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf
/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
/u01/app/oracle/oradata/PROD/disk1/temp01.dbf
/u01/app/oracle/oradata/PROD/disk1/users01.dbf
/u01/app/oracle/oradata/PROD/disk1/undotbs02.dbf
/u01/app/oracle/oradata/PROD/disk1/bigtbs01.dbf
/u01/app/oracle/oradata/PROD/disk1/redo04.log
/u01/app/oracle/oradata/PROD/disk1/redo05.log
/u01/app/oracle/oradata/PROD/disk1/redo06.log
SYSTEM
UNDOTBS
SYSAUX
TEMPTS1
USERS
UNDOTBS2
BIGTBS
SYSTEM
UNDOTBS
SYSAUX
TEMPTS1
USERS
UNDOTBS2
BIGTBS
/u01/app/oracle/1_11_835022046.dbf
/u01/app/oracle/1_12_835022046.dbf
/u01/app/oracle/1_11_835022046.dbf
PROD
UNNAMED_INSTANCE_1
[oracle@ocm1 ~]$

2）提供创建控制文件的SQL语句

SYS@PROD>alter database backup controlfile to trace as '/home/oracle/controlfile_trace.ora';

Database altered.

[oracle@ocm1 ~]$ more controlfile_trace.ora
-- 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="PROD"
--
-- 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/archlog'
-- 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 NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
    '/u01/app/oracle/oradata/PROD/disk1/redo01.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo04.log'
) SIZE 100M,
GROUP 2 (
    '/u01/app/oracle/oradata/PROD/disk1/redo02.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
) SIZE 100M,
GROUP 3 (
    '/u01/app/oracle/oradata/PROD/disk1/redo03.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo06.log'
) SIZE 100M
-- STANDBY LOGFILE

DATAFILE
'/u01/app/oracle/oradata/PROD/disk1/system01.dbf',
'/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf',
'/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf',
'/u01/app/oracle/oradata/PROD/disk1/users01.dbf',
'/u01/app/oracle/oradata/PROD/disk1/undotbs02.dbf',
'/u01/app/oracle/oradata/PROD/disk1/bigtbs01.dbf'
CHARACTER SET US7ASCII
;

-- 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/archlog/1_1_835022046.dbf';
-- 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 TEMPTS1 ADD TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp01.dbf'
SIZE 20971520 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.
-- 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 "PROD" RESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
    '/u01/app/oracle/oradata/PROD/disk1/redo01.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo04.log'
) SIZE 100M,
GROUP 2 (
    '/u01/app/oracle/oradata/PROD/disk1/redo02.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo05.log'
) SIZE 100M,
GROUP 3 (
    '/u01/app/oracle/oradata/PROD/disk1/redo03.log',
    '/u01/app/oracle/oradata/PROD/disk1/redo06.log'
) SIZE 100M
-- STANDBY LOGFILE

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

3）使用rman 备份控制文件

RMAN> backup current controlfile format '/u01/app/oracle/backup/controlfile_%U';

Starting backup at 13-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=309 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 13-JAN-14
channel ORA_DISK_1: finished piece 1 at 13-JAN-14
piece handle=/u01/app/oracle/backup/controlfile_01otvndf_1_1 tag=TAG20140113T155423 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JAN-14

控制文件的日常操作

1）控制文件的添加、删除、修改路径、重命名。说明一下，这些操作同一个控制文件损坏，有控制文件是好的情况恢复，操作步骤是一致的。如果单独一个控制文件损坏，我们可以把镜像的控制文件拷贝过来，启动数据库，这样数据库不需要恢复，这就是建议将控制文件存放在不同的物理磁盘的好处。

下面以一个控制文件损坏为例，演示其操作过程。

关闭数据库，删除control03.ctl控制文件

SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@ocm1 oracle]$ cd oradata/PROD/disk1
[oracle@ocm1 disk1]$ ls
bigtbs01.dbf control02.ctl redo01.log redo03.log redo05.log sysaux01.dbf temp01.dbf undotbs02.dbf
control01.ctl control03.ctl redo02.log redo04.log redo06.log system01.dbf undotbs01.dbf users01.dbf
[oracle@ocm1 disk1]$
[oracle@ocm1 disk1]$ rm -f control03.ctl

启动数据库

SYS@PROD>startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             159383776 bytes
Database Buffers          360710144 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

在alert log 日志中，我们可以看到那个日志丢失了。

Mon Jan 13 16:09:55 2014
ALTER DATABASE MOUNT
Mon Jan 13 16:09:56 2014
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jan 13 16:09:56 2014
ORA-205 signalled during: ALTER DATABASE MOUNT...

关闭数据库

SYS@PROD>shutdown abort
ORACLE instance shut down.

拷贝其他控制文件并重命名为/u01/app/oracle/oradata/PROD/disk1/control03.ctl
[oracle@ocm1 oracle]$ cp /u01/app/oracle/oradata/PROD/disk1/control02.ctl /u01/app/oracle/oradata/PROD/disk1/control03.ctl

启动数据库

SYS@PROD>startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             159383776 bytes
Database Buffers          360710144 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

2）使用alter database backup controlfile to '/home/oracle/controlfile_20140113.ora' ; 备份的控制文件恢复，只要使用操作系统级别的拷贝即可

例如：cp /home/oracle/controlfile_20140113.ora /u01/app/oracle/oradata/PROD/disk1/control03.ctl

3）控制文件全部损坏，但是有rman备份

备份控制文件

RMAN> backup current controlfile format '/u01/app/oracle/backup/controlfile_%U';

Starting backup at 13-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=308 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 13-JAN-14
channel ORA_DISK_1: finished piece 1 at 13-JAN-14
piece handle=/u01/app/oracle/backup/controlfile_02otvomu_1_1 tag=TAG20140113T161630 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JAN-14

关闭数据库并删除全部控制文件

SYS@PROD>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@ocm1 disk1]$ rm -f control0*

启动数据库到mount

SYS@PROD>startup nomount
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size                  1220384 bytes
Variable Size             159383776 bytes
Database Buffers          360710144 bytes
Redo Buffers                2973696 bytes
SYS@PROD>
SYS@PROD>alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

在alert log日志中可以看到如下信息

Mon Jan 13 16:19:35 2014
alter database mount
Mon Jan 13 16:19:35 2014
ORA-00202: control file: '/u01/app/oracle/oradata/PROD/disk1/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Mon Jan 13 16:19:35 2014
ORA-205 signalled during: alter database mount...

使用rman 进行恢复

RMAN> restore controlfile from '/u01/app/oracle/backup/controlfile_02otvomu_1_1';

Starting restore at 13-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=321 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/PROD/disk1/control01.ctl
output filename=/u01/app/oracle/oradata/PROD/disk1/control02.ctl
output filename=/u01/app/oracle/oradata/PROD/disk1/control03.ctl
Finished restore at 13-JAN-14

启动数据库

SYS@PROD>alter database mount;

Database altered.

SYS@PROD>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@PROD>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'

检查一下

SYS@PROD>select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
789731

SYS@PROD>
SYS@PROD>select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            789731
            789731
            789731
            789731
            789731
            789731

6 rows selected.

SYS@PROD>select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            790006
            790006
            790006
            790006
            790006
            790006

6 rows selected.

SYS@PROD>alter system set "_allow_resetlogs_corruption"=true scope=spfile;

System altered.

SYS@PROD>

SYS@PROD>shutdown abort
ORACLE instance shut down.

启动数据库到mount状态

SYS@PROD>recover database using backup controlfile until cancel;
ORA-00279: change 789867 generated at 01/13/2014 16:39:09 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archlog/1_1_836757538.dbf
ORA-00280: change 789867 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/u01/app/oracle/archlog/1_1_836757538.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk1/system01.dbf'

SYS@PROD>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SYS@PROD>startup mount;
ORACLE instance started.

Database altered.

SYS@PROD>

4）使用controlfile 创建语句，这个主要就是脚本，如果没有controlfile脚本的备份，那就需要自己根据已知的信息来写了。恢复过程同步骤3

需要说明的是，步骤3和4都开启了隐含参数。如果可能需要重建库。

控制文件相关视图

View
Description

V$DATABASE

Displays database information from the control file

V$CONTROLFILE

Lists the names of control files

V$CONTROLFILE_RECORD_SECTION

Displays information about control file record sections

V$PARAMETER

Can be used to display the names of control files as specified in the CONTROL_FILES initialization parameter

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

转载于:http://blog.itpub.net/11590946/viewspace-1070096/

cuandan3585

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
控制文件-control file

什么是控制文件 Every Oracle database has a control file. A control file is a small binary file that records the ...
复制链接

扫一扫