Auditing ddl on a database + DDL trigger

Conn / as sysdba

15:53:53 SQL > CREATE TABLE perfstat.STATS$DDL_LOG
15:55:30 2 (
15:55:30 3 USER_NAME VARCHAR2(25 BYTE),
15:55:30 4 DDL_DATE DATE,
15:55:30 5 DDL_TYPE VARCHAR2(25 BYTE),
15:55:30 6 OBJECT_TYPE VARCHAR2(25 BYTE),
15:55:30 7 OWNER VARCHAR2(25 BYTE),
15:55:30 8 OBJECT_NAME VARCHAR2(30 BYTE),
15:55:30 9 TERMINAL_NAME VARCHAR2(40 BYTE),
15:55:30 10 IP_ADDRESS VARCHAR2(30 BYTE)
15:55:30 11 );

Table created.

15:55:32 SQL > grant all on perfstat.STATS$DDL_LOG to apps;

Grant succeeded.

15:55:38 SQL > conn apps
Enter password:
Connected.
15:55:45 SQL > create synonym DDL_LOG for perfstat.STATS$DDL_LOG;

Synonym created.

15:55:53 SQL > conn / as sysdba
Connected.
15:55:57 SQL > CREATE OR REPLACE TRIGGER DDLTrigger
15:56:21 2 AFTER DDL ON DATABASE
15:56:21 3 DECLARE
15:56:21 4 v_terminal_name varchar2(40);
15:56:21 5 v_ip_address varchar2(40);
15:56:21 6 BEGIN
15:56:21 7 SELECT SYS_CONTEXT ('USERENV', 'TERMINAL')
15:56:21 8 INTO v_terminal_name
15:56:21 9 FROM DUAL;
15:56:21 10 SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS')
15:56:21 11 INTO v_ip_address
15:56:21 12 FROM DUAL;
15:56:21 13 insert into
15:56:21 14 perfstat.stats$ddl_log
15:56:21 15 (
15:56:21 16 user_name,
15:56:21 17 ddl_date,
15:56:21 18 ddl_type,
15:56:21 19 object_type,
15:56:21 20 owner,
15:56:21 21 object_name,
15:56:21 22 terminal_name ,
15:56:21 23 ip_address
15:56:21 24 )
15:56:21 25 VALUES
15:56:21 26 (
15:56:21 27 ora_login_user,
15:56:21 28 sysdate,
15:56:21 29 ora_sysevent,
15:56:21 30 ora_dict_obj_type,
15:56:21 31 ora_dict_obj_owner,
15:56:21 32 ora_dict_obj_name,
15:56:21 33 v_terminal_name,
15:56:21 34 v_ip_address
15:56:21 35 );
15:56:21 36
15:56:21 37 END;
15:56:22 38 /

Trigger created.


16:01:36 SQL > conn apps
Enter password:
Connected.

16:02:36 SQL > select * from DDL_LOG;

USER_NAME DDL_DATE DDL_TYPE OBJECT_TYPE OWNER OBJECT_NAME TERMINAL_NAME IP_ADDRESS
---------- --------- ---------- --------------- -------- -------------------- -------------------- ----------------
APPS 16-JUN-08 CREATE TABLE APPS MOHIT LGDBA 10.102.120.118
APPS 16-JUN-08 DROP TABLE APPS MOHIT LGDBA 10.102.120.118

=======================================================================================================================

ORA-27211: Failed to load Media Management Library

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of allocate command on t1 channel at 02/03/2008 19:31:04

ORA-19554: error allocating device, device type: SBT_TAPE, device name:

ORA-27211: Failed to load Media Management Library

Additional information: 2

Solution :

Step 1 :

Check the trace file for more information in udump .

-------------------------------------------------------------------------------------------

/ms10/oracle/9.2.0/admin/lgeil/udump/lgeil_ora_4026994.trc

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

ORACLE_HOME = /ms10/oracle/9.2.0

System name: AIX

Node name: ilomsdb01

Release: 3

Version: 5

Machine: 00C0657C4C00

Instance name: lgeil

Redo thread mounted by this instance: 1

Oracle process number: 20

Unix process pid: 4026994, image: oracle@ilomsdb01 (TNS V1-V3)

*** SESSION ID:(19.1) 2008-02-03 19:31:04.091

Failed to load SBT library libobk.a(shr.o)

--------------------------------------------------------------------------------------------

libobk.a -> /usr/lib/libobk64.a

-------------------------------------------------------------------------------------------------------------------------

Solution :

Step 1 :

Now go to the locaiton $ORACLE_HOME/lib and check for the file libobk.a . If you won't find this file then contact your OS support team or Backup Team

Step 2 : ( To be peroformed by the unix team )

Now , Go to the location /usr/openv/netbackup/bin and chk for the file libobk.a64 . If this file exist , then run the command given below ( from root as user )

1. ln -s /usr/openv/netbackup/bin/libobk.a64 /usr/lib/libobk64.a

2. ln -s /usr/lib/libobk64.a <$ORACLE_HOME>/lib/libobk.a

ex :

ln -s /usr/lib/libobk64.a /ms10/oracle/9.2.0/lib/libobk.a

