BigdataLyn's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Oracle 18c check cdb/pdb Tips

2018-08-05

Oracle 18c check cdb/pdb Tips

Some sql for checking cdb/pdbs are the following.

[oracle@database18c ~]$ . oraenv
ORACLE_SID = [PRODCDB] ? PRODCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@database18c ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Aug 5 05:50:38 2018
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 name,cdb,con_id from v$database;

NAME      CDB     CON_ID
--------- --- ----------
PRODCDB   YES          0

SQL> select instance_name, status, con_id from v$instance;

INSTANCE_NAME    STATUS           CON_ID
---------------- ------------ ----------
PRODCDB          OPEN                  0

SQL> 
SQL> !lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 07-AUG-2018 05:54:56

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database18c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                07-AUG-2018 05:52:42
Uptime                    0 days 0 hr. 2 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/18.3.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/database18c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=database18c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "72c5e38ff8194667e0530100007ff4b4" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
Service "PRODCDB" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
Service "PRODCDBXDB" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL> !lsnrctl services

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 07-AUG-2018 05:54:59

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=database18c)(PORT=1521)))
Services Summary...
Service "72c5e38ff8194667e0530100007ff4b4" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
	Handler(s):
	  "DEDICATED" established:0 refused:0 state:ready
		 LOCAL SERVER
Service "PRODCDB" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
	Handler(s):
	  "DEDICATED" established:0 refused:0 state:ready
		 LOCAL SERVER
Service "PRODCDBXDB" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
	Handler(s):
	  "D000" established:0 refused:0 current:0 max:1022 state:ready
		 DISPATCHER <machine: database18c, pid: 12792>
		 (ADDRESS=(PROTOCOL=tcp)(HOST=database18c)(PORT=11127))
Service "pdb1" has 1 instance(s).
  Instance "PRODCDB", status READY, has 1 handler(s) for this service...
	Handler(s):
	  "DEDICATED" established:0 refused:0 state:ready
		 LOCAL SERVER
The command completed successfully

SQL> 
SQL> select name,con_id from v$services;

NAME                                                                 CON_ID
---------------------------------------------------------------- ----------
PRODCDB                                                                   1
SYS$BACKGROUND                                                            1
SYS$USERS                                                                 1
pdb1                                                                      3
PRODCDBXDB                                                                1

SQL> show pdbs;

	CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
		 2 PDB$SEED                       READ ONLY  NO
		 3 PDB1                           MOUNTED
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------------------
1
SQL> 

Use sys_context to list the env.

SYS@PRODCDB> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

SYS@PRODCDB> select sys_context('userenv','con_id') from dual;

SYS_CONTEXT('USERENV','CON_ID')
--------------------------------------------------------------------------------
1

SYS@PRODCDB> 

Others sql.

SYS@PRODCDB> col pdb_name for a10
SYS@PRODCDB> select pdb_id,pdb_name,dbid,con_id from cdb_pdbs;

	PDB_ID PDB_NAME         DBID     CON_ID
---------- ---------- ---------- ----------
		 3 PDB1        276918485          3
		 2 PDB$SEED    821946563          2

SYS@PRODCDB> 


SYS@PRODCDB> col member for a50
SYS@PRODCDB> select group#,con_id,member from v$logfile;

	GROUP#     CON_ID MEMBER
---------- ---------- --------------------------------------------------
		 3          0 /u01/app/oracle/oradata/PRODCDB/redo03.log
		 2          0 /u01/app/oracle/oradata/PRODCDB/redo02.log
		 1          0 /u01/app/oracle/oradata/PRODCDB/redo01.log

SYS@PRODCDB> 

SYS@PRODCDB> col name for a50
SYS@PRODCDB> select name,con_id from v$controlfile;

NAME                                                   CON_ID
-------------------------------------------------- ----------
/u01/app/oracle/oradata/PRODCDB/control01.ctl               0
/u01/app/oracle/oradata/PRODCDB/control02.ctl               0

SYS@PRODCDB> 

SYS@PRODCDB> set linesize 1000
SYS@PRODCDB> set pagesize 1000
SYS@PRODCDB> col file_name for a50
SYS@PRODCDB> select file_name,tablespace_name,con_id from cdb_data_files order by con_id;

FILE_NAME                                          TABLESPACE_NAME                    CON_ID
-------------------------------------------------- ------------------------------ ----------
/u01/app/oracle/oradata/PRODCDB/system01.dbf       SYSTEM                                  1
/u01/app/oracle/oradata/PRODCDB/users01.dbf        USERS                                   1
/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf      UNDOTBS1                                1
/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf       SYSAUX                                  1
/u01/app/oracle/oradata/PRODCDB/PDB1/system01.dbf  SYSTEM                                  3
/u01/app/oracle/oradata/PRODCDB/PDB1/users01.dbf   USERS                                   3
/u01/app/oracle/oradata/PRODCDB/PDB1/undotbs01.dbf UNDOTBS1                                3
/u01/app/oracle/oradata/PRODCDB/PDB1/sysaux01.dbf  SYSAUX                                  3

8 rows selected.

SYS@PRODCDB> 


SYS@PRODCDB> col con_id for a10
SYS@PRODCDB> select username,common,con_id from cdb_users where username = 'SYSTEM';

USERNAME                                                                                          COM      CON_ID
-------------------------------------------------------------------------------------------------------------------------------- --- ----------
SYSTEM                                                                                            YES ##########
SYSTEM                                                                                            YES ##########

SYS@PRODCDB> 

SYS@PRODCDB> select username,con_id from cdb_users where common='NO';

USERNAME            CON_ID
--------------- ----------
PDBADMIN                 3
HR                       3

SYS@PRODCDB> 

SYS@PRODCDB> select cdb from v$database;

CDB
---
YES

SYS@PRODCDB> 
SYS@PRODCDB> select name,con_id from v$datafile order by 2;

NAME                                                             CON_ID
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/PRODCDB/system01.dbf                          1
/u01/app/oracle/oradata/PRODCDB/sysaux01.dbf                          1
/u01/app/oracle/oradata/PRODCDB/undotbs01.dbf                         1
/u01/app/oracle/oradata/PRODCDB/users01.dbf                           1
/u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf                  2
/u01/app/oracle/oradata/PRODCDB/pdbseed/undotbs01.dbf                 2
/u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf                  2
/u01/app/oracle/oradata/PRODCDB/PDB1/users01.dbf                      3
/u01/app/oracle/oradata/PRODCDB/PDB1/system01.dbf                     3
/u01/app/oracle/oradata/PRODCDB/PDB1/sysaux01.dbf                     3
/u01/app/oracle/oradata/PRODCDB/PDB1/undotbs01.dbf                    3

11 rows selected.

SYS@PRODCDB> 

DBCA create Database

dbca

To be continue….

Have a good life! 2018/08 via LinHong


Similar Posts

Comments