|
More Resources by
Google: |
|
|
|
|
Topics: Changing the database
mode
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
|