Flashback database with deleted archive log, or no archive log.

By: Brian Fitzgerald

Scenarios:

  1. You want to run flashback database, but, for some reason, a needed archivelog has been deleted, but you have a backup.
  2. You want to be able to flashback a database without needing any archivelog.

Solutions:

  1. Identify and restore the needed archive log (usually just one).
  2. Create the restore point while the database is mounted and consistent.

Demo #1. Flashback if a needed archivelog has been deleted:

[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 20:09:43 2016

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

connected to target database: TESTFB (DBID=2908670758)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 20:10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 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=21 RECID=6 STAMP=926107742
input archived log thread=1 sequence=22 RECID=7 STAMP=926107771
input archived log thread=1 sequence=23 RECID=8 STAMP=926107773
input archived log thread=1 sequence=24 RECID=9 STAMP=926107777
input archived log thread=1 sequence=25 RECID=10 STAMP=926107803
channel ORA_DISK_1: starting piece 1 at 20161024 20:10
channel ORA_DISK_1: finished piece 1 at 20161024 20:10
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 thread=1 sequence=21
RMAN-08139: WARNING: archived redo log not deleted, needed for guaranteed restore point
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 thread=1 sequence=22
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_23.469.926107773 RECID=8 STAMP=926107773
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926107777 RECID=9 STAMP=926107777
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.527.926107803 RECID=10 STAMP=926107803
Finished backup at 20161024 20:10

RMAN> delete noprompt force archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
List of Archived Log Copies for database with db_unique_name TESTFB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - --------------
6 1 21 A 20161024 20:07
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741

7 1 22 A 20161024 20:09
 Name: +RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771

deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_21.456.926107741 RECID=6 STAMP=926107742
deleted archived log
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926107771 RECID=7 STAMP=926107771
Deleted 2 objects

[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:32:02 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009
flashback database to restore point FBDEMO_RP_20161024_2009
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 1709223 to SCN 1709224
ORA-38761: redo log sequence 22 in thread 1, incarnation 2 could not be
accessed

OK, we have a problem. The solution is to find out the needed SCN from v$restore_point, and then restore that archivelog.

[oracle@stormking TESTFB blog]$ sysdba @ vrestorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:54:02 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1709224 2 YES 2016-10-24 20:09 FBDEMO_RP_20161024_2009

RMAN> restore archivelog from scn 1709224 until scn 1709224;

Starting restore at 20161024 20:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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=22
channel ORA_DISK_1: reading from backup piece +RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807
channel ORA_DISK_1: piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t201006_0.532.926107807 tag=TAG20161024T201006
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 20161024 20:39

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 20:39:16 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
FBDEMO_RP_20161024_2009

FBDEMO_RP_20161024_2009
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point FBDEMO_RP_20161024_2009

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Demo #2. Be able to flashback without any archivelogs. First, shutdown immediate, then startup mount. Create the restore point. No archivelogs will be required to flash back.

[oracle@stormking TESTFB blog]$ sysdba @ cr.consistent.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:02:59 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

flashback database is enabled

Session altered.


no rows selected


no rows selected

no restore point exists

RP INST
-------------------------------------------------------------------------------- --------
consistent_rp_20161024_2102 TESTFB

consistent_rp_20161024_2102
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.

no rows selected

the database is consistent
old 1: create restore point &&rp guarantee flashback database
new 1: create restore point consistent_rp_20161024_2102 guarantee flashback database

Restore point created.


Session altered.


 SCN DATABASE_INCARNATION# GUA TIME NAME
---------------- --------------------- --- ---------------- ----------------------------------------
 1712257 3 YES 2016-10-24 21:03 CONSISTENT_RP_20161024_2102

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@stormking TESTFB blog]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Oct 24 21:03:40 2016

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

connected to target database: TESTFB (DBID=2908670758, not open)

RMAN> backup archivelog all not backed up 1 times delete input;

Starting backup at 20161024 21:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
skipping archived log of thread 1 with sequence 22; already backed up
skipping archived logs of thread 1 from sequence 24 to 25; already backed up
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=26 RECID=13 STAMP=926109581
channel ORA_DISK_1: starting piece 1 at 20161024 21:03
channel ORA_DISK_1: finished piece 1 at 20161024 21:03
piece handle=+RECO/TESTFB/BACKUPSET/2016_10_24/annnf0_tag20161024t210351_0.469.926111033 tag=TAG20161024T210351 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=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_26.527.926109581 RECID=13 STAMP=926109581
channel ORA_DISK_1: deleting archived log(s)
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_22.465.926109543 RECID=11 STAMP=926109542
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_24.536.926109583 RECID=14 STAMP=926109582
archived log file name=+RECO/TESTFB/ARCHIVELOG/2016_10_24/thread_1_seq_25.456.926109579 RECID=12 STAMP=926109579
Finished backup at 20161024 21:03

RMAN> delete noprompt archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
specification does not match any archived log in the repository

RMAN>

Recovery Manager complete.
[oracle@stormking TESTFB blog]$ sysdba @ flashback.db.to.restorepoint.sql

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 24 21:04:21 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


no rows selected

a guaranteed restore point exists

NAME
--------------------------------------------------------------------------------
CONSISTENT_RP_20161024_2102

CONSISTENT_RP_20161024_2102
ORACLE instance started.

Total System Global Area 2533359616 bytes
Fixed Size 2927576 bytes
Variable Size 671089704 bytes
Database Buffers 1845493760 bytes
Redo Buffers 13848576 bytes
Database mounted.
old 1: flashback database to restore point &&name
new 1: flashback database to restore point CONSISTENT_RP_20161024_2102

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Scripts that were used in this blog post, in alphabetical order:

assert.database.flashback.is.on.sql

whenever sqlerror exit 1
select 0 / 0 "chk that flashback is on"
from v$database
where FLASHBACK_ON != 'YES';

prompt flashback database is enabled

assert.database.is.consistent.sql:

whenever sqlerror exit 1

select 0 / 0 "chk datafiles are consistent"
from v$datafile_header
where status = 'ONLINE'
and fuzzy = 'YES';

prompt the database is consistent

assert.guaranteed.restorepoint.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk for guar restore point"
from dual
where not exists
(
 select *
 from v$restore_point
 where guarantee_flashback_database = 'YES'
);

prompt a guaranteed restore point exists

assert.no.restore.point.exists.sql:

whenever sqlerror exit 1

select 0 / 0 "chk no restore point exists"
from v$restore_point
where rownum = 1;
prompt no restore point exists

cr.consistent.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'consistent_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

shutdown immediate
startup mount

@ assert.database.is.consistent.sql

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

cr.fbdemo.restorepoint.sql:

whenever oserror exit 1
whenever sqlerror exit 1
column inst format a8
set linesize 200
set trimspool on

@ assert.database.flashback.is.on.sql
@ vrestorepoint.sql
@ assert.no.restore.point.exists.sql

column rp new_value rp format a80
select 'fbdemo_rp_' || to_char ( sysdate , 'yyyymmdd_hh24mi' ) rp,
 sys_context( 'userenv', 'instance_name') inst
from dual;

prompt &&rp

alter system checkpoint;
alter system switch logfile;

create restore point &&rp guarantee flashback database;

@ vrestorepoint.sql
quit

vrestorepoint.sql:

set linesize 200
set trimspool on
set pagesize 900
column scn format 999999999999999
column time format a16
column name format a40

alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi';

select rp.scn,
 rp.database_incarnation#,
 rp.guarantee_flashback_database,
 rp.time,
 rp.name
from v$restore_point rp;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s