How to put an Oracle 9i and 10g and 11G database in Automatic Archive Log (ARCHIVELOG) Mode using Oracle Enterprise Manager Console or SQL Plus.

Article:TECH31192  |  Created: 2004-01-20  |  Updated: 2014-01-14  |  Article URL http://www.symantec.com/docs/TECH31192
Article Type
Technical Solution

Product(s)

Environment

Issue



How to put an Oracle 9i and 10g and 11G database in Automatic Archive Log (ARCHIVELOG) Mode using Oracle Enterprise Manager Console or SQL Plus.


Solution



Oracle 9i using Oracle Enterprise Manager Console

 

For putting an Oracle Database in Automatic Archival Mode, the parameter log_archive_start must be set to True in the initialization parameter file of the Oracle database in question. The following method can be useful for the users that prefer the GUI to the command line.

Please follow the following steps to enable the automatic archival option of an Oracle database:
 

  1. After starting the Oracle Enterprise Manager Console, connect to the Oracle database in question as SYSDBA (Figure 1). If it is not displayed in the left pane in the Oracle Enterprise Manager Console, then the required database should be added.


Figure 1:
 
 

  1. In the left pane, expand the Oracle database in question till Network | Databases | <Database name> | Instance | Configuration (Figure 2).


Figure 2:
 
 

  1. Now in the right pane, under General, click the All Initialization Parameters button (Figure 2). (This step is given assuming that the archive log mode and automatic archival options have already been selected and applied on the Recovery tab in the right pane in the above image.)
  2. Now select SPFile under the All Parameters section in the Edit Database: Configuration dialog box (Figure 3). Then search for the parameter log_archive_start under the parameter name and set its value to TRUE. Then click on Apply.

    Figure 3:
     

     
  3. Click Yes to restart the database which will apply the changes (Figure 4).

    Figure 4:
     

     
  4. Then respond Yes again to continue with the restart of the instance (Figure 5).

    Figure 5:
     

     
  5. Click OK to restart the database with the Automatic Archival option enabled (Figure 6).

    Figure 6:


    To check if the Automatic Archival option has been enabled or not, either

    1. Use SQL Plus and run the command  archive log list after connecting to the respective database as SYSDBA  

      Or

       
    2.  Run the Database Configuration Report through the Oracle Enterprise Manager Console by doing the following:

      1. In the left pane, browse down till Network | Databases | <Database name> | Instance | Configuration

      2. Click on the first icon of Create Report in the toolbar on the left pane (Figure 7)

      Figure 7:


      3. In the Database Configuration Report dialog box, just select the checkbox for Instance and then click the View button to see the report

      4. In the Database Configuration Report, browse down and check the value shown next to the parameter log_archive_start  that should be shown TRUE

 

ORACLE 10G using Oracle Enterprise Manager


Starting with Oracle Database 10g release 10.1, the LOG_ARCHIVE_START initialization parameter has been deprecated. Archiving is now automatically started when the database is placed in ARCHIVELOG mode.

To enable ARCHIVELOG mode in Oracle 10g (10.1):
 

  1. Check the Archive Log Mode (Figure 1)


Figure 1:
 

 

  1. To switch the database to Archive Log Mode .


Access the Enterprise Manager and Login as SYS as shown in Figure 2 .

Figure 2:
 


  1. If its the first time logging in, then a prompt for the licensing information will appear; Click "I Agree" to continue (Figure 3)


Figure 3:
 
 

  1. On the Homepage, various information about the database and instance can be accessed (Figure 4)  Archiving is disabled .


Figure 4:
 
 

  1. Click "Maintenance" to go to the Maintenance Page.  Click on "Configure Recovery Settings" (Figure 5)


Figure 5:
 
 

  1. In the Configure Recovery Settings Page "check"  the box "ARCHIVELOG Mode", accept the other default values and Click "Apply"  (Figure 6)


Figure 6:
 
 

  1. When prompted  to restart the database to reflect the changes  Click "Yes" to continue (Figure 7)


Figure 7:
 
 

  1. Fill in the required OS and DB Credentials to restart the database and click "OK"  (Figure 8)


Figure 8:
 
 

  1. Click "OK" to confirm the restart (Figure 9)


Figure 9:
 
 

  1. 10. Database restarting will now show.  Wait a few minutes and then click "Refresh" (Figure 10)


Figure 10:
 
 

  1. Database restarted Archiving has been enabled will appear now (Figure 11)


Figure 11:
 
 

  1. Confirm the Archive Log Mode Setting (Figure 12)


Figure 12:
 


Oracle 10g or 11i using sqlplus

 

Note: If you try to change the mode while the database is open, you will get the following error. Make sure that you obtain downtime for the database before this step.


SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance


 

Connect to the instance using SQL Plus and check the log mode. For example:
 

cd $ORACLE_HOME/bin
sqlplus / as sydba

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG
 

Change the database to mount state and then alter the log mode:
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  775946240 bytes
Fixed Size                  1981864 bytes
Variable Size             218104408 bytes
Database Buffers          553648128 bytes
Redo Buffers                2211840 bytes
Database mounted.
 

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

 

Note:  If the command is successfull but there are multiple databases on the oracle machine then the need to connect to to each database individually and apply this setting can be accompleshed by first attaching to the inidividual sid on the machine.

C:\Users\Administrator>set oracle_sid=Oracle03 (This is the individual database that caused the problem)

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 16 11:51:01 2012

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

SQL> connect /as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 3
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1720328192 bytes
Fixed Size 2176448 bytes
Variable Size 1006635584 bytes
Database Buffers 704643072 bytes
Redo Buffers 6873088 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 USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>exit

Once the editing has been done do not forget to set the oracle_sid back to the primary database of the system:

C:\Users\Administrator>set oracle_sid=Orcl
 




Legacy ID



266835


Article URL http://www.symantec.com/docs/TECH31192


Terms of use for this information are found in Legal Notices