Posts Tagged ‘Patching Oracle RDBMS’

Oracle – Restricted Database Session

November 17, 2010 Leave a comment

Sometimes it is necessary to do work on a database without any other users being logged in. It is possible to restrict the database session in such a case. When the database starts in restricted mode only users with restricted session privileges can get access to the database even though it is technically in open mode.

Enable / Disable Restricted Session

SQL> startup restrict ORACLE instance started.
Total System Global Area 504366872 bytes
Fixed Size 743192 bytes
Variable Size 285212672 bytes
Database Buffers 218103808 bytes
Redo Buffers 307200 bytes
Database mounted.
Database opened.

Startup the database in restricted mode

The alter system command can be used to put the database in and out of restricted session once it is open:

SQL> alter system enable restricted session;
system altered
SQL> alter system disable restricted session;
system altered

Find and disconnect users connected during restricted session

Any users connected to the database when restricted session is enabled will remain connected and need to be manually disconnected
To check which users are connected to the database run the following:

SQL> SELECT username, logon_time, process from v$session;
-------- --------- -------
17-NOV-10 606252
17-NOV-10 598054
17-NOV-10 540690
17-NOV-10 421948
17-NOV-10 561182
17-NOV-10 512046
17-NOV-10 1257542
SYS      17-NOV-10 1310796
8 rows selected.

By querying the process id you can then issue a kill -9 <process_id> at the operating system level to disconnect the connected user. The blank usernames in v$session refer to background database processes.

Check if database in restricted mode

If you are unsure whether the database is in restricted session or not you can run the following query to check:

SQL> SELECT logins from v$instance;


Oracle – Opatch

October 20, 2010 Leave a comment

Opatch is an Oracle utility for the application and rolling back of interim patches to the Oracle RDBMS.  In order to use opatch it is recommended to set its location in $PATH.


OPatch can be found under $ORACLE_HOME/OPatch

Current Patch History

To find the current patch history run the following:

$ opatch lsinventory [ -all ] [ -i[nvPtrLoc] <Path to oraInst.loc> ][ -oh <ORACLE_HOME>]

– list all patches currently applied.

OPatch Version

$ sh opatch version
Invoking OPatch
OPatch Version:
OPatch succeeded.

OPatch Apply

$ opatch apply [-f[orce]] [-i[nvPtrLoc]<path to oraInst.log] [-m[inimize_downtime]] [-n[o_inventory]] [-oh<ORACLE_HOME>] [<patch_location>]

– used to remove any conflicting patches and force the patch to be applied


– used to locate the oraInst.loc file


– used only in RAC instances, allowing for the remaining instances to remain up during patching. It is the responsibility of the user to shutdown each instance before applying the patches, after the last instance is patched then all instances can be brought back up.


– This only works if the inventory is unavailable and allows for bypassing the inventory for reading and updates.


– Directory to be used instead of the default $ORACLE_HOME


– Where to install the patch from

OPatch Rollback

Sometimes it is necessary to rollback a patch once it has been applied.

$ opatch rollback -id patch_id [-i[nvPtrLoc]<Path to oraInst.loc>] [-oh <ORACLE_HOME>] -pj patch_dir

– The id of the patch that requires rollback


– The directory that is a valid patch area.