Enable or Disable Flashback Technology in Oracle
Introduction
Using Flashback Technology we can restore the database and dropped users,and tables,schemas in oracle we will flashback the database to past when the database ,user,table,schema is available at the time of dropped database before.
Here in this blog i am going to expalin how to enable or disable by using Flashback technology in oracle
Let us start the process
Enable Flashback
The Database must be in archive log mode
Here i am showing how to enable archive log mode
SQL > select name,log_mode from v$database;
NAME LOG_MODE
--------- ----------------------
PROD NOARCHIVELOG
SQL > archive log list
Database log mode No Archive Mode
Automatic archival Disbled
Archive destination /chaitanya/arch/PROD
Oldest online log sequence 206506
Next log sequence to archive 206512
Current log sequence 206512
make sure db is running in spfile
SQL > alter system set log_archive_dest_1='LOCATION=/chaitanya/arch/PROD' scope=spfile;
database altered.
SQL >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL > startup mount
ORACLE instance started.
Total System Global Area 5415597568 bytes
Fixed Size 2170304 bytes
Variable Size 805970240 bytes
Database Buffers 4502926848 bytes
Redo Buffers 3530176 bytes
Database mounted.
SQL >alter database archivelog;
database altered.
SQL >alter database open;
database altered.
SQL >select name,log_mode from v$database;
NAME LOG_MODE
--------- --------------------
PROD ARCHIVELOG
SQL >archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /chaitanya/arch/PROD
Oldest online log sequence 206506
Next log sequence to archive 206512
Current log sequence 206512
To enable flashback we need to set two parameters
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
SQL> alter system set db_recovery_file_dest='/home/oracle/prod';
System altered.
SQL> alter system set db_recovery_file_dest_size=12g;
System altered.
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /home/oracle/prod
db_recovery_file_dest_size big integer 10G
Turn on Flashback
SQL> select flashback_on from v$database;
FLASHBACK_ON
-----------------------
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
---------------------
YES
Disable Flashback
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> alter database flashback off;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
----------------------
NO
Note : if you are using 10g or above versions then we need to enable or disable in flashback mode in mount stage
shutdown immediate
startup mount
alter database flashback off;
alter database open
Note : Info on enable or disable flashback technology it may be differ in your environment like production,development,testing and directories etc
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
No comments:
Post a Comment