Information about Archive logs and redo logs

1. To check the lgwr process [which moves changed vectors from log buffer (memory) to redo logs (disk)]

ps -ef |grep lgwr

2. To check the # of redo logs and their location, use v$logfile

set linesize 200

set pagesize 200

col member format a60

select

group#,

member

from

v$logfile

order by 1;

3. How to check if the database is in archivelog mode or no archivelog mode?

SQL> archive log list

4. How to perform a manual log switch?

SQL> Alter system switch logfile;

5.  Find the group# and redolog name.

select group#, member from v$logfile;

6. How to check the size and other details of redo logs?

set linesize 200

set pagesize 200

select

to_char(first_time, 'DD-MON-YY HH24:MI:SS') as LogSwitch_Time,

group#,

sequence#,

bytes/1024/1024 as Size_IN_MB,

First_Change# as First_SCN,

Next_Change# as Last_SCN,

Next_change#-First_change# as SCN_Difference

from

v$log

order by

Sequence#;

or

set linesize 200

column member format a50

break on group# skip 1

select

    a.group#,

    a.member,

    b.bytes/1024/1024 as Size_in_MB,

    b.status,

    b.archived as "Is it Archived?",

    b.Sequence#,

    to_char(b.first_time, 'Mon-DD-YY HH24:Mi:SS') as Log_Switch_Time

from

    v$logfile a,

    v$log b

where a.group#=b.group#

order by 6,2;

7. Example of path of archive log destination

show parameter log_archive_dest_1

show parameter log_archive_dest_2

8. Example of how to clear log archive destination.

alter system set log_archive_dest_2='';

9. Example of adding different log_archive_dest locations.

alter system set log_archive_dest_2='LOCATION=/tmp/orcl1/Archive';

alter system set log_archive_dest_3='LOCATION=/tmp/orcl3/Archive';

alter system set log_archive_dest_4='LOCATION=/tmp/orcl4/Archive';