DBA Fundamentals

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:

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

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:

To:

control_files=("C:.ctl",
"C:.ctl",
"C:.ctl",
"C:.ctl")

Then, save and close the file.

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

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
/
 

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.
 

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?