Tuesday, June 5, 2012

how to move datafiles when you change db_unique name

I made a typo this week on the db_unique_name while configuring dataguard  and had to correct it. some datafiles where already created under the wrong db_unique_name ...

rman is you friend especially with ASM most of the time there it is your only friend.

so basically it comes down to put the correct DB_UNIQUE_NAME bounce the instance put it in mount ...

and of course make sure that you put the device channel disk in rman correctly in our case

errata :
       this is handled by the db_recovery_file_dest parameter or can be controlled
      with backup as copy ... format '+DATADG1' clause

so here goes :

rman target /

backup as copy datafile 652 ;

after this finishes 
switch datafile to copy ;

this is an example to do it on datafile level ofcourse you can do it on db level as well with

backup as copy database 

you can delete the old files in old asm directory.

Saturday, June 2, 2012

flashback database to a previous oracle version

This due to issues with RAT( probably inconsistent data ) i had to flashback the RAT testing db …

now this db was upgraded to

so i shut the db down and started it up with the home since the flashback was generated when the home was still used

SQL> select  scn , name from v$restore_point;

SCN                     NAME
8446404412              BEFORE_ACTIVATE

so i issued

SQL> flashback database to restore point before_activate;

however it returned 

flashback database to restore point before_activate
ERROR at line 1:
ORA-38792: encountered unknown flashback record from release

so i stopped and mounted the db with home and did the flashback

SQL> flashback database to restore point before_activate;
Flashback complete.

shutdown the db and started it in mount modus with the 10g home

and opened resetlogs ...

this is was recorded in the alertlog

RESETLOGS after incomplete recovery UNTIL CHANGE 8446404413Resetting resetlogs activation ID 2090464176 (0x7c99f3b0)Online log +DATADG1//onlinelog/group_7.2217.784034813: Thread 2 Group 7 was previously clearedOnline log +ARCHDG1//onlinelog/group_7.316.784034813: Thread 2 Group 7 was previously clearedOnline log +DATADG1//onlinelog/group_8.2218.784034815: Thread 2 Group 8 was previously clearedOnline log +ARCHDG1//onlinelog/group_8.317.784034815: Thread 2 Group 8 was previously cleared

funny thing is that current_scn is much higher, i guess this is to avoid clashes… with scn 's that were created across the different flashback and resetlogs operations I did.

SQL> select current_scn from v$database;


I would like to thank the twitter community and especially @yvelikanov and @jarneil @martinberx 

PS jarneil wrote an excellent article about this but seemed to use the binaries to flashback as this didn't work in my case i decided to devote a blog article for this .