A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Creating pluggable database from another pdb, when pdb$seed in in mount state

2 min read
SQL> create pluggable database PDB1 from DEV1 create_file_dest='+DEV_DG_FLASH';
 
Pluggable database created.
 
SQL> show pdbs
 
    CON_ID CON_NAME                  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                          MOUNTED 
         3 PDB1                                  MOUNTED
         4 DEV                                   READ WRITE    NO
SQL> alter pluggable database PDB1 open;
 
Pluggable database altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                 OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB1                               READ WRITE      NO
         4 DEV                                 READ WRITE      NO

→  Here we can see that new pluggable database PDB1 created from another pluggable database DEV even if the PDB$SEED is in mount state.

Here we can see from where the PDB is created

SQL> select PDB_NAME,PDB_ID,OP_TIMESTAMP,CLONED_FROM_PDB_NAME from DBA_PDB_HISTORY;
ALTER DATABASE MOVE DATAFILE '+DEV_DG/DEV//DATAFILE/d_r_m004.260.1005408637' TO '/rman_backup/backup/d_r_m004.260.1005408637' REUSE ;

Database altered.

ALTER DATABASE MOVE DATAFILE '+DEV_DG/DEV//DATAFILE/d_r_m004.260.1005408637' TO '/rman_backup/backup/d_r_m004.260.1005408637' REUSE ;

Database altered.

ALTER DATABASE MOVE DATAFILE '+DEV_DG/DEV//DATAFILE/d_r_m004.260.1005408637' TO '/rman_backup/backup/d_r_m004.260.1005408637' REUSE ;

Database altered.
SQL> shutdown immediate

SQL> startup upgrade

SQL> alter database local undo off;

Drop undo tablespace in PDB

SQL> shutdown immediate

SQL> startup
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

SQL> CREATE UNDO TABLESPACE LOCALUNDO DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M;

SQL> ALTER PLUGGABLE DATABASE PDB$SEED CLOSE;

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;