When a Security Recommendation Became an Operational Incident

A database team was implementing recommendations from the latest CIS Benchmark for an Oracle 19c Server.

Among the reviewed items were hidden parameters related to symbolic link handling and directory validation.

The implementation was completed during a scheduled maintenance window.

- Database startup was successful.

- Applications connected successfully.

- All standard validation checks passed.

- The change was signed off as successful.

Monday Morning

The following morning, during routine health checks, an engineer noticed that all scheduled Oracle Data Pump export jobs had failed overnight.

- The database was healthy.

- Applications were working.

- Monitoring showed no major issues.

- Only the export jobs had failed.

Investigation

The first step was to review the Data Pump logs generated by the failed export jobs.

The logs indicated that Data Pump was unable to access the export directory used by the DIRECTORY object.

At this point, the focus shifted from database availability to filesystem access.

The team validated:

• Oracle DIRECTORY object configuration
• Directory privileges
• Windows filesystem permissions
• Symbolic link configuration
• Target storage accessibility

The DIRECTORY object was valid.

The symbolic link existed and was accessible from the operating system.

The target storage location was also available.

This created an interesting situation:

Windows could access the path successfully, but Oracle Data Pump could not.

The investigation then turned to the changes implemented during the weekend maintenance window.

The Technical Detail

The Oracle Data Pump DIRECTORY object pointed to a Windows path that contained a symbolic link.

The symbolic link redirected exports to the actual storage location.

This configuration had existed for years and had never caused operational issues.

After reviewing the hardening changes, the team discovered that newly implemented security-related parameters were affecting Oracle's handling of symbolic links.

The result was that Data Pump could no longer use the DIRECTORY object as expected.

The More Interesting Discovery

While reviewing the CIS Benchmark documentation, another observation emerged.

The benchmark did not explicitly state:

"Set this parameter to this value in all environments."

Instead, the recommendation required administrators to evaluate the security implications and determine whether the setting was appropriate for their environment.

In other words, the benchmark required analysis, not blind implementation.

The environment contained a legitimate dependency on symbolic links.

That dependency had not been identified before the change was implemented.

Root Cause

Technically, the export failure was caused by the interaction between Oracle's symbolic link security controls and the existing Data Pump configuration.

From a process perspective, the root cause was different.

The parameter change was implemented without a full assessment of:

• Existing architecture
• Filesystem dependencies
• Oracle Data Pump usage
• Business impact

The Datapump failure was simply the visible symptom.

Lessons Learned

1. Compliance Recommendations Are Not Always Mandatory Configurations

A benchmark recommendation should trigger analysis.

It should not automatically trigger implementation.

Understanding why a recommendation exists is just as important as understanding how to implement it.

2. Hidden Parameters Deserve Additional Scrutiny

Hidden parameters can influence behavior in ways that are not immediately obvious.

Any change involving hidden parameters should include:

• Impact assessment
• Architecture review
• Functional testing
• Rollback planning

3. Test Critical Business Functions

Database startup and application connectivity are not enough.

Validation should include:

• Data Pump exports
• RMAN backups
• Batch jobs
• Replication
• Filesystem-dependent operations

4. Architecture Assumptions Matter

The symbolic link had existed for years.

Nobody questioned it because it worked.

The change simply exposed a dependency that already existed.

Final Thoughts

One of the easiest mistakes in technology is assuming that every recommendation should be implemented exactly as written.

Benchmarks provide guidance.

Engineering requires judgement.

The most important lesson from this incident was not related to Oracle Data Pump or symbolic links.

It was a reminder that before implementing any security recommendation, we should first ask:

"What problem is this recommendation trying to solve, and what existing assumptions in my environment might it affect?"

Asking that question before implementation could have prevented the incident altogether.

 

Oracle DB migration - Information collection for Primary server

 OS level info:

Oracle_Home for grid :

Oracle_Home for RDBMS :

Oracle_base :

cat /etc/sysctl.conf |grep -v \# |sort

