FAQ
Hi All,
i have a question about how to deal with the tablespace that had
turned off flashback after flashback a database.
for example:
i turned off the flashback of MYTS and made it offline.then make a
database flashback
flashback database to scn 21248000;
flashback database to scn 21248000
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get
error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01110: data file 6: '/u01/oradata/noasm/myts01.dbf'

i don't want to drop MYTS. so i try to make a restore and recover.
run {
2> set until scn=21248000;
3> restore datafile 6;
4> recover datafile 6;
5> }

executing command: SET until clause

Starting restore at 2011-09-15 10:30:47
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00006 to /u01/oradata/noasm/myts01.dbf
channel ORA_DISK_1: reading from backup piece
/u01/fra/NOASM/backupset/2011_09_14/o1_mf_nnndf_TAG20110914T212055_771bkqyv_.bkp
channel ORA_DISK_1: restored backup piece 1
piece
handle=/u01/fra/NOASM/backupset/2011_09_14/o1_mf_nnndf_TAG20110914T212055_771bkqyv_.bkp
tag=TAG20110914T212055
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2011-09-15 10:30:55

Starting recover at 2011-09-15 10:30:55
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2011 10:30:56
RMAN-06067: RECOVER DATABASE required with a backup or created control file

the question: how to make the MYTS recover to the same scn of flashback
and open the database?

gachoyi

Search Discussions

  • Kamran Agayev (ICT/SNO) at Sep 16, 2011 at 5:09 am
    Hi

    You need to restore it from backup, make it online and recover from SQL*Plus using "RECOVER DATABASE USING BACKUP CONTROLFILE" command. Check the following code:



    SQL> select name, flashback_on from v$tablespace;

    NAME FLA
    ------------------------------ ---
    SYSTEM YES
    UNDOTBS1 YES
    SYSAUX YES
    USERS NO
    TEMP YES


    RMAN> backup database plus archivelog;

    SQL> select current_scn from v$database;

    CURRENT_SCN
    -----------
    534414

    SQL> shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 599785472 bytes
    Fixed Size 2022600 bytes
    Variable Size 171967288 bytes
    Database Buffers 419430400 bytes
    Redo Buffers 6365184 bytes
    Database mounted.
    SQL> alter database datafile '/home/oracle/oradata/newdb/users01.dbf' offline;

    Database altered.

    SQL> flashback database to scn 534414;
    flashback database to scn 534414
    *
    ERROR at line 1:
    ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
    below
    ORA-01245: offline file 4 will be lost if RESETLOGS is done
    ORA-01110: data file 4: '/home/oracle/oradata/newdb/users01.dbf'

    SQL>

    RMAN> run
    2> {
    3> set until scn = 534414;
    4> restore datafile 4;
    5> recover datafile 4;
    6> }

    executing command: SET until clause
    using target database control file instead of recovery catalog

    Starting restore at 16-SEP-11
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=158 devtype=DISK

    channel ORA_DISK_1: starting datafile backupset restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00004 to /home/oracle/oradata/newdb/users01.dbf
    channel ORA_DISK_1: reading from backup piece /home/oracle/flash_recovery_area/NEWDB/backupset/2011_09_16/o1_mf_nnndf_TAG20110916T100120_775p113q_.bkp
    channel ORA_DISK_1: restored backup piece 1
    piece handle=/home/oracle/flash_recovery_area/NEWDB/backupset/2011_09_16/o1_mf_nnndf_TAG20110916T100120_775p113q_.bkp tag=TAG20110916T100120
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
    Finished restore at 16-SEP-11

    Starting recover at 16-SEP-11
    using channel ORA_DISK_1

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 09/16/2011 10:04:50
    RMAN-06067: RECOVER DATABASE required with a backup or created control file

    RMAN> exit


    SQL> alter database datafile '/home/oracle/oradata/newdb/users01.dbf' online;

    Database altered.

    SQL> recover database using backup controlfile;
    ORA-00279: change 534384 generated at 09/16/2011 10:01:21 needed for thread 1
    ORA-00289: suggestion :
    /home/oracle/flash_recovery_area/NEWDB/archivelog/2011_09_16/o1_mf_1_3_%u_.arc
    ORA-00280: change 534384 for thread 1 is in sequence #3


    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    AUTO
    ORA-00279: change 534400 generated at 09/16/2011 10:01:58 needed for thread 1
    ORA-00289: suggestion :
    /home/oracle/flash_recovery_area/NEWDB/archivelog/2011_09_16/o1_mf_1_4_%u_.arc
    ORA-00280: change 534400 for thread 1 is in sequence #4
    ORA-00278: log file '/home/oracle/oradata/newdb/redo02.log' no longer needed
    for this recovery


    Log applied.
    Media recovery complete.
    SQL> alter database open resetlogs;

    Database altered.

    SQL>



    Sincerely

    Kamran Agayev A.
    Oracle ACE, OCP
    http://kamranagayev.wordpress.com



    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of ganchengyu
    Sent: Friday, 16 September 2011 8:01 AM
    To: oracle-l@freelists.org
    Subject: how to deal with the tablespace without flashback data

    Hi All,
    i have a question about how to deal with the tablespace that had
    turned off flashback after flashback a database.
    for example:
    i turned off the flashback of MYTS and made it offline.then make a
    database flashback
    flashback database to scn 21248000;
    flashback database to scn 21248000
    *
    ERROR at line 1:
    ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get
    error below
    ORA-01245: offline file 6 will be lost if RESETLOGS is done
    ORA-01110: data file 6: '/u01/oradata/noasm/myts01.dbf'

    i don't want to drop MYTS. so i try to make a restore and recover.
    run {
    2> set until scn=21248000;
    3> restore datafile 6;
    4> recover datafile 6;
    5> }

    executing command: SET until clause

    Starting restore at 2011-09-15 10:30:47
    using channel ORA_DISK_1

    channel ORA_DISK_1: starting datafile backupset restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    restoring datafile 00006 to /u01/oradata/noasm/myts01.dbf
    channel ORA_DISK_1: reading from backup piece
    /u01/fra/NOASM/backupset/2011_09_14/o1_mf_nnndf_TAG20110914T212055_771bkqyv_.bkp
    channel ORA_DISK_1: restored backup piece 1
    piece
    handle=/u01/fra/NOASM/backupset/2011_09_14/o1_mf_nnndf_TAG20110914T212055_771bkqyv_.bkp
    tag=TAG20110914T212055
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
    Finished restore at 2011-09-15 10:30:55

    Starting recover at 2011-09-15 10:30:55
    using channel ORA_DISK_1

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 09/15/2011 10:30:56
    RMAN-06067: RECOVER DATABASE required with a backup or created control file

    the question: how to make the MYTS recover to the same scn of flashback
    and open the database?

    gachoyi
    --



    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 16, '11 at 3:03a
activeSep 16, '11 at 5:09a
posts2
users2
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase