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