Why and How to enable a DB system event traces?

  Why ?

In certain cases a further information will be needed in order to get know what it is the details of these issue occur, especially for those cases where Oracle consider it as an information ORA like 28 and 3136. Actually these steps can be used for any type of event traces where event is an ORA error expected to be. This is can be enabled for current connected session or sql it is self or for the whole system.

Note*: alter system will be reset and loosed after DB restart.

How ?

Below is steps to enable traces for ora-28 and ora-3436:

1- As sys user , login to sqlplus:

alter system set events '3136 trace name errorstack level 3';

ALTER SYSTEM SET EVENTS '28 trace name errorstack level 12';

2- To make sure trace is enabled you may use on of the below whether to send all details to trace file or get the details on screen as below:

oradebug setmypid

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

3- To send details to file :

oradebug setmypid

oradebug UNLIMIT

alter session set tracefile_identifier='myevents';

oradebug eventdump session

oradebug eventdump process

oradebug eventdump system

oradebug tracefile_name

Why and How I do upgrade from 12.2 to 19 ?

 Why ?

As a main stage in Oracle lifecycle management is performing the upgrade stage, this is will be due to new feature application may need or bug to be fixed, security enhancement, out of support version ..etc. eventually you will do an upgrade , below are steps and resources that I follow while performing the Oracle upgrade to 19c.

How

I used Sun Solaris 11 things here, DB and RAC option will be covered as well.

I used autoupgrade.jar option as a the only recommended way to upgrade to 19c as per oracle seminars.

1-   Install oracle 19c software only as below (use your OS oracle install document):

1.1 create new home for oracle 19 and I created folder/directory for upgrade files.

mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

chown -R oracle:oinstall  /u01/app/oracle/product/19.0.0/dbhome_1

chmod 775 /u01/app/oracle/product/19.0.0/dbhome_1

mkdir /u01/upgrade

1.2 unzip the downloaded file of DB in the created path. Unzip your patch if any to be applied while installation of DB.

unzip -q db19.3.zip -d /u01/app/oracle/product/19.0.0/dbhome_1

unzip -q p31281355_190000_SOLARIS64.zip

1.3 Update the Opatch as needed by readme of that patch and replace it with the new opatch from oracle download.

rm -rf /u01/app/oracle/product/19.0.0/dbhome_1/OPatch/

unzip -q p6880880_190000_SOLARIS64.zip -d /u01/app/oracle/product/19.0.0/dbhome_1

 

1.4 Start the software only installation with applying a patch directly while install.

/u01/app/oracle/product/19.0.0/dbhome_1/runInstaller -applyRU /u01/upgrade/31281355

 

1.5 Copy the password, parameter, network and any dependencies file from old home to new home.

cp dr* /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp orapwTWFADB /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp spfileTWFADB.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

cp *.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/

 

2-   Pre upgrade checkup and preparations.

2.1 Download, configure and run autoupgrade script using oracle doc : 2485457.1

java -jar  autoupgrade.jar -create_sample_file config

 

2.2 After modifying the file with needed details start the analyze stage .

java -jar autoupgrade.jar -config sample_config.cfg -mode ANALYZE

 

2.3 After applying the precheck needed things we will start the actual upgrade , below is needed action in case data-guard is used in placed and broker is configured as well . In Standby DB perform the below

DGMGRL> edit database <standby_db> set state = APPLY-OFF

DGMGRL> edit database <primary_db> set state = TRANSPORT-OFF

DGMGRL> DISABLE CONFIGURATION;

# In primary DB

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

# In standby DB

stop db and listener

 

2.4 to speed upgrade as per the pre-upgrade fix you will need to do the below to check the last update for optimizer gather objects date:

column OPERATION format a40

set linesize 200

select to_char(max(END_TIME),'DD-MON-YY hh24:mi') LATEST, OPERATION

from DBA_OPTSTAT_OPERATIONS

where OPERATION in ('gather_dictionary_stats', 'gather_fixed_objects_stats')

group by operation;

 

2.5 Start the upgrade stage.

java -jar autoupgrade.jar -config sample_config.cfg -mode deploy

 

2.6 To check the status life while it is run in html page.

cd ~/upg_logs/cfgtoollogs/upgrade/auto

python -m SimpleHTTPServer 8000

http://10.10.10.10:8000/state.html

 

3-   Pre upgrade checkup and preparations.

3.1       Convert TDE from sqlnet.ora to parameter based .

How To Convert From Using SQLNET.ENCRYPTION_WALLET_LOCATION To 19c Parameter (WALLET_ROOT and TDE_CONFIGURATION) (Doc ID 2642694.1)

 

3.2       Things to do right after upgrade

• Configure statistics history retention period

• Check space usage:

• Check retention

• Default: 31 days

• Adjust setting

• Example: 10 days.

SQL> select space_usage_kbytes/1024 mb

from v$sysaux_occupants where

occupant_name='SM/OPTSTAT';

SQL> select dbms_stats.get_stats_history_retention from dual;

SQL> exec dbms_stats.alter_stats_history_retention(10);

SQL> exec DBMS_STATS.PURGE_STATS (SYSDATE-10) ;

 

 

3.3       To check health in case of neede

hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above (Doc ID 136697.1)

 

3.4       In case you need to check whether apex is need to be upgrade/used.

Check if any one use apex

 select workspace, view_date, seconds_ago from apex_workspace_activity_log

check work space

select workspace, workspace_id from apex_workspaces;

Why and How to perform a session tracing in Oracle ?

  Why ?

