Continuing my series on Oracle Database Flashback. This post demonstrates a FLASHBACK DATABASE when a Tablespace is set to FLASHBACK OFF.
UPDATE 20-Feb-15 : When might I have FLASHBACK OFF for a Tablespace in a FLASHBACK ON Database ? When, the Tablespace contents are "throwaway-able". I can avoid the overheads of Flashback for DML in the Tablespace and I am confident that on the rare occasion that I have to Flashback the Database, I can discard and rebuild the tablespace and it's contents because they are not important for persistency.
After my last post on 15/16-Feb (midnight), I had generated some transactions. The Flashback Size has grown from 24.576MB to 190.972MB.
Question : Does Database Flashback always apply to the whole database ? Can we exclude a selected tablespace ?
So, it is possible to set FLASHBACK OFF for a tablespace ! What are the implications ? Oracle does NOT save Flashback information for this tablespace. If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.
UPDATE : 18-FEB-15
Continuing the case.
Note : Subsequent to last night's post I had some more activity on the database and even a RESTORE and RESETLOGS. The v$flashback_database_log contents are different now.
Now, let me create a RESTORE POINT and then some activity in the TEST_NOFB database.
So, I have generated DML activity in the TEST_NOFB Tablespace.
Let me define another Restore Point and generate more DML.
I now have two Restore Points and DML against the target table in a FLASHBACK OFF Tablespace between and after the Restore Points.
Let my try to Flashback to the 2nd Restore Point. What should I see ? 225138 rows in the table ? Or no rows in the table ?
AAHA ! It cannot flashback the datafile because no flashback log exists for it.
Let me quote again what I said yesterday : If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.
So, I must take the necessary action :
I *cannot* selectively recover that datafile. What options do I have ?
In the meantime, let's look at the alert.log.
So, the datafile is unusable.
I can only drop the tablespace.
There ! A Tablespace that was in FLASHBACK OFF mode cannot be accessed after a Flashback Database --- not even in respect to data that existed before the Restore Point that is used for the Flashback.
.
.
.
UPDATE 20-Feb-15 : When might I have FLASHBACK OFF for a Tablespace in a FLASHBACK ON Database ? When, the Tablespace contents are "throwaway-able". I can avoid the overheads of Flashback for DML in the Tablespace and I am confident that on the rare occasion that I have to Flashback the Database, I can discard and rebuild the tablespace and it's contents because they are not important for persistency.
[oracle@localhost Hemant]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 17 22:30:53 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
---------
17-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
-------------------- --------- ---------------- -------------- ------------------------
14573520 15-FEB-15 1440 190971904 0
SYS>
After my last post on 15/16-Feb (midnight), I had generated some transactions. The Flashback Size has grown from 24.576MB to 190.972MB.
Question : Does Database Flashback always apply to the whole database ? Can we exclude a selected tablespace ?
SYS>create tablespace TEST_NOFB; Tablespace created. SYS>alter tablespace TEST_NOFB flashback OFF; Tablespace altered. SYS>
So, it is possible to set FLASHBACK OFF for a tablespace ! What are the implications ? Oracle does NOT save Flashback information for this tablespace. If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.
UPDATE : 18-FEB-15
Continuing the case.
Note : Subsequent to last night's post I had some more activity on the database and even a RESTORE and RESETLOGS. The v$flashback_database_log contents are different now.
[oracle@localhost Hemant]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Wed Feb 18 23:27:19 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
---------
18-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 62783488 0
SYS>
Now, let me create a RESTORE POINT and then some activity in the TEST_NOFB database.
SYS>create restore point FEB18_15;
Restore point created.
SYS>connect hemant/hemant
Connected.
HEMANT>create table test_nofb_tbl tablespace test_nofb
2 as select * from dba_objects
3 where 1=2;
Table created.
HEMANT>select tablespace_name
2 from user_tables
3 where table_name = 'TEST_NOFB_TBL'
4 /
TABLESPACE_NAME
------------------------------
TEST_NOFB
HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /
225138 rows created.
HEMANT>truncate table test_nofb_tbl;
Table truncated.
HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /
225138 rows created.
HEMANT>commit;
Commit complete.
HEMANT>connect / as sysdba
Connected.
SYS>select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
14573526 17-FEB-15 1440 62783488 646397952
SYS>
So, I have generated DML activity in the TEST_NOFB Tablespace.
Let me define another Restore Point and generate more DML.
SYS>create restore point FEB18_15_2nd;
Restore point created.
SYS>connect hemant/hemant
Connected.
HEMANT>insert into test_nofb_tbl
2 select * from dba_objects
3 union all
4 select * from dba_objects
5 union all
6 select * from dba_objects
7 /
225138 rows created.
HEMANT>commit;
Commit complete.
HEMANT>select count(*) from test_nofb_tbl;
COUNT(*)
----------
450276
HEMANT>
I now have two Restore Points and DML against the target table in a FLASHBACK OFF Tablespace between and after the Restore Points.
Let my try to Flashback to the 2nd Restore Point. What should I see ? 225138 rows in the table ? Or no rows in the table ?
HEMANT>connect / as sysdba Connected. 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> SYS>flashback database to restore point FEB18_15_2nd; flashback database to restore point FEB18_15_2nd * ERROR at line 1: ORA-38753: Cannot flashback data file 6; no flashback log data. ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf' SYS>
AAHA ! It cannot flashback the datafile because no flashback log exists for it.
Let me quote again what I said yesterday : If I need to Flashback the Database, this tablespace or all the datafiles in this tablespace must be taken OFFLINE or DROPped.
So, I must take the necessary action :
SYS>alter database datafile 6 offline; Database altered. SYS>flashback database to restore point FEB18_15_2nd; Flashback complete. SYS>alter database open read only; Database altered. SYS> SYS>alter database datafile 6 online; alter database datafile 6 online * ERROR at line 1: ORA-01113: file 6 needs media recovery ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf' SYS>recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-38798: Cannot perform partial database recovery ORA-38797: Full database recovery required after a database has been flashed back SYS>
I *cannot* selectively recover that datafile. What options do I have ?
In the meantime, let's look at the alert.log.
Wed Feb 18 23:47:04 2015 flashback database to restore point FEB18_15_2nd ORA-38753 signalled during: flashback database to restore point FEB18_15_2nd... Wed Feb 18 23:49:42 2015 alter database datafile 6 offline Completed: alter database datafile 6 offline flashback database to restore point FEB18_15_2nd Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Serial Media Recovery started Warning: Datafile 6 (/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf) is offline during full database recovery and will not be recovered Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0 Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log Incomplete Recovery applied until change 14577570 time 02/18/2015 23:40:29 Flashback Media Recovery Complete Completed: flashback database to restore point FEB18_15_2nd Wed Feb 18 23:50:08 2015 alter database open read only AUDIT_TRAIL initialization parameter is changed to OS, as DB is NOT compatible for database opened with read-only access ARCH: STARTING ARCH PROCESSES ..... ..... Completed: alter database open read only Wed Feb 18 23:50:10 2015 ..... ..... Wed Feb 18 23:50:36 2015 alter database datafile 6 online ORA-1113 signalled during: alter database datafile 6 online... Wed Feb 18 23:50:36 2015 Signalling error 1152 for datafile 6! Checker run found 2 new persistent data failures ALTER DATABASE RECOVER datafile 6 Media Recovery Start Serial Media Recovery started Media Recovery failed with error 38798 ORA-283 signalled during: ALTER DATABASE RECOVER datafile 6 ...
So, the datafile is unusable.
I can only drop the tablespace.
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> SYS>recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-38798: Cannot perform partial database recovery ORA-38797: Full database recovery required after a database has been flashed back SYS>alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SYS>alter database open resetlogs; Database altered. SYS> SYS>recover datafile 6; ORA-00283: recovery session canceled due to errors ORA-19909: datafile 6 belongs to an orphan incarnation ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/orcl/ORCL/datafile/o1_mf_test_nof_bg9cq9bv_.dbf' SYS>select tablespace_name from dba_data_files 2 where file_id=6; TABLESPACE_NAME ------------------------------ TEST_NOFB SYS>drop tablespace test_nofb; drop tablespace test_nofb * ERROR at line 1: ORA-01549: tablespace not empty, use INCLUDING CONTENTS option SYS>drop tablespace test_nofb including contents and datafiles; Tablespace dropped. SYS>
There ! A Tablespace that was in FLASHBACK OFF mode cannot be accessed after a Flashback Database --- not even in respect to data that existed before the Restore Point that is used for the Flashback.
.
.
.
No comments:
Post a Comment