Hands-On 03 (Multiplexing controlfiles)

As a DBA, you are responsible for multiplexing controlfiles in order to protect your organization in case of a possible 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:

Multiplexing control files

Using the V$CONTROLFILE view

Editing the INIT.ORA file

Using the V$CONTROLFILE_RECORD_SECTION view

Commands:

ALTER DATABASE BACKUP CONTROLFILE

ALTER DATABASE BACKUP CONTROLFILE TO TRACE

HOST COPY

HOST MKDIR

-- Hands-On 03 (Multiplexing controlfiles)
-- 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.

-- First, let's connect 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
-- in the database. 

pause


SELECT *
FROM v$controlfile
/

-- Add one more controlfile to the list.

pause

CLEAR SCR
-- Now, 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
-- Open the INIT.ORA file 
-- located in the PFILE directory.
--\/-- (%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 and close the file.

pause
pause

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

-- The controlfile was added.

pause

CLEAR SCR
-- Now, backup the controlfile.

-- Before making backup, let's first create a directory named
-- 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 is the current date, it is there
-- to let you know the date you backed-up the controlfile.

pause

CLEAR SCR
-- Now, check to see how a controlfile is divided into several sections.

pause

CLEAR SCR
-- It is advisable to backup the CONTROLFILE anytime we change
-- the structure of our database such as adding or dropping
-- tablespace, 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. 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