Moving APEX Applications Repository

Hello,
Most likely you land here because you need to migrate APEX Applications/Workspaces from one database to another, correct? You are in the right place!

We’ll use the APEXExport for this end.

Here you have a quick summary of the steps to use the tool, assuming:

  • The source APEX instance is at least 4.2.4.
  • The target instance must be 4.2.4 or higher.

Also, be aware that the APEX installation (the APEX and FLOWS_FILES schemas) cannot be exported in this manner or in any other manner.
So the APEX itself must pre-exist, what we’ll do is migrate the workspaces from one installation to another.

To Export:

1. Use database Export utilities (Datapump or Legacy Export, be aware of the limitations of each) to generate a dumpfile with all DB objects and data that your APEX applications need to run.
This will normally be the objects in the schemas that your APEX workspaces are dependent upon.

2. Run the APEXExport twice as follows:

2.1 First run it using “-expWorkspace” to export all workspaces (This will generate a w*.sql script for each workspace)

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -expWorkspace

2.2 Now run it using “-instance” which will generate a f*.sql script for every application and shared component.

java oracle.apex.APEXExport -db localhost:1521:MYDB -user system -password systems_password -instance

Note that that workspace export should export all of the shared components from the workspaces.
Note that this does not mention RESTful services but if using the APEXExport from 4.2.4 or higher, they will be included.

To Import:

1. Import the dumpfile generated for the regular database schemas your APEX Application use.
2. Import the workspaces via sqlplus as per:
2.1 connect sys / as sysdba
2.2 alter session set current_schema = APEX_040200;
2.3 run the scripts to create the workspaces

@<script_generated>.sql

This will create the workspaces with the same workspace IDs as the source DB.
This also prevents the need to modify the workspace ID contained in each of the application exports.

3. From the same session as above, accomplish the import of each of the application exports.

SQL> @.sql 
SQL> @.sql [...] 
SQL> @.sql

I hope it helps!

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!

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!

Opatchauto Failing on “CheckActiveFilesAndExecutables” during Prerequisite Check

Hi all,
So, very recently when applying the 2021 January CPU in a client environment, the following happened:

[root@dbserver01 32226239]# $ORACLE_HOME/OPatch/opatchauto apply

OPatchauto session is initiated at Sun Mar 14 03:00:06 2021

System initialization log file is /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchautodb/systemconfig2021-03-14_03-00-08AM.log.

Session log file is /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/opatchauto2021-03-14_03-00-13AM.log
The id for this session is 1J89

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19c/db
Patch applicability verified successfully on home /u01/app/oracle/product/19c/db


Executing patch validation checks on home /u01/app/oracle/product/19c/db
Patch validation checks successfully completed on home /u01/app/oracle/product/19c/db


Verifying SQL patch applicability on home /u01/app/oracle/product/19c/db
SQL patch applicability verified successfully on home /u01/app/oracle/product/19c/db


Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/product/19c/grid
Patch applicability verified successfully on home /u01/app/oracle/product/19c/grid


Executing patch validation checks on home /u01/app/oracle/product/19c/grid
Patch validation checks successfully completed on home /u01/app/oracle/product/19c/grid


Preparing to bring down database service on home /u01/app/oracle/product/19c/db
Successfully prepared home /u01/app/oracle/product/19c/db to bring down database service


Bringing down database service on home /u01/app/oracle/product/19c/db
Following database has been stopped and will be restarted later during the session: er1pprd,obiee
Database service successfully brought down on home /u01/app/oracle/product/19c/db


Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/oracle/product/19c/grid
Prepatch operation log file location: /u01/app/oracle/product/crsdata/dbserver01/crsconfig/hapatch_2021-03-14_03-06-15AM.log
CRS service brought down successfully on home /u01/app/oracle/product/19c/grid


Start applying binary patch on home /u01/app/oracle/product/19c/db
Failed while applying binary patches on home /u01/app/oracle/product/19c/db

Execution of [OPatchAutoBinaryAction] patch action failed, check log for more details. Failures:
Patch Target : dbserver01->/u01/app/oracle/product/19c/db Type[sidb]
Details: [
---------------------------Patching Failed---------------------------------
Command execution failed during patching in home: /u01/app/oracle/product/19c/db, host: dbserver01.
Command failed: /u01/app/oracle/product/19c/db/OPatch/opatchauto apply /ora02/soft/jan21cpu/32126842/32226239 -oh /u01/app/oracle/product/19c/db -target_type oracle_database -binary -invPtrLoc /u01/app/oracle/product/19c/grid/oraInst.loc -jre /u01/app/oracle/product/19c/grid/OPatch/jre -persistresult /u01/app/oracle/product/19c/db/opatchautocfg/db/sessioninfo/sessionresult_dbserver01_sidb_2.ser -analyzedresult /u01/app/oracle/product/19c/db/opatchautocfg/db/sessioninfo/sessionresult_analyze_dbserver01_sidb_2.ser
Command failure output:
==Following patches FAILED in apply:

Patch: /ora02/soft/jan21cpu/32126842/32226239/32218454
Log: /u01/app/oracle/product/19c/db/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_03-17-58AM_1.log
Reason: Failed during Patching: oracle.opatch.opatchsdk.OPatchException: Prerequisite check "CheckActiveFilesAndExecutables" failed.

After fixing the cause of failure Run opatchauto resume

]
OPATCHAUTO-68061: The orchestration engine failed.
OPATCHAUTO-68061: The orchestration engine failed with return code 1
OPATCHAUTO-68061: Check the log for more details.
OPatchAuto failed.

OPatchauto session completed at Sun Mar 14 03:19:25 2021
Time taken to complete the session 19 minutes, 19 seconds

opatchauto failed with error code 42

OK, going by parts, let's see what we have on the refered log:

[Mar 14, 2021 3:33:32 AM] [INFO] Start fuser command /sbin/fuser /u01/app/oracle/product/19c/grid/bin/expdp at Sat Mar 14 03:33:32 PDT 2021
[Mar 14, 2021 3:33:32 AM] [INFO] Finish fuser command /sbin/fuser /u01/app/oracle/product/19c/grid/bin/expdp at Sat Mar 14 03:33:32 PDT 2021
[Mar 14, 2021 3:33:32 AM] [INFO] Following active executables are not used by opatch process :


Following active executables are used by opatch process :
/u01/app/oracle/product/19c/grid/lib/libclntsh.so.19.1
[Mar 14, 2021 3:33:32 AM] [INFO] Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:


Following active executables are not used by opatch process :


Following active executables are used by opatch process :
/u01/app/oracle/product/19c/grid/lib/libclntsh.so.19.1
[Mar 14, 2021 3:33:33 AM] [INFO] UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Mar 14, 2021 3:33:33 AM] [SEVERE] OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[Mar 14, 2021 3:33:33 AM] [INFO] Finishing UtilSession at Sat Mar 14 03:33:33 PDT 2021
[Mar 14, 2021 3:33:33 AM] [INFO] Log file location: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/oapatch_2021-03-14_03-06-15AM.log

This is an interesting situation.

After some validations making sure no service is online, the path is writable, oracle and root have the required privilege and access, I found some relevant Oracle notes:

  • 19c Installation Fails with error “libclntsh.so: file format not recognized; treating as linker script” (Doc ID 2631283.1): Pointing to file corruption
  • While Applying a Weblogic Patch, opatch Fails with “Prerequisite check “CheckActiveFilesAndExecutables” failed” Error (Doc ID 2705809.1): Not a DB note and pointing to other processes using the files.
  • Opatch failure due to “CheckActiveFilesAndExecutables” as Remote registry service holding files (Doc ID 2462952.1): Remote registry holding the binaries.
  • Prerequisite Check “Checkactivefilesandexecutables” Failed (Doc ID 1281644.1): Patch requisite miss on 10g
  • Failed to apply PSU due to CheckActiveFilesAndExecutables check failure (Doc ID 2506432.1): SQLPlus holding the binaries.
  • [OCI]: Database System Patching Failed With Error “DCS-10001:Internal Error Encountered: Failure : Failed To Apply” And Opatch Log Shows “Prerequisite check “CheckActiveFilesAndExecutables” failed” (Doc ID 2687607.1): My case is not an OCI and not in RAC.

So, no matches at all.

However, this last note gave me the hints I needed. From Doc ID 2687607.1, for RAC environments:

/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -unlock
/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -init
/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -prepatch
/u01/app/19.0.0.0/grid/crs/install/rootcrs.sh -postpatch

So, in my case, a Standalone On-Premise Database (and GI):

/ora01/app/oracle/product/19c/grid/crs/install/roothas.sh -unlock
/ora01/app/oracle/product/19c/grid/crs/install/roothas.sh -init
/ora01/app/oracle/product/19c/grid/crs/install/roothas.sh -prepatch
[ Apply the patch! ]
/ora01/app/oracle/product/19c/grid/crs/install/roothas.sh -postpatch

