Oracle PDB: differenze tra le versioni
Jump to navigation
Jump to search
(→PDB) |
|||
Riga 1: | Riga 1: | ||
=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= |
=PDB= |
||
Da sql: |
Da sql: |
Versione delle 13:37, 22 giu 2022
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
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'
Listener e service names
Definire i service names
alter session set container=PDB01 exec dbms_service.stop_service('SERVICE01'); exec dbms_service.delete_service('SERVICE01'); exec dbms_service.create_service('SERVICE01','SERVICE01'); exec dbms_service.start_service('SERVICE01'); alter pluggable database save state; SELECT name,network_name FROM dba_services ORDER BY name;
definire 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/