EasyReliableDBA: Oracle 19c Commands-CDB And PDB

To Check Database details

select pdb_id,pdb_name,dbid,status from dba_pdbs;

SELECT NAME, OPEN_MODE, CDB FROM V$DATABASE;

SHOW CON_ID;

SHOW CON_NAME;

SELECT NAME, OPEN_MODE FROM V$CONTAINERS;

SHOW pdbs;

ALTER SESSION SET CONTAINER=pdb1;

We can use Oracle easy connect method to connect a PDB directly.

CONNECT username/password@host[:port][/service_name][:server][/instance_name

sqlplus user/password@//localhost/pdb2

select name,open_mode,status from dba_pdbs p ,v$pdbs s where p.con_id=s.con_id;

To check service

select name,pdb from v$services;

To check parameters are modifiable at PDB level?

select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

To monitor SGA usage on a PDB by PDB basis

SQL> alter session set container=CDB$ROOT;

SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';

SQL> select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL;

To close and open PDB database in read only

ALTER PLUGGABLE DATABASE pdb5 CLOSE;

ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

To Create user and grant

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

To Check Archive Usage

SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB FROM gv$parameter WHERE name = 'db_recovery_file_dest_size'

column USED_GB format 999,990.999

column USED% format 990.99

column RECLAIM_GB format 999,990.999

column RECLAIMABLE% format 990.99

column LIMIT_GB format 999,990.999

select frau.file_type as type,

frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",

frau.percent_space_used "USED%",

frau.percent_space_reclaimable "RECLAIMABLE%",

frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",

frau.number_of_files "FILES#"

from v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

order by file_type;

TYPE USED_GB USED% RECLAIMABLE% RECLAIM_GB FILES#

----------------------- ------------ ------- ------------ ------------ ----------

ARCHIVED LOG 1,998.001 19.99 0.00 0.000 320

AUXILIARY DATAFILE COPY 0.000 0.00 0.00 0.000 0

BACKUP PIECE 0.000 0.00 0.00 0.000 0

CONTROL FILE 0.000 0.00 0.00 0.000 0

FLASHBACK LOG 0.000 0.00 0.00 0.000 0

FOREIGN ARCHIVED LOG 0.000 0.00 0.00 0.000 0

IMAGE COPY 0.000 0.00 0.00 0.000 0

REDO LOG 0.000 0.00 0.00 0.000 0

select name,

space_limit/1024/1024/1024 "LIMIT_GB",

space_used/1024/1024/1024 "USED_GB",

space_used/space_limit*100 "USED%",

space_reclaimable/1024/1024/1024 "RECLAIM_GB",

number_of_files "FILE#"

from v$recovery_file_dest

select trunc(l.first_time) arch_date, l.inst_id, l.status, n.destination, round(sum( blocks * block_size ) / ( 1024 * 1024 * 1024 ) ) total_file_size_gb, count(*) file_count

from gv$archived_log l, gv$archive_dest n

where l.inst_id = n.inst_id

and l.dest_id = n.dest_id

and n.destination = 'USE_DB_RECOVERY_FILE_DEST'

group by trunc(l.first_time),l.inst_id,l.status,n.destination

order by trunc(l.first_time) ,l.inst_id,l.status,n.destination

The status can have several values:

A - Available

D - Deleted

U - Unavailable

X - Expired

So, based on this column we can calculate how much archivelog is created and how much is still available in the FRA.

select trunc(l.first_time) arch_date

, round(sum(decode(l.status,'D',blocks * block_size,0)/(1024 * 1024 * 1024))) deleted_gb

, round(sum(decode(l.status,'A',blocks * block_size,0)/(1024 * 1024 * 1024))) available_gb

, round(sum(decode(l.status,'U',blocks * block_size,0)/(1024 * 1024 * 1024))) unavailable_gb

, round(sum(decode(l.status,'X',blocks * block_size,0)/(1024 * 1024 * 1024))) expired_gb

, round(sum(blocks * block_size) /(1024 * 1024 * 1024)) total_size_gb

from gv$archived_log l,gv$archive_dest n

where l.inst_id = n.inst_id

and l.dest_id = n.dest_id

and n.destination = 'USE_DB_RECOVERY_FILE_DEST'

group by trunc(l.first_time)

order by trunc(l.first_time)

SELECT inst_id

, to_char(oldest_flashback_time,'dd-mm-yyyy-hh24:mi') oldest_flashback_time

, retention_target

, round((sysdate - oldest_flashback_time ) * (60 * 24)) actual_retention_possible

, round(flashback_size / (1024 * 1024 * 1024)) flashback_size_gb

, round(estimated_flashback_size / (1024 * 1024 * 1024)) estimated_flashback_size_gb

FROM gv$flashback_database_log

To check Tablespace and resize data file

SQL> select name,open_mode from V$database;

NAME OPEN_MODE

--------- --------------------

TESTCDB READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TESTPROD READ WRITE NO

SQL>

alter session set container=TESTPROD

select group_number,name,state,type,total_mb,free_mb,usable_file_mb from v$asm_diskgroup

col "TABLESPACE" for a50

col "Totalspace(MB)" for 9999999

col "Used Space(MB)" for 9999999

set linesize 200

select t.tablespace,

t.totalspace as " Totalspace(MB)",

round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",

nvl(fs.freespace,0) as "Freespace(MB)",

round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",

round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"

from

(select round(sum(d.bytes)/(1024*1024)) as totalspace,

d.tablespace_name tablespace

from dba_data_files d group by d.tablespace_name) t,

(select round(sum(f.bytes)/(1024*1024)) as freespace,

f.tablespace_name tablespace

from dba_free_space f group by f.tablespace_name) fs

where t.tablespace=fs.tablespace (+) and fs.tablespace='&TSPACE'

order by t.tablespace;

col "TABLESPACE" for a50

col "Totalspace(MB)" for 9999999

col "Used Space(MB)" for 9999999

set linesize 200

select t.tablespace,

t.totalspace as " Totalspace(MB)",

round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",

nvl(fs.freespace,0) as "Freespace(MB)",

round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",

round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"

from

(select round(sum(d.bytes)/(1024*1024)) as totalspace,

d.tablespace_name tablespace

from dba_data_files d group by d.tablespace_name) t,

(select round(sum(f.bytes)/(1024*1024)) as freespace,

f.tablespace_name tablespace

from dba_free_space f group by f.tablespace_name) fs

where t.tablespace=fs.tablespace (+)

order by t.tablespace;

set num 9

select name,free_mb,total_mb from v$asm_diskgroup;

set num 5

COL file_name FORMAT A90

select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE from dba_data_files where AUTOEXTENSIBLE='YES';

set num 5

COL file_name FORMAT A90

select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC' ;

select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='&TSPAC' ;

select file_name from dba_data_files where tablespace_name='&TSPAC' ;

alter database datafile '+DATA1/TESTCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1041.1088802799' resize 20480m

alter database datafile '+DATA1/TESTCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1042.1088802857' resize 20480m

SQL>

SQL> alter database datafile '+DATA1/TESTCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1041.1088802799' resize 20480m

2 ;

Database altered.

SQL> alter database datafile '+DATA1/TESTCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1042.1088802857' resize 20480m

2 ;

Database altered.

alter database datafile '+DATA1/TESTCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1043.1088804069' resize 25480m

#######################################################

---Each container in a CDB stores data in its own data files and handles temporary data in its own temp files.--

List the root's tablespaces, data files, and temp files.

connect / as sysdba

select tablespace_name, con_id from cdb_tablespaces where con_id=1;

select file_name, con_id from cdb_data_files where con_id=1;

select file_name, con_id from cdb_temp_files where con_id=1;

-----Create a permanent tablespace in the root.---

create tablespace cdata datafile '/u01/app/oracle/oradata/cdb1/cdata01.dbf' SIZE 10M;

select tablespace_name, con_id from cdb_tablespaces order by con_id;

Create a tablespace in a PDB

connect system/oracle@localhost:1521/pdb3_bis

create tablespace ldata datafile '/u01/app/oracle/oradata/cdb1/pdb3/ldata01.dbf' SIZE 10M;

select tablespace_name, con_id from cdb_tablespaces order by con_id;

select file_name, con_id from cdb_data_files order by con_id;

select file_name from dba_data_files

Create a temporary tablespace in the PDB.

create temporary tablespace temp_pdb3 tempfile '/u01/app/oracle/oradata/cdb1/pdb3/temppdb301.dbf' SIZE 10M;

select tablespace_name, con_id from cdb_tablespaces where contents='TEMPORARY';

select file_name from dba_temp_files;

Create a common user while you are connected to the root.

connect / as sysdba

create user c##1 identified by oracle container=all;

select username, common, con_id from cdb_users where username like 'C##%';

Connect as a DBA in a PDB to create a local user.

connect system/oracle@localhost:1521/pdb3_bis

create user hr identified by oracle;

select username, common, con_id from cdb_users where username ='HR';

Connect as the local HR user in each PDB.

connect hr/oracle@localhost:1521/pdb2

connect hr/oracle@localhost:1521/pdb3_bis

Create a common role

connect / as sysdba

create role c##r1 container=all;

select role, common, con_id from cdb_roles where role='C##R1';

Create a local role in a PDB. (When you are connected to a PDB, you cannot create a common role.)

connect system/oracle@localhost:1521/pdb3_bis

create role hr_manager;

select role, common, con_id from cdb_roles where role='HR_MANAGER';

create role c##r2 container=all;

You should see an error message after executing this statement because you cannot create a common role inside a PDB.

Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE

Drop the common user and role that you created.

drop user c##1;

drop role c##r1;

Drop the tablespaces that you created in the CDB root.

drop tablespace cdata including contents;

drop tablespace temp_root including contents;

Open pdb1 and replace the database trigger with a trigger that opens only pdb1 at CDB startup.

alter pluggable database pdb1 open;

create or replace trigger Sys.After_Startup after startup on database

begin

execute immediate 'alter pluggable database pdb1 open';

end After_Startup;

How to Tell If the Database Is a CDB

select name, cdb, con_id from v$database;

Viewing Information About Containers

select name, con_id, db_id from v$containers;

Viewing Information About the PDBs

select pdb_id, pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB

select name, open_mode, restricted, open_time from v$pdbs;

Viewing the History of PDBs

select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property;

Viewing all the tablespaces in a CDB

select tablespace_name, con_id from cdb_tablespaces;</em>

How to check for Local Undo feature in 12.2

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ ------------------------------

LOCAL_UNDO_ENABLED TRUE

Connect to a PDB

The syntax to connect to a PDB varies depending on whether or not there is an entry within the tnsnames.ora file for the PDB.

As the oracle user:

Without an entry to the tnsnames.ora file, the syntax to connect to a PDB named orclpdb1 is as follows:

List all the available services and PDBs within the CDB:

SQL> select name, con_id from v$active_services;

NAME CON_ID

------------------------------------------------

orclpdb2 4

SYS$BACKGROUND 1

SYS$USERS 1

cdbXDB 1

orclpdb1 3

cdb 1

6 rows selected.

CONN <user>/<pwd>@//<host>:<listener port>/<service> {as sysdba};

$ $ORACLE_HOME/bin/sqlplus sys/<password>@localhost:1521/orclpdb1 as sysdba;

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 5 20:20:29 2017

Startup and Shutdown

Startup and Shutdown of a multitenant database function the same as on a regular database, however, if connected to pluggable database, only the pluggable database shuts down. If connected to the root container database then the entire instance shuts down. Pluggable databases also have their own commands that can be run from the root container or other pluggable db.

ALTER PLUGGABLE DATABASE <name>OPEN READ WRITE{RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN READ ONLY {RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN UPGRADE {RESTRICTED};

ALTER PLUGGABLE DATABASE <name> CLOSE {IMMEDIATE};

To retain state as startup state of container...

ALTER PLUGGABLE DATABASE <name> SAVE STATE;

To create plugable database

mkdir -p /u02/oradata/pdbs/clonepdb

CREATE PLUGGABLE DATABASE clonepdb FROM trsenpdb

PATH_PREFIX = '/u02/oradata/pdbs/clonepdb'

FILE_NAME_CONVERT = ('/u02/oradata/pdbs/trsenpdb/', '/u02/oradata/pdbs/clonepdb')

SERVICE_NAME_CONVERT = ('trsenpdb.com','clonepdb')

NOLOGGING;

view the pdbs's informations;

SYS@EMREP>select name,open_mode,status from dba_pdbs p ,v$pdbs s where p.con_id=s.con_id;

NAME OPEN_MODE STATUS

---------------------------------------- -------------------- ------------------

PDB$SEED READ ONLY NORMAL

PDBEMREP READ WRITE NORMAL

TRSENPDB READ WRITE NORMAL

CLONEPDB MOUNTED NEW

SYS@EMREP>select name,pdb from v$services;

NAME PDB

---------------------------------------- ------------------------------------------------------------

clonepdb.com CLONEPDB

trsenpdb.com TRSENPDB

pdbemrep.com PDBEMREP

EMREPXDB CDB$ROOT

handpay.com CDB$ROOT

SYS$BACKGROUND CDB$ROOT

SYS$USERS CDB$ROOT

SYS@EMREP>ALTER PLUGGABLE DATABASE clonepdb open read write;

Pluggable database altered.

SYS@EMREP>select name,open_mode,status from dba_pdbs p ,v$pdbs s where p.con_id=s.con_id;

NAME OPEN_MODE STATUS

---------------------------------------- -------------------- ------------------

PDB$SEED READ ONLY NORMAL

PDBEMREP READ WRITE NORMAL

TRSENPDB READ WRITE NORMAL

CLONEPDB READ WRITE NORMAL

Following are clauses used for Cloning and moving in syntax of CREATE PLUGGABLE DATABASE:

Clauses:

Storage Limit: clause specifies that the storage used by all tablespaces in PDB. you can use unlimited as STORAGE (MAXSIZE UNLIMITED)

Default Tablespace: clause to separate the data for multiple schemas into different PDBs. like noncdb have multiple schema which transfer to PDB.

PATH_PREFIX: clause ensures that all directory object paths associated with the PDB are restricted to the specified directory or its subdirectories

FILE_NAME_CONVERT: clause is the names of the PDB files after the PDB is created.

CREATE_FILE_DEST: clause specifies the default Oracle Managed Files file system directory or Oracle ASM disk group for the PDB’s files.

DB_CREATE_FILE_DEST: specifies the default location for Oracle Managed Files for the CDB

PDB_FILE_NAME_CONVERT: initialization parameter maps names of existing files to new file names

SERVICE_NAME_CONVERT : clause used to convert the service name used for connectivity.

SQL> COL NAME FORMAT A15

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

Enabling Local Undo

You simply can enable Local Undo by setting the database into STARTUP UPGRADE mode and then changing the undo mode to Local Undo:

-- Issue in SQL*Plus connected as SYSDBA

startup upgrade;

alter database local undo ON;

alter database open;

drop restore point PDB1_GRP1;

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string

db_recovery_file_dest_size big integer 0

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination F:\Oracle_database\RDBMS

Oldest online log sequence 54

Current log sequence 56

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 CDB_PDB1 MOUNTED

4 PDB1 READ WRITE NO

SQL> alter session set container=PDB1;

select pdb_name,name,pdb_restore_point,clean_pdb_restore_point from v$restore_point natural join dba_pdbs;

select property_name,property_value from database_properties where property_name like '%UNDO%';

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

SQL> create restore point STAGE1 guarantee flashback database;

Restore point created.

SQL> SELECT NAME,CON_ID ,TIME FROM V$RESTORE_POINT;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SDCP1 READ WRITE NO

SQL> alter pluggable database SDCP1 CLOSE;

Pluggable database altered.

SQL> flashback pluggable database SDCP1 to restore point STAGE1;

Flashback complete.

What if LOCAL_UNDO is not enabled?

In oracle 12.2 if local_undo is not enabled, then we can use AUXILIARY DESTINATION parameter , which will create an auxiliary instance with container database’s system,sysaux and undo tablespace, and then restore the PDB using rollback transactions. However this flahback will take a long time and will consume additional space on the auxiliary destination.

steps FOR SHARED UNDO:

SQL> alter pluggable database SDCP1 close;

SQL>flashback pluggable database SDCP1 to restore point STAGE1 auxiliary destination '/oradata/aux_inst';

SQL> alter pluggable database SDCP1 open resetlogs;

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

set linesize 132 serveroutput on pagesize 50000

select * from v$FLASHBACK_DATABASE_LOGFILE;

select * from V$FLASHBACK_DATABASE_STAT;

select * from V$FLASHBACK_DATABASE_LOG;

NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME

---------------------------------------------------- ---------- ---------- ---------- ---------- ------------- --------------------

+<FRA DISK GROUP>/log_1.261.782829073 1 2 6 15941632 1048169 10-MAY-2012 10:00:04 lowest log sequence# listed

+<FRA DISK GROUP>/log_60.339.782904711 60 1 103 15941632 1045574 10-MAY-2012 09:31:51 highest log sequence# listed.

CREATE PLUGGABLE DATABASE pdbtest ADMIN USER pdb1_admin IDENTIFIED BY pdb123 ROLES=(CONNECT)

CREATE_FILE_DEST='F:\oracle\oradata\pdb1test';

COLUMN name FORMAT A30

SELECT name, pdb FROM v$services

ORDER BY name;

The V$SERVICES views can be used to display available services from the database.

COLUMN name FORMAT A30

SELECT name, pdb FROM v$services

ORDER BY name;

NAME PDB

------------------------------ ------------------------------

SYS$BACKGROUND CDB$ROOT

SYS$USERS CDB$ROOT

cdb1 CDB$ROOT

cdb1XDB CDB$ROOT

pdb1 PDB1

pdb2 PDB2

6 rows selected.

SQL>

The lsnrctl utility allows you to display the available services from the command line.

$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 20-MAY-2014 09:01:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

Services Summary...

Service "cdb1" has 1 instance(s).

Instance "cdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "cdb1XDB" has 1 instance(s).

Instance "cdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"D000" established:0 refused:0 current:0 max:1022 state:ready

DISPATCHER

(ADDRESS=(PROTOCOL=tcp)(HOST=ol6-121.localdomain)(PORT=21196))

Service "pdb1" has 1 instance(s).

Instance "cdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

Service "pdb2" has 1 instance(s).

Instance "cdb1", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

The command completed successfully

Connections using services are unchanged from previous versions.

SQL> -- EZCONNECT

SQL> CONN system/password@//localhost:1521/cdb1

Connected.

SQL>

SQL> -- tnsnames.ora

SQL> CONN system/password@cdb1

Connected.

SQL>

The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.

CDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb1)

)

Displaying the Current Container

The SHOW CON_NAME and SHOW CON_ID commands in SQL*Plus display the current container name and ID respectively.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL>

SQL> SHOW CON_ID

CON_ID

------------------------------

SQL>

They can also be retrieved using the SYS_CONTEXT function.

SELECT SYS_CONTEXT('USERENV', 'CON_NAME')

FROM dual;

SYS_CONTEXT('USERENV','CON_NAME')

--------------------------------------------------------------------------------

CDB$ROOT

SQL>

SELECT SYS_CONTEXT('USERENV', 'CON_ID')

FROM dual;

SYS_CONTEXT('USERENV','CON_ID')

--------------------------------------------------------------------------------

SQL>

Switching Between Containers

When logged in to the CDB as an appropriately privileged user, the ALTER SESSION command can be used to switch between containers within the container database.

SQL> ALTER SESSION SET CONTAINER=pdb1;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PDB1

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL>

From Oracle 12.2 onward the ALTER SESSION command can also specify the service within the container. We can demonstrate this by creating a new service in a PDB, then switching to that service.

CONN / AS SYSDBA

ALTER SESSION SET CONTAINER=pdb1'

BEGIN

DBMS_SERVICE.create_service('my_new_service','my_new_service');

DBMS_SERVICE.start_service('my_new_service');

END;

ALTER SESSION SET CONTAINER=pdb1 SERVICE=my_new_service;

COLUMN con_id FORMAT A10

COLUMN service_name FORMAT A30

SELECT SYS_CONTEXT('USERENV', 'CON_ID') AS con_id,

SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name

FROM dual;

CON_ID SERVICE_NAME

---------- ------------------------------

6 my_new_service

SQL>

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made using a service. Each pluggable database automatically registers a service with the listener. This is how any application will connect to a pluggable database, as well as administrative connections.

SQL> -- EZCONNECT

SQL> CONN system/password@//localhost:1521/pdb1

Connected.

SQL>

SQL> -- tnsnames.ora

SQL> CONN system/password@pdb1

Connected.

SQL>

The connection using a TNS alias requires an entry in the "$ORACLE_HOME/network/admin/tnsnames.ora" file, such as the one shown below.

PDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = ol6-121.localdomain)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb1)

)

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB. All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the CREATE SESSION privilege to enable connections.

