Hands-On 05 (Changing the database mode)

As a DBA, you are responsible for changing the database mode for database maintenance purposes. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Database modes:

SUSPEND

RESUME

RESTRICTED SESSION

QUIESCE RESTRICTED

Using the RESOURCE_MANAGER_PLAN parameter

SHOW PARAMETER resource_manager_plan

Commands:

CREATE SPFILE='c:\spfileschool.ora' FROM PFILE

ALTER SYSTEM SET

SHOW PARAMETER

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

pause

--Start

CLEAR SCR
-- In this exercise you will learn how to change
-- the database mode, such as SUSPEND,RESUME,RESTRICTED 
-- SESSION, and QUIESCE RESTRICTED.

-- Connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager@school AS SYSDBA

pause

CLEAR SCR
-- First, change the database to the SUSPEND mode.

pause


ALTER SYSTEM SUSPEND
/

-- Now, the system is completely in the halted mode. No sessions
-- can do any tasks on it.

pause

CLEAR SCR
-- Open another session and connect as the ISELF user and check
-- how that affects the ISELF session.

-- SQL> CONNECT iself/schooling@school

pause
pause

CLEAR SCR
-- Resume the system mode.

pause


ALTER SYSTEM RESUME
/

pause

CLEAR SCR
-- Open the database while simultaneously preventing all users but
-- DBA from accessing the database objects.

pause


ALTER SYSTEM ENABLE RESTRICTED SESSION
/

-- Notice that no user can login to SQL*PLUS to access to the 
-- database objects.

pause

pause
pause

CLEAR SCR
-- Change the database mode to a quiescing state where only DBA
-- transactions, queries, or PL/SQL statements are allowed to be
-- executed.

pause


ALTER SYSTEM QUIESCE RESTRICTED
/

-- Notice that the Oracle Resource Manager must have remained 
-- active in all opened instances in order to do the 
-- ALTER SYSTEM command.

pause


CLEAR SCR
-- Query the RESOURCE_MANAGER_PLAN parameter.

pause


SHOW PARAMETER resource_manager_plan

-- Notice that the RESOURCE_MANAGER_PLAN has a NULL value.

pause

CLEAR SCR
-- Set the parameter RESOURCE_MANAGER_PLAN to a non-null value.

-- Open the init.ora parameter file and Add the 
-- RESOURCE_MANAGER_PLAN = 'SYSTEM_PLAN', and then shutdown and 
-- startup the database. Then, execute the ALTER command.

pause


SHUTDOWN IMMEDIATE

STARTUP PFILE=%ORACLE_HOME%\admin\school\pfile\init.ora

pause

CLEAR SCR
-- Query the RESOURCE_MANAGER_PLAN parameter.

pause


SHOW PARAMETER resource_manager_plan

pause

CLEAR SCR
-- Change the database mode to a quiescing state where only
-- DBA transactions, queries, or PL/SQL statements are allowed to 
-- execute.

pause


ALTER SYSTEM QUIESCE RESTRICTED
/

-- The ALTER SYSTEM command was successful.

-- Notice that the Oracle Resource Manager must have remained
-- active in all opened instances.

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