control file（控制文件）

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

crbzijz188696507

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

阅读量291

点赞数

文章标签：数据库操作系统网络

一、控制文件简介

A control file contains information about the associated database that is required for access by an instance, both at startup and during normal operation. Control file information can be modified only by Oracle; no database administrator or user can

edit a control file. Among other things, a control file contains information such as:

■ The database name （数据库名）

■ The timestamp of database creation（数据库创建时间）

■ The names and locations of associated datafiles and redo log files（数据文件和redo文件名称及存放位置）

■ Tablespace information （表空间信息）

■ Datafile offline ranges （offline数据文件信息）

■ The log history （历史日志信息）

■ Archived log information （归档日志信息）

■ Backup set and backup piece information（备份集及备份文件信息）

■ Backup datafile and redo log information （数据文件与日志文件备份信息）

■ Datafile copy information （数据文件复制信息）

■ The current log sequence number （当前日志序列号）

■ Checkpoint information（检查点信息）

二、转储控制文件，查看控制文件内部详细信息

SQL> show parameter dump

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /u01/app/oracle/oradata/PROD/b dump

core_dump_dest string /u01/app/oracle/oradata/PROD/c dump

max_dump_file_size string UNLIMITED

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/oradata/PROD/u dump

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

注意：告警日志存放在bdump下，自己转储下来的文件存放在udump目录下

--进入udump目录，读取刚刚转储的控制文件信息

[root@gc1 ~]# su - oracle

[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/udump

[oracle@gc1 udump]$ ls -lt | grep prod_ora

-rw-r----- 1 oracle oinstall 40352 Apr 2 23:27 prod_ora_4180.trc

-rw-r----- 1 oracle oinstall 656 Apr 2 18:51 prod_ora_3485.trc

-rw-r----- 1 oracle oinstall 599 Apr 2 18:51 prod_ora_3436.trc

-rw-r----- 1 oracle oinstall 2229 Apr 2 18:51 prod_ora_3340.trc

-rw-r----- 1 oracle oinstall 656 Apr 2 18:25 prod_ora_3339.trc

-rw-r----- 1 oracle oinstall 627 Apr 2 18:25 prod_ora_3286.trc

-rw-r----- 1 oracle oinstall 656 Apr 1 23:26 prod_ora_4264.trc

-rw-r----- 1 oracle oinstall 599 Apr 1 23:26 prod_ora_4215.trc

-rw-r----- 1 oracle oinstall 785 Mar 31 22:32 prod_ora_4342.trc

-rw-r----- 1 oracle oinstall 656 Mar 31 22:09 prod_ora_4214.trc

-rw-r----- 1 oracle oinstall 599 Mar 31 22:09 prod_ora_4165.trc

-rw-r----- 1 oracle oinstall 785 May 4 2013 prod_ora_6620.trc

-rw-r----- 1 oracle oinstall 1824 May 3 2013 prod_ora_3815.trc

-rw-r----- 1 oracle oinstall 656 May 3 2013 prod_ora_3780.trc

-rw-r----- 1 oracle oinstall 627 May 3 2013 prod_ora_3683.trc

-rw-r----- 1 oracle oinstall 1814 May 3 2013 prod_ora_4082.trc

-rw-r----- 1 oracle oinstall 656 May 3 2013 prod_ora_4079.trc

-rw-r----- 1 oracle oinstall 599 May 3 2013 prod_ora_4030.trc

-rw-r----- 1 oracle oinstall 656 May 3 2013 prod_ora_4512.trc

-rw-r----- 1 oracle oinstall 599 May 3 2013 prod_ora_4463.trc

-rw-r----- 1 oracle oinstall 756 May 3 2013 prod_ora_4454.trc

-rw-r----- 1 oracle oinstall 656 May 3 2013 prod_ora_4453.trc

-rw-r----- 1 oracle oinstall 599 May 3 2013 prod_ora_4404.trc

-rw-r----- 1 oracle oinstall 784 May 3 2013 prod_ora_4395.trc

-rw-r----- 1 oracle oinstall 656 May 3 2013 prod_ora_4394.trc

-rw-r----- 1 oracle oinstall 599 May 3 2013 prod_ora_4345.trc

-rw-r----- 1 oracle oinstall 784 May 3 2013 prod_ora_4344.trc

-rw-r----- 1 oracle oinstall 1842 May 2 2013 prod_ora_3688.trc

-rw-r----- 1 oracle oinstall 656 May 2 2013 prod_ora_3687.trc

-rw-r----- 1 oracle oinstall 627 May 2 2013 prod_ora_3628.trc

-rw-r----- 1 oracle oinstall 1830 May 2 2013 prod_ora_3671.trc

-rw-r----- 1 oracle oinstall 656 May 2 2013 prod_ora_3670.trc

-rw-r----- 1 oracle oinstall 627 May 2 2013 prod_ora_3618.trc

-rw-r----- 1 oracle oinstall 926 Apr 28 2013 prod_ora_27031.trc

-rw-r----- 1 oracle oinstall 1852 Apr 26 2013 prod_ora_3660.trc

-rw-r----- 1 oracle oinstall 562664 Apr 26 2013 prod_ora_3652.trc

-rw-r----- 1 oracle oinstall 599 Apr 26 2013 prod_ora_3603.trc

-rw-r----- 1 oracle oinstall 658 Sep 27 2012 prod_ora_10293.trc

-rw-r----- 1 oracle oinstall 601 Sep 27 2012 prod_ora_10270.trc

-rw-r----- 1 oracle oinstall 786 Sep 27 2012 prod_ora_10242.trc

-rw-r----- 1 oracle oinstall 601 Sep 27 2012 prod_ora_10218.trc

-rw-r----- 1 oracle oinstall 658 Sep 27 2012 prod_ora_10241.trc

-rw-r----- 1 oracle oinstall 785 Sep 27 2012 prod_ora_10196.trc

-rw-r----- 1 oracle oinstall 655 Sep 27 2012 prod_ora_4195.trc

-rw-r----- 1 oracle oinstall 627 Sep 27 2012 prod_ora_4107.trc

-rw-r----- 1 oracle oinstall 783 Sep 19 2012 prod_ora_4652.trc

-rw-r----- 1 oracle oinstall 655 Sep 19 2012 prod_ora_4646.trc

-rw-r----- 1 oracle oinstall 599 Sep 19 2012 prod_ora_4625.trc

-rw-r----- 1 oracle oinstall 599 Sep 19 2012 prod_ora_3631.trc

--读取控制文件详细信息

[oracle@gc1 udump]$ more prod_ora_4180.trc

/u01/app/oracle/oradata/PROD/udump/prod_ora_4180.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

System name: Linux

Node name: gc1

Release: 2.6.18-164.el5

Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009

Machine: i686

Instance name: PROD

Redo thread mounted by this instance: 1

Oracle process number: 26

Unix process pid: 4180, image: oracle@gc1 (TNS V1-V3)

*** 2014-04-02 23:27:29.393

*** SERVICE NAME:(SYS$USERS) 2014-04-02 23:27:29.393

*** SESSION ID:(289.7) 2014-04-02 23:27:29.393

DUMP OF CONTROL FILES, Seq # 361 = 0x169

V10 STYLE FILE HEADER:

Compatibility Vsn = 169869312=0xa200000

Db ID=206258609=0xc4b41b1, Db Name='PROD'

Activation ID=0=0x0

Control Seq=361=0x169, File size=370=0x172

File Number=0, Blksiz=16384, File Type=1 CONTROL

***************************************************************************

DATABASE ENTRY

***************************************************************************

(size = 316, compat size = 316, section max = 1, section in-use = 1,

last-recid= 0, old-recno = 0, last-recno = 0)

(extent = 1, blkno = 1, numrecs = 1)

09/19/2012 16:17:53

DB Name "PROD"

Database flags = 0x00404000 0x00001000

Controlfile Creation Timestamp 09/19/2012 16:17:55

Incmplt recovery scn: 0x0000.00000000

Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp 09/19/2012 16:17:53

Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp 01/01/1988 00:00:00

Redo Version: compatible=0xa200000

#Data files = 4, #Online files = 4

Database checkpoint: Thread=1 scn: 0x0000.00061a49

Threads: #Enabled=1, #Open=1, Head=1, Tail=1

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

--More--(4%)

在此省略其它信息，不一一列举，我们可以通过以上操作，生成一个控制文件，然后一一细读里面的详细信息。

注意控制文件是在mount状态打开的，下面以实例验证

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 121635664 bytes

Database Buffers 293601280 bytes

Redo Buffers 2973696 bytes

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

PROD STARTED

SQL> col name for a50

SQL> select file#,name from v$datafile;

select file#,name from v$datafile

ERROR at line 1:

ORA-01507: database not mounted

SQL> select * from v$log;

select * from v$log

ERROR at line 1:

ORA-01507: database not mounted

SQL> col member for a50;

SQL> select * from v$logfile;

select * from v$logfile

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

PROD MOUNTED

SQL> select file#,name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM

---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

1 1 10 104857600 2 NO INACTIVE 355469 03-MAY-13

3 1 12 104857600 2 NO CURRENT 399207 02-APR-14

2 1 11 104857600 2 NO INACTIVE 377693 02-APR-14

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------- -------------------------------------------------- ---

1 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo01.log NO

1 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo01.log NO

2 STALE ONLINE /u01/app/oracle/oradata/PROD/disk1/redo02.log NO

2 STALE ONLINE /u01/app/oracle/oradata/PROD/disk2/redo02.log NO

3 ONLINE /u01/app/oracle/oradata/PROD/disk1/redo03.log NO

3 ONLINE /u01/app/oracle/oradata/PROD/disk2/redo03.log NO

6 rows selected.

--控制文件通过三个scn保证数据库的一致性，下面我们获取这三个scn

--数据库的scn号，从控制文件读取

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

------------------

407806

--数据文件的scn号，从控制文件读取（即记忆中的数据文件scn号）

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 407806

2 407806

3 407806

4 407806

--数据文件头部scn号，是进程，通过系统扫描获取（即真实的数据文件scn号）

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#

---------- ------------------

1 407806

2 407806

3 407806

4 407806

注意：只有上面这三个scn一样，才可开库（alter database open），scn实际是一个时间点，可以通过

三、添加控制文件（一般存三个控制文件，其内容一样）

--查看当前已有控制文件信息

SQL> show parameter control;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/PROD/d

isk1/control01.ctl, /u01/app/o

racle/oradata/PROD/disk2/contr

ol01.ctl, /u01/app/oracle/orad

ata/PROD/disk3/control01.ctl

SQL> select name from v$controlfile;

NAME

--------------------------------------------------

/u01/app/oracle/oradata/PROD/disk1/control01.ctl

/u01/app/oracle/oradata/PROD/disk2/control01.ctl

/u01/app/oracle/oradata/PROD/disk3/control01.ctl

3.1 修改spfile文件方式，进行控制文件添加（数据库必须以spfile文件方式启动，才可做此修改操作）

--查看数据库的启动方式（pfile）

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string

--进行控制文件修改（这里不做实际添加操作，若要添加，可在后面加上新增的控制文件即可）

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

2 '/u01/app/oracle/oradata/PROD/disk3/control01.ctl' scope=spfile;

alter system set control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE specified at startup

以上报spfile文件不存在，查看初始化参数文件信息，进行确认

[oracle@gc1 disk3]$ cd $ORACLE_HOME/dbs

[oracle@gc1 dbs]$ ls -lt | grep PROD

-rw-r----- 1 oracle oinstall 1536 May 4 2013 orapwPROD

-rw-r--r-- 1 oracle oinstall 653 May 3 2013 initPROD.ora

-rw-rw---- 1 oracle oinstall 24 Sep 19 2012 lkPROD

-rw-rw---- 1 oracle oinstall 1544 Sep 19 2012 hc_PROD.dat

--创建spfile文件

SQL> create spfile from pfile;

File created.

--修改spfile文件参数control_files

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

2 '/u01/app/oracle/oradata/PROD/disk3/control01.ctl' scope=spfile;

alter system set control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE specified at startup

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 121635664 bytes

Database Buffers 293601280 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/10.2.0

/db_1/dbs/spfilePROD.ora

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl','/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

2 '/u01/app/oracle/oradata/PROD/disk3/control01.ctl' scope=spfile;

System altered.

3.2 修改pfile文件，进行控制文件添加（修改pfile文件时，数据必须是关库或nomount状态才可）

--关库，利用pfile文件启库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initPROD.ora

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 121635664 bytes

Database Buffers 293601280 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

--修改spfile文件名，主要是为了下次启动默认采用pfile的启动模式

[oracle@gc1 dbs]$ mv spfilePROD.ora spfilePROD.ora.bak

--关库，修改pfile文件，并生成添加的controlfile文件

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--下面红色内容为新增的控制文件

[oracle@gc1 dbs]$ vi initPROD.ora

sga_target=400m

db_name=PROD

control_files='/u01/app/oracle/oradata/PROD/disk1/control01.ctl',

'/u01/app/oracle/oradata/PROD/disk2/control01.ctl',

'/u01/app/oracle/oradata/PROD/disk3/control01.ctl',

'/u01/app/oracle/oradata/PROD/disk4/control01.ctl'

undo_management=auto

undo_tablespace=undotbs

background_dump_dest=/u01/app/oracle/oradata/PROD/bdump

core_dump_dest=/u01/app/oracle/oradata/PROD/cdump

user_dump_dest=/u01/app/oracle/oradata/PROD/udump

local_listener='lsnr2'

undo_retention=5400

sessions=300

job_queue_processes=15

shared_server_sessions=200

DISPATCHERS = '(PROTOCOL=TCP)(DISPATCHERS=3)'

max_dispatchers=10

shared_servers=10

max_shared_servers=30

recyclebin=on

"initPROD.ora" 49L, 705C written

--复制一份原来的控制文件至要新添加的目录（此文件路径必须与pfile中新添加的文件路径保持一致，且必须在关库情况下复制控制文件）

[oracle@gc1 disk4]$ cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /u01/app/oracle/oradata/PROD/disk4/control01.ctl

--启库并查看最新的控制文件信息

SQL> startup

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1219760 bytes

Variable Size 121635664 bytes

Database Buffers 293601280 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/PROD/d

isk1/control01.ctl, /u01/app/o

racle/oradata/PROD/disk2/contr

ol01.ctl, /u01/app/oracle/orad

ata/PROD/disk3/control01.ctl,

/u01/app/oracle/oradata/PROD/d

isk4/control01.ctl

SQL> select name from v$controlfile ;

NAME

----------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/PROD/disk1/control01.ctl

/u01/app/oracle/oradata/PROD/disk2/control01.ctl

/u01/app/oracle/oradata/PROD/disk3/control01.ctl

/u01/app/oracle/oradata/PROD/disk4/control01.ctl

由此可见，添加控制文件成功

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

转载于:http://blog.itpub.net/21251711/viewspace-1139021/

crbzijz188696507

关注

0
点赞
踩
0

收藏

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

一、控制文件简介 A control file contains information about the associated database that is required for access by...
复制链接

扫一扫