Enabling / Disabling Flashback Database in 11gR2 without recycling database

Flashback database offer a simple way for performing a point in time recovery. This feature was introduced in oracle 10g.

Let’s take a scenario, where we have generated huge amount of flashback logs. Now to reclaim the space, we can reduce the db_flashback_retention_target parameter to a very small value, which will make the logs obsolete after some time & will delete them in case of space pressure in FRA (Flash / Fast Recovery Area).

But if we want to reclaim the space immediately, we can trun off the flashback.

In 10g we’ll have to

shutdown immediate 

startup mount 

alter database flashback off; 

alter database open;

But with 11gR2, Oracle introduced a new feature. We can now turn flashback on / off, when database is OPEN

SQL> select * from v$version;

BANNER

------------------------------------------------------

Oracle Database 11g Enterprise Edition 

Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON STATUS

------------------ ------------

YES OPEN

SQL> alter database flashback off;

Database altered.

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON STATUS

------------------ ------------

NO OPEN

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on, status from v$database, v$instance;

FLASHBACK_ON STATUS

------------------ ------------

YES OPEN

 In addition to above scenario (which I took from some blog), I would like to add one more additional configurations needed to allocated Flash Recovery Area(FRA).

  There are two main parameters to set the size and location of FRA:

    db_recovery_file_dest_size= 4g  ( we can assign in MBs and GBs)

    db_recovery_file_dest='/ora_flash'    ( location - here it's /ora_flash)

   We can modify both the parameters above dynamically as follows:

SQL>   alter system set db_recovery_file_dest_size=10g scope=both;

SQL>  alter system set db_recovery_file_dest='/ora_flash' scope=both;

   To check the parameters and their values:

SQL>  SHOW PARAMETER DB_RECOVERY_FILE_DEST

     Please advice if you feel this has to be corrected or enhanced. 

Thanks,

Imran Khan

Concat rows into single column

Imran> select * from student;     

 Sno

----------     

1 

2

3

Imran> select xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()') as Sno from (select distinct Sno from student);

 Sno

--------------------------------------------------------------------------------------------------------------------------

1,2,3,

If we observe the above result it contains comma(,) at the end. In order to remove comma(,) we will use RTRIM function:

Imran> select rtrim(xmlagg (xmlelement (e,Sno ||',')).EXTRACT('//text()'), ',') as Sno from (select distinct Sno from student);

Sno

-------------------------------------------------------------------------------------------------------------------------

1,2,3