"There's a lot to
be said for self-delusionment when it comes to matters of the
heart." Diane Frolov and Andrew Schneider, Northern Exposure,
First Snow, 1993 |
Read
first then play the video:
DBA-VIDEO -Multiplexing
controlfiles
Multiplexing controlfiles
Introduction
As a DBA, you are
responsible for multiplexing controlfiles in order to protect your
organization in case of a possible 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:
Multiplexing control files
Using the V$CONTROLFILE
view
Editing the INIT.ORA file
Using the
V$CONTROLFILE_RECORD_SECTION view
Commands:
ALTER DATABASE BACKUP
CONTROLFILE
ALTER DATABASE BACKUP
CONTROLFILE TO TRACE
HOST COPY
HOST MKDIR
Hands-on
In this exercise you will learn how to multiplex control files,
backup controlfiles, components of controlfile and more. You multiplex
the controlfiles to protect the database in a case if there was a lost
of its controlfiles. You should make sure that keep multiple copy of
each controlfile at different hard disk. Note that if you lose your
controlfile, you will not able to open it unless you create it. It is
not easy to create a control file if you don"t know the database"s
structure. Make sure that you always have a copy of your controlfile
with a trace option. The following is an example of how you can keep a
backup of your controlfile with the TRACE option.
ALTER DATABASE BACKUP
CONTROLFILE TO TRACE;
Connect to a database
First, let's connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager@school AS
SYSDBA
List all the
controlfiles
Query the V$CONTROLFILE
view to list all of the controlfiles in the database.
SQL> SELECT *
FROM v$controlfile
/
Let us add one more controlfile to the list.
Shutdown a database
Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
Copy a controlfile
Copy a controlfile and name
it CONTROL04.CTL. Make sure that in real practice, you copy the
controlfile into a different hard disk. So, in the case if one hard
disk failure due to a media failure the other controlfile be secure.
SQL> HOST COPY \par C:.CTL \par C:.CTL
Edit parameter file
Open the INIT.ORA file
located in the PFILE directory.
(%ORACLE_BASE%.ora)
Edit the file and add the CONTROL04.CTL file to the control_files
list.
Change from:
control_files=("C:\CONTROL01.ctl",
"C:\CONTROL02.ctl",
"C:\CONTROL03.ctl")
To:
control_files=("C:\CONTROL01.ctl",
"C:\CONTROL02.ctl",
"C:\CONTROL03.ctl",
"C:\CONTROL04.ctl" );
Then, save and close the file.
Connect back to database
Connect to SQL*Plus as the
system/manager user.
SQL> CONNECT system/manager@school AS
SYSDBA
Start a database
Startup the database with
the parameter file (INIT.ORA) that you just edited.
SQL> STARTUP OPEN PFILE=%ORACLE_BASE%.ora
List all the controlfiles
Query the V$CONTROLFILE view to list all of the controlfiles in
the database.
SQL> SELECT *
FROM v$controlfile
/
The controlfile was added. That was how you can multiplex
your controlfiles.
Backup a controlfile
Now, let us see how we can
backup the controlfile. You don"t need to backup all your
controlfiles since there are exactly identical.
Before making backup, let's first create a directory named c:and then
backup the controlfile into it.
SQL> HOST MKDIR c:
SQL> ALTER DATABASE BACKUP
CONTROLFILE
TO 'c:_ddmmyyyy.ctl'
/
Notice that the ddmmyyyy is the current date ex: 25052003, it is there
to let you know the date you backed-up the controlfile.
Backup database structure
It is advisable to backup
the CONTROLFILE anytime we change the structure of our database such
as adding or dropping tablespace, datafile, etc.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
/
What is in a controlfile?
Now, 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. From the above query, you
see how many records were used in each section.
"Do not weep; do
not wax indignant. Understand." Baruch Spinoza (1632 - 1677) |
Questions:
Q: Describe an Oracle
database controlfile.
Q: How do you multiplex a
controlfile?
Q: Describe the
V$CONTROLFILE view.
Q: Describe the
V$CONTROLFILE_RECORD_SECTION view.
Q: What does the "ALTER
DATABASE BACKUP CONTROLFILE TO TRACE" command?
Q: What does the following
SQL statement?
SQL> ALTER DATABASE
BACKUP CONTROLFILE
TO 'c:_ddmmyyyy.ctl'
/
Q: How do you view the divided sections of a controlfile?
|