Monday, October 31, 2022

How to clear resolved errors from PDB_PLUG_IN_VIOLATIONS

The query below shows 4 resolved messages in the view PDB_PLUG_IN_VIOLATIONS:
SELECT NAME,CAUSE,TYPE,MESSAGE,STATUS
FROM PDB_PLUG_IN_VIOLATIONS
WHERE NAME='PDB$SEED'
NAME CAUSE TYPE MESSAGE STATUS
PDB$SEED SQL Patch ERROR Interim patch 31424070/24854845 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB RESOLVED
PDB$SEED SQL Patch ERROR Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): Not installed in the CDB but installed in the PDB RESOLVED
PDB$SEED SQL Patch ERROR '19.16.0.0.0 Release_Update 2207030222' is installed in the CDB but no release updates are installed in the PDB RESOLVED
PDB$SEED SQL patch error ERROR Interim patch 34086870/24803071 (OJVM RELEASE UPDATE: 19.16.0.0.220719 (34086870)): ROLLBACK with status INITIALIZE in the CDB. RESOLVED

The resolved messages can be easily removed with the procedure DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS:
SYS@CDB$ROOT SQL> exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS( pdb_name => 'PDB$SEED');

Useful resources:

  • Mike Dietrich on the same topic
  • The Oracle 19c Documentation for the PDB_PLUG_IN_VIOLATIONS view

    Note that there is an Unpublished Bug 16192980 : NO SIMPLE WAY TO CLEAN ROWS FROM PDB_PLUG_IN_VIOLATIONS AFTER DBMS_PDB CALL that prevents you from discarding no-impact warnings.
    According to Oracle there should be a fix for this in version 19.10, but I still strugle to remove messages like
      Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.
      
    which can be ignored. Read more about Database option mismatch errors in Doc ID 2020172.1
  • Friday, October 21, 2022

    Rename schema in PostgreSQL: syntax

    \connect db01
    alter schema schema1 rename to schema1_old;
    
    Documented here

    Rename database in PostgreSQL: syntax

    alter database db01 rename to db01;
    
    Documented here

    See this post for how to create a new database in PostgreSQL

    Wednesday, October 19, 2022

    How to prevent a user from login into a postgres database

    alter role scott with nologin;
    ALTER ROLE
    
    Remember that in postgres, "users" and "roles" are used interchangably, so this would also work:
    alter user scott with nologin;
    ALTER ROLE
    
    In either case, the postgres server will echo "ALTER ROLE" back to the administrator.

    To see the result of such an operation:
    echo "\du" | psql
    
    Example output:
                                                   List of roles
                Role name            |                         Attributes                         |      Member of
    ---------------------------------+------------------------------------------------------------+---------------------
     scott                           | Cannot login                                              +| {business_users}
     

    You can also query the postgres data dictionary for the answer, like this:
    postgres=# select rolcanlogin from pg_roles where rolname='jim';
     rolcanlogin
    -------------
     t
    (1 row)
    
    postgres=# select rolcanlogin from pg_roles where rolname='scott';
     rolcanlogin
    -------------
     f
    (1 row)
    
    where t and f indicates true if the user can login and false if the user cannot, respectively.

    Tuesday, October 11, 2022

    How to solve "Service name or network name of ... in the PDB is invalid or conflicts with an existing service name or network name in the CDB."

    From the alert log during startup of the container database called "cdb":
    pdb1(3):***************************************************************
    pdb1(3):WARNING: Pluggable Database saes with pdb id - 3 is
    pdb1(3):         altered with errors or warnings. Please look into
    pdb1(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
    pdb1(3):***************************************************************
    
    When I check the PDB_PLUG_IN_VIOLATIONS I see this:
    select name,cause,type,message,status,action 
    from pdb_plug_in_violations;
    

    NAME CAUSE TYPE MESSAGE STATUS ACTION
    sales Service Name Conflict WARNING Service name or network name of service salesXDB in the PDB is invalid or conflicts with an existing service name or network name in the CDB. PENDING Drop the service and recreate it with an appropriate name.

    Check the CDB_SERVICES view:
    SELECT name,network_name,creation_date,pdb,enabled
    FROM   cdb_services
    where con_id=3
    and name='salesXDB'
    ORDER BY 1;
    

    NAME NETWORK_NAME CREATION_DATE PDB ENABLED
    salesXDB salesXDB 03/03/2022 11:28:56 sales NO

    There is indeed a service called salesXDB, which is a reminisce from an earlier point in time where "sales" was a non-CDB database.
    It has now been replaced with the container database XDB service, in my case called "cdbXDB".

    How to address the warnings

    Log onto the sales pluggable database:
    sqlplus / as sysdba
    
    alter session set container=sales;
    
    Session altered.
    
    Remove the service from the PDB:
    exec dbms_service.delete_service('salesXDB');
    
    PL/SQL procedure successfully completed.
    
    If you check the CDB_SERVICES again, it will now be gone.

    Restart the pluggable database:
    sqlplus / as sysdba
    
    alter session set container=sales;
    
    Session altered.
    
    shutdown immediate
    startup
    

    If you query the PDB_PLUG_IN_VIOLATIONS again, you will see that the value for status in the error is now set to RESOLVED:
    time NAME CAUSE TYPE MESSAGE STATUS ACTION
    11.10.2022 12:49 sales Service Name Conflict WARNING Service name or network name of service salesXDB in the PDB is invalid or conflicts with an existing service name or network name in the CDB. RESOLVED Drop the service and recreate it with an appropriate name.


    Source: PDB Name Conflicts With Existing Service Name In The CDB Or The PDB (Doc ID 2247291.1) from Oracle Support

    How do I make sure that services are automatically brought online in an Oracle 19c Pluggable Database?

    In an Oracle 19c pluggable database, the service_names parameter has no bearing on whether or not the services are brought up automatically at instance or server restart.

    If the service was started in the PDB and you then saved as the pluggable database's state, it willl be brought online together with the pluggable database automatically.

    First, create and start your desired service from the PDBS:
    SQL> alter session set container=sales;
    
    Session altered.
    
    SQL> exec dbms_service.create_service(service_name=>'online_users', network_name=>'online_users');
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_service.start_service('online_users');
    
    PL/SQL procedure successfully completed.
    
    Switch to your root container:
    SYS@cdb>SQL>alter session set container=CDB$ROOT;
    
    Session altered.
    
    Then, save the state of your PDB:
    SYS@cdb>SQL>alter pluggable database sales save state;
    
    Pluggable database altered.
    
    Or, alternatively, save all current states of your PDBs:
    SYS@cdb>SQL> ALTER PLUGGABLE DATABASE ALL SAVE STATE;
    
    Pluggable database altered.
    

    The service_names parameter is actually deprecated in Oracle 19c. The documentation states
    To manage your services, Oracle recommends that you instead use the SRVCTL command-line utility, the GDSCTL command-line utility, or the DBMS_SERVICE PL/SQL package.

    How to check the code level currently in use in an EBS database

    To find the E-Business Suite and AD/TXK patch levels, log into the EBS container and use the following two queries:
    col abbreviation format a20
    col name format a40
    col codelevel format a20
    select  ABBREVIATION, NAME, codelevel FROM apps.AD_TRACKABLE_ENTITIES where abbreviation in ('txk','ad')
    
    ABBREVIATION         NAME                                     CODELEVEL
    -------------------- ---------------------------------------- --------------------
    ad                   Applications DBA                         C.11
    txk                  Oracle Applications Technology Stack     C.11
    
    select release_name from apps.FND_PRODUCT_GROUPS;
    
    RELEASE_NAME
    ------------
    12.2.9