Note:
This Practical is containing all information about file architecture of CDB and PDB. How will start and stop database of CDB with PDB and how to connect with CDB & PDB etc... This practical will help you make understanding with 19c multitenant architecture how to work.
1- Root, named CDB$ROOT. This contains Oracle metadata and common users (sys,system etc..).
2- Seed PDB, named PDB$SEED. This is a template that can be used to create new PDBs. You can’t add or modify objects in this PDB (PDB$SEED).
3- PDB is a pluggable database it will contain collection of objects like schemas and others objects(tablespaces etc..).
[oracle@dbs19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 15 11:51:26 2024
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> set lines 133 pages 133
SQL> desc dba_data_files;
Name Null? Type
------------------------------------------------------------------------- -------- -------------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
LOST_WRITE_PROTECT VARCHAR2(7)
SQL> col FILE_NAME format a60
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DBS19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/DBS19C/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/DBS19C/undotbs01.dbf
USERS /u01/app/oracle/oradata/DBS19C/users01.dbf
SQL> create tablespace dbs19c_Tablespace_TDE datafile '/u01/app/oracle/oradata/DBS19C/tde_tbs1.dbf' size 100M;
Tablespace created.
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DBS19C/system01.dbf
SYSAUX /u01/app/oracle/oradata/DBS19C/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/DBS19C/undotbs01.dbf
USERS /u01/app/oracle/oradata/DBS19C/users01.dbf
DBS19C_TABLESPACE_TDE /u01/app/oracle/oradata/DBS19C/tde_tbs1.dbf
SQL> create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_
create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLE
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb;
Session altered.
SQL> create user teddb identified by haf default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABL
create user teddb identified by haf default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLESPAC
*
ERROR at line 1:
ORA-01109: database not open
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> alter pluggable database pdb open;
Pluggable database altered.
SQL> alter pluggable database pdb save state;
Pluggable database altered.
SQL> alter session set container=pdb;
Session altered.
SQL> create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_
create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLE
*
ERROR at line 1:
ORA-00959: tablespace 'DBS19C_TABLESPACE_TDE' does not exist
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------------------------------------
UNDOTBS1 /u01/app/oracle/oradata/DBS19C/pdb/undotbs01.dbf
SYSAUX /u01/app/oracle/oradata/DBS19C/pdb/sysaux01.dbf
SYSTEM /u01/app/oracle/oradata/DBS19C/pdb/system01.dbf
USERS /u01/app/oracle/oradata/DBS19C/pdb/users01.dbf
SQL> col name format a10
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME CON_ID DBID CON_UID GUID
---------- ---------- ---------- ---------- --------------------------------
PDB 3 962847719 962847719 0EA7FFC0D70E4082E0632B29018C49BE
SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
---------- --------------- ----------
3 PDB NORMAL
SQL> shut immediate;
Pluggable Database closed.
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter pluggable database pdb open;
Pluggable database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> alter pluggable database pdb close;
Pluggable database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2248145928 bytes
Fixed Size 9137160 bytes
Variable Size 503316480 bytes
Database Buffers 1728053248 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> desc V$CONTAINERS
Name Null? Type
------------------------------------------------------------------------- -------- -------------------------------------------------
CON_ID NUMBER
DBID NUMBER
CON_UID NUMBER
GUID RAW(16)
NAME VARCHAR2(128)
OPEN_MODE VARCHAR2(10)
RESTRICTED VARCHAR2(3)
OPEN_TIME TIMESTAMP(3) WITH TIME ZONE
CREATE_SCN NUMBER
TOTAL_SIZE NUMBER
BLOCK_SIZE NUMBER
RECOVERY_STATUS VARCHAR2(8)
SNAPSHOT_PARENT_CON_ID NUMBER
APPLICATION_ROOT VARCHAR2(3)
APPLICATION_PDB VARCHAR2(3)
APPLICATION_SEED VARCHAR2(3)
APPLICATION_ROOT_CON_ID NUMBER
APPLICATION_ROOT_CLONE VARCHAR2(3)
PROXY_PDB VARCHAR2(3)
LOCAL_UNDO NUMBER
UNDO_SCN NUMBER
UNDO_TIMESTAMP DATE
CREATION_TIME DATE
PDB_COUNT NUMBER
AUDIT_FILES_SIZE NUMBER
MAX_SIZE NUMBER
MAX_DIAGNOSTICS_SIZE NUMBER
MAX_AUDIT_SIZE NUMBER
LAST_CHANGED_BY VARCHAR2(11)
MEMBER_CDB VARCHAR2(3)
TENANT_ID VARCHAR2(256)
UPGRADE_LEVEL NUMBER
GUID_BASE64 VARCHAR2(30)
SQL> desc DBA_PDBS
Name Null? Type
------------------------------------------------------------------------- -------- -------------------------------------------------
PDB_ID NOT NULL NUMBER
PDB_NAME NOT NULL VARCHAR2(128)
DBID NOT NULL NUMBER
CON_UID NOT NULL NUMBER
GUID RAW(16)
STATUS VARCHAR2(10)
CREATION_SCN NUMBER
VSN NUMBER
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(39)
FORCE_NOLOGGING VARCHAR2(3)
APPLICATION_ROOT VARCHAR2(3)
APPLICATION_PDB VARCHAR2(3)
APPLICATION_SEED VARCHAR2(3)
APPLICATION_ROOT_CON_ID NUMBER
IS_PROXY_PDB VARCHAR2(3)
CON_ID NOT NULL NUMBER
UPGRADE_PRIORITY NUMBER
APPLICATION_CLONE VARCHAR2(3)
FOREIGN_CDB_DBID NUMBER
UNPLUG_SCN NUMBER
FOREIGN_PDB_ID NUMBER
CREATION_TIME NOT NULL DATE
REFRESH_MODE VARCHAR2(6)
REFRESH_INTERVAL NUMBER
TEMPLATE VARCHAR2(3)
LAST_REFRESH_SCN NUMBER
TENANT_ID VARCHAR2(255)
SNAPSHOT_MODE VARCHAR2(6)
SNAPSHOT_INTERVAL NUMBER
CREDENTIAL_NAME VARCHAR2(262)
SQL> alter session set container=pdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> ALTER SESSION SET container=CDB$ROOT;
Session altered.
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
Session altered.
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDB$SEED
SQL> SELECT name, pdb FROM v$services;
no rows selected
SQL> ALTER SESSION SET CONTAINER=PDB;
Session altered.
SQL> COL PDB FORMAT A15
SQL> SELECT name, pdb FROM v$services;
NAME PDB
---------- ---------------
pdb PDB
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;
Session altered.
SQL> SELECT name, pdb FROM v$services;
no rows selected
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
SQL> COL NAME FORMAT A25
SQL> SELECT name, pdb FROM v$services;
NAME PDB
------------------------- ---------------
dbs19cXDB CDB$ROOT
dbs19c.hinopak.com CDB$ROOT
pdb PDB
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb;
Session altered.
SQL> alter pluggable database close;
Pluggable database altered.
SQL> startup;
Pluggable Database opened.
SQL> shut immediate;
Pluggable Database closed.
SQL> alter pluggable database open;
Pluggable database altered.
SQL> alter pluggable database pdb save state;
Pluggable database altered.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
SQL> ORACLE instance started.
Total System Global Area 2248145928 bytes
Fixed Size 9137160 bytes
Variable Size 503316480 bytes
Database Buffers 1728053248 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> alter session set container=pdb;
Session altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')FROM dual;
SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------
PDB
SQL> COLUMN name FORMAT A30
SQL> SELECT name, open_mode, recovery_status FROM v$pdbs ORDER BY 1;
NAME OPEN_MODE RECOVERY
------------------------------ ---------- --------
PDB READ WRITE ENABLED
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> conn / as sysdba
Connected.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> COLUMN name FORMAT A30
SQL> SELECT name, open_mode, recovery_status FROM v$pdbs ORDER BY 1 ;
NAME OPEN_MODE RECOVERY
------------------------------ ---------- --------
PDB READ WRITE ENABLED
PDB$SEED READ ONLY ENABLED
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/DBS19C/control01.ctl, /u01/app/oracle/fast_recovery_area/DBS19C/control02.ctl
SQL> alter session set container=pdb;
Session altered.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/DBS19C/control01.ctl, /u01/app/oracle/fast_recovery_area/DBS19C/control02.ctl
SQL> desc v$logfile;
Name Null? Type
------------------------------------------------------------------------- -------- -------------------------------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
CON_ID NUMBER
SQL> show con_name
CON_NAME
------------------------------
PDB
SQL> col MEMBER format a60
SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ------------------------------------------------------------
3 /u01/app/oracle/oradata/DBS19C/redo03.log
2 /u01/app/oracle/oradata/DBS19C/redo02.log
1 /u01/app/oracle/oradata/DBS19C/redo01.log
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
No comments:
Post a Comment