cat /etc/security/limits.conf  |grep oracle

uname -a

cat /etc/redhat-release

cat /etc/hosts

Files required for DB configuration :

Init.ora, TNSNAMES.ORA, Listener.ora , Sqlnet.ora from both grid and rdbms home.

Patch Details of Oracle grid   "opatch lspatches"

Patch details of Oracle RDBMS  "opatch lspatches"

Yesterday awr report of 10AM to 11AM AND 3pm TO 4pm in html format

init.ora, tnsnames.ora, listener.ora , sqlnet.ora from both grid and rdbms home.

output of "asmcmd lsdg"

output of os command "uname -mrs"

output of df-h of running database server

Database Info:

SET lines 170 NUMWIDTH 12 PAGES 10000 LONG 2000000000

COL version FORMAT a12

COL comp_id FORMAT a8

COL schema LIKE version

COL status FORMAT a12

COL comp_name FORMAT a35

COL value FORMAT a15

COL member FORMAT a100

COL file_name FORMAT a100

COL owner FORMAT a25

COL ddl FORMAT a100

col PLATFORM_NAME format a50;

define fileName=dba_snapshot

COLUMN spool_time NEW_VALUE _spool_time NOPRINT

SELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;

COLUMN dbname NEW_VALUE _dbname NOPRINT

SELECT name dbname FROM v$database;

spool &FileName._&_dbname._&_spool_time..txt

ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';

select instance_name,status from gv$instance;

SELECT * FROM v$version;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

SELECT comp_id,schema,status,version,comp_name  FROM dba_registry ORDER BY 1;

SELECT owner, count(*) FROM dba_objects WHERE owner IN ('CTXSYS', 'OLAPSYS', 'MDSYS', 'DMSYS', 'WKSYS', 'LBACSYS','ORDSYS', 'XDB', 'EXFSYS', 'OWBSYS', 'WMSYS', 'SYSMAN')  OR owner LIKE 'APEX%' and status='INVALID' GROUP BY owner  ORDER BY 1;

SELECT owner, object_type, COUNT(*)  FROM dba_objects WHERE object_type LIKE 'JAVA%' GROUP BY owner, object_type ORDER BY 1,2;

SELECT owner, object_type,status, COUNT(*)  FROM dba_objects GROUP BY owner, object_type,status ORDER BY 1,2;

SELECT * FROM nls_database_parameters  WHERE  parameter LIKE '%SET'  ORDER  BY 1;

SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;

SELECT group#,bytes/1024/1024,members,status FROM v$log ORDER BY 1;

SELECT * FROM v$logfile  ORDER BY 1,3;

SELECT * FROM v$pwfile_users;

select filename, status, bytes from v$block_change_tracking;

Patch details:

set pagesize 1000

set linesize 300

col comments format a50;

col ACTION_TIME format a60;

select ACTION_TIME,ACTION,VERSION,COMMENTS from registry$history;

select PATCH_ID,ACTION,ACTION_TIME,DESCRIPTION from DBA_REGISTRY_SQLPATCH;

select ENDIAN_FORMAT,PLATFORM_NAME from V$TRANSPORTABLE_PLATFORM where PLATFORM_NAME=(select PLATFORM_NAME from v$database);

select status,instance_name,database_role,open_mode,platform_name,platform_id from v$database,v$instance;

select sum(bytes)/1024/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024/1024 from dba_temp_files;

col value format a100;

col name format a50;

select 'Alter system set '|| name || '=' || value || ' scope=spfile sid=*;'  from v$parameter where ISDEFAULT='FALSE';

select name, value from v$parameter where ISDEFAULT='FALSE';

Redo Logs if RAC:

select thread#, group#,MEMBERS, count(1) from gv$log group by thread#, group#,MEMBERS order by 1,2 ; 

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)as "Size in GB"  from gv$log order by 1,2; 

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from gv$standby_log order by 1,2;

Redo if Single Instance:

col MEMBER format a50;

select member from v$logfile;

select thread#, group#,MEMBERS, (bytes/1024/1024/1024)"Size in GB" from v$log order by 1,2;

select thread#,group#,(bytes/1024/1024/1024)"Size in GB" from v$standby_log order by 1,2;

Archive location 

select * from v$recovery_area_usage where file_type='ARCHIVED LOG';

select round(sum(blocks*block_size)/1024/1024/1024) as "Archive_per_day_GB" from v$archived_log where first_time>sysdate-7 ;

select * from (select round(sum(blocks*block_size)/1024/1024/1024) "Archive_per_day_GB", to_char(first_time,'dd-mm-yyyy') "Date" from v$archived_log where first_time>sysdate-7 group by to_char(first_time,'dd-mm-yyyy')) order by 2 asc;

Users profile:

col profile format a20;

col RESOURCE_NAME format a40;

col LIMIT format a20;

select distinct profile,RESOURCE_NAME,LIMIT from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Select 'alter PROFILE '||  profile || ' limit ' ||  RESOURCE_NAME  ||' '|| limit ||';'  from dba_profiles where profile in (select profile from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM',

'XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES',

'DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA',

'OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS')) order by profile,RESOURCE_NAME;

Tablespace Information:

set linesize 300

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024) TBS_GB  from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024) TBS_MB from dba_data_files group by TABLESPACE_NAME order by 2;

select TABLESPACE_NAME, round(sum(bytes)/1024/1024/1024)  from dba_temp_files group by TABLESPACE_NAME order by 2;

select 'CREATE TABLESPACE '||TABLESPACE_NAME||' DATAFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1) ||'G AUTOEXTEND off '|| 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_data_files  where TABLESPACE_NAME not in('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS') and TABLESPACE_NAME not in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select 'CREATE TEMPORARY TABLESPACE '||TABLESPACE_NAME||' tempFILE ''/oradata01/oraclesid/'||lower(TABLESPACE_NAME)||'_01.dbf'''||' SIZE '||(round(sum(Bytes)/1024/1024/1024)+1)||'G AUTOEXTEND off EXTENT MANAGEMENT'  || ' LOCAL UNIFORM SIZE 8M ONLINE PERMANENT;' from dba_temp_files  where TABLESPACE_NAME in (select TABLESPACE_NAME from dba_temp_files) group by tablespace_name order by TABLESPACE_NAME; 

select username,profile,account_status  from dba_users where username not in('SCOTT','ORACLE_OCM','OJVMSYS','SYSKM','XS\$NULL','GSMCATUSER','MDDATA','SYSBACKUP','DIP','SYSDG','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','GSMUSER','AUDSYS','FLOWS_FILES','DVF','MDSYS','ORDSYS','DBSNMP','WMSYS','APEX_040200','APPQOSSYS','GSMADMIN_INTERNAL','ORDDATA','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','DVSYS','SI_INFORMTN_SCHEMA','OLAPSYS','LBACSYS','OUTLN','SYSTEM','SYS','MGMT_VIEW','SYSMAN','APEX_030200','OWBSYS_AUDIT','EXFSYS','OWBSYS') order by 3;

col PATH format a60;

select name,path,  disk_number disk_#, mount_status,header_status, state, round(total_MB/1024), round(free_MB/1024) from v$asm_disk order by group_number;

select NAME, BLOCK_SIZE, round(TOTAL_MB/1024), round(FREE_MB/1024) ,state,type from  v$ASM_DISKGROUP;

var OHM varchar2(100);

EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

PRINT OHM

> host df -h

Configure TDE on Oracle 19c Multitenant-Database Server on Windows

 1) Set Parameters

<CDB>

-- Wallet root location: E:\oracle\base\admin\tdedb\wallet

sqlplus / as sysdba

SQL> alter system set WALLET_ROOT="E:\oracle\base\admin\tdedb\wallet" scope=spfile;

System altered.

SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope=both;

-- The following parameter setting is required for patch level 19.25 (Oct-24)