Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Restore dropped multitenant PDB using auxiliary instance on the same DBCS server

Introduction

In a multitenant container database (CDB), the metadata for data dictionary tables and view definitions is stored only in the root. However, each pluggable database (PDB) has its own set of data dictionary tables and views for the database objects contained in the PDB.

So, a pluggable database (PDB) cannot be restored as a database. It must be restored as database object using an auxiliary instance. If a pluggable database (PDB)/datafiles are accidentally deleted, corrupted, and so on, the repository/metadata for the PDB still exists and it can be restored and recovered

If a PDB is accidentally/intentionally dropped using the DROP PLUGGABLE DATABASE <PDBNAME> command, this will drop the PDB and remove the metadata from repository. Therefore, a restore of the PDB (including PDB PITR to before the dropped time) will fail with an error such as RMAN-06813: could not translate pluggable database PDBxx.

Objectives

Restore and recover a PDB which was dropped including the datafiles. Since it cannot be restored in the same CDB, we will a create an auxiliary instance and restore the PDB in auxiliary CDB on same instance and also in the source.

Prerequisites

• Complete the Oracle Database Cloud Service (DBCS) database configuration

• Take a full backup of the CDB

Task 1: Create a new pluggable database

• Run the following commands for pluggable database creation.

   SQL> create pluggable database <PDBNAME3> admin user <USERNAME> identified by <PASSWORD>;
  
   COLUMN PDB_NAME FORMAT A15
   SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;
   PDB_ID PDB_NAME STATUS
   ---------- --------------- ---------
   2 <PDBNAME$SEED> NORMAL
   3 <PDBNAME1> NORMAL
   4 <PDBNAME2> NORMAL
   5 <PDBNAME3> NORMAL       
  

Task 2: Back up the Container Database (CDB)

• Connect to RMAN using sysdba or sysbackup user and take a backup of the CDB database which includes the pluggable database .

  RMAN>BACKUP DATABASE PLUS ARCHIVELOG ALL;

Task 3: Drop the pluggable database created in Task 1

• To simulate the case, we drop the pluggable database .

  SQL> drop pluggable database <PDBNAME3> INCLUDING DATAFILES ;

Task 4: Restore the dropped pluggable database in same source database

• Trying to restore PDB as a database, in same container, fails with RMAN-06813 error.

   RMAN> restore pluggable database <PDBNAME3>;
  
   Starting restore at <MM/DD/YYYY>
   using channel ORA_DISK_1
   RMAN-00571: ===========================================================
   RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   RMAN-00571: ===========================================================
   RMAN-03002: failure of restore command at <MM/DD/YYYY HH:MI:SS>
   RMAN-06813: could not translate pluggable database <PDBNAME3>
  

Task 5: Create a temporary auxiliary instance

• Create a temporary auxiliary instance in the same source (Production Server) or in another server (Auxiliary Server) to restore container database (CDB) and required pluggable database (PDB).

• In this example, auxiliary instance is created in the same source server.

• Create init file for auxiliary instance.

   -- create init<DBNAME AUX>.ora
   -- verify compatible parameter matches the version of your source db (init<DBCDBNAME>.ora)
  
   *.sga_target=10G
   *.control_files=+DATA/CDBAUX02/CONTROLFILE/control.ctl
   *.compatible=19.0.0.0.0
   *.db_files=65534
   *.db_create_file_dest=+DATA
   *.db_create_online_log_dest_1=+RECO
   *.undo_tablespace= UNDOTBS1
   *.remote_login_passwordfile=EXCLUSIVE
   *.global_names=FALSE
   *.instance_name=CDBAUX02
   *.audit_trail=DB
   *.db_name=CDB01
   *.db_unique_name=CDBAUX02
   *.enable_pluggable_database= TRUE
   *.db_recovery_file_dest=+RECO
   *.db_recovery_file_dest_size=40G
   *.wallet_root=/opt/oracle/dcs/commonstore/wallets/CDBAUX02
   *.one_step_plugin_for_pdb_with_tde=FALSE
   *.tde_configuration='keystore_configuration=FILE'
   *.DB_FILE_NAME_CONVERT='CDB01_IAD1ZD','CDBAUX02'
   *.log_file_name_convert='+RECO/CDB01_IAD1ZD/ONLINELOG/','+RECO/CDBAUX02/ONLINELOG/'
  

Task 6: Start the auxiliary instance in nomount state

• Start the auxiliary instance in nomount state using the following commands.

  [oracle@sud ~]$ mkdir -p /home/oracle/CDBAUX02

  [oracle@sud ~]$ cd /home/oracle/CDBAUX02

  [oracle@sud CDBAUX02]$ export ORACLE_SID=CDBAUX02

Task 7: Restore controlfile from backup in the IdP instance and mount the database

