Search My Oracle Blog

Custom Search

17 February, 2015

Database Flashback -- 4

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.


[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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016