RDBMS -- enabling custom maintanence for the deployments

During the deployment cycles, we may want to have a normal database, running with all of its components including its listener. We may want to have a database in a form equal to its runtime environment and we want to be the only one that is able to connect to the database.
These kinds of desires may arise for having the deployment processes, which may require no one except the administrator to be connected on an Oracle Database, for which we need all the database functionality up&running. So we are talking about a custom maintanence mode here.

Let's see how we implement such a maintanence mode functionality

We first create a trigger(in this example, it is "LOGON_PREVENT_DURING_DEPLOY") to read the sys_context and prevent the login requests except the one coming from the administrator's machine. (in this example: it only lets the user who has "ermanarslan" as its OS user name, to connect to the database). Note that: This trigger may be modified according to our needs.

Trigger:

CREATE OR REPLACE TRIGGER SYSTEM.logon_prevent_during_deploy
   AFTER LOGON ON DATABASE
DECLARE
   v_osuser    v$session.osuser%TYPE;
   v_program   v$session.program%TYPE;
   CURSOR user_prog
   IS
      SELECT osuser, program
        FROM v$session
       WHERE audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');
BEGIN
   OPEN user_prog;
   FETCH user_prog
    INTO v_osuser, v_program;
   CLOSE user_prog;

   IF LOWER (v_osuser) != ('ermanarslan')
   THEN
      raise_application_error
         (-20001,
          '<<<<< The System is in maintanence mode right now. Check again later  >>>>>'
         );
   END IF;
END;
/
ALTER TRIGGER LOGON_PREVENT_DURING_DEPLOY DISABLE;

Then we create 2 procedure for enabling and disabling the trigger. For enabling the trigger(actually enabling the maintanence mode) before we begin our deployment activities , we execute the enabling procedure and for disabling the trigger(actually the maintanence mode), we execute the disabling procedure.

Procedure for enabling the maintanance mode:

CREATE OR REPLACE procedure Enable_maintanence
 IS
 counter INTEGER := 1 ;
 count_son number;
 sqlStmt VARCHAR2(1000);
 cursor c1 is select a.sid,a.job,b.serial# from dba_jobs_running a, v$session b where a.sid=b.sid;
 jobs c1%ROWTYPE;
BEGIN
select count(*) into count_son from dba_jobs_running;
execute immediate 'alter trigger system.logon_prevent_during_deploy enable';
execute immediate 'revoke ADMINISTER DATABASE TRIGGER from dba';
execute immediate 'revoke ADMINISTER DATABASE TRIGGER from IMP_FULL_DATABASE';
loop
exit when count_son=0;
open c1 ;
fetch c1 into jobs;
DBMS_JOB.REMOVE(jobs.job);
sqlStmt := 'ALTER SYSTEM KILL SESSION ''' ||
                TO_CHAR(jobs.sid) ||
                ',' ||
                TO_CHAR(jobs.serial#) ||
                '''' ;

EXECUTE IMMEDIATE sqlStmt;

EXIT WHEN count_son=counter;
counter := counter+1;
close c1;
END LOOP;
execute immediate 'alter system set job_queue_processes=0';
END;
/

Procedure for disabling the maintanance mode:

CREATE OR REPLACE procedure Disable_maintanence
 IS
BEGIN
execute immediate 'alter trigger system.logon_prevent_during_deploy disable';
execute immediate 'grant ADMINISTER DATABASE TRIGGER to dba';
execute immediate 'grant ADMINISTER DATABASE TRIGGER to IMP_FULL_DATABASE';
execute immediate 'alter system set job_queue_processes=20';
END;

/

Of course, the enabling procedure can be modified for killing all current user sessions before performing any deployment/maintanence tasks. Lastly, you can always use the standard restricted mode of the Oracle Database (ALTER SYSTEM ENABLE RESTRICTED SESSION). This custom maintanence mode functioanlity just an alternative.