RMAN - unregister not existed database from catalog

Normally when I tried unregister non-existed database in RMAN catalog I received error:

execute dbms_rcvcat.unregisterdatabase(818673443,879389610);
BEGIN dbms_rcvcat.unregisterdatabase(818673443,879389610); END;

*
ERROR at line 1:
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found
ORA-06512: at "RMAN.DBMS_RCV

Probably some informations about backups are still in catalog for this database but without database I can't unregister this datbase.

Solution:

select name,DB_KEY,DBINC_KEY,dbid from rman.rc_database where name=<database_name>;
delete rman.tsatt where DBINC_KEY=<DBINC_KEY for db>;

commit;

execute dbms_rcvcat.unregisterdatabase(<DB_KEY for db>,<DBID for db>);

PL/SQL procedure successfully completed.


 

RMAN - restore process failed

On test server I had to restore database (3TB).
Due to network issue RESTORE (not recovery) process  failed. In normal situation I have to  start restore process from beginning once again but I already have almost 2.5 TB restored. How to use this data?

Database is in mount mode and in view V$DATAFILE_COPY we have information about file_id and datafile name (this information is needfull if OMF is used).

select 'set newname for datafile '||FILE#||' to '''||name||''';' from V$DATAFILE_COPY where name is not null order by file#;


This query generate set newname for all datafiles already restored.

run {

set until time "to_date('2015-01-05 00:11:00','yyyy-mm-dd hh24:mi:ss')";
<generated set newname> 

restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database DELETE ARCHIVELOG MAXSIZE 90G;
}

and if we run this script, rman continue restore process.

If in v$datafile_copy you don't have information for some datafiles, then RMAN not restored then already and for these datafiles you have to created fake name.

Monitoring RMAN backup

Query for EM 11 to monitoring all backups:

SELECT b.database_name                         AS "DB Name",
  t.type_qualifier1                            AS "Version",
  b.host                                       AS "Server Name" ,
  b.target_type                                AS "Type",
  TO_CHAR(b.start_time, 'YYYYMMDD-HH24:MI:SS') AS "Start Time",
  TO_CHAR(b.end_time, 'YYYYMMDD-HH24:MI:SS')   AS "End Time",
  b.input_type "Backup Info",
  b.time_taken_display   AS "Time taken",
  b.output_bytes_display AS "Final Size Bytes",
  status                 AS "Status"
FROM mgmt$ha_backup b,
  mgmt$target t
WHERE b.target_guid = t.target_guid
AND b.start_time    > sysdate -2
ORDER BY type_qualifier1,
  END_TIME ;

and progress restoring or backup from RMAN:

SELECT OPNAME,
  SOFAR                /TOTALWORK*100 PCT,
  TRUNC(TIME_REMAINING /60) MIN_RESTANTES,
  TRUNC(ELAPSED_SECONDS/60) MIN_ATEAGORA
FROM V$SESSION_LONGOPS
WHERE TOTALWORK>0
AND OPNAME LIKE '%RMAN%';

TSPITR using auxiliary destination

Unfortunately, After some time, we noted a logical corruption of data within an application schema  which could not be eliminated by scripts :/

Schema used by the application is only a small part of  data warehouses. Scheme had to be restored to a certain point in time.
After a moment's thought decided to use the new functionality of restore tablespaces using auxiliary destination with automatic instance.

Fortunately application had separated twelve tablespaces , so there was concern that violate the data from other systems.

We need to restore 400GB of 7TB database.

We created a separate directory for the external instance and run command:

run {
    SET AUXILIARY INSTANCE PARAMETER FILE TO '/oracle/app/11.2/dbs/aux_rman_instance.ora';
    recover TABLESPACE APP_DATA1,...,APP_DATA12 auxiliary destination '/oradb/aux';
}

After restoring tablespaces RMAN performs all the steps, which in previous versions you had to do manually. Now, all these steps are performed automatically. Cool, but can cause big problems and instead of saving time, it will bring a lot of work.

First problem:
The default memory settings for the automatic instance is too low and need to set  shared_pool_size = 200M and sga_target=400M. In this way, after about 12 hours restored is stopped and external instance, along with the files was removed after  got the error ORA-04031.

So, once again :)

The second problem, after several hours, again a error. This time has exceeded the number of processes (probably due  to run export process)   and  we change a parameter processes=150.

This time, however, despite the error, the auxiliary instance is not removed and datafiles can be used.
Further steps I done manually, since I could not wait for next 12 hours :)

One more thing. In parameter file for auxiliary instance should be specified db_name. Default db_name is random and thus easier to connect to it and export metadata for datafiles.