Home » RDBMS Server » Backup & Recovery » Flashback Database error (3 Node RAC, 11.2.0.3)
Flashback Database error [message #623332] Mon, 08 September 2014 13:05 Go to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Hi All,

I was trying to perform FLASHBACK DATABASE, but been getting an error.

/home/oracle[DEVEL3]$srvctl stop database -d DEVEL

/home/oracle[DEVEL3]$sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 8 10:28:07 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.9241E+11 bytes
Fixed Size                  2232512 bytes
Variable Size            1.4388E+11 bytes
Database Buffers         4.8318E+10 bytes
Redo Buffers              208666624 bytes
Database mounted.
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DEVEL   MOUNTED

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
          1410673507 02-SEP-14

SQL> set time on timing on
10:35:00 SQL>
10:35:36 SQL>
10:35:46 SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-06 17:55:00', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-06 17:55:00', 'YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1413790251 to SCN End-of-Redo
ORA-38761: redo log sequence 38 in thread 3, incarnation 2 could not be
accessed


Elapsed: 00:00:04.96

10:38:00 SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     2880


11:01:45 SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA_DG1
db_recovery_file_dest_size           big integer 8192G




I have been getting this error for quite a while now. I am not sure why its looking for that redo.
Do I have to unregister that arch log from RMAN? and then try? Any ideas please, I have been struggling a lot on this.

Please advise.

Thank you,
Ricky

[Updated on: Mon, 08 September 2014 13:06]

Report message to a moderator

Re: Flashback Database error [message #623333 is a reply to message #623332] Mon, 08 September 2014 13:09 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You'll need to restore the archive logs for the time to which you want to flash back. The flashback logs can't do the job by themselves.
Re: Flashback Database error [message #623334 is a reply to message #623332] Mon, 08 September 2014 13:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you try using the same from RMAN prompt?

[Updated on: Mon, 08 September 2014 13:16]

Report message to a moderator

Re: Flashback Database error [message #623336 is a reply to message #623334] Mon, 08 September 2014 14:34 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you John and Mahesh.

I have not tried it from RMAN prompt yet, is this the correct syntax from RMAN?

RMAN> FLASHBACK DATABASE TO TIME "TO_DATE('09/06/14 17:55:00','MM/DD/YY HH24:MI:SS')";



And yes, I did restore the arch logs from RMAN and then tried the flashback, and it gives back the same error.

run {
allocate channel t1 type 'SBT_TAPE'
parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' maxopenfiles 4;
allocate channel t2 type 'SBT_TAPE'
parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' maxopenfiles 4;
allocate channel t3 type 'SBT_TAPE'
parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' maxopenfiles 4;
allocate channel t4 type 'SBT_TAPE'
parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' maxopenfiles 4;
restore archivelog from sequence 38 thread 3;
}
.
.
.
.Finished restore at 08-SEP-14
released channel: t1
released channel: t2
released channel: t3
released channel: t4


12:25:28 SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-06 17:55:00', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-06 17:55:00', 'YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1413790251 to SCN End-of-Redo
ORA-38761: redo log sequence 38 in thread 3, incarnation 2 could not be
accessed



It is still not working Sad
Re: Flashback Database error [message #623337 is a reply to message #623336] Mon, 08 September 2014 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
 *Cause: A FLASHBACK DATABASE command did not start.  A redo log needed for
         the recovery part of FLASHBACK DATABASE could not be found or
         accessed.
 *Action: See trace files for details of the problem.
ORA-38762: redo logs needed for SCN %s to SCN %s
 *Cause: The FLASHBACK DATABASE operation failed.  The recovery portion of
         FLASHBACK DATABASE needed redo from the first SCN to the second,
         but one or more of the redo logs in the range could not be read or
         opened.
 *Action: Check the FLASHBACK DATABASE command trace file for a
          description of the inaccessible redo logs, restore access to
          them, and retry the FLASHBACK DATABASE command.
ORA-38761: redo log sequence %s in thread %s, incarnation %s could not be accessed
 *Cause: A redo log needed for the recovery portion of FLASHBACK could not
         be read or opened.  The FLASHBACK operation failed.
 *Action: Restore the log and retry the FLASHBACK command.
Re: Flashback Database error [message #623338 is a reply to message #623337] Mon, 08 September 2014 15:18 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
That redo log, which the FLASHBACK operation is looking for, is quite old, about 1+ week old. I am doing the flashback to some 36 hours back. I am not really sure why it is looking for that.

Is there anyway to unregister that particular arch log from RMAN?
Would that affect the DB in any bad way?

I am kind of stuck here, please help.
Re: Flashback Database error [message #623339 is a reply to message #623338] Mon, 08 September 2014 15:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
In order to maintain complete data integrity, the restoration process must start with a known good & complete snapshot(level 0 backup) of the database
before it can begin to apply REDO log files to roll the database forward.

When was the most recent full backup of this instance taken?
Re: Flashback Database error [message #623340 is a reply to message #623339] Mon, 08 September 2014 15:47 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
The most recent full backup was on Saturday(09-06-2014), at 4 am.
Re: Flashback Database error [message #623341 is a reply to message #623338] Mon, 08 September 2014 16:49 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Ricky_1362002 wrote on Mon, 08 September 2014 21:18
That redo log, which the FLASHBACK operation is looking for, is quite old, about 1+ week old. I am doing the flashback to some 36 hours back. I am not really sure why it is looking for that.

Is there anyway to unregister that particular arch log from RMAN?
Would that affect the DB in any bad way?

I am kind of stuck here, please help.
Flashback needs the older logs because the flashback logging mechanism does not capture every change. There will be at least one block where the closest version it has is that old, so it needs redo to recover it from then up to the time you have requested.
Re: Flashback Database error [message #623343 is a reply to message #623341] Mon, 08 September 2014 17:16 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you John.
I understand that those redo/arch logs are needed to perform "Flashback Database" but is there any other way that the flashback would work without those arch/redo logs?

The reason being, even when I restore the files, the flashback doesn't work. Am I missing something somewhere?

Please advise.
Re: Flashback Database error [message #623491 is a reply to message #623343] Wed, 10 September 2014 10:55 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Quote:

Flashback needs the older logs because the flashback logging mechanism does not capture every change. There will be at least one block where the closest version it has is that old, so it needs redo to recover it from then up to the time you have requested.


So, I disabled flashback on monday, dropped all the restore points, waited for a while, and re-enabled the flashback again, assuming that this time 'flashback database' operation would work without looking for that old redo/arch log. But then again, I get the same error Sad

08:34:31 SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL  RETENTION_TARGET     FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ---------  ----------------     -------------- ------------------------
1415114071           08-SEP-14  2880                 3.7581E+10     1.1621E+10


Elapsed: 00:00:06.07
08:34:53 SQL>
08:34:57 SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-08 18:22:00', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-09-08 18:22:00', 'YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1415205845 to SCN End-of-Redo
ORA-38761: redo log sequence 38 in thread 3, incarnation 2 could not be
accessed


Elapsed: 00:00:02.95



so, the only way to make it work is to restore that arch file and then try the flashback?
Re: Flashback Database error [message #623492 is a reply to message #623491] Wed, 10 September 2014 11:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 08 September 2014 21:42

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
 *Cause: A FLASHBACK DATABASE command did not start.  A redo log needed for
         the recovery part of FLASHBACK DATABASE could not be found or
         accessed.
 *Action: See trace files for details of the problem.
ORA-38762: redo logs needed for SCN %s to SCN %s
 *Cause: The FLASHBACK DATABASE operation failed.  The recovery portion of
         FLASHBACK DATABASE needed redo from the first SCN to the second,
         but one or more of the redo logs in the range could not be read or
         opened.
 *Action: Check the FLASHBACK DATABASE command trace file for a
          description of the inaccessible redo logs, restore access to
          them, and retry the FLASHBACK DATABASE command.
ORA-38761: redo log sequence %s in thread %s, incarnation %s could not be accessed
 *Cause: A redo log needed for the recovery portion of FLASHBACK could not
         be read or opened.  The FLASHBACK operation failed.
 *Action: Restore the log and retry the FLASHBACK command.


Last line: *Action: Restore the log and retry the FLASHBACK command.

[Updated on: Wed, 10 September 2014 11:27]

Report message to a moderator

Re: Flashback Database error [message #624445 is a reply to message #623492] Mon, 22 September 2014 16:16 Go to previous messageGo to next message
Ricky_1362002
Messages: 111
Registered: February 2009
Senior Member
Thank you everyone for your help.

Well, the problem was due to the fact that the resetlogs# in the v$archived_log and incarnations was not matching.

ReCreated the control file, enabled flashback, and it started working.

Thanks again.

Regards,
Ricky
Re: Flashback Database error [message #624454 is a reply to message #624445] Tue, 23 September 2014 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Re: Flashback Database error [message #624466 is a reply to message #624445] Tue, 23 September 2014 01:56 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
According to the docs, you violated one of the pre-requisites for database flashback:Quote:
You are not trying to use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. If the database control file is restored from backup or re-created, then all accumulated flashback log information is discarded.
so do not assume that is repeatable behaviour. None-the-less, I hope the exercise was valuable.
Previous Topic: Recover dropped table from rman backup
Next Topic: rman restore to new server / different filesystem
Goto Forum:
  


Current Time: Thu Mar 28 14:22:58 CDT 2024