Development is suggesting you to apply fix of Bug 33304775 - IPCDAT Errors - IBV Resources are Used for Processes Which Have No Need of Them ( Doc ID 33304775.8 )
There is no workaround, you will need to apply the fix. I'm checking for an available fix for you
Per Dev findings: ============= The suspicion here is that RMAN is forking DB processes which are using a libcell context/IPCDAT context/QPs that are created by RMAN.
Due to a potential kernel issue, the child does not inherit the security context of the parent correctly (it does a deep copy as expected, but the ibverbs specific code is only doing a shallow comparison of the security context pointer).
If that happens, any ibv_modify_qp() call by the child will hit an EACCES for QPs created by RMAN.
The fix is to make sure that RMAN does not create QPs at all (it should not need to, since it does not do IOs with the cell directly). Bug 33304775 prevents processes from creating QPs unnecessarily, and could help with this issue. =============
We had a couple of questions for you.. 1. Is this issue reproducible 100% of the time when you try restoring this DB? if yes, how much time does it take from the time the restore is started.. 2. Do you see the same issue across other DBs that you're migrating to 19c? 3. Are there any other DB or OS processes that are impacted during the same time ?
We can try and arrange a Zoom with rman development and Commvault support during US hours if this issue is easily reproducible. Also here is other information that Engg is requesting..
First, if the CommVault software changes uid or gid or any other process credential during runtime.
Second, 1. Copy the attached dtrace script trace.d to the node where they launch RMAM. The dtrace script will log a message for every attempt to change uid or gid. 2. Run “dtrace -s trace.d > /tmp/dtrace.out”. 3. Reproduce the issue. a. While commVault/RMAN are running, capture the output of “cat /proc/<pid>/status” periodically, where <pid> is the pid of commVault and RMAN. 4. Once the issue is reproduced, collect the following: a. The output of “dmesg -T”. b. The ExaWatcher PS data from the period -- /opt/oracle.ExaWatcher/archive/Ps.ExaWatcher/ /c. var/log/audit/audit.log d. /var/log/messages e. /tmp/dtrace.out f. The periodic dump of “cat /proc/<pid>/status” from Step 3a
XML Publisher Report Bursting Program (XDOBURSTREP) Performance Poor While Initializing ( Doc ID 2300674.1 ) SOLUTION: Replace random with urandom by modifying the Concurrent Program Define Execution Option --All 5 reports completed in acceptable period of time.
This solution may work to resolve the issue. However customer should still work on cleaning the out and log files as this situation will result in further issues. Customer will discuss internally to decide if the purging / moving of the out and log (FSS directory) will occur tonight or be put on hold until the weekend
-------------------- ACTION PLAN DETAILS BELOW---------------------
Customer Applied solution from: XML Publisher Report Bursting Program (XDOBURSTREP) Performance Poor While Initializing ( Doc ID 2300674.1 ) SOLUTION: Replace random with urandom by modifying the Concurrent Program Define Execution Option --All 5 reports completed in acceptable period of time.
(1) Responsibility: System Administrator (2) Navigate to: Concurrent > Program > Define (3) Query report (4) Under "Executable" it shows Name = XDODTEXE and (Options = NULL) or (Options = -Xms512M -Xmx512M) (5) Update Options to = -Xms512M -Xmx512M -Djava.security.egd=file:/dev/./urandom * note the space after -Xmx512M and before -Djava.security (6) Save. (7) Test using report that was updated
Action Plan From Here: ==================== Customer Actions: 1. Customer working on cleaning the out and log Per Doc ID 2603009.1 - Concurrent Processing R12.2 : Too Many Concurrent Requests In Pending Status After Migrating To OCI -- Log and Out files from concurrent processing in FSS directory needs to be restricted to 50000 records only
* Customer discussing internally to decide if the purging / moving of the out and log (FSS directory) will occur tonight or be put on hold until the weekend
2. If performance is still unacceptable, please provide the AWR and analyzer, per earlier action plan as follows 2.1> Generate AWR reports for intervals 20-Sep 13:15:to 13:30 , 13:30 to 13:45 . Generate instance level reports from all RAC instances using awrrpt.sql and addmrpt.sql scripts 2.2> Also run analyzer to check settings : EBS Database Performance and Statistics Analyzer( Doc ID 2126712.1 ) NOTE: It is important that you review saved output file before uploading. If you ran the Analyzer as a Concurrent Program, make sure to choose 'Web Page, HTML only' for the 'Save as type'. Do not save as 'Webpage, complete (*.htm,*.html)' as it will be rejected when uploaded to Support. This applies to all Web browsers.
Oracle-Provided Diagnostic Tools
• Individual tools
• HANGFG: semi-automates hang and dumpfile generation
• HANGFG User Guide (Doc ID 362094.1)
• Procwatcher
• Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes
(Doc ID 459694.1)
• How To Troubleshoot Database Contention With Procwatcher (Doc ID 1352623.1)
• ORATOP
• oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance
(Doc ID 1500864.1)
You can use AWS Database Migration Service (AWS DMS) to migrate your data to and from most widely used commercial and open-source databases such as Oracle, PostgreSQL, Microsoft SQL Server, Amazon Redshift, Amazon Aurora, MariaDB, and MySQL. The service supports homogeneous migrations such as Oracle to Oracle, and also heterogeneous migrations between different database platforms, such as Oracle to MySQL or MySQL to Amazon Aurora MySQL-Compatible Edition. The source or target database must be on an AWS service.
In this guide, you can find step-by-step walkthroughs that go through the process of migrating sample data to AWS:
Amazon RDS supports Oracle SQLTXPLAIN (SQLT) through the use of the SQLT option.
The Oracle EXPLAIN PLAN statement can determine the execution plan of a SQL statement. It can verify whether the Oracle optimizer chooses a certain execution plan, such as a nested loops join. It also helps you understand the optimizer's decisions, such as why it chose a nested loops join over a hash join. So EXPLAIN PLAN helps you understand the statement's performance.
SQLT is an Oracle utility that produces a report. The report includes object statistics, object metadata, optimizer-related initialization parameters, and other information that a database administrator can use to tune a SQL statement for optimal performance. SQLT produces an HTML report with hyperlinks to all of the sections in the report.
Unlike Automatic Workload Repository or Statspack reports, SQLT works on individual SQL statements. SQLT is a collection of SQL, PL/SQL, and SQL*Plus files that collect, store, and display performance data.
Following are the supported Oracle versions for each SQLT version.
SQLT version
Oracle Database 19c
Oracle Database 18c
Oracle Database 12c Release 2 (12.2)
Oracle Database 12c Release 1 (12.1)
12.2.180725
Supported
Supported
Supported
Supported
12.2.180331
Not supported
Supported
Supported
Supported
12.1.160429
Not supported
Not supported
Supported
Supported
To download SQLT and access instructions for using it:
Log in to your My Oracle Support account, and open the following documents:
You must remove tablespaces that are required by SQLT, if they exist.
The SQLT option creates the following tablespaces on a DB instance:
RDS_SQLT_TS
RDS_TEMP_SQLT_TS
If your DB instance has these tablespaces, log in to the DB instance using a SQL client, and drop them.
SQLT option settings
SQLT can work with licensed features that are provided by the Oracle Tuning Pack and the Oracle Diagnostics Pack. The Oracle Tuning Pack includes the SQL Tuning Advisor, and the Oracle Diagnostics Pack includes the Automatic Workload Repository. The SQLT settings enable or disable access to these features from SQLT.
Amazon RDS supports the following settings for the SQLT option.
Option setting
Valid values
Default value
Description
LICENSE_PACK
T, D, N
N
The Oracle Management Packs that you want to access with SQLT. Enter one of the following values:
T indicates that you have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, and you want to access the SQL Tuning Advisor and Automatic Workload Repository from SQLT.
D indicates that you have a license for the Oracle Diagnostics Pack, and you want to access the Automatic Workload Repository from SQLT.
N indicates that you don't have a license for the Oracle Tuning Pack and the Oracle Diagnostics Pack, or that you have a license for one or both of them, but you don't want SQLT to access them.
Note
Amazon RDS does not provide licenses for these Oracle Management Packs. If you indicate that you want to use a pack that is not included in your DB instance, you can use SQLT with the DB instance. However, SQLT can't access the pack, and the SQLT report doesn't include the data for the pack. For example, if you specify T, but the DB instance doesn't include the Oracle Tuning Pack, SQLT works on the DB instance, but the report it generates doesn't contain data related to the Oracle Tuning Pack.
VERSION
2016-04-29.v1, 2018-03-31.v1, 2018-07-25.v1
2016-04-29.v1
The version of SQLT that you want to install.
Note
For Oracle Database 19c (19.0.0.0), the only supported version is 2018-07-25.v1. This version is also the default for Oracle Database 19c.
Adding the SQLT option
The following is the general process for adding the SQLT option to a DB instance:
Create a new option group, or copy or modify an existing option group.
Add the SQLT option to the option group.
Associate the option group with the DB instance.
After you add the SQLT option, as soon as the option group is active, SQLT is active.
To add the SQLT option to a DB instance
Determine the option group that you want to use. You can create a new option group or use an existing option group. If you want to use an existing option group, skip to the next step. Otherwise, create a custom DB option group with the following settings:
For Engine, choose the Oracle edition that you want to use. The SQLT option is supported on all editions.
For Major engine version, choose the version of your DB instance.
Apply the option group to a new or existing DB instance:
For a new DB instance, you apply the option group when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
For an existing DB instance, you apply the option group by modifying the instance and attaching the new option group. For more information, see Modifying an Amazon RDS DB instance.
(Optional) Verify the SQLT installation on each DB instance with the SQLT option.
Use a SQL client to connect to the DB instance as the master user.
SELECT sqltxplain.sqlt$a.get_param('tool_version') sqlt_version FROM DUAL;
The query returns the current version of the SQLT option on Amazon RDS. 12.1.160429 is an example of a version of SQLT that is available on Amazon RDS.
Change the passwords of the users that are created by the SQLT option.
Use a SQL client to connect to the DB instance as the master user.
Run the following SQL statement to change the password for the SQLTXADMIN user:
Upgrading SQLT requires uninstalling an older version of SQLT and then installing the new version. So, all SQLT metadata can be lost when you upgrade SQLT. A major version upgrade of a database also uninstalls and re-installs SQLT. An example of a major version upgrade is an upgrade from Oracle Database 18c to Oracle Database 19c.
Using SQLT
SQLT works with the Oracle SQL*Plus utility.
To use SQLT
Download the SQLT .zip file from Document 215187.1 on the My Oracle Support site.
Note
You can't download SQLT 12.1.160429 from the My Oracle Support site. Oracle has deprecated this older version.
Unzip the SQLT .zip file.
From a command prompt, change to the sqlt/run directory on your file system.
From the command prompt, open SQL*Plus, and connect to the DB instance as the master user.
SQLT generates the HTML report and related resources as a .zip file in the directory from which the SQLT command was run.
(Optional) To enable application users to diagnose SQL statements with SQLT, grant SQLT_USER_ROLE to each application user with the following statement:
GRANT SQLT_USER_ROLE TOapplication_user_name;
Note
Oracle does not recommend running SQLT with the SYS user or with users that have the DBA role. It is a best practice to run SQLT diagnostics using the application user's account, by granting SQLT_USER_ROLE to the application user.
Upgrading the SQLT option
With Amazon RDS for Oracle, you can upgrade the SQLT option from your existing version to a higher version. To upgrade the SQLT option, complete steps 1–3 in Using SQLT for the new version of SQLT. Also, if you granted privileges for the previous version of SQLT in step 7 of that section, grant the privileges again for the new SQLT version.
Upgrading the SQLT option results in the loss of the older SQLT version's metadata. The older SQLT version's schema and related objects are dropped, and the newer version of SQLT is installed. For more information about the changes in the latest SQLT version, see Document 1614201.1 on the My Oracle Support site.
Note
Version downgrades are not supported.
Modifying SQLT settings
After you enable SQLT, you can modify the LICENSE_PACK and VERSION settings for the option.
To remove SQLT from a DB instance, do one of the following:
To remove SQLT from multiple DB instances, remove the SQLT option from the option group to which the DB instances belong. This change affects all DB instances that use the option group. For more information, see Removing an option from an option group.
To remove SQLT from a single DB instance, modify the DB instance and specify a different option group that doesn't include the SQLT option. You can specify the default (empty) option group or a different custom option group. For more information, see Modifying an Amazon RDS DB instance.
