Sundaramurthy Blog

January 28, 2010

Controle file

Filed under: Inside Controle file — sundar5 @ 4:55 pm

Oracle maintains the record of the physical structure and on-going database changes
in the control file. These records are consistency with data/logs files header
information in the database.

To make consistence between database and control file, oracle uses the unique
number called SCN number. This SCN number is generated by oracle using
checkpoint method.

Oracle generates various kinds of SCN numbers in the database operations.

• Full Checkpoint
• Thread Checkpoint
• File Checkpoint
• Object   Checkpoint
• Parallel Query Checkpoint
• Incremental Checkpoint
• Log Switch Checkpoint

Full Checkpoint

Oracle writs all the dirty block image of cache buffers in to the data files for
all instances. The following actions are caused by full check points.

• Shutdown immediate
• Alter database close
• Alter system checkpoint [global]
• Alter database begin backup

Statistics update:

• DBWR threads checkpoint buffers written
• DBWR checkpoints
• DBWR checkpoint buffers written

Thread Checkpoint:

Writs all the dirty block images of cache buffers in to the data files for
single instance.

• Alter system checkpoint local

File Checkpoint

All the dirty block images are written to the data file for a tablespace

• Alter tablespace TEST begin backup
• Alter tablespace TEST offline
• Alter tablespace TEST read only

Parallel Query Checkpoint

All the dirty block images of the objects are written to the data files
from all instances.

• Parallel Query
• Mandatory for consistency
• Parallel Query component.(PDML/PDDL)

Object Checkpoint

All the dirty block images of the object are written tot the data files
from all the instance.

• Drop table sales
• Drop table sales purge
• Truncate table sales
• Drop index sales_idx

Log Switch Checkpoint

All the or some of the dirty buffers are written in the data files and
Control file is updated.

• Control file and data file headers are updated

Incremental Checkpoint

From CKPT-Q queue dirty buffers are written to the data files base on
the fast_start_mttr_target parameter  to keep-up with instance
recovery minimum.

• Block images written in SCN order

• Checkpoint RBA updated in SGA

Controlfile contains non-reusable records

• Database
• Tablespace
• Datafile
• Online redo log …

Circular reuse records

• Archived log
• Backup set and piece
• Backup datafile and datafile copy …

34 different record types in 10g R2

Session must obtain CF enqueue first

Controlfile contains 2 versions of each block

• bitmap indicates which version is current

• transaction modifies non-current version

At the end of transaction

•  controlfile sequence number is incremented

• bitmap is updated and written out atomically

• CF enqueue is released

1.  What the size of control file block size?

default  always database block size (db_block_size parameter)

FILE HEADER:

Compatibility Vsn = 168821248=0xa100200
Db ID=3364774739=0xc88e6353, Db Name=’TEST
Activation ID=0=0x0
Control Seq=925546=0xe1f6a, File size=282=0x11a
File Number=0, Blksiz=16384, File Type=1 CONTROL

db ID      = 3364774739 (Database id)
db name    = TEST (Database name)
File size  = 282 blocks (282*16384=4620288 + 16384(Blksiz) =4636672)
blksize    = 16384
file Number= 0
file type  = control file

DATABASE ENTRY

(extent = 1, blkno = 1, numrecs = 1)
03/21/2007 00:09:01
DB Name “TEST”
Database flags = 0x10404000 0x00001000
Controlfile Creation Timestamp  03/21/2007 00:09:02
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00000001 Resetlogs Timestamp  03/21/2007 00:09:01
Prior resetlogs scn: 0x0000.00000000 Prior resetlogs Timestamp  01/01/1988 00:00:00
Redo Version: compatible=0xa200000
#Data files = 5, #Online files = 5
Database checkpoint: Thread=1 scn: 0x0000.0002a28b
Threads: #Enabled=2, #Open=2, Head=1, Tail=2
enabled  threads:  01100000 00000000 00000000 00000000 00000000 00000000

