iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 06

“"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?