|
“Education is a
progressive discovery of our own ignorance.” Will Durant (1885
- 1981) |
Read
first then play the video:
DBAx03(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 \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?
|