RMAN repository was not used. The new auxiliary instance with small sga and other related parameters was created for that purpose. One of the possible steps of restoration presented below.
1. We have to restore datafiles from backup. It's possible to use existing standby controlfile, if it "knows" about interesting backup's details. If not, older standby controlfile has to be restored from backup, for example:
RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from 'c-2517399282-20170801-00';
or
RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from autobackup;
2. Then mount auxiliary instance with recovered controlfile and restore only interested datafiles in the similar way like this rman script does (because we have cluster database, we have to restore ALL undo tablespaces) :
run {
allocate channel ch00 device type sbt_tape ;
allocate channel ch01 device type disk ;
# set recover database condition
set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;
# set newname for interested datafiles
set newname for datafile 1 to '/rab/oradata/aaa_temp/system.282.870241429' ;
set newname for datafile 8 to '/rab/oradata/aaa_temp/sysaux.268.870241429' ;
set newname for datafile 9 to '/rab/oradata/aaa_temp/undotbs1.283.870241429' ;
set newname for datafile 10 to '/rab/aaa_temp/undotbs2.270.870241429' ;
set newname for datafile 11 to '/rab/aaa_temp/undotbs3.285.870241429' ;
set newname for datafile 18 to '/rab/aaa_temp/undotbs4.284.870241429' ;
set newname for datafile 19 to '/rab/aaa_temp/userdata.ora' ;
restore datafile 1,8,9,10,11,18 ;
set archivelog destination to '/temporary_stage' ;
restore archivelog until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;
# next statement will not run because controlfile is a standby control file
# recover database skip forever tablespace
- ;
release channel ch00 ;
release channel ch01 ;
}
3. At this point we need backup controlfile from primary db, or we have to create it from scratch. Because primary database was unavailable, we had to, using sql statement 'alter database backup controlfile to trace as '...' ;' create sql pattern for creating new controlfile for recovered datafiles with resetlogs section. It's also possible to modify quantity and size of redo log files and other parameters influencing to final size of new controlfile. Next is example of new controlfile creation script :
shutdown immediate
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "AAA" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 8
MAXLOGMEMBERS 1
MAXDATAFILES 10
MAXINSTANCES 4
MAXLOGHISTORY 100
LOGFILE
GROUP 1 (
'/rab/oradata/aaa_temp/group_3.2964.914404769'
) SIZE 1024M,
GROUP 2 (
'/rab/oradata/aaa_temp/group_6.2994.892222399'
) SIZE 1024M
DATAFILE
'/rab/oradata/aaa_temp/system.282.870241429',
'/rab/oradata/aaa_temp/sysaux.268.870241429',
'/rab/oradata/aaa_temp/undotbs1.283.870241429',
'/rab/aaa_temp/undotbs2.270.870241429',
'/rab/aaa_temp/undotbs3.285.870241429',
'/rab/aaa_temp/undotbs4.284.870241429',
'/rab/aaa_temp/userdata.ora'
CHARACTER SET RU8PC866
;ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 3 (
'/rab/oradata/aaa_temp/group_14.757.914404773'
) SIZE 1024M REUSE,
GROUP 4 (
'/rab/oradata/aaa_temp/group_15.25922.914404775'
) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 3
GROUP 5 (
'/rab/oradata/aaa_temp/group_28.25921.914404779'
) SIZE 1024M REUSE,
GROUP 6 (
'/rab/oradata/aaa_temp/group_30.4341.914404783'
) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 4
GROUP 7 (
'/rab/oradata/aaa_temp/group_35.4226.914404787'
) SIZE 1024M REUSE,
GROUP 8 (
'/rab/oradata/aaa_temp/group_36.642.914404791'
) SIZE 1024M REUSE ;
4. Catalog archivelog files and recover part of database with the similar rman script :
run {
allocate channel ch00 device type sbt_tape ;
allocate channel ch01 device type disk ;
set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;
catalog start with 'temporary_stage' noprompt ;
recover database ;
release channel ch00 ;
release channel ch01 ;
}
5. Now you can open database with resetlogs option and have a lot of fun :-).
P.S. There will be datafiles with 'MISSING' word in their names. If you don't need dictionary data linked with objects in these missing files (for example, AWR or ASH data), remove them with help of the following dynamic sql :
> spool drop_missing.sql
> select distinct 'drop tablespace '|| b.name||' including contents and datafiles ;' from v$datafile a,v$tablespace b where a.ts#=b.ts# and a.name like '%MISSING%' ;
> spool off
> start missing.sql
P.P.S. Accessibility of primary database controlfile gives more opportunities to achieve the same results: a ) rman command 'recover database skip forever tablespace' ; b) sql command 'alter database datafile n offline for drop' etc.
P.P.P.S. Improtant thing mentioned before - if you have cluster database, for partial database restoration you have to restore all undo tablespaces, otherwise ora-00600 and other 'unexplained', 'wierd' errors will occur.
