Oracle online redo logs and standby logs Info ..  

Oracle log file info ..
logfiles.sql
standby logs ...

Syntax to add /drop group are as Below

 SQL>Alter database add standby logfile Thread <thread no> group <group no> <PATH> size <>M;

SQL>Alter database add logfile Thread <thread no> group <group no>  <PATH> size <>M;

Handling ORL and SRL (Resize) on Primary and Physical Standby in Data Guard Environment (Doc ID 1532566.1)

set linesize 200 pagesize 200
column member format a50
column first_change# format 99999999999999999999
column next_change# format 99999999999999999999
select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$log l
where  l.group# = lf.group#
order by l.thread#,lf.group#, lf.member;

with time !!!

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

set linesize 300 pagesize 200

column member         format a50

column first_change#  format 99999999999999999999

column next_change#   format 99999999999999999999

select l.thread#,

       lf.group#,

       lf.member,

       trunc(l.bytes/1024/1024) as size_mb,

       l.status,

       l.archived,

       lf.type,

       lf.is_recovery_dest_file as rdf,

       l.sequence#,

       l.first_change#,

       l.next_change# ,

FIRST_TIME,NEXT_TIME 

from   v$logfile lf , v$log l

where  l.group# = lf.group#

order by l.thread#,lf.group#, lf.member;

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
set linesize 300 pagesize 200
column member         format a90
column first_change#  format 99999999999999999999
column next_change#   format 99999999999999999999
col group_size for a10
select l.thread#,
       lf.group#,
       lf.member,
       CASE WHEN l.bytes < 1024          THEN l.bytes||''
WHEN l.bytes < POWER(1024,2) THEN ROUND(l.bytes/POWER(1024,1),1)||'K'
WHEN l.bytes < POWER(1024,3) THEN ROUND(l.bytes/POWER(1024,2),1)||'M'
WHEN l.bytes < POWER(1024,4) THEN ROUND(l.bytes/POWER(1024,3),1)||'G'
WHEN l.bytes < POWER(1024,5) THEN ROUND(l.bytes/POWER(1024,4),1)||'T'
 END group_size	 ,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change# ,
FIRST_TIME,NEXT_TIME 
from   v$logfile lf , v$log l
where  l.group# = lf.group#
order by l.thread#,lf.group#, lf.member;

====================

For standby logs ...

set linesize 200 pagesize 200
column member            format a50
column first_change#   format 99999999999999999999
column next_change#   format 99999999999999999999
select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$standby_log l
where  l.group# = lf.group#
order by l.thread#,lf.group#, lf.member;



select * from v$logfile where type='STANDBY';

set linesize 300 pagesize 200

column member            format a70

column first_change#   format 99999999999999999999

column next_change#   format 99999999999999999999

select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$log l
where  l.group# = lf.group#
union 
select l.thread#,
       lf.group#,
       lf.member,
       trunc(l.bytes/1024/1024) as size_mb,
       l.status,
       l.archived,
       lf.type,
       lf.is_recovery_dest_file as rdf,
       l.sequence#,
       l.first_change#,
       l.next_change#   
from   v$logfile lf , v$standby_log l
where  l.group# = lf.group#
order by 7;

======
to create redo for standby  ..

select 'alter database add standby logfile '''||regexp_substr(MEMBER,'/.+/')||'stdby_'||regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||''' size '||bytes||';' "Create Standby redo" from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/

==============



set linesize 300 pagesize 300
COL GROUP#       FORMAT 999999 HEAD 'Group'
COL THREAD#        FORMAT 999999 HEAD 'Thread'
COL SEQUENCE#       FORMAT 99999999 HEAD 'Sequence'
COL size                FORMAT A6 HEAD 'Size|Mb'
COL STATUS        FORMAT A10
COL MEMBER        FORMAT A50
COL switch_dt        FORMAT A20
COL ARCHIVED        FORMAT A7
BREAK ON THREAD# NODUP ON GROUP# NODUP SKIP 1 ON sizeMb NODUP ON switch_dt NODUP
COMPUTE NUMBER LABEL 'MEMBER:' OF SEQUENCE# ON GROUP#

SELECT L.GROUP#, L.THREAD#, L.SEQUENCE#, LPAD(TRUNC(BYTES/1024/1024),5) sizeMb, L.STATUS, MEMBER, TO_CHAR(FIRST_TIME, 'dd-mm-yyyy HH24:MI:SS') switch_dt,
DECODE(ARCHIVED,'YES','Done..','Non') ARCHIVED
FROM V$LOG L, V$LOGFILE F
WHERE L.GROUP# = F.GROUP#
ORDER BY 1,3,6;

=============

ORA-01624: log 2 needed for crash recovery of instance

ORA-00312: online log 2 thread 

 alter database drop logfile group 2;

alter database drop logfile group 2

*

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance vihcdbd8 (thread 1)

ORA-00312: online log 2 thread 1: '/dumps/vihcdbd8_DATABASE/logfile_2'

 alter system checkpoint;  ----<<<<<<<<<<<<<<<<<<<<<<

System altered.

then drop 

 alter database clear logfile group 2;

Database altered.

begin
for log_cur in ( select group# group_no from v$log )
loop
execute immediate 'alter database clear logfile group '||log_cur.group_no;
end loop;
end;
/

to rename ...

define 1='/u01/app/oracle/Datafile/'

set linesize 200

 col file_name for a150

 select 'ALTER DATABASE RENAME FILE ''' ||member|| ''' to '||'''&1'|| substr(member,instr(member,'/',-1)+1, instr(substr(member,instr(member,'/',-1)+1),'.')-1 )||'.log' ||''' ;' file_name

from V$logfile;

====

-- standby log only 

set lines 200 pages 9999

col thread# for 9999990

col sequence# for 999999990

col grp for 990

col fnm for a50 head "File Name"

col "Fisrt SCN Number" for 999999999999990

break on thread

select a.thread#

,a.sequence#

,a.group# grp     

,a.bytes/1024/1024 Size_MB     

,a.status     

,a.archived     

,a.first_change# "First SCN Number"     

,to_char(FIRST_TIME,'DD-Mon-yyyy HH24:MI:SS') "First SCN Time"   

,to_char(LAST_TIME,'DD-Mon-yyyy HH24:MI:SS') "Last SCN Time"  from

v$standby_log a  order by 1,2,3,4

 /

alter database add standby logfile  group 15 '+DATA' size 1024m;

alter database add standby logfile  group 16 '+DATA' size 1024m;

 THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS       ARC First SCN Number First SCN Time                Last SCN Time

-------- ---------- ---- ---------- ------------ --- ---------------- ----------------------------- -----------------------------

       0          0   15       1024 UNASSIGNED   YES

       0          0   16       1024 UNASSIGNED   YES

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 15;

Database altered.

SQL> ALTER DATABASE DROP STANDBY LOGFILE GROUP 16;

Database altered.

on a single instance, use the thread number while  create Standby Redo Log statement.

 with thread !!!  

With thread 

SQL> alter database add standby logfile thread 1 group 17 '+DATA' size 1048m;

Database altered.

alter database add standby logfile thread 1 group 16 '+DATA' size 1048m;

Database altered.

Start the database at mount stage

Clear unarchive log file

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

Start again the database in open.

Check the status 

set linesize 500

col "redo file name" for a60

SELECT group#,l.thread#,l.sequence#,l.archived,l.status,V.MEMBER "redo file name",bytes/1024/1024 "size in MB"

FROM V$LOG L JOIN V$LOGFILE V USING (GROUP#) ORDER BY GROUP#;

===

ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic

alter system set standby_file_management=MANUAL;

SRDC - Collect Data Guard Diagnostic Information (Doc ID 2219763.1) srdc_DGPhyStby_diag.sql(14.01 KB) srdc_DGPrimary_diag.sql(21.03 KB)