Oracle RAC, ASM, Exadata, Cloud, and More..: 2019

ORA-28362 -Wallet key not found

Once TDE is created, it is very important component of the database and never delete or move out of the wallet location. If the old wallet is lost, then the database has to be recreated and the encrypted data is lost.

Typical solution is to restore the old wallet in location.

select * from v$encryption_wallet;

Oracle recommends that the wallet files are placed outside of the $ORACLE_BASE directory to avoid having them backed up to the same location as other Oracle files. In addition, it is recommended to restrict the access to the directory and to the wallet files to avoid accidental removals.

See Metalink Doc ID 1228046.1 master note for TDE

Remote Oracle Net connections hang -Doc ID 1302367.1

Symptoms: Something may occur where there is a connectivity problem between clients or servers and a remote database such as standby or DR site. What happens with a SQLPLUS test hang. For example: sqlplus username/password@remote_sid--> hangs

Cause determination: One potential cause of this condition, is that a TCP/IP network device (firewall, router, etc.) located between the client and server is set to restrict or limit communication through it when nay MTU or packet size is exceeded. This is an MTU setting at the network level, so the systems or network admin needs to analyze and correct the MTU setting. The MTU setting problem is typical issue that caused the sqlplus in the remote site/standby site hang.

To enable traces for the hanging sqplus session:

sqlnet.ora, you need to add
DIAG_ADR_ENABLED=OFF
TRACE_LEVEL_CLIENT=16
TRACE-DIECTORY_CLIENT= <location> is the directory to write trace file to

Solution: Verify the MTU settings from the network devices.

Insert statements cause table contention

Insert contention usually is caused by block level contention such as other session and sequential reads along with the indexes

Troubleshooting steps:

Run ASH reports
Run AWR report when database is good performance v.s when database is bad performance
Ensure tables have latest statistics
Find the objects fragmented below high water mark
Run SQL Tunning Health-check Script (SQLHC) -1366133.1
Review OSW during bad time

Recommendations:

De-fragment the table and related indexes. It's is best to use ALTER TABLE move and ALTER INDEX REBUILD ONLINE and gather new statistics.
It's best to change the PCTFREE to 20% and Indexes rebuilt with PCFREE 20%

Queries to run:

select index_name, INI_TRANS,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,NUM_ROWS,PCT_FREE from dba_indexes where table_name ='your-table-with-insert-issue'

select OWNER, SEGMENT_NAME, SEGMENT_TYPE from dba_extents where file_id = 106 and 50600154 between block_id and block_id + blocks - 1 and rownum = 1;

Latch Free Wait contention issue

To troubleshoot this issue, following the following steps:

1. run AWR reports on each node RAC

2. run AWR diff report

3. Patch 26436717 HIGH RESULT CACHE LATCHES AFTER MIGRATION TO 12.1.0.2

Apply the patch or use workaround:

alter system set "_optimizer_dsdir_usage_control" = 0;

This parameter can be set dynamically

spool query_result.html

set serveroutput on

exec dbms_result_cache.memory_report;

set lines 500

set long 9999

set pages 999

set serveroutput on size 1000000

set feedback off

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title><STYLE TYPE='TEXT/CSS'></STYLE>"

set echo off

select namespace, status, name,

count(*) number_of_results,

round(avg(scan_count)) avg_scan_cnt,

round(max(scan_count)) max_scan_cnt,

round(sum(block_count)) tot_blk_cnt

from v$result_cache_objects

where type = 'Result'

group by namespace, name, status

order by namespace, tot_blk_cnt;

select name, value from v$result_cache_statistics;

spool off

SET MARKUP HTML OFF

set echo on

Remote Diagnostic Agent - RDA

RDA is a command-line diagnostic tool that is designed to reduce the number of support requests for additional information by collecting all of the information which help to resolve the current problem.

Install the RDA is simply to ftp the file and unzip it. To run a collection ./rda.sh -v-e TRC/TRACE=1

Materialized view is not doing a fast refresh

1) For fast refresh, the mlog$ is required ensure that you create mlog$ on tables that involve in the materialized view.
2) Get capability output of the materialized view

SET pages 60 lines 130 feedback on echo off long 1000000
REM
COLUMN capability_name format a30 heading "Capability|Name"
COLUMN possible format a10 heading "Possible"
COLUMN msgtxt format a70 heading "Message|Text"
REM
TTITLE "Materialized View Capabilities Report"
REM
DROP TABLE mv_capabilities_table;
@?/rdbms/admin/utlxmv.sql
EXEC dbms_mview.explain_mview('&&ENTER_OWNER..&&ENTER_MVIEW_NAME');
SELECT capability_name, possible, SUBSTR (msgtxt, 1, 60) AS msgtxt
FROM mv_capabilities_table;