Check the output:

[root@dbserver01 jan21cpu]# /u01/app/oracle/product/19c/grid/crs/install/roothas.sh -unlock
Using configuration parameter file: /u01/app/oracle/product/19c/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/product/crsdata/dbserver01/crsconfig/haunlock__2021-03-14_04-00-35AM.log
2021/03/14 04:01:01 CLSRSC-347: Successfully unlock /u01/app/oracle/product/19c/grid
[root@dbserver01 jan21cpu]# /u01/app/oracle/product/19c/grid/crs/install/roothas.sh -init
Using configuration parameter file: /u01/app/oracle/product/19c/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/product/crsdata/dbserver01/crsconfig/roothas_2021-03-14_04-01-09AM.log
[root@dbserver01 jan21cpu]# /u01/app/oracle/product/19c/grid/crs/install/roothas.sh -prepatch
Using configuration parameter file: /u01/app/oracle/product/19c/grid/crs/install/crsconfig_params
The log of current session can be found at:
/u01/app/oracle/product/crsdata/dbserver01/crsconfig/hapatch_2021-03-14_04-01-16AM.log
2021/03/14 04:01:27 CLSRSC-347: Successfully unlock /u01/app/oracle/product/19c/grid
2021/03/14 04:01:27 CLSRSC-671: Pre-patch steps for patching GI home successfully completed.

And now resuming the Opatchauto:

[root@dbserver01 jan21cpu]# cd 32126842/32226239/
[root@dbserver01 32226239]# $ORACLE_HOME/OPatch/opatchauto resume

OPatchauto session is initiated at Sun Mar 14 04:02:07 2021
Session log file is /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/opatchauto2021-03-14_04-02-10AM.log
Resuming existing session with id 1J89

Start applying binary patch on home /u01/app/oracle/product/19c/db
Binary patch applied successfully on home /u01/app/oracle/product/19c/db


Start applying binary patch on home /u01/app/oracle/product/19c/grid

Binary patch applied successfully on home /u01/app/oracle/product/19c/grid


Performing postpatch operations on CRS - starting CRS service on home /u01/app/oracle/product/19c/grid
Postpatch operation log file location: /u01/app/oracle/product/crsdata/dbserver01/crsconfig/hapatch_2021-03-14_04-27-58AM.log
CRS service started successfully on home /u01/app/oracle/product/19c/grid


Preparing home /u01/app/oracle/product/19c/db after database service restarted
No step execution required.........


Trying to apply SQL patch on home /u01/app/oracle/product/19c/db
SQL patch applied successfully on home /u01/app/oracle/product/19c/db

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:dbserver01
SIDB Home:/u01/app/oracle/product/19c/db
Version:19.0.0.0.0
Summary:

==Following patches were SKIPPED:

Patch: /ora02/soft/jan21cpu/32126842/32226239/32218663
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /ora02/soft/jan21cpu/32126842/32226239/29340594
Reason: This patch is not applicable to this specified target type - "oracle_database"

Patch: /ora02/soft/jan21cpu/32126842/32226239/32240590
Reason: This patch is not applicable to this specified target type - "oracle_database"


==Following patches were SUCCESSFULLY applied:

Patch: /ora02/soft/jan21cpu/32126842/32226239/32218454
Log: /u01/app/oracle/product/19c/db/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-02-36AM_1.log

Patch: /ora02/soft/jan21cpu/32126842/32226239/32222571
Log: /u01/app/oracle/product/19c/db/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-02-36AM_1.log


Host:dbserver01
SIHA Home:/u01/app/oracle/product/19c/grid
Version:19.0.0.0.0
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /ora02/soft/jan21cpu/32126842/32226239/29340594
Log: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-11-34AM_1.log

Patch: /ora02/soft/jan21cpu/32126842/32226239/32218454
Log: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-11-34AM_1.log

Patch: /ora02/soft/jan21cpu/32126842/32226239/32218663
Log: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-11-34AM_1.log

Patch: /ora02/soft/jan21cpu/32126842/32226239/32222571
Log: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-11-34AM_1.log

Patch: /ora02/soft/jan21cpu/32126842/32226239/32240590
Log: /u01/app/oracle/product/19c/grid/cfgtoollogs/opatchauto/core/opatch/opatch2021-03-14_04-11-34AM_1.log

