Oracle Rac Storage migration and Data center Migration

Storage Migration 

Storage migration is pretty simple and online  activity . We just need to get new disk of same size .

After new asm disk are allocated  to server  we just need to add new disk first to diskgroup . 

Once new disk are added wait for rebalancing to complete and later we can drop old disk.

Same procedure applies for Grid diskgroup 

Ideally disk path are not changed , If disk path are changed we need to update asm_diskstring parameter in asm 

alter system set asm_diskstring=’/dev/ORACLE/*/*’ scope=spfile sid='*';

Below are sample commands 

1) Adding new disk to diskgroup  

ALTER DISKGROUP Grid add  disk '/dev/rasm_lc_flash_04'  SIZE 1g  ;  

( for data diskgroup you can add multiple disk at one time however for grid diskgroup go one disk at a time ) 

ALTER Diskgroup ASM_PERAMGC_DATA_DG3  ADD DISK
'/dev/rhdisk149',
'/dev/rhdisk150',
'/dev/rhdisk151',
'/dev/rhdisk152'
rebalance power 6;

2) Verify  rebalancing is completed and check if new disk mapped under diskgroup 

Select * from v$asm_operation;

Select header_status , group_number from v$asm_disk where path ='/dev/rasm_lc_flash_04'  ;

3) Drop  old disk  from diskgroup . We can drop multiple disk at one time . Please note we need  to drop disk with disk name and not path .

ALTER Diskgroup ASM_PERAMGC_DATA_DG3  Drop disk 
'ASM_PSBP_TIER1_DG1_0000', 'ASM_PSBP_TIER1_DG1_0005' REBALANCE POWER 11 ; 

For Oracle home binary filesystem  no action was  taken from our dba team .   Oracle home is also configured on San  , unix  team will do needful  of copying filesystem to new  and renaming filesystem. Relinking oracle home and setting up acl might be needed .

Below is just overview what i understand Unix team did online migration of filesystem . It depends on operating system to operating system . 

•  Check new device which ‘ll be used after migration
•  Check LVM device which you need to migrate
•  Create PV with new added LUN device
•  Extend VG with new added PV
•  Add new mirror device to LVM.
•  Remove old disk from LVM.Before remove old disk from LVM you need to check mirror state with “lvdisplay -v” or “lvs -o+devices” command.
•  Remove old pv from Volume Group(vg).
•  Remove old pv

 

Data center Migration

It may be necessary to change or update interface names, or subnet associated with an interface if there is a network change affecting the servers

There are multi ways of data center migration , in our case it was data center migration with lift and shift approach  Placing same server into new network 

You need  update scan dns to point to new ip .  Making other changes  in /etc/hosts 

Private Network : 

Reference :

Following How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1)

 Please  note that below steps are considering we are using for flex asm . 

 1) 

Please take a backup of profile.xml on all cluster nodes before proceeding, as grid user:
$ cd $GRID_HOME/gpnp//profiles/peer/
$ cp -p profile.xml profile.xml.bk

2) . Ensure Oracle Clusterware is running on ALL cluster nodes in the cluster

3) . As grid user:
Get the existing information. For example:
$ oifcfg getif
eth1 100.17.10.0 global public
eth0 192.168.0.0 global cluster_interconnect,asm

Above example shows network eth0 is used for both cluster_interconnect and ASM network.

4) 

Add the new cluster_interconnect information:

$ oifcfg setif -global /:cluster_interconnect[,asm]

For example:
a. add a new interface bond0 with the same subnet
$ oifcfg setif -global bond0/192.168.0.0:cluster_interconnect,asm

b. add a new subnet with the same interface name but different subnet or new interface name
$ oifcfg setif -global eth0/192.68.10.0:cluster_interconnect,asmor

$ oifcfg setif -global eth3/192.168.1.96:cluster_interconnect,asm

5) . As ASMLISTENER is using the private network, modifying the private network will affect 

If different network is used for private network and ASM network, then modify them accordingly.

It is required to add a new ASMLISTENER with the new network configuration. 

Skip this step if the subnet for the ASM network is not changed.

• Add a new ASMLISTENER (for example: ASMNEWLSNR_ASM) with the new subnet, as grid user:

$ srvctl add listener -asmlistener -l -subnet 
eg:
$ srvctl add listener -asmlistener -l ASMNEWLSNR_ASM -subnet 192.168.10.0

• Drop the existing ASMLISTENER (ASMLSNR_ASM in this example) and remove the dependency, as grid user:

$ srvctl update listener -listener ASMLSNR_ASM -asm -remove -force
$ lsnrctl stop ASMLSNR_ASM

Note. -force option is required, otherwise the following error will occur:$ srvctl update listener -listener ASMLSNR_ASM -asm -remove

PRCR-1025 : Resource ora.ASMLSNR_ASM.lsnr is still running
$ srvctl stop listener -l ASMLSNR_ASM
PRCR-1065 : Failed to stop resource ora.ASMLSNR_ASM.lsnr
CRS-2529: Unable to act on 'ora.ASMLSNR_ASM.lsnr' because that would require stopping or relocating 'ora.asm', but the force option was not specified

• Verify the configuration

$ srvctl config listener -asmlistener
$ srvctl config asm

6)   Shutdown Oracle Clusterware on ALL nodes and disable the Oracle Clusterware as root user:

# crsctl stop crs
# crsctl disable crs

7)  Make the network configuration change at OS level as required, ensure the new interface is available on all nodes after the change.

$ ifconfig -a

8)  Enable Oracle Clusterware and restart Oracle Clusterware on all nodes as root user:

# crsctl enable crs
# crsctl start crs

9)Remove the old interface if required:

$ oifcfg delif -global [/]
eg:
$ oifcfg delif -global eth0/192.168.0.0





Public Network : 

Reference :

How to Modify Public Network Information including VIP in Oracle Clusterware (Doc ID 276434.1)

If the change is only public IP address and the new ones are still in the same subnet, nothing needs to be done on clusterware level 
(all changes needs to be done on OS level to reflect the change). If the change involves different subnet or interface, as there is not a 'modify' option -
you will need to delete the interface and add it back with the correct information


oifcfg getif
oifcfg iflist 

% $CRS_HOME/bin/oifcfg/oifcfg delif -global <if_name>[/<subnet>]

% $CRS_HOME/bin/oifcfg/oifcfg setif -global <if_name>/<subnet>:public

For example:

% $CRS_HOME/bin/oifcfg delif -global eth0/10.X.156.0

% $CRS_HOME/bin/oifcfg setif -global eth0/10.X.166.0:public

'

VIP Change : 

srvctl config nodeapps -a

Starting with 11.2, the VIPs depend on the network resource (ora.net1.network), the OCR only records the VIP hostname or the IP address associated with the VIP resource. The network attributes (subnet/netmask/interface) are recorded with the network resource. When the nodeapps resource is modified, the network resoure(ora.net1.network) attributes are also modified implicitly.

From 11.2.0.2 onwards, if only subnet/netmask/interface change is required, network resource can be modified directly via srvctl modify network command.

as root user:

# srvctl modify network -k <network_number>] [-S <subnet>/<netmask>[/if1[|if2...]]

eg:

# srvctl modify network -k 1 -S 110.XX.XX.0/255.255.255.0/<if_name>

There is no need to modify VIP or SCAN if other attributes are not changed.

For 12.1.0.1 release, due to unpublished Bug 16608577 - CANNOT ADD SECOND PUBLIC INTERFACE IN ORACLE 12.1, the srvctl modify network command fails with:

# srvctl modify network -k 1 -S 110.XX.XX.0/255.255.255.0/<if_name>

PRCT-1305 : The specified interface name "<if_name>2" does not match the existing network interface name "<if_name>1"

Workaround is to modify network resource with an empty interface name, then modify it again with the desired interface name, eg:

# srvctl modify network -k 1 -S 110.XX.XX.0/255.255.255.0

# srvctl modify network -k 1 -S 110.XX.XX.0/255.255.255.0/<if_name>2

The bug has been fixed in 12.1.0.2 and above.

Scan Ip change  : 

 Once Ip mapping is chnaged at DNS refresh the SCAN VIPs with the new IP addresses from the DNS entry

# $GRID_HOME/bin/srvctl modify scan -n scan.example.com

# GRID_HOME/bin/srvctl config scan

# GRID_HOME/bin/srvctl modify scan_listener -u

Hostname change  : 

 

In pre-11.2 Oracle Clusterware, private hostname is recorded in OCR, it can not be updated. Generally private hostname is not required to change. Its associated IP can be changed. The only way to change private hostname is by deleting/adding nodes, or reinstall Oracle Clusterware.

In 11.2 Grid Infrastructure, private hostname is no longer recorded in OCR and there is no dependency on the private hostname. It can be changed freely in /etc/hosts

 

Server migration : 

Server migration is not in scope of this blog however just to give glimpse  most common used approach is to install fresh grid cluster in new servers and do migration of database using export import or rman .

There are other online approach available like heterogenous standby and add node/delete node which can also be explored .

References : 

Exact Steps to Migrate ASM Diskgroups to Another SAN/Disk-Array/DAS/etc without Downtime (When ASMLIB Devices Are Involved) (Doc ID 1918350.1)

Oracle Database Auditing

                                           Oracle Database Auditing

Please note that from 12c  Unified Auditing has been introduced . Hence for 12c database and above  views and functionality will be different as pre defined audit policies were introduced  . 

Until Oracle 10g, auditing is disabled by default, but can be enabled by setting the AUDIT_TRAIL static parameter in the init.ora file.

From Oracle 11g, auditing is enabled for some system level privileges.



Oracle stores information that is relevant to auditing in its data dictionary.
Every time a user attempts anything in the database where audit is enabled the Oracle kernel checks to see if an audit record should be created or updated .

Audit data can be stored in a database (SYS.AUD$) table or in an OS file residing in AUDIT_FILE_DEST (default is $ORACLE_HOME/rdbms/audit) directory.
This table is, by default, located in the SYSTEM tablespace. This itself can cause problems with potential denial of service attacks. If the SYSTEM tablespace fills up, the database will hang.

AUDIT_TRAIL can have the following values :

AUDIT_TRAIL={NONE or FALSE| OS| DB or TRUE| DB_EXTENDED| XML |XML_EXTENDED}

The following list provides a description of each value:

NONE or FALSE -> Auditing is disabled. Default until Oracle 10g.
DB or TRUE -> Auditing is enabled, with all audit records stored in the database audit trial (AUD$). Default from Oracle 11g.
DB_EXTENDED –> Same as DB, but the SQL_BIND and SQL_TEXT columns are also populated.
XML-> Auditing is enabled, with all audit records stored as XML format OS files.
XML_EXTENDED –> Same as XML, but the SQL_BIND and SQL_TEXT columns are also populated.
OS -> Auditing is enabled, with all audit records directed to the operating system’s file specified by AUDIT_FILE_DEST

#########################
Unified Auditing in 12c 
#########################


If you enable the unified audit feature announced with 12c, all audit records will be in the UNIFIED_AUDIT_TRAIL table. Logon failure records will not be in this table because the corresponding policy is not enabled by default. The policy ORA_LOGON_FAILURES must be enabled to audit the Failed Logon attempts.


The unified audit trail, which resides in a read-only table in the AUDSYS schema in the SYSAUX tablespace, makes this information available in an uniform format in the UNIFIED_AUDIT_TRAIL data dictionary view.


When the database is writable, audit records are written to the unified audit trail. If the database is not writable, then audit records are written to new format operating system files in the POSIX file system in the $ORACLE_BASE/audit/$ORACLE_SID directory.


Bug 17466854 – Cannot set Unified Auditing in Standard  Edition (MOS)

Patch 17466854



2 new roles were introduced 

AUDIT_ADMIN
Manages audit configuration & audit trail

AUDIT_VIEWER

Analysis audit data



Enable ORA_LOGON_FAILURES to track failed logon attempts . You can enable the ORA_LOGON_FAILURES policy as follows.

SQL> audit policy ORA_LOGON_FAILURES;



To turn off this audit, type the following:

NOAUDIT POLICY hr_emp_select BY dcollins;



Records will then be created. You can see the number of records related to this policy with a query like the one below.

  SELECT audit_type,
         unified_audit_policies,
         action_name,
         return_code,
         COUNT (*)
    FROM unified_audit_trail
   WHERE unified_audit_policies = 'ORA_LOGON_FAILURES'
GROUP BY audit_type,
         unified_audit_policies,
         action_name,
         return_code
ORDER BY COUNT (*);

LOGON success action is audited by unified audit policy ORA_LOGON_FAILURES after executing NOAUDIT & AUDIT command (Doc ID 2305054.1)

SQL> col DBUSERNAME for a10

SQL> col ACTION_NAME for a10

SQL> col UNIFIED_AUDIT_POLICIES for a20

SQL> col RETURN_CODE for 9999999

SQL> select DBUSERNAME,ACTION_NAME,UNIFIED_AUDIT_POLICIES,RETURN_CODE from unified_audit_trail where UNIFIED_AUDIT_POLICIES = 'ORA_LOGON_FAILURES';

To see all the audits captured for the HR user, type

SELECT EVENT_TIMESTAMP, CLIENT_PROGRAM_NAME, ACTION_NAME,
UNIFIED_AUDIT_POLICIES
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'HR'
ORDER BY EVENT_TIMESTAMP DESC;



To see which users or roles have been enabled to be audited by policies in the database, type


By default 7 audit policies will be present in a 12c database.

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

To see what privileges are being audited by default for specific policies 

 SELECT POLICY_NAME, AUDIT_OPTION, AUDIT_OPTION_TYPE
FROM AUDIT_UNIFIED_POLICIES
order by policy_name, AUDIT_OPTION; 


DEFAULT POLICIES IN 12C DATABASE :-

SQL> select distinct POLICY_NAME from AUDIT_UNIFIED_POLICIES;


select AUDIT_OPTION from AUDIT_UNIFIED_POLICIES where POLICY_NAME='ORA_SECURECONFIG';





Manually creating  Audit policy :

 CREATE AUDIT POLICY MY_POLICY
 PRIVILEGES SELECT ANY TABLE
 ACTIONS CREATE TABLE, DROP TABLE;
AUDIT POLICY MY_POLICY BY HR;

CREATE AUDIT POLICY ORA_SELECT  ACTIONS All on owner.table;

CREATE AUDIT POLICY <POLICY_NAME> ACTIONS ALL ONLY TOPLEVEL;

CREATE AUDIT POLICY test_audit_policy

ACTIONS SELECT ON schema.tab1,

SELECT ON schema.tab2,

SELECT ON schema.tab1_VIEW,

SELECT ON schema.tab1_SYNONYM ;

AUDIT POLICY test_audit_policy

AUDIT POLICY <POLICY_NAME>;

AUDIT POLICY <POLICY_NAME> BY <Desired USER>; <<<<< Can add users separated by ','

OR

AUDIT POLICY <POLICY_NAME> except sys,system  <<<<<<< Can add any more.

NOAUDIT POLICY hr_emp_select BY sys,system  ;

NOAUDIT POLICY hr_emp_select ;

How to view audit information with Oracle 12c

After configuring for and turning on auditing, see what audit data is being collected.

DBA_AUDIT_TRAIL shows all audit entries in the system.

DBA_AUDIT_OBJECT shows all audit entries in the system for objects.

DBA_AUDIT_STATEMENT shows audit entries for the statements GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM.

-- find statements audited in the database:

col user_name for a12 heading "User name"

col audit_option format a30 heading "Audit Option"

set pages 1000

prompt

prompt System auditing options across the system and by user

select user_name,audit_option,success,failure from sys.dba_stmt_audit_opts

order by user_name, proxy_name, audit_option

/

-- Find privileges audited in the database:

col user_name for a12 heading "User name"

col privilege for a30 heading "Privilege"

set pages 1000

prompt

prompt System Privileges audited across system

select user_name,privilege,success,failure from dba_priv_audit_opts

order by user_name, proxy_name, privilege

/

select parameter , value from gv$option where parameter like '%Aud%'; 

select * from dba_audit_mgmt_config_params where parameter_name in ('DB AUDIT TABLESPACE') and audit_trail ='UNIFIED AUDIT TRAIL'; 

SELECT table_name , partition_type , def_tablespace_name ,  interval  from dba_part_tables where table_name ='AUD$UNIFIED'; 

SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name   like 'UNIFIED_AUDIT%'; 

select  * from   DBA_SCHEDULER_JOB_details where job_name   like 'UNIFIED_AUDIT%'; 

Cleaning sysaux 


If the ORA_LOGON_FAILURES policy is enabled, your SYSAUX tablespace can be full in a short time, as too many records will be created. You can learn more about SYSAUX tablespace in the article named “SYSAUX Tablespace Usage”.

You can create a job to automatically remove old records. The following command can be used to create a job that will delete every 24 hours. The first deletion will take place 24 hours after you run the command.

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 24,
   AUDIT_TRAIL_PURGE_NAME      => 'Audit_Trail_Purge_Job',
   USE_LAST_ARCH_TIMESTAMP     => TRUE,
   CONTAINER                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/


The following command can be used to change the job’s schedule.

BEGIN
 DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
  AUDIT_TRAIL_PURGE_NAME       => 'Audit_Trail_Purge_Job',
  AUDIT_TRAIL_INTERVAL_VALUE   => 48);
END;
/

Mixed Auditing  Vs  Pure Auditing 

1. Mixed auditing – By default it is enable in 12c. It enables to use both traditional auditing and unified auditing methods. I.e. apart from traditional auditing we can use all the features of unified auditing. Once we are comfortable with the unified concept, we can migrate exiting audit setup to unified policy , we can enable pure auditing.
This serves as a good mediator for an easy and hassle free switch to the preferred Unified auditing.

2. Pure auditing – Once pure auditing is enabled. We cannot use the traditional auditing methods.



Which unified auditing mode enabled for my database

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

FALSE – > MIXED AUDTING
TRUE –> PURE AUDITING


How to change from MIXED to PURE auditing:(relink library)

SQL> shut immediate

[oracle@orcl:~ orcldemo] cd $ORACLE_HOME/rdbms/lib
[oracle@orcl:lib orcldemo] make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME
/usr/bin/ar d /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/libknlopt.a kzanang.o
/usr/bin/ar cr /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/libknlopt.a /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/kzaiang.o
chmod 755 /oradb/app/oracle/product/12.2.0.1/db_1/bin

- Linking Oracle
rm -f /oradb/app/oracle/product/12.2.0.1/db_1/rdbms/lib/oracle

SQL> startup;

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER='Unified Auditing'






SYS AUDITING:-

With mix auditing, sys audit records will be written to both os level and unified_trail also if audit action policy is enable.

FOR MIXED AUDITING:
Administrative user sessions generate SYS audit records. These records are written if the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE.This process writes the records only to the traditional audit trails. However, when unified audit policies are enabled for administrative users,
these unified audit records are also written to unified audit trail.

FOR PURE UNIFIED AUDITING:
All sys audit records will be written to UNIFIED_AUDIT_TRAIL TABLE ONLY

#########################
FGA (FINE_GRAINED_AUDITING)  
#########################

Fine-grain auditing (FGA) allows us to audit users accessing data of a certain criteria. As per standard auditing we can audit select, insert, update and delete operations. We use the package dbms_fga to add, remove, enable and disable FGA auditing policies, it is virtually impossible to bypass these policies, one nice feature is that we can attach handlers (like a trigger) to the policies which can execute procedures.

There are many options that can be applied to the dbms_fga package, here are some simple examples :

The FGA method was introduced in the Oracle 9i database release. However, this method provided support for only the SELECT statements.  With the 10g database release, it becomes possible to extend the FGA method to UPDATE, INSERT, and DELETE statements as well

For the FGA method to work, an audit condition is not needed. The database audits all statements that access the object if you do not specify an audit condition. DELETE statements are always audited because all columns are deleted together. It only creates an audit record if a statement manipulates or selects specific rows that meet the FGA policy.

Creating the FGA policy say “FGA_AUDIT”:

begin
dbms_fga.add_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT',
audit_column=>'name',
enable=>true,
statement_types=>'select,update');
end;
/




Removing FGA policy :-

begin
dbms_fga.drop_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/


Enabling FGA policy :-

begin
dbms_fga.enable_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/


Disabling FGA policy :-

begin
dbms_fga.disable_policy (
object_schema=>'ABDUL',
object_name=>'NEW',
policy_name=>'FGA_AUDIT');
end;
/



Query  FGA audit 

select db_user,scn,sql_text from dba_fga_audit_trail where db_user='ABDUL';

#########################
Audit Management till 11g 
#########################


Levels of auditing  

Three levels of Auditing:-
Statement level
Object level
System/privilege level


Statement level auditing :-

Auditing will be done at statement level.
Statements that can be audited are found in STMT_AUDIT_OPTION_MAP.

SQL> audit table by Abdul;

Audit succeeded.


SQL> select * from DBA_STMT_AUDIT_OPTS;

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS   FAILURE
--------- ---------- ------------ --------- ---------
ABDUL               TABLE        BY ACCESS BY ACCESS


SQL> conn ABDUL/ABDUL;
Connected.

SQL> create table new(name varchar(20));

Table created.

SQL> select USERNAME,TIMESTAMP,ACTION_NAME from dba_audit_trail where USERNAME='ABDUL';

USERNAME             TIMESTAMP ACTION_NAME
-------------------- --------- ------------
ABDUL                 12-AUG-19 CREATE TABLE




Object level auditing :-

Auditing will be done at object level.
These objects can be audited: tables, views, sequences, packages, stored procedures and stored functions.

SQL> audit insert on ABDUL.testtab;

Audit succeeded.

SQL> insert into ABDUL.testtab values('oracle');

1 row created.


SQL> select object_name,object_type,ins,sel from dba_obj_audit_opts where owner='ABDUL';

OBJECT_NAME  OBJECT_TYPE INS     SEL
------------ ----------- ------- ----
TESTTAB      TABLE       S/S     -/-


select username,timestamp,action_name,sys_privilege,priv_used from dba_audit_trail where username='ABDUL'

USERNAME TIMESTAMP ACTION_NAME SYS_PRIVILEGE PRIV_USED                      
-------- --------- ----------- ------------- ----------------- 
DBWR     12-OCT-18 SESSION_REC               INSERT ANY TABLE   






System/Privilege level auditing :-

Auditing will be done at privilege level.
All system privileges that are found in SYSTEM_PRIVILEGE_MAP can be audited.

SQL> audit create any table by ABDUL;

Audit succeeded.

SQL> select privilege,user_name from dba_priv_audit_opts where user_name='ABDUL';

PRIVILEGE                                USER_NAME
---------------------------------------- --------------------
CREATE ANY TABLE                         ABDUL


SQL> grant dba to ABDUL;

Grant succeeded.

SQL> create table testtab(name varchar(20));

Table created.

select username,timestamp,action_name,sys_privilege,priv_used from dba_audit_trail where username='ABDUL'

USERNAME TIMESTAMP ACTION_NAME SYS_PRIVILEGE PRIV_USED                       
-------- --------- ----------- ------------- -----------------
HARI     12-OCT-19 CREATE TABLE              CREATE ANY TABLE

Audit Failed Logins till 11g 

The Oracle auditing utility has this command:

audit create session whenever not successful;

To enable auditing of failed sign-on attempts:

1 - Add initialization parameters & bounce instance:

audit_trail=true

audit_file_dest='/u01/app/oracle/mysid/mydir/'

2 - Enable auditing of failed logion attempts as SYSDBA:

SQL> audit create session whenever not successful;

3 - You can now view failed login attempts in dba_audit_trail:

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;






Purge Audit records :

Purging of  audit records can be done either using  traditional purge method or using New Feature DBMS_AUDIT_MGMT 



Purging using traditional Method :

1)  Take  export  backup of  2019  data  that will be delete using below scrpt 

exp '"/ as sysdba"' FILE=cpp_audit_2019-12-31.dmp log=cpp_audit_2019-12-31.log tables=AUD\$ query=\"WHERE TO_CHAR\(NTIMESTAMP\#,\'YYYY\'\) like \'2019\'\" statistics=none

2) Enable  restricted session 

alter system enable restricted session;

2) create  backup  table with  required  data that needs to be retained  and purge  from aud$

select sum(bytes/1024/1024/1024)  from   dba_segments where segment_name like 'AUD$';    

select count(*)  from aud$ where TO_CHAR(NTIMESTAMP# , 'YYYY') like '%2020%'   ;

( Verify tablespace  has enough space before proceeding )

create table temp_aud tablespace AUDIT_EFP as select * from sys.aud$  where TO_CHAR(NTIMESTAMP# , 'YYYY') like '%2020%'; 

select count(*)  from temp_aud  ;

truncate table sys.aud$;

select count(*) from SYS.AUD$;

insert into sys.aud$ select * from temp_aud;

commit ;

select count(*) from SYS.AUD$;

alter index SYS.I_AUD1 rebuild online;


exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5);

 3) disable  restricted session ;

alter system disable restricted session;

 4) drop backup table created .

Drop table temp_aud ;

Purging using  New New Feature DBMS_AUDIT_MGMT

New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information (Doc ID 731908.1)



Manually Purging 

SELECT * FROM dba_audit_mgmt_config_params;

sql>BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);   NOTE:- (Set FALSE to purge all records/files)
END;
/

OR

sql>BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-5);
END;



Setting auto purge

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

sql>SELECT * FROM dba_audit_mgmt_last_arch_ts;

sql>BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);     NOTE- (Set TRUE to only purge records/files older than the timestamp specified for the audit trail)
END;
/






Generate script to check enabled audit in database  :

SCRIPT: Generate AUDIT and NOAUDIT Statements for Current Audit Settings (Doc ID 287436.1)

Example for statement auditing :

AUDIT ADMINISTER KEY MANAGEMENT  BY ACCESS ;

AUDIT ALTER ANY PROCEDURE  BY ACCESS ;

AUDIT ALTER ANY SQL TRANSLATION PROFILE  BY ACCESS ;

AUDIT ALTER ANY TABLE  BY ACCESS ;

AUDIT ALTER DATABASE  BY ACCESS ;

AUDIT ALTER PROFILE  BY ACCESS ;

AUDIT ALTER SYSTEM  BY ACCESS ;

AUDIT ALTER USER  BY ACCESS ;

AUDIT AUDIT SYSTEM  BY ACCESS ;

AUDIT BECOME USER  BY ACCESS ;

AUDIT COMMENT TABLE  BY ACCESS ;

AUDIT CONTEXT  BY ACCESS ;

AUDIT CREATE ANY JOB  BY ACCESS ;

AUDIT CREATE ANY LIBRARY  BY ACCESS ;

AUDIT CREATE ANY PROCEDURE  BY ACCESS ;

AUDIT CREATE ANY SQL TRANSLATION PROFILE  BY ACCESS ;

AUDIT CREATE ANY TABLE  BY ACCESS ;

AUDIT CREATE EXTERNAL JOB  BY ACCESS ;

AUDIT CREATE LIBRARY  BY ACCESS ;

AUDIT CREATE PUBLIC DATABASE LINK  BY ACCESS ;

AUDIT CREATE SESSION  BY ACCESS ;

AUDIT CREATE SQL TRANSLATION PROFILE  BY ACCESS ;

AUDIT CREATE USER  BY ACCESS ;

AUDIT DATABASE LINK  BY ACCESS ;

AUDIT DIMENSION  BY ACCESS ;

AUDIT DIRECTORY  BY ACCESS ;

AUDIT DROP ANY PROCEDURE  BY ACCESS ;

AUDIT DROP ANY SQL TRANSLATION PROFILE  BY ACCESS ;

AUDIT DROP ANY TABLE  BY ACCESS ;

AUDIT DROP PROFILE  BY ACCESS ;

AUDIT DROP USER  BY ACCESS ;

AUDIT EXEMPT ACCESS POLICY  BY ACCESS ;

AUDIT EXEMPT REDACTION POLICY  BY ACCESS ;

AUDIT GRANT ANY OBJECT PRIVILEGE  BY ACCESS ;

AUDIT GRANT ANY PRIVILEGE  BY ACCESS ;

AUDIT GRANT ANY ROLE  BY ACCESS ;

AUDIT GRANT DIRECTORY  BY ACCESS ;

AUDIT GRANT PROCEDURE  BY ACCESS ;

AUDIT GRANT SEQUENCE  BY ACCESS ;

AUDIT GRANT TABLE  BY ACCESS ;

AUDIT GRANT TYPE  BY ACCESS ;

AUDIT INDEX  BY ACCESS ;

AUDIT LOCK TABLE  BY ACCESS ;

AUDIT LOGMINING  BY ACCESS ;

AUDIT MATERIALIZED VIEW  BY ACCESS WHENEVER NOT SUCCESSFUL  ;

AUDIT NOT EXISTS  BY ACCESS ;

AUDIT PLUGGABLE DATABASE  BY ACCESS ;

AUDIT PROCEDURE  BY ACCESS ;

AUDIT PROFILE  BY ACCESS ;

AUDIT PUBLIC DATABASE LINK  BY ACCESS ;

AUDIT PUBLIC SYNONYM  BY ACCESS ;

AUDIT PURGE DBA_RECYCLEBIN  BY ACCESS ;

AUDIT RESTRICTED SESSION  BY ACCESS ;

AUDIT ROLE  BY ACCESS ;

AUDIT ROLLBACK SEGMENT  BY ACCESS ;

AUDIT SELECT ANY DICTIONARY  BY ACCESS ;

AUDIT SELECT TABLE  BY ACCESS WHENEVER NOT SUCCESSFUL  ;

AUDIT SEQUENCE  BY ACCESS ;

AUDIT SYNONYM  BY ACCESS ;

AUDIT SYSTEM AUDIT  BY ACCESS ;

AUDIT SYSTEM GRANT  BY ACCESS ;

AUDIT TABLE  BY ACCESS ;

AUDIT TRANSLATE ANY SQL  BY ACCESS ;

AUDIT TRIGGER  BY ACCESS ;

AUDIT TYPE  BY ACCESS ;

AUDIT USER  BY ACCESS ;

AUDIT VIEW  BY ACCESS ;

Reference :

https://docs.oracle.com/cd/E25054_01/network.1111/e16543/auditing.htm







#########################
Pointing Audit to syslog 

#########################

Recently we moved our  audit pointing to syslog  since we onboard Splunk in our environment to read  audit . 

Below was steps  for  pointing audit to syslog and settings from splunk agent side 


References :
How To Set the AUDIT_SYSLOG_LEVEL Parameter? (Doc ID 553225.1)
How To Enable Database Syslog Auditing On Solaris (Doc ID 1450842.1)

SYSLOG Auditing Fails To Write Entries To The File Destination Set In SYSLOG.CONF (Doc ID 1314077.1)

For Syslog :

A)  As  root  user

                1) take backup of /etc/syslog.conf

                2) Modify  /etc/syslog.conf   as under ( note to  use tab and not space)

                                local1.warning /var/log/oracle/oracleaudit.log

                3) the syslog services needs restarting after this file is changed

             svcadm restart svc:/system/system-log:default

B) add below database parameters and  restart database as oracle user

                1) table backup of database spfile

        2) Verify below 2 parameters are set 

alter system set audit_file_dest='/oradata/ora12c/product/admin/test/audit' scope=spfile;

alter system set audit_sys_operations=true scope=spfile;

       update database syslog parameters as  under

                               

                                alter system set audit_trail=os scope=spfile;

                                alter system set enable_ddl_logging=TRUE scope=spfile;

                                alter system set audit_syslog_level='local1.warning' scope=spfile;

                               

exec dbms_audit_mgmt.set_audit_trail_property( audit_trail_type=>dbms_audit_mgmt.audit_trail_os,audit_trail_property=>dbms_audit_mgmt.os_file_max_size,audit_trail_property_value=>50);

                               

exec dbms_audit_mgmt.set_audit_trail_property(audit_trail_type=>dbms_audit_mgmt.audit_trail_os,audit_trail_property=>dbms_audit_mgmt.os_file_max_age,audit_trail_property_value=>1);

                                                Shutdown immediate;

                                                Startup;

For Splunk :

A) as splunk user

    1) Ensure  attached Splunk_TA_oracle  directory  is present under /opt/splunkforwarder/etc/apps/  and permission is  owned by  splunk

    2) /opt/splunkforwarder/etc/apps/Splunk_TA_oracle/local/inputs.conf   file  must have below  format if using audit file monitoring

[monitor:///var/log/oracle/oracleaudit.log]

sourcetype = oracle:audit:text

disabled = false

index = moe

crcSalt = <SOURCE>

   3) Need to ensure "  useACK = true" line is  not present in file    /opt/splunkforwarder/etc/apps/org_all_forwarder_outputs/local/outputs.conf

[tcpout]

defaultGroup = primary_indexers

maxQueueSize = 7MB

#useACK = true

forceTimebasedAutoLB = true

forwardedindex.2.whitelist = (_audit|_introspection|_internal)

#IP of Heavy Forwarder

[tcpout:primary_indexers]

server = XX.XXX.XX.XX:XXXX

4) Restart  splunk services with splunk user and  check if audit files are listed 

/opt/splunkforwarder/bin/splunk restart 

/opt/splunkforwarder/bin/splunk list monitor 

Verification: 

Verifiy  entres are getting written in  /var/log/oracle/oracleaudit.log and get confirmation from splunk  team

Rollback steps  : 

1) restore backup of  /etc/syslog  taken   and restart  syslog services

2) restore backup of database spfile and  restart database

References : 

SCRIPT: Generate AUDIT and NOAUDIT Statements for Current Audit Settings (Doc ID 287436.1)

SRDC - How to Collect Standard Information for Database Auditing (Doc ID 1901113.1)