We can use this document to change GUID of a PDB database.
Recently when we tried to convert a Non-CDB database as PDB, it through error "ORA-65122: Pluggable database GUID conflicts with GUID of an existing container"
We found the the existing PDB with same GUID and the new PDB were both at one time got refreshed with same database using RMAN .
One workaround is to drop the exiting PDB(but keeping the datafile) and recreate the PDB with 'AS CLONE' in 'Create Pluggable database command' This option will create the PDB with new GUID .
Lets test it out.
---------------------------------
Our Environment.
CDB database : WALLCDB
PDB database : OVERPDB
TDE enabled : YES.
---------------------------------
1. Check the status of PDB and notedown the GUID and te datafiles info.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB READ WRITE NO
4 REMPDB1 READ WRITE NO
SQL> alter session set container=overpdb;
Session altered.
SQL> select guid from v$containers;
GUID
--------------------------------
BADA4B84200F2A1DE0530118A8C0BCC3
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/OVERPDB/DATAFILE/system.306.1063982809
+DATA/OVERPDB/DATAFILE/sysaux.307.1063982845
+DATA/OVERPDB/DATAFILE/undotbs1.309.1063982869
+DATA/OVERPDB/DATAFILE/users.308.1063982871
+DATA/OVERPDB/DATAFILE/undotbs2.442.1063983359
+DATA/OVERPDB/DATAFILE/overpdb_ts.278.1063987847
2) Export the TDE keys of source PDB database.
If our PDB database has TDE enabled, we need to export the TDE keys and reimport them after we recreate the PDB 'AS CLONE'
DATABASE_HOST DB_NAME DATABASE_ROLE OPEN_MODE STARTUP_TIME
-------------------------- ---------------- ---------------- -------------------- --------------------
vm19rac1.localdomain wallcdb1 PRIMARY READ WRITE 01-MAR-21
vm19rac2.localdomain wallcdb2 PRIMARY READ WRITE 01-MAR-21
SQL> select con_id,name from v$pdbs;
CON_ID NAME
---------- ----------
2 PDB$SEED
3 OVERPDB
4 REMPDB1
SQL> set lines 200
col wrl_parameter for a25
col wrl_type for a19
select * from v$encryption_wallet;SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------- ------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/wallcdb/WALLET/ OPEN AUTOLOGIN SINGLE NONE NO 1
ASM OPEN AUTOLOGIN SINGLE UNITED NO 2
ASM OPEN AUTOLOGIN SINGLE UNITED NO 3 --- This is our PDB,OVERPDB
ASM OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNITED UNDEFINED 4
SQL> alter session set container=overpdb;
Session altered.
SQ> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/u01/app/software/CLONE/overpdb_export.p12' FORCE KEYSTORE IDENTIFIED BY Welcome1;
keystore altered.
3) Connect to CDB and Shutdown the PDB database as below.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB READ WRITE NO
4 REMPDB1 READ WRITE NO
SQL> alter pluggable database OVERPDB close instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB MOUNTED
4 REMPDB1 READ WRITE NO
4) Unplug the PDB to xml as below so that we can use the xml file to re-create the PDB in later steps.
Connect to the root container and execute below command
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB MOUNTED
4 REMPDB1 READ WRITE NO
SQL> alter pluggable database OVERPDB unplug into '/u01/app/software/CLONE/OVERPDB_unplug.xml';
Pluggable database altered.
5) Now drop the PDB database but keep the datafiles so that we can reuse them when we re-create the PDB database.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB MOUNTED
4 REMPDB1 READ WRITE NO
SQL> drop pluggable database OVERPDB keep datafiles;
Pluggable database dropped.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 REMPDB1 READ WRITE NO
6) Re-create the PDB database using 'AS CLONE ' option .
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OVERPDB MOUNTED
4 REMPDB1 READ WRITE NO
SQL> create pluggable database OVERPDB AS CLONE using '/u01/app/software/CLONE/OVERPDB_unplug.xml'
Pluggable database created.
7) Open the pluggable database in read write mode .
SQL> alter pluggable database OVERPDB open instances=all;
show pdbs;
Warning: PDB altered with errors.
SQL>
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 REMPDB1 READ WRITE NO
5 OVERPDB READ WRITE YES
As you can see the PDB database is open in RESTRICTED mode, this is because we still need to import the TDE keys and bounce the database again.
8) Lets check for any violation, it should complain about the TDE keys which we will import in next step.
SQL> alter session set container=OVERPDB;
Session altered.
set lines 200
col message for a44
select con_id, name, type, message, status
from PDB_PLUG_IN_VIOLATIONS
where status<>'RESOLVED'
order by name,time;
CON_ID NAME TYPE MESSAGE STATUS
------ -------- --------- -------------------------------------------- ---------
5 OVERPDB ERROR PDB needs to import keys from source. PENDING
9) Now Import the TDE keys into the PDB.
SQL>
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 REMPDB1 READ WRITE NO
5 OVERPDB READ WRITE YES
SQL> alter session set container=OVERPDB;
Session altered.
SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/u01/app/software/CLONE/overpdb_export.p12' FORCE KEYSTORE IDENTIFIED BY Welcome1 with backup;
keystore altered.
10) Restart the PDB OVERPDB.
Since we have imported the TDE keys, we need to bounce the PDB database so that it can come out of RESTRICTED mode.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 REMPDB1 READ WRITE NO
5 OVERPDB READ WRITE YES
SQL> alter pluggable database OVERPDB close instances=all;
Pluggable database altered.
SQL> alter pluggable database OVERPDB open instances=all;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 REMPDB1 READ WRITE NO
5 OVERPDB READ WRITE NO
11)) Now check the GUID to see if its changed from the intial GUID which we took note in earlier steps.
SQL> select GUID from v$containers;
GUID
--------------------------------
BC81BB9BBE025FA7E0530118A8C027F0
GUID is in fact changed now when compared to the GUID that we saw in step 1.
12) Now check the datafiles to see if 'Create PDB' has created any diretory with new GUID and created any new aliases.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------
+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/system.579.1066061957+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/sysaux.578.1066061957+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/undotbs1.580.1066061957
+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/users.282.1066061957+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/undotbs2.576.1066061957+DATA/WALLCDB/BC81BB9BBE025FA7E0530118A8C027F0/DATAFILE/overpdb_ts.577.1066061957
6 rows selected.
13) Finally check if we can assess the database object to see if TDE is open and fine.
SQL> select * from encrypt;
NAME
----------------------------------------------------------------------------------------------------
This is OVERPDB table and its Encrypted
=================================================================================== END OF DOCUMENT ==================================================================
No comments:
Post a Comment