In oracle in certain cases you will need to trace the session and see the details of where and how it is work and see all details of error if any, this is trace and it is level.

How ?

Below are some knowledge article from oracle that I used to reach this matter :

1-      First :

How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug (Doc ID 1058210.6)

2- Second  :

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues (Doc ID 376442.1)

3- Third

How to Trace a Procedure From Active Session And View All Statements and Continually Track All or Some of the Statements Using 10046 Tracing (Doc ID 2412465.1)

Why and How to create a core dump file of crashed process ?

  Why ?

If a process got crashed then creating a crash dump files that is contains everything occur to analysis it to find the source cause of this issue, below is steps to create this dump files.

How ?

Below is steps to create a dump file for a specific process :

1- As a root user find and change directory to that process :

cd /path/of/process/fle

ps aux | grep process

2- To make sure parameter files allow any size of dump  :

prlimit --pid <PID process> --core=unlimited

3- To create the core dump file

gdb process <PID>

(gdb) generate-core-file

(gdb) detach

4- To create an analysis file of that core dump to read it as well

 python lib_extractor.py process /path/to/file/core.<PID process>

Why and How I do manage a certificate (cer, jks..etc) files or keystore things ?

 Why ?

In certain cases I do need to use or configure a certificates files , I do use the below tools and/or commands to manage my things .

How ?

Below is name of utilities  :

1- On Windows based host install and user this :

KeyStore Explorer

KeyStore Explorer is an open source GUI replacement for the Java command-line utilities keytool and jarsigner

https://keystore-explorer.org/

2- On Linux/Unix  :

Openssl

openssl - OpenSSL cryptographic and Secure Sockets Layer toolkit

3- On Linux / Unix

keytool

Key and Certificate Management Tool

Why and How to check the tablespace size using script file for specific size ?

  Why ?

To check the tablespace status in a script file that is return a value of 0 OK or 1 not OK a long with Tablespaces details , I am using the below bin bash script which is show all needed details and I configured a waring for 12GB then error for 9GB otherwise all are ok , you may change this upon needed .

How ?

Below is bin bash script and I am assuming you make sure to set the profile things in the running user  :

1- create and set up the script file:

vi new_check_tablespace.sh

chmod +x new_check_tablespace.sh

2- in the script.sh file  :

#!/bin/bash

export PATH=/usr/bin:/usr/sbin

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

export ORACLE_SID=ORCL

export PATH=$PATH:$ORACLE_HOME/bin

check_tbspace() {

temp1=`sqlplus -s / as sysdba <<EOF

set serveroutput on;

SET FEEDBACK OFF

DECLARE

temp integer ;

CURSOR table_space IS

SELECT tablespace_name,

       size_mb,

       free_mb,

       max_size_mb,

       max_free_mb,

       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,

       RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct

FROM   (

        SELECT a.tablespace_name,

               b.size_mb,

               a.free_mb,

               b.max_size_mb,

               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb

        FROM   (SELECT tablespace_name,

                       TRUNC(SUM(bytes)/1024/1024) AS free_mb

                FROM   dba_free_space

                GROUP BY tablespace_name) a,

               (SELECT tablespace_name,

                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,

                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb

                FROM   dba_data_files

                GROUP BY tablespace_name) b

        WHERE  a.tablespace_name = b.tablespace_name

       )

ORDER BY tablespace_name;

t_row table_space%ROWTYPE;

begin

temp := 0;

open table_space ;

loop

fetch table_space into t_row ;

exit when table_space%NOTFOUND;

if ( t_row.MAX_FREE_MB <=12288) and (temp <> 1)  then

temp :=1;

DBMS_OUTPUT.PUT_LINE(1);

elsif ( t_row.MAX_FREE_MB <= 119216) and (temp <> 1) then

temp :=2;

DBMS_OUTPUT.PUT_LINE(2);

end if;

END LOOP;

if temp = 0 then

dbms_output.put_line(0);

end if;

close table_space;

END;

/

EXIT;

EOF`

}

tbspace_details() {

sqlplus -s / as sysdba <<EOF

SET PAGESIZE 140 LINESIZE 200;

COLUMN used_pct FORMAT A11;

SELECT tablespace_name,

       size_mb,

       free_mb,

       max_size_mb,

       max_free_mb,

       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,

       RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_pct

FROM   (

        SELECT a.tablespace_name,

               b.size_mb,

               a.free_mb,

               b.max_size_mb,

               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb

        FROM   (SELECT tablespace_name,

                       TRUNC(SUM(bytes)/1024/1024) AS free_mb

                FROM   dba_free_space

                GROUP BY tablespace_name) a,

               (SELECT tablespace_name,

                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,

                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb

                FROM   dba_data_files

                GROUP BY tablespace_name) b

        WHERE  a.tablespace_name = b.tablespace_name

       )

ORDER BY free_pct ;

exit;

EOF

}

status() {

check_tbspace 

tbspace_details

if [ "$temp1" == "1" ]; then

echo "**Warning NOT All TableSpaces have free space more than 12GB "

return 1

elif [ "$temp1" == "2" ]; then

echo "--ERROR One or more TableSpaces have free space less than 9GB "

return 1

else 

echo "++OK All TableSpaces have free space more than 10GB "

return 0

fi

}

###################### END "STATUS" SECTION ##############################

case "$1" in

    'status')

        status

        ;;

    'root')

su  oracle -c "cd /u01/app/monit/script && ./new_check_tablespace.sh status"

        ;;

    *)

        echo  $"Usage: $0 {status}" 

        exit 1

esac

exit 0