Search This Blog

Sunday, August 8, 2010

Enabling Archive log mode in Oracle 10g and 9i.

First of all make sure that your database is started using spfile.
If you have created your database using DBCA then your database
will automatically start from spfile located in the dbs folder.

path of my spfile is "/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora"

You can check that by executing the following command.
show parameter spfile;
if the above command shows the path of the spfile that means your instance
was started using spfile.

or you can go to the following manual database creation link
and check the entry for spfile creation.

also make sure that you have flash recovery area configured.
if not configured then insert the following two entries in the pfile
and convert it to spfile. Also make sure that the folder structure
is already present and appropriate permissions are given to it.

db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
Now lets get started.

su - oracle # switch to oracle user.
export ORACLE_SID=orcl # export SID
sqlplus # enter into command prompt.
/ as sysdba # login as sys
alter system set log_archive_start=true scope=spfile;
# Enables automatic archiving.
# This parameter has been deprecated in Oracle 10g. But is 
# used in Oracle 9i.

alter system set log_archive_dest_1='location=/oracle/data'
scope=spfile;

# optional, you can create upto 9 alternative archive destinations.

alter system set log_archive_format='arch_%t_%s_%r.arc'
scope=spfile;

# optional, Specify the naming format of the archived log files.

shutdown immediate

startup mount
# startup the database in mount stage.

archive log start
# Start the archiving process.
# This parameter has been deprecated in Oracle 10G.
# But is required in Oracle 9i.

alter database archivelog;
# With this command the database will get into
# archive log mode from
# non-archive log mode.

alter database open;
# open the database

archive log list;
# check the archival status.

Force a archival by issuing any one of the following commands.

alter system switch logfile;
or
alter system archive log current;
 
Notes:

1) you can disable a archival destination by the following command.
alter system set log_archive_dest_state_1=defer;

to enable it againg execute

alter system set log_archive_dest_state_1=enable;
2) In the log_archive_format command

%s includes the log sequence number as part of the filename and
%t includes the thread number as part of the filename.
%r resetlogs ID that ensures unique names are constructed
for the archived log files across multiple incarnations
of the database.

3) The "archive log list;" command provides the DBA
with information about the log mode and status of
archiving for the database.

4) You can check the archival status and destination by issuing
the following query:

SQL> select name, archived from v$archived_log;

0 comments:

Post a Comment