Category: Oracle

How to Check PDB is compatible with a new CDB

To test if a PDB is compatible with a new CDB before plugging it in, use the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function in the target CDB. This function checks for incompatibilities like patch levels, character sets, database options, and the COMPATIBLE parameter setting.

Generate a PDB Description File (XML): Before you can run the check, you must have an XML metadata file that describes the PDB. This is usually created during the unplugging process from the source CDB or by using DBMS_PDB.DESCRIBE.
Access to Target CDB: You need SYSDBA privileges on the target CDB.

— On Source CDB

BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file => '/tmp/pdb_description.xml',
    pdb_name       => 'YOUR_PDB_NAME');
END;
/
 $ grep -v "<parameter>" /tmp/pdb_description.xml > /tmp/pdb_description.xml_no_param.xml

— On Target CDB

SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) := 
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
      pdb_descr_file => '/tmp/pdb_description.xml',
      pdb_name       => 'NEW_PDB_NAME')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
    END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible? ==> ' || compatible);
END;
/

Check for Violations

COLUMN MESSAGE FORMAT A60
COLUMN STATUS FORMAT A10
SELECT NAME, TYPE, MESSAGE, STATUS FROM PDB_PLUG_IN_VIOLATIONS 
ORDER BY TIME;

DBCA: Database configuration assistant with error “java lang nullpointerexception”

Solution

$ export CV_ASSUME_DISTID=OEL7.6

Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT

Symptoms

When attempting to create a new purge job,
the following error occurs.

ERROR
———————–
ORA-46252: Cannot complete the operation, overlapping job already exists
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 61
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 2595
ORA-06512: at line 2


STEPS
———————–
The issue can be reproduced at will with the following steps:
1. sqlplus / as sysdba

2. BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  AUDIT_TRAIL_PURGE_INTERVAL => 24,
  AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
  USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Cause

DBA_SCHEDULER_JOB does not have the information about the audit_mgmt job.
 
This scenario happens when DBMS_SCHEDULER and DBMS_AUDIT_MGMT are not Synchronous .
 

Solution

SQL> DELETE FROM SYS.DAM_CLEANUP_JOBS$ where JOB_NAME = ‘STANDARD_OS_AUDIT_TRAIL_PURGE’;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

PL/SQL procedure successfully completed.
 

References

NOTE:1252235.1 – Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT

Standby Database:  *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID/

Symptoms

There are many *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID.

For example:

$ ls -ltr
total 666644
-rw-------. 1 oracle oinstall 10240512 Sep 19 15:09 ora_audit_1212246579_3985105844.bin
-rw-------. 1 oracle oinstall 10241536 Sep 20 06:52 ora_audit_1212303144_3688912851.bin
-rw-------. 1 oracle oinstall    20480 Sep 20 12:02 ora_audit_1212321763_3431476539.bin
-rw-------. 1 oracle oinstall    12288 Sep 20 12:35 ora_audit_1212323740_3065638769.bin
-rw-------. 1 oracle oinstall 10241024 Sep 20 22:35 ora_audit_1212359756_874733345.bin
-rw-------. 1 oracle oinstall  1126400 Sep 21 03:40 ora_audit_1212378045_893687335.bin
-rw-------. 1 oracle oinstall 10241024 Sep 21 06:30 ora_audit_1212388214_2015242572.bin
-rw-------. 1 oracle oinstall 10240512 Sep 21 14:19 ora_audit_1212416379_363470844.bin
-rw-------. 1 oracle oinstall 10241536 Sep 22 06:02 ora_audit_1212472944_1767466054.bin
-rw-------. 1 oracle oinstall 10241536 Sep 22 21:45 ora_audit_1212529539_1413139791.bin
-rw-------. 1 oracle oinstall 10241024 Sep 23 13:28 ora_audit_1212586104_1758745070.bin
...
..
.

CAUSE

The *.bin files are Unified Auditing OS “spillover” files.  These are generated when the audit trail records cannot be written to the database.  For example, when the database is not writable (such as during database mounts), if the database is closed, or if it is read-only, then Oracle Database writes the audit records to external files in the $ORACLE_BASE/audit/$ORACLE_SID directory.
 
This is expected behavior on 12c onwards when the DB is configured for Unified Auditing.

SOLUTION

If Unified Auditing is not necessary, disabling the default Unified Auditing policies will cause the *.bin files to stop being generated.  
 
Refer to:  The UNIFIED_AUDIT_TRAIL is Getting Populated even if Unified Auditing was not explicitly enabled in 12c (Doc ID 1624051.1)

If these policies are re-enabled, one will need to periodically maintain $ORACLE_BASE/audit/$ORACLE_SID.

If one does not require the audit data in the *.bin files, these files can be safely deleted.