This discussion is archived
7 Replies Latest reply: Mar 19, 2013 9:42 AM by FreddieEssex RSS

FLASHBACK Database : rman vs sql command differnces

Sivaprasad S Newbie
Currently Being Moderated
I am new to Oracle and want understand the difference and which is the correct way of flashing back the database?


RMAN command vs SQL Command on using FLASHBACK DATABASE
 SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_ONLINE_TEST;  {CODE} Vs 
rman> run{
     flashback database to restore point BEFORE_TEST_320130313;
     sql "alter database open resetlogs"; }
 
Referring this article on http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BGBDCAFA it says we can use both RMAN and SQL command for database flashback.

Questions:

1. Difference between using RMAN and SQL command on database flashback?

2. When to use RMAN or SQL on the  database flashback?

3. Which is the Oracle recommended or best way ?

Edited by: Sivaprasad S on Mar 15, 2013 7:26 PM

Edited by: Sivaprasad S on Mar 15, 2013 7:26 PM

Edited by: Sivaprasad S on Mar 15, 2013 7:27 PM

Edited by: Sivaprasad S on Mar 15, 2013 7:27 PM

Edited by: Sivaprasad S on Mar 15, 2013 7:28 PM

Edited by: Sivaprasad S on Mar 15, 2013 7:29 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 1. Re: FLASHBACK Database : rman vs sql command differnces
    mseberg Guru
    Currently Being Moderated
    Sivaprasad;

    It does not matter. You can use either to flashback to :

    SCN
    TIMESTAMP
    RESTORE POINT

    In the end the effect is the same if you issue the command from RMAN or SQL Plus.

    Neither is more or less correct than the other.

    Best Regards

    mseberg
  • 2. Re: FLASHBACK Database : rman vs sql command differnces
    Sivaprasad S Newbie
    Currently Being Moderated
    When I issue the SQL Plus command of
    SQL> flashback database to restore point BEFORE_TEST_20130313 ;
    flashback database to restore point BEFORE_TEST_20130313
    *
    ERROR at line 1:
    ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
    ORA-38762: redo logs needed for SCN 87612483975 to SCN 87612485856
    ORA-38761: redo log sequence 1 in thread 1, incarnation 3 could not be accessed
    However on running this on RMAN
    RMAN>
    list backup of archivelog scn between 87612483975 and 87612485856
    RMAN> ;
    
    starting full resync of recovery catalog
    full resync complete
    
    List of Backup Sets
    ===================
    
    
    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ --------------------
    5212758 19.75M     SBT_TAPE    00:00:04     13-MAR-2013 16:36:17
            BP Key: 5212765   Status: AVAILABLE  Compressed: NO  Tag: TAG20130313T163611
            Handle: <TestDB_60:809973373:1>.al   Media: 945c30b6:506c6cd2:3a14:0001
    
      List of Archived Logs in backup set 5212758
      Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
      ---- ------- ---------- -------------------- ---------- ---------
      1    1       87612468290 13-MAR-2013 13:28:19 87612484321 13-MAR-2013 16:36:06
      1    2       87612484321 13-MAR-2013 16:36:06 87612484339 13-MAR-2013 16:36:09
    
    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ --------------------
    5212759 896.50M    SBT_TAPE    00:00:17     13-MAR-2013 16:36:30
            BP Key: 5212766   Status: AVAILABLE  Compressed: NO  Tag: TAG20130313T163611
            Handle: <TestDB_59:809973373:1>.al   Media: 945c30b6:506c6cbd:39f5:0001
    
      List of Archived Logs in backup set 5212759
      Thrd Seq     Low SCN    Low Time             Next SCN   Next Time
      ---- ------- ---------- -------------------- ---------- ---------
      1    5       87612363812 12-MAR-2013 14:22:26 87612659040 13-MAR-2013 03:04:08
    
    
    run {
    allocate CHANNEL dev_2  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    allocate CHANNEL dev_3  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    allocate CHANNEL dev_4  TYPE 'SBT_TAPE' format '%U' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8)';
    flashback database to restore point BEFORE_TEST_20130313;
    sql "alter database open resetlogs";
    release channel dev_2;
    release channel dev_3;
    release channel dev_4;
    }
    
    starting media recovery
    
    archived log for thread 1 with sequence 2 is already on disk as file +DG_PERF_FLASH_02/TestDB/archivelog/2013_03_14/thread_1_seq_2.3612.810054205
    archived log for thread 1 with sequence 3 is already on disk as file +DG_PERF_FLASH_02/TestDB/archivelog/2013_03_14/thread_1_seq_3.1588.810054205
    channel dev_2: starting archived log restore to default destination
    channel dev_2: restoring archived log
    archived log thread=1 sequence=1
    channel dev_2: reading from backup piece <TestDB_60:809973373:1>.al
    channel dev_2: piece handle=<TestDB_60:809973373:1>.al tag=TAG20130313T163611
    channel dev_2: restored backup piece 1
    channel dev_2: restore complete, elapsed time: 00:00:46
    channel dev_2: deleting archived log(s)
    archived log file name=+DG_PERF_FLASH_02/TestDB/archivelog/2013_03_15/thread_1_seq_1.2969.810106317 RECID=49 STAMP=810106318
    media recovery complete, elapsed time: 00:00:05
    Finished flashback at 10-MAR-2013 05:32:07
    database reset to incarnation 5215723
    When we issue the SQL Plus command, if there is  need of old database backup of archive log, how does that SQL Plus communicate with rman recovery catalog and retrieve?
  • 3. Re: FLASHBACK Database : rman vs sql command differnces
    FreddieEssex Pro
    Currently Being Moderated
    So when you run your flashback from SQLPlus you get the following error:

    ORA-38761: redo log sequence 1 in thread 1, incarnation 3 could not be accessed

    So this means that sequence 1 has probably been removed from disk and is required.

    When you run the command from RMAN look at the log at what RMAN is doing:

    *archived log for thread 1 with sequence 2 is already on disk as file +DG_PERF_FLASH_02/TestDB/archivelog/2013_03_14/thread_1_seq_2.3612.810054205*
    *archived log for thread 1 with sequence 3 is already on disk as file +DG_PERF_FLASH_02/TestDB/archivelog/2013_03_14/thread_1_seq_3.1588.810054205*

    So sequence 2 and 3 are already on disk as per the message above.

    You then get this message:

    channel dev_2: restoring archived log
    archived log thread=1 sequence=1

    So RMAN knows it needs sequence 1 as well and restores this archive log which it needs for recovery.

    In a nutshell when running it from SQLPlus it won't communicate to RMAN and restore the logfile for you, however using RMAN it will.

    You can restore the archive log manually and then run it from SQLPlus which will then work.

    Edited by: Freddie Essex on 18-Mar-2013 12:46
  • 4. Re: FLASHBACK Database : rman vs sql command differnces
    Sivaprasad S Newbie
    Currently Being Moderated
    So, I understand the for FLASHBACK , RMAN would be optimal choice.
  • 5. Re: FLASHBACK Database : rman vs sql command differnces
    FreddieEssex Pro
    Currently Being Moderated
    I suppose a case could be made for RMAN.

    Bear in mind that you may not have RMAN for some non-prod environments in which case your only option is to flashback from SQLPlus.
  • 6. Re: FLASHBACK Database : rman vs sql command differnces
    user10697869 Newbie
    Currently Being Moderated
    you may not have RMAN for some non-prod environments in which case your only option is to flashback from SQLPlus.

    rman comes by default. if u can do from sqlplus, u shd be able to do from rman. correct me if I am wrong
  • 7. Re: FLASHBACK Database : rman vs sql command differnces
    FreddieEssex Pro
    Currently Being Moderated
    You are indeed correct....it was worded badly on my part. The fact that you are using RMAN or sqlplus should be irrelevant.

    I'll rephrase so the point I was trying to make is more obvious - You may not have setup RMAN backups for some environments like dev or test, so if you removed an archivelog from disk which is required for flashback, and then used RMAN to do your flashback you would be unable to flashback the database as the archivelog is gone forever !!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points