Skip to content
Advertisements

Can we use Flashback database If noarchivelog exists

Can we use flashback database log after switching from archivelog mode to noarchivelog mode and back to archivelog mode?

According to  this below document reference

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV583

 

We cannot, because when we turn off flashback database, you lose the flashback logs unless they are guaranteed

If they guaranteed, We can’t alter no archivelog mode

Let us see some example :

Step 1: Ensure Flashback ON

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

Step 2:

SQL> conn flashown/flashown;
Connected.
SQL> create table employee as select * from scott.emp;

Table created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

SQL> create restore point azar;

Restore point created.

SQL> update employee set sal=sal+50;

14 rows updated.

SQL> commit;

SQL>select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 09.53.55.726000 AM +03:00

Step 3: alter noarchivelog mode

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> update flashown.employee set sal=sal+50;

14 rows updated.

SQL> commit;

Commit complete.

Step 4: Again back to archivelog mode & Recover flashback database But we can’t.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> flashback database to restore point azar;
flashback database to restore point azar
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS');
flashback database to timestamp to_date('19-DEC-10 09.54.00','DD-MM-YY HH24:MI:SS')
                                                             *
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

Step 5: Create Guarantee Restore point

SQL> show parameter db_flashback_retention_target;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> create restore point azardb guarantee flashback database;

Restore point created.

SQL> alter database open;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
19-DEC-10 10.11.43.716000 AM +03:00

SQL> update flashown.employee set sal=sal-5;

14 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount
Database mounted.
SQL> alter database flashback off;

Database altered.

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
SQL> alter database flashback on;

Database altered.

SQL> flashback database to timestamp to_date('19-DEC-10 10.11.44','DD-MM-YY HH24:MI:SS');

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select sal from flashown.employee;

       SAL
----------
       955
      1755
      1405
      3130
      1405
      3005
      2605
      3255
      5155
      1655
      1255

       SAL
----------
      1105
      3155
      1455

14 rows selected.
SQL>

So we can’t alter noarchivelog mode While Guarantee restore point exists in DB.

And the If flashback database off,  we can’t recover flashback database using flashback logs.

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: