Category: Oracle

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS): ORA-46264 ORA-06512

Subscribe to continue reading

Become a paid subscriber to get access to the rest of this post and other exclusive content.

How to Reset User Expired Password to the Original Password

We want to keep using the old password due to application restraints.

SQL>  alter user TESTUSER identified by "Testuser#Password123";
 alter user TESTUSER identified by "Testuser#Password123"
*
ERROR at line 1:
ORA-28007: the password cannot be reused

Check profile DEFAULT resource limits:

DEFAULT    PASSWORD_REUSE_MAX               UNLIMITED
DEFAULT    PASSWORD_REUSE_TIME              UNLIMITED
SQL>  alter user TESTUSER profile default;
SQL> select  NAME, SPARE4 FROM SYS.USER$ WHERE NAME ='TESTUSER';

NAME            SPARE4
--------------- ------------------------------------------------
TESTUSER S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04
SQL>  alter user TESTUSER identified by values 'S:E75D474EB09C5E5222BAC532E6F7595BF3A4D7EE6927E615860872DE1892;T:C53F84BF7BF4D1D865D31E7D864B6FC732CF6CB0FE23DD0E780221D514BF05B663FF20B504DBDF2A17A4F82B0376E09C245E7F915BDFAB1FD6E841B21080774321AE15C9FF5A87A9C2E660E82789AC04';

User altered.

SQL> connect testuser
Enter password: Testuser#Password123 
Connected.
SQL>

Oracle RMAN to Restore and Recover Database to a Previous Incarnation

Subscribe to continue reading

Become a paid subscriber to get access to the rest of this post and other exclusive content.

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;