RE: how to deal with the tablespace without flashback data

From: Kamran Agayev (ICT/SNO) <"Kamran>
Date: Fri, 16 Sep 2011 05:08:00 +0000
Message-ID: <557040DAAF56EB46B534A8C9C179F3CF1040BA_at_EXMAIN01.azercell.com>



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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ganchengyu Sent: Friday, 16 September 2011 8:01 AM
To: oracle-l_at_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
Received on Fri Sep 16 2011 - 00:08:00 CDT

Original text of this message