Database RAC Dataguard RMAN Performance Tuning Feedback
Database Migration & Upgradation
DataGuard
ASM & RAC
Exadata
RMAN Backup & Recovery
Oracle Cloud AWS Azure Google Cloud
DevOps Ansible Kubernetes Terraform
Shell Scripting Powershell Scripting
Infomratica Others

Tuesday, June 15, 2021

19c CDB and PDM Basic Administration Or Multitenant Database Administration

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 sysdba
SQL*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 ~]$ . oraenv
ORACLE_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 sysdba
SQL*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 sysdba
SQL*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 sysdba
SQL*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 ~]$ . oraenv
ORACLE_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
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month. [oracle@o...