Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Tuesday, June 21, 2011

How to enable and disable archivelog mode in an Oracle database

Although this is a very basic info, I just wanted to express it again and give some tips about it.

1-) Enable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:15:49 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Current log sequence           429

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             373295800 bytes
Database Buffers           75497472 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Enable archivelog in the database
SQL> alter database archivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     427
Next log sequence to archive   429
Current log sequence           429

Do you know where the newly generated archivelogs are going to from now on, if there is any "log_archive_dest_n" parameter set and its "log_archive_des_n_state" is "enable" then all the newly generated archivelogs goes to that destination.


SQL> show parameter log_archive_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string
log_archive_dest_2                   string
log_archive_dest_20                  string
log_archive_dest_21                  string
log_archive_dest_22                  string
log_archive_dest_23                  string
log_archive_dest_24                  string
log_archive_dest_25                  string
log_archive_dest_26                  string
log_archive_dest_27                  string
log_archive_dest_28                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_29                  string
log_archive_dest_3                   string
log_archive_dest_30                  string
log_archive_dest_31                  string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_10            string      enable
log_archive_dest_state_11            string      enable
log_archive_dest_state_12            string      enable
log_archive_dest_state_13            string      enable
log_archive_dest_state_14            string      enable
log_archive_dest_state_15            string      enable
log_archive_dest_state_16            string      enable
log_archive_dest_state_17            string      enable
log_archive_dest_state_18            string      enable
log_archive_dest_state_19            string      enable
log_archive_dest_state_2             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable
log_archive_dest_state_3             string      enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_30            string      enable
log_archive_dest_state_31            string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL> 

As you can see above there is no explicitly set "log_archive_dest_n", then archivelogs will go to the destination set in Oracle database parameter "db_recovery_file_dest" because "log_archive_dest_10" parameter implicitly set to "db_recovery_file_dest" parameter value, if there is no explicitly set "log_archive_dest_n" parameter. This is also the reason why we see in the above output "Archive destination USE_DB_RECOVERY_FILE_DEST", means the destination set in the "db_recovery_file_dest" parameter value.

Lets check that "db_recovery_file_dest" parameter value and see that it points to flash recovery area.

SQL> sho parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /home/oracle/app/oracle/flash_
                                                 recovery_area
db_recovery_file_dest_size           big integer 3852M

Check that destination
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/flash_recovery_area/ORCL
[oracle@localhost ORCL]$ ls -l
total 4
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$

There is a folder named "ORCL"(value of $ORACLE_SID) below that folder.There is no "archivelog" folder yet, since there is no archivelogs generated yet.

Lets force generation of some archivelogs
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

Check the archivelog destination again
[oracle@localhost ORCL]$ ls -l
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 21 06:37 archivelog
drwxr-x--- 2 oracle oracle 4096 Oct 30  2009 onlinelog
[oracle@localhost ORCL]$ cd archivelog/
[oracle@localhost archivelog]$ ls -l
total 4
drwxrwx--- 2 oracle oracle 4096 Jun 21 06:37 2011_06_21
[oracle@localhost archivelog]$ cd 2011_06_21/
[oracle@localhost 2011_06_21]$ ls -l
total 2756
-rw-rw---- 1 oracle oracle 2808320 Jun 21 06:37 o1_mf_1_429_7017nnrw_.arc
-rw-rw---- 1 oracle oracle    1024 Jun 21 06:37 o1_mf_1_430_7017nq5w_.arc
-rw-rw---- 1 oracle oracle    2560 Jun 21 06:37 o1_mf_1_431_7017nrxo_.arc
[oracle@localhost 2011_06_21]$
As you can see Oracle created a folder named "archivelog" and a subfolder with today's date "2011_06_21" and put the newly generated archivelogs there.

If you want, you can explicitly set a new archivelog destination instead of implicitly set flash recovery area like below.

First create the new destination on the server
[oracle@localhost oracle]$ pwd
/home/oracle/app/oracle
[oracle@localhost oracle]$ mkdir arc2
[oracle@localhost oracle]$ cd arc2
[oracle@localhost arc2]$ mkdir ORCL
[oracle@localhost arc2]$ cd ORCL
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ 

Now set "log_archive_dest_1" parameter to this new destination, you can do this while the database is in open mode.
SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/app/oracle/arc2/ORCL';

System altered.

--Check the "log_archive_dest_1" parameter
SQL> sho parameter log_archive_dest_1 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/home/oracle/app/orac
                                                 le/arc2/ORCL
log_archive_dest_10                  string
log_archive_dest_11                  string
log_archive_dest_12                  string
log_archive_dest_13                  string
log_archive_dest_14                  string
log_archive_dest_15                  string
log_archive_dest_16                  string
log_archive_dest_17                  string
log_archive_dest_18                  string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19                  string

--Now check the archivelog mode state, as you can see we see the new destination as "Archive destination            /home/oracle/app/oracle/arc2/ORCL"
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     430
Next log sequence to archive   432
Current log sequence           432
SQL> 

From now on, all the newly generated archivelogs will go to that new destination.

There is no archivelog in the new destination for now
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 0
[oracle@localhost ORCL]$ 

Generate some new archivelogs
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

Check the new destination and see the newly generated archivelogs

[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf
[oracle@localhost ORCL]$

2-) Disable archivelog mode :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 06:56:38 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--Check current archivelog mode in database
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Next log sequence to archive   435
Current log sequence           435

--Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;

System altered.

--Shutdown the database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--Start the database in mount mode
SQL> startup mount
ORACLE instance started.

SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.

--Disable archivelog in the database
SQL> alter database noarchivelog;

Database altered.

--Open the database
SQL> alter database open;

Database altered.

--Check the current archivelog mode in the database
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     433
Current log sequence           435

As you can see we disabled the archivelog mode in the database, there will not be any newly generated archivelogs in the archivelog destination although it is still set in the database.

Switch some logfiles
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> 

As you can see there is no newly generated archivelogs in the archivelog destination.
[oracle@localhost ORCL]$ pwd
/home/oracle/app/oracle/arc2/ORCL
[oracle@localhost ORCL]$ ls -l
total 816
-rw-rw---- 1 oracle oracle 822272 Jun 21 06:53 1_432_701609923.dbf
-rw-rw---- 1 oracle oracle   2560 Jun 21 06:53 1_433_701609923.dbf
-rw-rw---- 1 oracle oracle   3072 Jun 21 06:53 1_434_701609923.dbf

3-) Additional info :

Do not close the database in a way that it will require instance recovery(e.g. shutdown abort) before you will mount it for archivelog mode change otherwise you will get the below error "ORA-00265", always try to shutdown the database gracefully by using "shutdown immediate".

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:07:41 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> 

If there is a little chance that you can close the database with "shutdown immediate" because of the heavy activity on the database, you can use the below method to bring the database to the mount mode without causing any instance recovery.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 21 07:11:07 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system checkpoint;

System altered.

SQL> shu abort
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
Database opened.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             381684408 bytes
Database Buffers           67108864 bytes
Redo Buffers                6008832 bytes
Database mounted.
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            /home/oracle/app/oracle/arc2/ORCL
Oldest online log sequence     438
Next log sequence to archive   440
Current log sequence           440
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ 

When you open the database with "startup restrict", only the users with "Restricted Session" system privilege (SYS user is one of them) can connect to database, since all the other client sessions will not be able to connect to the database, you will be able to shutdown the database faster with "shutdown immediate".

No comments: