DBA

three rman catalog script to find out rman backup of archivelog, controlfile and datafile. TAG input is optional @rman_tag_archivelog.sql @rman_tag_controlfile.sql @rman_tag_datafile.sql sqlplus rmanuser/password@rmancatalog get rman_tag_archivelog.sql set linesize 240 col handle format a120 col piece# format 99999 col bp_key format 999999999999999 col recid format 99999 col stamp format 999999999999999 col thread# format 9999999 col sequence# format 99999999 select distinct al.db_name,al.THREAD#,al.SEQUENCE#,al.NEXT_CHANGE#,al.NEXT_TIME,  bp.BP_KEY,bp.RECID,BS_KEY,handle from RC_BACKUP_ARCHIVELOG_DETAILS al, rc_backup_piece bp where  upper(al.db_name) like upper('%&cdb_name%') and upper(bp.tag)  like upper('%&tag_of_rman_backup%') --al.db_name='DBA1C' and upper(bp.tag)='DBA1C_FULL_DISK_BAC1' and bp.BS_KEY=al.BTYPE_KEY and bp.BACKUP_TYPE='L' and al.id1=bp.STAMP  --and al.BTYPE_KEY=13173376  order by bp.BS_KEY,bp.BP_KEY,al.THREAD#,al.SEQUE...

  OCI PDB ORA-01035: ORACLE only available to users with RESTRICTED SESSION due to Sync Failure ERROR "Sync PDB failed with ORA-65177 during 'alter user' in pdb_plug_in_violations (Doc ID 2503982.1) select CTIME,SQLSTMT,NAME,FLAGS,OPCODE,REPLAY# from PDB_SYNC$ where lower(SQLSTMT) like ('%alter user %'); delete from PDB_SYNC$  where lower(SQLSTMT) like ('%alter user %');

## problem: unplugged pdb xml file mismatch datafile info. # cause: I tested flashback database after re-plug pdb # fix: DBMS_PDB.RECOVER( PDB_DESCR_FILE => 'DBA1_new.xml', PDB_NAME => 'DBA1', FILENAMES => '+DATA/DBA1C/FAF5D035069EFA1BE053327578CE0266/DATAFILE/system.1656.1191011593, ... ## below are journals: rpres: rp_6 alter pluggable database dba1 open instances=all * ERROR at line 1: ORA-65107: Error encountered when processing the current task on instance:1 ORA-01147: SYSTEM tablespace file 30 is offline 09:36:02 2025-03-18 server2 SYS@DBA1C::CDB$ROOT $ SELECT file#, name, status FROM v$datafile WHERE file# = 30; FILE# NAME STATUS ---------- -------------------------------------------------------------------------------- ---------- 30 /u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_14/dbs/UNNAMED00030 SYSOFF 09:36:07 2025-...

  How to create SQL profile Pickup the best hash from the history SYS@TST3 $ @awr_plan_change_v2 0a99buvm9gszh SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO ---------- ------ --------------------------------- ------------- --------------- ------------ ------------ -------------- 1 1 16-NOV-24 11.43.11.000 AM 0a99buvm9gszh 4020639609 4 11,964.283 137,065,366.8 1 2 16-NOV-24 11.44.41.000 AM 0a99buvm9gszh 2 11,985.675 145,016,795.0 60593 1 28-FEB-25 11.00.29.449 AM 0a99buvm9gszh 389675671 2 38.798 18,428,828.0 60593 2 28-FEB-25 11.00.29.454 AM 0a99buvm9gszh 2 34.657 18,428,869.5 60594 2 28-FEB-25 12.00.32.413 PM 0a99buvm9gszh 2 33.034 18,426,252.0 60698 1 04-MA...

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/

 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  ...

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...

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....

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...