Continuing my series on Database Flashback.
Here I demonstrate that Flashback Logs alone are not sufficient. The process of FLASHBACK DATABASE does need *some* redo entries from Archive/Online Redo Logs.
My database seems to have adequate Flashback Capability in the Flashback Logs. Let me run some transactions and generate Redo and Flashback.
Now, let's suppose that a scheduled (periodic) archive log backup job kicks in and creates a backup of archivelogs and deletes them.
Now, suppose that I need to Flashback the database.
Notice how the OLDEST_FLASHBACK_TIME has changed from 17-Feb to the midnight of 18/19-Feb ! Apparently, my FRA cannot hold very many Flashback Logs.
As I have mentioned in two posts earlier, here and here, the scope of the actual ability to Flashback may vary.
Can I flashback to SCN 14577572 ? Let me give it a try.
Aaha ! Apparently, Oracle needs to read some redo from Archive Log(s) ! So, having Flashback Logs alone is *not* sufficient. I know that I need the database to be running in ArchiveLog mode. But I should also know that if I want to Flashback to a particular Time or SCN, I will need the corresponding ArchiveLog as well ! (Imaging trying to Flashback to 3 days ago and having purged all ArchiveLogs simply because I do daily Full Backups and have Retention of 2 days only !)
So, I must take the necessary action now.
What if I want to Flashback to another, later time ?
I have Sequences 1 and 2 that were restored for the first flashback and Sequences 5 and 6 that have been restored now. Do I need Sequences 3 and 4 ? Do I need Sequence 7 (that contains the Redo beyond SCN 14584203) ?
Let's see.
Here is something important (nothing to do with Flashback Database). The server clock went past midnight into 21-Feb so the restored files went into 2015_02_21 and not 2015_02_20 !
Remember my question about whether I would need Sequence 7 ? Let's see what the alert.log shows about the Flashback Database actions.
Another learning point ! Sequence 7 was applied from the Online Redo Log file.
What we have learned :
1. If we Flashback the database to a particular SCN / Time / Restore Point, Oracle does need the Redo from the Archive / Online Redo Log file that was active at that time. It still needs some Redo to make the database consistent (e.g. apply Undo)
2. If we Flashback the database to SCN 101 and then (without OPEN RESETLOGS), choose to Flashback to a subsequent SCN 201, we again need ArchiveLogs !
3. Flashback from the SQL command-line is intelligent enough to use the Online Redo Log but not (like, say, RMAN's RECOVER DATABASE), automatically restore ArchiveLogs as they are required !
.
.
,
Here I demonstrate that Flashback Logs alone are not sufficient. The process of FLASHBACK DATABASE does need *some* redo entries from Archive/Online Redo Logs.
[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 20 23:18:37 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS>select sysdate from dual;
SYSDATE
---------
20-FEB-15
SYS>show parameter db_flashback_retention_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SYS>select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573526 17-FEB-15 1440 78561280 415383552
SYS>
My database seems to have adequate Flashback Capability in the Flashback Logs. Let me run some transactions and generate Redo and Flashback.
SYS>connect hemant/hemant
Connected.
HEMANT>drop table obj_list;
drop table obj_list
*
ERROR at line 1:
ORA-00942: table or view does not exist
HEMANT>create table obj_list tablespace users
2 as select * from dba_objects where 1=2;
Table created.
HEMANT>select tablespace_name
2 from user_tables
3 where table_name = 'OBJ_LIST';
TABLESPACE_NAME
------------------------------
USERS
HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects;
225138 rows created.
HEMANT>rollback;
Rollback complete.
HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects;
225135 rows created.
HEMANT>delete obj_list;
225135 rows deleted.
HEMANT>
HEMANT>select count(*)
2 from v$archived_log
3 where first_time >
4 (select startup_time
5 from v$instance)
6 /
COUNT(*)
----------
2
HEMANT>
HEMANT>alter system switch logfile;
System altered.
HEMANT>insert into obj_list
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 union all
8 select * from dba_objects;
300180 rows created.
HEMANT>insert into obj_list
2 select * from obj_list;
300180 rows created.
HEMANT>select count(*) from obj_list;
COUNT(*)
----------
600360
HEMANT>commit;
Commit complete.
HEMANT>select count(*) from v$archived_log
2 where first_time >
3 (select startup_time from v$instance)
4 /
COUNT(*)
----------
4
HEMANT>
Now, let's suppose that a scheduled (periodic) archive log backup job kicks in and creates a backup of archivelogs and deletes them.
HEMANT>exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@localhost ~]$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:30:36 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655) RMAN> backup as compressed backupset archivelog all delete input; Starting backup at 20-FEB-15 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=58 device type=DISK channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=106 STAMP=872033779 channel ORA_DISK_1: starting piece 1 at 20-FEB-15 channel ORA_DISK_1: finished piece 1 at 20-FEB-15 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnshxs_.bkp tag=TAG20150220T233055 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 channel ORA_DISK_1: deleting archived log(s) archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_18/o1_mf_1_3_bg9dcl3v_.arc RECID=106 STAMP=872033779 channel ORA_DISK_1: starting compressed archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=3 RECID=112 STAMP=872205844 input archived log thread=1 sequence=4 RECID=113 STAMP=872205859 input archived log thread=1 sequence=5 RECID=114 STAMP=872205867 input archived log thread=1 sequence=6 RECID=115 STAMP=872206048 input archived log thread=1 sequence=7 RECID=116 STAMP=872206098 input archived log thread=1 sequence=8 RECID=117 STAMP=872206254 channel ORA_DISK_1: starting piece 1 at 20-FEB-15 channel ORA_DISK_1: finished piece 1 at 20-FEB-15 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 channel ORA_DISK_1: deleting archived log(s) archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_3_bggndmz4_.arc RECID=112 STAMP=872205844 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_4_bggnf0qy_.arc RECID=113 STAMP=872205859 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_5_bggnf8ty_.arc RECID=114 STAMP=872205867 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_6_bggnlzvr_.arc RECID=115 STAMP=872206048 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_7_bggnnk6c_.arc RECID=116 STAMP=872206098 archived log file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_8_bggnsg5k_.arc RECID=117 STAMP=872206254 Finished backup at 20-FEB-15 Starting Control File and SPFILE Autobackup at 20-FEB-15 piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2015_02_20/o1_mf_s_872206274_bggnt34k_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 20-FEB-15 RMAN> exit Recovery Manager complete. [oracle@localhost ~]$
Now, suppose that I need to Flashback the database.
[oracle@localhost ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 20 23:32:36 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS>select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14577571 19-FEB-15 1440 135348224 3784998912
SYS>select to_char(oldest_flashback_time,'DD-MON HH24:MI:SS')
2 from V$flashback_database_log;
TO_CHAR(OLDEST_FLASHBACK
------------------------
19-FEB 00:04:02
SYS>
Notice how the OLDEST_FLASHBACK_TIME has changed from 17-Feb to the midnight of 18/19-Feb ! Apparently, my FRA cannot hold very many Flashback Logs.
As I have mentioned in two posts earlier, here and here, the scope of the actual ability to Flashback may vary.
Can I flashback to SCN 14577572 ? Let me give it a try.
SYS>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS>startup mount ORACLE instance started. Total System Global Area 456146944 bytes Fixed Size 1344840 bytes Variable Size 394267320 bytes Database Buffers 54525952 bytes Redo Buffers 6008832 bytes Database mounted. SYS>flashback database to SCN 14577572; flashback database to SCN 14577572 * ERROR at line 1: ORA-38754: FLASHBACK DATABASE not started; required redo log is not available ORA-38762: redo logs needed for SCN 14577571 to SCN 14577572 ORA-38761: redo log sequence 1 in thread 1, incarnation 5 could not be accessed SYS>
Aaha ! Apparently, Oracle needs to read some redo from Archive Log(s) ! So, having Flashback Logs alone is *not* sufficient. I know that I need the database to be running in ArchiveLog mode. But I should also know that if I want to Flashback to a particular Time or SCN, I will need the corresponding ArchiveLog as well ! (Imaging trying to Flashback to 3 days ago and having purged all ArchiveLogs simply because I do daily Full Backups and have Retention of 2 days only !)
So, I must take the necessary action now.
SYS>!sh sh-3.2$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:43:43 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655, not open) RMAN> restore archivelog from sequence 1 until sequence 2; Starting restore at 20-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 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 /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001619_bg9gpq87_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001619_bg9gpq87_.bkp tag=TAG20150219T001619 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: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001930_bg9gwl9w_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_19/o1_mf_annnn_TAG20150219T001930_bg9gwl9w_.bkp tag=TAG20150219T001930 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-FEB-15 RMAN> exit Recovery Manager complete. sh-3.2$ sh-3.2$ exit exit SYS>l 1* flashback database to SCN 14577572 SYS>/ Flashback complete. SYS>alter database open read only; Database altered. SYS>
What if I want to Flashback to another, later time ?
SYS>select sequence#, first_change#, to_char(first_time,'DD-MON HH24:MI:SS')
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by 1;
SEQUENCE# FIRST_CHANGE# TO_CHAR(FIRST_TIME,'DD-M
---------- ------------- ------------------------
4 14580736 20-FEB 23:24:03
5 14581651 20-FEB 23:24:16
6 14583536 20-FEB 23:24:23
7 14584203 20-FEB 23:27:27
8 14584351 20-FEB 23:28:17
SYS>
SYS>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS>startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SYS>flashback database to SCN 14584205;
flashback database to SCN 14584205
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 14577573 to SCN 14584205
ORA-38761: redo log sequence 5 in thread 1, incarnation 5 could not be accessed
SYS>
SYS>!sh
sh-3.2$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Fri Feb 20 23:53:48 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1229390655, not open)
RMAN> restore archivelog from sequence 5 until sequence 6;
Starting restore at 20-FEB-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 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=5
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 20-FEB-15
RMAN> exit
Recovery Manager complete.
sh-3.2$
sh-3.2$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20
sh-3.2$ ls -l
total 63344
-rw-rw---- 1 oracle oracle 494592 Feb 20 23:44 o1_mf_1_1_bggol5t8_.arc
-rw-rw---- 1 oracle oracle 18432 Feb 20 23:44 o1_mf_1_2_bggol6wm_.arc
-rw-rw---- 1 oracle oracle 50630144 Feb 20 23:54 o1_mf_1_5_bggp4zbp_.arc
-rw-rw---- 1 oracle oracle 13635072 Feb 20 23:54 o1_mf_1_6_bggp4zh3_.arc
sh-3.2$
I have Sequences 1 and 2 that were restored for the first flashback and Sequences 5 and 6 that have been restored now. Do I need Sequences 3 and 4 ? Do I need Sequence 7 (that contains the Redo beyond SCN 14584203) ?
Let's see.
sh-3.2$ exit exit SYS>l 1* flashback database to SCN 14584205 SYS>/ flashback database to SCN 14584205 * ERROR at line 1: ORA-38754: FLASHBACK DATABASE not started; required redo log is not available ORA-38762: redo logs needed for SCN 14577573 to SCN 14584205 ORA-38761: redo log sequence 3 in thread 1, incarnation 5 could not be accessed SYS>Apparently, I also need Sequences 3,at least, and (maybe ?) 4 ! Why ? Because my database is currently at an SCN lower than the one I want to Flashback to and the corresponding redo is required. (If I had *not* done the first Flashback to the lower SCN, I wouldn't need these Archivelogs !)
SYS>!sh sh-3.2$ rman target / Recovery Manager: Release 11.2.0.2.0 - Production on Sat Feb 21 00:01:34 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1229390655, not open) RMAN> restore archivelog from sequence 3 until sequence 4; Starting restore at 21-FEB-15 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=17 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=3 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=4 channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2015_02_20/o1_mf_annnn_TAG20150220T233055_bggnsmgc_.bkp tag=TAG20150220T233055 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 21-FEB-15 RMAN> exit Recovery Manager complete. sh-3.2$ sh-3.2$ cd /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20 sh-3.2$ ls -l total 63344 -rw-rw---- 1 oracle oracle 494592 Feb 20 23:44 o1_mf_1_1_bggol5t8_.arc -rw-rw---- 1 oracle oracle 18432 Feb 20 23:44 o1_mf_1_2_bggol6wm_.arc -rw-rw---- 1 oracle oracle 50630144 Feb 20 23:54 o1_mf_1_5_bggp4zbp_.arc -rw-rw---- 1 oracle oracle 13635072 Feb 20 23:54 o1_mf_1_6_bggp4zh3_.arc sh-3.2$ cd ../*21 sh-3.2$ ls -l total 80272 -rw-rw---- 1 oracle oracle 31472640 Feb 21 00:01 o1_mf_1_3_bggpmf06_.arc -rw-rw---- 1 oracle oracle 50630144 Feb 21 00:01 o1_mf_1_4_bggpmdxk_.arc sh-3.2$
Here is something important (nothing to do with Flashback Database). The server clock went past midnight into 21-Feb so the restored files went into 2015_02_21 and not 2015_02_20 !
sh-3.2$ exit exit SYS>l 1* flashback database to SCN 14584205 SYS>/ Flashback complete. SYS>
Remember my question about whether I would need Sequence 7 ? Let's see what the alert.log shows about the Flashback Database actions.
Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_2_bggol6wm_.arc Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_21/o1_mf_1_3_bggpmf06_.arc Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_21/o1_mf_1_4_bggpmdxk_.arc Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_5_bggp4zbp_.arc Flashback Media Recovery Log /home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2015_02_20/o1_mf_1_6_bggp4zh3_.arc Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log Incomplete Recovery applied until change 14584206 time 02/20/2015 23:28:03 Flashback Media Recovery Complete Completed: flashback database to SCN 14584205
Another learning point ! Sequence 7 was applied from the Online Redo Log file.
What we have learned :
1. If we Flashback the database to a particular SCN / Time / Restore Point, Oracle does need the Redo from the Archive / Online Redo Log file that was active at that time. It still needs some Redo to make the database consistent (e.g. apply Undo)
2. If we Flashback the database to SCN 101 and then (without OPEN RESETLOGS), choose to Flashback to a subsequent SCN 201, we again need ArchiveLogs !
3. Flashback from the SQL command-line is intelligent enough to use the Online Redo Log but not (like, say, RMAN's RECOVER DATABASE), automatically restore ArchiveLogs as they are required !
.
.
,
No comments:
Post a Comment