HOW TO FIND OUT MOST CPU CONSUMING DBs IN A MULTI DB ENVIRONMENT

echo "" > cpu_consuming_dbs.txt
for i in $(grep -iv asm /etc/oratab|grep -iv agent|awk -F":" '{print $1}')
do
export ORACLE_SID=$i
sqlplus -s / as sysdba << EOF >> cpu_consuming_dbs.txt
set head off
SELECT SUM_CPU, (SELECT name FROM v\$database) DB_NAME
  FROM (SELECT SUM (cpu_time)     SUM_CPU
          FROM TABLE (
                   DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
                       begin_snap         =>--You can use -24 for 1 day (def.ret.)
                           (SELECT MAX (snap_id) - 1     snap_id
                              FROM dba_hist_snapshot),
                       end_snap           =>
                           (SELECT MAX (snap_id)     snap_id
                              FROM dba_hist_snapshot),
                       ranking_measure1   => 'cpu_time')));
EOF
done
sort -nk1 cpu_consuming_dbs.txt

HOW TO FIND OUT MOST I/O CONSUMING DBs IN A MULTI DB ENVIRONMENT

echo "" > io_consuming_dbs.txt
for i in $(grep -iv asm /etc/oratab|grep -iv agent|awk -F":" '{print $1}')
do
export ORACLE_SID=$i
sqlplus -s / as sysdba << EOF >> io_consuming_dbs.txt
set head off
SELECT SUM_DISK_READS, (SELECT name FROM v\$database) DB_NAME
  FROM (SELECT SUM (disk_reads) SUM_DISK_READS
          FROM TABLE (
                   DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
                       begin_snap         => --You can use -24 for 1 day (def.ret.)
                           (SELECT MAX (snap_id) - 1     snap_id
                              FROM dba_hist_snapshot),
                       end_snap           =>
                           (SELECT MAX (snap_id)     snap_id
                              FROM dba_hist_snapshot),
                       ranking_measure1   => 'disk_reads')));
EOF
done
sort -nk1 io_consuming_dbs.txt

SET SQL_TRACE VIA LOGON TRIGGER

No need to login with myuser, with any power user run these:

alter session set current_schema = MYUSER;

CREATE OR REPLACE TRIGGER myuser.my_logon_trg
 AFTER
  LOGON
 ON SCHEMA
begin
 execute immediate 'alter session set SQL_TRACE = TRUE';
END;
/

ORA-01503: CREATE CONTROLFILE failed ORA-01227:log string is inconsistent with other logs

Problem:
CREATE CONTROLFILE REUSE DATABASE "MYDB1" RESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01227:log string is inconsistent with other logs

Solution:
CREATE CONTROLFILE REUSE SET DATABASE "MYDB1" RESETLOGS FORCE LOGGING ARCHIVELOG
..

ORA-15177: cannot operate on system aliases

Problem:

ALTER DISKGROUP DATADG RENAME DIRECTORY '+DATADG/MYDB1' TO '+DATADG/MYDB2'

ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15177: cannot operate on system aliases

Solution:

alter session set events '15193 trace name context forever, level 2';
ALTER DISKGROUP DATADG RENAME DIRECTORY '+DATADG/MYDB1' TO '+DATADG/MYDB2'
alter session set events '15193 trace name context off';

ASM SECTOR SIZE CHECK

You’d better check asm and physical disk sector sizes.
In this case they differ, so any problem can encounter:

$ kfed read /dev/oracleasm/disks/MYDB|grep size
kfdhdb.secsize:                    4096 ; 0x0b8: 0x1000
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000


[@]/root $ cd /sys/devices/virtual/block/dm-0/queue
[@]/sys/devices/virtual/block/dm-0/queue $ cat *block*
512
512

ORA-15177: cannot operate on system aliases WHEN RENAME DIRECTORY IN ASM

ALTER DISKGROUP DATADG RENAME DIRECTORY '+DATADG/MYDB1' TO '+DATADG/MYDB2'


ERROR at line 1:

ORA-15032: not all alterations performed

ORA-15177: cannot operate on system aliases


Solution:

alter session set events '15193 trace name context forever, level 2';
ALTER DISKGROUP DATADG RENAME DIRECTORY '+DATADG/MYDB1' TO '+DATADG/MYDB2'
alter session set events '15193 trace name context off';

SQLPLUS INTO SHELL VARIABLE

Be carefull with double escape sequence ( \\ ):

export VLISTENERSTATUS=`sqlplus -s / as sysdba << EOF
set head off feed off pages 0
select trim(nvl(length(value),0)) from v\\$parameter where name='local_listener';
EOF`

echo $VLISTENERSTATUS

HOW TO EXTRACT CREATE SCRIPT FOR ALL SQL PATCHES

SET SERVEROUTPUT ON
SET LINES 1000

DECLARE
    v_sqlhint   VARCHAR2 (500);
BEGIN
    FOR i IN (SELECT REPLACE (name, 'SQL_PATCH_', '') sql_id, signature
                FROM dba_sql_patches
               WHERE name LIKE 'SQL_PATCH%')
    LOOP
        SELECT CAST (EXTRACTVALUE (VALUE (x), '/hint') AS VARCHAR2 (500))    AS outline_hints
          INTO v_sqlhint
          FROM XMLTABLE (
                   '/outline_data/hint'
                   PASSING (SELECT xmltype (comp_data)     xml
                              FROM sys.sqlobj$data
                             WHERE signature = i.signature)) x;

        DBMS_OUTPUT.put_line ('SET SERVEROUTPUT ON');
        DBMS_OUTPUT.put_line ('DECLARE');
        DBMS_OUTPUT.put_line (
            'v_sql_id       VARCHAR2 (13) := ''' || i.sql_id || ''';');
        DBMS_OUTPUT.put_line ('v_patch_name   VARCHAR2 (30);');
        DBMS_OUTPUT.put_line (
            'v_hint           VARCHAR2 (4096) := ''' || v_sqlhint || ''';');
        DBMS_OUTPUT.put_line ('BEGIN');
        DBMS_OUTPUT.put_line (
            'v_patch_name :=SYS.DBMS_SQLDIAG.create_sql_patch (sql_id => v_sql_id, hint_text => v_hint, name => ''SQL_PATCH_'' || v_sql_id);');
        DBMS_OUTPUT.put_line ('END;');
        DBMS_OUTPUT.put_line ('/');
    END LOOP;
END;
/

verify_queryable_inventory returned ORA-01403: no data found WHEN DATAPATCH

Problem:

$ $ORACLE_HOME/OPatch/datapatch -verbose

SQL Patching tool version 19.21.0.0.0 Production on Wed Jan 31 15:04:23 2024

Copyright (c) 2012, 2023, Oracle.  All rights reserved.



Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_124550_2024_01_31_15_04_23/sqlpatch_invocation.log



Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Error: prereq checks failed!

verify_queryable_inventory returned ORA-01403: no data found

Prereq check failed, exiting without installing any patches.



Please refer to MOS Note 1609718.1 and/or the invocation log

/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_124550_2024_01_31_15_04_23/sqlpatch_invocation.log

for information on how to resolve the above errors.



SQL Patching tool complete on Wed Jan 31 15:04:26 2024


Solution:

Set these variables and run datapatch again:

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

export NLS_LANGUAGE=AMERICAN

export NLS_TERRITORY=AMERICA