Apesar de ser possível definir o diretório que abrigará os arquivos no novo PDB, o indicado é ter o recurso OMF no ambiente, para delegar ao Oracle a administração dos arquivos e a definição de seus diretórios e nomes. Checando se o ambiente possui o OMF:
[oracle@oel8 ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 7 15:41:50 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> show parameter DB_CREATE_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /oracle/dados
SQL>
Criando o PDB chamado HIPOFISE1:
SQL> CREATE PLUGGABLE DATABASE HIPOFISE1
ADMIN USER BSS IDENTIFIED BY BSS
DEFAULT TABLESPACE USERS
STORAGE (MAXSIZE 2G); 2 3 4
Pluggable database created.
SQL>
Realizando a abertura do banco de dados:
SQL> ALTER PLUGGABLE DATABASE HIPOFISE1 OPEN;
Pluggable database altered.
SQL>
Verificando se o PDB novo está registrado no listener:
[oracle@oel8 ~]$ lsnrctl service
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 07-MAR-2021 15:52:34
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oel8.localdomain)(PORT=1521)))
Services Summary...
Service "TALAMO.localdomain" has 1 instance(s).
Instance "TALAMO", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "TALAMOXDB.localdomain" has 1 instance(s).
Instance "TALAMO", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: oel8.localdomain, pid: 4391>
(ADDRESS=(PROTOCOL=tcp)(HOST=oel8.localdomain)(PORT=33655))
Service "bcf7b920ca5c126ae0536b00a8c0b4f1.localdomain" has 1 instance(s).
Instance "TALAMO", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "hipofise1.localdomain" has 1 instance(s).
Instance "TALAMO", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@oel8 ~]$
Adicionando uma nova string de conexão no arquivo tnsnames.ora:
[oracle@oel8 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/18.0.0/product/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
CORTEX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel7.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cortex.localdomain)
)
)
TALAMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TALAMO.localdomain)
)
)
LISTENER_TALAMO =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
HIPOFISE1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oel8.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HIPOFISE1.localdomain)
)
)
[oracle@oel8 admin]$
Realizando o teste de conexão via listener:
[oracle@oel8 admin]$ sqlplus BSS/BSS@HIPOFISE1
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 7 16:01:17 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Sun Mar 07 2021 15:59:49 -03:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL>
Fazendo consulta no container sobre os seus PDBs. Uma coluna importante é a GUID, que é um ID único para cada PDB, e que é utilizado pelo OMF para criação de diretórios:
[oracle@oel8 admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Mar 7 16:03:09 2021
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;
INSTANCE_NAME STATUS
---------------- ------------
TALAMO OPEN
SQL> col name format a10
SELECT NAME, CON_ID, OPEN_MODE, GUID FROM V$PDBS ORDER BY 1;SQL>
NAME CON_ID OPEN_MODE GUID
---------- ---------- ---------- --------------------------------
HIPOFISE1 3 READ WRITE BCF7B920CA5C126AE0536B00A8C0B4F1
PDB$SEED 2 READ ONLY BC8B772AA6A82204E0536B00A8C0CF6B
SQL>
Checando diretório criado OMF:
[oracle@oel8 TALAMO]$ ll
total 4
drwxr-x---. 3 oracle oinstall 22 Mar 7 15:45 BCF7B920CA5C126AE0536B00A8C0B4F1
drwxr-x---. 2 oracle oinstall 33 Mar 2 06:30 controlfile
drwxr-x---. 2 oracle oinstall 4096 Mar 2 06:36 datafile
drwxr-x---. 2 oracle oinstall 93 Mar 2 06:30 onlinelog
[oracle@oel8 TALAMO]$ pwd
/oracle/dados/TALAMO
[oracle@oel8 TALAMO]$ cd BCF7B920CA5C126AE0536B00A8C0B4F1
[oracle@oel8 BCF7B920CA5C126AE0536B00A8C0B4F1]$ ll
total 0
drwxr-x---. 2 oracle oinstall 175 Mar 7 15:49 datafile
[oracle@oel8 BCF7B920CA5C126AE0536B00A8C0B4F1]$ cd datafile/
[oracle@oel8 datafile]$ ll
total 860248
-rw-r-----. 1 oracle oinstall 387981312 Mar 7 16:04 o1_mf_sysaux_j4b7tm62_.dbf
-rw-r-----. 1 oracle oinstall 283123712 Mar 7 16:04 o1_mf_system_j4b7tm5g_.dbf
-rw-r-----. 1 oracle oinstall 65019904 Mar 7 15:49 o1_mf_temp_j4b7tm65_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Mar 7 16:01 o1_mf_undotbs1_j4b7tm63_.dbf
-rw-r-----. 1 oracle oinstall 104865792 Mar 7 15:54 o1_mf_users_j4b81g2r_.dbf
[oracle@oel8 datafile]$
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish
Pingback: ALL options for creating a PDB using Create Puggable Database command – Bruno Santos da Silva
Pingback: ALL options for creating a PDB using Create Puggable Database command – SWIV