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 a pdb by Hand (not recommended if there is no Data Guard active).
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.
Mathijs Bruggink 