topic Re: What is the pluggable database (PDB) called in an Oracle enterprise geodatabase? in ArcGIS Enterprise Questions

What is the pluggable database (PDB) called in an Oracle enterprise geodatabase?

Bud — Sat, 22 Apr 2023 16:10:01 GMT

I’m a non-admin trying to learn about Oracle pluggable databases (PDB) for professional development purposes.

What is the OOTB pluggable database (PDB) called that is used by an Oracle 18c 10.7.1 enterprise geodatabase?

For example, in Oracle 18c Express Edition, there is a default PDB called XEPDB1. What is the equivalent PDB in an Oracle EGDB? Or does the PDB name get defined by the DBA who set up the Oracle database?

Can I determine the PDB name by looking at:

ArcGIS Pro database connection properties?
SQL Developer connection properties (JDBC)?
A database query from a user/schema/owner connection (like PARKS, not SYS)?

The non-CDB architecture [no PDBs] (the way the databases work before 12c) is available in recent versions, but it was deprecated in Oracle 12c and desupported in Oracle 20c.
https://www.databasestar.com/oracle-pdb/

@MarceloMarques might find this interesting.

Re: What is the pluggable database (PDB) called in an Oracle enterprise geodatabase?

ChrisUnderwood — Fri, 21 Apr 2023 08:35:40 GMT

You would normally choose the Pluggable database name yourself, eg when using Oracle's Database Configuration Assistant. But if you left the form like this, I believe the database would be called either notcontain or orclpdb.

You can tell if a database is Container or Pluggable with a SQL Query like this.

SQL> select cdb from v$database;

Re: What is the pluggable database (PDB) called in an Oracle enterprise geodatabase?

MarceloMarques — Fri, 21 Apr 2023 17:59:39 GMT

@Bud - you can find a complete database template script to setup an Oracle CDB and PDBs in the link below, by looking my template you shall find answers to your questions.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

Oracle-Base also has a nice tutorial.

https://oracle-base.com/articles/12c/multitenant-overview-container-database-cdb-12cr1

Answers:

PDB name is controlled by the Oracle DBA when the PDB is created.

Example:

--PMPDB
CREATE PLUGGABLE DATABASE pmpdb ADMIN USER pmpdbadm IDENTIFIED BY "pmpdb$admin"
ROLES = (dba)
DEFAULT TABLESPACE USERS LOGGING
DATAFILE 'C:/oracle/oradata/mcs/pmpdb/users.dbf'
SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
FILE_NAME_CONVERT=('C:/oracle/oradata/mcs/pdbseed/','C:/oracle/oradata/mcs/pmpdb/')
PATH_PREFIX = NONE
STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED);

Can I determine the PDB name by looking at:

1. ArcGIS Pro database connection properties?

[Marcelo]: yes, the easy connect syntax "<server_name>:<port_name>@<service_name>

service_name = pdb_name

if using tnsnames.ora then look the details of your tnsnames.ora file to learn if it points to the pdb.

2. SQL Developer connection properties (JDBC)?

[Marcelo]: same as above.

3. A database query from a user/schema/owner connection (like PARKS, not SYS)?

alter session set container = cdb$root;

SELECT * FROM dba_pdbs ORDER BY pdb_name;
SELECT * FROM v$pdbs ORDER BY name;
SELECT * FROM dba_registry;
SELECT * FROM dba_registry_sqlpatch order by action_time desc;
--PDB_PLUG_IN_VIOLATIONS
select count(*) from PDB_PLUG_IN_VIOLATIONS;
select name, cause, type, message, status, action
from PDB_PLUG_IN_VIOLATIONS
--where /*type like 'ERROR%' and*/ status like 'PENDING'
order by 1,2;

See this screenshot with SQL Plus commands that you can use.