1.
Summary
RMAN Restore with the CommVault Media Manager.
12CR1 : 12.1.0.2
11GR2 : 11.2.0.4 RDBMS
CommVault Agent iDataAgent64 Data Agent AIX for Oracle
PITR Restore Types
|
Chapter |
Restore Level |
Type |
Arcmode |
PDB |
|
2.1) |
DB |
Out-of-place |
O |
N |
|
2.2) |
DB |
Out-of-place |
N |
N |
|
3) |
Archivelogs |
In-Place |
O |
N |
|
4) |
Table |
In-Place |
O |
O |
|
5.1) |
Tablespace |
In-Place |
O |
N |
|
5.2) |
Tablespace |
In-Place |
O |
O |
|
6.1) |
PDB |
In-Place |
O |
O |
|
6.2) |
PDB |
Out-of-Place |
O |
O |
|
7.1) |
CDB |
Out-of-place |
N |
O |
|
7.2) |
CDB |
Out-of-place |
O |
O |
|
8.1) |
Schéma |
Out-of-place |
O |
N |
|
8.2) |
Schéma |
Out-of-place |
O |
O |
|
8.3) |
Schéma |
Out-of-place |
N |
N |
|
8.4) |
Schéma |
Out-of-place |
N |
O |
2. Database PITR – Out of place ( NoCDB )
2.1. DB PITR - Out of place - Archivelog Mode - No CDB
SELECT db_key, dbid, name
FROM rc_database WHERE name = 'DBNAME'
connect target /
connect catalog IRC/<irc_psw>@IRC_PRO
set parallelmediarestore OFF;
run {
startup nomount;
}
set dbid=1938511762 ;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile from autobackup maxdays 5;
}
shutdown immediate ;
startup nomount ;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 5 ; }
alter database mount ;
run {
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
SET UNTIL TIME "TO_DATE('20-05-2021 12:00:00','DD-MM-YYYY HH24:MI:SS')" ;
restore database ;
RECOVER DATABASE ;
# ALTER DATABASE OPEN RESETLOGS ;
}
Don’t forget to create TEMP Tablespace.
CREATE TEMPORARY TABLESPACE
temp_1 tempfile '/<file>/temp_1_02.dbf' size 2048M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 1M ;
alter database default
temporary tablespace temp_1 ;
2.2. DB PITR - Out of place - No Archivelog Mode - No CDB
Get DBID from RMAN Catalog
SELECT db_key, dbid, name FROM rc_database WHERE name = 'DBNAME'
connect catalog IRC/<psw>@IRC_PRO
startup nomount;
}
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile from autobackup maxdays 15 ;
}
startup nomount ;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
SET UNTIL TIME "TO_DATE('16-06-2021 12:00:00','DD-MM-YYYY HH24:MI:SS')" ;
# By default, RMAN does not restore read-only files when you issue the RESTORE DATABASE command.
RESTORE DATABASE CHECK READONLY;
# Suppresses the application of redo logs during recovery. Only incremental backups are applied.
RECOVER DATABASE NOREDO;
# ALTER DATABASE OPEN RESETLOGS ;
Open Resetlogs may fail, if so :
Finished recover at OCT 11 2021 21:47:13
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 10/11/2021 21:47:13
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/data/dbname/system_01.dbf'
è simulate « Incomplete Recovery »
---------- ------- ------------------ ---
217 ONLINE 55762472853 NO
ORA-00279: change 55762472853 generated at 10/02/2021 21:18:16 needed for thread 1
ORA-00289: suggestion : /work/oracle/dbname/noarc/DBNAME1_44334_973023343.arc
ORA-00280: change 55762472853 for thread 1 is in sequence #44334
CANCEL
Database altered.
3. Archivelogs Restore
connect catalog IRC/<mdp>@IRC_PRO
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
}
4. Table PITR in a PDB – Archivelog Mode
and change table name -- In place Restore.
connect catalog IRC/<irc_psw>@IRC_PRO
UNTIL TIME "TO_DATE('08-APR-2021 20:00', 'DD-MON-YYYY HH24:MI')"
AUXILIARY DESTINATION '/WORK_EXP_TMP/oracle/tspitr'
REMAP TABLE 'SCHEMA'.'TRP_INSC_DOC':'TRP_INSC_DOC_210408';
Run script connected as Target CDBNAME.
5. Tablespace PITR – Archivelog Mode
5.1. TS PITR - In place - Archivelog Mode - No CDB
Tablespace = BAL_DATPrerequisite 1
Get Tablespace list from Schema
select distinct tablespace_name from dba_segments where owner = 'BAL'
Prerequisite 2 : prepare init file and check for self-contained TS
_DISK_SECTOR_SIZE_OVERRIDE=TRUE
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
PL/SQL procedure successfully completed.
no rows selected
$ cat
rman_rtrpit_ts_online_cmv_cat_DBNAME.scr
connect target /
connect catalog IRC/<mdp>@IRC_PRO
run {
# 10g+ fully automated TSPITR
recover
tablespace BAL_DAT until time "to_date('Apr 07 2021 15:00:00','MON DD YYYY
HH24:MI:SS')"
auxiliary
destination '/WORK_EXP_TMP/oracle/tspitr'
UNDO
TABLESPACE "UNDO", "XDB" ;
}
Note AIX Problem
ORA-19504: failed to create file "/data/dbname/bal_dat.dbf"
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 22: Invalid argument
RMAN-06956: create datafile failed; retry after removing /data/dbname/bal_dat.dbf from OS
It is necessary to remove the file and run RMAN again !
DBNAME /work/oracle/dbname/diag/rdbms/dbname/DBNAME/trace
$ vi alert_DBNAME.log
DBNAME /work/oracle/dbname/diag/rdbms/dbname/DBNAME/trace $ rm /data/dbname/bal_dat.dbf
Post-Restore
$ sqlplus / as sysdba
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter tablespace BAL_DAT online ;
Tablespace altered.
5.2. TS PITR - In place - Archivelog Mode - CDB
Tablespace = APP_DAT@PDBNAME
Prerequisite 1
Get Tablespace list from Schema
SQL> select distinct tablespace_name from dba_segments where owner =
'GSU' ;
TABLESPACE_NAME
--------------------------------------------------------------------------
APP_DAT
Prerequisite 2 : prepare init file and check for self-contained TS
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 8 14:36:15 2021
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
PL/SQL procedure successfully completed.
no rows selected
SQL>
Run script connected as Target CDBNAME.
$ cat rman_rtrpit_ts_online_cmv_cat_PDBNAME.scr
connect target /
connect catalog IRC/<catalog_psw>@IRC_PRO
run {
recover tablespace PDBNAME:APP_DAT
until time "to_date('Oct 07 2021 17:00:00','MON DD YYYY HH24:MI:SS')"
auxiliary destination '/WORK_EXP_TMP/oracle/tspitr' ;
}
Post-Restore
PDB_NAME : /work/oracle/pdb_name/rman/scr
$ sqlplus system/$psw@PDBNAME
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> alter tablespace APP_DAT ONLINE ;
Tablespace altered.
SQL>
5.3. Manage side effects on Standby
Manage Standby Database
Effect of PITR on
Dataguard Environment (Standby MRP Crashed with ORA-39873) (Doc ID 1591492.1)
Effect of Tablespace point in time recovery in Data guard Environment (Standby MRP Crashed) (Doc ID 2229201.1)
/oracle $ oerr ora 19729
19729, 00000, "File %s is not the initial version of the
plugged in datafile"
// *Cause: The file is not the initial version of the plugged in datafile.
Ex :
Errors in file /work/oracle/dbname/diag/rdbms/dbname/CPR2S/trace/CPR2S_pr00_20775170.trc:
ORA-19729: File 89 is not the initial version of the plugged in
datafile
ORA-01110: data file 89: '/data/prods/gma_dat.dbf'
Backup/Restore Tablespace on Standby DB using disk backup.
On Primary
SQL> select file#,name from v$datafile where ts#=(select ts# from v$tablespace where name='BAL_DAT') ;
FILE# NAME
6 /data/dbname/bal_dat.dbf
$ rman target /
RMAN> backup datafile 6 format '/WORK_EXP_TMP/oracle/tspitr/DBNAME/%U';
RMAN> backup current controlfile for standby format '/WORK_EXP_TMP/oracle/tspitr/DBNAME/%U';
On Standby
ORA-01109: database not open
Database altered.
restore datafile 6;
}
$ sqlplus / as sysdba
no rows selected
Database altered.
6. PDB PITR – Arch Mode
6.1. PDB PITR - In place - Archivelog Mode - CDB
$ cat rman_rtrpit_pdb_online_cmv_cat_AUC.scr
connect target /
connect catalog IRC/<irc_psw>@IRC_PRO
run {
ALTER PLUGGABLE DATABASE AUC CLOSE;
SET UNTIL TIME "TO_DATE('13-APR-2021
14:00:00','DD-MON-YYYY HH24:MI:SS')" ;
RESTORE PLUGGABLE DATABASE AUC ;
RECOVER PLUGGABLE DATABASE AUC auxiliary
destination '/WORK_EXP_TMP/oracle/tspitr' ;
ALTER PLUGGABLE DATABASE AUC OPEN RESETLOGS;
}
Manage Standby Database
Following the restore, the Standby PDB is ahead of the Primary PDB.
Effect of PITR on Dataguard Environment (Standby MRP Crashed with ORA-39873) (Doc ID 1591492.1)
First, we got to find the SCN of the Restore
2021-10-01T15:27:12.953597+02:00
ARC1
(PID:17564090): Archived Log entry 5286 added for T-1.S-5353 ID 0x3f4ebd53
LAD:1
2021-10-01T15:55:10.076633+02:00
(11):Recovery
of pluggable database PDBNAME aborted due to pluggable database open resetlog
marker.
(11):To
continue recovery, restore all data files for this PDB to checkpoint SCN lower than 55743980577,
or timestamp before 10/01/2021 14:30:00, and restart recovery
PR00
(PID:27132332): MRP0: Detected orphaned datafiles!
PR00
(PID:27132332): Recovery will possibly be retried after pluggable database
flashback...
2021-10-01T15:55:10.089025+02:00
Errors
in file /work/oracle/cbd_stdby_name/diag/rdbms/cbd_stdby_name/CDB_STANDBY_NAME/trace/CDB_STANDBY_NAME_pr00_27132332.trc:
ORA-39874:
Pluggable Database PDBNAME recovery halted
ORA-39873:
Restore all data files to a checkpoint SCN lower than 55743980577.
PR00
(PID:27132332): Managed Standby Recovery not using Real Time Apply
Recovery
interrupted!
Recovery
stopped due to failure in applying recovery marker (opcode 17.46).
Datafiles
are recovered to a consistent state at change 55744062157 but controlfile could
be ahead of datafiles.
Stopping
change tracking
2021-10-01T15:55:10.298900+02:00
Errors
in file /work/oracle/cbd_stdby_name/diag/rdbms/cbd_stdby_name/CDB_STANDBY_NAME/trace/CDB_STANDBY_NAME_pr00_27132332.trc:
ORA-39874:
Pluggable Database PDBNAME recovery halted
ORA-39873:
Restore all data files to a checkpoint SCN lower than 55743980577.
2021-10-01T15:55:30.409604+02:00
MRP0
(PID:34406802): Recovery coordinator encountered one or more errors during
automatic flashback on standby
2021-10-01T15:55:30.416962+02:00
Background
Media Recovery process shutdown (CDB_STANDBY_NAME)
Then, restore the PDB on Standby CDB :
connect catalog IRC/<irc_psw>@IRC_PRO
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
}
Restart Apply
$ sqlplus / as sysdba
Connected to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.10.0.0.0
SQL> alter database recover managed standby database disconnect;
Database altered.
6.2. TS PITR - Out of place - Archivelog Mode - CDB
PDB Name = GOS
Restore SPFILE on Recovery Server
connect target /
connect
catalog IRC/<psw>@IRC_PRO
set parallelmediarestore OFF;
run {
startup
nomount;
}
set
dbid=4134306046 ;
run {
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile for db_unique_name PROXT
from autobackup maxdays 5 ;
}
shutdown immediate ;
connect target /
connect
catalog IRC/<psw>@IRC_PRO
set parallelmediarestore OFF;
startup nomount ;
run {
SET UNTIL TIME "TO_DATE('28-SEP-2021 14:00:00','DD-MON-YYYY HH24:MI:SS')" ;
SET
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore
controlfile from autobackup maxdays 15 ; }
alter database mount ;
select
''||pdb_name||':'||tablespace_name||' ' from cdb_tablespaces a , cdb_pdbs b
where a.con_id = b.con_id
and pdb_name
!= 'GOS'
Run Restore not connected to RCAT as we don't want to register a newly created Instance of the Restored Database.
connect target /
SET UNTIL TIME "TO_DATE('28-SEP-2021 14:00:00','DD-MON-YYYY HH24:MI:SS')" ;
skip forever tablespace IRC:SYSTEM ,
IRC:SYSAUX , IRC:TEMP , IRC:IRC_DAT , PDB02:SYSTEM , PDB02:SYSAUX , PDB02:TEMP ,
PDB02:USERS , PDB02:TS02 ;
recover database
skip forever tablespace IRC:SYSTEM ,
IRC:SYSAUX , IRC:TEMP , IRC:IRC_DAT , PDB02:SYSTEM , PDB02:SYSAUX , PDB02:TEMP ,
PDB02:USERS , PDB02:TS02 ;
}
7. CDB PITR – Arch|NoArch Mode
CDB = "CDBNAME"
7.1. CDB PITR - Out of place - No Archivelog Mode - CDB
With Tablespaces Exclusion.
connect target /
connect catalog IRC/<irc_psw>@IRC_PRO
startup nomount;
}
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile from autobackup maxdays 15 ;
}
startup nomount ;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
RESTORE DATABASE SKIP FOREVER TABLESPACE PDBNAME01:TS01_DAT,PDBNAME01:TS02_DAT,PDBNAME01:TS03_DAT
CHECK READONLY;
# RECOVER DATABASE NOREDO;
7.2. CDB PITR - Out of place - Archivelog Mode - CDB
connect catalog IRC/<psw>@IRC_PRO
startup nomount;
}
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile for db_unique_name PDB_NAME
from autobackup maxdays 5 ;
}
connect catalog IRC/<psw>@IRC_PRO
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
connect catalog IRC/<psw>@IRC_PRO
8. Schema PITR
8.1. Schema PITR - Out of place - Archivelog Mode - No CDB
- Get DBID from RMAN Catalog
$ sqlplus IRC/<psw>@IRC_PRO
SELECT db_key, dbid, name FROM rc_database WHERE name = 'DBNAME' ;
Get list then check for Self Contained Tablespaces on Source
DB.
select distinct tablespace_name from dba_segments where owner = 'SCHEM01'
$ sqlplus / as sysdba
execute dbms_tts.transport_set_check('SCHEM01_DAT',TRUE,TRUE);
select * from transport_set_violations ;
Script 1 : restore SPFILE
connect target /
startup nomount;
}
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile for db_unique_name PRDUN from autobackup maxdays 15 ;
}
If necessary, resize spfile memory.
Get TS to excluded to keep only schema related Tablespaces – keeping LOB TS du to following bug :
Bug 26828994
- Table Point In Time Recovery (TPITR) reports ORA-376 when Lob is in Different
Tablespace (Doc ID 26828994.8)
where tablespace_name not in ('SCHEM01_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS' , 'TEMP', 'TEMP1' , 'ADB_DAT' )
and not exists ( select '1' from dba_lobs b where a.tablespace_name = b.tablespace_name )
order by 1
Script 2 : Restore
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
# Bug 26828994 - Table Point In Time Recovery (TPITR) reports ORA-376 when Lob is in Different Tablespace (Doc ID 26828994.8)
TS01_DAT, TS02_DAT, TS03_DAT ;
TS01_DAT, TS02_DAT, TS03_DAT ;
If OK, then export schema.
flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""
8.2. Schema PITR - Out of place - Archivelog Mode - CDB
- Get DBID from RMAN Catalog
$ sqlplus IRC/<psw>@IRC_PRO
SELECT db_key, dbid, name FROM rc_database WHERE name = 'CDBNAME' ;
Get list then check for Self Contained Tablespaces on Source
DB.
select distinct tablespace_name from dba_segments where owner = 'UBI'
$ sqlplus / as sysdba
execute dbms_tts.transport_set_check('UBI_DAT',TRUE,TRUE);
select * from transport_set_violations ;
Script 1 : restore SPFILE
connect target /
connect catalog IRC/<psw>@IRC_PRO
startup nomount;
}
set dbid=1395127278 ;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile for db_unique_name PDB_NAME
from autobackup maxdays 5 ;
}
Get TS list to be excluded :
select ''||pdb_name||':'||tablespace_name||', ' from cdb_tablespaces a , cdb_pdbs b
where a.con_id = b.con_id
and ( pdb_name != 'PDB_NAME'
OR
( pdb_name = 'PDB_NAME' and tablespace_name not in ('UBI_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS', 'TEMP', 'TEMP1' ) )
)
order by 1 ;
Script 2 : restore PDB
connect target /
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
PDB_NAME02:TS01_DAT, PDB_NAME02:TS02_DAT, PDB_NAME02:TS03_DAT, PDB_NAME02:TS04_DAT, PDB_NAME:TS01_DAT, PDB_NAME:TS02_DAT ;
PDB_NAME02:TS01_DAT, PDB_NAME02:TS02_DAT, PDB_NAME02:TS03_DAT, PDB_NAME02:TS04_DAT, PDB_NAME:TS01_DAT, PDB_NAME:TS02_DAT ;
Run Restore.
archived log file name=/arch/pdb_name/PDB_NAME1_4298_1053080302.arc thread=1 sequence=4298
media recovery complete, elapsed time: 00:01:55
Finished recover at OCT 19 2021 18:23:09
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4
Start dedicated Listener to get access to the restored PDB :
SQL> alter system set local_listener = 'PDB_NAME_RF' ;
Then run export :
> flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""
8.3. Schema PITR - Out of place - No Archivelog Mode - No CDB
SCHEMA = "SCHEM01@DBNAME"
$ sqlplus IRC/<psw>@IRC_PRO
SELECT db_key, dbid, name FROM rc_database WHERE name = 'DBNAME' ;
Get Tablespaces List.
union
select distinct tablespace_name from dba_tab_partitions where table_owner = 'SCHEM01'
Script 1 : restore SPFILE
connect target /
startup nomount;
}
set dbid=1683006242 ;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile from autobackup maxdays 15 ;
}
where tablespace_name not in
('SCHEM01_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS' , ',TEMP', 'TEMP1' )
order by 1
Script 2 : restore DB
connect target /
SET UNTIL TIME "TO_DATE('07-10-2021 12:00:00','DD-MM-YYYY HH24:MI:SS')" ;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore controlfile from autobackup maxdays 15 ; }
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
RESTORE DATABASE
skip forever tablespace
TS01_DAT, TS02_DAT, TS03_DAT
CHECK READONLY;
RECOVER DATABASE
skip forever tablespace
TS01_DAT, TS02_DAT, TS03_DAT
NOREDO;
Run Restore and open database :
Database Opening :
SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 11 14:26:01 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
ORA-00279: change 55762472853 generated at 10/02/2021 21:18:16 needed for
thread 1
ORA-00289: suggestion : /work/oracle/dbname/noarc/DBNAME1_44334_973023343.arc
ORA-00280: change 55762472853 for thread 1 is in sequence #44334
CANCEL
Media recovery cancelled.
Database altered.
And Export :
$ expdp system/${psw} schemas=SCHEM01
DIRECTORY=BAT_DP_DIR DUMPFILE=expdp_SCHEM01_RF.dmp LOGFILE=expdp_SCHEM01_RF.log
parallel=2 \
> flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""
8.4. Schema PITR - Out of place - No Archivelog Mode - CDB
Get DBID from RMAN Catalog
SELECT db_key, dbid, name FROM rc_database WHERE name = 'CDBNAME' ;
DB_KEY DBID NAME
---------- ---------- --------
7699790 2991130853 CDBNAME
Get TS list :
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Last Successful login time: Fri Feb 11 2022 10:05:51 +01:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
------------------------------
IBO_01_DAT
IBO_02_DAT
IBO_03_DAT
Script 1 : restore SPFILE
connect target /
startup nomount;
}
set dbid=2991130853 ;
run {
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'sbt_tape' TO '%F';
restore spfile from autobackup maxdays 15 ;
}
Script 2 : Controlfile
connect target /
connect catalog IRC/<catalog_psw>@IRC_PRO
restore controlfile from autobackup maxdays 15 ; }
select ''||pdb_name||':'||tablespace_name||' , ' from cdb_tablespaces a , cdb_pdbs b where a.con_id = b.con_id
and tablespace_name not in ('SCHEM01_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS' , 'TEMP', 'TEMP1' )
and pdb_name != 'PDB_NAME'
order by 1 ;
Script 3 : DB Restore
connect catalog IRC/<catalog_psw>@IRC_PRO
PDBNAME01:TS01_DAT , PDBNAME01:TS02_IDX , PDBNAME01:XDB
;
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 11 15:39:39 2022
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
----------
PDB$SEED READ ONLY
PDBNAME01 MOUNTED
PDB_NAME READ WRITE
SQL> alter system set local_listener = 'PDB_NAME_RF' ;
flashback_time="\"to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')\""
Another Example in case we run into the error "RMAN-07551: data file 10 must be restored or preplugin recovery must be completed"
At Step : extract the TS list to SKIP
for the Restore part :
select ''||pdb_name||':'||tablespace_name||', ' from cdb_tablespaces a , cdb_pdbs b where a.con_id = b.con_id
and tablespace_name not in ('IBO_01_DAT', 'IBO_02_DAT', 'IBO_03_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS' )
and tablespace_name not like '%TEMP%'
and pdb_name = 'PDB_NAME'
order by 1 ;
select ''||pdb_name||':'||tablespace_name||', ' from cdb_tablespaces a , cdb_pdbs b where a.con_id = b.con_id
and tablespace_name not in ('HRC_DAT', 'SYSTEM' , 'SYSAUX', 'UNDO', 'XDB' , 'USERS' )
and tablespace_name not like '%TEMP%'
and pdb_name = 'PDB_NAME'
union
select decode(tablespace_name,'UNDO',''||pdb_name||':"'||tablespace_name||'",',''||pdb_name||':'||tablespace_name||',')
from cdb_tablespaces a , cdb_pdbs b where a.con_id = b.con_id
and tablespace_name not like '%TEMP%'
and pdb_name != 'PDB_NAME'
order by 1 ;
That gives Rman script like this :
