Skip to main content

Posts

query rman catalog for backup info of archivelog, controlfile and datafile

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...
Post a Comment
Read more
Recent posts

pdb in restricted mode

  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 %');
Post a Comment
Read more

create pluggable pdb xml from datafiles

## 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-...
Post a Comment
Read more

oracle import sql profile

  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...
Post a Comment
Read more

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  ...
Post a Comment
Read more

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...
Post a Comment
Read more