• Restore controlfile from backup in auxiliary instance and mount the database using the following commands.

  run
  {
  set dbid=1672349321;
  set until time "to_date('2023-05-02 17:03:27','YYYY-MM-DD HH24:MI:SS')";
  allocate channel ch1 device type sbt parms 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/oss/CDB01_iad1zd/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/oss/CDB01_iad1zd/97164268-b09e-4c4d-8295-e3d3337b07e0/opc_CDB01_iad1zd.ora)';
  restore controlfile from 'c-1672349321-20230502-13' ;
  alter database mount;
  }
  

Task 8: Restore and recover the dropped PDB database in auxiliary instance

• Restore and recover the dropped PDB database in auxiliary instance using the following commands.

  select status from V$BLOCK_CHANGE_TRACKING;
  alter database disable block change tracking;
  select status from V$BLOCK_CHANGE_TRACKING;
  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
  alter database flashback off;
  Changing the State of (DISABLE RECOVERY)  other PDB:
  set lines 300 pages 300
  select 'alter session set container='||name||'; '|| CHR(13) || CHR(10) ||'alter pluggable database disable recovery; ' from v$pdbs where name not in ('PDB03');
  

• If the source database has multiple PDBs and we want to restore CDB with ONLY ONE PDB, then disable the recovery for all other PDBs in Aux database as above and restore only the dropped PDB along with root container database.

  run
  {
  set until time "to_date('2023-05-02 17:03:27','YYYY-MM-DD HH24:MI:SS')";
  allocate channel ch1 device type sbt parms 'SBT_LIBRARY=/opt/oracle/dcs/commonstore/oss/CDB01_iad1zd/libopc.so ENV=(OPC_PFILE=/opt/oracle/dcs/commonstore/oss/CDB01_iad1zd/97164268-b09e-4c4d-8295-e3d3337b07e0/opc_CDB01_iad1zd.ora)';
  set newname for database to '+DATA/';
  restore pluggable database PDB03,root;
  switch datafile all;
  recover pluggable database PDB03,root;
  }
  

• When RMAN has applied the last archived redo log in the backup and can’t find any more logs, a message similar to the following appears: Unable to find archived log..

• Recover the database using the following command.

  SQL> recover database using backup controlfile until cancel;

  

Task 9: Add and drop the online redo log file in source database

• Online Redo log groups adding (4,5,6) and deleting (1,2,3) in Source server.

  

• See the size of the current logs.

  select GROUP#,THREAD#,MEMBERS,STATUS,BYTES/1024/1024 from v$log;

• Let’s create 3 new log groups and name them groups 4, 5, and 6, each 2048MB in size.

  alter database add logfile group 6 size 2048m

  alter system switch logfile

• Switch until we are into log group 4, so we can drop log groups 1, 2, and 3.

  alter database drop logfile group 3

• Retrieve all the log member names for the groups.

  select member from v$logfile;

Task 10: Open database in resetlogs in auxiliary instance

• Open database in resetlogs in auxiliary instance using the following command.

  SQL> alter database open resetlogs;

Task 11: Alter the pluggable database in auxiliary instance

• Alter the pluggable database to read write mode in auxiliary instance.

  SQL> alter pluggable database <PDBNAME3> open read write;

Task 12: Create user and grant permission for dblink

• Create users and grant permission for dblink in auxiliary instance and Source.

  CREATE USER C##PDBCLONEDBA IDENTIFIED BY "XXXXXXX";
  GRANT CREATE SESSION TO C##PDBCLONEDBA CONTAINER=ALL;
  GRANT CREATE PLUGGABLE DATABASE TO C##PDBCLONEDBA CONTAINER=ALL;
  GRANT CREATE DATABASE LINK TO C##PDBCLONEDBA CONTAINER=ALL;
  GRANT SELECT ANY DICTIONARY TO C##PDBCLONEDBA CONTAINER=ALL;
  GRANT CREATE JOB TO C##PDBCLONEDBA CONTAINER=ALL;
  GRANT EXECUTE ON DBMS_SCHEDULER TO C##PDBCLONEDBA CONTAINER=ALL;
  

• Create database link in Source.

  CREATE DATABASE LINK "CDBAUX02" CONNECT TO C##PDBCLONEDBA IDENTIFIED BY "XXXXXXX"
  USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.174)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = CDBAUX02)))';  2
  
  SQL> alter session set global_names=FALSE;
  Session altered.
  
  SQL> select * from dual@CDBAUX02;
    D
    --
    x
  

  

Task 13: Clone the pluggable database PDB03 in source

• Clone the pluggable database PDB03 in Source.

  CREATE PLUGGABLE DATABASE PDB03 FROM PDB03@CDBAUX02 NO REKEY KEYSTORE IDENTIFIED BY "XXXXXXX";

• Change the State of PDBs in a CDB: Example: Assume that the current container is the root. The following statement opens PDBs in the CDB with open mode READ WRITE.

  alter pluggable database PDB03 open read write;

  

Acknowledgments

• Author - Aditya Srivastawa (Principal Cloud Architect )

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Restore a dropped multitenant PDB using auxiliary instance on the same DBCS server

F81982-01

May 2023

Copyright © 2023, Oracle and/or its affiliates.