Database audit and failed login trace

1.  When database account keeps getting locked, below trigger is useful to find failed logins.

CREATE TABLE system.LOGIN_AUDIT
(
  LOGIN_TIME  DATE,
  MACHINE     VARCHAR2(64 BYTE),
  IP_ADDR     VARCHAR2(64 BYTE),
  OSUSER      VARCHAR2(30 BYTE),
  USERNAME    VARCHAR2(30 BYTE),
  PROGRAM     VARCHAR2(48 BYTE),
  MODULE      VARCHAR2(64 BYTE)
)
TABLESPACE users;

CREATE OR REPLACE TRIGGER system.LOGIN_AUDIT
AFTER SERVERERROR
ON DATABASE
DECLARE
  v_prog    login_audit.PROGRAM%TYPE;
  v_mod     login_audit.module%TYPE;
BEGIN
  -- Login failure
   IF (is_servererror(1017)) THEN
    SELECT PROGRAM, module INTO v_prog, v_mod  FROM v$session
     WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND ROWNUM<2;

    INSERT INTO LOGIN_AUDIT (
               username
                ,machine
                ,ip_addr
                ,osuser
                ,login_time
                ,PROGRAM,
                MODULE
        )
    VALUES (
          SYS_CONTEXT('USERENV','SESSION_USER')
         ,SYS_CONTEXT('USERENV', 'HOST')
         ,SYS_CONTEXT('USERENV', 'IP_ADDRESS')
         ,SYS_CONTEXT('USERENV', 'OS_USER')
         ,SYSDATE
         ,v_prog
         ,v_mod
    );
  END IF;
END;
/

2.  Metalink note 352389.1 also gives a way to find the source of failed login attempts

3.  Database auditing 
To turn on audit on database level may not practically acceptable to users due to potential impact on database performance and a database downtime.

Auditing can be enabled by setting the AUDIT_TRAIL init parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

. none or false - Auditing is disabled. This is the default.
. db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
. db,extended - 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 - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
. os - Auditing is enabled, with all audit records saved to OS files.

The AUDIT_SYS_OPERATIONS init parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail defined by AUDIT_FILE_DEST parameter. The audit on activities by a user with DBA privilege is not controled by AUDIT_SYS_OPERATIONS parameter.

The AUDIT_FILE_DEST init parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used (In addition, V$XML_AUDIT_TRAIL for xml format). (It seems to me that AUDIT_FILE_DEST holds audit files for activities of SYSDBA or SYSOPER. All other audit files go to adump directory).

To audit all operations by user JY, run below lines by SYS:

SQL> CONNECT sys/password AS SYSDBA

SQL> AUDIT ALL BY jy BY ACCESS;
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY jy BY ACCESS;
SQL> AUDIT EXECUTE PROCEDURE BY jy BY ACCESS;

These options audit all DDL and DML, along with some system events.

. DDL (CREATE, ALTER & DROP of objects)
. DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
. SYSTEM EVENTS (LOGON, LOGOFF etc.)

Without specifying the username JY, the audit trial will be on all users. You can see all enabled options from view DBA_STMT_AUDIT_OPTS:

SELECT user_name, audit_option, success, failure
FROM sys.dba_stmt_audit_opts;

You can get the list of views that hold the audit trail by below query:

SELECT view_name FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%' ORDER BY view_name;

The three main views are:

. DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
. DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
. DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.

The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size. Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:

AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;

Fine grained auditing (FGA) extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. For more details, check out DBMS_FGA package.

Apply patch by adpatch or opatch

•  adpatch to apply EBS patches

- If you don’t see the “AutoPatch is complete” message at the end of the Autopatch log file, Autopatch did not complete successfully.

- The autopatch log is in below directory with default name adpatch.log:
$APPL_TOP/admin/<SID>/log

File adpatch.lgi is the file that has information (about patch applied), for example, about files
that were not applied because of some kind of problem that occurs or because patches had been applied already. See below UPDATES 2.

Ignore warning messages. See Doc ID 401424.1  (for R12.1.3)