Note : In 99% case this will work . If it work for you , Then thats fine .Even after this if you get the error mentioned below .

----------------------------------------------------------------------------------------------------------------

RMAN-03009: failure of backup command on t1 channel at 09/11/2008 19:51:26

ORA-19506: failed to create sequential file, name="al_ORANMES_02jqbjl6_1_1_665177766", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

VxBSACreateObject: Failed with error:

Server Status: Communication with the server has not been iniatated or the server status has not been retrieved from the server.

channel t1 disabled, job failed on it will be run on another channel

released channel: t1

released channel: t2

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t2 channel at 09/11/2008 19:51:26

ORA-19506: failed to create sequential file, name="al_ORANMES_03jqbjl7_1_1_665177767", parms=""

ORA-27028: skgfqcre: sbtbackup returned error

ORA-19511: Error received from media manager layer, error text:

VxBSACreateObject: Failed with error:

Server Status: Communication with the server has not been iniatated or the server status has not been retrieved from the server.

---------------------------------------------------------------------------------------------------------------

Solution :

Then ask the UNIX ADMIN

--> To take the reboot of VERITAS Services .

--> Also to kill all the process related with VERITAS on the client side

ORA - 28040 : No matching authentication protocol

Cause

The parameter DB_ALLOWED_LOGON_VERSION has not been set in the remote database.
DB_ALLOWED_LOGON_VERSION is a new parameter introduced in 10g that allows the DBA to explicitly determine which authentication protocols are allowed by the client or database.
The value specified is the earliest client version level supported by this database.
When clients earlier than the specified version attempt to connect, authentication will fail.

Solution

In the init.ora of the remote (Downstream capture) database, set the following : db_allowed_logon_version=10
Note: Bug 2981553, which is implemented in 10.1.0.2, removes the parameter db_allowed_logon_version. This is replaced by the sqlnet.ora parameter called sqlnet_allowed_logon_version.

SQLNET_ALLOWED_LOGON_VERSIONS

Purpose
Use the parameter SQLNET_ALLOWED_LOGON_VERSIONS to specify whichauthentication protocols are allowed
by the client or database. If the client and database server do not have at least one matching version, then
authentication fails with an error.

Allowed Values

■ 10 for Oracle Database 10g authentication protocols

Note: Additional protocols might support this parameter on certain operating systems. Refer to operating-system specific
documentation for information about additional protocols that support this parameter.

See Also: Oracle Database Net Services Administrator's Guide for information about configuring this parameter

See Also: Oracle Database Advanced Security Administrator's Guide

Profile Parameters
Profile Parameters (sqlnet.ora) 5-7

■ 9 for Oracle9i authentication protocols
■ 8 for Oracle8 authentication protocols
■ 7.3 for Oracle 7.3 authentication protocols

Any value other than 10 could expose vulnerabilities that may have existed in previous version of the authentication protocols. For complete compatibility, set the list of allowable versions for logon to include all versions of database in the system.

Default

10, 9, 8

Example

If both Oracle 8.1.7 and Oracle9i databases are present, then set the parameter as follows:

SQLNET_ALLOWED_LOGON_VERSIONS=(10,9,8)

ORA-00600: internal error code, arguments: [unable to load XDB library]

Error :

--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
Wed Aug 6 17:45:55 2008
Errors in file /u06/oradata/admin/ilpnp/bdump/ilpnp_s000_73754.trc:
ORA-00600: internal error code, arguments: [ksudel1], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []



[plantopiadb:orapnp] /u06/oradata/admin/ilpnp/bdump> more ilpnp_s000_62274.trc
/u06/oradata/admin/ilpnp/bdump/ilpnp_s000_62274.trc
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /u01/app/orapnp
System name: AIX
Node name: plantopiadb
Release: 3
Version: 5
Machine: 00C96E2E4C00
Instance name: ilpnp
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 62274, image: oracle@plantopiadb (S000)

*** 2008-08-06 17:45:54.338
*** SESSION ID:(21.29949) 2008-08-06 17:45:54.337
Dynamic link error: 0509-022 Cannot load module /u01/app/orapnp/lib32/libxdb.so.
0509-103 The module has an invalid magic number.
*** 2008-08-06 17:45:54.338
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []

--------------------------------------------------------------------------------

Cause

These errors may be the result if the database was started and the environment variable LD_LIBRARY_PATH was not set, or was set to an incorrect version of $ORACLE_HOME/lib (e.g. to a different $ORACLE_HOME). If during the user level or full database export we are unable to locate the correct version of the libxdb.so / libxdb.sl library, the internal errors may occur.


Solution :

1. Stop the database and stop the listener.

2. Set LD_LIBRARY_PATH so the first directory referenced is $ORACLE_HOME/lib
Example (replace $ORACLE_HOME with the full path of the Oracle home directory):

> export LD_LIBRARY_PATH=$ORACLE_HOME/lib
> export LIBPATH=$ORACLE_HOME/lib

For Oracle9i and Oracle10g on AIX: set environment variable LIBPATH and afterwards, run /usr/sbin/slibclean as root
For Oracle9i and Oracle10g on HP-UX, Linux, Solaris, and Tru64: set environment variable LD_LIBRARY_PATH

3. Re-start the database and the listener.