Stesp to Add/Resize Redolog files
Steps to Resize the Online Redo Logfiles >sqlplus /nolog SQL> connect / as sysdba SQL> select name,open_mode from v$database; NAME OPEN_MODE ——— ——————– PROD READ WRITE Please check the below…
Read MoreScript to compare two initoras.sh file
Please use the below script. #!/bin/ksh file1=$1 file2=$2 for parameter in `cat $file1 | grep = | awk ‘{print $1}’ | grep -v ‘#’` do value1=`grep ^$parameter $file1 |…
Read MoreQuery to check snapshot refresh are successfully completed and refresh for materialized views.
Please use the below query. SELECT DISTINCT(TRUNC(last_refresh)) FROM dba_snapshot_refresh_times; select owner, mview_name, last_refresh_type, last_refresh_date from dba_mviews;
Read MoreQuery to check whether database has any externally authenticated SSL users
Please use the below query. SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = ‘GLOBAL’;
Read MoreScript to check for corruption in the database dictionary
Set verify off Set space 0 Set line 120 Set heading off Set feedback off Set pages 1000 Spool analyze.sql SELECT ‘Analyze cluster “‘||cluster_name||'” validate structure cascade;’ FROM dba_clusters WHERE…
Read MoreScript to check duplicate objects in SYS and SYSTEM
Please use the below query to check the same. column object_name format a30 select object_name, object_type from dba_objects where object_name||object_type in (select object_name||object_type from dba_objects where owner = ‘SYS’) and…
Read MoreORA-56920: a prepare or upgrade window or an on-demand or datapump-job loading of a secondary time zone data file is in an active state
Please use the below to fix the issue. CONN / as sysdba alter session set “_with_subquery”=materialize; alter session set “_simple_view_merging”=TRUE; set serveroutput on VAR numfail number BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail, parallel…
Read MoreORA-01591: lock held by in-doubt distributed transaction 1.92.66874
Below is an example for transaction id 1.92.66874, you need to replace correct transaction id. Trying to manually commit or rollback this transaction commit force ‘1.92.66874’; ORA-02058: no prepared…
Read MoreQuery to get session details for pending transactions in Oracle Database
Please get session detail for transaction 95.22.1516570 (Replace with your transaction id) select t1.sid, t1.username, t2.xidusn, t2.used_urec, t2.used_ublk from v$session t1, v$transaction t2 where t1.saddr = t2.ses_addr; and t2.XIDUSN =…
Read MoreScript to collect all the required information for understanding the Concurrent Processing workload on a system
REM HEADER REM $Header: cp_analyzer.sql v1.01 MCOSTA $ REM REM MODIFICATION LOG: REM REM MCOSTA REM REM Consolidated script to diagnose the current status and footprint of Concurrent Processing on…
Read More