iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 04

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