How to enable
Archivelog mode in Oracle database 11g.
There
are two types of logging modes in Oracle database :-
1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.
2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.
First we will see on spfile how to enable ARCHIVE LOG MODE on SPFILE.
1. ARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don't have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.
2. NOARCHIVELOG :- In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.
First we will see on spfile how to enable ARCHIVE LOG MODE on SPFILE.
How to check log mode :-
]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
----------
ORCL
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence 1
Current log sequence 1
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- - -----------
ORCL NOARCHIVELOG
Currently the ORCL database is in NOARCHIVELOG mode
To change the Oracle database in ARCHIVELOG mode:-
1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.
NAME LOG_MODE
--------- - -----------
ORCL NOARCHIVELOG
Currently the ORCL database is in NOARCHIVELOG mode
To change the Oracle database in ARCHIVELOG mode:-
1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.
SQL>
sho parameter spfile
NAME TYPE VALUE
------------------------------ --------- ----------------------------------------------------------------------
Spfile string /oraeng/apps/oracle/product/11.2.0/dbs/spfileorcl.ora
SQL> alter system set
log_archive_dest='/disk1/oradata/orcl/arch’ scope=spfile;
System altered.
2. After this you need to shut down your database and start again in MOUNT mode
System altered.
2. After this you need to shut down your database and start again in MOUNT mode
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size
1341000 bytes
Variable Size
322963896 bytes
Database Buffers
696254464 bytes
Redo Buffers
4739072 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode
Archive ModeAutomatic archival Enabled
Archive destination /disk1/oradata/orcl/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
To change the Oracle database
in NOARCHIVELOG mode.
1.Shutdown your running database.
SQL> shut immediate
Database closed.
Database
dismounted.
ORACLE instance
shut down.
2. Start your database in MOUNT mode.
SQL> startup
mount
ORACLE instance
started.
Total System
Global Area 1025298432 bytes
Fixed Size
1341000 bytes
Variable Size
322963896 bytes
Database Buffers
696254464 bytes
Redo Buffers
4739072 bytes
Database
mounted.
SQL> alter
database noarchivelog;
Database
altered.
SQL> alter
database open;
Database
altered.
SQL> archive
log list
Database log
mode No Archive Mode
Automatic
archival Disabled
Archive
destination /disk1/oradata/orcl/arch
Oldest online
log sequence 1
Current log
sequence 1
SQL> select
name,log_mode from v$database;
NAME
LOG_MODE
---------
------------
ORCL
NOARCHIVELOG
]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 21 23:03:44 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
----------
ORCLE
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence 1
Current log sequence 1
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oraeng/apps/oracle/product/11.2.0/dbs
Oldest online log sequence 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- - -----------
ORCL NOARCHIVELOG
Currently the ORCL database is in NOARCHIVELOG mode
To change the Oracle database in ARCHIVELOG mode:-
1. If needed set the archive log destination where you want to save your archive logs whether to a single location . Then you have to set your archive location before changing your database to ARCHIVELOG mode.
NAME LOG_MODE
--------- - -----------
ORCL NOARCHIVELOG
Currently the ORCL database is in NOARCHIVELOG mode
To change the Oracle database in ARCHIVELOG mode:-
1. If needed set the archive log destination where you want to save your archive logs whether to a single location . Then you have to set your archive location before changing your database to ARCHIVELOG mode.
SQL>
sho parameter spfile
NAME TYPE VALUE
------------------------- -----------------
---------------------------------------------------------------------
Spfile string /oraeng/apps/oracle/product/11.2.0/dbs/spfileorcl.ora
SQL> create pfile from spfile;
2.
After this you need to shut down your database and start again in MOUNT mode
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
]$mkdir –p /disk1/oradata/orcl/arch
]$cd /oraeng/apps/oracle/product/11.2.0/dbs
Dbs]$ vi initorcl.ora
{“Add this parameter into to parameter file”}
Log_archive_dest=’/disk1/oradata/orcl/arch’
:wq
Dbs]$ rm –rf spfileorcl.ora
]$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1025298432 bytes
Fixed Size
1341000 bytes
Variable Size
322963896 bytes
Database Buffers
696254464 bytes
Redo Buffers
4739072 bytes
Database mounted.
SQL> archive
log list
Database log
mode No Archive Mode
Automatic
archival Disabled
Archive
destination /disk1/oradata/orcl/arch
Oldest online
log sequence 1
Current log
sequence 1
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination /disk1/oradata/orcl/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
To change the Oracle database in NOARCHIVELOG mode.
Automatic archival Enabled
Archive destination /disk1/oradata/orcl/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name,log_mode from v$database;
NAME LOG_MODE
--------- ------------
ORCL ARCHIVELOG
To change the Oracle database in NOARCHIVELOG mode.
1.Shutdown your running database.
SQL> shu immediate
Database closed.
Database
dismounted.
ORACLE instance
shut down.
2. Start your database in MOUNT mode.
SQL> startup
mount
ORACLE instance
started.
Total System
Global Area 1025298432 bytes
Fixed Size
1341000 bytes
Variable Size
322963896 bytes
Database Buffers
696254464 bytes
Redo Buffers
4739072 bytes
Database
mounted.
SQL> alter
database noarchivelog;
Database
altered.
SQL> alter
database open;
Database
altered.
SQL> archive
log list
Database log
mode No Archive Mode
Automatic
archival Disabled
Archive destination
/disk1/oradata/orcl/arch
Oldest online
log sequence 1
Current log
sequence 1
SQL> select
name,log_mode from v$database;
NAME
LOG_MODE
---------
------------
ORCL
NOARCHIVELOG
I hope this POST helped you a lot.
Thank you.
Aasalamwalkum bhai nice blog created its helpful to me keep posting new concept and some entry level interview question and answer
ReplyDeleteHI
ReplyDeleteI need clear steps to enable archive log in rac environment
IMP SCOTT/TIGER SHOW=Y FILE=import_file.dmp
ReplyDelete