OEM: Metric “Tablespace Allocation Metric” not Collected – Agent is Running but Not Ready

Hi all,

That’s an interesting case with OEM. A client reported the metric “Tablespace Allocation Metric” is not being updated on OEM for a specific database. In this case, the last gathering was in Nov/2020, as you’ll see.

When checking for it, the first try as usual was checking on the OEM agent status, and here is what I got:

oracle:dbserver@mydb02 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : (unknown)
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 61641
Parent Process ID      : 61394
Currently initializing component             : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
        TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = rac_database.c1prod - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 


Target Manager Current Activity              : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)


Current target operations in progress
-------------------------------------
        oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
        rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds


Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

Agent not ready, that’s interesting.
Trying then to clear the agent state as this has solved some previous similar cases:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl clearstate agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD clearstate completed successfully

Now running the problematic metric manually:

oracle:dbserver02@c1test2 /u01/app/oracle: runCollection c1test_DW:oracle_pdb tbspAllocation                                                                <
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection error:The agent is running but is currently not ready to accept client requests

Ok, trying to just upload the case:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl upload
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error:The agent is running but is currently not ready to accept client requests

Maybe something is stuck, so let’s kill the process and start all over again:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping agent ...

 stopped.

Making sure we have no agent process running:

oracle:dbserver02@c1test2 /u01/app/oracle:  ps -ef | grep java | agent
oracle:dbserver02@c1test2 /u01/app/oracle:

Also adjusting the threshod for metric running:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl  setproperty agent -a
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded
oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl setproperty agent -allow_new -name _cancelThread  -value 210
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
EMD setproperty succeeded

And starting the agent:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Starting agent ........................................................................................................................... started but not ready.

On the Agent log:

