WARNING: Procedure APEX_ADMIN could not be added to allow list. The function or procedure is a common object. You must execute the following as a common user: begin APEX_240100.wwv_flow_listener.sync_ords_gateway_allow_list; end; WARNING: ORDS migrated 0 entry points from APEX to ORDS PL/SQL Procedure Gateway Allow List.
Not Found HTTP Status Code: 404 Request ID: EHFC-h-DNoA9A0Qo-_W Request Timestamp: 2024-12-17T13:20:38.684973Z Error Code: ORDS-2201 The procedure named apex could not be accessed, it may not be declared, or the user
executing this request may not have been granted execute privilege on the procedure, or a
function specified by security.requestValidationFunction configuration property has prevented
access. Check the spelling of the procedure, check that the execute privilege has been
granted to the caller and check the configured security.requestValidationFunction function. If
using the PL/SQL Gateway Procedure Allow List, check that the procedure has been allowed
via ords_admin.add_plsql_gateway_procedure.
SQL> select version,comments from dba_registry_history where action='UPGRADE' order by action_time; VERSION COMMENTS _____________ _________________________________________ 10.2.0.3.0 Upgraded from 9.2.0.6.0 10.2.0.4.0 Upgraded from 10.2.0.3.0 10.2.0.5.0 Upgraded from 10.2.0.4.0 11.2.0.3.0 Upgraded from 10.2.0.5.0 12.1.0.2.0 Upgraded from 11.2.0.3.0 12.2.0.1.0 Upgraded from 12.1.0.2.0 12.2.0.1.0 Upgraded from 12.1.0.2.0 19.0.0.0.0 Upgraded from 12.2.0.1.0 to 19.7.0.0.0
SQL> select username,common from dba_users where oracle_maintained='Y';
USERNAME COMMON
_____________ _________
SYSTEM NO
SYS NO
SYSDG YES
SYSKM YES
SYSBACKUP YES
SYSRAC YES
DBSNMP NO
... ...
FLOWS_FILES YES
APEX_PUBLIC_ROUTER YES
APEX_240100 YES<entry key="security.requestValidationFunction">ords_util.authorize_plsql_gateway</entry>
<entry key="security.requestValidationFunction">wwv_flow_epg_include_modules.authorize</entry>
connect / as sysdba create table sys.backup_of_user$ as select * from sys.user$; create table sys.backup_of_user$_updated as select * from sys.user$ where bitand(spare1, 256) = 256;
connect / as sysdba update user$ set spare1 = spare1 - bitand(spare1, 128) + 128 where bitand(spare1, 256) = 256; commit;
alter system flush shared_pool;
SQL> select username,common from dba_users where oracle_maintained='Y';
USERNAME COMMON
_____________ _________
SYSTEM YES
SYS YES
SYSDG YES
SYSKM YES
SYSBACKUP YES
SYSRAC YES
DBSNMP YES
... ...
FLOWS_FILES YES
APEX_PUBLIC_ROUTER YES
APEX_240100 YES
-- -----------------------------------------------------------------------------------
-- File Name : sess_blocking_tree.sql
-- Author : https://www.dbaplus.ca
-- Description : Displays session blocking heirarchy
-- Requirements : Access to the following views
-- gv$instance
-- gv$session
-- Usage : @sess_blocking_tree
-- Last Modified: 25-Nov-2024
-- -----------------------------------------------------------------------------------
set termout off
-- ----------------------------------------------------
-- Save current SET variable
-- ----------------------------------------------------
column v_set_save_file new_value V_SET_SAVE_FILE
select 'tmp_SQLPlus_ENV_&_USER' v_set_save_file from dual;
STORE SET &V_SET_SAVE_FILE replace
-- ----------------------------------------------------
-- Save current NLS_DATE_FORMAT
-- ----------------------------------------------------
column v_nls_date_format new_value V_NLS_DATE_FORMAT
select sys_context('USERENV','NLS_DATE_FORMAT') v_nls_date_format from dual;
-- ------------------------------------------------------------------------
-- Show INST_ID column when RAC database has more than one instance running
-- ------------------------------------------------------------------------
column v_inst_id new_value V_INST_ID
select decode(count(*),1,'','INST_ID,') v_inst_id from gv$instance;
-- ------------------------------------------------------------------------
-- SET env variable
-- ------------------------------------------------------------------------
set linesize 512
set pagesize 999
set verify off
column username format a30
column osuser format a10
column machine format a25
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on
-----------------------------------------------------------------------------
SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser, &V_INST_ID
s.sid,
s.serial#,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
s.logon_Time
from gv$session s
where level > 1
or exists (select 1
from gv$session
where blocking_session = s.sid and blocking_instance=s.inst_id)
connect by prior s.sid = s.blocking_session
and prior s.inst_id=s.blocking_instance
start with s.blocking_session is null;
---------------------------------------------
-- Restore SET variable and NLS_DATE_FORMAT
---------------------------------------------
set termout off
start &V_SET_SAVE_FILE
alter session set nls_date_format='&V_NLS_DATE_FORMAT';
column v_set_save_file clear
column v_nls_date_format clear
column v_inst_id clear
set termout on
Windows Domain controller: DC01.lab.dbaplus.ca - Windows 2022 Workstation 1: wkstn01 – Windows 11 + Oracle Client 19.3+ RU 19.22 Workstation 2: wkstn02 – Oracle Linux 9.3 + Oracle Client 19.3
After applying patches on Oracle Database home with out-of-place patching. The listener cannot be started with error,
TNS-01201: Listener cannot find executable <ORACLE_HOME>/bin/oracle for SID <ORACLE_SID>
This PowerShell script picks up server machines from given text file which includes all SQL server host names.
In order to run the script successfully, it needs following requirements,
1. User who runs the script has to be a domain user account who has start/stop SQL server privileges on all SQL servers listed by the text file.
2. All SQL servers are member of same domain, and each server is listed as one line in a text file.
3. The script file accepts two parameters. First one is operation directive, it should be start, stop or status. Second parameter is full path of SQL server list file, the script will read all servers from the list file.
$ cd $OMS_HOME$ rm -rf sqldeveloper
2023-01-29T15:55:03.286965-05:00 Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc (incident=97764) (PDBNAME=PDB1): ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], [] PDB1(3):Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2023-01-29T15:55:03.291141-05:00 Errors in file /u01/app/oracle/diag/rdbms/cdb01/cdb01/trace/cdb01_mz00_513756.trc: ORA-00600: internal error code, arguments: [qosdExpStatRead: expcnt mismatch], [65561], [25], [3125688], [1], [], [], [], [], [], [], []
When creating Oracle 19c databases, got errors as following,
comment on table AUDSYS.AUD$UNIFIED is
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table
"AUDSYS"."AUD$UNIFIED".It was reported by Oracle support on 19.5. It is still there when creating non-cdb on 19.16. ORA-02291: integrity constraint (APEX_200100.WWV_FLOW_FND_GU_INT_G_FK) violated - parent key not found ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 2067 ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_API", line 339 ORA-06512: at line 2 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.
$ srvctl start database -db db01 PRCR-1079 : Failed to start resource ora.db01.db CRS-5017: The resource action "ora.db01.db start" encountered the following error: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/db01/parameterfile/spfiledb01.ora' ORA-17503: ksfdopn:10 Failed to open file +DATA/db01/parameterfile/spfiledb01.ora ORA-27300: OS system dependent operation:open failed with status: 13 ORA-27301: OS failure message: Permission denied ORA-27302: failure occurred at: sskgmsmr_7 . For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/host01/crs/trace/ohasd_oraagent_grid.trc". CRS-2674: Start of 'ora.db01.db' on 'host01' failed
sys@rac01> alter pluggable database pdb1 open; alter pluggable database pdb1 open * ERROR at line 1: ORA-12963: A read-only instance cannot be the first instance to open a database.
When installing Oracle Grid Infrastructure (GI) 12c or higher version (18c, 19c, 21c), multiple network interfaces (NIC) can be used as private interconnection to implement inter-communication load balance and failover. However, if this configuration is adopted on Oracle Linux (OL) 6 or Red Hat Enterprise Linux (RHEL) 6 or higher, the GI installation prerequists checking shows error
The issue happens on Oracle Linux / Red Hat Enterprise Linux 7/8 on which chronyd is adopted as the network time protocol daemon instead of ntpd. When installing Oracle Grid Infrastructure 19c on these platform, the installation fails during prerequisit check, the message shows that
Verifying Network Time Protocol (NTP) ...FAILED
Verifying chrony daemon is synchronized with at least one external time
source ...FAILED
rac01: PRVG-13605 : Query of chrony daemon for its time sources could not
be performed on node "rac01".
PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
"rac01" and produced the following output:
506 Cannot talk to daemon
rac02: PRVG-13605 : Query of chrony daemon for its time sources could not
be performed on node "rac02".
PRVG-2043 : Command "/usr/bin/chronyc sources " failed on node
"rac02" and produced the following output:
506 Cannot talk to daemon
The agent status shows "Agent Unreachable (Post Blackout)" in Oracle Enterprise Manager 13c console. On the agent home page, it shows following error message,
Agent is unreachable as its first severity has not yet come after blackout end.
It is seen in EM 13c, can can be fixed by running following commands on agent host,
[INS-08101] Unexpected error while executing the action at state: 'supportedOSCheck'
Cause - No additional information available. Action - Cantact Oracle Spport Services or refer to the software manual. Summary - java.lang.NullPointerException
Host=host01.dbaplus.ca
Target type=Agent
Target name=host01.dbaplus.ca:3872
Categories=Availability
Message=Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable.
Severity=Critical
Event reported time=Nov 6, 2021 12:36:48 PM EDT
Operating System=SunOS
Platform=sparc
Event Type=Target Availability
Event name=Status
Availability status=Agent Unreachable
Update Details:
Agent Unreachable (REASON = The agent is running but is currently not ready to accept client requests). Host is reachable.
When I shared the source code in my blog before, I used to reference source code by highlighting the code lines with HTML tag. It was not an issue until I had many references to different lines of a long clode block. It could take too much of my time to highlight or mark the reference when I post an article. Therefore, I added the line number to the source code in my posts. The line-numbered code block looks like,
Updated on Thursday, Oct 21, 2021 with following, The error is also found with RU 19.13 (19.13.0.0.211019). I believe it is same thing with 19.12, though I did not test it.
When installing Oracle 19c with patch apply of Database RU 19.11 and OJVM RU 19.11, runInstaller failed with message
Error in invoking target 'irman ioracle idrdactl idrdalsnr idrdaproc' of makefile '/u01/app/oracle/product/19.11.0/dbhome_1/rdbms/lib/ins_rdbms.mk'. See /u01/app/oraInventory/logs/InstallActions2021-05-01_08-40-24PM/installActions2021-05-01_08-40-24PM.log for details.
When custom certificate is configured for OEM 13c, WebLogic Servers installed as part of Enterprise Manager Cloud control (Administration Server and Managed Servers) can be secured with custom certificate using following command,
$OMS_HOME/bin/emclt secure wls
However, the WebLogic Servers and their deployments could show down in OEM console after secured with custom certificate, though they are still running well.
The reason is that the CA involved in issuing the custom certificate for OMS is not "well known", at least it is not accepted by Oracle as default trusted CA. When agent running on OMS server communicates with WebLogic Servers (WLS), WLS is using the custom certificate as his own identification, but agent cannot find trusted certificates of CA involved in issuing the custom certificate from agent's local keystore. Therefore, agent cannot verify the validation of WLS's certificate, and stops the communication with WLS.
The quick fix is to import the certificate of each CA involved in issuing the ticket into agent local keystore with following command,
$AGENT_HOME/bin/emctl secure add_trust_cert_to_jks -trust_certs_loc <ca_certificate_file> -alias <certificate_alias> [-password <keystore_pwd>]
Here, <certificate_alias> is used to identify the certificates saved in the keystore, they must be unique for each certificate, <keystore_pwd> is the password of the keystore, the default value is welcome.
For example, I have installed a CA in my lab network, and the CA issued certificate to my OMS server. The two of my CA server certificates (root certificate & intermediate certificate) has to be imported into agent keystore as following,
$AGENT_HOME/bin/emctl stop agent
$AGENT_HOME/bin/emctl secure add_trust_cert_to_jks -password welcome -alias dbaplus-root -trust_certs_loc /home/oracle/Root_CA_Certificate.txt
$AGENT_HOME/bin/emctl secure add_trust_cert_to_jks -password welcome -alias dbaplus-intermediate -trust_certs_loc /home/oracle/Intermediate_CA_Certificate.txt
$AGENT_HOME/bin/emctl start agent
List the certificates imported into agent monitor keystore,
$AGENT_HOME/jdk/bin/keytool -list -alias <certificate_alias> -keystore $AGENT_INSTANCE_HOME/sysman/config/montrust/AgentTrust.jks -storepass welcome -v
If needed, the certificates can be removed from keystore as following
$AGENT_HOME/jdk/bin/keytool -delete -alias <certificate_alias> -keystore $AGENT_INSTANCE_HOME/sysman/config/montrust/AgentTrust.jks -storepass welcome -v
"EM Jobs Service" target status is showing down in Enterprise Manager Cloud Control(EM) console even though all associated targets are up and running. It could be an issue with the metric collection definition. Usually it is seen at post blackout of associated targets.
The status of EM Jobs Service is aggregated target status, it is calculated based on the status of the associated targets. The associated targets and calculation logic are defined by default when the system is installed, and you can change it later.
The issue can be fixed by changing/restoring Availability Definition of the service as following,
1. In EM Console navigate to the following menu
Targets > Services > Click on "EM Jobs Service" target
2. In "EM Jobs Service" home page, click on the tab "Monitoring Configuration" and then click on the link "Availability Definition"
3. Take a screen shot of the "Availability Definition" configurations, change the definition to a different option and click OK to save it.
For Instance, If Availability Definition is to consider "All key components are up" (default definition), change it to "At least one key component is up" and save change.
4. Now revert "Availability Definition" of the service back to original configration by following the same procedure.
For instance, change and save "Availability Definition" to "All key components are up"
The target status shows up as all components are up.