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 II

 

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 27 | Lesson 28 | Lesson 29 |

 

Lesson 02

“Education is a progressive discovery of our own ignorance.” Will Durant (1885 - 1981)

 

Read first then play the video:

   DBA-VIDEO -Multiplexing the Control Files (Advanced)
 
 

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@school 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  C:\CONTROL01.CTL C:\CONTROL04.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:\CONTROL01ctl",
"C:\CONTROL02.ctl",
"C:\CONTROL03.ctl")
To:
control_files=("C:\CONTROL01ctl",
"C:\CONTROL02.ctl",
"C:\CONTROL03.ctl"),
"C:\CONTROL04.ctl")

Then save the changes and close the file.

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

 

“Success is counted sweetest by those who ne'er succeed.” Emily Dickinson (1830 - 1886)

 

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?