oracle:dbserver02@c1test2 /u01/app/oracle: tail /u01/app/oracle/product/agent12c/agent_inst/sysman/log/gcagent.log
oracle:dbserver02@c1test2 /u01/app/oracle:
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest Begin
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - Agent Overall Health: 0
2020-12-20 13:15:03,457 [35:686116F5] DEBUG - StatusAgentAction: satisfyRequest End
Response:
initializing
2020-12-20 13:15:03,457 [35:686116F5] INFO - >>> Reporting response: StatusAgentResponse (initializing) (request id 1) <<< 2020-12-20 13:15:03,457 [35:686116F5] DEBUG - closing request input stream for "StatusAgentRequest (AGENT timeout:300)" 2020-12-20 13:15:03,457 [35:686116F5] DEBUG - overriding the buffer with a thread local copy (size: 8192b) 2020-12-20 13:15:03,458 [35:686116F5] DEBUG - closing request output stream for "StatusAgentRequest (AGENT timeout:300)" 2020-12-20 13:15:03,458 [35:686116F5] DEBUG - StatusAgentAction.call() is complete. 2020-12-20 13:15:03,458 [35:B5326F3F:HTTP Listener-35 - /emd/lifecycle/main/] DEBUG - removing entry for emdctl@18081@dbserver02=>[160849530330001] completely
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - requests executed.
2020-12-20 13:15:03,458 [35:B5326F3F] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:03,458 [35:3C0B0663:HTTP Listener-35] DEBUG - using connection SCEP@1197017148 [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 5 bytesProduced = 26
2020-12-20 13:15:03,780 [35:3C0B0663] DEBUG - using connection SCEP@1197017148  [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 26 bytesProduced = 5
2020-12-20 13:15:06,986 [31:858161EB] DEBUG - Submitting task SchedulerHeartbeat for execution
2020-12-20 13:15:06,986 [395:1AE716D8] DEBUG - Begin task SchedulerHeartbeat on Thread: GC.SysExecutor.8
2020-12-20 13:15:06,986 [395:F944F4C8:GC.SysExecutor.8 (SchedulerHeartbeat)] DEBUG - Scheduler heartbeat
2020-12-20 13:15:06,988 [395:F944F4C8] DEBUG - Scheduling next SchedulerHeartbeat after delay 29998 including periodShift of 0 milliseconds
2020-12-20 13:15:06,988 [395:1AE716D8:GC.SysExecutor.8] DEBUG - End task SchedulerHeartbeat
2020-12-20 13:15:07,016 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:07,017 [396:1AE716D9] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.9
2020-12-20 13:15:07,017 [396:391F60D7:GC.SysExecutor.9 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:07,017 [396:1AE716D9:GC.SysExecutor.9] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:12,017 [37:1AE716D0] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.0
2020-12-20 13:15:12,017 [37:FE21F10E:GC.SysExecutor.0 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:12,017 [37:1AE716D0:GC.SysExecutor.0] DEBUG - End task HeapMonitorTask
2020-12-20 13:15:12,189 [33:6D553CF6] DEBUG - HTTPListener Threads deallocated resource back to LifecycleRequestHandler partition
2020-12-20 13:15:12,190 [35:3C0B0663] DEBUG - using connection SCEP@1611645943  [d=true,io=1,w=true,b=false|false],NOT_HANDSHAKING, in/out=0/0 Status = OK HandshakeStatus = NOT_HANDSHAKING
bytesConsumed = 100 bytesProduced = 121
2020-12-20 13:15:12,191 [35:7107E334:HTTP Listener-35 - /emd/persistence/main/] DEBUG - HTTPListener Threads allocated resource from LifecycleRequestHandler partition
2020-12-20 13:15:17,017 [31:858161EB] DEBUG - Submitting task HeapMonitorTask for execution
2020-12-20 13:15:17,018 [45:1AE716D1] DEBUG - Begin task HeapMonitorTask on Thread: GC.SysExecutor.1
2020-12-20 13:15:17,018 [45:CBCC52CF:GC.SysExecutor.1 (HeapMonitorTask)] DEBUG - Scheduling next HeapMonitorTask after delay 5000 including periodShift of 0 milliseconds
2020-12-20 13:15:17,018 [45:1AE716D1:GC.SysExecutor.1] DEBUG - End task HeapMonitorTask

Following MOS Enterprise Manager12c: Oracle Database Tablespace Monthly Space Usage shows no data (Doc ID 1536654.1), a few changes were made:

$/AGENT_INST/bin/emctl setproperty agent -allow_new -name MaxInComingConnections -value 150
$/AGENT_INST/bin/emctl setproperty agent -allow_new -name _cancelThread  -value 210

The status before the change:

oracle:dbserver02@c1test2 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : (unknown)
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 61641
Parent Process ID      : 61394
Currently initializing component             : Target Manager (2) (54 of 70)
Receivelet Interaction Manager Current Activity: Outstanding receivelet event tasks
----------------------------------
        TargetID = oracle_pdb.c4test_PDB1 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB2 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = oracle_pdb.c4test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c6test_CDBROOT - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:29 
        TargetID = oracle_pdb.c3test_PDB3 - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 
        TargetID = rac_database.c1test - EventType - TARGET_EVENT for operation LOAD_TARGET submitted at 2020-12-20 12:54:30 

Target Manager Current Activity              : Compute Dynamic Properties (total operations: 37, active: 7, finished: 28)

Current target operations in progress
-------------------------------------
        oracle_pdb.c6test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_PDB1 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB2 - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c4test_CDBROOT - LOAD_TARGET_DYNAMIC running for 120 seconds
        oracle_pdb.c3test_PDB3 - LOAD_TARGET_DYNAMIC running for 120 seconds
        rac_database.c1test - LOAD_TARGET_DYNAMIC running for 120 seconds

Dynamic property executor tasks running
------------------------------


---------------------------------------------------------------
Agent is Running but Not Ready

And the status after the change:

oracle:dbserver02@c1test2 /u01/app/oracle:  /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.5.0
OMS Version            : 12.1.0.5.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/agent12c/agent_inst
Agent Log Directory    : /u01/app/oracle/product/agent12c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/agent12c/core/12.1.0.5.0
Agent Process ID       : 56994
Parent Process ID      : 56654
Agent URL              : https://dbserver02:3872/emd/main/
Local Agent URL in NAT : https://dbserver02:3872/emd/main/
Repository URL         : https://omsweb:4903/empbs/upload
Started at             : 2020-12-20 13:08:35
Started by user        : oracle
Operating System       : Linux version 3.10.0-957.27.2.el7.x86_64 (amd64)
Last Reload            : (none)
Last successful upload                       : 2020-12-20 13:40:41
Last attempted upload                        : 2020-12-20 13:40:41
Total Megabytes of XML files uploaded so far : 1.02
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 10.85%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2020-12-20 13:40:40
Last successful heartbeat to OMS             : 2020-12-20 13:40:40
Next scheduled heartbeat to OMS              : 2020-12-20 13:41:40

---------------------------------------------------------------
Agent is Running and Ready

Great! Agent issue resolved.
However, the metric is not being gathered not even after running it manually:

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_CDBROOT:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl status agent scheduler | grep tbspAllocation
2020-12-28 23:05:14.562 : rac_database:c1test:tbspAllocation_cdb
2020-12-29 03:07:21.988 : rac_database:c4prod:tbspAllocation_cdb
2020-12-29 03:08:11.888 : rac_database:c6prod:tbspAllocation_cdb
2020-12-29 03:09:39.103 : rac_database:c2prod:tbspAllocation_cdb
2020-12-29 03:09:55.372 : rac_database:c3prod:tbspAllocation_cdb

oracle:dbserver01@c1test1 /u01/app/oracle: /u01/app/oracle/product/agent12c/core/12.1.0.5.0/bin/emctl control agent runCollection c1test_DW:oracle_pdb tbspAllocation
Oracle Enterprise Manager Cloud Control 12c Release 5
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD runCollection completed successfully

On OEM Repository database:

SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,max(ROLLUP_TIMESTAMP )
from mgmt$metric_daily where TARGET_NAME like '%c1test%'
and TARGET_TYPE='oracle_pdb'
and METRIC_NAME='tbspAllocation'
group by TARGET_NAME,TARGET_TYPE,TARGET_GUID;  2    3    4    5

TARGET_NAME                    TARGET_TYPE          TARGET_GUID                      MAX(ROLLUP_TIMESTAM
------------------------------ -------------------- -------------------------------- -------------------
c1test_DW         oracle_pdb           7B1DF5DD4555EB978330A6D522004D44 2020-11-12 00:00:00
c1test_CDBROOT    oracle_pdb           4CE72911295C0287E053837F649B7D0E 2020-11-12 00:00:00


SQL> select TARGET_NAME,TARGET_TYPE,TARGET_GUID,ROLLUP_TIMESTAMP from mgmt$metric_daily where TARGET_NAME like '%c1test%' and TARGET_TYPE='oracle_pdb' and ROLLUP_TIMESTAMP>sysdate-3 order by 4

TARGET_NAME                    TARGET_TYPE          ROLLUP_TIMESTAMP       AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_DW         oracle_pdb           2020-11-06 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-07 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-08 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-09 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-10 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-11 00:00:00  1575.9375
c1test_DW         oracle_pdb           2020-11-12 00:00:00  1575.9375
c1test_CDBROOT    oracle_pdb           2020-11-05 00:00:00 37581.5625


TARGET_NAME                    TARGET_TYPE          ROLLUP_TIMESTAMP       AVERAGE
------------------------------ -------------------- ------------------- ----------
c1test_CDBROOT    oracle_pdb           2020-11-08 00:00:00  227138.75
c1test_CDBROOT    oracle_pdb           2020-11-09 00:00:00 455087.688
c1test_CDBROOT    oracle_pdb           2020-11-10 00:00:00 278230.875
c1test_CDBROOT    oracle_pdb           2020-11-11 00:00:00 208727.188
c1test_CDBROOT    oracle_pdb           2020-11-12 00:00:00 454964.063

Ok, so in summary: After fixing all issues on OEM side, everything running fine, still the database metrics are not being updated.

Long story short: After some investigation, bumped in MOS Database Hangs With Simple Queries like on view dba_data_files & dba_free_space (Doc ID 2665935.1)
Turns out this seemed to be a match. So proceeding with the recommendation:

SQL> alter session set container=DW;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 DW                             READ WRITE NO
SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     28522

SQL> purge recyclebin;

Recyclebin purged.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Once done, all issues solved and metric being collected again:

Some additional reference:

  • Database Tablespace Metrics: Tablespace Allocation Is Not Collected (Metric tbspAllocation) (Doc ID 404692.1)
  • EM 12c : emctl start agent Fails With Error ‘Starting agent … started but not ready’ (Doc ID 1591477.1)
  • EM12c : emctl start / status agent ‘Agent Running but Not Ready’ ‘ERROR – The agent is overloaded [current requests: 30]’ Reported in gcagent.log (Doc ID 1546529.1)

I hope it helps!

Dataguard Broker: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting

This seems like a simple message to be fixed, right?

The parameter is different between the broker configuration and the database parameters, most likely changed directly on the database after the DGBroker configuration be created or the database added. However, there is an interesting thing in this case.

Let’s check on the error first. On the primary database side of the broker configuration:

DGMGRL> show database myprodDB;

Database - myprodDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
rmprdb01
Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting

Database Warning(s):
ORA-16707: the value of the property LogFileNameConvert is invalid, valid values are pairs of file specifications

Database Status:
WARNING

DGMGRL> show database verbose myprodDB;

Database - myprodDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
rmprdb01
Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting

Database Warning(s):
ORA-16707: the value of the property LogFileNameConvert is invalid, valid values are pairs of file specifications

Properties:
DGConnectIdentifier = 'myprodDB'
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 = '1800'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/MYDATABASE/DATAFILE/, +DATA/myprodDB/DATAFILE'
LogFileNameConvert = '+DATA/MYDATABASE/ONLINELOG/, +DATADG/myprodDB/ONLINELOG/, +DATA2/MYDATABASE/ONLINELOG/, +DATADG2/myprodDB/ONLINELOG/, +DATA3/MYDATABASE/ONLINELOG/', +DATADG3/myprodDB/ONLINELOG/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myprodDB_DGMGRL)(INSTANCE_NAME=MYDATABASE)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'MYDATABASE_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
WARNING

And checking for the status in the standby database server:

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - my_dg_configuration

Protection Mode: MaxPerformance
Members:
myprodDB - Primary database
Warning: ORA-16809: multiple warnings detected for the database

mySTDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING (status updated 12 seconds ago)

DGMGRL> show database myprodDB;

Database - myprodDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MYDATABASE
Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting

Database Warning(s):
ORA-16707: the value of the property LogFileNameConvert is invalid, valid values are pairs of file specifications

Database Status:
WARNING

Ok, let’s check now for the database parameters perspective on the Primary:

SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string +DATA/MYDATABASE/DATAFILE/, +
DATADG/myprodDB/DATAFILE
log_file_name_convert string +DATA/MYDATABASE/ONLINELOG/,+DATADG/myprodDB/ONLINELOG/, +DATA2/MYDATABASE/ONLINELOG/, 
                             +DATADG2/myprodDB/ONLINELOG/, +DATA3/MYDATABASE/ONLINELOG/, +DATADG3/myprodDB/ONLINELOG/
pdb_file_name_convert string

Comparing the settings:

  • LogFileNameConvert=’+DATA/MYDATABASE/ONLINELOG/, +DATA/myprodDB/ONLINELOG/, +DATA2/MYDATABASE/ONLINELOG/, +DATADG2/myprodDB/ONLINELOG/,+DATA3/MYDATABASE/ONLINELOG/’, +DATADG3/myprodDB/ONLINELOG/’
  • log_file_name_convert=+DATA/MYDATABASE/ONLINELOG/,+DATA/myprodDB/ONLINELOG/,+DATA/MYDATABASE/ONLINELOG/, +DATADG2/myprodDB/ONLINELOG/,+DATA/MYDATABASE/ONLINELOG/, +DATADG3/myprodDB/ONLINELOG/

It seems all right!

What is the problem then?

That’s the interesting part. Checking on MOS Usage and Limitation of db_file_name_convert and log_file_name_convert (Doc ID 1367014.1):

When using the Data Guard Broker the Values for these Parameters are limited to 512 Bytes (Characters) due to the Limit of the corresponding Data Guard Broker Properties ‘DbFileNameConvert’ and ‘LogFileNameConvert’.

That’s new to me! So, possible alternatives are:

  • Use OMF (Oracle Managed Files)
  • Use the same File Structure on both Sites
  • Rename and create Datafiles/RedoLog Files manually

What I did in my case?

We checked and confirmed with the client the only places for the logfiles are DATA and DATA2 (multiplexed). So the fix was easy:

edit database 'myprodDB' set property 'LogFileNameConvert' = "+DATA/MYDATABASE/ONLINELOG/,+DATADG/myprodDB/ONLINELOG/,+DATA/MYDATABASE/ONLINELOG/, +DATADG2/myprodDB/ONLINELOG/";

Once done:

DGMGRL> show configuration;

Configuration - my_dg_configuration

Protection Mode: MaxPerformance
Members:
myprodDB - Primary database
mySTDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 3 seconds ago)

DGMGRL> show database myprodDB;

Database - myprodDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MYDATABASE

Database Status:
SUCCESS

DGMGRL> show database mySTDB;

Database - mySTDB

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 325.00 KByte/s
Real Time Query: OFF
Instance(s):
mySTDB

Database Status:
SUCCESS

DGMGRL> show database verbose myprodDB;

Database - myprodDB

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
MYDATABASE

Properties:
DGConnectIdentifier = 'myprodDB.'
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 = '1800'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/MYDATABASE/DATAFILE/, +DATA/myprodDB/DATAFILE'
LogFileNameConvert = '+DATA/MYDATABASE/ONLINELOG/,+DATADG/myprodDB/ONLINELOG/,+DATA/2MYDATABASE/ONLINELOG/,+DATADG2/myprodDB/ONLINELOG/'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myprodDB_DGMGRL)(INSTANCE_NAME=MYDATABASE)(SERVER=DEDICATED)))'
StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'MYDATABASE_%t_%s_%r.arc'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

Did you know that?
I hope it helps!

OMS Opatch out of space on /tmp

This is a quick and old, but still a good one. while installing the Opatch using the JAR file I got the error saying that I had no space under /tmp.

This is a new server that I´m building and I missed requesting a bigger /tmp.

So what to do now?

This installer does not recognize the environment variables such as TMPDIR so you need to set a _JAVA_OPTIONS with a path to replace its default location.

grepora01[oracle] /u01/software/6880880 $ # $ORACLE_HOME/oracle_common/jdk/bin/java -jar ./opatch_generic.jar  -silent oracle_home=$ORACLE_HOME -J-Djava.io.tmpdir=/u01/tmp
Launcher log file is /tmp/OraInstall2021-03-24_12-53-25PM/launcher2021-03-24_12-53-25PM.log.
Extracting the installer . . . . Done
Checking if CPU speed is above 300 MHz.   Actual 2593.207 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 4099 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 0 MB    Failed <<<<
Some system prerequisite checks failed.
You must fulfill these requirements before continuing.

To fix it, you can simply:

grepora01[oracle] /u01/software/6880880 $ # export _JAVA_OPTIONS="-Djava.io.tmpdir=/u01/tmp"
grepora01[oracle] /u01/software/6880880 $ # $ORACLE_HOME/oracle_common/jdk/bin/java -jar ./opatch_generic.jar  -silent oracle_home=$ORACLE_HOME
Picked up _JAVA_OPTIONS: -Djava.io.tmpdir=/u01/tmp
Launcher log file is /u01/tmp/OraInstall2021-03-24_01-30-12PM/launcher2021-03-24_01-30-12PM.log.
Extracting the installer . . . . Done
Checking if CPU speed is above 300 MHz.   Actual 2593.207 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 4099 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 198503 MB    Passed
Preparing to launch the Oracle Universal Installer from /u01/tmp/OraInstall2021-03-24_01-30-12PM
Picked up _JAVA_OPTIONS: -Djava.io.tmpdir=/u01/tmp

