ORACLE DATABASE FLASHBACK TECHNOLOGY 1

                                                 

 FLASHBACK TECHNOLOGY 1  

Here will see how to enable the flashback mode & create a restore point/restore as well in an ORACLE database. if the database running  noarchivelog mode.

Points to know

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

• FlashBack technology is oracle 10G new feature.
• To enable the flashback technology the database has to be in Archivelog mode.
• Using flashback technology we can create a RESTORE point and also we can restore any tables and users dropped. 
• Here we are doing whole Flashback database.

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

As I mentioned above. To enable flashback feature the database has to be in archivelog mode.

Using the below query we can check whether a database running archivelog mode (OR) not.

connect to the specific database as SYS user and issue the below.

SQL> archive log list;

Database log mode              No Archive Mode 

Automatic archival             Disabled

Archive destination            /oradba/

Oldest online log sequence     12106

Next log sequence to archive   12108

Current log sequence           12108

Here we can see the database is in noarchivelog mode. To enable archive & flashback mode we need to add below  parameters in pfile and bounce the database to effect those changes. 

NOTE : Always better to create fresh pfile form spfile & edit it. It wont miss out other parametrs.

- db_recovery_file_dest='/oradba/flashback'  - Here it generates flashback logs 

- db_recovery_file_dest_size=50G - So it will allocate 50G space to flashback destination.

- log_archive_dest='/oradba/arch'

- log_archive_format='test_%t_%s_%r.log'

Now we can check using below & confirm the same for your ref.

SQL>show parameter archive

SQL>show parameter db_recovery

We are all set here. Database is in noarchivelog mode & also we have added required parameters for archive & flashback. Now will enable archive and flashback mode for the database.

NOTE: To enable archive & flashback, that's needs to be done MOUNT state of the database. So whenever we do this we need to bounce the database and STARTUP in MOUNT stage.

Once the below parameters added in the pfile STOP the database and START the database in MOUNT stage with the created pfile only. Follow the below steps. 

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

- db_recovery_file_dest='/oradba/flashback' 

- db_recovery_file_dest_size=50G 

- log_archive_dest='/oradba/arch'

- log_archive_format='test_%t_%s_%r.log'

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

SQL> select name from v$database;

NAME

---------

TEST

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

SQL> startup mount pfile='<file location>'

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  2101808 bytes

Variable Size             297799120 bytes

Database Buffers          767557632 bytes

Redo Buffers                6283264 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oradba/arch

Oldest online log sequence     12106

Next log sequence to archive   12108

Current log sequence           12108

SQL> select flashback_on from v$database;

FLASHBACK_ON

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

YES

SQL> CREATE RESTORE POINT TEST_RESTORE GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME;

NAME

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

TEST_RESTORE

SQL> alter database open;

Database altered.

SQL> select open_mode,log_mode from v$database;

OPEN_MODE  LOG_MODE

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

READ WRITE ARCHIVELOG

Here we done with our Target :) Thank You !

NOW WILL SEE HOW TO RESTORE THE DATABASE USING THE RESTORE POINT

Here we are restoring database to TEST_RESTORE point. which we created above.

NOTE : The database restore needs to be done MOUNT stage of the database.

SQL> select name from v$database;

NAME

---------

TEST

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

SQL> startup mount

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  2101808 bytes

Variable Size             297799120 bytes

Database Buffers          767557632 bytes

Redo Buffers                6283264 bytes

Database mounted.

  SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME;

NAME

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

TEST_RESTORE

SQL> flashback database to restore point TEST_RESTORE;

Flashback complete.

SQL> alter database open resetlogs;  

Database altered.

- The database needs to be open with resetlogs. otherwise it will prompt us to open with resetlogs.

Here we done with our Target :) Thank You !