Search My Oracle Blog

Custom Search

25 November, 2016

12.2 New Features -- 3 : Flashback Pluggable Database

12.1 allows Point In Time Recovery of a Pluggable Database but not Flashback of an individual PDB.

12.2 now allows Flashback of an individual PDB.   This is easier with a Local Undo Tablespace instead of a Shared Undo Tablespace.

Here is a quick demo  :

[timestsamps in UTC]

[oracle@HKCORCL ~]$ sqlplus system/Oracle_4U@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 25 14:19:06 2016

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

Last Successful login time: Thu Nov 24 2016 01:03:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from hr.employees_part;

  COUNT(*)
----------
       107

SQL> drop table hr.employees_part purge;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL>
SQL> select sysdate, sysdate-oldest_flashback_time
  2  from v$flashback_database_log;

SYSDATE   SYSDATE-OLDEST_FLASHBACK_TIME
--------- -----------------------------
25-NOV-16                    2.36273148

SQL>
SQL> flashback pluggable database pdb1
  2  to timestamp sysdate-2/24;

Flashback complete.

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17:
'/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf
_pdb1undo_d3dgxhbh_.dbf'


SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> select count(*) from hr.employees_part;

  COUNT(*)
----------
       107

SQL>


(Note : The 12.2 documentation shows the TO TIME clause, it is actually TO TIMESTAMP).
I have done a flashback of PDB1 to a time as of 2hours ago, when the table HR.EMPLOYEES_PART still existed.

Let's look for messages in the alert log.

2016-11-25T14:19:52.992589+00:00
Archived Log entry 11 added for T-1.S-11 ID 0x38800462 LAD:1
2016-11-25T14:19:57.621705+00:00
alter pluggable database pdb1 close
2016-11-25T14:19:57.640353+00:00
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:19:58.885892+00:00
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close
2016-11-25T14:26:10.205824+00:00
flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:10.627900+00:00
Flashback Restore Start
2016-11-25T14:26:11.513882+00:00
Restore Flashback Pluggable Database PDB1 (3) until change 3536013
Flashback Restore Complete
2016-11-25T14:26:11.707236+00:00
Flashback Media Recovery Start
2016-11-25T14:26:11.718480+00:00
Serial Media Recovery started
2016-11-25T14:26:12.006472+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
  Mem# 0: /u04/app/oracle/redo/redo02.log
2016-11-25T14:26:12.283587+00:00
Incomplete Recovery applied until change 3536477 time 11/25/2016 12:26:56
Flashback Media Recovery Complete
Flashback Pluggable Database PDB1 (3) recovered until change 3536477, at 11/25/2016 12:26:56
Completed: flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:21.451523+00:00
alter pluggable database pdb1 open
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:21.659109+00:00
Pdb PDB1 hit error 1113 during open read write (1) and will be closed.
2016-11-25T14:26:21.659410+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_ora_7920.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:21.804780+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 9: '/u02/app/oracle/oradata/HKCORCL/PDB1/system01.dbf'
ORA-1113 signalled during: alter pluggable database pdb1 open...
2016-11-25T14:26:22.086212+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 10: '/u02/app/oracle/oradata/HKCORCL/PDB1/sysaux01.dbf'
2016-11-25T14:26:22.175778+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 12: '/u02/app/oracle/oradata/HKCORCL/PDB1/users01.dbf'
2016-11-25T14:26:22.270876+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
Checker run found 4 new persistent data failures
2016-11-25T14:26:39.804216+00:00
alter pluggable database pdb1 open resetlogs
2016-11-25T14:26:40.377390+00:00
Online datafile 17
Online datafile 12
Online datafile 10
Online datafile 9
2016-11-25T14:26:40.881181+00:00
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868281239 end:868281333 diff:94 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:42.441388+00:00
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:42.827673+00:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868283079 end:868283168 diff:89 ms (0.1 seconds)
PDB1(3):Pluggable database PDB1 dictionary check beginning
2016-11-25T14:26:43.706672+00:00
PDB1(3):Pluggable Database PDB1 Dictionary check complete
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2016-11-25T14:26:44.083617+00:00
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
2016-11-25T14:26:45.205147+00:00
Starting control autobackup

Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/415864F430FE5FFEE053B2D4100A149C/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qldlnv_.bkp
2016-11-25T14:26:46.523130+00:00
Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/3E09703FB0AF1A7EE053DE4BC40A6C1D/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qlfzqg_.bkp
Control autobackup written to DISK device

