check Oracle database slow SQL Response

SET LINESIZE 200 PAGESIZE 50000

COL INST_ID FORMAT 999

COL "Response_Time (msecs)" FORMAT 999,999,999,999.99

COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17

SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
INST_ID,
ROUND (VALUE * 10, 2) "Response_Time (msecs)"
FROM GV$SYSMETRIC
WHERE     1 = 1
AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

BEGIN_TIME        END_TIME          INST_ID Response_Time (msecs)                                                                                                                                       
----------------- ----------------- ------- ---------------------                                                                                                                                       

15-JUL-2023 01:05 15-JUL-2023 01:06       1                   .44
15-JUL-2023 01:04 15-JUL-2023 01:05       1                   .49
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

You can also query for the minimum, average and maximum response time for the last minute:

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM GV$SYSMETRIC_SUMMARY
   WHERE     1 = 1
         AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY INST_ID;

BEGIN_TIME        END_TIME          INST_ID Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs)
----------------- ----------------- ------- ------------------------- ------------------------- -------------------------
30-JUL-2023 20:12 30-JUL-2023 21:13       1                       .00                     17.65                     40.38

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_HISTORY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

BEGIN_TIME        END_TIME          INST_ID Response Time (msecs)                                                                                                                                       
----------------- ----------------- ------- --------------------- 
15-JUL-2023 01:03 15-JUL-2023 01:04       1                   .59
15-JUL-2023 01:02 15-JUL-2023 01:03       1                   .52
15-JUL-2023 01:01 15-JUL-2023 01:02       1                   .45
15-JUL-2023 01:00 15-JUL-2023 01:01       1                  1.07
15-JUL-2023 00:59 15-JUL-2023 01:00       1                   .58

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (VALUE * 10, 2) "Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_HISTORY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL INST_ID FORMAT 999
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99

  SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
         TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
         INSTANCE_NUMBER INST_ID,
         ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
         ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
         ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
    FROM DBA_HIST_SYSMETRIC_SUMMARY
   WHERE 1 = 1 AND METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME DESC, INSTANCE_NUMBER;

grep commands patterns and metacharacter classes In Linux escape "\" character importance

 

cat /etc/oratab | grep -e "^[\a-Az-Z0-9\]\{0,8\}\:*/[[:graph:]]*"

Hints:-

Always use caret "^" and "$" without escape "\"  like  "^[[:digit:]]"  or "$[[:digit:]]" .

To include forward search use "\+" or "*"  like "^[[:graph:]]\+" or "^[[:digit:]]*" .

unzip and zip files and tar files in linux

 unzip -o /path/to/source_zip_file.zip -d /path/to/destination

-o :- source_directory

-d :- destination_directory 

unzip 

-o /u01/software/LINUX.X64_19300_GRID_HOME.zip

-d /u01/app/grid

         

OPATCH, OPATCHAUTO AND AUTOUPGRADE.JAR usage In Oracle Database

 Opatch :- 

a java based utility enables the application and rollback patches oracle software.

location:- $ORACLE_HOME/OPatch/

----------------------------------------------------------------------------------------------------------------

opatch prereq CheckSystemPatch -phBaseDir <path_to_patch>
opatch apply <path_to_patch>

opatchauto apply <path_to_patch> -oh <grid_oracle_home> -ocmrf <path_to_ocm_response_file>

opatch lsinventory

opatch lsinventory -patch_id

opatch rollback -id <patch_id>

opatch lsinventory | grep <patch_id>

opatch prereq CheckConflictAmongPatches -phBaseDir <path_to_patch>

------------------------------------------------------------------------------------------------------

Opatchauto :-

orchestration tool generates patching instruction specific to your target configuration then uses opatch to perform patching without user intervention.

location:- $GRID_HOME/OPatch/opatchauto

-----------------------------------------------------------------------------------------

opatchauto apply <path_to_patch> -analyze

opatchauto apply <path_to_patch>

Rolling Patch Apply :-

opatchauto apply <path_to_patch> -oh <grid_oracle_home> -ocmrf <path_to_ocm_response_file>

opatchauto apply <path_to_patch> -verify

opatchauto lspatches

opatchauto rollback -id <patch_id>

opatchauto lspatch <patch_id>

opatchauto prereq CheckConflictAmongPatches <path_to_patch>

--------------------------------------------------------------------------------------------------

autoupgrade.jar :-

automate upgrade of oracle database.

1) first install latest version oracle db software 

2) start 

location:- $ORACLE_HOME/jlib/autoupgrade.jar

cat config.properties

----------------------------------------------------------------------------------

# Source database details

source_home=/path/to/source/oracle_home

source_sid=SOURCE_SID

source_user=SYS

source_password=source_sys_password

source_sysdba_password=source_sysdba_password

# Target database details

target_home=/path/to/target/oracle_home

target_version=19.0.0

# AutoUpgrade behavior

auto_pfile=TRUE

compatible=19.0.0

timezone_upg=yes

datapatch=yes

upgrade_parallelism=4

# Log file locations

log_dir=/path/to/log/directory

log_file=autoupgrade.log

# Optional parameters

parameters=statistics_level=typical

---------------------------------------------------------------------------------------------------

java -jar autoupgrade.jar -config <path_to_config_file> -mode readyness

java -jar autoupgrade.jar -config <path_to_config_file> -mode prechecks

java -jar autoupgrade.jar -config <path_to_config_file> -mode analyze

Apply upgrades:-

java -jar autoupgrade.jar -config <path_to_config_file> -mode deploy

java -jar autoupgrade.jar -config <path_to_config_file> -mode fixups

java -jar autoupgrade.jar -config <path_to_config_file> -mode report

---------------------------------------------------------------------------------------------------

++++++++++++ Always use latest opatch from oracle support websites and Export Oracle environments before doing anything ++++++++++++++++++

always do after autoupgrades/ patching 

$ORACLE_HOME/OPatch/datapatch verbose

RAC SRVCTL & CRSCTL

 Stop DB

sudo -iu oracle

default :- srvctl stop database -d PUNEDB     ---------> will stop database on all nodes

srvctl stop database -d PUNEDB -o normal

srvctl stop database -d PUNEDB -o immediate

srvctl stop database -d PUNEDB -o transactional 

srvctl stop database -d PUNEDB -o abort 

START DB

sudo -iu oracle

default :- srvctl start database -d PUNEDB ----------------> will start database on all nodes

srvctl start database -d PUNEDB -o nomount

srvctl start database -d PUNEDB -o mount        --------> for standby db, backup task

srvctl start database -d PUNEDB -o open          ---------> default behaviour

STOP / START INSTANCE 

sudo -iu oracle

srvctl stop instance -d PUNEDB -i PUNE1

srvctl start instance -d PUNEDB -i PUNE2 

To Stop CLUSTERWARE / CRS :-

sudo -iu root

ps -ef | grep -i d.bin

cd ~/bin 

~/bin # ./crsctl disable crs 

~/bin # ./crsctl stop crs -f 

To Start CLUSTERWARE /CRS :-

sudo -iu root 

ps -ef | grep -i d.bin

cd ~/bin

~/bin # ./crsctl enable crs

~/bin # ./crsctl start crs 

Start & Stop MRP on Standby database

Stop log apply service (MRP) on standby :-

alter database recover managed standby database cancel;

shutdown immediate;

stop log shipping from primary and shutdown primary database:-

Note:- Run this on primary--> to get log_archive_dest location for standby db's please use below query:-

col dest_name for a30
col status for a10
col destination for a50
select dest_name,status,destination from gv$archive_dest_status where destination is not null;

then :-

show parameter log_archive_dest_state_&n; ------> n is number from above query 

alter system set log_archive_dest_state_&n='DEFER';

shutdown immediate;

To start primary database & enable log shipping use below steps:-

steps to be performed on PRIMARY DB:-

startup;

alter system set log_archive_dest_state_&n='ENABLE';

steps to be performed on STANDBY DB:-

startup nomount;

alter database mount standby database; 

alter database recover managed standby database disconnect from session;

--------- to check status of mrp on standby and (lgwr or lns) on Primary -----

on standby:-

SQL > ! ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep

or 

$ ps -ef | grep -e "pmon\|tnslsnr\|crs\|PARAM\|mrp" | grep -v grep

SQL> 

-------------------------- Final all in one query for MRP DR ARCHIVE LOG GAP --------------

select a.thread# thread,
 k.host_name,
 k.instance_name,
 m.process,
 m.status,
 a.arch_seq arch,
 b.appl_seq appl,