Installation Summary

Disk Space : Required 34 MB, Available 198,462 MB
Feature Sets to Install:
        Next Generation Install Core 13.9.4.0.1
        OPatch 13.9.4.2.5
        OPatch Auto OPlan 13.9.4.2.5
Session log file is /u01/tmp/OraInstall2021-03-24_01-30-12PM/install2021-03-24_01-30-12PM.log

Loading products list. Please wait.

 1%
 40%
Loading products. Please wait.
 42%
 43%
 45%
 46%
 48%
 49%
 50%
 51%
 52%
 54%
 55%
 57%
 58%
 60%
 61%
 62%
 64%
 65%
 67%
 68%
 70%
 71%
 72%
 74%
 75%
 77%
 78%
 80%
 81%
 82%
 84%
 85%
 87%
 88%
 90%
 91%
 92%
 94%
 95%
 97%
 98%
 99%

Updating Libraries

Starting Installations
 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%
 30%
 31%
 32%
 33%
 34%
 35%
 36%
 37%
 38%
 39%
 40%
 41%
 42%
 43%
 44%
 45%
 46%
 47%
 48%
 49%
 50%
 51%
 52%
 53%
 54%
 55%
 56%
 57%
 58%
 59%
 60%
 61%
 62%
 63%
 64%
 65%
 66%
 67%
 68%
 69%
 70%
 71%
 72%
 73%
 74%
 75%
 76%
 77%
 78%
 79%
 80%
 81%
 82%
 83%
 84%
 85%
 86%
 87%
 88%
 89%
 90%
 91%
 92%
 93%
 94%
 95%

