iSelfSchooling - Advanced DBA Hands-On #03-Multiplexing the Control File

DBAx03(VIDEO)-Multiplexing the Control Files (Advanced)
You must first Register and then request for username and password to access to VIDEOS

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 \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@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?