Wednesday, January 8, 2014

ORA-38754: FLASHBACK DATABASE not started & ORA-38761

Yesterday I suddenly faced ORA-38761 and ORA-38754 errors.
I checked all logs and backups which were available, but why I could not flashback db.

Let`s simulate.

-- First I will create new user and will create table under new user. This is for checking purpose after flashback.
SQL> create user ulfet identified by ulfet;

User created.

SQL> grant dba to ulfet;

Grant succeeded.

SQL> conn ulfet/ulfet
Connected.

-- Note date. If you desire you may note SCN
SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.13.23.721393 AM +04:00

SQL>


SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
09-JAN-14 09.15.10.917401 AM +04:00

SQL>



-- Now create restore point for flashback db, but you may flashaback db using timestamp or scn too.
SQL> create restore point first_point_09102014_0917;

Restore point created.

-- Now drop table
SQL> drop table test;

Table dropped.



SQL> conn /as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> flashback database to restore point first_point_09102014_0917;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

--After flashback db we have to open db with resetlogs option.

-- Now check table and data

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> 



-- Take backup of db and archivelog all and delete input

RMAN> backup incremental level 0 device type disk tag 'FULL_BACKUP_0' database;

Starting backup at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DB11GR2/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB11GR2/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DB11GR2/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB11GR2/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB11GR2/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_nnnd0_FULL_BACKUP_0_9dwdltvh_.bkp tag=FULL_BACKUP_0 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836385904_9dwdojxc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14


RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=3 STAMP=836385564
input archived log thread=1 sequence=3 RECID=4 STAMP=836385564
input archived log thread=1 sequence=4 RECID=2 STAMP=836385562
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtrhj_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwdbtld_.arc RECID=3 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwdbwp4_.arc RECID=4 STAMP=836385564
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwcdmhp_.arc RECID=1 STAMP=836384595
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwdbtjj_.arc RECID=2 STAMP=836385562
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=836386072
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_1_9dwdtr7r_.arc RECID=5 STAMP=836386072
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386076_9dwdtww2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 



-- Now make dml and ddl statement 

SQL> conn ulfet/ulfet
Connected.

SQL> select * from test;

ID
----------
1

SQL> update test set id=111 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> create table test2 as select * from test where 1=2;

Table created.

--Generate Archivelogs

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> 


-- Take again archivelogs backup
[oracle@dbserver Desktop]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:33:39 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11GR2 (DBID=1660417640)

RMAN> backup  device type disk tag "ARCHIVELOG_BACKUP" archivelog all not backed up delete all input;

Starting backup at 09-JAN-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=6 STAMP=836386263
input archived log thread=1 sequence=3 RECID=7 STAMP=836386270
input archived log thread=1 sequence=4 RECID=8 STAMP=836386427
channel ORA_DISK_1: starting piece 1 at 09-JAN-14
channel ORA_DISK_1: finished piece 1 at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwf0qnp_.arc RECID=6 STAMP=836386263
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_3_9dwf0y20_.arc RECID=7 STAMP=836386270
archived log file name=/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_4_9dwf5vld_.arc RECID=8 STAMP=836386427
Finished backup at 09-JAN-14

Starting Control File and SPFILE Autobackup at 09-JAN-14
piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/autobackup/2014_01_09/o1_mf_s_836386429_9dwf5xcm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-14

RMAN> 

[oracle@dbserver 2014_01_09]$ pwd
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09

[oracle@dbserver 2014_01_09]$ ls
[oracle@dbserver 2014_01_09]$ 

-- Now try to restore using flashback db

Jan 9 09:32:00 2014 -- before backup of archivelog



SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size    2217992 bytes
Variable Size  499124216 bytes
Database Buffers  335544320 bytes
Redo Buffers    2396160 bytes
Database mounted.

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';
FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00'
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 989641 to SCN 990218
ORA-38761: redo log sequence 2 in thread 1, incarnation 3 could not be accessed


SQL> 


[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 9 09:43:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB11GR2 (DBID=1660417640, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       DB11GR2  1660417640       PARENT  1          15-AUG-09
2       2       DB11GR2  1660417640       PARENT  945184     11-SEP-13
3       3       DB11GR2  1660417640       CURRENT 989483     09-JAN-14

RMAN> 



-- Take which archivelog need

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------



-- Try restore archivelog and select again

RMAN> restore archivelog from sequence 1;

Starting restore at 09-JAN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwdtvmf_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=2
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DB11GR2/backupset/2014_01_09/o1_mf_annnn_ARCHIVELOG_BACKUP_9dwf5w15_.bkp tag=ARCHIVELOG_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 09-JAN-14

RMAN> 


SQL> SELECT NAME
  FROM V$ARCHIVED_LOG A, V$DATABASE_INCARNATION I 
 WHERE A.RESETLOGS_ID = I.RESETLOGS_ID
   AND A.SEQUENCE# = 2 
   AND A.THREAD# = 1
   AND I.INCARNATION# = 3  2    3    4    5    6  ;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/flash_recovery_area/DB11GR2/archivelog/2014_01_09/o1_mf_1_2_9dwg
1v3r_.arc



-- Needed archivelog is available now try to flashback

SQL> FLASHBACK DATABASE TO TIMESTAMP timestamp '2014-01-09 09:32:00';

Flashback complete.


SQL> alter database open resetlogs;

Database altered.

SQL> 

-- Now check 
SQL> conn ulfet/ulfet
Connected.

SQL> select * from tab;

TNAME       TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST       TABLE
TEST2       TABLE

SQL> select * from test;

ID
----------
       111

SQL> select * from test2;

no rows selected

SQL> 

Thats all
at

2 comments:

Subscribe to: Post Comments (Atom)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...