Recover - Dropped Pluggable database (PDB2) with an available RMAN backup

--Environment looks as below:

 [oracle@srvdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 9 18:57:10 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0 

SQL> select name,cdb from v$database;

NAME      CDB

--------- ---

ORCL      YES 

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         5 PDB2                           READ WRITE NO

--Will drop the pluggable database PDB2.

SQL> alter pluggable database PDB2 close;

Pluggable database altered.

SQL> drop pluggable database PDB2 including datafiles;

Pluggable database dropped.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

--The alert.log shows the drop completion:

[oracle@srvdb01 trace]$ tail -20f alert_orcl.log

handle '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1682927172-20231209-01'

2023-12-09T18:59:17.941047+03:00

alter pluggable database PDB2 close

2023-12-09T18:59:18.020014+03:00

PDB2(5):JIT: pid 22876 requesting stop

PDB2(5):Buffer Cache flush started: 5

PDB2(5):Buffer Cache flush finished: 5

Pluggable database PDB2 closed

Completed: alter pluggable database PDB2 close

2023-12-09T18:59:25.300292+03:00

drop pluggable database PDB2 including datafiles

2023-12-09T18:59:25.532513+03:00

Deleted file /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf

Deleted file /u01/app/oracle/oradata/ORCL/pdb2/temp012023-12-09_18-14-05-608-PM.dbf

Deleted file /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf

Deleted file /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf

Deleted file /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf

Completed: drop pluggable database PDB2 including datafiles

--Will use RMAN RECOVER command to recover this dropped pluggable database PDB2.

RMAN will create an auxiliary database to the specified point in time, will unplug the PDB from this auxiliary and plug the PDB back into the CDB target.

[oracle@srvdb01 aux]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Dec 9 19:06:26 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1682927172)

RMAN> set echo on

RMAN> recover pluggable database PDB2 until time "to_date('09-DEC-2023 18:59:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/u01/app/oracle/aux/';

recover pluggable database PDB2 until time "to_date('09-DEC-2023 18:59:00','DD-MON-YYYY HH24:MI:SS')" auxiliary destination '/u01/app/oracle/aux/';

Starting recover at 09-DEC-23

using target database control file instead of recovery catalog

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=37 device type=DISK

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='Cgml'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=Cgml_pitr_PDB2_ORCL

compatible=19.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=692M

processes=200

db_create_file_dest=/u01/app/oracle/aux/

log_archive_dest_1='location=/u01/app/oracle/aux/'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     725611152 bytes

Fixed Size                     9138832 bytes

Variable Size                201326592 bytes

Database Buffers             507510784 bytes

Redo Buffers                   7634944 bytes

Automatic instance created

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('09-DEC-2023 18:59:00','DD-MON-YYYY HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

 

# mount the controlfile

sql clone 'alter database mount clone database';

}

executing Memory Script

executing command: SET until clause

 Starting restore at 09-DEC-23

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1682927172-20231209-01

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1682927172-20231209-01 tag=TAG20231209T185648

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/aux/ORCL/controlfile/o1_mf_lq94556l_.ctl

Finished restore at 09-DEC-23

 

sql statement: alter database mount clone database

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('09-DEC-2023 18:59:00','DD-MON-YYYY HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  7 to new;

set newname for datafile  17 to

 "/u01/app/oracle/oradata/ORCL/pdb2/system01.dbf";

set newname for datafile  18 to

 "/u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf";

set newname for datafile  19 to

 "/u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf";

set newname for datafile  20 to

 "/u01/app/oracle/oradata/ORCL/pdb2/users01.dbf";

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3, 7, 17, 18, 19, 20;

 

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

Starting restore at 09-DEC-23

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_052dkbs3_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_052dkbs3_1_1 tag=UPGRADE_DB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00018 to /u01/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00019 to /u01/app/oracle/oradata/ORCL/pdb2/undotbs01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_072dkbt7_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_072dkbt7_1_1 tag=UPGRADE_DB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/aux/ORCL/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_042dkbs3_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_042dkbs3_1_1 tag=UPGRADE_DB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/ORCL/pdb2/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00020 to /u01/app/oracle/oradata/ORCL/pdb2/users01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/cdb_0a2dkbu6_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/cdb_0a2dkbu6_1_1 tag=UPGRADE_DB

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 09-DEC-23

 

datafile 1 switched to datafile copy

input datafile copy RECID=8 STAMP=1155150610 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_system_lq947xhd_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=1155150610 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_undotbs1_lq945f6j_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=1155150610 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_sysaux_lq945f2p_.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=11 STAMP=1155150610 file name=/u01/app/oracle/aux/ORCL/datafile/o1_mf_users_lq947xjt_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('09-DEC-2023 18:59:00','DD-MON-YYYY HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  7 online";

sql clone 'PDB2' "alter database datafile

 17 online";

sql clone 'PDB2' "alter database datafile

 18 online";

sql clone 'PDB2' "alter database datafile

 19 online";

sql clone 'PDB2' "alter database datafile

 20 online";

#recover pdb

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX", "USERS" pluggable database

 'PDB2'  delete archivelog;

#open in read write mode

sql clone 'alter database open resetlogs';

#unplug dropped pdb into temp file

sql clone "alter pluggable database PDB2 unplug into ''

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_2_Cgml.xml''";

#create pdb using temp file of recovered pdb

sql "create pluggable database PDB2 using ''

/u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_2_Cgml.xml'' nocopy tempfile reuse";

alter pluggable database PDB2 open;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  1 online

 

sql statement: alter database datafile  4 online

 

sql statement: alter database datafile  3 online

 

sql statement: alter database datafile  7 online

 

sql statement: alter database datafile  17 online

 

sql statement: alter database datafile  18 online

 

sql statement: alter database datafile  19 online

 

sql statement: alter database datafile  20 online

 

Starting recover at 09-DEC-23

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/archive/1_8_1155146439.dbf

archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/archive/1_9_1155146439.dbf

archived log file name=/u01/app/oracle/archive/1_8_1155146439.dbf thread=1 sequence=8

archived log file name=/u01/app/oracle/archive/1_9_1155146439.dbf thread=1 sequence=9

media recovery complete, elapsed time: 00:00:00

Finished recover at 09-DEC-23

 

sql statement: alter database open resetlogs

 

sql statement: alter pluggable database PDB2 unplug into ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_2_Cgml.xml''   <== the PDB is unplugged

 

sql statement: create pluggable database PDB2 using ''/u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_2_Cgml.xml'' nocopy tempfile reuse  <== the PDB is plugged in

 

Statement processed

 

Removing automatic instance

shutting down automatic instance

Oracle instance shut down

Automatic instance removed

auxiliary instance file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/_rm_pdb_pitr_2_Cgml.xml deleted

auxiliary instance file /u01/app/oracle/aux/ORCL/datafile/o1_mf_sysaux_lq945f2p_.dbf deleted

auxiliary instance file /u01/app/oracle/aux/ORCL/controlfile/o1_mf_lq94556l_.ctl deleted

Finished recover at 09-DEC-23



-- Pluggable database PDB2 recovered and plugged into target CDB:

[oracle@srvdb01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 9 20:03:41 2023

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> select name,cdb from v$database;

NAME      CDB

--------- ---

ORCL      YES

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

         4 PDB2                           READ WRITE NO

 

 

 

 

 

 


Comments

Post a Comment

Popular posts from this blog

Step-by-Step Guide to Configure Oracle GoldenGate Plug-in for Enterprise Manager 13cR5

Image
Oracle GoldenGate Enterprise Manager Plug-in helps monitor and manage Goldengate processes through Oracle Enterprise Manager Cloud Control. Environment details: Host: srvdb01.localdomain Operating System: OEL 8.8 OEM Version: 13.5.0.0 OEM Home: /u01/app/oracle/em13c/oraclehome Database Version: 19.23.0.0.0 Database Home: /u01/app/oracle/product/19.0.0/dbhome_1 Goldengate Version: 21.3.0.0.0 Goldengate Home: /u01/app/oracle/product/21.3.0/ogg_1 Goldengate Agent Home: /u01/app/oracle/oggmon_agent122   All components, including Oracle Enterprise Manager 13.5, Oracle Database 19c, Oracle Goldengate 21.3 and Goldengate Monitor Agent 12.2 are configured on the same host srvdb01.localdomain running on OEL 8.8. We will set up the Oracle GoldenGate Plug-in for Enterprise Manager 13cR5. 1. Download the Oracle Goldengate Plug-in for OEM 13.5 from edelivery.oracle.com. https://www.oracle.com/middleware/technologies/goldengate-downloads.html 2. Copy the Oracle Goldengate Plug-in to the OMS ...
Read more

Step-by-Step Guide to Configure Oracle GoldenGate Veridata 23c

Image
Oracle Goldengate Veridata is a fast tool that compares data between source and target databases, report differences and fixes them. Oracle GoldenGate Veridata 23c supports only MySQL Server as the repository database for storing metadata. The environment consists of: Host: dbsrv01.localdomain : Veridata Agent 23c, Source Database (19c) running on OEL 8.8, extracting  HR.REGIONS table using GoldenGate 21.3 Host: dbsrv02.localdomain : Veridata Agent 23c, Target Database (19c) running on OEL 8.8, replicating HR.REGIONS table using GoldenGate 21.3 Host: dbsrv03.localdomain : Veridata Server 23c, running on OEL 8.8 We will set up the Veridata Server and Veridata Agent 23c for data comparison and synchronization. 1. Download the Oracle Goldengate Veridata installation file from Oracle Software Delivery Cloud. 2.   Copy the Oracle Goldengate Veridata installer to the target server and unzip under /tmp. [oracle@dbsrv03 ~]$ cd /tmp [or...
Read more

How to Reposition Extract, Data Pump and Replicat in GoldenGate During Trail File Corruption

In this blog, we will walk you through the steps to reposition the Extract, Data Pump and Replicat processes in the event of Extract trail file corruption.   The environment consists of: Host: dbsrv01.localdomain: Source Database (19c) named "master" running on OEL 8.8, Extracting HR.REGIONS table using GoldenGate 21.3. Host: dbsrv02.localdomain: Target Database (19c) named "orcl" running on OEL 8.8, Replicating HR.REGIONS table using GoldenGate 21.3. Source (master) Target (orcl) 1. We have an integrated Extract running on the source database.   [oracle@dbsrv01 ~]$ ggsci   Oracle GoldenGate Command Interpreter for Oracle Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047_FBO Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 29 2021 03:59:23 Operating system character set identified as UTF-8.   Copyright (C) 1995, 2021, Oracle and/or its...
Read more