control file（控制檔案）

pwz1688發表於2014-04-09
一、控制檔案簡介
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章