Install pending

Installation in progress

Component : oracle.glcm.logging 1.6.4.0.0

Copying files for oracle.glcm.logging 1.6.4.0.0

Component : oracle.glcm.comdev 7.8.4.0.0

Copying files for oracle.glcm.comdev 7.8.4.0.0

 Component : oracle.glcm.dependency 1.8.4.0.0

Copying files for oracle.glcm.dependency 1.8.4.0.0

 Component : oracle.glcm.xmldh 3.4.4.0.0

Copying files for oracle.glcm.xmldh 3.4.4.0.0

 Component : oracle.glcm.wizard 7.8.4.0.0

Copying files for oracle.glcm.wizard 7.8.4.0.0

 Component : oracle.nginst.common 13.9.4.0.0

Copying files for oracle.nginst.common 13.9.4.0.0

 Component : oracle.nginst.core 13.9.4.0.0

Copying files for oracle.nginst.core 13.9.4.0.0

 Component : oracle.glcm.opatch.common.api 13.9.4.0.0

Copying files for oracle.glcm.opatch.common.api 13.9.4.0.0

 Component : oracle.glcm.encryption 2.7.4.0.0

Copying files for oracle.glcm.encryption 2.7.4.0.0

 Component : oracle.swd.opatch 13.9.4.2.5

Copying files for oracle.swd.opatch 13.9.4.2.5

 Component : oracle.glcm.osys.core 13.9.1.0.0

Copying files for oracle.glcm.osys.core 13.9.1.0.0

Install successful

Post feature install pending

Post Feature installing

 Feature Set : glcm_encryption_lib

 Feature Set : oracle.glcm.osys.core.classpath

 Feature Set : commons-cli_1.3.1.0.0

 Feature Set : oracle.glcm.opatch.common.api.classpath

Post Feature installing commons-cli_1.3.1.0.0

Post Feature installing oracle.glcm.opatch.common.api.classpath

 Feature Set : glcm_common_lib

 Feature Set : glcm_common_logging_lib

Post Feature installing glcm_common_lib

Post Feature installing glcm_common_logging_lib

 Feature Set : oracle.glcm.opatchauto.core.classpath

 Feature Set : oracle.glcm.opatchauto.core.binary.classpath

Post Feature installing oracle.glcm.opatchauto.core.classpath

Post Feature installing oracle.glcm.opatchauto.core.binary.classpath

Post Feature installing oracle.glcm.osys.core.classpath

Post Feature installing glcm_encryption_lib

 Feature Set : oracle.glcm.oplan.core.classpath

Post Feature installing oracle.glcm.oplan.core.classpath

 Feature Set : oracle.glcm.opatchauto.core.actions.classpath

Post Feature installing oracle.glcm.opatchauto.core.actions.classpath

 Feature Set : oracle.glcm.opatchauto.core.wallet.classpath

Post Feature installing oracle.glcm.opatchauto.core.wallet.classpath

Post feature install complete

String substitutions pending

String substituting

 Component : oracle.glcm.logging 1.6.4.0.0

String substituting oracle.glcm.logging 1.6.4.0.0

 Component : oracle.glcm.comdev 7.8.4.0.0

String substituting oracle.glcm.comdev 7.8.4.0.0

 Component : oracle.glcm.dependency 1.8.4.0.0

String substituting oracle.glcm.dependency 1.8.4.0.0

 Component : oracle.glcm.xmldh 3.4.4.0.0

String substituting oracle.glcm.xmldh 3.4.4.0.0

 Component : oracle.glcm.wizard 7.8.4.0.0

String substituting oracle.glcm.wizard 7.8.4.0.0

 Component : oracle.nginst.common 13.9.4.0.0

String substituting oracle.nginst.common 13.9.4.0.0

 Component : oracle.nginst.core 13.9.4.0.0

String substituting oracle.nginst.core 13.9.4.0.0

 Component : oracle.glcm.opatch.common.api 13.9.4.0.0

String substituting oracle.glcm.opatch.common.api 13.9.4.0.0

 Component : oracle.glcm.encryption 2.7.4.0.0

String substituting oracle.glcm.encryption 2.7.4.0.0

 Component : oracle.swd.opatch 13.9.4.2.5

String substituting oracle.swd.opatch 13.9.4.2.5

 Component : oracle.glcm.osys.core 13.9.1.0.0

String substituting oracle.glcm.osys.core 13.9.1.0.0

String substitutions complete

Link pending

Linking in progress

 Component : oracle.glcm.logging 1.6.4.0.0

Linking oracle.glcm.logging 1.6.4.0.0

 Component : oracle.glcm.comdev 7.8.4.0.0