JDBC Connections to PDBs

It has already been mentioned that you must connect to a PDB using a service. This means that by default many JDBC connect strings will be broken. Valid JDBC connect strings for Oracle use the following format.

# Syntax

jdbc:oracle:thin:@[HOST][:PORT]:SID

jdbc:oracle:thin:@[HOST][:PORT]/SERVICE

# Example

jdbc:oracle:thin:@ol6-121:1521:pdb1

jdbc:oracle:thin:@ol6-121:1521/pdb1

When attempting to connect to a PDB using the SID format, you will receive the following error.

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Ideally, you would correct the connect string to use services instead of SIDs, but if that is a problem the USE_SID_AS_SERVICE_listener_name listener parameter can be used.

Edit the "$ORACLE_HOME/network/admin/listener.ora" file, adding the following entry, with the "listener" name matching that used by your listener.

USE_SID_AS_SERVICE_listener=on

Reload or restart the listener.

$ lsnrctl reload

Now both of the following connection attempts will be successful as any SIDs will be treated as services.

jdbc:oracle:thin:@ol6-121:1521:pdb1

jdbc:oracle:thin:@ol6-121:1521/pdb1

Data Pump Connections (expdp, impdp)

Connections to the expdp and impdp utilities are unchanged, provided you specify a service.

expdp username/password@service ...

expdp \"username/password@service as sysdba\" ...

impdp username/password@service ...

impdp \"username/password@service as sysdba\" ...

Connections as SYSDBA must be to a common user. For example.

expdp scott/tiger@pdb1 tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

expdp \"sys/SysPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

expdp \"c##myuser/MyPassword1@pdb1 as sysdba\" tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

admin password =admin@123

select * from product_component_version;

To bypass this error, run your Command Prompt as Administrator before lunching "DBCA".

Add the following parameter to bypass the error:

dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false &

https://DESKTOP-I2SNK8O:5501/em

C:\Windows\system32>set ORACLE_HOME=f:\oracle_database

C:\Windows\system32>set oracle_sid=CDB2

C:\Windows\system32>sqlplus " /as sysdba"

SQL>

SQL> select * from v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE NUM_WRITES NUM_WRITERS CON_ID

---------- ---------- ---------- ---------- ----------- ----------

0 0 0 0 0 1

0 0 0 0 0 2

0 0 0 0 0 3

SQL>

SQL> alter session set container=app_pdb;

Session altered.

SQL> show con_name

SQL> show pdbs

SELECT con_id, name, open_mode, application_root app_root,

application_pdb app_pdb, application_seed app_seed

from v$containers

where application_root = 'NO' and application_PDb = 'NO'

and application_seed = 'NO';

application_pdb app_pdb, application_seed app_seed

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 CDB_PDB1 READ WRITE NO

SQL>

Create Database from control file trace file

CREATE DATABASE cdb1

DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/app/oracle/oradata/cdb1/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE

'/u01/app/oracle/oradata/cdb1/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1

('/u01/app/oracle/oradata/cdb1/redo01.log') SIZE 50M, GROUP 2 ('/u01/app/oracle/oradata/cdb1/redo02.log') SIZE 50M,

GROUP 3 ('/u01/app/oracle/oradata/cdb1/redo03.log') SIZE

50M

USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM

IDENTIFIED BY "&&systemPassword"

ENABLE PLUGGABLE DATABASE …;

SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba

set sqlprompt SALES_APP_ROOT>

SQL> conn sys/oracle@host01:1522/sales_app_root as sysdba

SELECT cause, type, message, status, action

select name from v$tempfile;

Select tablespace_name from dba_tablespaces;

SELECT name FROM v$datafile;

select tablespace_name,file_name from dba_data_files;

SELECT con_id, name, open_mode, application_root,application_pdb, application_seed from v$containers where application_pdb='YES';

SQL> show con_name;

CON_NAME

------------------------------

CDB$ROOT

SQL> select count(*) from sys.dba_tab_statistics where last_analyzed is not null;

2274

SQL> ALTER SESSION SET CONTAINER =XEPDB1;

Sesión modificada.

SQL> select count(*) from sys.dba_tab_statistics where last_analyzed is not null;

2641

SQL> ALTER SESSION SET CONTAINER =CDB$ROOT;

Session altered.

SQL> EXEC DBMS_STATS.GATHER_DATABASE_STATS;

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dba_tab_statistics where last_analyzed is not null;

3727

SQL> ALTER SESSION SET CONTAINER =XEPDB1;

Session altered.

SQL> select count(*) from sys.dba_tab_statistics where last_analyzed is not null;

Real-Time Example

----------------

In this example ,We have creating easyuser and providing select privilete

1) check if user exist

select username,account_status,profile from dba_users where username like 'easyuser';

2) easyuser don't exist , so we will take metadata for similiar type of user

set long 9999999

set pagesize 0

set linesize 120

select DBMS_METADATA.GET_DDL('USER','EASYUSER2') from dual;

create user easyuser identified by <PASSWORD> default tablespace users temporary tablespace temp;

ALTER USER BZ2805 quota unlimited on users;

GRANT CREATE SESSION to easyuser;

Grant succeeded.

SQL> GRANT CONNECT to easyuser;

select username,account_status,profile from dba_users where username like 'easyuser';

grant READ_ROLE to easyuser

Dynamic query to generate privilege

select 'GRANT SELECT ON '||OWNER||'.'||object_name ||' TO R_ODS_DAT;'

from dba_objects where owner in ('INTERNSCHEMA','EASYSCHEMA') and object_type='TABLE';

To reset password

select

username,

last_login,

password

from

dba_users where username in ('EASYUSER','EASYUSER2')

col name format a20

col password format a60

select name,password from sys.user$ where name in ('EASYUSER','EASYUSER2')

alter user EASYUSER identified by Infra123

select username,account_status,profile from dba_users where username like 'easyuser';

To check privilege

SQL> select * from dba_role_privs where grantee='easyuser';

no rows selected

SQL> select * from dba_sys_privs where grantee='easyuser';

no rows selected

SQL>

select * from dba_tab_privs where grantee='easyuser';

grant CONNECT,EASY_ADMIN_ROLE to 'easyuser

grant create session to 'easyuser

grant CREATE SYNONYM to 'easyuser

EasyReliableDBA

Wednesday, 17 March 2021

Oracle 19c Commands-CDB And PDB

No comments:

Post a Comment

Search This Blog