L'Oracle de Victor: 05/18/17

Problèeme avec la configuration DataGuard Broker et un des paramètres de la BD

Symptôme :

L’OEM a détecté ce message d’erreur

ORA-16792: configurable property value is inconsistent with database setting

Cause:

Une différence entre une valeur compris dans le spfile et le broker

Solution:

Le nom de la BD ci-dessous est juste un exemple, il faudrait s’assurer de travailler sur la bonne BD.

export ORACLE_SID=«NOMINSTANCE»

# dgmgrl

DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@«NOMINSTANCE»

Connected.

DGMGRL> SHOW CONFIGURATION;

Configuration - xxxxCONF

Protection Mode: MaxPerformance

Members:

«NOMBDPRIM» - Primary database

Warning: ORA-16792: configurable property value is inconsistent with database setting

«NOMBDSTB» - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

WARNING (status updated 11 seconds ago)

DGMGRL> SHOW DATABASE VERBOSE '«NOMBDPRIM»' ;

Database - «NOMBDPRIM»

Enterprise Manager Name: «NOMBDPRIM».xxxxxx.yy

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

«NOMINSTANCE»

Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting

Properties:

DGConnectIdentifier = '«NomBdPrim»'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

RedoRoutes = ''

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '5'

…

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

…

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

WARNING

DGMGRL> SHOW DATABASE '«NOMBDPRIM»' 'StatusReport';

STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

«NOMINSTANCE» WARNING ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting

DGMGRL> SHOW DATABASE '«NOMBDPRIM»' 'InconsistentProperties';

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

«NOMINSTANCE» ArchiveLagTarget 3600 3600 0

DGMGRL> EDIT DATABASE '«NOMBDPRIM»' SET PROPERTY 'ArchiveLagTarget'=3600;

Property "ArchiveLagTarget" updated

DGMGRL> SHOW DATABASE '«NOMBDPRIM»' 'InconsistentLogXptProps';

DGMGRL> SHOW DATABASE VERBOSE '«NOMBDPRIM»';

Cela devrait être correct maintenant

Voici la procédure pour remplacer les redologs de vos bases de données configurées en Dataguard

Bd Primaire 11gR2 ou 12c (Non PDB)
Bd StandBy 11gR2 ou 12c (Non PDB) - Physical Database
###############################
#### Étape 1: Validation ####
###############################

MYBD - PRIMAIRE :
================
SET PAGESIZE 100
SET LINESIZE 1000
SELECT DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON FROM V$DATABASE ;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
------------------------------ -------------------- ---------------- ------------------
QMYBD READ WRITE PRIMARY YES

