INTERESTING. I'll read over those blog posts as I haven't done a partial
restore/recovery.
Thanks,
Chris
On Mon, Aug 22, 2016 at 4:41 PM, Jure Bratina <jure.bratina@xxxxxxxxx>
wrote:
Hi Chris,
Instead of (partially) duplicating the database, maybe an option could be
to restore/recover the database to an auxiliary instance using "recover
database skip forever tablespace <list of tablespaces to be skipped>".
Ivica Arsov has recently written a nice blog post about that:
https://iarsov.com/oracle/performing-partial-restore-and-recovery/ ;,
another nice article is on the Pythian's website:
https://www.pythian.com/blog/how-to-recover-a-subset-of-an-
oracle-database/
This technique should probably work even if the skipped tablespaces
contain materialized views. I quickly checked it on my test database and
while the "duplicate database to dup skip tablespace ts_mv;" command fails
with:
Checking that duplicated tablespaces are self-contained
The following materialized objects were found in skipped tablespaces
Materialized table MV on tablespace TS_MV
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/22/2016 22:43:35
RMAN-05501: aborting duplication of target database
RMAN-05589: Materialized objects in skipped tablespaces prevent duplication
the manual restore/recover procedure, which takes datafiles offline by
means of the recover command below, worked:
RMAN> run { set until scn xxxx; recover database skip forever tablespace
TS_MV; }
RMAN> alter database open resetlogs;
Statement processed
The materialized view "MV" which is defined on table T1 is of course not
accessible, since the TS_MV tablespace's datafile is offline, but segments
in other tablespaces can be queried normally:
SQL> select segment_name, tablespace_name from dba_segments where owner =
'U1';
SEGMENT_NAME TABLESPACE_NAME
--------------- -------------------------
T1 TS_ORDINARY
MV TS_MV
SQL> select tablespace_name, file_name, online_status from dba_data_files
where tablespace_name in ('TS_ORDINARY','TS_MV');
TABLESPACE_NAME FILE_NAME
ONLINE_
------------------------- ---------------------------------------------
-------
TS_MV /u01/app/oracle/oradata/nonCDB/mv01.dbf
OFFLINE
TS_ORDINARY /u01/app/oracle/oradata/dup/ordinary01.dbf
ONLINE
SQL> select count(*) from u1.T1;
COUNT(*)
----------
90957
SQL> select count(*) from u1.MV;
select count(*) from u1.MV
*
ERROR at line 1:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/nonCDB/mv01.dbf'
Regards,
Jure Bratina
