Transcript Database Administration

Week 2
Lecture 2
Data Dictionary Views
and Control Files
Learning Objectives
 Use the data dictionary components
and views
 List useful dynamic performance
views
 Manage and multiplex control files
Learning Objectives con’t.
 Use OMF to manage control files
 Create new control files
 View control file data
Looking at Important Data
Dictionary Components
Data dictionary views:

Use underlying tables owned by SYS

Can be queried like other views

Number in the hundreds
Looking at Important Data
Dictionary Components
The
DICTIONARY
view lists data
dictionary views
with a short
description
Looking at Important Data
Dictionary Components
Data dictionary views:

Cannot be updated

Are documented on otn.oracle.com

Have a prefix that defines the scope:

USER: Views focused on a user’s own
objects

ALL: Views about objects a user either owns
or can query

DBA: Views for DBAs only, showing
information about all objects in the database
Using Data Dictionary Views
Uses for Data Dictionary Views

Supply information to the Enterprise
Manager console

Help you build queries

Help you review naming standards

Help you find invalid views

Much more…
Useful Dynamic
Performance Views
Dynamic performance views are similar to
data dictionary views except:

The contents of the views change with
database activity

They are used mostly for tuning and
monitoring
Dynamic performance views are prefixed with:

V$: Standard dynamic performance view

GV$: View spanning multiple instances
Useful Dynamic
Performance Views
Some useful views include:

V$SYSSTAT: statistical details about
all sessions running on the database

V$SQL: Details on individual SQL
statements running on the database

V$SESSTAT: Information by session
to help identify memory usage
Introduction to the Control
File
The control file contains current details
about the database:

Database name

Data file and online redo log file
names and locations

Checkpoint information critical to
recovery
Managing and Multiplexing
the Control Files
What you can do with control files:

Add a new control file

Rename or relocate an existing
control file

Delete an existing control file (so long
as at least one remains)

Replace a damaged control file

Regenerate a control file
Adding a New Control File
Steps in a nutshell:
1.
Shut down the database
2.
Copy a current control file
3.
Edit the init<sid>.ora file
4.
Start up the database
Renaming or Relocating an
Existing Control File
Steps in a nutshell:
1.
Shut down the database
2.
Rename or relocate a current control
file
3.
Edit the init<sid>.ora file
4.
Start up the database
Replacing a Damaged
Control File
Steps in a nutshell:
1.
Shut down the database
2.
Delete the bad control file
3.
Copy a good control file, naming the
copy to match the old, bad control
file
4.
Start up the database
Using OMF to Manage
Control Files

OMF can create and manage control files

Requires the use of initialization parameters
when creating the database:

CONTROL_FILES is blank

DB_CREATE_ONLINE_LOG_DEST_n has
valid directory name

DB_CREATE_FILE_DEST can be used
instead if no multiplexing is needed
Using OMF to Manage
Control Files
Example:
Initialization parameters:
CONTROL_FILES = ‘’
DB_CREATE_ONLINE_LOG_DEST_1=‘C:\ora\oradata’
DB_CREATE_ONLINE_LOG_DEST_2=‘D:\ora\oradata’
CREATE DATABASE command:
CREATE DATABASE TECHNO92
MAXDATAFILES 100
DATAFILE ‘C:\ora\oradata\system01.dbf'
SIZE 325M AUTOEXTEND;
Creates a database
with OMF control files
and OMF redo log files
and user-managed
data files
Creating a New Control File
Needed when:

All control files are lost or damaged

Changing initialization parameters that
affect the size of the control file

Changing the database name
How to create a new control file:

Use manually created command

Use generated script
Creating a New Control File
The manually created command syntax:
CREATE CONTROLFILE REUSE SET DATABASE
<dbname>
List all redo
log groups
and members
GROUP n 'X/xxx/logfilename' SIZE <nn>,
LOGFILE
GROUP 2 'X/xxx/logfilename' SIZE <nn>List all datafiles
NORESETLOGS
DATAFILE
'<X/xxx>/<filename>', ....
MAXLOGFILES <nn> MAXLOGMEMBERS <nn>
MAXLOGHISTORY <nn> MAXDATAFILES <nn>
MAXINSTANCES <nn> ARCHIVELOG
CHARSET <charsetvalue>;
Creating a New Control File
The generated script command:
ALTER DATABASE BACKUP
CONTROL FILES TO TRACE;

You must be logged on as a DBA user such
as SYSTEM

You must use this command before the
control files are lost or damaged, while the
database is open
Creating a New Control File
Example of a
generated
script
Viewing Control File Data
The control file has record sections for:

File identification

Details needed for recovery

Database activity and status

Recovery checkpoints
Viewing Control File Data
Use dynamic
performance views to
see control file
information
Lecture Summary

Data dictionary views and dynamic
performance views are owned by SYS

Use normal queries on these views

Most data dictionary views have a
prefix of USER, ALL, or DBA

Most dynamic performance views
have a prefix of V$ or GV$
Lecture Summary

Control files track current information
on:

Datafiles and online redo log files

Checkpoints and log group number

By creating more than one control file,
you multiplex the control files

Add extra control files by copying
others
Lecture Summary

You can also rename or relocate
existing control files

You can replace damaged control files
with copies of undamaged ones

You can recreate a control file either
manually or using a generated script
Lecture Summary

The manual method requires building
a CREATE CONTROLFILE command

The generated method creates the
CREATE CONTROLFILE command
for you

Several V$ views show information
stored in the control file