There are several ways to affect Oracle database availability.
1. First when you start the database you can start it in mount or in nomount state.
SQL> STARTUP NOMOUNT;
SQL> STARTUP MOUNT;
These states are used with administrating operations. For example with nomount state you can run restore jobs for controlfiles with RMAN and with mount state you can change database settings such as archiving. But with these states you database is answering only locally and only for certain commands.
For opening database to general use run following (this is same as: alter database open read write; ):
SQL> ALTER DATABASE OPEN;
If you wan't to open the database only READ access you can do it with this (this doesn't allow any writes):
SQL> ALTER DATABASE OPEN READ ONLY;
You can also use (RESTRICT can be also used with open, mount and nomount):
SQL> STARTUP RESTRICT;
With restricted mode only users with RESTRICTED SESSION system privilege can connect into database and only local connections are available. Only database administrators should have this privilege.
2. Second you can use logon and logoff triggers to make restrictions for user sessions connecting into database or disconnecting:
Remember that connection restriction made by these kind of trigger does not affect database administrator users unless you exclude ADMINISTER DATABASE TRIGGER privilege from that dba user.
Syntax goes like this:
#Logon trigger:
SQL> CREATE OR REPLACE TRIGGER <trigger_name>
AFTER LOGON ON <schema_name>
BEGIN
<trigger actions>
END;
#Logoff trigger:
SQL> CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE LOGOFF ON <schema_name>
BEGIN
<trigger actions>
END;
More info can be find for example here:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_triggers.htm#TDDDG52900
3. Third you can use sqlnet.ora (this works on Oracle11g with older version you need to use
protocol.ora config file) to make IP restriction for database connections:
Add following lines in sqlnet.ora file:
#This creates a hard failure when host name in exclude/invited list fail to resolve IP address:
tcp.validnode_checking = YES
#To specify which clients are denied access to the database:
tcp.excluded_nodes = {list of IP_addresses}
#To specify which clients are allowed access to the database:
tcp.invited_nodes = {list of IP_addresses}
After you have done these changes you have to restart the listener:
lsnrctl status
lsnrctl stop
lsnrctl start
lsnrctl status
4. Fourth you can use your firewall settings (or iptables locally) to restrict database availability.
