Oracle control file description

1. The content of the Oracle control file can be viewed in the v $ controlfile_record_section view. SQLgt; selecttype

1. The content of the Oracle control file can be viewed in the v $ controlfile_record_section view. SQLgt; select type

I. Oracle Control File Content

You can view the contents in the control file in the v $ controlfile_record_section view.

SQL> select type from v $ controlfile_record_section;

TYPE

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

DATABASE

CKPT PROGRESS

REDO THREAD

REDO LOG

DATAFILE

FILENAME

TABLESPACE

TEMPORARY FILENAME

RMAN CONFIGURATION

LOG HISTORY

OFFLINE RANGE

ARCHIVED LOG

BACKUP SET

BACKUP PIECE

BACKUP DATAFILE

BACKUP REDOLOG

DATAFILE COPY

BACKUP uption

COPY upload uption

DELETED OBJECT

PROXY COPY

BACKUP SPFILE

DATABASE INCARNATION

FLASHBACK LOG

RECOVERY DESTINATION

INSTANCE SPACE RESERVATION

REMOVABLE RECOVERY FILES

RMAN STATUS

THREAD INSTANCE NAME MAPPING

MTTR

DATAFILE HISTORY

STANDBY DATABASE MATRIX

GUARANTEED RESTORE POINT

RESTORE POINT

Database block upload uption

ACM OPERATION

FOREIGN ARCHIVED LOG

37 rows selected.

2. You can view the control file through dump.

2.1 direct dump controlfile

Alter system set events 'immediate trace name controlf level 10'

2.2. Use alter database backup controlfile to filename

The dump file generated by the preceding two methods is unreadable and garbled. It is readable only after the trace is generated.

2.2. Use alter database backup controlfile to trace

The generated trace file is in the udump directory and can be determined by date.

SQL> show parameteruser_dump_dest

You can also use the following SQL statement to query the corresponding trace file:

SELECT a. VALUE | B. symbol | c. instance_name | '_ ora _' | d. spid | '. trc'

Trace_file

FROMv $ parameter

Symbol

FROMv $ parameter

B,

(SELECTinstance_name FROMv $ instance) c,

