|
Changing the database mode
Introduction
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:
ALTER SYSTEM
SET
SHOW PARAMETER
Hands-on
In this
exercise you will learn how to change the
database mode, such as SUSPEND, RESUME,
RESTRICTED SESSION, and QUIESCE RESTRICTED.
Connect to a
database
Connect to SQL*Plus as the system/manager
user.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
SUSPEND mode
First, change the database to the SUSPEND
mode.
SQL> ALTER SYSTEM
SUSPEND
/
Now, the system is completely in
the halted mode. No sessions can do any
tasks on it.
To check that, open another session and
connect as the ORACLE user and check how
that affects the ORACLE session.
SQL> CONNECT
oracle/learning@yourhost
Notice that
the user is not able to do anything on
his/her session.
Resume a
database
To resume the
database to the system mode, go back to the
system/manager session and resume the
database.
SQL > ALTER SYSTEM
RESUME
/
Restricted Session
Open the
database while simultaneously preventing all
users but DBA from accessing the database
objects. As a system/manager user, do the
following command.
SQL> ALTER SYSTEM
ENABLE RESTRICTED SESSION
/
Notice that this time no user can
login to SQL*PLUS to access to the database
objects.
Quiescing state
Now, let us to
change the database mode to a quiescing
state where only DBA transactions, queries,
or PL/SQL statements are allowed to be
executed.
SQL > ALTER SYSTEM
QUIESCE RESTRICTED
/
Notice that the Oracle Resource
Manager must have remained active in all
opened instances in order to do this ALTER
SYSTEM command.
To check the
Oracle Resource Manager active options,
query the RESOURCE_MANAGER_PLAN parameter.
SQL> SHOW PARAMETER
resource_manager_plan
Notice that the
RESOURCE_MANAGER_PLAN has a NULL value.
Turn on
Resource Manager Plan
To
set the parameter RESOURCE_MANAGER_PLAN to a
non-null value, open the init.ora parameter
file and Add the following line to it.
RESOURCE_MANAGER_PLAN = 'SYSTEM_PLAN'
And then
shutdown and startup the database. Then,
execute the ALTER command.
After you changed the INIT<sid>.ORA
parameter file, do the shutdown command.
SQL> SHUTDOWN
IMMEDIATE
SQL> -- start the database.
SQL> CONNECT
system/manager@yourhost AS SYSDBA
SQL> STARTUP PFILE=%ORACLE_HOME%.ora
Query the RESOURCE_MANAGER_PLAN parameter.
SQL> SHOW PARAMETER
resource_manager_plan
Change the database mode to a quiescing
state where only DBA transactions, queries,
or PL/SQL statements are allowed to execute.
SQL> ALTER SYSTEM
QUIESCE RESTRICTED
/
Note that this time, this ALTER
SYSTEM command was successful. Remember that
the Oracle Resource Manager must have
remained active in all opened instances.
Questions:
Q: How many
different types of database mode can you
change your database to?
Q: Describe
the Oracle database SUSPEND mode.
Q: Describe
the Oracle database RESUME mode.
Q: Describe
the Oracle database RESTRICTED SESSION mode.
Q: Describe
the Oracle database QUIESCE RESTRICTED mode.
Q: Descript
the RESOURCE_MANAGER_PLAN parameter.
Q: How do you
change a database mode to the SUSPEND mode?
Q: How do you
turn on the Resource Manager Plan? |
