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

Procedura per spostare con minimo fermo un PDB da un CDB ad un altro CDB su altro server, via DB link. Va eseguita sul CDB di destinazione. Prima però serve creare un utente con i privilegi appropriati sul CDB sorgente.

-- se PDB sorgente indietro di versione bisogna prima aver lanciato preupgrade.jar ed eseguito preupgrade_fixups.sql
-- e la open va eseguita con l'opzione upgrade.
--
-- 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_CDB01 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/