Cloning Pluggable Database In Oracle 12c

How to Clone an existing pluggable database in Oracle 12c.

Step 1:-

Verify all the databases present in the system.

Select name,pdb from v$services

NAME                                              PDB

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

SYS$BACKGROUND                  CDB$ROOT

SYS$USERS                                 CDB$ROOT     

TESTPDB                                     PDB.DOMAIN.COM

Let’s follow the below steps to clone the PDB “TESTPDB” to “TESTPDB_CP”.

Step 2:-

Close the pluggable database TESTPDB.

Sql > Alter pluggable database TESTPDB close

Step 3:-

Open the pluggable database TESTPDB in read only mode.

Sql > Alter pluggable database TESTPDB open read only

Step 4:-

Create the new pluggable database TESTPDB_CP from the pluggable database TESTPDB.

Sql > create pluggable database TESTPDB_CP from TESTPDB

          file_name_convert = (‘/TESTPDB’,’/TESTPDB_CP’)

Step 5:-

Open the source pluggable database TESTPDB in read write mode.

Sql > Alter pluggable database TESTPDB open

Step 6:-

Open the new pluggable database TESTPDB_CP in read write mode.

Sql > Alter pluggable database TESTPDB_CP open

Step 7:-

Now we can check the status of all the PDB’s.

Select name,pdb from v$services;

Performance Monitoring SQL

It will Show sessions along with operations which is doing full table scan or any sort operation which may consume lots of resources.

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, START_TIME,

ROUND(SOFAR/TOTALWORK*100,2) COMPLETE

FROM   V$SESSION_LONGOPS

WHERE SOFAR <> TOTALWORK;

Through the below sql we need to find out the queries which is currently running for more than 60 seconds.

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

       SID,  

       MACHINE,

       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT

  FROM V$SESSION SES,  

       V$SQLtext_with_newlines SQL

 where SES.STATUS = 'ACTIVE'

   and SES.USERNAME is not null

   and SES.SQL_ADDRESS    = SQL.ADDRESS

   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

order by runt desc, 1,sql.piece;

Show sessions holding a TX lock:

    SELECT * FROM v$lock vl, v$session vs, v$sqltext vt

     WHERE vl.TYPE = 'TX'

       AND vl.lmode > 0

       AND vl.sid = vs.sid

       AND vs.SQL_ADDRESS    = vt.ADDRESS

       AND vs.SQL_HASH_VALUE = vt.HASH_VALUE

Show sessions waiting for a TX lock:

    SELECT * FROM v$lock vl, v$session vs, v$sqltext vt

     WHERE vl.TYPE = 'TX'

       AND vl.request > 0

       AND vl.sid = vs.sid

       AND vs.SQL_ADDRESS    = vt.ADDRESS

       AND vs.SQL_HASH_VALUE = vt.HASH_VALUE