SET LINESIZE 500
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 Mo, MEMBERS, ARCHIVED, STATUS, TO_CHAR(FIRST_CHANGE#) FIRST_CHANGE, FIRST_TIME
FROM V$LOG ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 927 100 2 YES INACTIVE 184809714 17-MAY-17
11 1 928 100 2 NO CURRENT 184870197 17-MAY-17
12 1 926 100 2 YES INACTIVE 184728978 16-MAY-17

SET LINESIZE 500
SELECT GROUP#, BYTES/1024/1024 Mo, ARCHIVED, STATUS
FROM V$STANDBY_LOG ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 100 YES UNASSIGNED
2 100 YES UNASSIGNED
3 100 YES UNASSIGNED
4 100 YES UNASSIGNED

COL MEMBER FORMAT A80
SELECT GROUP#, STATUS, TYPE, MEMBER FROM V$LOGFILE ORDER BY TYPE, GROUP#;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ----------------------------------------------------
10 ONLINE +FLASMYBD/qmybd/onlinelog/group_10.258.832507365
10 ONLINE +REDOMYBD/qmybd/onlinelog/group_10.258.832507365
11 ONLINE +FLASMYBD/qmybd/onlinelog/group_11.284.832507415
11 ONLINE +REDOMYBD/qmybd/onlinelog/group_11.257.832507417
12 ONLINE +REDOMYBD/qmybd/onlinelog/group_12.256.832507259
12 ONLINE +FLASMYBD/qmybd/onlinelog/group_12.283.832507259
1 STANDBY +FLASMYBD/qmybd/onlinelog/group_1.279.868263421
2 STANDBY +FLASMYBD/qmybd/onlinelog/group_2.280.868263453
3 STANDBY +FLASMYBD/qmybd/onlinelog/group_3.274.833968935
4 STANDBY +FLASMYBD/qmybd/onlinelog/group_4.261.840700501

SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

MYBD - STANDBY :
================
SET PAGESIZE 100
SET LINESIZE 1000
SELECT DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON FROM V$DATABASE ;

DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
------------------------------ -------------------- ---------------- ------------------
MMYBD MOUNTED PHYSICAL STANDBY YES

SET LINESIZE 500
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 Mo, MEMBERS, ARCHIVED, STATUS, TO_CHAR(FIRST_CHANGE#) FIRST_CHANGE, FIRST_TIME
FROM V$LOG ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 0 100 2 YES UNUSED 176610509 11-APR-17
12 1 0 100 2 YES UNUSED 0
11 1 0 100 2 YES UNUSED 0

SET LINESIZE 500
SELECT GROUP#, BYTES/1024/1024 Mo, ARCHIVED, STATUS
FROM V$STANDBY_LOG ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 100 NO UNASSIGNED
2 100 YES ACTIVE
3 100 NO UNASSIGNED
4 100 NO UNASSIGNED

COL MEMBER FORMAT A80
SELECT GROUP#, STATUS, TYPE, MEMBER FROM V$LOGFILE ORDER BY TYPE, GROUP#;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------------------------
10 ONLINE +DATINXRWMYBD/MMYBD/ONLINELOG/group_10.266.930408787
10 ONLINE +FLASMYBD/MMYBD/ONLINELOG/group_10.281.930408787
11 ONLINE +DATINXRWMYBD/MMYBD/ONLINELOG/group_11.265.930408787
11 ONLINE +FLASMYBD/MMYBD/ONLINELOG/group_11.264.930408789
12 ONLINE +DATINXRWMYBD/MMYBD/ONLINELOG/group_12.264.930408789
12 ONLINE +FLASMYBD/MMYBD/ONLINELOG/group_12.268.930408789
1 STANDBY +DATINXRWMYBD/MMYBD/ONLINELOG/group_1.280.930405485
1 STANDBY +FLASMYBD/MMYBD/ONLINELOG/group_1.282.930405487
2 STANDBY +FLASMYBD/MMYBD/ONLINELOG/group_2.284.930405487
2 STANDBY +DATINXRWMYBD/MMYBD/ONLINELOG/group_2.274.930405487
3 STANDBY +DATINXRWMYBD/MMYBD/ONLINELOG/group_3.273.930405487
3 STANDBY +FLASMYBD/MMYBD/ONLINELOG/group_3.285.930405487
4 STANDBY +DATINXRWMYBD/MMYBD/ONLINELOG/group_4.279.930405485
4 STANDBY +FLASMYBD/MMYBD/ONLINELOG/group_4.283.930405487

SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

@verif_synchro.sql (Faire quelques switch sur la primaire)

LOGS TIME SEQUENCE# INCARNATION#
---------------- -------------------- ---------- ------------
LAST APPLIED : 17-MAY-2017 15:17:52 928 10
LAST RECEIVED : 17-MAY-2017 15:21:05 929 10

###############################
#### Étape 2: Modification ####
###############################
MYBD - STANDBY :
================
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL ;

SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL

MYBD - PRIMAIRE :
================
DISKGROUPS : FLASMYBD et REDOMYBD

REDOLOGS :
------------
Comme ça termine par le groupe 12 et juste parce que c''est un exemple :

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 13 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 14 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

SET LINESIZE 500
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 Mo, MEMBERS, ARCHIVED, STATUS, TO_CHAR(FIRST_CHANGE#) FIRST_CHANGE, FIRST_TIME
FROM V$LOG ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 930 100 2 NO CURRENT 184877625 17-MAY-17
11 1 928 100 2 YES INACTIVE 184870197 17-MAY-17
12 1 929 100 2 YES INACTIVE 184877204 17-MAY-17
13 1 0 120 2 YES UNUSED 0
14 1 0 120 2 YES UNUSED 0
15 1 0 120 2 YES UNUSED 0

ALTER DATABASE DROP LOGFILE GROUP 11 ;
Database altered.

ALTER DATABASE DROP LOGFILE GROUP 12 ;
Database altered.

SET LINESIZE 500
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 Mo, MEMBERS, ARCHIVED, STATUS, TO_CHAR(FIRST_CHANGE#) FIRST_CHANGE, FIRST_TIME
FROM V$LOG ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 930 100 2 NO CURRENT 184877625 17-MAY-17
13 1 0 120 2 YES UNUSED 0
14 1 0 120 2 YES UNUSED 0
15 1 0 120 2 YES UNUSED 0

ALTER SYSTEM SWITCH LOGFILE ;
System altered.
ALTER SYSTEM SWITCH LOGFILE ;
System altered.

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 930 100 2 YES ACTIVE 184877625 17-MAY-17
13 1 931 120 2 YES ACTIVE 184880767 17-MAY-17
14 1 932 120 2 NO CURRENT 184880903 17-MAY-17
15 1 0 120 2 YES UNUSED 0

ALTER SYSTEM SWITCH LOGFILE ;
System altered.

ALTER DATABASE DROP LOGFILE GROUP 10 ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
13 1 931 120 2 YES INACTIVE 184880767 17-MAY-17
14 1 932 120 2 YES ACTIVE 184880903 17-MAY-17
15 1 933 120 2 NO CURRENT 184881070 17-MAY-17

STANDBY_LOG :
-------------
SET LINESIZE 500
SELECT GROUP#, BYTES/1024/1024 Mo, ARCHIVED, STATUS
FROM V$STANDBY_LOG ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 100 YES UNASSIGNED
2 100 YES UNASSIGNED
3 100 YES UNASSIGNED
4 100 YES UNASSIGNED

ALTER DATABASE DROP STANDBY LOGFILE GROUP 1 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 120 YES UNASSIGNED
2 100 YES UNASSIGNED
3 100 YES UNASSIGNED
4 100 YES UNASSIGNED

ALTER DATABASE DROP STANDBY LOGFILE GROUP 2 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 3 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 120 YES UNASSIGNED
2 120 YES UNASSIGNED
3 120 YES UNASSIGNED
4 120 YES UNASSIGNED

MYBD - STANDBY :
================
DISKGROUPS : FLASMYBD et REDOMYBD

REDOLOGS :
------------
SET LINESIZE 500
SELECT GROUP#, THREAD#, SEQUENCE#, BYTES/1024/1024 Mo, MEMBERS, ARCHIVED, STATUS, TO_CHAR(FIRST_CHANGE#) FIRST_CHANGE, FIRST_TIME
FROM V$LOG ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
10 1 0 100 2 YES UNUSED 176610509 11-APR-17
12 1 0 100 2 YES UNUSED 0
11 1 0 100 2 YES UNUSED 0

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL ;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 13 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 14 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

ALTER DATABASE DROP LOGFILE GROUP 10 ;
ALTER DATABASE DROP LOGFILE GROUP 11 ;
ALTER DATABASE DROP LOGFILE GROUP 12 ;

GROUP# THREAD# SEQUENCE# MO MEMBERS ARC STATUS FIRST_CHANGE FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------ ---------
13 1 0 120 2 YES UNUSED 0
15 1 0 120 2 YES UNUSED 0
14 1 0 120 2 YES UNUSED 0

STANDBY_LOG :
-------------
SET LINESIZE 500
SELECT GROUP#, BYTES/1024/1024 Mo, ARCHIVED, STATUS
FROM V$STANDBY_LOG ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 100 YES UNASSIGNED
2 100 YES UNASSIGNED
3 100 YES UNASSIGNED
4 100 YES UNASSIGNED

ALTER DATABASE DROP STANDBY LOGFILE GROUP 1 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 120 YES UNASSIGNED
2 100 NO UNASSIGNED
3 100 NO UNASSIGNED
4 100 NO UNASSIGNED

ALTER DATABASE DROP STANDBY LOGFILE GROUP 2 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 3 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

ALTER DATABASE DROP STANDBY LOGFILE GROUP 4 ;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 ('+FLASMYBD', '+REDOMYBD' ) SIZE 120M ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 120 YES UNASSIGNED
2 120 YES UNASSIGNED
3 120 YES UNASSIGNED
4 120 YES UNASSIGNED

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO ;

SHOW PARAMETER STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SET PAGESIZE 100
SET LINESIZE 1000
SELECT CLIENT_PROCESS, PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

CLIENT_P PROCESS SEQUENCE# STATUS
-------- --------- ---------- ------------
ARCH ARCH 925 CLOSING
ARCH ARCH 929 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 924 CLOSING
ARCH ARCH 930 CLOSING
N/A MRP0 934 WAIT_FOR_LOG
LGWR RFS 934 IDLE
ARCH RFS 0 IDLE
UNKNOWN RFS 0 IDLE

@verif_synchro.sql (Faire quelques switch sur la primaire)

LOGS TIME SEQUENCE# INCARNATION#
---------------- -------------------- ---------- ------------
LAST APPLIED : 17-MAY-2017 16:07:04 934 10
LAST RECEIVED : 17-MAY-2017 16:07:05 935 10

SET LINESIZE 500
SELECT GROUP#, BYTES/1024/1024 Mo, ARCHIVED, STATUS
FROM V$STANDBY_LOG ;

GROUP# MO ARC STATUS
---------- ---------- --- ----------
1 120 NO UNASSIGNED
2 120 YES ACTIVE
3 120 YES UNASSIGNED
4 120 YES UNASSIGNED

L'Oracle de Victor

Translate

jeudi 18 mai 2017

ORA-16792 Broker configuration "configurable property value is inconsistent with database setting"

Resizing RedoLogs & StandByLogs ( DATAGUARD )