Skip to main content

Posts

Oracle sql plan baseline enabled vs accepted vs fixed

this is excellent post to test combination  of enabled vs accepted vs fixed https://www.dbi-services.com/blog/enabled-accepted-fixed-sql-plan-baselines/
Recent posts

ORDS: how to restore the OAuth client secret

 I have ORDS configuration in Dev database, when Dev db is refreshed from PROD db using RMAN or pluggable database feature, the ORDS configuration in DEV is replaced by PROD, and that break the application unless we update the app with new ORDS client secret. This document discover the way how to 1. export and import ORDS client 2. restore the ORDS client secret to previous value 3. verify this method works. This demo how to export and import ORDS client on DEV database, further more, modify the ORDS oauth secret back to previous value. This demo how to export and import ORDS client on DEVDB database, further more, modify the ORDS oauth secret back to previous value. Collect existing ORDS configuration: run as dba user 1. Which schema has ORDS enabled?  SQL>         select s.*,m.pattern,type from DBA_ORDS_SCHEMAS s, DBA_ORDS_URL_MAPPINGS m where s.url_mapping_id=m.id; ID URL_MAPPING_ID PARSING_SCHEMA       STATUS     AUTO_REST_ OPS_ALLOWED PRE_HOOK   UPDATED_O UPDATED_BY 

Query plan not in dba_hist_sql_plan

I rely on dba_hist_sql_plan for me to track query performance for each sql_id, however, this time the query plan hash value shows in v$sql, but not recorded in dba_hist_sql_plan, though AWR snapshots are take every 1 hour. When I open myOracle support ticket, the support engineer told me that " The PHV 1779814119 was first loaded on 2023-08-20/08:36:37, and it's still the current execution plan, while PHV 521264029 was historically used and appears in the view dba_hist_sql_plan Once there is a new current PHV different than 1779814119 , you will find this in the historical sql plans when it's flushed by the mmon slaves as part of the AWR activities " I think that's a wrong statement. ChatGPT tells me that topNsql of AWR might be the answer, googling on topNsql results in a few findings such as this one below. I did not test it yet because I do not have DBA permission on the database, so in theory this could be the issue.  https://ironmandba.wordpress.com/modifying

APEX meta information

I use these query to get APEX meta information about APEX version, workspace, application, pages, and table/queries the page are using.  col comp_name format a30 col procedure format a20 col username format a30 col proxy format a30 col client format a30 col APPLICATION_ID  heading "APP_ID" format 9999 select comp_name,version,schema,procedure from dba_registry where comp_name like '%APEX%'; select username,created from dba_users where username like '%APEX%'; select owner,count(*) from dba_objects where owner like 'APEX%' group by owner; select owner,object_type,count(*) from dba_objects where owner like 'APEX_LISTENER'  group by owner,object_type; --select synonym_name,TABLE_OWNER,TABLE_NAME from dba_synonyms where OWNER='APEX_LISTENER'; select * from proxy_users where proxy like '%ORDS%' or proxy like '%APEX%'; -- list workspace and applications SELECT    --  w.workspace_id,     w.workspace,     a.application_id,     a.

ORDS.sql

I use this to display all ORDS settings that I need to know: -- Run this part as dba user, until you see another comment about running as ORDS schema user set echo on set linesize 240 col object_type format a20 col object_name format a40 col proxy,client format a20 col parsing_schema format a20 col status format a10 col pre_hook format a10 col updated_by format a15 col created_by format a15 col auto_rest_auth format a10 COLUMN name FORMAT A60 COLUMN privilege_name FORMAT A60 COLUMN role_name FORMAT A50 COLUMN name FORMAT A40 COLUMN pattern FORMAT A40 COLUMN name FORMAT A20 col client_name format a20 COLUMN client_name FORMAT A30 COLUMN role_name FORMAT A20 set long 5000 column PARSING_SCHEMA format a20 col name format a60 COLUMN name FORMAT A40 COLUMN uri_prefix FORMAT A80 COLUMN uri_template FORMAT A20 COLUMN source_type FORMAT A30 COLUMN source FORMAT A60 col base_path format a30 col name format a40 col pattern format a20 col source format a20 col source_type format a40 col client_sc

Sergey's script of rman Incrementally Updated Backups

  #!/bin/bash # This script invokes rman to perform a daily # incremental backup of db # if ARC key is used it makes an archive log backup if a threshold is exceeded # It checks for errors and sends email if any. # It cleans up this script log files older then the log file retention period, Check "set up variables" section. # set up a debug mode ##set -x host_name=$(hostname) opsys=$(uname) script=$(basename $0) if [ ${opsys} = 'Linux' ]; then     scriptpath=$(dirname $(readlink -f $0)) else     scriptpath=$(dirname $0) fi scriptNoExt=$(echo ${script} | cut -d \. -f 1) paramFilePath=${scriptpath}/${scriptNoExt}.param oraSetFile="${HOME}/.oraset_ss" #---------------- # Functions usage () {     echo "Usage: ${script} db_instance_name type_of_backup"     echo "Where type_of_backup = [empty|ARC|DELARC|DELBACKUPS]"     echo "    empty - backup the whole database"     echo "    ARC - backup and clean up archive logs"     ech