OPatchauto session completed at Sun Mar 14 04:31:48 2021
Time taken to complete the session 29 minutes, 43 seconds

And here is the relevant point: This has been happening to me on several environments and servers across the recent weeks. Always for 2021 January CPU.
My guess is that this might have something to do with this CPU binaries set or, most likely, with the latest OPatch version:

[oracle@dbserver01 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.24

I hope it helps you as well!

5 Best Practices for Setting Dispatchers for Shared Connections

Hi all,

Here are 5 Best Practices / Tips for when setting dispatches with Shared Connections:

1. Set local_listener on both instances on the database

alter system set LOCAL_LISTENER=”(address=(protocol=tcp)(port=1521)(host=yourhost))” scope=both sid=’instance_name’;
Ref: Shared Server: Dispatchers Are Not Registered With Listener (Doc ID 465881.1)

2. Dispatchers parameter should be set to utilize the VIP name of the host

alter system set dispatchers='(address=(protocol=tcp)(host=node1-vip))(dispatchers=2)’ scope=both sid=’instance_name’;
Ref: How To Configure Shared Server Dispatchers For RAC Environment (Doc ID 578524.1)

3. Dispatchers count should be set appropriately considering the number of sessions expected to connect to the database

A general rule of thumb is that 1 dispatcher can handle 50 shared server connections with minimal performance impact.
Ref: Shared Server Only: TNS-12518, TNS-12564 and TNS-12602 Errors at Connect Time (Doc ID 1539104.1)

4. Arguments can be used with the dispatchers parameter for closer control of how the shared server sessions are used

SESSIONS – Determines the max sessions allowed for each dispatcher.
CONNECTIONS – The maximum number of network connections to allow for each dispatcher.
Ref: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DISPATCHERS.html#GUID-DCBCCF94-8A73-4805-9138-412DA413FC7C

5. Shared_servers parameter can be set to control total number of shared servers spawned by the database

shared_servers set to 1 –> This will enable shared server sessions on the database.
max_shared_server  –> Specifies the maximum number of shared servers that can run simultaneously.
shared_server_sessions  –> Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables to reserve user sessions for dedicated servers.
Ref: Automatic Shared Server Configuration (Doc ID 265931.1)

See you next post!

Weekly Events #1

 

MARCH 16

 

Oracle Cloud SaaS Awareness Session

Oracle SD-WAN – ensuring application performance and reliability for real-time and other critical applications.

Oracle SD-WAN helps enterprises connect their branches, HQ, data centers, multiple clouds but also vehicles and ships with an autonomous WAN providing greater network reliability and superior application quality of experience while reducing the cost of WAN.

READ MORE

Oracle User Group Get-Together: JDE-DevOps(Blue-Green deployment) and Pluggable DB for Data refreshes

This is a webinar associated with Oracle Benelux User Group and French Oracle Users Group.

Come and join us to see our success in Oracle’s JDE internal development operations with Blue-Green deployment for the JDE Tools updates process and simplification of data refresh process using pluggable databases(PDB) of containerized database(CDB). This session showcases the benefits of Blue-Green deployment strategy, Pluggable Databases and how much Time/Effort we have saved in our internal operations by adopting the latest innovations of DevOps strategies and Pluggable Databases.

READ MORE

Cloud Applications Implementation Best Practices Webinar

ORACLE CS connectx: implementation best practices.

We invite you to attend a LIVE webinar to get started with implementation of your Oracle Cloud Applications. This is a perfect start to your Cloud journey where you will learn about:-

READ MORE

Oracle Maximize Value with Oracle Database 19c

“The Time is Now” for database modernization removing technology debt.

The new normal has accelerated digitalization of many customers across industries. Customers are facing new challenges and need to develop new business models to increase efficiency, be more agile and highly resilient.

Addressing “technology debt” enables customers to move much more rapidly in this direction.Technology debt is not only linked to hardware and software obsolescence, but leaves organisations “lagging” – unable to leverage automation and integration capabilities, lacking superior scalability and availability to be able to handle demand and offer near-zero business service disruption. To add to this, there is an inability to get insights from data in real-time, and increased vulnerability as cyber-attacks grow exponentially and data breaches are commonplace, particularly, as systems remain out-of-date.

READ MORE

DAY ONE – Hyperion on Oracle Cloud Infrastructure – Hands On Lab

See Oracle Cloud Infrastructure (OCI) in action with your Hyperion application in a live hands-on virtual lab.

In this workshop, Oracle cloud engineers will demonstrate the setting up of Oracle Hyperion Financial Management (HFM), Oracle Hyperion Foundation Services, and Oracle Hyperion Financial Data Quality Management Enterprise Edition (FDMEE) on OCI.

READ MORE

   MARCH 17

 

DAY TWO – Hyperion on Oracle Cloud Infrastructure – Hands On Lab

See Oracle Cloud Infrastructure (OCI) in action with your Hyperion application in a live hands-on virtual lab.

In this workshop, Oracle cloud engineers will demonstrate the setting up of Oracle Hyperion Financial Management (HFM), Oracle Hyperion Foundation Services, and Oracle Hyperion Financial Data Quality Management Enterprise Edition (FDMEE) on OCI.

READ MORE

DAY ONE – UTOUG – Training Days 2021

We are going VIRTUAL this year.

UTOUG Training Days brings the best speakers in the country to your doorstep! Network with people who are part of your local community – no hotel required! Explore the session schedule when it becomes available then go ahead and invest in yourself by registering today.

UTOUG is committed to ensuring that all Oracle technologists in our area can learn and grow. Our available scholarships help further that commitment by helping UTOUG members and students attend Training Days.

READ MORE

Oracle Live Event – MySQL Accelerate Live Session: GAMING

Mysql for modern & secure high availability gaming applications.

This Accelerate Live Session is designed to get you on track with the latest DBA trends and technology with real life use cases.

READ MORE

New Autonomous Data Warehouse Innovations

Featuring andrew mendelsohn, evp, oracle database server technologies.

On Wednesday, March 17, Andrew Mendelsohn will announce Oracle’s latest cloud data warehouse innovations that put the full power of your data within the reach of your entire organization. New intuitive, integrated capabilities enable analysts, data scientists, and line-of-business developers to deliver even faster results, accelerating insights and time-to-market. Together with the existing zero-admin capabilities of Oracle Autonomous Data Warehouse, organizations of all sizes can lower TCO and increase ROI while more rapidly gaining maximum value from their data.

READ MORE

Live Webinar: Simplify and Modernize Your On-Premises Cloud using Oracle Private Cloud Appliance

Cloud native technology are rapidly gaining acceptance and is simplifying the building of complex applications, while at the same time enabling organizations to build and deploy these applications faster.

Now, with Cloud native environments integrated with the latest PCA software release, organizations are further empowered to build and run scalable applications in modern, dynamic, highly available environments, which enable loosely coupled systems that are resilient, manageable, and observable.

READ MORE

ZDLRA and MAA integration to reach ZERO RPO everywhere

Zero Data Loss Recovery Appliance (ZDLRA) is an Oracle Engineering System that was it is part of the Oracle Maximum Availability Architecture (MAA) family. But it goes beyond just backup and recovery. In this webinar I will show how to integrate ZDLRA with all kinds of databases that you have, protecting from the simplest single instance to the huge multi-site DG+RAC database. Everything with ZERO Recovery Point Objective (RPO) and multi-site protection. Check in this webinar how you integrate ZDLRA features like Real-Time Redo transport, Multi ZDLRA Replication, and Tape backups with the Oracle MAA Reference Architecture. After the webinar, you will understand why you need to consider ZDLRA for your architecture design and how ZDLRA and MAA can work together to reach maximum protection for all kinds of databases.

READ MORE

   MARCH 18
DAY TWO – UTOUG – Training Days 2021

We are going VIRTUAL this year.

UTOUG Training Days brings the best speakers in the country to your doorstep! Network with people who are part of your local community – no hotel required! Explore the session schedule when it becomes available then go ahead and invest in yourself by registering today.

READ MORE

Cloud-Driven Transformation: Accelerating resiliency and business continuity with Oracle

Today’s organizations are undergoing massive transformation, ramping up digital initiatives across the enterprise. Further driven by the pandemic, according to IDC, at least 50% of organizations in Africa will launch new lines of business driven by DX investments in 2021.

READ MORE

Live Webcast: Optimizing value of your Oracle licenses – best practices for Software Asset Management

Optimizing value of your oracle licenses – best practices for software asset management.

Do you want to gain a better understanding of the various licensing and contract options that Oracle offers? Is the management of your Oracle licenses fixed in a solid and standardized Software Asset Management process? Are you wondering what methods and techniques you can use and what this means for your organization? Then join us for this upcoming webinar!

READ MORE

Meetup: Experiences with Oracle Database Migrations to the Cloud (Moved to April!)

Meetup: Experiences with Oracle Database Migrations to the cloud</strong>

Originally this Meetup was supposed to take place on March 18th. The Meetup will be postponed to a date yet to be determined in April. More information about this will follow as soon as possible.

READ MORE

APEX Office Hours
APEX + Server-side JavaScript = Awesome!
Welcome to the future! Starting with Oracle Database 21c, developers can now execute JavaScript within the database. This functionality is enabled by the Multilingual Engine (MLE), powered by GraalVM. And APEX 20.2 is the first (and only) low code framework on the planet which natively supports server-side JavaScript, out of the box!

READ MORE

   MARCH 19
A la découverte de la nouvelle machine virtuelle d’Oracle – GraalVM

Venez decouvrir graalvm lors de ce webinar.

Une toute nouvelle solution elaborée par “Oracle Lab” qui apporte une valeure ajoutee ultra rapide au Business que vous soyez une Multinational ou une PME.

READ MORE

AWS AQUA for Redshift

Hi all,

Quick one today. Did you see this new release? I’m a bit behind the schedule but trying to catch up with the news. It seems very interesting:

AQUA (Advanced Query Accelerator) for Amazon Redshift is available in preview. AQUA provides a new distributed and hardware-accelerated cache that brings compute to the storage layer for Amazon Redshift and delivers up to 10x faster query performance than other cloud data warehouses.

AQUA is a high-speed cache on top of Redshift Managed Storage that can scale out and process data in parallel across many AQUA nodes. AQUA uses AWS-designed analytics processors that dramatically accelerate data compression, encryption, and data processing on queries that scan, filter, and aggregate large data sets. With this new architecture, customers can run queries quicker than ever before, allowing them to query data directly, even at scale, and giving them more up-to-date dashboards, reducing development time, and making system maintenance easier.

It is available for preview in US East (Ohio), US East (N. Virginia), and US West (Oregon) regions at this point.

For more references:

  • There is a very tech detailed article about it HERE.
  • There is also a very nice TechTalk presenting it HERE.

Let’s keep up to date!

ORA-07445 [kxsPurgeCursor()]

Hi all,
We started getting lots of ORA-07445 errors that are being reported in the Exadata production database from a client.
Checking on the traces we can see that someone is running the below SQL and that is triggering these errors:
declare
i number := 0;
begin
for c1 in (
select address, hash_value, sql_id from v$sql where
last_active_time < sysdate - 1/24
and executions < 2 ) loop
begin
dbms_shared_pool.purge(c1.address || ' ' || to_char(c1.hash_value), 'C');
i := i + 1;
exception when others then
dbms_output.put_line(c1.sql_id);
end;
end loop;
dbms_output.put_line('Cursors purged = ' || to_char(i));
end;
/
I’m not even going to discuss the PL above, it’s purging all new SQLs executed less than 2 times in the last 24hours. I’m not really agreeing with this…
What matters is: in the end, it was being caused because hash value of non-cursor is being passed to dbms_shared_pool.purge().  This is a match to Bug 29281112 – ORA-7445: [kxspurgecursor()+517] [sigsegv] (Doc ID 29281112.8)
There is no actual workaround besides stop passing a non-cursor hash to dbms_shared_pool.purge, however we have a fix for a better addressing of those cases on:

Quick Reference: Oracle Exadata default passwords

It’s not needed to say how important this is to have all the password reset, even though on new Exa hardware the SSH is usually disabled. How ever, oftenly we need to have access in some of the consoles knowing the password was never changed but unaware on what it should be.

For those cases (until you change it, ASAP), here is a quick reference:

Database Server:

  • root/welcome1
  • oracle/welcome1
  • grid/welcome1
  • grub/sos1Exadata

Exadata Storage Servers:

  • root/welcome1
  • celladmin/welcome1
  • cellmonitor/welcome1

InfiniBand switches:

  • root/welcome1
  • nm2user/changeme

Ethernet switches:

  • admin/welcome1

Power distribution units (PDUs):

  • admin/welcome1
  • root/welcome1

Database server ILOMs:

  • root/welcome1

Exadata Storage Server ILOMs:

  • root/welcome1

InfiniBand ILOMs:

  • ilom-admin/ilom-admin
  • ilom-operator/ilom-operator

Keyboard, video, mouse (KVM):

  • admin/welcome1

Change them!

I hope this was useful for you.