Re: Really, Oracle? Really?

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_at_gmail.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l