handle '/u03/app/oracle/fast_recovery_area/HKCORCL/autobackup/2016_11_25/o1_mf_s_928852005_d3jlk651_.bkp'

Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 open resetlogs


The set of ORA-01113 and ORA-01110 errors are when I tried to open PDB1 without a RESETLOGS.
The OPEN RESETLOGS issued at 2016-11-25T14:26:39.804216+00:00 was successful.
(Note : The ALTER SYSTEM SWITCH LOGFILE wasn't required but I like to archive out the CURRENT redo whenever I make a significant action against the database).

.
.
.

24 November, 2016

12.2 New Features -- 2 : Partitioning an Existing Table

A non-partitioned table can be Partitioned (without having to use DBMS_REDEFINITION) online.

SQL> connect hr/Oracle_4U@PDB1
Connected.
SQL> select count(*) from employees;

  COUNT(*)
----------
       107

SQL> create table employees_part as select * from employees;

Table created.

SQL> select table_name from user_part_tables;

no rows selected

SQL> alter table employees_part
  2  modify
  3  partition by range (last_name)
  4  (partition p_N values less than ('O'),
  5   partition p_Q values less than ('R'),
  6   partition p_LAST values less than (MAXVALUE))
  7  online;

Table altered.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
------------
P_N
'O'

P_Q
'R'

P_LAST
MAXVALUE


SQL>
SQL> select table_name, partitioning_type, partition_count
  2  from user_part_tables
  3  where table_name = 'EMPLOYEES_PART'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION PARTITION_COUNT
--------- ---------------
EMPLOYEES_PART
RANGE                   3


SQL>
SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'EMPLOYEES_PART'
  4  order by partition_position
  5  /

PARTITION_NAME
--------------------------------------------------------------------------------
  NUM_ROWS
----------
P_N
        71

P_Q
        10

P_LAST
        26


SQL>


I was able to convert a Non-Partitioned Table to a Range-Partitioned Table online.
.
.
.

12.2 New Features -- 1 : Separate Undo Tablespace for each PDB

Unlike 12.1 MultiTenant, 12.2 introduces a separate Undo Tablespace for each PDB.

SQL> l
  1  select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
  2  from v$containers c, cdb_tablespaces t
  3  where c.con_id=t.con_id
  4  and t.tablespace_name like '%UNDO%'
  5* order by 1,2
SQL> /

    CON_ID CON_NAME         TABLESPACE_NAME  CONTENTS              STATUS
---------- ---------------- ---------------- --------------------- ---------
         1 CDB$ROOT         UNDOTBS1         UNDO                  ONLINE
         3 PDB1             UNDOTBS1         UNDO                  ONLINE
         5 PDB2             UNDOTBS1         UNDO                  ONLINE

SQL>


I have two PDBs and each PDB has an Undo Tablespace.

Let me create a new Undo Tablespace.

SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> create undo tablespace PDB1UNDO ;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_tablespace='PDB1UNDO';

System altered.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>
SQL> select c.con_id, c.name  con_name, t.tablespace_name, t.contents, t.status
  2  from v$containers c, cdb_tablespaces t
  3  where c.con_id=t.con_id
  4  and t.tablespace_name like '%UNDO%'
  5  order by 1,2
  6  /

    CON_ID CON_NAME         TABLESPACE_NAME  CONTENTS              STATUS
---------- ---------------- ---------------- --------------------- ---------
         1 CDB$ROOT         UNDOTBS1         UNDO                  ONLINE
         3 PDB1             PDB1UNDO         UNDO                  ONLINE
         5 PDB2             UNDOTBS1         UNDO                  ONLINE

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      PDB1UNDO
SQL> select tablespace_name, contents, status
  2  from dba_tablespaces
  3  where tablespace_name like '%UNDO%'
  4  /

TABLESPACE_NAME  CONTENTS              STATUS
---------------- --------------------- ---------
PDB1UNDO         UNDO                  ONLINE

SQL>


I was able to switch PDB1 to a new Undo Tablespace (and drop the old Undo Tablespace).
.
.
.

20 November, 2016

Flashback Database -- 3 : Purging (older) Flashback Logs

As demonstrated earlier, Oracle may maintain Flashback Logs for a duration that is longer than the Flashback Retention Target.  This can happen when the db_recovery_filie_dest_size is large enough to support them (along with ArchiveLogs, Backups etc)

For example, in my play database I have reset the retention target to 1day but the Flashback Logs exceed 4 days :

SQL> show parameter flashback_ret

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target      integer  1440
SQL> select sysdate-oldest_flashback_time from v$flashback_database_log;

SYSDATE-OLDEST_FLASHBACK_TIME
-----------------------------
     4.21686343



The DBA should not manually delete Flashback Logs.

The only way I've found to purge older Flashback Logs is to reset db_recovery_file_dest_size to a lower value such that current FRA usage exceeds the dest_size.  This prompts Oracle to purge older Flashback Logs.

However, if ArchiveLogs exist and consume significant space and frequncy in the FRA, you do run the risk of

ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl - Archival Error
ORA-16038: log 1 sequence# nnn cannot be archived
ORA-19809: limit exceeded for recovery files

and/or

ORACLE Instance orcl- Cannot allocate log, archival required
Thread 1 cannot allocate new log, sequence nnn
All online logs need archiving
Examine archive trace files for archiving errors


errors.
So, be careful to monitor your FRA usage and the Flashback Logs.  Use V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE, V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE view.
(see my earlier post that also points to an Oracle Support Doc about the first two views).
.
.
.


14 November, 2016

Flashback Database -- 2 : Flashback Requires Redo (ArchiveLog)

Although Flashback Logs support the ability to execute a FLASHBACK DATABASE command, the actual Flashback also requires Redo to be applied.  This is because the Flashback resets the images of blocks but doesn't guarantee that all transactions are reset to the same point in time (any one block can contain one or more active, uncommitted transactions, and there can be multiple blocks with active transactions at any point in time).  Therefore, since Oracle must revert the database to a consistent image, it needs to be able to apply redo as well (just as it would do for a roll-forward recovery from a backup).

Here's a quick demo of what happens if the redo is not available.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.oldest_flashback_scn, l.oldest_flashback_time
  2  from v$flashback_database_log l;

SYSDATE            OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T
------------------ -------------------- ------------------
14-NOV-16 22:51:37              7246633 14-NOV-16 22:39:43

SQL> 

sh-4.1$ pwd
/u02/FRA/ORCL/archivelog/2016_11_14
sh-4.1$ date
Mon Nov 14 22:52:29 SGT 2016
sh-4.1$ rm *
sh-4.1$ 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.

SQL> flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS');
flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 7246634 to SCN 7269074
ORA-38761: redo log sequence 70 in thread 1, incarnation 5 could not be
accessed


SQL> 
SQL> l
  1  select sequence#, first_change#, first_time
  2  from v$archived_log
  3  where resetlogs_time=(select resetlogs_time from v$database)
  4  and sequence# between 60 and 81
  5* order by 1
SQL> /

 SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ------------- ------------------
        60       7245238 14-NOV-16 22:27:35
        61       7248965 14-NOV-16 22:40:46
        62       7250433 14-NOV-16 22:40:52
        63       7251817 14-NOV-16 22:41:04
        64       7253189 14-NOV-16 22:41:20
        65       7254583 14-NOV-16 22:41:31
        66       7255942 14-NOV-16 22:41:44
        67       7257317 14-NOV-16 22:41:59
        68       7258689 14-NOV-16 22:42:10
        69       7260094 14-NOV-16 22:42:15
        70       7261397 14-NOV-16 22:42:22
        71       7262843 14-NOV-16 22:42:28
        72       7264269 14-NOV-16 22:42:32
        73       7265697 14-NOV-16 22:42:37
        74       7267121 14-NOV-16 22:42:43
        75       7269075 14-NOV-16 22:48:05
        76       7270476 14-NOV-16 22:48:11
        77       7271926 14-NOV-16 22:48:17
        78       7273370 14-NOV-16 22:48:23
        79       7274759 14-NOV-16 22:48:32
        80       7276159 14-NOV-16 22:48:39
        81       7277470 14-NOV-16 22:48:43

22 rows selected.

SQL> 



Note how the error message states that Redo(Archive)Log Sequence#70 is required but provides a range of SCNs that span Sequence#60 to Sequence#74 !

Bottom Line : Flashback Logs alone aren't adequate to Flashback database.  You also need the corresponding Redo.

Just to confirm that I can continue with the current (non-Flashbacked Database) state (in spite of the failed Flashback)  :

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    89
Next log sequence to archive  90
Current log sequence          90
SQL> select current_scn from v$database; 

CURRENT_SCN
-----------
    7289329

SQL> 


.Bottom Line : *Before* you attempt a FLASHBACK DATABASE to the OLDEST_FLASHBACK_TIME (or SCN) from V$FLASHBACK_DATABASE_LOG, ensure that you *do* have the "nearby"  Archive/Redo Logs. !
.
.
.

07 November, 2016

Flashback Database -- 1 : Introduction to Operations

Continuing on my previous post,  ....

In 11gR2,  ALTER DATABASE FLASHBACK ON   and OFF can be executed when the database is OPEN.  Setting FLASHBACK OFF results in deletion of all Flashback Files.

Here is some information that I have pulled from my test database environment :

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> 
SQL> select oldest_flashback_scn, oldest_flashback_time,
  2  retention_target, flashback_size
  3  from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET FLASHBACK_SIZE
-------------------- ------------------ ---------------- --------------
             7140652 07-NOV-16 10:53:30              180      314572800

SQL> select sysdate from dual;

SYSDATE
------------------
07-NOV-16 17:46:54

SQL> 
SQL> select begin_time, end_time, flashback_data, estimated_flashback_size
  2  from v$flashback_database_stat
  3  order by begin_time;

BEGIN_TIME         END_TIME           FLASHBACK_DATA ESTIMATED_FLASHBACK_SIZE
------------------ ------------------ -------------- ------------------------
06-NOV-16 18:56:28 06-NOV-16 21:20:55      202129408                251873280
06-NOV-16 21:20:55 07-NOV-16 09:53:26      107102208                 62054400
07-NOV-16 09:53:26 07-NOV-16 10:53:30       51609600                 67866624
07-NOV-16 10:53:30 07-NOV-16 13:14:45       10682368                 60887040
07-NOV-16 13:14:45 07-NOV-16 14:14:51       66002944                 67986432
07-NOV-16 14:14:51 07-NOV-16 15:14:57       10018816                 66112512
07-NOV-16 15:14:57 07-NOV-16 16:15:01       10190848                 64441344
07-NOV-16 16:15:01 07-NOV-16 17:15:05       53559296                 68751360
07-NOV-16 17:15:05 07-NOV-16 17:47:57       52862976                        0

9 rows selected.

SQL> 
SQL> select log#, sequence#, bytes/1048576 Size_MB, first_time
  2  from v$flashback_database_logfile
  3  order by sequence#;

      LOG#  SEQUENCE#    SIZE_MB FIRST_TIME
---------- ---------- ---------- ------------------
         6          6         50 07-NOV-16 09:00:46
         1          7         50 07-NOV-16 10:36:01
         2          8         50 07-NOV-16 13:13:22
         3          9         50 07-NOV-16 13:43:28
         4         10         50 07-NOV-16 16:43:49
         5         11         50 07-NOV-16 17:44:42

6 rows selected.

SQL> 


Firstly, we note (as in my previous blog post), that the available flashback that is from 10:53am to 5:46pm (almost 7hours) exceeds the Flashback Target of 3hours (180minutes).  Apparently, Flashback Logfiles 1 to 5 have already been purged (but I find no entries for the deletions in the alert log).

Note how the "earliest time" does not match in all three views.  The OLDEST_FLASHBACK_TIME is 10:53am although V$FLASHBACK_DATABASE_STAT reports statistics from the previous day (I had enabled Flashback in the database at 18:56:27 of 06-Nov) while V$FLASHBACK_DATABASE_LOGILE shows an existing logfile from 09:00am to 10:36am.

Let me do a Flashback.  I must rely on the V$FLASHBACK_DATABASE_LOG view to know that I  cannot flashback beyond 10:53am.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> 
SQL> flashback database to timestamp trunc(sysdate)+11/24;

Flashback complete.

SQL> 
SQL> alter database open read only;  --- to verify data if necessary

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;     
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             750781320 bytes
Database Buffers          310378496 bytes
Redo Buffers                5517312 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> 


A FLASHBACK DATABASE requires an OPEN RESETLOGS to open READ WRITE.

Let's look at the alert log for messages about the Flashback operation itself :

Mon Nov 07 17:56:36 2016
flashback database to timestamp trunc(sysdate)+11/24
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 2 slaves
Flashback Media Recovery Log /u02/FRA/ORCL/archivelog/2016_11_07/o1_mf_1_81_d2052ofj_.arc
Mon Nov 07 17:56:43 2016
Incomplete Recovery applied until change 7141255 time 11/07/2016 11:00:01
Flashback Media Recovery Complete
Completed: flashback database to timestamp trunc(sysdate)+11/24
Mon Nov 07 17:57:08 2016
alter database open read only


What happens if I disable and re-enable Flashback ?

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter database flashback off;

Database altered.

SQL> 

From the alert log :
Mon Nov 07 18:03:02 2016
alter database flashback off
Stopping background process RVWR
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y32vjv_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y32xq0_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y3bhkx_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y3dd8r_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1y6r6bf_.flb
Deleted Oracle managed file /u02/FRA/ORCL/flashback/o1_mf_d1ycky3v_.flb
Flashback Database Disabled
Completed: alter database flashback off

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> alter database flashback on;

Database altered.

SQL> 

From the alert log :
Mon Nov 07 18:04:21 2016
alter database flashback on
Starting background process RVWR
Mon Nov 07 18:04:21 2016
RVWR started with pid=30, OS id=12621
Flashback Database Enabled at SCN 7142426
Completed: alter database flashback on

From the FRA :
[oracle@ora11204 flashback]$ pwd
/u02/FRA/ORCL/flashback
[oracle@ora11204 flashback]$ ls -ltr
total 102416
-rw-rw----. 1 oracle oracle 52436992 Nov  7 18:04 o1_mf_d20nf7wc_.flb
-rw-rw----. 1 oracle oracle 52436992 Nov  7 18:05 o1_mf_d20nf5nz_.flb
[oracle@ora11204 flashback]$ 

SQL> alter session set nls_date_Format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select log#, sequence#, bytes/1048576 Size_MB, first_time
  2  from v$flashback_database_logfile
  3  order by sequence#;

      LOG#  SEQUENCE#    SIZE_MB FIRST_TIME
---------- ---------- ---------- ------------------
         2          1         50
         1          1         50 07-NOV-16 18:04:22

SQL> 
 


So, I can set FLASHBACK OFF and ON when the database is OPEN.  (But I can't execute a FLASHBACK TO .... with the database OPEN).
.
.
.

Aggregated by orafaq.com

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