Hands-On 03 (Multiplexing the Control File)

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%\admin\school\pfile\init.ora

HOST MKDIR

ALTER DATABASE BACKUP CONTROLFILE

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

SHUTDOWN IMMEDIATE

-- Hands-On 03 (Multiplexing the Control File)
-- Preparation
set echo on
connect system/manager@school as sysdba
host copy %ORACLE_HOME%\admin\school\pfile\initoriginal.ora %ORACLE_HOME%\admin\school\pfile\init.ora
SET linesize 1000 pagesize 55
COL name FORMAT a50
col parameter format a40
col username format a10
pause

--Start

CLEAR SCR
-- 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.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Query the V$CONTROLFILE view to list all of the controlfiles
-- currently used in the database. 

pause


SELECT *
FROM v$controlfile
/

-- Add one more controlfile to the list.

pause

CLEAR SCR
-- First, shutdown the database.

pause

SHUTDOWN IMMEDIATE

pause

CLEAR SCR
-- Copy a controlfile and name it CONTROL04.CTL.

pause


HOST COPY C:\ORACLE\ORADATA\SCHOOL\CONTROL01.CTL C:\ORACLE\ORADATA\SCHOOL\CONTROL04.CTL

pause

CLEAR SCR
-- Then, open the INIT.ORA file located 
-- in the %ORACLE_BASE%\admin\school\pfile\init.ora.

-- Edit the file and add the CONTROL04.CTL file to the
-- control_files list.

--\/-- control_files=("C:\oracle\oradata\school\control01.ctl", 
--\/-- "C:\oracle\oradata\school\control02.ctl", 
--\/-- "C:\oracle\oradata\school\control03.ctl",
--\/-- "C:\oracle\oradata\school\control04.ctl")

-- Then save the changes and close the file.

pause
pause

CLEAR SCR
-- Now, connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- Startup the database with the parameter file (INIT.ORA) that 
-- you just edited.

pause

-- >>>>>>>>>>>>>>>>>>>>>> Startup
STARTUP OPEN PFILE=%ORACLE_BASE%\admin\school\pfile\init.ora

pause

CLEAR SCR
-- Query the V$CONTROLFILE view to list all of the controlfiles
-- in the database. 

pause


SELECT *
FROM v$controlfile
/

-- Observe that the controlfile was added.

pause

CLEAR SCR
-- Now, backup the controlfile.

-- Before making a backup, let create a directory name
-- c:\backupcontrolfile and then backup the controlfile into it.

pause

HOST MKDIR c:\backupcontrolfile

ALTER DATABASE BACKUP CONTROLFILE 
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
/

-- Notice that the ddmmyyyy will signify your current date to
-- let you know the date you backed up the controlfile.

pause

CLEAR SCR
-- Check to see how a controlfile is divided into several sections.

pause

CLEAR SCR
-- It is advisable to backup the CONTROLFILE anytime you change
-- the structure of your database such as, adding or dropping
-- tablespaces, datafile, etc...

pause


ALTER DATABASE BACKUP CONTROLFILE TO TRACE
/

pause

CLEAR SCR
-- Check to see how a controlfile is divided into several sections.

pause

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.

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause