Archived and redo logs - Examples
--- 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