Oracle PDB

CDB

crea CDB con DBCA in modalità silente

#!/bin/bash
SID=$1
PDB=$2
CODESET=$3
[ $# -ne 3 ] && exit 1
dbca -silent -createDatabase \
 -databaseConfigType SINGLE \
 -templateName New_Database.dbt \
 -gdbname ${SID} -sid ${SID} -responseFile NO_VALUE \
 -characterSet ${CODESET} \
 -nationalCharacterSet AL16UTF16 \
 -sysPassword Welcome1 \
 -systemPassword Welcome1 \
 -createAsContainerDatabase true \
 -useLocalUndoForPDBs true \
 -pdbName $2 \
 -pdbAdminPassword Welcome1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -enableArchive false \
 -storageType FS \
 -useOMF true \
 -datafileDestination "/oradata/" \
 -recoveryAreaDestination "/archive/" \
 -recoveryAreaSize 102400 \
 -redoLogFileSize 200 \
 -emConfiguration NONE \
 -ignorePreReqs
# -templateName General_Purpose.dbc \
# -numberOfPDBs 1 \

PDB

creazione PDB

create pluggable database BUBBA admin user sys_bubba identified by "Welcome1";
alter pluggable database BUBBA open;
alter pluggable database BUBBA save state;

accesso

Da sql:

show pdbs;
show con_name;
alter session set container=PDB1;

per tornare nel container root:

alter session set container=CDB$ROOT;

Variabili di ambiente

ORACLE_SID=CDB1
ORACLE_PDB_SID=PDB1
export ORACLE_SID ORACLE_PDB_SID
sqlplus '/ as sysdba'

Spostamento PDB

Procedur per spostare con minimo fermo un PDB da un CDB ad un altro CDB su altro server, via DB link.

-- se indietro di versione bisogna prima aver lanciato preupgrade.jar ed eseguito preupgrade_fixups.sql
-- sul CDB sorgente va create l'utente REMOTE_CLONE_USER con privilegi SYSOPER, CREATE SESSION e CREATE PLUGGABLE DATABASE
-- create user C##REMOTE_CLONE_USER identified by "password" container=all;
-- grant CREATE SESSION,CREATE PLUGGABLE DATABASE to C##REMOTE_CLONE_USER container=all;
-- grant sysoper to C##REMOTE_CLONE_USER container=all;

spool migrazione.log
set timing on echo on feedback on linesize 200 pagesize 200 trim on trims on

alter session set container=CDB$ROOT;
create database link CLONE_LINK_CDB01 connect to C##REMOTE_CLONE_USER identified by "password" using '192.168.0.1:2483/CDB01';

-- alter pluggable database PDB01 close;
-- drop pluggable database PDB01 including datafiles;

-- spostamento database
create pluggable database PDB01 from PDB01@CLONE_LINK_DAQ01D01 parallel 8 relocate availability max refresh mode every 30 minutes;
show pdbs;
alter pluggable database PDB01 open;
ALTER PLUGGABLE DATABASE ALL SAVE STATE INSTANCES=ALL;

spool off

Listener e service names

Definire i service names

alter session set container=PDB01;
exec dbms_service.stop_service(service_name=>'SERVICE01');
exec dbms_service.delete_service(service_name=>'SERVICE01');
exec dbms_service.create_service(service_name=>'SERVICE01',network_name=>'SERVICE01');
exec dbms_service.start_service(service_name=>'SERVICE01');
alter pluggable database PDB01 save state;
SELECT name,network_name FROM dba_services ORDER BY name;

select name from V$SERVICES;
select name from V$ACTIVE_SERVICES;

alter session set container=CDB$ROOT;
col pdb for a10
col name for a30
col network_name for a30
set pagesize 200 linesize 200 trim on trims on
SELECT pdb,name,network_name FROM cdb_services ORDER BY name;

definire listener

alter system set local_listener

ORA-65144

alter system disable restricted session;

col time for a30
col action for a80
select TIME,STATUS,ACTION from PDB_PLUG_IN_VIOLATIONS;

select * from PDB_PLUG_IN_VIOLATIONS where status !='RESOLVED';

Utenti CDB con autenticazione externally

L'autenticazione externally funziona nei PDB come autenticazione remota. Per ottenere lo stesso effetto è necessario creare un common user a cui assegnare la possibilità di accedere al PDB, e creare un trigger after logon per connetterlo al PDB.

nel CDB:

alter session set container=CDB$ROOT;
alter system set os_authent_prefix='C##' scope=spfile;

Riavviare il DB se necessario.

nel CDB:

alter session set container=CDB$ROOT;
create user c##user1 identified externally container=all;
grant alter session, create session to c##user1 container=all;

Trigger after connect per cambiare il PDB in base all'utente:

create or replace trigger SET_CONTAINER_AT_LOGON after logon on database
 when (user in ('C##USER1'))
  begin
   execute immediate 'alter session set container=PDB1';
  end;
/

Nel PDB:

alter session set container=PDB1 ;
grant set container to c##user1 container=current;

Grant aggiuntive:

grant resource to c##user1 container=current;

Da sistema operativo:

sqlplus /

Per verificare a quale container siamo connessi, se SHOW PDBS non funziona:

select sys_context('userenv', 'con_name') from dual;

Per riferimento:

SP2-0382: The SHOW PDBS Command Is Not Available (Doc ID 2669189.1)

References

https://mikedietrichde.com/2020/07/13/pitfalls-connect-to-a-pdb-directly-with-oracle_pdb_sid/