Linking oracle.glcm.comdev 7.8.4.0.0

 Component : oracle.glcm.dependency 1.8.4.0.0

Linking oracle.glcm.dependency 1.8.4.0.0

 Component : oracle.glcm.xmldh 3.4.4.0.0

Linking oracle.glcm.xmldh 3.4.4.0.0

 Component : oracle.glcm.wizard 7.8.4.0.0

Linking oracle.glcm.wizard 7.8.4.0.0

 Component : oracle.nginst.common 13.9.4.0.0

Linking oracle.nginst.common 13.9.4.0.0

 Component : oracle.nginst.core 13.9.4.0.0

Linking oracle.nginst.core 13.9.4.0.0

 Component : oracle.glcm.opatch.common.api 13.9.4.0.0

Linking oracle.glcm.opatch.common.api 13.9.4.0.0

 Component : oracle.glcm.encryption 2.7.4.0.0

Linking oracle.glcm.encryption 2.7.4.0.0

 Component : oracle.swd.opatch 13.9.4.2.5

Linking oracle.swd.opatch 13.9.4.2.5

 Component : oracle.glcm.osys.core 13.9.1.0.0

Linking oracle.glcm.osys.core 13.9.1.0.0

Linking in progress

Link successful

Setup pending

Setup in progress

 Component : oracle.glcm.logging 1.6.4.0.0

Setting up oracle.glcm.logging 1.6.4.0.0

 Component : oracle.glcm.comdev 7.8.4.0.0

Setting up oracle.glcm.comdev 7.8.4.0.0

 Component : oracle.glcm.dependency 1.8.4.0.0

Setting up oracle.glcm.dependency 1.8.4.0.0

 Component : oracle.glcm.xmldh 3.4.4.0.0

Setting up oracle.glcm.xmldh 3.4.4.0.0

 Component : oracle.glcm.wizard 7.8.4.0.0

Setting up oracle.glcm.wizard 7.8.4.0.0

 Component : oracle.nginst.common 13.9.4.0.0

Setting up oracle.nginst.common 13.9.4.0.0

 Component : oracle.nginst.core 13.9.4.0.0

Setting up oracle.nginst.core 13.9.4.0.0

 Component : oracle.glcm.opatch.common.api 13.9.4.0.0

Setting up oracle.glcm.opatch.common.api 13.9.4.0.0

 Component : oracle.glcm.encryption 2.7.4.0.0

Setting up oracle.glcm.encryption 2.7.4.0.0

Component : oracle.swd.opatch 13.9.4.2.5

Setting up oracle.swd.opatch 13.9.4.2.5

Component : oracle.glcm.osys.core 13.9.1.0.0

Setting up oracle.glcm.osys.core 13.9.1.0.0

Setup successful

Save inventory pending

Saving inventory

96%

Saving inventory complete

 97%

Configuration complete

Component : glcm_common_logging_lib

Component : glcm_common_lib

Saving the inventory glcm_common_lib

Saving the inventory glcm_common_logging_lib

Component : oracle.glcm.opatch.common.api.classpath

Saving the inventory oracle.glcm.opatch.common.api.classpath

Component : cieCfg_common_rcu_lib

Saving the inventory cieCfg_common_rcu_lib

Component : oracle.glcm.osys.core.classpath

Saving the inventory oracle.glcm.osys.core.classpath

Component : cieCfg_common_lib

Saving the inventory cieCfg_common_lib

Component : oracle.glcm.logging

Saving the inventory oracle.glcm.logging

Component : oracle.glcm.oplan.core.classpath

Saving the inventory oracle.glcm.oplan.core.classpath

Component : glcm_encryption_lib

Saving the inventory glcm_encryption_lib

Component : svctbl_lib

Saving the inventory svctbl_lib

Component : com.bea.core.binxml_dependencies

Saving the inventory com.bea.core.binxml_dependencies

Component : svctbl_jmx_client

Saving the inventory svctbl_jmx_client

Component : cieCfg_wls_shared_lib

Saving the inventory cieCfg_wls_shared_lib

Component : rcuapi_lib

Saving the inventory rcuapi_lib

Component : rcu_core_lib

Saving the inventory rcu_core_lib

Component : cieCfg_cam_lib

Saving the inventory cieCfg_cam_lib

Component : cieCfg_cam_external_lib

Saving the inventory cieCfg_cam_external_lib

