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


 CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+DATADG1'
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 /


run
{
backup as copy datafile 652 ;
....
}


after this finishes 
     
run
{
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 11.2.0.3

so i shut the db down and started it up with the 10.2.0.3 home since the flashback was generated when the  10.2.0.3 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 11.0.0.0.0


so i stopped and mounted the db with 11.2.0.3 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;

CURRENT_SCN
---------------------
 8447540415

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 10.2.0.3 binaries to flashback as this didn't work in my case i decided to devote a blog article for this .