--- Archive Redo Logs see Oracle Server Administrator's Guide (Chap. Archiving Redo Information) for more information
To switch the database to noarchivelog :
shutdown;
startup mount;
alter database noarchivelog;
alter database open;
To switch the database to archive log mode:
shutdown;
startup mount;
alter database archivelog;
alter database open;
To enable automatic archiving of redo logs set log_archive_start=true in init.ora file.
To enable automatic archiving after instance startup:
alter system archive log start;
To switch the logs:
alter system switch logfile;
Destination of archive logs:
log_archive_dest=/dir/
To change the destination of archive files shutdown the instance, change the parm in init.ora and then restart the instance
Format of archive file names:
log_archive_format=arch%t_%s.arc
%s is the sequence number
%t is the thread number
-- Redo's see Oracle Server Administrator's Guide (Chap. Managing the Online Redo Log)
-- How should you size the redo logs:
-- Considerations
-- If logs are to large:
- Then you MAY loose the active log and all its transactions, in case of media failure.
- How much data can you afford to loose in case of recovery? (If you use mirroring of the logs this risk is minimized)
-- If logs are to small:
- Each time oracle switches between logs Oracle does a lot of things (Checkpoint of the database is one of them).
This may cause performance degradation if it is to frequent. Another thing is that a log is not done archiving
when oracle wants to write transactions to it again, oracle waits (no transactions) until its freed.
A good rule of thumb is to size the redo logs so that Oracle archives about once each hour.
-- Altering logfile size after the database is set in production is not a problem. You just add the new files and drop the old ones.
-- Add online Redo Log Groups:
alter database add logfile group 1 ('/db02/oradata/DBSTAR/DBSTAR_log01.dbf') size 300M;
alter database add logfile group 2 ('/db03/oradata/DBSTAR/DBSTAR_log02.dbf') size 300M;
alter database add logfile group 3 ('/db04/oradata/DBSTAR/DBSTAR_log03.dbf') size 300M;
alter database add logfile group 1
('/db02/oradata/DBSTAR/DBSTAR_log01a.dbf','/db03/oradata/DBSTAR/DBSTAR_log01b.dbf') size 80M;
alter database add logfile group 2
('/db02/oradata/DBSTAR/DBSTAR_log02a.dbf','/db03/oradata/DBSTAR/DBSTAR_log02b.dbf') size 80M;
alter database add logfile group 3
('/db02/oradata/DBSTAR/DBSTAR_log03a.dbf','/db03/oradata/DBSTAR/DBSTAR_log03b.dbf') size 80M;
-- Drop online Redo Log Groups:
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
alter database drop logfile group 5;
--
If the instance cannot be shutdown because there are pending transactions and the archiver is not archiving for any reason
you can add a redo log to finish the tx's and then shutdown.
alter database add logfile group 4
('/db02/oradata/DBSTAR/DBSTAR_log04a.dbf','/db03/oradata/DBSTAR/DBSTAR_log04b.dbf') size 20M;
-- Data Dictionary queries:
Archiving information: select * from v$database;
Logs to be archived: select group#, archived from v$log;
select * from v$logfile;
Show archiving information: archive log list;
SQL> select * from V$ARCHIVE_DEST;
ARCMODE STATUS DESTINATION
------------ -------- ----------------------------------------------------------------------------------------------
MUST SUCCEED NORMAL /db01/app/oracle/admin/DBSTAR/arch/a
SQL> select * from v$archive;
GROUP# THREAD# SEQUENCE# CUR FIRST_CHANGE#
---------- ---------- ---------- --- -------------
1 1 19327 NO 110712590
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 19327 20971520 2 NO INACTIVE 110712590 2000-11-01:02:35:20
2 1 19328 20971520 2 NO CURRENT 110715338 2000-11-01:07:46:21
3 1 19326 20971520 2 YES INACTIVE 110712555 2000-11-01:02:15:11
