Oracle 18c – Unplug/Plug Pluggable Database (PDB) with RMAN restore & recover
Posted by Mir Sayeed Hassan on March 18th, 2019
Oracle 18c – Unplug/Plug Pluggable Database (PDB) with RMAN restore & recover
The given scenario tested with Oracle 18c(18.3) DB with Preplugin backup, restore & recover, In this case the backup is taken from the PDB before they plugged into a target DB and we are unplugging it form the target DB & plugging into the target DB with RMAN method, therefore I have perform the rman restore & recover operation on the target DB using the pre-plugin backup.
Database Status
SQL> select instance_name,version, status from V$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ ora18c 18.0.0.0.0 OPEN
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------------------------------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
Try to create a user with the default tablespace & create a table with some record init
SQL> alter session set container=pdb1; Session altered.
SQL> create user mir identified by mir default tablespace test; User created.
SQL> create table mir_test1 as select * from user_tables; Table created.
SQL> select count(*) from mir_test1; COUNT(*) ------------ 1500
Connect to the PDB & take a backup of the PDB (pdb1) Database with archivelog by using the RMAN
[oracle@oracle18c_db ~]$ rman target sys/sys@pdb1 Recovery Manager: Release 18.0.0.0.0 - Production on Sat Mar 16 08:55:29 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA18C:PDB1 (DBID=1378676247) RMAN> backup pluggable database pdb1 plus archivelog; Starting backup at 16-MAR-19 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=270 device type=DISK skipping archived logs when connected to a PDB backup cancelled because there are no files to backup Finished backup at 16-MAR-19 Starting backup at 16-MAR-19 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g7wpc2p2_.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g7wpc2oo_.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g7wpc2p3_.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sodqd1_.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g7wpcl0v_.dbf channel ORA_DISK_1: starting piece 1 at 16-MAR-19 channel ORA_DISK_1: finished piece 1 at 16-MAR-19 piece handle=/u01/app/oracle/fast_recovery_area/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/backupset/2019_03_16/o1_mf_nnndf_TAG20190316T085602_g8swm2ww_.bkp tag=TAG20190316T085602 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 16-MAR-19 Starting backup at 16-MAR-19 using channel ORA_DISK_1 skipping archived logs when connected to a PDB backup cancelled because there are no files to backup Finished backup at 16-MAR-19
Now Unplug Pluggable database (PDB1)
Here we are going to unplug the pdb1 from the source database & create the xml file to be restore the unplug database.
[oracle@oracle18c_db ~]$ !sq sqlplus / as sysdba
SQL> alter pluggable database pdb1 close; Pluggable database altered.
SQL> alter pluggable database pdb1 unplug into '/home/oracle/pdb1.xml'; Pluggable database altered.
SQL> drop pluggable database pdb1 keep datafiles; Pluggable database dropped.
Verify the XML File
[oracle@oracle18c_db ~]$ pwd /home/oracle
[oracle@oracle18c_db ~]$ ls pdb1.xml --
SQL> alter session set container=pdb1; ERROR: ORA-65011: Pluggable database PDB1 does not exist.
Now plugin the unplugged PDB
SQL> create pluggable database pdb1 using '/home/oracle/pdb1.xml'; Pluggable database created.
SQL> alter pluggable database pdb1 open; Pluggable database altered.
Verify
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
SQL> alter session set container=pdb1; Session altered.
SQL> select count(*) from mir_test1; COUNT(*) ---------- 1500
Different scenario for unplug pdb
In this case my mistaken someone has removed the datafile by using the (rm command), Now we need to restore & recover by using our preplugin backup
Consider or try to remove the datafile file from the OS Level
[oracle@oracle18c_db datafile]$ cd /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile
[oracle@oracle18c_db datafile]$ ls o1_mf_sysaux_g7wpc2p2_.dbf o1_mf_system_g7wpc2oo_.dbf o1_mf_temp_g8sx02ho_.dbf o1_mf_test_g8sx02hq_.dbf o1_mf_undotbs1_g8sx02hn_.dbf o1_mf_users_g8sx02hp_.dbf o1_mf_sysaux_g8sx02hl_.dbf o1_mf_system_g8sx02h4_.dbf o1_mf_test_g8sodqd1_.dbf o1_mf_undotbs1_g7wpc2p3_.dbf o1_mf_users_g7wpcl0v_.dbf
[oracle@oracle18c_db datafile]$ rm -rf o1_mf_test_g8sx02hq_.dbf
SQL> alter pluggable database pdb1 close; Pluggable database altered.
SQL> alter pluggable database pdb1 open; alter pluggable database pdb1 open * ERROR at line 1: ORA-01157: cannot identify/lock data file 18 - see DBWR trace file ORA-01110: data file 18: '/u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_ test_g8sx02hq_.dbf'
(Here you get the error as the physical file is missing or deleted)
Connect to the source database by login with sys user & try to perform the restore & recover
[oracle@oracle18c_db datafile]$ rman target sys/sys@ora18c Recovery Manager: Release 18.0.0.0.0 - Production on Sat Mar 16 09:11:38 2019 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: ORA18C (DBID=624054314) RMAN> SET PREPLUGIN CONTAINER=pdb1; executing command: SET PREPLUGIN CONTAINER Find the preplugin backup with list
RMAN> list preplugin backup of pluggable database pdb1; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 612.49M DISK 00:00:05 16-MAR-19 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20190316T085602 Piece Name: /u01/app/oracle/fast_recovery_area/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/backupset/2019_03_16/o1_mf_nnndf_TAG20190316T085602_g8swm2ww_.bkp List of Datafiles in backup set 1 Container ID: 3, PDB Name: PDB1 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 14 Full 2970773 16-MAR-19 NO /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g8sx02h4_.dbf 15 Full 2970773 16-MAR-19 NO /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g8sx02hl_.dbf 16 Full 2970773 16-MAR-19 NO /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g8sx02hn_.dbf 17 Full 2970773 16-MAR-19 NO /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g8sx02hp_.dbf 18 Full 2970773 16-MAR-19 NO /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sx02hq_.dbf
RMAN> list preplugin archivelog all; List of Archived Log Copies for database with db_unique_name ORA18C ===================================================================== Key Thrd Seq S Low Time ------- ---- ------- - --------- 1 1 41 A 16-MAR-19 Name: /u01/app/oracle/fast_recovery_area/ORA18C/archivelog/2019_03_16/o1_mf_1_41_g8swjgg2_.arc
Restore PDB with above given preplugin backup
RMAN> restore pluggable database pdb1 from preplugin; Starting restore at 16-MAR-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=41 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g8sx02h4_.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g8sx02hl_.dbf channel ORA_DISK_1: restoring datafile 00016 to /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g8sx02hn_.dbf channel ORA_DISK_1: restoring datafile 00017 to /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g8sx02hp_.dbf channel ORA_DISK_1: restoring datafile 00018 to /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sx02hq_.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/backupset/2019_03_16/o1_mf_nnndf_TAG20190316T085602_g8swm2ww_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/backupset/2019_03_16/o1_mf_nnndf_TAG20190316T085602_g8swm2ww_.bkp tag=TAG20190316T085602 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 16-MAR-19
Restore is completed successfully not try to recover
Recover PDB using preplugin backups.
Here I got an error with some missing archive log, therefore catalog the archivelog files after the last backup & then try
RMAN> recover pluggable database pdb1 from preplugin; Starting recover at 16-MAR-19 using channel ORA_DISK_1 starting media recovery unable to find archived log archived log thread=1 sequence=42 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/16/2019 09:36:12 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 42 and starting SCN of 2970773
Check the archive log location & perform the catalog
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 40 Next log sequence to archive 42 Current log sequence 42
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 12918M
[oracle@oracle18c_db ~]$ cd /u01/app/oracle/fast_recovery_area/ORA18C/archivelog/2019_03_16/
[oracle@oracle18c_db archivelog]$ cd 2019_03_16/ [oracle@oracle18c_db 2019_03_16]$ ls o1_mf_1_41_g8swjgg2_.arc
RMAN> catalog preplugin archivelog '/u01/app/oracle/fast_recovery_area/ORA18C/archivelog/2019_03_16/o1_mf_1_41_g8swjgg2_.arc'; using target database control file instead of recovery catalog cataloged archived log archived log file name=/u01/app/oracle/fast_recovery_area/ORA18C/archivelog/2019_03_16/o1_mf_1_41_g8swjgg2_.arc RECID=2 STAMP=0
RMAN> recover pluggable database pdb1 from preplugin; Starting recover at 17-MAR-19 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 17-MAR-19
Now restore any of the missing datafile added to the pdb since plugin to new source db (CDB), In my case could not find any new datafile added init, therefore we can skip this step
RMAN> restore pluggable database pdb1 skip preplugin; Starting restore at 17-MAR-19 using channel ORA_DISK_1 data file 14 not processed because it is a plugged in data file data file 15 not processed because it is a plugged in data file data file 16 not processed because it is a plugged in data file data file 17 not processed because it is a plugged in data file data file 18 not processed because it is a plugged in data file restore not done; all files read only, offline, excluded, or already restored Finished restore at 17-MAR-19
Finally run the recovery of the PDB, Open the PDB Database & verify
RMAN> recover pluggable database pdb1; Starting recover at 17-MAR-19 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 17-MAR-19
Open the PDB Database
RMAN> alter pluggable database pdb1 open; Statement processed
Verify the database with datafile removed & its data contain init
SQL> alter session set container=pdb1; Session altered.
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
SQL> select count(*) from mir_test1; COUNT(*) ---------- 1500
Verify its tablespace & datafile
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC CON_ID ---------- ------------------------------ --- --- --- --- ---------- 0 SYSTEM YES NO YES 3 1 SYSAUX YES NO YES 3 2 UNDOTBS1 YES NO YES 3 3 TEMP NO NO YES 3 5 USERS YES NO YES 3 6 TEST YES NO YES 3 6 rows selected.
SQL> set linesize 300 SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_system_g8sx02h4_.dbf /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_sysaux_g8sx02hl_.dbf /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_undotbs1_g8sx02hn_.dbf /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_users_g8sx02hp_.dbf /u01/app/oracle/oradata/ORA18C/8357EF6595DF3749E0538378A8C0E2C5/datafile/o1_mf_test_g8sxpxts_.dbf
=================Hence tested & verified in our test env====================