- Apply the patch on the CM node first. The patching process on other nodes will skip some steps that already ran in CM node.

- To apply GDF patches, maintenance mode is not necessary.
$ cd XXXXXXX
$ adpatch options=hotpatch

- For patch analysis, run
$ cd XXXXXXX
$ adpatch apply=n options=hotpatch

- Merge multiple patches: syntax
$ admrgpch -s /path/to/sourceFolder -d /patch/to/mergedPatch -manifest manifest_list.txt
After that, folder mergedPatch can be zipped up and copied to other servers. File manifest_list.txt shall list all .zip files with full patch.

- If patch failed with below similar error, you can answer No or answer Yes to finish the process. Then come back to fix the individual error.

The following Oracle Forms objects did not generate successfully:
ar      forms/US        ARXCWMAI.fmx
An error occurred while generating Oracle Forms files.
Continue as if it were successful [No] : No 
Freeing includes hash table
Freeing fixes hash table
 Freeing basedons hash table
Freeing entities hash table

If you answer NO, you have to re-run adpatch after you have fixed (i.e. compiled successfully) what failed. Re-run adpatch :
$ adpatch
... ...
Your previous AutoPatch session did not run to completion.
Do you wish to continue with your previous AutoPatch session [Yes] ? Yes
... ...

It will NOT ask you any passwords and will re-try what failed in previous session:

Assigned: file ARXCWMAI.fmx on worker  1 for product ar  username AR.
Completed: file ARXCWMAI.fmx on worker  1 for product ar  username AR.
... ...
Running AutoConfig ...
Running AutoConfig on : All products ...
... ...
Saving Patch History information to Database...
 Updating the current-view snapshot...
 ... ... ...

- To check if an EBS patch is installed or not, run a single SELECT statement:

SQL> select * from apps.ad_bugs where bug_number = 1313962;

