Oracle Listener Security

Listener security is an often overlooked component in locking down the database from unexpected access.  With network firewalls in place, database username and password, etc. database administrators is some cases overlook what it more likely the largest open threat to the database and that is the internal user with a username and password and access from local desktop tools. 

We know from several security experts that the most security lapses come from internal sources and things like data download into spreadsheets and other files can be the largest risk to the data being compromised as local machines tend to not have the security controls servers and applications do.

This opens the question why we would allow access to databases from a local machine such as a laptop or other local hosts on the network.  In a typical network there may not be a firewall in place between the client and server for access control.  One way we can lock this down would be to ensure that only authorized clients can access the database through a listener filter to ensure that only the secured servers access the data.

The listener filter acts as additional internal security to ensure that even on your local network that connections from authorized machines are allowed and can help ensure they you data does not come to a local laptop and saved on that laptop and then compromised if that laptop is lost or stolen.

Configuring the IP filter on the listener is done via the listener.ora and the following parameters.

tcp.validnode_checking turns on the listener IP filter.

tcp.validnode_checking = yes

tcp.invited_nodes is set to a comma delimited list of hosts/ipaddresses that are allowed through the listener to make a database connection.

tcp.invited_nodes = (hostname1, hostname2)

tcp.excluded_nodes is set to a comma delimited list of hosts/ip addresses that are to be denied access to connect to the database through the listener.

tcp.excluded_nodes = (hastname3, hostname4)

tcp.excluded nodes is a more passive form of filtering by excluding known hosts you do not want to have access, typically the more proactive approach is to use tcp.invited_nodes and it is the most commonly used.  This will only allow servers in the list to make a connection and offers the most assured security and when invited_nodes is used anything not in the list is denied access and therefore excluded.

Change Oracle ASM Diskgroup Redundancy

Summary

This process is utilized to change the redundancy of an existing diskgroup.  The example utilizes a RAC environment, but same process can be utilized for single instance environment with small variation.

Basic High Level Process

1.       Backup all databases that are on disk group(s) where redundancy needs changed.

2.       Remove disk group

3.       Create disk group at new redundancy level

4.       Restore databases

Process Detail Steps

1.  Shutdown database(s) to get all activity out of database

Example

srvctl stop database –d db1

. oraenv

db21

sqlplus / as sysdba

shutdown immediate

2. Start database instances (in RAC on node 1 and make sure all other instances of the database(s) are down) in restricted mode this will make sure no activity gets into database.

. oraenv

db1

sqlplus / as sysdba

startup mount

. oraenv

db21

sqlplus / as sysdba

startup mount

3. Take backup for each database on the diskgroup(s) where redundancy needs to be changed.

Example uses standard script to do level 0 hotbackup and backs up archive logs since database is in mount state no changes going on in DBs:

/u01/app/oracle/scripts/rman_hot_backup_rac.sh db1 0

/u01/app/oracle/scripts/rman_hot_backup_rac.sh db21 0

4. Shut database(s) down and re-start database instance(s) in nomount state, in this state we can do what we need with the disk groups and it be set to restore databases once the disk group(s) work is complete.

Example:

. oraenv

db1

sqlplus / as sysdba

shutdown immediate

startup nomount

exit

. oraenv

db21

sqlplus / as sysdba

shutdown immediate

startup nomount

5. Remove Existing diskgroup(s) that need redundancy changed, in a RAC environment make sure disk groups are dismounted from other ASM instances or other ASM instances are shutdown.

** On other nodes in ASM instance dismount the diskgroups

alter diskgroup data dismount ;

alter diskgroup reco dismount ;

Example

. oraenv

+ASM1

sqlplus / as sysasm

DROP DISKGROUP DATA INCLUDING CONTENTS;

DROP DISKGROUP RECO INCLUDING CONTENTS;

6. Recreate Disk Group(s) so that they are a different redundancy in this example we are doing external redundancy and au_size of 4M

Example:

. oraenv

+ASM1

sqlplus / as sysasm

CREATE DISKGROUP DATA EXTERNAL REDUNDANCY DISK '/dev/sdd1' SIZE 255996M ,

'/dev/sde1' SIZE 255996M ,

'/dev/sdf1' SIZE 255996M ,

'/dev/sdg1' SIZE 255996M 

ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' ;

CREATE DISKGROUP RECO EXTERNAL REDUNDANCY DISK '/dev/sdh1' SIZE 255996M ,

'/dev/sdi1' SIZE 255996M 

ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='4M' ;

7. Check that disk groups are the right size now and au_size and state is good.

select name, allocation_unit_size/1024/1024, total_mb, usable_file_mb, type, state

from v$asm_diskgroup ;

8. Restore database(s)

Example:

.oraenv

db1

rman target=/ catalog=/@rman

SET DBID 807730062;

run {

RESTORE CONTROLFILE FROM AUTOBACKUP ;

ALTER DATABASE MOUNT;

}

run {

restore database ;

recover database ;

}

.oraenv

db21

rman target=/ catalog=/@rman

SET DBID 807730062;

run {

RESTORE CONTROLFILE FROM AUTOBACKUP ;

ALTER DATABASE MOUNT;

}

run {

restore database ;

recover database ;

}

9. Open database(s) with resetlogs         

Example:

. oraenv

db1

sqlplus / as sysdba

alter database open resetlogs ;

. oraenv

db21

sqlplus / as sysdba

alter database open resetlogs ;

10. Check control file entries as after restore of control file then names change, then set the

initialization parameter to the new names

Example:

. oraenv

+ASM1

asmcmd

Check control files for the databases in their locations and update the init.ora or spfile

alter system set control_files='+RECO/db/controlfile/current.256.839933795', '+DATA/db/controlfile/current.257.839933795' scope=spfile sid='*' ;

vi init.ora file so just need to update init.ora file

*.control_files='+RECO/db2/controlfile/current.268.839934043', '+DATA/db2/controlfile/current.271.839934043'

11. bounce databases after restore and recover to ensure all is back to normal

Example:

. oraenv

db1

sqlplus / as sysdba

shutdown immediate

exit

srvctl start database -d db

. oraenv

db21

sqlplus / as sysdba

shutdown immediate

startup

exit

12. Take new level 0 hot backups of both databases.

Example:

/u01/app/oracle/scripts/rman_hotbackup_rac.sh db1 0 &

/u01/app/oracle/scripts/rman_hotbackup_rac.sh db21 0

ALL DONE!!