Enable or Disable in Archive log mode in Oracle Database

 Enable or Disable in Archive log mode in Oracle Database

Introduction

Two types of logging mode in oracle database

1.Archivelog mode: In this Archivelog mode after the online redo logs are filled , it will move to the archive location,archivelog mode you can put the database in for creating a backup of all transactions occured in the database so that you can recover at any point of time 

2.Noarchivelog mode:In this Noarchivelog mode Filled  online redo logs wont be accepted,archives are insted they will be overwritten, In this mode absence of archivelog and database not be recoverd at any point of time 

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         6502926848 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

 

Disable archivelog mode

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

 

 

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         6502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

 

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

Info: Info on enable or disable archivelog mode it may be differ in your environment like production,testing,develoment etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Enable or Disable Flashback Technology in Oracle

 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