How configure oracle 11g In Archivelog Mode & No archvielog Mode On Redhat
The following are the steps required to enable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
oracle@ora1 sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:02:52 2010
Copyright 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Current log sequence 27
SQL
The log mode is No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You can determine the path by looking at the parameter RECOVERY_FILE_DEST.
SQL show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3852M
SQL
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.
SQL alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both;
System altered.
SQL archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 25
Current log sequence 27
SQL
Now we shutdown the database and bring it backup in mount mode.
SQL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup mount
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 511708752 bytes
Database Buffers 331350016 bytes
Redo Buffers 5132288 bytes
Database mounted.
SQL
Lastly all that is needed it set archive log mode and open the database.
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 /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 25
Next log sequence to archive 27
Current log sequence 27
SQL
We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well. In Oracle 9i an earlier another parameter needed to be set in order to enable automatic archiving. This in no longer the case in 10g and 11g as automatic archiving is enabled when the database is placed in archive log mode.
You can switch to the log file to see that an archive is written to archive log location.
SQL alter system switch logfile;
System altered.
SQL host
oracle@ora1 ~$ ls /u02/app/oracle/oradata/orcl/arch
1_27_711369564.dbf
oracle@ora1 ~$ exit
exit
SQL
The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
oracle@ora1 ~$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:54:05 2010
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, OLAP, Data Mining and Real Application Testing options
SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
he Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.
SQL shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL startup mount
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 511708752 bytes
Database Buffers 331350016 bytes
Redo Buffers 5132288 bytes
Database mounted.
SQL
All that is left is to disable archive log mode and open the database.
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 /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence 26
Current log sequence 28
SQL