poniedziałek, 25 czerwca 2012

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.

Brak komentarzy:

Prześlij komentarz