(a.arch_seq-b.appl_seq)
 difference from (select thread#, max(sequence#) 
as arch_seq from gv$archived_log where archived = 'YES' group by thread#) a,
 (select thread#, max(sequence#) as appl_seq from gv$archived_log where applied = 'YES' group by thread#) b,gv$instance k,gv$managed_standby m
 where a.thread# = b.thread# (+) and b.thread# = k.thread# (+) and m.thread#<>0  and m.process like '%MRP%' order by a.thread#, a.arch_seq, b.appl_seq;

----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
    THREAD HOST_NAME                 INSTANCE_NAME    PROCESS   STATUS             ARCH       APPL DIFFERENCE
---------- ---------------------------------------------------------------- ---------------- --------- ------------ ---------- ---------- ----------
         1 host1                                                          instance1        MRP0      APPLYING_LOG     418336     418336          0
         2 host2                                                          instance2        MRP0      APPLYING_LOG     512681     512681          0
         3 host3                                                          instance3        MRP0      APPLYING_LOG     564134     564134          0
         4 host4                                                          instance4        MRP0      APPLYING_LOG     549583     549583          0

**************** The Best Option is Search For Oracle Support Help ***************************************

OPEN STANDBY DB IN READONLY (ACTIVE DATAGUARD)

1. check status of dataguard

select name,open_mode from gv$dataguard;

NAME        OPEN_MODE

----------------------------------

PUNE            MOUNTED

2. Cancel MRP on standby db

alter database recover managed standby database cancel;

3. Open standby db in readonly mode:-

Hint:- db should be in mount mode already.

alter database open read only;

4. Start MRP with real-time log apply on standby db:-

alter database recover managed standby database using current logfile disconnect from session;

5. Verify the standby db status:-

select name, open_mode from gv$database;

NAME        OPEN_MODE

---------------------------------------------------

PUNE        READ ONLY WITH APPLY 

6. Check MRP process is running :-

select process, status, sequence# from gv$managed_standby where process like '%MRP%';

PROCESS        STATUS        SEQUENCE#

----------------------------------------------------

MRP0            APPLYING LOG        2000

=========================================================================

************************** If you need to do manual recovery ********************************

**copy archive logs from  primary db to standby db and perform below steps:-

alter database recover managed standby database cancel;

alter database register logfile  '/u01/oradata/redo03.log';

alter database recover managed standby database using current logfile disconnect from session;

alter database recover managed standby database disconnect from session;

check gap :-

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#

FROM V$ARCHIVE_GAP;

automation of listener and database and RMAN

 For Listener must start at reboot automatically:-

[oracle@kayyum ~]$ cat start_listener.sh
#!/bin/sh
# we will add below environment variable of oracle db
export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
# we will trigger listener command
lsnrctl start

[oracle@kayyum ~]$ crontab -l

@reboot /u02/app/oracle/product/19c/dbhome_1/bin/dbstart > /dev/null 2>&1

@reboot /home/oracle/start_listener.sh > /dev/null 2>&1

#!/bin/sh

export ORACLE_HOME=/u02/app/oracle/product/19c/dbhome_1

export ORACLE_SID=emcdb
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

rman target \/ << EOF  > rman_$ORACLE_SID_$(DATE).log

run 

{

alter system checkpoint;

backup database format '/path/to/backup/location/%d_%t_%U';

}

EOF

alter system suspend & alter system resume In Oracle Database

If the ALTER SYSTEM SUSPEND statement is entered on one system in an Oracle RAC configuration, then the internal locking mechanisms propagate the halt request across instances, thereby suspending I/O operations for all active instances in a given cluster."

 10:41:18 SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------

SUSPENDED
Elapsed: 00:00:00.01
10:41:26 SQL>
10:41:27 SQL>
10:41:28 SQL>
10:41:28 SQL> alter system resume;
System altered.
Elapsed: 00:00:00.63
10:41:34 SQL>
10:41:35 SQL>
10:41:35 SQL> select database_status from v$instance;
DATABASE_STATUS
-----------------
ACTIVE
Elapsed: 00:00:00.01
10:41:43 SQL>

Interactive copy on select HTML PAGE

 <!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>On Select Copy To Clipboard Using JavaScript</title>
    <!--<link rel="stylesheet" href="style.css">-->
    <style>
        body{
    display: flex;
    flex-direction: column;
    align-items: center;
    margin-top: 200px;
}
input{
 box-shadow: 0 0 5px;
}
pre{
 box-shadow: 0 0 5px;
}
textarea{
    white-space: pre-wrap;
    word-wrap: break-word;
    height: auto;
    width: auto;
    resize: none;
    overflow: auto;
box-shadow: 0 0 5px;
}

.container{
    display: flex;
    flex-direction: column;
    flex-wrap: wrap;
    align-items:center;
    gap: 10px;
    border: 2px solid gold;
    margin: 10px;
    padding: 10px;
    background-color: antiquewhite;
}

.reload{
    height: 25px;
    width: 100px;
    box-shadow: 0 0 5px;
}

.output{
    white-space: pre-wrap;
    word-wrap: break-word;
    height: auto;
    width: auto;
    resize: both;
}
    </style>
</head>
<body>
    <div class="container">
        <!--input file-->
        <input type="file" />
        <!--button to reload page-->
        <button class="reload">Refresh Page</button>

        <!--file output in textarea-->
        <textarea class="input" readonly placeholder="input file content"></textarea>

        <!--for testing select copy-->
        <pre class="output" ></pre>

        <!--for testing paste-->
        <textarea  class="output1" placeholder="test paste here"></textarea>
    </div>
</body>
<!--<script src="script.js"></script>-->
<script>
    // addeventlistener select event
document.querySelector('.input').addEventListener('select',(e)=>{
let selection = e.target.value.substring(
    e.target.selectionStart,
    e.target.selectionEnd
);

// assign selection content to pre
document.querySelector('pre').textContent = selection;
let pre = document.querySelector('pre').textContent;

// copy pre tag content to navigator api clipboard
navigator.clipboard.writeText(pre).then(()=>{
    alert("copied to clipboard !");
});
});

// open file
document.querySelector('input').addEventListener('change',function(){
 // filereader constructor
 let filereader = new FileReader();
 //function for onload event
 filereader.onload = function(){
    //output location 'textarea'
    document.querySelector('.input').value = filereader.result;
 }

 // readastext()
 filereader.readAsText(this.files[0]);
 
 //screen width height
 let width = document.querySelector('.input').innerHTML+screen.width;
 let height = document.querySelector('.input').innerHTML+screen.height;
 // resize textarea for content
 document.querySelector('.input').style.width = `${width-100}px`;
 document.querySelector('.input').style.height = `${height}px`;
 
 
});


// reload page button
document.querySelector('.reload').addEventListener('click',()=>{
    window.location.reload();
});
//outpu1 box 
document.querySelector('.output1').addEventListener('input',(e)=>{
//screen width height
 let width = document.querySelector('.output1').innerHTML+screen.availWidth;
 let height = document.querySelector('.output1').innerHTML+screen.availHeight;
 // resize textarea for content
 document.querySelector('.output1').style.width = `${width-100}px`;
 document.querySelector('.output1').style.height = `${height}px`;
});
    </script>
</html>

Why we perform "alter system switch logfile/alter system archive log current (RAC) and alter system checkpoint "

 alter system switch logfile;

switch the logfile in the current instance so log sequence move to another 

number by executing the command without waiting to full the logfile and 

archiving it. forcing it also switches checkpoint.

alter system archive log current;

this forces all redo log files to get archived.

It performs the switch and archiving both.

RAC DB it forces global switch and archiving both in all instances.

for only a thread : since one db instances can contain only one thread....

alter system archive log current thread 1;

alter system checkpoint;

it makes change in checkpoint but not switch redo log file. 

it makes current active redo log to inactive so that we can drop the redo log sequence.

alter system checkpoint;

LOG SWITCH  "alter system switch logfile (STANDALONE)  / alter system archive log current (RAC DB) ------> triggers checkpoint

CHECKPOINT "alter system checkpoint"  ----------------> does not trigger LOG SWITCH

select first_time,first_change#,sequence#,status from v$log;

select checkpoint_change#,checkpoint_time from v$datafile_header;

alter system switch logfile;  

alter system checkpoint;

alter system archive log current;

• ALTER SYSTEM SWITCH LOGFILE;: Manually switches the current redo log file to the next available log file.
• ALTER SYSTEM CHECKPOINT;: Forces a checkpoint, ensuring modified data in the buffer cache is written to data files.
• ALTER SYSTEM ARCHIVE LOG CURRENT;: Manually archives the current redo log file, making it available for backup and recovery.

BEST SQL practice In Oracle Database

 sysdate  - interval '2' hour;

sysdate - interval '2' day;

sysdate - interval '2' month;

sysdate - interval '2' minute;

sysdate - interval '2' second;

---- to enable and disable history on SQLPLUS 

history;

show history;

hist clear;

hist;

set history off;

set history on;

--- fetch or rownum ---

fetch next n rows;

fetch first n rows;

fetch next n rows only;

fetch first n percent rows only;

offset n rows fetch next n rows only;

you can use gv_$view_name instead of gv$view_name