Oracle DBA – A lifelong learning experience

Posts Tagged ‘v$standby_log’

Scripts to resize standby redolog files

Posted by John Hallas on February 28, 2011

I have already posted about an issue that required me to drop and recreate standby log files so I thought I would post the scripts I used.

Resize Standby Redo Logs

1. On primary defer log shipping (dynamic change)

alter system set log_archive_dest_state_2 = defer scope = memory;

2. On standby database cancel managed recovery

alter database recover managed standby database cancel;

3. Drop standby logs on standby database

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;

4. Recreate the new Standby logs

alter database add standby logfile THREAD 1 group 4 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;

alter database add standby logfile THREAD 1 group 5 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;

alter database add standby logfile THREAD 1 group 6 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;

alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 1000M;

5. Enable log shipping on the Primary database

alter system set log_archive_dest_state_2 = enable scope = memory;

6. Enable managed recovery on standby database

alter database recover managed standby database using current logfile disconnect;

7. Check the the standby logs are being used by running following query :

set lines 155 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
# skip 1
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-RR HH24:MI:SS') "First SCN Time"   
,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time"  from
 v$standby_log a  order by 1,2,3,4
 /

Should return the following :

THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS     ARC Fisrt SCN Number First SCN Time              Last SCN Time
-------- ---------- ---- ---------- ---------- --- ---------------- --------------------------- ---------------------------
       1          0    4        100 UNASSIGNED NO                 0
                  0    6        100 UNASSIGNED YES                0
                  0    7        100 UNASSIGNED YES                0
               7316    5        100 ACTIVE     YES        153517071 04-Feb-11 13:39:32          04-Feb-11 13:40:41
Advertisements

Posted in Oracle, scripts | Tagged: , , , , | 7 Comments »

Managing dataguard – monitoring scripts

Posted by John Hallas on July 16, 2009

I am providing three scripts which I find useful in ensuring that the standby database is keeping up with the primary database in Dataguard setup.

@last

LOGS             TIME
---------------- ------------------
Last applied   :  16-JUL-09:09:24:16
Last received :  16-JUL-09:09:28:36
    select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);

@dg_stats

NAME                   VALUE                  UNIT
---------------------- ---------------------- -----------------------------------
apply finish time      +00 00:02:07.2         day(2) to second(1) interval
apply lag              +00 00:01:59           day(2) to second(0) interval
estimated startup time 16                     second
standby has been open  N
transport lag          +00 00:00:00           day(2) to second(0) interval

                                              Time Computed: 16-JUL-2009 09:33:16

   

select
    NAME Name,
    VALUE Value,
    UNIT Unit
    from v$dataguard_stats
    union
    select null,null,' ' from dual
    union
    select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
   from v$dataguard_stats;

A final option is to use v$standby_log

@last_redo

Redo onsite
--------------------
16-JUL-2009 09:42:44

   

select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "Redo onsite"
     from v$standby_log

None of them that clever, but all of them are useful.  Thanks to colleague Simon Ryan for pulling them together.

Posted in Oracle | Tagged: , , , , , | Leave a Comment »