DATA FILE RECORDS
(size = 428, compat size = 428, section max = 2000, section in-use = 5,
last-recid= 7, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 15, numrecs = 2000)
DATA FILE #1:
(name #7) /u22/oradata/TEST/TESTsystem_01.dbf
creation size=64000 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:20 scn: 0x0000.0002a28b 03/21/2007 02:16:59
Stop scn: 0xffff.ffffffff 03/21/2007 00:29:35
Creation Checkpointed at scn:  0x0000.00000006 03/21/2007 00:09:13
thread:1 rba:(0x1.3.10)

LOG FILE RECORDS

(size = 72, compat size = 72, section max = 32, section in-use = 6,

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

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

LOG FILE #1:

(name #1) /u02/origlog/TEST/TESTlog_t1_g1_m1.dbf

(name #2) /u03/mirrlog/TEST/TESTlog_t1_g1_m2.dbf

Thread 1 redo log links: forward: 2 backward: 0

siz: 0x4b000 seq: 0x0000000a hws: 0x1 bsz: 1024 nab: 0xffffffff flg: 0x8 dup: 2

Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00029e02

Low scn: 0x0000.0002a28b 03/21/2007 02:16:59

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

RMAN CONFIGURATION RECORDS

(size = 1108, compat size = 1108, section max = 50, section in-use = 0,

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

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

CHANNEL:DEVICE TYPE ‘SBT_TAPE’ PARMS  ‘SBT_LIBRARY=/opt/omni/lib/

libob2oracle8_64bit.so’ recno=1

DEFAULT DEVICE TYPE TO:’SBT_TAPE’ recno=2

CONTROLFILE AUTOBACKUP:ON recno=3

CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE:’SBT_TAPE’ TO ‘control_file_%F’ recno=4

BACKUP OPTIMIZATION:ON recno=5

RETENTION POLICY:TO RECOVERY WINDOW OF 45 DAYS recno=6

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

BACKUP SET RECORDS

(size = 40, compat size = 40, section max = 1227, section in-use = 0,

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

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

BACKUP PIECE RECORDS

(size = 736, compat size = 736, section max = 1000, section in-use = 0,

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

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

BACKUP DATAFILE RECORDS

(size = 116, compat size = 116, section max = 1128, section in-use = 0,

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

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

BACKUP LOG RECORDS

(size = 76, compat size = 76, section max = 215, section in-use = 0,

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

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

DATAFILE COPY RECORDS

(size = 660, compat size = 660, section max = 1016, section in-use = 0,

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

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

BACKUP DATAFILE CORRUPTION RECORDS

(size = 44, compat size = 44, section max = 1115, section in-use = 0,

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

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

DATAFILE COPY CORRUPTION RECORDS

(size = 40, compat size = 40, section max = 1227, section in-use = 0,

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

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

DELETION RECORDS

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            19            0           0          0          0

REDO THREAD                          256            16            2           0          0          2

REDO LOG                              72            32            6           0          0          6

DATAFILE                             428          2000            5           0          0          7

FILENAME                             524          6210           18           0          0          0

TABLESPACE                            68          2000            6           0          0          8

TEMPORARY FILENAME                    56          2000            1           0          0          1

RMAN CONFIGURATION                  1108            50            0           0          0          0

LOG HISTORY                           56          1168           34           1         34         34

OFFLINE RANGE                        200          1063            0           0          0          0

ARCHIVED LOG                         584          1008            0           0          0          0

BACKUP SET                            40          1227            0           0          0          0

BACKUP PIECE                         736          1000            0           0          0          0

BACKUP DATAFILE                      116          1128            0           0          0          0

BACKUP REDOLOG                        76           215            0           0          0          0

DATAFILE COPY                        660          1016            0           0          0          0

BACKUP CORRUPTION                     44          1115            0           0          0          0

COPY CORRUPTION                       40          1227            0           0          0          0

DELETED OBJECT                        20          1636            0           0          0          0

PROXY COPY                           852          1017            0           0          0          0

BACKUP SPFILE                         36           454            0           0          0          0

DATABASE INCARNATION                  56           292            1           1          1          1

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            16           16           0          0          0

MTTR                                 100            16            2           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

RESTORE POINT                        212          2083            0           0          0          0

34 rows selected

DATABASE VIEWS

V$BUFFERED_QUEUES

V$BUFFERED_PUBLISHERS

V$BSP

V$BLOCK_CHANGE_TRACKING

V$BACKUP_SPFILE_SUMMARY

V$BACKUP_SPFILE_DETAILS

V$BACKUP_SPFILE

V$BACKUP_SET_SUMMARY

V$BACKUP_SET_DETAILS

V$BACKUP_SET

V$BACKUP_REDOLOG

V$BACKUP_PIECE_DETAILS

V$BACKUP_PIECE

V$BACKUP_FILES

V$BACKUP_DEVICE

V$BACKUP_DATAFILE_SUMMARY

V$BACKUP_DATAFILE_DETAILS

V$BACKUP_DATAFILE

V$BACKUP_CORRUPTION

V$BACKUP_COPY_SUMMARY

V$BACKUP_COPY_DETAILS

V$BACKUP_CONTROLFILE_SUMMARY

V$BACKUP_CONTROLFILE_DETAILS

V$BACKUP_ASYNC_IO

V$BACKUP_ARCHIVELOG_SUMMARY

V$BACKUP_ARCHIVELOG_DETAILS

CATALOG VIEWS

RC_DATABASE                    VIEW

RC_DATABASE_INCARNATION        VIEW

RC_RESYNC                      VIEW

RC_CHECKPOINT                  VIEW

RC_TABLESPACE                  VIEW

RC_DATAFILE                    VIEW

RC_TEMPFILE                    VIEW

RC_REDO_THREAD                 VIEW

RC_REDO_LOG                    VIEW

RC_LOG_HISTORY                 VIEW

RC_ARCHIVED_LOG                VIEW

RC_BACKUP_SET                  VIEW

RC_BACKUP_PIECE                VIEW

RC_BACKUP_DATAFILE             VIEW

RC_BACKUP_CONTROLFILE          VIEW

RC_BACKUP_SPFILE               VIEW

RC_DATAFILE_COPY               VIEW

RC_CONTROLFILE_COPY            VIEW

RC_BACKUP_REDOLOG              VIEW

RC_BACKUP_CORRUPTION           VIEW

RC_COPY_CORRUPTION             VIEW

RC_OFFLINE_RANGE               VIEW

RC_STORED_SCRIPT               VIEW

RC_STORED_SCRIPT_LINE          VIEW

RC_PROXY_DATAFILE              VIEW

RC_PROXY_CONTROLFILE           VIEW

RC_RMAN_CONFIGURATION          VIEW

RC_DATABASE_BLOCK_CORRUPTION

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: