|
Multiplexing Control Files
Introduction
You, as a DBA,
are responsible to multiplex controlfiles to
protect your organization from a possible
and unexpected loss of controlfiles, due to
media failure. Your job�s responsibilities
dictate that you should at least be informed
of the following basic fundamental subjects:
Control Files
Using the
V$CONTROLFILE view
Using the
V$CONTROLFILE_RECORD_SECTION view
Multiplexing
the Control File
Using the
INIT.ORA file
Editing the
INIT.ORA
Commands:
STARTUP OPEN
PFILE=%ORACLE_BASE%.ora
HOST MKDIR
ALTER DATABASE
BACKUP CONTROLFILE
ALTER DATABASE
BACKUP CONTROLFILE TO TRACE
SHUTDOWN
IMMEDIATE
Hands-on
In this
exercise you will learn how to multiplex
control files and more.
Now, begin by connecting to SQL*Plus as the
system/manager user.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
View a
database controlfiles
The
controlfile in the Oracle database is a
binary file that contains the database
structure, backup information, datafiles
synchronization, and more.
Query the
V$CONTROLFILE view to list all of the
controlfiles currently used in the database.
Note that the
V$CONTROLFILE view contains the location of
your controlfiles and their status.
SQL> SELECT *
FROM v$controlfile
/
Add a
controlfile
Add
one more controlfile to the list.
First, shutdown the database.
SQL> SHUTDOWN
IMMEDIATE
Copy a controlfile and name it
CONTROL04.CTL.
SQL> HOST COPY \par
C:.CTL \par C:.CTL
Then, open the INIT.ORA file located in the
%ORACLE_BASE%.ora.
Edit the file and add the CONTROL04.CTL file
to the control_files list.
Change from:
control_files=("C:.ctl",
"C:.ctl",
"C:.ctl")
To:
control_files=("C:.ctl",
"C:.ctl",
"C:.ctl",
"C:.ctl")
Then save the changes and close the file.
Now, connect to SQL*Plus as the
system/manager user.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
Start a
database
Startup the database with the parameter file
(INIT.ORA) that you just edited.
SQL> -- start using pfile�
SQL> STARTUP
OPEN
PFILE=%ORACLE_BASE%.ora
Check the
controlfile was added
Query the
V$CONTROLFILE view to list all of the
controlfiles in the database.
SQL> SELECT *
FROM v$controlfile
/
Observe that the controlfile was
added.
Backup a
controlfile
Now, backup the controlfile. Before making a
backup, let create a directory name c:and
then backup the controlfile into it.
SQL> -- make directory
SQL> HOST
MKDIR c:
SQL> ALTER DATABASE BACKUP CONTROLFILE
TO 'c:_ddmmyyyy.ctl'
/
Notice that
the ddmmyyyy will signify your current date
to let you know the date you backed up the
controlfile.
Backup a
database structure
Check to see how a controlfile is divided
into several sections. It is advisable to
backup the CONTROLFILE anytime you change
the structure of your database such as,
adding or dropping tablespaces, datafile,
etc...
SQL> ALTER
DATABASE BACKUP CONTROLFILE TO TRACE
/
Check a
controlfile sections
Check to see how a controlfile is divided
into several sections.
SQL> SELECT * FROM
v$controlfile_record_section
/
Notice that each section stores
different information about the database
with different record sizes. Now, check to
see how many records were used in each
section.
Questions:
Q: What is the
controlfile in the Oracle database?
Q: How do you
get a list of all your controlfiles�
location?
Q: Describe
the following views:
V$CONTROLFILE view
V$CONTROLFILE_RECORD_SECTION view
Q: What do the
following SQL statements?
SQL> ALTER
DATABASE BACKUP CONTROLFILE
TO 'c:_ddmmyyyy.ctl'
/
SQL> ALTER
DATABASE BACKUP CONTROLFILE TO TRACE
/
SQL> SELECT * FROM
v$controlfile_record_section
/
Q: You, as a
DBA, are responsible to multiplex
controlfiles to protect your organization
from a possible and unexpected loss of
controlfiles, due to media failure. Your
task is to add one more controlfile to you
database. What are the steps? |
