Requête SELECT de visualisation des REDO log Standby

Bonjour,

j'ai une requête que j'aimerai optimiser pour afficher les redo log que j'essai d'adapter pour ne voir que le redo log stanbdy, mais j'ai des doublons et je ne suis pas sur de la validité du résultat :

Citation:

---

set lines 999
set lines 999
col MEMBER for a80
col status for a20
col type for a15
COL l.BYTES/1024/1024 FORMAT 999,999,999.99 HEAD 'Taille en Mo'
select l.GROUP#,
l.THREAD#,
l.SEQUENCE#,
l.BYTES/1024/1024,
l.MEMBERS,
l.STATUS,
f.TYPE,
f.MEMBER
from V$LOGFILE f, V$LOG l
where type = 'STANDBY'
order by TYPE,GROUP#,MEMBER;

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

GROUP# THREAD# SEQUENCE# Taille en Mo MEMBERS STATUS TYPE MEMBER ---------- ---------- ---------- --------------- ---------- -------------------- --------------- -------------------------------------------------------------------------------- 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo 1 1 189945 150.00 2 CURRENT STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo 2 1 189943 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo 3 1 189942 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo 7 1 189944 150.00 2 INACTIVE STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo   24 rows selected.

---

Mais comme on peut le voir j'ai des doublons et quand je fais les requêtes SELECT séparées sur les 2 tables V$LOGFILE et V$LOG, cela donne ça :

Citation:

---

SQL> select * from V$LOG;

---

Code:

---

1 2 3 4 5 6

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- -------------------- ------------- --------------- 1 1 189945 157286400 2 YES ACTIVE 7771095445 16-DEC-15 7 1 189944 157286400 2 YES INACTIVE 7771034899 16-DEC-15 3 1 189946 157286400 2 NO CURRENT 7771152648 16-DEC-15 2 1 189943 157286400 2 YES INACTIVE 7770971127 16-DEC-15

---

Citation:

---

SQL> select * from V$LOGFILE;

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

GROUP# STATUS TYPE MEMBER IS_ ---------- -------------------- --------------- -------------------------------------------------------------------------------- --- 1 ONLINE /CHEMIN/01/BDD/BDD_redo1_1.rdo NO 1 ONLINE /CHEMIN/02/BDD/BDD_redo1_2.rdo NO 2 ONLINE /CHEMIN/01/BDD/BDD_redo2_1.rdo NO 2 ONLINE /CHEMIN/02/BDD/BDD_redo2_2.rdo NO 3 ONLINE /CHEMIN/01/BDD/BDD_redo3_1.rdo NO 3 ONLINE /CHEMIN/02/BDD/BDD_redo3_2.rdo NO 4 STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo NO 4 STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo NO 5 STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo NO 5 STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo NO 6 STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo NO 6 STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo NO 7 ONLINE /CHEMIN/01/BDD/BDD_redo7_1.rdo NO 7 ONLINE /CHEMIN/02/BDD/BDD_redo7_2.rdo NO

---

Est-ce qu'il y a possibilité de corriger ma requête ou bien avez-vous une requête qui donnerai les informations des redo log stanbdy en une seule requête avec le Groupe, la Taille, les Members, le type et le Chemin (Member) ?

Merci.

Il manque déjà une jointure dans ta requête pour lier V$LOG et V$LOGFILE

Code:

---

1 2 3

from V$LOGFILE f, V$LOG l where l.group# = f.group# and l.thread# = l.thread#

---

Ensuite j'ai l'impression que V$LOG c'est plus pour les "ONLINE REDOLOG", et V$STANDBY_LOG pour les "STANDBY REDOLOG" (je ne peux pas tester).
Donc remplace aussi V$LOG par V$STANDBY_LOG

Super et merci McM, j'ai refait la requête donc ainsi :

Citation:

---

set lines 999
set pages 999
col MEMBER for a80
col status for a20
col type for a15
COL l.BYTES/1024/1024 FORMAT 999,999,999.99 HEAD 'Taille en Mo'
select l.GROUP#,
l.THREAD#,
l.SEQUENCE#,
l.BYTES/1024/1024,
l.STATUS,
f.TYPE,
f.MEMBER
from V$LOGFILE f, V$STANDBY_LOG l
where l.group# = f.group#
and l.thread# = l.thread#
order by TYPE,GROUP#,MEMBER;

---

Code:

---

1 2 3 4 5 6 7 8 9 10

GROUP# THREAD# SEQUENCE# Taille en Mo STATUS TYPE MEMBER ---------- ---------- ---------- --------------- -------------------- --------------- -------------------------------------------------------------------------------- 4 1 0 150.00 UNASSIGNED STANDBY /CHEMIN/01/BDD/BDD_redostby1_1.rdo 4 1 0 150.00 UNASSIGNED STANDBY /CHEMIN/02/BDD/BDD_redostby1_2.rdo 5 1 0 150.00 UNASSIGNED STANDBY /CHEMIN/01/BDD/BDD_redostby2_1.rdo 5 1 0 150.00 UNASSIGNED STANDBY /CHEMIN/02/BDD/BDD_redostby2_2.rdo 6 0 0 150.00 UNASSIGNED STANDBY /CHEMIN/01/BDD/BDD_redostby3_1.rdo 6 0 0 150.00 UNASSIGNED STANDBY /CHEMIN/02/BDD/BDD_redostby3_2.rdo   6 rows selected.

---

Exactement ce que je voulais.

Merci encore !