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.
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;
6 comments:
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/fast_recovery_area/ORA12/onlinelog/o1_mf_1_dw9fqrwm_.log';
file path
select distinct substr(name, 1, instr(name, '/',-1)) PATH
from (select name from v$datafile
union all
select NAME from v$controlfile
union all
select MEMBER from v$logfile
union all
select name from v$tempfile) ;
http://anuj-singh.blogspot.co.uk/2011/10/oracle-redo-log-files-info.html
alter database add standby logfile group 10 '/u02/oradata/stdby_redo10a.log' size 52428800;
alter database add standby logfile group 11 '/u02/oradata/stdby_redo11a.log' size 52428800;
alter database add standby logfile group 12 '/u02/oradata/stdby_redo12a.log' size 52428800;
SQL> alter database rename file '+DATA/orclse/redo01.log' to '/u01/app/oradata/Orclsed/redo01.log';
Database altered.
SQL> alter database rename file '+DATA/orclse/redo02.log' to '/u01/app/oradata/Orclsed/redo02.log';
Database altered.
SQL> alter database rename file '+DATA/orclse/redo03.log' to '/u01/app/oradata/Orclsed/redo03.log';
Database altered.
Oracle 10g
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#
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#
from v$logfile lf , v$standby_log l
where l.group# = lf.group#
order by 6;
Post a Comment