- BTW, as sysdba use sql to check if a database patch is installed or not
SQL> set serveroutput on;
SQL> exec dbms_qopatch.get_sqlpatch_status;
or
SQL> select xmltransform(dbms_qopatch.is_patch_installed('31219939'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;
SQL> select * from sys.registry$history;

UPDATES 1:
Below query show status on each node if patch is not included in a merged patch. It is tested in R12.1.

SQL> SELECT aap.patch_name, aat.name, apr.end_date
FROM ad_applied_patches aap,
ad_patch_drivers apd,
ad_patch_runs apr,
ad_appl_tops aat
WHERE aap.applied_patch_id = apd.applied_patch_id
and apd.patch_driver_id = apr.patch_driver_id
and aat.appl_top_id = apr.appl_top_id
and aap.patch_name = '9239090';

UPDATES 2:
xxxxx.lgi file reports files that were not applied. For example, adpatch only compares the package versions on file system and does not check the version inside the database. If for some reason a newer one was copied to the file system,  the patch file will not get applied.

$  grep apgdfalb $APPL_TOP/admin/${TWO_TASK}/log/16981628.lgi
Will not apply apgdfalb.pls: Patch file is older.
  Patch  : /path/to/16981628/ap/patch/115/sql/apgdfalb.pls, v120.1.12010000.68
  On-Site: $AP_TOP/patch/115/sql/apgdfalb.pls, v120.1.12010000.72
Checkfile            sql          ap      apgdfalb.pls

But, the package version in the database is much lower. So I had to manually run file apgdfalb.pls in Sql*Plus to get the newer one.

SQL> select text from dba_source where name='AP_ACCTG_DATA_FIX_PKG' and line=2;
/* $Header: apgdfalb.pls 120.1.12010000.43 2011/08/24 05:55:58 kpasikan ship $ */

SQL> @/path/to/16981628/ap/patch/115/sql/apgdfalb.pls
Package created.

• opatch to apply patches to ORACLE_HOME

- Go to the directory first
$ cd 7120514
- Apply the patch
$ opatch apply
(or,  $ opatch apply -invPtrLoc $ORACLE_HOME/oraInst.loc )

Notes:
(1) Use below line to get opatch version:
$ ./opatch -version

(2) While applying the patch, if OPatch detects a different platform ID in the applying instance, then do the below work-around to make Opatch work fine. For example, if the patch is available for Linux86 but the instance OS at Linux AMD x86-64, set an env variable:

$ export OPATCH_PLATFORM_ID=64

(3) Steps to Rollback the patch
1. Stop all services
2. Use the following command:
$ cd 7120514
$ opatch rollback -id 7120514

UPDATES:
1. If a patch is for a lower version of product or is using a wrong ORACLE_HOME, opatch may not apply it.  For example, when I wrongly downloaded zip file of patch 12965674 for 10.1.3.4 and then applied it to a 10.1.3.5 ORACLE_HOME, it just gave message:

SKIPPING_COMPONENT=oracle.j2ee,10.1.3.4.0
None of the patch actions is applicable to the Oracle Home.

OPatch will not apply this patch.
OPatch succeeded.

2. When the folder of the unzipped files had permission issue (owned by a different user), opatch stopped in the middle with below message. I answered N to apply it and then rolled it back successfully.

$ opatch apply
. . .  . . .  . . .
Replying 'Y' will terminate the patch installation immediately. It WILL NOT restore any updates that have been performed to this point. It WILL NOT update the inventory.
Replying 'N' will update the inventory showing the patch has been applied.
NOTE: After replying either 'Y' or 'N' it is critical to review:
      My Oracle Support Note 312767.1 How to rollback a failed Interim patch installation.
Do you want to STOP?
Please respond Y|N >
N
Running make for target ias_install.

Inventory is good and does not have any dangling patches.

Updating inventory...

Verifying patch...
  Verifying that patch ID is in Oracle Home inventory.
  Verifying archive files.

Comparing "/path/to/11780669/files/lib/librw.a/rwadr.o" and "$ORACLE_HOME/.patch_storage/verify/lib/librw.a/rwadr.o"
OPATCH_JAVA_ERROR: Unable to verify if patch has been applied.

Exception in thread "main" java.io.FileNotFoundException: /path/to/11780669/files/lib/librw.a/rwadr.o (Permission denied)
        at java.io.FileInputStream.open(Native Method)
        at java.io.FileInputStream.<init>(Unknown Source)
        at java.io.FileInputStream.<init>(Unknown Source)
        at opatch.VerifyPatch.verify(VerifyPatch.java:663)
        at opatch.VerifyPatch.main(VerifyPatch.java:933)Verification of the patch failed.

ERROR: OPatch failed as verification of the patch failed.

$ opatch rollback -id 11780669
. . .  . . .  . . . 
Is this system ready for updating?
Please respond Y|N >
Y
Removing patch 11780669...

Restoring archive files...
Running make for target  ias_install.
Updating inventory...
Backing up comps.xml ...
Inventory is good and does not have any dangling patches.
OPatch succeeded.

Verify if a server patch is installed or not

When you install a server patch on your database server or Discoverer server, you need to check if the patch or pre-requisite patch has been installed or not. Here is how you can do that:

$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc | grep 6472361

If this command returns anything, the patch 6472361 may be installed on your server.

Below line also gives the list of installed patches. But, it does not give the patch number for the Top-level upgrade (such as 5337014 for upgrading 10.2.0.1.0 to 10.2.0.3.0. Without it, it is not easy to identify the binary file in download).

$ ./opatch lsinventory -oh $ORACLE_HOME

Installed Top-level Products (2):

Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0
There are 2 products installed in this Oracle Home.

Interim patches (3) :

Patch 6596361 : applied on Fri Nov 23 10:24:05 MST 2007
Created on 20 Nov 2007, 02:42:09 hrs PST8PDT,M3.2.0,M11.1.0
Bugs fixed:
6596361

Patch 5556081 : applied on Mon Sep 17 14:21:59 MST 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
5556081

Patch 5557962 : applied on Mon Sep 17 14:21:39 MST 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
4269423, 5557962, 5528974

Rac system comprising of multiple nodes
Local node = dssdb1p
Remote node = dssdb2p