Oracle How to Restore Of Single PDB via RMAN
Rman to Restore Of Single PDB in Multitenant
Aim to restore only ORCL database ONLY via rman !!!!
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO <<<< only this database !!!
4 ORCLD READ WRITE NO
=========
RMAN> restore controlfile from '/home/oracle/RmanBackup/ORCLCDB_T20221230_cf15_p1_t1124819364';
Starting restore at 31-12-2022 02:43:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLCDB/control01.ctl
Finished restore at 31-12-2022 02:43:12
RMAN> alter database mount ;
released channel: ORA_DISK_1
Statement processed
from another session !!!
set linesize 300 pagesize 100
col inst_id for 9999999 heading 'Instance #'
col file_nr for 9999999 heading 'File #'
col file_name for A70 heading 'File name'
col checkpoint_change_nr for 99999999999999 heading 'Checkpoint #'
col checkpoint_change_time for A20 heading 'Checkpoint time'
col last_change_nr for 99999999999999 heading 'Last change #'
col SCNStatus for a15
SELECT
fe.inst_id,
fe.CON_ID, ---- for >12c
fe.fenum file_nr,
fn.fnnam file_name,
TO_NUMBER (fe.fecps) checkpoint_change_nr,
fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus, ---- Recover to max 'scn'
DECODE (
fe.fetsn,
0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
DECODE (BITAND (fe.festa, 18),
0, 'OFFLINE',
2, 'ONLINE',
'RECOVER')
) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 ) OR (fe.fepax = 65535 OR fe.fepax = 0) )
AND fn.fnfno = fe.fenum
AND fe.fefnh = fn.fnnum
AND fe.fedup != 0
AND fn.fntyp = 4
AND fn.fnnam IS NOT NULL
AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;
Instance # CON_ID File # File name Checkpoint # Checkpoint time Last change # SCNSTATUS STATUS
---------- ---------- -------- ---------------------------------------------------------------------- --------------- -------------------- -------------------- --------------- -------
1 1 1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf 4079968 12/30/2022 17:49:19<-Good SYSTEM
1 1 3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 2 5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf 2163739 05/31/2019 15:32:31<-Good SYSOFF
1 2 6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf 2163739 05/31/2019 15:32:31<-Good OFFLINE
1 1 7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 2 8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf 2163739 05/31/2019 15:32:31<-Good OFFLINE
1 3 9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf 4079968 12/30/2022 17:49:19<-Good SYSTEM
1 3 10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 3 12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 3 13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 1 14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 3 15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf 4079968 12/30/2022 17:49:19<-Good ONLINE
1 4 16 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf 4078427 12/30/2022 17:42:42<-Good SYSOFF
1 4 17 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf 4078427 12/30/2022 17:42:42<-Good OFFLINE
1 4 18 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf 4078427 12/30/2022 17:42:42<-Good OFFLINE
1 4 19 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf 4078427 12/30/2022 17:42:42<-Good OFFLINE
16 rows selected.
Find out the scn from above SQL
run { set until scn 4079968;
restore database root ;
restore database "PDB$SEED";
restore database ORCL;
}
Starting restore at 31-12-2022 02:50:50
Starting implicit crosscheck backup at 31-12-2022 02:50:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=414 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 31-12-2022 02:50:51
Starting implicit crosscheck copy at 31-12-2022 02:50:51
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 31-12-2022 02:50:51
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
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 00001 to /u01/app/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:51:33
Starting restore at 31-12-2022 02:51:33
using channel ORA_DISK_1
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 00005 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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 00006 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:52:03
Starting restore at 31-12-2022 02:52:03
using channel ORA_DISK_1
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 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00009 to /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-12-2022 02:53:04
RMAN> recover database ;
Starting recover at 31-12-2022 03:27:16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:27:16
RMAN-07551: data file 16 must be restored or preplugin recovery must be completed
******************************************************************************************************************
How to use Rman to Restore Of Single PDB in Multitenant to Alternate Server (Doc ID 2142675.1)
Skip the PDB's database not required during the recovery stage by using 'Skip forever tablespace >'
*******************************************************************************************************************
RMAN> report schema ;
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLCDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 910 SYSTEM *** /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3 570 SYSAUX *** /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5 270 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6 330 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8 100 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9 410 ORCL:SYSTEM *** /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
10 1200 ORCL:SYSAUX *** /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
12 401 ORCL:USERS *** /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
13 2 ORCL:APEX_1291597703607401 *** /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
14 50 UNDOTBS2 *** /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15 50 ORCL:UNDOTBS2 *** /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
16 0 ORCLD:SYSTEM *** /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
17 0 ORCLD:SYSAUX *** /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf
18 0 ORCLD:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf
19 0 ORCLD:ORCL1_USERS *** /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-31_15-03-14-330-PM.dbf
3 36 ORCL:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf
4 36 ORCLD:TEMP 32767 /u01/app/oracle/oradata/ORCLCDB/orcld/cl_ixantemp012019-05-31_15-03-14-330-PM.dbf
Now skip ORCLD pluggable and datafiles!!!
RMAN> recover database skip forever tablespace ORCLD:SYSTEM,ORCLD:ORCL1_USERS,ORCLD:SYSAUX,ORCLD:UNDOTBS1 ;
Starting recover at 31-12-2022 03:39:38
using channel ORA_DISK_1
Executing: alter database datafile 16 offline drop
Executing: alter database datafile 19 offline drop
Executing: alter database datafile 17 offline drop
Executing: alter database datafile 18 offline drop
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=33
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=34
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc thread=1 sequence=33
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc RECID=6 STAMP=1124854779
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc thread=1 sequence=34
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc RECID=5 STAMP=1124854779
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=35
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc thread=1 sequence=35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc RECID=7 STAMP=1124854781
unable to find archived log
archived log thread=1 sequence=36
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:39:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 36 and starting SCN of 4079968
RMAN>
RMAN> alter database open resetlogs;
Statement processed
Recovey completed ....
SQL> select open_mode,current_scn,checkpoint_change#,archive_change#,controlfile_change# from v$database;
OPEN_MODE CURRENT_SCN CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_CHANGE#
-------------------- ----------- ------------------ --------------- -------------------
READ WRITE 4092115 4079972 0 4092072
set linesize 300
col NAME for a70
col ERROR for a15
select * from (select file#,con_id,tablespace_name,status,error ,recover ,fuzzy ,checkpoint_time checkpoint from v$datafile_header)
natural join (select hxfil file#, fhsta, fhscn, fhrba_seq, fhafs from x$kcvfhall);
FILE# CON_ID TABLESPACE_NAME STATUS ERROR REC FUZ CHECKPOIN FHSTA FHSCN FHRBA_SEQ FHAFS
---------- ---------- ------------------------------ ------- --------------- --- --- --------- ---------- -------------------- ---------- --------------------
1 1 SYSTEM ONLINE NO YES 31-DEC-22 8196 4079972 1 0
3 1 SYSAUX ONLINE NO YES 31-DEC-22 4 4079972 1 0
5 2 SYSTEM ONLINE NO 31-MAY-19 8192 2163739 7 0
6 2 SYSAUX ONLINE NO 31-MAY-19 0 2163739 7 0
7 1 USERS ONLINE NO YES 31-DEC-22 4 4079972 1 0
8 2 UNDOTBS1 ONLINE NO 31-MAY-19 0 2163739 7 0
9 3 SYSTEM ONLINE NO YES 31-DEC-22 8196 4080979 1 0
10 3 SYSAUX ONLINE NO YES 31-DEC-22 4 4080979 1 0
12 3 USERS ONLINE NO YES 31-DEC-22 4 4080979 1 0
13 3 APEX_1291597703607401 ONLINE NO YES 31-DEC-22 4 4080979 1 0
14 1 UNDOTBS2 ONLINE NO YES 31-DEC-22 4 4079972 1 0
15 3 UNDOTBS2 ONLINE NO YES 31-DEC-22 4 4080979 1 0
16 4 OFFLINE FILE NOT FOUND 0 0 0 0
17 4 OFFLINE FILE NOT FOUND 0 0 0 0
18 4 OFFLINE FILE NOT FOUND 0 0 0 0
19 4 OFFLINE FILE NOT FOUND 0 0 0 0
16 rows selected.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
4 ORCLD MOUNTED <<<<<
SQL>
