Oci clone and create Pdbs in Same Container

Introduction.

In the OCI it is best practice that (almost) all activities should be performed  via the Gui. And of course that all happens for very good reasons. This blog will focus on creating  a clone Pdb in same container database when (active) Data Guard is involved. Please be aware that the Ui will not allow you to create another pdb when the Data Guard is active ( the button will be gray).

Setup and recommendation for good practice:

  • There are different scenarios – opinions but when cloning pdb should be read only is my recommendation.
  • In a container if you have multi tenant option in licencings You should  consider using a pdb as a template  for cloning in your db. This will make sure that ALL tablespaces are encrypted. When a pdb is created via Oci ui, than this is always true!
  • Create such “empty” template Pdb before you activate your Data Guard. As always, planning is everything.
  • When creating pdb manually in sqplus be aware of below required steps too:

Preparations:

Check if  your template pdb has all tablspaces encrypted.

select TABLESPACE_NAME , ENCRYPTED from dba_tablespaces;

Make supre that the source pdb = read only.

SQL> alter pluggable database PDB<9> close instances=all;

SQL> alter pluggable database PDB<9> open read only instances=all;

## Checking

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ ONLY  NO

## Create Pdb from  pdb1 ( in same container)

SQL> create pluggable database PDB1C from PDB1 keystore identified by “<Password for keystore>” ;

## Checking

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ ONLY  NO

         4 PDB1C                        MOUNTED

## Open it

SQL> alter pluggable database  PDB1C open instances=all;

## Check

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                   READ ONLY  NO

         3 PDB1                           READ ONLY  NO

         4 PDB1C                         READ WRITE NO

## Check your tempfiles

SQL> select * from dba_temp_files;

FILE_NAME

——————————————————————————–

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

———- —————————— ———- ———- ——-

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

———— — ———- ———- ———— ———- ———–

SHARED           INST_ID

————- ———-

+DATA/PDB1C/TEMPFILE/temp.263.1087120765

         1 TEMP                            290455552      35456 ONLINE

           1 YES 3.4360E+10    4194302           80  289406976       35328

SHARED

## If not in place,  on prim side you will  need to add a temp file to the temp tablespace  :

SQL> alter tablespace temp add tempfile ‘+DATA’ size 100M;

## Check

SQL> show con_name

CON_NAME

——————————

CDB$ROOT

## Change into your PDB environment

SQL> alter session set container = PDB1C;

### Check if all tablespaces are indeed encrypted.

SQL> select TABLESPACE_NAME , ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC

—————————— —

SYSTEM                         YES

SYSAUX                         YES

UNDOTBS1                       YES

UNDO_2                         YES

USERS                          YES

TEMP                           YES

HUDDEL                         YES

7 rows selected.

When creating the pdb by hand , you will see that system , sysaux , undox are not encrypted , which will prevent you from moving beyond a  ‘restricted mode ‘ which shows when doing ‘show pdbs’ .

Next steps , when pdb  is not completely encrypted:

alter session set container = PDB1

select tablespace_name,encrypted from dba_tablespaces  order by 1 ;

## This might show something similar to below:

TABLESPACE_NAME                ENC

—————————— —

HUDDEL                         YES

SYSAUX                         NO

SYSTEM                         NO

TEMP                           NO

UNDOTBS1                       NO

UNDO_2                         YES

This will be an issue for cloning cause the clone will inherit this as well! So you should fix it.

ALTER TABLESPACE SYSTEM ENCRYPTION OFFLINE ENCRYPT;

ALTER TABLESPACE SYSAUX ENCRYPTION OFFLINE ENCRYPT;

ALTER TABLESPACE UNDOTBS1 ENCRYPTION OFFLINE ENCRYPT;

Note.  When testing this I was unable to make Temp encrypted as well.

Now let”s check again:

SQL> alter pluggable database PDB1 open instances = all;

SQL> select tablespace_name, encrypted from dba_tablespaces order by 1 ;

TABLESPACE_NAME                ENC

—————————— —

HUDDEL                         YES

SYSAUX                         YES

SYSTEM                         YES

TEMP                           NO

UNDOTBS1                       YES

UNDO_2                         YES

Note: After that first challenge (cloning a a pdb from a pdb  that was created by hand ) we had another issue that  sysaux tablespace was showing unencrypted blocks.

## so performed these steps :

Of course this could be fixed as well:

## make sure you are in your pdb.

SQL> show con_name

CON_NAME

——————————

PDB1C

## On the Primary  Side perform  a rekey of  the encryption of the sysaux tablespace:

SQL> ALTER TABLESPACE SYSAUX ENCRYPTION USING ‘AES128’ REKEY;

## Checking is believing:

SQL> select  tablespace_name,encrypted from  dba_tablespaces order by 1 ;

TABLESPACE_NAME                ENC

—————————— —

HUDDEL                         YES

SYSAUX                         YES

SYSTEM                         YES

TEMP                           NO

UNDOTBS1                       YES

UNDO_2                         YES

6 rows selected.

## Now lets check on SB Side a well:

SQL> show pdbs

    CON_ID CON_NAME                OPEN MODE  RESTRICTED

———- —————————— ———- ———-

   3 PDB1            READ ONLY  NO

   4 PDB1C           MOUNTED    NO

## Since we had the issue with encryption of Sysaux  PDB1C could not move beyond  mounted on sb side.

## Let’s give it an other try ( after fixing as described above).

SQL> alter pluggable database PDB1C open read only instances = all;

## Check from Root container that the SB side is happy now in the active Data Guard setup:

SQL> show pdbs

    CON_ID CON_NAME                OPEN MODE  RESTRICTED

———- —————————— ———- ———-

   3 PDB1            READ ONLY  NO

   4 PDB1C           READ ONLY  NO

As always seeing is believing. Always check scenarios  as the above in a Non Prod Environment first and test test test.

Till we meet again.

Mathijs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s