Sadan's Oracle dba Blog

set pages 9999 lines 300 col OPEN_MODE for a30 col HOST_NAME for a30 col INSTANCE_START_TIME for a20 col UPTIME for a20 col DATABASE_STATUS for a15 col VERSION for a10 col LOG_MODE for a10 col INSTANCE_NAME for a10 col DATABASE_ROLE for a10 select INST_ID, INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, OPEN_MODE, version, DB_VERSION, DATABASE_STATUS, DATABASE_ROLE, LOGINS, log_mode, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "instance_start_time", to_char(sysdate - STARTUP_TIME) "UPTIME", to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "CURRENT_TIME" from v$database, gv$instance; SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial||''' IMMEDIATE;' FROM v$session where sid=973; ALTER SYSTEM KILL SESSION '1921,26176,@2' IMMEDIATE; SELECT sid, serial#, inst_id, username, status FROM gv$session WHERE username='EPCAUTH03OWNER'; SELECT 'ALTER SYSTEM KILL SESSION '''||inst_id||...

coe_xfr_sql_profile.sql in Oracle 19c How to find the coe_xfr_sql_profile.sql script How to find the  coe_xfr_sql_profile.sql  script Please find the  coe_xfr_sql_profile.sql  script below which you can use to pin the flipped plan for an SQL ID in Oracle. Sometimes the application team faces issues with the  long running queries in Oracle  and they come to the DBA team and ask please check if there is any plan flip so at that time if there is a plan file you can use the below script to pin the good plan again. SPO coe_xfr_sql_profile.log; SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>; SET SERVEROUT ON SIZE UNL; REM REM $Header: coe_xfr_sql_profile.sql 2020/03/10 carlos.sierra $ REM REM AUTHOR REM Carlos Sierra REM REM SCRIPT REM coe_xfr_sql_profile.sql REM REM DESCRIPTION REM This script generates another that contains the commands to R...

  Table of Contents Introduction How to resolve the High CPU issue in Oracle 19c 1. Log in to the Server and run the top command 2. Check the Active Session details 3. Find the SPID, SID, SERIAL# and SQL_ID details in a Single Query 4. Find out the SQL_TEXT from SQL_ID 5. Check Blocking Sessions 6. Check the details of the Schema and Tables 7. Check the Stats and Lock status of the Tables 8. Check the Index and its Partition Stats 9. Find out the history of PLAN_HASH_VALUE for the SQL_ID 10. Gather Table and Index Stats 11. High-Level Points that are the reason for the HIGH CPU issue in Oracle Conclusion Introduction When working on database environments, one of the most common issues every DBA encounters is  high CPU utilization . Typically, the application team approaches the DBA and reports that their queries are taking longer than usual or the application is slower. They request an investigation, and the first step a DBA usually performs is checking the  CPU load ...

 =======Oracle asm grid installation ======= systemctl stop firewalld systemctl disable firewalld systemctl status firewalld cat /etc/selinux/config sed -i --follow-symlinks 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config cat /etc/selinux/config systemctl restart systemd-hostnamed groupadd asmadmin groupadd oinstall groupadd asmdba usermod -g oinstall oracle usermod -a -G asmdba oracle useradd -u 54323 -g oinstall -G asmadmin,asmdba grid passwd grid usermod -a -G vboxsf oracle usermod -a -G vboxsf grid mkdir -p /u01/app/oracle/product/19.0.0/db_1 mkdir -p /u01/app/grid mkdir -p /u01/app/19.0.0/grid chown -R grid:oinstall /u01 chown -R oracle:oinstall /u01/app/oracle chmod -R 775 /u01 yum install oracleasm-support oracleasm configure -i grid oinstall y y fdisk -l | grep "Disk /dev/sd" then press: n, p, 1, ENTER, ENTER, w – to apply changes 2. Verify that the partitions are created. fdisk -l | grep "/dev/s" oracleasm createdisk OCRDISK1 /dev/sdb1 ...

yum install -y oracle-database-preinstall-19c groupadd asmadmin groupadd asmdba groupadd asmoper useradd -g oinstall -G asmadmin,asmdba,asmoper grid usermod -G asmdba -a oracle systemctl stop avahi-daemon.service systemctl stop avahi-daemon.socket systemctl disable avahi-daemon.service systemctl disable avahi-daemon.socket mkdir -p /u01/app/oraInventory chown grid:oinstall /u01/app/oraInventory chmod 775 /u01/app/oraInventory mkdir -p /u01/app/grid chown grid:oinstall /u01/app/grid chmod 775 /u01/app/grid mkdir -p /u01/app/19.0.0/grid chown grid:oinstall /u01/app/19.0.0/grid mkdir -p /u01/gridtmp chown grid:oinstall /u01/gridtmp chmod 775 /u01/app/19.0.0/grid mkdir -p /u01/app/oracle chown oracle:oinstall /u01/app/oracle mkdir -p /u01/oratmp chown oracle:oinstall /u01/oratmp chmod 775 /u01/app/oracle mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 chown oracle:oinstall /u01/app/oracle/product/19.0.0/dbhome_1 chmod 775 /u01/app/oracle/product/19.0.0/dbhome_1 mkdir -p /u01/software cd /...