Enable/Disable Archive Log Mode 10g/11g


If you would like to perform these tasks using Database Console see the post Enable/Disable Archive Log Mode 10g/11g using Database Console.

Enable Archive Log Mode

The following are the steps required to enable archive log mode on an Oracle 10g or 11g database.

Verify the database log mode.

2

3

4

5

6

7

8

9

10

11

12

13

[oracle@ora1 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:02:52 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              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.

2

3

4

5

6

7

8

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.

2

3

4

5

6

7

8

9

10

11

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.

2

3

4

5

6

7

8

9

10

11

12

13

14

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.

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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.

2

3

4

5

6

7

8

9

10

11

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>

Disable Archive Log Mode

Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.

The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.

Verify the database log mode.

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

[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

SQL>

The Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.

2

3

4

5

6

7

8

9

10

11

12

13

14

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.

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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>

As you can see, ARCHIVELOG mode has been disabled.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s