Oracle 控制檔案(CONTROLFILE)

分類： Oracle 體系結構 2010-07-15 16:35 2001人閱讀評論(0) 舉報

--=============================

-- Oracle 控制檔案(CONTROLFILE)

--=============================

一、Oracle 控制檔案

為二進位制檔案，初始化大小由CREATE DATABASE指定,可以使用RMAN備份

記錄了當前資料庫的結構資訊,同時也包含資料檔案及日誌檔案的資訊以及相關的狀態,歸檔資訊等等

在引數檔案中描述其位置，個數等等。通常採用分散放開，多路複用的原則。在mount階段被讀取，open階段一直被使用

維護資料庫一致性(資料庫啟動時會比較控制檔案與聯機日誌檔案中的ckpt,即起始scn號，如相等則正常啟動，否則需要介質恢復)

一個控制檔案只能屬於一個資料庫

控制檔案的任意修改將寫入到初始化引數中指定的所有控制檔案中，讀取時則僅讀取第一個控制檔案

控制檔案只能連線一個資料庫，控制檔案的大小一般不要超過MB,最多為個，最少一個，互為映象

控制檔案中包含的內容

資料庫的名字、ID、建立的時間戳

表空間的名字

聯機日誌檔案、資料檔案的位置、個數、名字

聯機日誌的Sequence號碼

檢查點的資訊

撤銷段的開始或結束

歸檔資訊

備份資訊

二、檢視控制檔案的相關資訊

1.使用相關檢視來檢視

V$CONTROLFILE --列出例項中所有控制檔案的名字及狀態資訊

V$PARAMETER --列出所有引數的位置及狀態資訊

V$CONTROLFILE_RECORD_SECTION --列出控制檔案中記錄的部分資訊

SHOW PARAMETER CONTROL_FILES --列出控制檔案的名字、狀態、位置等

SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS

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

/u01/app/oracle/oradata/orcl/control01.ctl NO 16384 430

/u01/app/oracle/oradata/orcl/control02.ctl NO 16384 430

/u01/app/oracle/oradata/orcl/control03.ctl NO 16384 430

SQL> select name,type,value from v$parameter where name like '%control%';

NAME TYPE VALUE

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

control_files 2 /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/

oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/con

trol03.ctl

control_file_record_keep_time 3 7

SQL> select * from v$controlfile_record_section;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

DATABASE 316 1 1 0 0 0

CKPT PROGRESS 8180 11 0 0 0 0

REDO THREAD 256 8 1 0 0 0

REDO LOG 72 16 9 0 0 20

DATAFILE 428 100 8 0 0 28

FILENAME 524 2298 21 0 0 0

TABLESPACE 68 100 7 0 0 7

TEMPORARY FILENAME 56 100 1 0 0 1

RMAN CONFIGURATION 1108 50 0 0 0 0

LOG HISTORY 56 292 35 1 35 35

OFFLINE RANGE 200 163 0 0 0 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

ARCHIVED LOG 584 28 20 1 20 20

BACKUP SET 40 409 0 0 0 0

BACKUP PIECE 736 200 0 0 0 0

BACKUP DATAFILE 116 282 0 0 0 0

BACKUP REDOLOG 76 215 0 0 0 0

DATAFILE COPY 660 223 1 1 1 1

BACKUP CORRUPTION 44 371 0 0 0 0

COPY CORRUPTION 40 409 0 0 0 0

DELETED OBJECT 20 818 3 1 3 3

PROXY COPY 852 249 0 0 0 0

BACKUP SPFILE 36 454 0 0 0 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

DATABASE INCARNATION 56 292 2 1 2 2

FLASHBACK LOG 84 2048 0 0 0 0

RECOVERY DESTINATION 180 1 1 0 0 0

INSTANCE SPACE RESERVATION 28 1055 1 0 0 0

REMOVABLE RECOVERY FILES 32 1000 0 0 0 0

RMAN STATUS 116 141 0 0 0 0

THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0

MTTR 100 8 1 0 0 0

DATAFILE HISTORY 568 57 0 0 0 0

STANDBY DATABASE MATRIX 400 10 10 0 0 0

GUARANTEED RESTORE POINT 212 2048 0 0 0 0

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID

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

RESTORE POINT 212 2083 0 0 0 0

SQL> show parameter control_files;

NAME TYPE VALUE

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

control_files string /u01/app/oracle/oradata/orcl/c

ontrol01.ctl, /u01/app/oracle/

oradata/orcl/control02.ctl, /u

01/app/oracle/oradata/orcl/con

trol03.ctl

SQL> select controlfile_sequence# from v$database;

CONTROLFILE_SEQUENCE#

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

985

2.使用STRINGS命令來檢視控制檔案中的具體內容

SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl | more

}|{z

JORCL

L-+RG

+ORCL

orcl

-+-='

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo3.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo2.log

/u01/app/oracle/oradata/orcl/redo01.log

/u01/app/oracle/oradata/orcl/tbs1_2.dbf

/u01/app/oracle/oradata/orcl/tbs1_1.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

u01/app/oracle/oradata/orcl/undotbs02.dbf

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo3.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo2.log

/u01/app/oracle/oradata/orcl/redo01.log

/u01/app/oracle/oradata/orcl/tbs1_2.dbf

/u01/app/oracle/oradata/orcl/tbs1_1.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

u01/app/oracle/oradata/orcl/undotbs02.dbf

SYSTEM

UNDOTBS1

SYSAUX

USERS

EXAMPLE

TBS1

TEMP

3.備份控制檔案到平面檔案(然後檢視控制檔案中的具體內容)

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

Database altered.

--或者使用

SQL> host strings /u01/app/oracle/oradata/orcl/control01.ctl > /u01/app/oracle/ctl.txt

SQL> host cat /u01/app/oracle/ctl.txt;

-- 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="orcl"

--

-- 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_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'

-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'

-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'

-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_10=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 "ORCL" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

GROUP 2 (

'/u01/app/oracle/oradata/orcl/redo02.log',

'/u01/app/oracle/oradata/orcl/redo2.log'

) SIZE 50M,

GROUP 3 (

'/u01/app/oracle/oradata/orcl/redo03.log',

'/u01/app/oracle/oradata/orcl/redo3.log'

) SIZE 100M,

GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,

GROUP 7 (

'/u01/app/oracle/oradata/orcl/redo07.log ',

'/u01/app/oracle/oradata/orcl/redo7.log '

) SIZE 50M,

GROUP 8 (

'/u01/app/oracle/oradata/orcl/redo08.log',

'/u01/app/oracle/oradata/orcl/redo8.log'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf',

'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',

'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'

CHARACTER SET WE8ISO8859P1

;

-- 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/flash_recovery_area

-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

-- /ORCL/archivelog/2010_05_20/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 '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 26214400 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 "ORCL" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

GROUP 2 (

'/u01/app/oracle/oradata/orcl/redo02.log',

'/u01/app/oracle/oradata/orcl/redo2.log'

) SIZE 50M,

GROUP 3 (

'/u01/app/oracle/oradata/orcl/redo03.log',

'/u01/app/oracle/oradata/orcl/redo3.log'

) SIZE 100M,

GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50M,

GROUP 7 (

'/u01/app/oracle/oradata/orcl/redo07.log ',

'/u01/app/oracle/oradata/orcl/redo7.log '

) SIZE 50M,

GROUP 8 (

'/u01/app/oracle/oradata/orcl/redo08.log',

'/u01/app/oracle/oradata/orcl/redo8.log'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/example01.dbf',

'/u01/app/oracle/oradata/orcl/tbs1_1.dbf',

'/u01/app/oracle/oradata/orcl/tbs1_2.dbf'

CHARACTER SET WE8ISO8859P1

;

-- 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/flash_recovery_area

-- /ORCL/archivelog/2010_05_20/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area

-- /ORCL/archivelog/2010_05_20/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 '/u01/app/oracle/oradata/orcl/temp01.dbf'

SIZE 26214400 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

Oracle 控制檔案(CONTROLFILE)- <1>

Oracle 控制檔案(CONTROLFILE)

相關文章