By: Brian Fitzgerald
Scenarios:
- You want to run flashback database, but, for some reason, a needed archivelog has been deleted, but you have a backup.
- You want to be able to flashback a database without needing any archivelog.
Solutions:
- Identify and restore the needed archive log (usually just one).
- 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;