How to create common user at CDB level:
[oracle@oraclerac1 admin]$ cat tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclerac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:03:10
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclerac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:03:22 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> create user mallik identified by mallik;
create user mallik identified by mallik
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> create user c##_mallik identified by mallik;
User created.
SQL> grant dba to c##_mallik;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclerac1 admin]$ sqlplus c##_mallik/mallik@ORCL
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:04:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user
USER is "C##_MALLIK"
SQL>
How to create local user at PDB level:
[oracle@oraclerac1 admin]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:05:36 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> alter session set container=PDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE NO
SQL>
SQL> create user mallik identified by mallik;
User created.
SQL> grant dba to mallik;
Grant succeeded.
SQL>
Default service names in PDB:
[oracle@oraclerac1 ~]$ . oraenvORACLE_SID = [+ASM1] ? orcl1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclerac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:09:35 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>
[oracle@oraclerac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:52:35
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 05-FEB-2021 22:22:06
Uptime 0 days 20 hr. 30 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclerac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.229)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.233)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 ~]$ lsnrctl service
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:12:09
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: oraclerac1.localdomain.com, pid: 23742>
(ADDRESS=(PROTOCOL=tcp)(HOST=oraclerac1.localdomain.com)(PORT=23821))
Service "pdb" has 1 instance(s).
Instance "orcl1", 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@oraclerac1 ~]$
[oracle@oraclerac1 admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclerac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
PDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclerac-scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb)
)
)
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ tnsping PDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:54:29
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclerac-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb)))
OK (0 msec)
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ sqlplus mallik/mallik@PDB
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:55:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Feb 06 2021 18:40:08 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter service_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> show pdbs
SP2-0382: The SHOW PDBS command is not available
SQL> select con_id,name,open_mode from v$containers;
CON_ID NAME OPEN_MODE
---------- ------ ---------
3 PDB READ WRITE
SQL>
How to connect to PDB$SEED:
[oracle@oraclerac1 admin]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:31:58 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>
How to create new service and how to delete service at PDB level:
[oracle@oraclerac1 admin]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 18:42:38 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=PDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE NO
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>
exec dbms_service.CREATE_SERVICE('PDB1','PDB1');
exec dbms_service.start_service('PDB1');
SQL> exec dbms_service.CREATE_SERVICE('PDB1','PDB1');
PL/SQL procedure successfully completed.
SQL> exec dbms_service.start_service('PDB1');
PL/SQL procedure successfully completed.
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>
[oracle@oraclerac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:36:53
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 05-FEB-2021 22:22:06
Uptime 0 days 20 hr. 14 min. 46 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclerac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.229)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.233)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "PDB1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ lsnrctl service
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:37:09
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PDB1" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: oraclerac1.localdomain.com, pid: 23742>
(ADDRESS=(PROTOCOL=tcp)(HOST=oraclerac1.localdomain.com)(PORT=23821))
Service "pdb" has 1 instance(s).
Instance "orcl1", 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@oraclerac1 admin]$
SQL> alter session set container=PDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE NO
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL>
BEGIN
DBMS_SERVICE.stop_service(
service_name => 'PDB1'
);
END;
/
BEGIN
DBMS_SERVICE.delete_service(
service_name => 'PDB1'
);
END;
/
SQL> BEGIN
DBMS_SERVICE.stop_service(
service_name => 'PDB1'
);
END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_SERVICE.delete_service(
service_name => 'PDB1'
);
END;
/
PL/SQL procedure successfully completed.
SQL>
[oracle@oraclerac1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:45:19
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 05-FEB-2021 22:22:06
Uptime 0 days 20 hr. 23 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclerac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.229)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.35.36.233)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Service "pdb" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclerac1 admin]$
[oracle@oraclerac1 admin]$ lsnrctl service
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2021 18:45:29
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "+ASM_RECO" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "ba9c19dc75cf1dc2e053e524230a9483" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: oraclerac1.localdomain.com, pid: 23742>
(ADDRESS=(PROTOCOL=tcp)(HOST=oraclerac1.localdomain.com)(PORT=23821))
Service "pdb" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0 state:ready
LOCAL SERVER
The command completed successfully
[oracle@oraclerac1 admin]$
Check the active service and and services at CDB and PDB level:
[oracle@oraclerac1 admin]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 22:14:05 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL> select con_id, name from v$active_services;
CON_ID NAME
---------- ----------------------------------------------------------------
1 orclXDB
3 pdb
1 orcl
1 SYS$BACKGROUND
1 SYS$USERS
SQL>
SQL> select NAME,PDB,CON_ID from v$services;
NAME PDB CON_ID
-------------------- ------------------------------ ----------
orclXDB CDB$ROOT 1
pdb PDB 3
orcl CDB$ROOT 1
SYS$BACKGROUND CDB$ROOT 1
SYS$USERS CDB$ROOT 1
SQL>
SQL> alter session set container=PDB;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE NO
SQL> select con_id, name from v$active_services;
CON_ID NAME
---------- ----------------------------------------------------------------
3 pdb
SQL>
SQL> select NAME,PDB,CON_ID from v$services;
NAME PDB CON_ID
-------------------- ------------------------------ ----------
pdb PDB 3
SQL>
How/Where the PDB datafile are located:
[oracle@oraclerac1 ~]$ . oraenvORACLE_SID = [orcl1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclerac1 ~]$
[oracle@oraclerac1 ~]$ asmcmd -p
ASMCMD [+] > lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 512 4096 4194304 61428 51096 20476 15310 0 Y DATA/
MOUNTED NORMAL N 512 512 4096 4194304 30708 27480 10236 8622 0 N RECO/
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > ls -l
Type Redund Striped Time Sys Name
Y ASM/
N ORCL/
Y oraclu/
PASSWORD HIGH COARSE FEB 05 04:00:00 N orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.1063688371
PASSWORD HIGH COARSE FEB 05 05:00:00 N orapwasm_backup => +DATA/ASM/PASSWORD/pwdasm.257.1063688653
ASMCMD [+DATA] > cd ORCL
ASMCMD [+DATA/ORCL] > ls -l
Type Redund Striped Time Sys Name
Y 86B637B62FE07A65E053F706E80A27CA/
Y BA9BE798A5647436E053E524230AE0BD/
Y BA9C19DC75CF1DC2E053E524230A9483/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y PASSWORD/
Y TEMPFILE/
ASMCMD [+DATA/ORCL] >
[oracle@oraclerac1 ~]$ . oraenv
ORACLE_SID = [orcl1] ?
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclerac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Feb 6 01:51:47 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SQL>
SQL> SELECT CDB FROM V$DATABASE;
CDB
---
YES
SQL> COLUMN NAME FORMAT A8
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID DBID CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT 1 1591536784 1 86B637B62FDF7A65E053F706E80A27CA
PDB$SEED 2 3680827711 3680827711 BA9BE798A5647436E053E524230AE0BD
PDB 3 2635452016 2635452016 BA9C19DC75CF1DC2E053E524230A9483
Regards,
Mallik
No comments:
Post a Comment