|
""A clever man
commits no minor blunders." - Goethe (1749-1832) |
Read
first then play the video:
DBA-VIDEO -Changing
the database mode
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@school 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 ISELF user and
check how that affects the ISELF session.
SQL> CONNECT iself/schooling@school
<mailto:iself/schooling@school>
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@school 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.
|
"All truth
passes through three stages. First, it is ridiculed. Second, it
is violently opposed. Third, it is accepted as being
self-evident." - Arthur Schopenhauer (1788-1860) |
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?
|