(SELECTspid

FROMv $ session s, v $ process p, v $ mystat m

Statistic # d

TRACE_FILE

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

/U01/app/oracle/admin/dave/udump/dave_ora_7215.trc

The entire Trace content is as follows:

[Oracle @ qs-dmm-rh2 udump] $ cat dave_ora_7215.trc

/U01/app/oracle/admin/dave/udump/dave_ora_7215.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production

With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

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

System name: Linux

Node name: qs-dmm-rh2

Release: 2.6.18-194. el5

Version: #1 SMP Tue Mar16 21:52:43 EDT 2010

Machine: i686

Instance name: dave

Redo thread mounted by this instance: 0

Oracle process number: 15

Unix process pid: 7215, image: oracle @ qs-dmm-rh2 (TNS V1-V3)

* ** Action name :() 22:05:46. 401

* Module name :( sqlplus @ qs-dmm-rh2 (TNS V1-V3) 2011-03-1722: 05: 46.401

* ** Service name :() 22:05:46. 401

* ** Session id: (159.1) 22:05:46. 401

ORA-01160: file is not a data file

ORA-01110: data file: '/u01/app/oracle/oradata/dave/temp01.dbf'

* ** 22:08:25. 791

Control file created with size 370 blocks

* ** 22:10:21. 444

Tkcrrsarc: (WARN) Failed to find ARCH for message (message: 0x1)

Tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message: 0x1)

Kwqmnich: current time: 14: 10: 24

Kwqmnich: instance no 0 check_only flag 1

Kwqmnich: initialized job cache structure

Krvscm (+): Validating controlfile with logical metadata

Krvscm (+): Initial controlfile state

Krvscm (+): kccdiflg [400001] kccdifl2 [1000]

Krvscm (+): kccdi2ldscn [0x0000.00000000]

Krvscm (+): kccdi2lrscn [0x0000.00000000]

Krvscm (+): Inspecting logical metadata

Krvscm (+): Metadata state

Krvscm (+): hasPrepSwitchSta [0]

Krvscm (+): hasPrepSwitchPri [0]

Krvscm (+): hasw.eddict [0]

Krvscm (+): hasDumpedDict [0]

Krvscm (+): hasCommittedBor [0]

Krvscm (+): hasSwitchedFromPri [0]

Krvscm (+): hasStartedTa [0]

Krvscm (+): hasValidSess [0]

Krvscm (+): hasTxnConsistency [0]

Krvscm (+): hasCleanlyShutdown [0]

Krvscm (+): Generating new controlfile state from metadata

Krvscm (+): Updating controlfile with new state

Krvscm (+): New controlfile state

Krvscm (+): kccdiflg [400001] kccdifl2 [1000]

Krvscm (+): kccdi2ldscn [0x0000.00000000]

Krvscm (+): kccdi2lrscn [0x0000.00000000]

Krvscm (+): Updating SGA associated with controlfile state

Krvscm (+): Validation complete

* ** 22:13:21. 115

-- The following are current System-scope REDO Log Archival related

-- Parameters and can be encoded in the database initializationfile.

--

-- LOG_ARCHIVE_DEST =''

-- LOG_ARCHIVE_DUPLEX_DEST =''

--

-- LOG_ARCHIVE_FORMAT = % t _ % s _ % r. dbf

--

-- DB_UNIQUE_NAME = "dave_st"

--

-- LOG_ARCHIVE_CONFIG = 'send, RECEIVE'

-- LOG_ARCHIVE_CONFIG = 'dg _ CONFIG = ("dave_pd ")'

-- LOG_ARCHIVE_MAX_PROCESSES = 2

-- STANDBY_FILE_MANAGEMENT = AUTO

-- STANDBY_ARCHIVE_DEST =/u01/archivelog

-- FAL_CLIENT = dave_st

-- FAL_SERVER = dave_pd

--

-- LOG_ARCHIVE_DEST_2 = 'service = dave_pd'

-- LOG_ARCHIVE_DEST_2 = 'optional REOPEN = 120 nodelay'

-- LOG_ARCHIVE_DEST_2 = 'lgwr noaffrem noexpedite noverifyasync = 661'

-- LOG_ARCHIVE_DEST_2 = 'register noalternate nodependency'

-- LOG_ARCHIVE_DEST_2 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED'

-- LOG_ARCHIVE_DEST_2 = 'db _ UNIQUE_NAME = dave_pd'

-- LOG_ARCHIVE_DEST_2 = 'valid _ FOR = (STANDBY_LOGFILE, STANDBY_ROLE )'

-- LOG_ARCHIVE_DEST_STATE_2 = ENABLE

--

-- LOG_ARCHIVE_DEST_1 = 'location =/u01/archivelog'

-- LOG_ARCHIVE_DEST_1 = 'optional REOPEN = 300 nodelay'

-- LOG_ARCHIVE_DEST_1 = 'arch noaffrem noexpedite noverify sync'

-- LOG_ARCHIVE_DEST_1 = 'register noalternate nodependency'

-- LOG_ARCHIVE_DEST_1 = 'nomax _ FAILURE NOQUOTA_SIZE NOQUOTA_USED'

-- LOG_ARCHIVE_DEST_1 = 'db _ UNIQUE_NAME = dave_st'

-- 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 setopens

-- The database with the NORESETLOGS option and shoshould be used onlyif

-- The current versions of all online logs are available. The second

-- Set opens the database with the RESETLOGS option and shoshould beused

-- If online logs are unavailable.

-- The appropriate set of statements can be copiedfrom the trace

-- A script file, edited as necessary, andexecuted when there is

-- Need to re-create the control file.

--

-- Set #1. NORESETLOGS case

-- Instructions on using noresetlogs

--

-- 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 STANDBYDATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

Create controlfile reuse database "DAVE" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 30

MAXINSTANCES 1

# MAXLOGHISTORY 292

LOGFILE

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

GROUP 2'/u01/app/oracle/oradata/dave/redo02.log 'SIZE 50 M,

GROUP 3 '/u01/app/oracle/oradata/dave/redo03.log' SIZE 50 M

-- STANDBY LOGFILE

DATAFILE

'/U01/app/oracle/oradata/dave/system01.dbf ',

'/U01/app/oracle/oradata/dave/undotbs01.dbf ',

'/U01/app/oracle/oradata/dave/sysaux01.dbf ',

'/U01/app/oracle/oradata/dave/users01.dbf'

Character set ZHS16GBK

;

-- The above is the syntax for creating Control Files

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

-- Re-create incarnation records.

-- Alter database register logfile '/u01/archivelog/1_1_746031707.dbf ';

-- Recovery is required if any of the datafiles are restoredbackups,

-- Or if the last shutdown was not normal orimmediate.

RECOVER DATABASE

-- All logs need archiving and a log switch isneeded.

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 temp add tempfile '/u01/app/oracle/oradata/dave/temp01.dbf'

SIZE 32505856 reuse autoextend off;

-- Note that when you recreate a control file, you cannot write a temporary tablespace. After the control file is created, add a temporary tablespace to the SQL statement.

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

-- In the second case, use the resetlogs description

--

-- 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 STANDBYDATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

Create controlfile reuse database "DAVE" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 30

MAXINSTANCES 1

# MAXLOGHISTORY 292

LOGFILE

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

GROUP 2'/u01/app/oracle/oradata/dave/redo02.log 'SIZE 50 M,

GROUP 3 '/u01/app/oracle/oradata/dave/redo03.log' SIZE 50 M

-- STANDBY LOGFILE

DATAFILE

'/U01/app/oracle/oradata/dave/system01.dbf ',

'/U01/app/oracle/oradata/dave/undotbs01.dbf ',

'/U01/app/oracle/oradata/dave/sysaux01.dbf ',

'/U01/app/oracle/oradata/dave/users01.dbf'

Character set ZHS16GBK

;

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

-- Re-create incarnation records.

-- Alter database register logfile '/u01/archivelog/1_1_746031707.dbf ';

-- Recovery is required if any of the datafilesare 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/dave/temp01.dbf'

SIZE 32505856 reuse autoextend off;

-- End of tempfile additions.

--

Note the following parameters:

(1) MAXDATAFILES