Erman Arslan's Oracle Forum › Database

Dataguard logs

Classic

List

Threaded

16 messages Options

Roshan

Dataguard logs

Hello Erman,

I am doing a healthcheck on a database where dataguard is implemented(Oracle 9i)

SQL> select sequence#
from v$archived_log
where standby_dest='NO'
minus
select sequence#
from v$archived_log
where standby_dest='YES'
minus
select min(sequence#) from v$archived_log 2 3 4 5 6 7 8 9
10 ;

SEQUENCE#
----------
386140
386141
386176
386204
386205
386206
386207
386208
386226
386273
386303

SEQUENCE#
----------
386304
386305
386306
386307
386308
386309
386310
386311
386381
386382
386383

SEQUENCE#
----------
386384
386385
386386
386387
386388
386404
386507
386508
386509
386510
386511
....

Do I need to transfer the archived redo with above thread number to standby database?

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2 OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST
---------- ----------
1 387993

Can you please advise
Regards,
Roshan

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

Send me the following;

In primary:

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

SELECT THREAD#, MAX(SEQUENCE#)

FROM V$LOG_HISTORY

WHERE RESETLOGS_CHANGE# =

(SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT')

GROUP BY THREAD#

In Standby:

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

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"

from (select thread# thrd, max(sequence#) almax

from v$archived_log

where resetlogs_change#=(select resetlogs_change# from v$database)

group by thread#) al,

(select thread# thrd, max(sequence#) lhmax

from v$log_history

where first_time=(select max(first_time) from v$log_history)

group by thread#) lh

where al.thrd = lh.thrd;

2016-11-01 13:35 GMT+02:00 Roshan [via Erman Arslan's Oracle Forum] <[hidden email]>:

Hello Erman,

I am doing a healthcheck on a database where dataguard is implemented(Oracle 9i)

SQL> select sequence#
from v$archived_log
where standby_dest='NO'
minus
select sequence#
from v$archived_log
where standby_dest='YES'
minus
select min(sequence#) from v$archived_log 2 3 4 5 6 7 8 9
10 ;

SEQUENCE#
----------
386140
386141
386176
386204
386205
386206
386207
386208
386226
386273
386303

SEQUENCE#
----------
386304
386305
386306
386307
386308
386309
386310
386311
386381
386382
386383

SEQUENCE#
----------
386384
386385
386386
386387
386388
386404
386507
386508
386509
386510
386511

Do I need to transfer the archived redo with above thread number to standby database?

SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
2 OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

THREAD LAST
---------- ----------
1 387993

Can you please advise
Regards,
Roshan

If you reply to this email, your message will be added to the discussion below:
http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Dataguard-logs-tp1478.html

To start a new topic under Database, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

Erman Arslan, MBA

Applications and Database Operations Manager

Oracle Certified Expert, Certified Exadata and Linux Administrator

Author, Practical Oracle E-Business Suite

Blog: ermanarslan.blogspot.com

Forum: http://ermanarslan.blogspot.com/p/forum.html

Mobile: 05301567803

Roshan

Re: Dataguard logs

It is Oracle Database 9i

In Primary ----
ora920 SQL>SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD# 2 3 4 5 6 7
8 ;
WHERE RESETLOGS_CHANGE# =
*
ERROR at line 3:
ORA-00904: "RESETLOGS_CHANGE#": invalid identifier

ora920 SQL>desc V$LOG_HISTORY
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NUMBER
STAMP NUMBER
THREAD# NUMBER
SEQUENCE# NUMBER
FIRST_CHANGE# NUMBER
FIRST_TIME DATE
NEXT_CHANGE# NUMBER

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

Use this note:

Script to Collect Data Guard Primary Site Diagnostic Information for Version 9i (Doc ID 241374.1)

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

For 9i; In short you will check the applied logs using the below query;

run this query in both primary and standby site and compare the result.

select sequence#, applied,
to_char(first_time, 'mm/dd/yy hh24:mi:ss') first
from v$archived_log
order by first_time;

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

Also, check,

1. Check for gaps in logs on standby;

On standby -> SQL>select * from v$archive_gap;

Reference: Business Continuity for Oracle Applications Release 11i, Database Releases 9i and 10g (Doc ID 216212.1)

If this query returns a row, it indicates at least one archived redo log is missing from the standby.
If you still have access to your production database, you can determine the full name of the redo logs by querying v$archived_log, using the low_sequence# and high_sequence# returned above:

SQL>select name from v$archived_log
where thread# = <thread# from above query>
and sequence# between <low_sequence# above> and <high_sequence# above>;

Locate the missing logs and copy them to the standby server’s standby redo log destination, then register them:Check for maximum possible log

If you still have access to your production database, or if there are other standby databases available, find the maximum possible log that could be applied to the standby you will fail over to. On the available databases, issue this query:

SQL>select unique thread# as thread, max(sequence#)
over (partition by thread#)
as last from v$archived_log;
If you identify more missing logs, copy them to the appropriate directory and register them as described above.

Roshan

Re: Dataguard logs

Thanks Erman :)

Roshan

Re: Dataguard logs

In reply to this post by ErmanArslansOracleBlog

Hi Erman,

the logs are being shipped to standby but I do not see applied.

standby DB:
37537 NO 08/18/20 18:55:04
37538 NO 08/18/20 18:56:16
42713 NO 08/18/20 19:02:17
42714 NO 08/18/20 19:02:49
37539 NO 08/18/20 19:02:50
42715 NO 08/18/20 19:02:53
37540 NO 08/18/20 19:08:44
37541 NO 08/18/20 19:09:59
42716 NO 08/18/20 19:17:22

SEQUENCE# APPLIED FIRST
---------- --------- -----------------
37542 NO 08/18/20 19:36:32
37543 NO 08/18/20 19:39:29
37544 NO 08/18/20 19:41:14
42717 NO 08/18/20 19:41:16
42718 NO 08/18/20 19:50:19
37545 NO 08/18/20 19:51:11
37546 NO 08/18/20 19:54:32
37547 NO 08/18/20 19:55:32
42719 NO 08/18/20 19:55:35
42720 NO 08/18/20 20:04:02
37548 NO 08/18/20 20:08:14

SEQUENCE# APPLIED FIRST
---------- --------- -----------------
37549 NO 08/18/20 20:09:59
37550 NO 08/18/20 20:29:24
42721 NO 08/18/20 20:29:26
42722 NO 08/18/20 20:39:15
37552 NO 08/18/20 20:39:33

28000 rows selected.

PRIMARY DB:

SEQUENCE# APPLIED FIRST
---------- --------- -----------------
42718 NO 08/18/20 19:50:19
42718 NO 08/18/20 19:50:19
37545 NO 08/18/20 19:51:11
37545 NO 08/18/20 19:51:11
37546 NO 08/18/20 19:54:32
37546 NO 08/18/20 19:54:32
37547 NO 08/18/20 19:55:32
37547 NO 08/18/20 19:55:32
42719 NO 08/18/20 19:55:35
42719 NO 08/18/20 19:55:35
42720 NO 08/18/20 20:04:02

SEQUENCE# APPLIED FIRST
---------- --------- -----------------
42720 NO 08/18/20 20:04:02
37548 NO 08/18/20 20:08:14
37548 NO 08/18/20 20:08:14
37549 NO 08/18/20 20:09:59
37549 NO 08/18/20 20:09:59
37550 NO 08/18/20 20:29:24
37550 NO 08/18/20 20:29:24
42721 NO 08/18/20 20:29:26
42721 NO 08/18/20 20:29:26
37551 NO 08/18/20 20:37:06
37551 NO 08/18/20 20:37:06

SEQUENCE# APPLIED FIRST
---------- --------- -----------------
42722 NO 08/18/20 20:39:15
42722 NO 08/18/20 20:39:15
37552 NO 08/18/20 20:39:33
37552 NO 08/18/20 20:39:33
37553 NO 08/18/20 20:39:51

28000 rows selected.

Also, I checked v$archive_gap and i see no records. Why are archivelogs not applied?

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

Run the following on primary ->

SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#;

check the "max(Sequence#)"

Run the following on standby ->

> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd

Check "Last Seq Applied"

If "max(Sequence#)" returned from primary and "Last Seq Applied" returned from standby is the same. Then you are ok. You are sync.

Roshan

Re: Dataguard logs

Primary:
SQL> SELECT THREAD#, MAX(SEQUENCE#)
FROM V$LOG_HISTORY
WHERE RESETLOGS_CHANGE# =
(SELECT RESETLOGS_CHANGE#
FROM V$DATABASE_INCARNATION
WHERE STATUS = 'CURRENT')
GROUP BY THREAD#; 2 3 4 5 6 7

THREAD# MAX(SEQUENCE#)
---------- --------------
1 37629
2 42813

target:
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd 2 3 4 5 6 7 8 9 10
11 ;

Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
2 42813 42774

I noticed the mrp0 is not running:
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 42813
ARCH CONNECTED 0
ARCH CLOSING 42811
ARCH CLOSING 42812
RFS RECEIVING 37603
RFS RECEIVING 37604
RFS RECEIVING 42791
RFS RECEIVING 42814
RFS RECEIVING 42792
RFS RECEIVING 42790
RFS RECEIVING 37590

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS RECEIVING 37631

Roshan

Re: Dataguard logs

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/data/DATA/DWAREDR/datafile/o1_mf_system_hmsrw5sl_.dbf'

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

RFS is still receiving the logs and MRP is not running so recovery is not running.
Check the logs ( alert log and etc) to find the cause.

Roshan

Re: Dataguard logs

Errors in file /u01/app/ora12c/diag/rdbms/dwaredr/dware1/trace/dware1_m000_7560.trc:
ORA-00313: open failed for members of log group 17 of thread 2
ORA-00312: online log 17 thread 2: '+DATA/DWARE1/ONLINELOG/group_17.543.1018688819'
ORA-17503: ksfdopn:2 Failed to open file +DATA/DWARE1/ONLINELOG/group_17.543.1018688819
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15064: communication failure with ASM instance
WARNING: Clusterware is not configured.
WARNING: Clusterware is not configured.
Completed: alter database recover managed standby database disconnect from session

The restore was from ASM to standalone filesystem.

Roshan

Re: Dataguard logs

1 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_1_hmsylp8r_.log NO 0

2 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_2_hmsylt1p_.log NO 0

3 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_3_hmsylyg2_.log NO 0

4 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_4_hmsym2l8_.log NO 0

16 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_16_hmsym69c_.log NO 0

17 ONLINE +DATA/DWARE1/ONLINELOG/group_17.543.1018688819 NO 0

18 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_18_hmsymb0w_.log NO 0

19 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_19_hmsymfqo_.log NO 0

21 STANDBY /data/DATA/dware1/log/stby_redo21.log NO 0

22 STANDBY /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_22_hmsx395b_.log NO 0

23 STANDBY /data/DATA/dware1/log/stby_redo23.log NO 0

24 STANDBY /data/DATA/dware1/log/stby_redo24.log NO 0

25 STANDBY /data/DATA/dware1/log/stby_redo25.log NO 0

26 STANDBY /data/DATA/dware1/log/stby_redo26.log NO 0

27 STANDBY /data/DATA/dware1/log/stby_redo27.log NO 0

28 STANDBY /data/DATA/dware1/log/stby_redo28.log NO 0

29 STANDBY /data/DATA/dware1/log/stby_redo29.log NO 0

30 STANDBY /data/DATA/dware1/log/stby_redo30.log NO 0

31 STANDBY /data/DATA/dware1/log/stby_redo31.log NO 0

32 STANDBY /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_32_hmsx4bhm_.log NO 0

33 STANDBY /data/DATA/dware1/log/stby_redo33.log NO 0

34 STANDBY /data/DATA/dware1/log/stby_redo34.log NO 0

35 STANDBY /data/DATA/dware1/log/stby_redo35.log NO 0

36 STANDBY /data/DATA/dware1/log/stby_redo36.log NO 0

37 STANDBY /data/DATA/dware1/log/stby_redo37.log NO 0

38 STANDBY /data/DATA/dware1/log/stby_redo38.log NO 0

39 STANDBY /data/DATA/dware1/log/stby_redo39.log NO 0

40 STANDBY /data/DATA/dware1/log/stby_redo40.log NO 0

41 STANDBY /data/DATA/dware1/log/stby_redo41.log NO 0

42 STANDBY /data/DATA/dware1/log/stby_redo42.log NO 0

5 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_5_hmsymk89_.log NO 0

6 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_6_hmsymo9b_.log NO 0

7 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_7_hmsyms2h_.log NO 0

8 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_8_hmsymwng_.log NO 0

9 ONLINE +DATA/DWARE1/ONLINELOG/group_9.535.1018688171 NO 0

10 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_10_hmsyn0f2_.log NO 0

11 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_11_hmsyn4vm_.log NO 0

12 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_12_hmsync3o_.log NO 0

13 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_13_hmsynhv8_.log NO 0

14 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_14_hmsynmt4_.log NO 0

15 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_15_hmsynqyt_.log NO 0

20 ONLINE /data/DATA/dware1/log/DWAREDR/onlinelog/o1_mf_20_hmsynvwx_.log NO 0

Roshan

Re: Dataguard logs

seems good now after recreating the log files. :)

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CLOSING 37643
ARCH CONNECTED 0
ARCH CLOSING 42821
ARCH CLOSING 37622
RFS IDLE 0
RFS IDLE 0
RFS RECEIVING 42744
RFS IDLE 42824
RFS RECEIVING 42745
RFS RECEIVING 42743
RFS IDLE 0

PROCESS STATUS SEQUENCE#
--------- ------------ ----------
RFS RECEIVING 37644
MRP0 WAIT_FOR_LOG 42743

ErmanArslansOracleBlog

Re: Dataguard logs

Administrator

Yes. exactly what you did should have been done :) Good for you Roshan.

If you have other issues, please create seperate threads/issues and ask your questions.