Category: CDB/PDB

How to Restore a PDB into another CDB from Tape Backups

This is to demonstrate how to restore a pdb from backups, then plug into a different cdb with same or another pdb name.

Environment

Source database name CDBDBA , pdb name pdba
Target database name CDBDBB, pdb name pdbb

Prepare Auxiliary Database

create pfile from target db

SQL> create pfile='/tmp/initCDBDUP1.ora' from spfile;

File created.

drop pdbb on target if already exists

SQL> ALTER PLUGGABLE DATABASE PDBB  CLOSE IMMEDIATE INSTANCES=ALL;

SQL> DROP PLUGGABLE DATABASE  PDBB  including datafiles;

SQL> select NAME,TOTAL_MB/1024,FREE_MB/1024 from  v$asm_diskgroup order by NAME;

startup auxiliary instance

Modify /tmp/initCDBDUP1.ora file with the following values:

cluster_database = false
control_files ='+DATA1','+FRA' 
db_unique_name = CDBDUP 
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST'

SQL> startup nomount pfile='/tmp/initCDBDUP1.ora'

The create a spfile, which is compulsory for duplicate database. 

SQL>  create spfile from pfile='/tmp/initCDBDUP1.ora';

Make sure $ORACLE_HOME/dbs/initCDBDUP1.ora pointing to newly created spfile either in $ORACLE_HOME/dbs or in ASM

Then force instance to use spfile.

SQL> startup nomount force
ORACLE instance started.

Total System Global Area 5.3687E+10 bytes
Fixed Size 37495560 bytes
Variable Size 9261023232 bytes
Database Buffers 4.4292E+10 bytes
Redo Buffers 96718848 bytes

SQL> show parameter pfile;

NAME    TYPE  VALUE
-----   ----  -------------------------------------------------------------
spfile string /u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileCDBDUP1.ora

Maybe the spfile created in ASM :

SQL> show parameter pfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/CDBDUP/PARAMETERFILE/spfile.859.1175610247

Duplicate Database with Required pdb:

$ nohup rman auxiliary / catalog rman@rman_catalog/Passwd cmdfile=duplicate_CDBDBA_PDBA_to_CDBDUP.rmna log= rman_duplicate_CDBDBA_PDBA_to_CDBDUP.log &
$ cat duplicate_CDBDBA_PDBA_to_CDBDUP.rman
run
{
Allocate auxiliary channel c1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
Allocate auxiliary channel c2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
Allocate auxiliary channel c3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=1048576";
SET NEWNAME FOR DATABASE TO '+DATA';
duplicate database CDBDBA to CDBDUP pluggable database pdba until time "TO_DATE('2024-07-10 11:11:11','YYYY-MM-DD HH24:MI:SS')";
}

While duplicate a PDB, the following error might occur at the end of the RMAN process when dropping a tablespace, though it can be ignored because the PDB has been open.

Executing: drop tablespace "AUDIT_AUX" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/19/2025 19:39:47
RMAN-05501: aborting duplication of target database
RMAN-06136: Oracle error from auxiliary database: ORA-22868: table with LOBs contains segments in different tablespaces

Recovery Manager complete.

Unplug and Plug pdb

The rest of the work is to unplug the PDB from the temporary auxiliary PDB, drop the auxiliary CDB and finally plug the PDB into the target CDB.

Uuplug pdb

SQL> select name from v$database;

NAME
-----
CDBDUP
SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO
 3     pdba       READ WRITE NO

Better bounce the Auxiliary database here.

SQL> shutdown immediate;

SQL> startup;
SQL> alter pluggable database pdba close immediate instances=all;

Pluggable database altered.
SQL> alter pluggable database  pdba unplug into '/tmp/pdba.xml';

Pluggable database altered.
SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO

plug pdb into new CDB

SQL> select name from v$database;

NAME
-----
CDBDBB
SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO
$ grep -v "<parameter>" /tmp/pdba.xml > /tmp/pdba_no_param.xml

Check compatibility:

set serveroutput on
declare
compatible constant varchar2(3) :=
case dbms_pdb.check_plug_compatibility(
pdb_descr_file=>'/tmp/pdba_no_param.xml',
pdb_name=>'pdba')
when true then 'YES'
else 'NO'
END;
begin
dbms_output.put_line(compatible);
end;

/

YES

PL/SQL procedure successfully completed.
SQL> create pluggable database pdbb using '/tmp/pdba_no_param.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

here there are two options
— copy
SQL> create pluggable database pdbb using ‘/tmp/pdba_no_param.xml’ copy;
— NOCOPY TEMPFILE REUSE

SQL> alter pluggable database pdbb open instances=all;

Pluggable database altered.
SQL> alter pluggable database pdbb save state;

Pluggable database altered.
SQL> show pdbs

CON_ID CON_NAME   OPEN MODE  RESTRICTED
------ ---------  ---------  -----------
 2     PDB$SEED   READ ONLY  NO
 3     pdba       READ WRITE NO

Now we can drop the auxiliary database.

drop auxillary duplicate database

-- no need this step, cos after the auxiliary database, the orphan PDB pdba will be dropped with CDB auxiliary database CDBDUP.

SQL> drop pluggable database pdba keep datafiles;

Pluggable database dropped.
SQL> startup mount restrict 

ORACLE instance started.

Total System Global Area 5.3687E+10 bytes
Fixed Size 37495560 bytes
Variable Size 7918845952 bytes
Database Buffers 4.5634E+10 bytes
Redo Buffers 96718848 bytes
Database mounted.
SQL> drop database;

Database dropped.

If you use COPY option to create the pdb, then you are able to remove the ASM files of the old pdb manually if still there.

ASMCMD> pwd
+data2/CDBDUP/05CBB1A7A1FCD520E0630C21010A57ED/datafile
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> pwd
+data2/CDBDUP/05CBB1A7A1FCD520E0630C21010A57ED/datafile
ASMCMD> cd ../
ASMCMD-8002: entry 'CDBDUP' does not exist in directory '+data2/'

run datapatch if rquired when the patch versions are different.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose
SQL> select NAME,CAUSE,MESSAGE,TYPE,CON_ID  from PDB_PLUG_IN_VIOLATIONS where STATUS !='RESOLVED';

If still get Errors in PDB_PLUG_IN_VIOLATIONS

  1. check rollback/applied sqlpatch to confirm.
    SQL>set linesize 150col logfile for a90
    SQL>select patch_id, action, logfile, status from registry$sqlpatch;
  2. Bounce the database, make PDB online on all nodes, the it should be fine.
SQL> select NAME,CAUSE,MESSAGE,TYPE,CON_ID  from PDB_PLUG_IN_VIOLATIONS where STATUS !='RESOLVED';

How to Change Global_Name for a PDB after Added Default Domain

For some reason, a database has to be reconfigured with a domain name “.world”. After change the spfile and bounced the database, we need change the global_name manually.

  1. Check current global_name in CDB:
SQL>  select * from global_name;

GLOBAL_NAME
--------------
TESTDB

2. Drop database links now, and need recreate them later. because they are inaccessible after global_name changed. Otherwise you will get the following errors:

SQL>  drop  database link TESTDBLINK;
 drop  database link TESTDBLINK
                     *
ERROR at line 1:
ORA-02024: database link not found

To fix this issue, you have to rename global_name back to without ‘.world’, and remove domain from GLOBAL_NAME. The only way to remove the domain is to manually update the props$ table.

SQL> connect / as sysdba

SQL> show parameter domain

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_domain                            string      world

SQL> alter database rename global_name to "TESTDB";

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
-------------
TESTDB

SQL> update sys.props$ set value$ = 'TESTDB' where name = 'GLOBAL_DB_NAME';

1 row updated.

SQL>  select * from global_name;

GLOBAL_NAME
--------------
TESTDB

SQL> commit;

SQL> drop database link TESTDB; -> Drop old dblinks

Database link dropped.

at this point, you should define db_domain to a not NULL value, rename GLOBAL_NAME to db_name.db_domain and drop and recreate all dblinks.

SQL>  alter database rename global_name to "TESTDB.WORLD";

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------
TESTDB.WORLD

3. Change global name in CDB.

QL> alter database TESTDB rename global_name to "TESTDB.WORLD";
Database altered.

SQL> select * from global_name;
GLOBAL_NAME
----------------------------------
TESTDB.WORLD

4. Connect to PDB and restart

This is can be done node by node to take advantage of RAC high availability. One node with domain and other one without domain. so the application can still be running during this change.

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------
PDB1

SQL> alter session set container=PDB1;
Session altered.

SQL> alter pluggable database PDB1 close;
Pluggable database altered.

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

-- Check global_name for PDB
SQL> select * from global_name;
GLOBAL_NAME
----------------------------------
PDB1.WORLD

Finally recreate all the necessary database links.

Interesting Case

After changing CDB global_name to “TESTDB.WORLD”, the PDB global_name ( PDB1) is not changed after database bounced. “Invalid Username/Password” errors occur when using user created services attached to PDB.

After trying to change CDB global_name to “TESTDB” without domain name, bounced the database, then CDB global_Name is still “TESTDB.WORLD”. The more interesting thing happed. PDB global_name changed to “PDB1.WORLD” strangly.

Just record those for future reference.

How to Drop a Pluggable Database

Close the Pluggable Database

SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE IMMEDIATE INSTANCES=ALL;

Drop PDB but Keep Datafiles

SQL>  DROP PLUGGABLE DATABASE PDB1 keep datafiles;

Drop PDB Including Datafiles

SQL>  DROP PLUGGABLE DATABASE PDB1 including datafiles;

ORA-65036: pluggable database not open in required mode

When clone a pluggable database from remote CDB database, the following error occurs:

SQL> create pluggable database PDB1 from PDB1@remote_db_link;
create pluggable database PDB1 from PDB1@remote_db_link
*
ERROR at line 1:
ORA-17628: Oracle error 65036 returned by remote Oracle server
ORA-65036: pluggable database not open in required mode

SOLUTION

Make sure source PDB is opened on all instances, otherwise on source CDB:

SQL>  alter pluggable database pdb1 open instances=all;



Pluggable database altered.

If still not working, then check “LOCAL_UNDO_ENABLED” enabled or not.

ORA-65054: Cannot open a pluggable database in the desired mode

In RAC environment, we cannot open PDB with the following messages:

SQL> alter pluggable database open instances=all;

*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.

Check CDB still in Mount status:

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

QL> select name,open_mode from v$database;

NAME    OPEN_MODE
------  ---------
TESTDB  MOUNTED


SQL> alter database open ;

*
ERROR at line 1:
ORA-65054: Cannot open a pluggable database in the desired mode.

CAUSE and SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe