Create ,drop, clone pluggable databases and solve wallet issue

 Case1 : DB files are in ASM storage

Verify the default location where database files are configured to be created.

SQL>Show parameter db_create_file_dest

If the PDB already exists, it must be dropped before proceeding.

SQL>alter session set container=CDB$ROOT;

SQL>ALTER PLUGGABLE DATABASE MYPDB close immediate instances=ALL;

SQL>DROP PLUGGABLE DATABASE MYPDB  INCLUDING DATAFILES;

If this is the first time the PDB is being created, follow the steps outlined below.

SQL>CREATE PLUGGABLE DATABASE MYPDB ADMIN USER admin IDENTIFIED BY  <password>;

SQL>ALTER PLUGGABLE DATABASE MYPDB open instances = all;

It is important to save the PDB state; otherwise, the PDB may start in the MOUNTED state each time the container database is restarted.

SQL>ALTER PLUGGABLE DATABASE MYPDB save state instances = all;

SQL>alter session set container= MYPDB ;

Check default tablespaces exists or not ? If USERS or TEMP tablespaces don't exists ,please create as below.

SQL> select distinct tablespace_name from dba_data_files;

SQL>CREATE TABLESPACE "USERS" DATAFILE SIZE 10M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

=============================

Case2 : When DB files are stored in filesystem 

Please change file location as per your environment. 

Note here - I am using PDB$SEED . It acts as a template for creating new pluggable databases (PDBs). 

CREATE PLUGGABLE DATABASE MYPDB ADMIN USER admin IDENTIFIED BY "*****" DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/BSSOSSCDB/MYPDB/users01.dbf' SIZE 100M REUSE AUTOEXTEND ON file_name_convert = ('/u01/app/oracle/oradata/BSSOSSCDB/pdbseed/','/u01/app/oracle/oradata/BSSOSSCDB/MYPDB/');

ALTER PLUGGABLE DATABASE MYPDB open instances = all;

ALTER PLUGGABLE DATABASE MYPDB save state instances = all;

===================================

Case3 : New tablespace  creation failing in PDB due to TDE configuration.

In some scenarios, creating a new tablespace in a PDB may fail because Transparent Data Encryption (TDE) is configured at the container (CDB) level.

To verify the wallet status, connect to the root container and run the following SQL:

alter session set container=CDB$ROOT;

SET LINESIZE 200

COLUMN wrl_parameter FORMAT A50

SELECT * FROM v$encryption_wallet; 

If the encryption wallet is not open, you must open it using the TDE master key password. Once the wallet is opened, you will be able to create new database objects, including tablespaces, in the PDB.

alter session set container=MYPDB;

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'TAG_NAME' FORCE KEYSTORE IDENTIFIED BY Master_Key_pwd WITH BACKUP ;

=====================================

Case 4: Clone an Existing PDB to a New PDB with All Datafiles

An existing pluggable database (PDB) can be cloned to a new PDB within the same container database for proof-of-concept (PoC) purposes. This cloning method copies all datafiles from the source PDB.

Please note that this operation requires downtime for the source PDB, as the source PDB must be closed during the cloning process.

SQL> alter pluggable database MYPDB close immediate instances = all;

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

SQL> CREATE PLUGGABLE DATABASE MYPDBNEW FROM MYPDB;

######In case wallet is configured

SQL> CREATE PLUGGABLE DATABASE MYPDBNEW FROM MYPDB keystore identified by "Master_Key_pwd";

SQL>  alter pluggable database MYPDB close immediate instances = all;

SQL>  alter pluggable database MYPDB open read write instances = all;

SQL> alter pluggable database MYPDBNEW open read write instances = all;

SQL> ALTER PLUGGABLE DATABASE MYPDBNEW save state instances = all; 

=====================================

Case 5: PDBs are hosted in file system

alter pluggable database MYPDB close immediate instances = all;

alter pluggable database MYPDB open read only instances = all;

SQL> CREATE PLUGGABLE DATABASE PINPDB2 FROM PINPDB1 file_name_convert = ('/u01/app/oracle/oradata/BSSTMP/MYPDB/','/u01/app/oracle/oradata/BSSTMP/MYPDBNEW/');

alter pluggable database MYPDB close immediate instances = all;

alter pluggable database MYPDB open read write instances = all;

alter pluggable database MYPDBNEW open read write instances = all;

ALTER PLUGGABLE DATABASE MYPDBNEW save state instances = all;