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

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

. Online Accounting        .Copyright & User Agreement   |

.Vision      .Biography     .Acknowledgement .Contact Us      .Comments/Suggestions       Email2aFriend    |

   DBA-VIDEO -Multiplexing the Control Files (Advanced)
   

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:

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

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.

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?

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?