Component : cieCfg_cam_impl_lib

Saving the inventory cieCfg_cam_impl_lib

Component : cieCfg_wls_lib

Saving the inventory cieCfg_wls_lib

Component : cieCfg_wls_external_lib

Saving the inventory cieCfg_wls_external_lib

Component : cieCfg_wls_impl_lib

Saving the inventory cieCfg_wls_impl_lib

Component : rcu_dependencies_lib

Saving the inventory rcu_dependencies_lib

Component : oracle.fmwplatform.fmwprov_lib

Saving the inventory oracle.fmwplatform.fmwprov_lib

Component : fmwplatform-wlst-dependencies

Saving the inventory fmwplatform-wlst-dependencies

Component : oracle.fmwplatform.ocp_lib

Saving the inventory oracle.fmwplatform.ocp_lib

Component : oracle.fmwplatform.ocp_plugin_lib

Saving the inventory oracle.fmwplatform.ocp_plugin_lib

Component : wlst.wls.classpath

Saving the inventory wlst.wls.classpath

Component : maven.wls.classpath

Saving the inventory maven.wls.classpath

Component : com.oracle.webservices.fmw.ws-assembler

Saving the inventory com.oracle.webservices.fmw.ws-assembler

Component : sdpmessaging_dependencies

Saving the inventory sdpmessaging_dependencies

Component : sdpclient_dependencies

Saving the inventory sdpclient_dependencies

Component : oracle.jrf.wls.classpath

Saving the inventory oracle.jrf.wls.classpath

Component : oracle.jrf.wlst

Saving the inventory oracle.jrf.wlst

Component : fmwshare-wlst-dependencies

Saving the inventory fmwshare-wlst-dependencies

Component : oracle.fmwshare.pyjar

Saving the inventory oracle.fmwshare.pyjar

Component : mapviewer-client

Saving the inventory mapviewer-client

Component : bitech-analysis-application-thirdparty

Saving the inventory bitech-analysis-application-thirdparty

Component : glcm_common_logging_lib

Component : glcm_common_lib

Saving the inventory glcm_common_logging_lib

Saving the inventory glcm_common_lib

Component : oracle.glcm.opatch.common.api.classpath

Component : glcm_encryption_lib

Saving the inventory oracle.glcm.opatch.common.api.classpath

Saving the inventory glcm_encryption_lib

Component : oracle.glcm.oplan.core.classpath

Component : oracle.glcm.osys.core.classpath

Saving the inventory oracle.glcm.oplan.core.classpath

Saving the inventory oracle.glcm.osys.core.classpath

The install operation completed successfully.

Logs successfully copied to /u01/app/oraInventory/logs.

 

 

Relying in Guaranteed Restore Points? Be careful!

Hi all,

Are you relying on Guaranteed Restore Points (GRP) as a fallback plan for your migration or upgrade strategy? Be careful!

When performing some non-Prod upgrade with the Autoupgrade tool,  after completing the upgrade, I wanted to roll it back and go through the process again,  This is what happened:

SQL> startup
ORA-29702: error occurred in Cluster Group Service operation

When looking for it found this blog post from Mike I missed the last year: https://mikedietrichde.com/2020/11/13/ora-29702-and-your-instance-does-not-startup-in-the-cluster-anymore/

This means my database is not starting anymore! Oh man, glad that I’m in the testing phase!

This caused by of Bug 31561819 – Incompatible maxmembers at CRSD Level Causing Database Instance Not Able to Start.

As per Mike’s post, “you don’t need to even restore or flashback a database to hit this error. A simple instance in NOMOUNT state leads to the same error. Without even any datafile.”

The bug is fixed on:

  • 19.9.0.0.201020 (Oct 2020) OCW RU
  • 18.12.0.0.201020 (Oct 2020) OCW RU
  • 12.2.0.1.201020 (Oct 2020) OCW RU

As being, you should include this patch BEFORE starting any move! Do it right away if you are on these versions!

Also, be aware of the latest change regarding Restore Points propagation on 19c, as per MOS Automatic Propagate Restore Points from Primary to Standby site in 19C (Doc ID 2463082.1)

In my case, the usage is exactly for a 12.1->19c upgrade. So, the fix is not even available (no Extended Support in place). As being, we had to think on alternate fallback plans, like a physical standby. But this is a topic for another post.

So for YOU:

  • Apply this patch if you can!
  • If not, be very careful on the fallback plans and as usual: Test, Test and Test!

See you next post!