Trace scheduler job execution in oracle

 

Scheduler jobs are executed with service name:SYS$USERS

begin

dbms_scheduler.drop_job ('TEST_JOB1');

end;

/

BEGIN dbms_scheduler.create_job ('TEST_JOB1', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE   l_sql VARCHAR2(3999) := ''''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => ''DBMS_SCHEDULER'',action_name => ''CE_STREAM''); DBMS_OUTPUT.PUT_LINE(''This is a test job'');     END;', number_of_arguments => 0, start_date => systimestamp, repeat_interval => null, end_date => null, job_class => 'DEFAULT_JOB_CLASS', enabled => false, auto_drop => false, comments => 'A test Job ' ); END; 

/

Session 1:

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

  

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

BEGIN

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'SYS$USERS',  module_name=>'DBMS_SCHEDULER',  action_name=>'CE_STREAM',waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

END;

/

Session 2:

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

BEGIN

    dbms_scheduler.enable(name => 'TEST_JOB1');

END;

/ 

session 1:

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

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=> 'SYS$USERS',  module_name=>'DBMS_SCHEDULER',  action_name=>'CE_STREAM');

select * from all_Scheduler_jobs where job_name='TEST_JOB1';

select * From all_scheduler_running_jobs ;

select * from all_Scheduler_job_run_Details where job_name='TEST_JOB1';

select * from gv$session where module ='DBMS_SCHEDULER';

select * from gV$ACTIVE_SESSION_HISTORY where module ='DBMS_SCHEDULER';

select *  from dba_hist_active_sess_history where  module ='DBMS_SCHEDULER';

 

 

 col ADR_HOME for a60

 col trace_filename for a80

 set lines 400

 set timing on;

 set feedback on;

 

select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents 

where regexp_like (payload, 'DBMS_SCHEDULER|CE_STREAM|TM_XMD_'  )

and trace_filename like '%.trc'

--and component_name='SQL_Trace' 

and TIMESTAMP > sysdate-1;

order by inst_id;

 col ADR_HOME for a60

 col trace_filename for a80

 set lines 400

 set timing on;

 set feedback on;

 SELECT DISTINCT

    inst_id,

    adr_home,

    trace_filename

FROM

    gv$diag_trace_file_contents

WHERE

    REGEXP_LIKE ( payload,

                  'DBMS_SCHEDULER|CE_STREAM|TM_XMD_' )

    AND trace_filename LIKE '%.trc'

    AND timestamp > sysdate - 1;

order

    by inst_id;

select PAYLOAD from gv$diag_trace_file_contents 

where trace_filename='DBNAME_ora_6338.trc'

and inst_id=1

and adr_home='..'

order by line_number

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

  

  grep -i CE_STREAM  *.trc

schedpoc3_j000_14008.trc:*** ACTION NAME:(CE_STREAM) 2021-08-04T05:33:43.715390-07:00

schedpoc3_j000_14008.trc:DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZONE := :mydate;  broken BOOLEAN := FALSE;  job_name VARCHAR2(128) := :job_name;  job_subname VARCHAR2(128) := :job_subname;  job_owner VARCHAR2(128) := :job_owner;  job_start TIMESTAMP WITH TIME ZONE := :job_start;  job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start;  window_start TIMESTAMP WITH TIME ZONE := :window_start;  window_end TIMESTAMP WITH TIME ZONE := :window_end;  chain_id VARCHAR2(14) :=  :chainid;  credential_owner VARCHAR2(128) := :credown;  credential_name  VARCHAR2(128) := :crednam;  destination_owner VARCHAR2(128) := :destown;  destination_name VARCHAR2(128) := :destnam;  job_dest_id varchar2(14) := :jdestid;  log_id number := :log_id;  BEGIN  DECLARE   l_sql VARCHAR2(3999) := ''; l_count NUMBER := 0; BEGIN DBMS_APPLICATION_INFO.set_module(module_name => 'DBMS_SCHEDULER',action_name => 'CE_STREAM'); DBMS_OUTPUT.PUT_LINE('This is a test job');     END;  :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

[mkusukun@denaa433 trace]$

tkprof schedpoc3_j000_14008.trc schedpoc3_j000_14008.log sys=no sort=EXEELA waits=yes aggregate=no

 TRCSESS command-line utility to consolidate tracing information from several trace files, then run TKPROF on the result.

trcsess  [output=output_file_name]

         [session=session_id]

         [clientid=client_id]

         [service=service_name]

         [action=action_name]

         [module=module_name]

         [trace_files]

 

tkprof input_file output_file

  [ waits=yes|no ] 

  [ sort=option ] PRSELA - Elapsed time spent parsing,EXECPU - CPU time spent executing EXEELA - Elapsed time spent executing

  [ print=n ]

  [ aggregate=yes|no ] 

  [ insert=filename3 ] 

  [ sys=yes|no ]

  [ table=schema.table ]

  [ explain=user/password ] 

  [ record=filename4 ] 

  [ width=n ]

  

TOP N PARTITION FROM EACH PARTITION TABLE

 TOP N PARTITION NAMES FROM EACH PARTITION TABLE IN SCHEMA:

SELECT

    table_name,

    LISTAGG(partition_name, '   ') WITHIN GROUP(

        ORDER BY

            partition_position

    ) AS partition_names

FROM

    (

        WITH rws AS (

            SELECT

                o.*,

                ROW_NUMBER() OVER(

                    PARTITION BY table_name

                    ORDER BY

                        partition_position DESC

                ) rn

            FROM

                user_tab_partitions o

        )

        SELECT

            table_name,

            partition_name,

            partition_position

        FROM

            rws

        WHERE

            rn <= 10

        ORDER BY

            table_name,

            partition_position

    )

GROUP BY

    table_name

ORDER BY

    table_name;

It will display top 10 partitions of each table in a schema.

Output will have 1 row per each table.

How to Enable Trace service name and module, another session in oracle

How to Enable Trace servicename and module, another session:

Note:

Test case 1 and Test case 2 : Enable /Disable tracing of sessions on combination of Service name and Module name

Test 3:   Enable/Disable trace on a particular user based on SID,SERIAL# .

Test case 1:

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

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME',action_name => NULL); 

END;

/

session 2:

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

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

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

create table test1 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME');

 

 

Session 1:

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

exit;

DBNAME1_ora_14963.trc:create table test1 (a number, b number)

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=4 pr=0 pw=0 str=1 time=621 us)'

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=0 pr=0 pw=0 str=1 time=91 us)'

Test case2:

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

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME_2',action_name => NULL); 

END;

/

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME_2',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

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

create table test2 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME_2');

 

  

  

  

DBNAME1_ora_17154.trc:create table test2 (a number, b number)

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

 

TEST CASE 3:

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

 

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

session 2:

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

sqlplus sys as sysdba

col module for a30

col program for a30

col machine for a30

select sid, serial#, module, program, machine,SERVICE_NAME  from gv$session where username is not null;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 392, serial_num => 45590,waits => TRUE, binds => TRUE);

 

 

Session 1:

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

create table test3 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 392, serial_num => 45590);

DBNAME1_ora_19678.trc:create table test3 (a number, b number)

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

Queries to find trace files of particualr SQL or whole trace file:

select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents 

where regexp_like (payload, '8h284jvnd0z4k|g3jkap9hxf75k  )

and trace_filename like 'DBNAME%ora%.trc'

and component_name='SQL_Trace' 

--and TIMESTAMP > sysdate -0.5

select PAYLOAD from gv$diag_trace_file_contents 

where trace_filename='DBNAME_ora_6338.trc'

and inst_id=1

and adr_home='..'

order by line_number

How to run query with bind variables same as it run from application in oracle database

How to run query with bind variables same as it run from application in oracle database
We see queries run from application , we get different sql_id, plan hash value when we take that sql text and rum from command line, sql worksheet or sql developer for those queries that get values at run time in the form of bind variables.
the thing that I am going explain here how can we run a query exactly the way it is get executed from an application.
Take that exact query:

Orignal query:


SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= :B1

this is how I ran

create or replace procedure temp_p_123( param in varchar2 )
as
type rc is ref cursor;
l_cursor rc;
begin
open l_cursor for SELECT TABLE_OWNER FROM TABLE_VIEW_NAME WHERE FIELD_NAME='MARK' AND OWNER= param;
end;

then

- PASS bind variable value exactly as it was passed from application.

begin
temp_p_123( param =>'INPUT' )
end;


then take the explain plan very after query got executed, if you could not find sql_id, get it from GV$sql, dba_hist_active_sess_history etc.

in my case , i see its in cursor cache.
SELECT * from table (dbms_xplan.display_cursor ('sql_id',NULL,'allstats advanced last'));

I found that the same sql_id for same statement that I ran in database is same as the SQL_ID of same statement that ran from application. including plan hash value.

- this test is done to see if sql_profile created is really helped.. and had to evaluate if sql_profile created for sql_id is really helped or execution is really made use of sql_profile.

look for Notes section:
Note
-----
- SQL profile PROFILE_xxxxxxxx used for this statement

How to change max_string_size in oracle RAC container database and pluggable databases

 

Change max_string_size in oracle RAC container database and pluggable databases:

Steps: 

1: create a pfile from spfile.

2. set max_string_size=EXTENDED 

3.  set cluster_database=FALSE 

4. startup upgrade

5. @?/rdbms/admin/utl32k.sql - run it in CDB.

6. set cluster_database=TRUE

7. Restart CDB with srvctl commands and ensure all the instances are up and running.

8. Now we are done with the root container.

9. run utl32.sql in each PDB exactly the way we ran in CDB. please note PDB has to be closed and need to open in upgrade mode. 

10. Steps are given below, see the commands and their output carefully.




bash-4.2$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:09:13 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL> 

SQL> show parameter string_size

NAME                                 TYPE        VALUE

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

max_string_size                      string      STANDARD

SQL> alter system set max_string_size=EXTENDED scope=spfile sid='*';

System altered.

SQL> exit

srvctl stop database -d PRODEXA1 -o immediate




bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:18:41 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password:

Connected to an idle instance.

SQL>  startup nomount;

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size                 30385984 bytes

Variable Size            1.1006E+10 bytes

Database Buffers         6.9793E+10 bytes

Redo Buffers              238047232 bytes

SQL> SQL> SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> SQL>

SQL>

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL>

SQL>  shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>

startup upgrade

ORACLE instance started.

Total System Global Area 8.1068E+10 bytes

Fixed Size                 30385984 bytes

Variable Size            1.1006E+10 bytes

Database Buffers         6.9793E+10 bytes

Redo Buffers              238047232 bytes

Database mounted.

Database opened.

SQL> 

SQL> show parameter string_size

NAME                                 TYPE        VALUE

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

max_string_size                      string      EXTENDED

SQL>

SQL>

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

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

12/20/2020 20:21:27.778469000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered. 

Session altered.

 

SQL> alter system set cluster_database=TRUE scope=spfile sid='*';

System altered.

SQL>

SQL> shutdown immediate;

bash-4.2$  srvctl stop database -d PRODEXA1 -o immediate

PRCC-1016 : PRODEXA1 was already stopped

bash-4.2$   srvctl start  database -d PRODEXA1

bash-4.2$  srvctl status  database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

bash-4.2$

bash-4.2$

bash-4.2$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 20 20:23:29 2020

Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

SQL>

SQL>

SQL> show parameter cluster_database

NAME                                 TYPE        VALUE

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

cluster_database                     boolean     TRUE 

SQL> show parameter max_string_size

NAME                                 TYPE        VALUE

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

max_string_size                      string      EXTENDED

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MOUNTED

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL>

SQL> alter pluggable database pdb$seed open upgrade;

Pluggable database altered.

SQL> alter session set container=PDB$SEED;

Session altered.

SQL>  @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

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

12/20/2020 20:24:43.903946000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL>  alter session set container=cdb$root;

Session altered.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       MIGRATE    YES

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 EXA                            MOUNTED

         4 MC1F6791                       MOUNTED

SQL> alter pluggable database EXA,MC1F6791 open upgrade;

Pluggable database altered.

SQL>  alter session set container=EXA;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

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

12/20/2020 20:26:16.600081000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL> alter session set container=MC1F6791;

Session altered.

SQL> @?/rdbms/admin/utl32k

Session altered.

Session altered.

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database has not been opened for UPGRADE.

DOC>

DOC>   Perform a "SHUTDOWN ABORT"  and

DOC>   restart using UPGRADE.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if the database does not have compatible >= 12.0.0

DOC>

DOC>   Set compatible >= 12.0.0 and retry.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

Session altered.

0 rows updated.

Commit complete.

System altered.

PL/SQL procedure successfully completed.

Commit complete.

System altered.

Session altered.

Session altered.

Table created.

Table created.

Table created.

Table truncated.

0 rows created.

Session altered.

PL/SQL procedure successfully completed.

STARTTIME

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

12/20/2020 20:26:41.482322000

PL/SQL procedure successfully completed.

No errors.

Session altered.

Session altered.

Session altered.

0 rows created.

no rows selected

no rows selected

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01722: invalid number"

DOC>   error if we encountered an error while modifying a column to

DOC>   account for data type length change as a result of enabling or

DOC>   disabling 32k types.

DOC>

DOC>   Contact Oracle support for assistance.

DOC>#######################################################################

DOC>#######################################################################

DOC>#

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Commit complete.

Package altered.

Package altered.

Session altered.

SQL> SQL> SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

bash-4.2$  srvctl stop database -d PRODEXA1 -o immediate

bash-4.2$   srvctl start  database -d PRODEXA1

bash-4.2$   srvctl status  database -d PRODEXA1

Instance PRODEXA11 is running on node host1.my.domain.com

Instance PRODEXA12 is running on node host2.my.domain.com

bash-4.2$

connect to CDB SQL> alter pluggable database all close immediate instances=all; Pluggable database altered. SQL> alter pluggable database all open instances=all; Pluggable database altered. SQL>

ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32. and ORA-30094: failed to find the time zone data file for version 34 in $ORACLE_HOME/oracore/zoneinfo

 Error:
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32.

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        32

conn sys as sysdba 

SQL> ALTER SESSION SET CONTAINER=mypdb;

ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE INSTANCES=ALL;

Session altered.

SQL>

 

Pluggable database altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> STARTUP UPGRADE;

Pluggable Database opened.

SQL> SET SERVEROUTPUT ON

SQL> EXEC DBMS_DST.begin_upgrade(34);

BEGIN DBMS_DST.begin_upgrade(34); END;

*

ERROR at line 1:

ORA-30094: failed to find the time zone data file for version 34 in

$ORACLE_HOME/oracore/zoneinfo

ORA-06512: at "SYS.DBMS_DST", line 84

ORA-06512: at "SYS.DBMS_DST", line 1237

ORA-06512: at line 1

SQL>

SQL>

Solution: Patch, patch, patch …

Note: Ensure that 19c Patch Set Update (PSU) 31281355 is already applied on the Oracle Database.

1. download the  DSTv34 Patch 29997937 . 

2. Unzip   Patch 29997937

3. Apply  Patch 29997937 using Opatch. 

Commands to apply patch and verification:

 opatch version

 opatch lsinventory

  opatch prereq CheckConflictAgainstOHWithDetail -ph ./

  opatch apply

  opatch lspatches

UPGRADE 19c 2 node RAC cluster from 19.3 to 19.8 with July 2020 RU

Environment: 19.3 2 node RAC cluster.

Latest OPatch:                      : p6880880_190000_Linux-x86-64.zip  -- to get OPatch Version: 12.2.0.1.23

GI Release Update (July 2020) : p31305339_190000_Linux-x86-64.zip

Database Release Update (July 2020) : p31281355_190000_Linux-x86-64.zip

RU: Release Updates

followed exactly what is given in READ-ME files however below errors are seen.

  Following active executables are used by opatch process :

[Dec 15, 2020 2:44:55 AM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.

                                    The details are:

                                    Following active executables are not used by opatch process :

                                    //...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1

                                    //...//u01/app/19.3.0/grid/lib/libasmclntsh19.so

                                    Following active executables are used by opatch process :

[Dec 15, 2020 2:44:55 AM] [SEVERE]  OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.

[Dec 15, 2020 2:44:55 AM] [INFO]    Finishing UtilSession at Tue Dec 15 02:44:55 PST 2020

[Dec 15, 2020 2:44:55 AM] [INFO]    Log file location: //...//u01/app/19.3.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-12-15_02-41-24AM_1.log

bash-4.2$ /sbin/fuser //...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1; /sbin/fuser //...//u01/app/19.3.0/grid/lib/libasmclntsh19.so

//...//u01/app/19.3.0/grid/lib/libclntsh.so.19.1: 13192m 13214m 13216m 13245m 13298m 13402m

//...//u01/app/19.3.0/grid/lib/libasmclntsh19.so: 13192m 13214m 13216m 13245m 13298m 13402m

bash-4.2$ ps -elf |egrep '13192 | 13214 | 13216 | 13245 | 13298 | 13402' |grep -iv grep                                                 4 S oracle   13192     1  0  80   0 - 421363 futex_ 01:28 ?       00:01:14 //...//u01/app/19.3.0/grid/bin/oraagent.bin

0 S oracle   13214     1  0  80   0 - 92685 poll_s 01:28 ?        00:00:43 //...//u01/app/19.3.0/grid/bin/mdnsd.bin

0 S oracle   13216     1  0  80   0 - 217186 poll_s 01:28 ?       00:01:39 //...//u01/app/19.3.0/grid/bin/evmd.bin

0 S oracle   13245     1  0  80   0 - 264129 hrtime 01:28 ?       00:00:48 //...//u01/app/19.3.0/grid/bin/gpnpd.bin

0 S oracle   13298 13216  0  80   0 - 111523 poll_s 01:28 ?       00:00:42 //...//u01/app/19.3.0/grid/bin/evmlogger.bin -o //...//u01/app/19.3.0/grid/log/[HOSTNAME]/evmd/evmlogger.info -l //...//u01/app/19.3.0/grid/log/[HOSTNAME]/evmd/evmlogger.log

4 S oracle   13402     1  0 -40   - - 674556 futex_ 01:28 ?       00:01:33 //...//u01/app/19.3.0/grid/bin/ocssd.bin

bash-4.2$

Any attempt to kill anyone of the above processes linked to said two lib* executable resulted two nodes get rebooted automatically even if we ran on single node... 

In my case: I followed 

Prerequisite check CheckActiveFilesAndExecutables failed on executable libclntsh.so.12.1 with opatch rollback (Doc ID 2391109.1)

and ensured all the cluster resources are relocated to another node on which patch is not being applied. 

thereafter I was able apply RU patch, first on Grid, and oracle home. on each of the nodes one after another.

 

Oracle Database schema tables rows count

 We do come across a requirement or want to know count of each and every table in a particular schema or group of schemas. 

what is simplest way of doing this?.  do we need query each and every table ?.. not required as tables are used for some other DMLs .. . we can create a job or run whenever we want to know the count of each and every table in a particular schema. The code given below can be modified to suit to user requirement. 

There are two ways, run code and wait for output or simply run a job that inserts a record in a table, query stats table to see the count . 

Result table can be truncated or measured to see how each table growth is in terms of rows count..  similar option can be applied to find schema size , perhaps once is a day or run once in a week and compare the database growth .. capacity planning.. etc.. many uses. .. 

 

 Create a table:

Create table STATS_TABLE

(TABLE_NAME VARCHAR2(100) not null,

SCHEMA_NAME VARCHAR2(100) not null,

RECORD_COUNT NUMBER(12),

CREATED DATE);

Option 1:

set serveroutput on;

DECLARE

    v_count        INTEGER;

    l_owner_name   VARCHAR2(30) := 'PRODADMIN';

    l_table_name   varchar2(100);

    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (

    SELECT

        table_name,

        owner

    FROM

        all_tables

    WHERE

        owner = l_owner_name

) LOOP 

l_table_name:=concat(concat(r.owner,'.'),r.table_name);

 l_sql_qry:= 'SELECT /*+ parallel */ count (*) FROM '||l_table_name;

    EXECUTE IMMEDIATE l_sql_qry INTO v_count;    

dbms_output.put_line(l_table_name ||' ' ||  v_count);

END LOOP; 

COMMIT;

END;

/

Option 2:

BEGIN

    dbms_scheduler.create_job('XXXXX_JOB_COUNT_TABLES_2', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE     v_count        INTEGER;     l_owner_name   VARCHAR2(30) := ''PRODADMIN'';     l_table_name   varchar2(100);    l_sql_qry     VARCHAR2 (999);

BEGIN FOR r IN (    SELECT        table_name,        owner    FROM        all_tables    WHERE        owner = l_owner_name ) LOOP l_table_name:=concat(concat(r.owner,''.''),r.table_name);  l_sql_qry:= ''SELECT /*+ parallel */ count (*) FROM ''||l_table_name;   EXECUTE IMMEDIATE l_sql_qry INTO v_count;    INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) VALUES (r.table_name,r.owner,v_count,SYSDATE);END LOOP;  COMMIT; END;'

    , number_of_arguments => 0, start_date => systimestamp,

                              repeat_interval => NULL, end_date => NULL, job_class => 'DEFAULT_JOB_CLASS', enabled => TRUE, auto_drop

                              => false,

                              comments => 'A Job that count table rows');

END;

/

ORA-22275: invalid LOB locator specified , do we really have LOB columns in table

 ORA-22275: invalid LOB locator specified , do we really have LOB columns in table:

I came across a customer problem that query returning ORA-22275: invalid LOB locator specified error  from application logs when they ran a query that has join conditions with just two tables . none of the tables in the query have LOB columns, it all number, char , varchar2 data type columns.

the interesting part is the same query get executed from sqlplus command prompt. sql developer and other tool but not from JDBC connection made applications. the query gave same error when running from OEM worksheet too.

 

SELECT

     ..

    cmtf.t_context,

    ..

FROM

    table1       cmt

    LEFT OUTER JOIN 

    table1_fld   cmtf ON cmt.per_name = cmtf.per_name

WHERE

    cmt.per_name = 'MAX RANO KING'

ORDER BY

    cmtf.seq_num,

    cmtf.LOC_NAME; 

there may some other methods to investigate but the method I followed is :

1. check table have lob columns, none of them have lob columns

2. t_context column in both tables has VARCHAR2(4000)

column that has VARCHAR2(4000) really creates the issue???  perhaps testing I did made me to believe is YES. read on How

I ran this query to see what session NLS parameter get set from calling environment.

SELECT DB.PARAMETER, DB.VALUE "DATABASE", I.VALUE "INSTANCE", S.VALUE "SESSION" FROM   NLS_DATABASE_PARAMETERS DB, NLS_INSTANCE_PARAMETERS I, NLS_SESSION_PARAMETERS S WHERE  DB.PARAMETER=I.PARAMETER(+) AND DB.PARAMETER=S.PARAMETER(+) and db.parameter ='NLS_LENGTH_SEMANTICS' ORDER BY DB.PARAMETER;

SQL command prompt, sql developer and other GUI tools NLS_LENGTH_SEMANTICS=BYTE and query get executed successfully whereas in OEM worksheet (perhaps the same case in application session too), the  NLS_LENGTH_SEMANTICS was set to CHAR.

I don't see any other problem and any other difference. 

Does it really matter if even data in that column not having length of size specified at column level?.

is it the inconsistency in storage semantics or NLS settings that we should address in application ?. 
is it because of session nls parameters not compatible with nls database parameters or instance nls parameters ? this seems to make to believe the case YES. 

however more to come on this as when I found more on this. 

any comments and inputs or help is welcome. thanks. 

Configuring Local Undo AND Configuring Shared Undo

Configuring Local Undo:

Shtudown the instance:

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> show con_name

CON_NAME

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

CDB$ROOT

Change the Undo mode to “Local Undo”:

SQL> alter database local undo on;

Reboot the instance:

SQL> shutdown immediate;

SQL> startup;

Verify that the Local Undo is now used:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED TRUE

Configuring Shared Undo

Shutdown the instance:

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> show con_name

CON_NAME

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

CDB$ROOT

Change the Undo mode to “Shared Undo”:

SQL> alter database local undo off;

Reboot the instance:

SQL> shutdown immediate;

SQL> startup;

Verify that the new Undo mode is now used:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'

PROPERTY_NAME PROPERTY_VALUE

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

LOCAL_UNDO_ENABLED FALSE

How to export and import schema stats from one database to another database in oracle

How to import schema stats from one database to another database in oracle:

It is not just importing schema stats from one database to another database, the one of the problem I have come across is export delay or import delay when export or importing whole schema stats with including statistics. 

I found this is useful when making my export and imports faster however the solution is FYI only, you can test and see it yourself.

The steps in exporting and importing schema stats  in summary:

Create the STATS table.

Export the statistics to the STATS table.

Export the STATS table using export(exp) or datapump(expdp)

Transfer the dump file to the destination database.

Import the STATS table to the destination database.

In detail:

Please note to generate stats on the schema as you see fit prior to export and import just in case.

you may use code like this or can be added to more options.. 

begin

    dbms_stats.gather_schema_stats('MYSCHEMA_DEV');

end;

/

1. Create the STATS table.

begin

    dbms_stats.create_stat_table( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

2. Export the statistics to the STATS table.

begin

    dbms_stats.export_schema_stats( ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

3. Export the STATS table using export(exp) or datapump(expdp)

this can be along with schema export or only stats table can be exported

1. expdp command.

2. dbms_datapump API procedures. 

expdp SCHEMAS=MYSCHEMA_DEV DIRECTORY=dumps parallel=24 DUMPFILE=MYSCHEMA_DEV_%U.dmp LOGFILE=MYSCHEMA_DEV_exp.log COMPRESSION=ALL CLUSTER=N METRICS=Y LOGTIME=ALL  EXCLUDE=STATISTICS

4. Transfer the dump file to the destination database.

use sftp or winscp whatever you think is fit.

5. Import the STATS table to the destination database.

begin

    dbms_stats.import_schema_stats(ownname => 'MYSCHEMA_DEV', stattab => 'exp_stats');

end;

/

Hope this helps... please feel free to comment .. thanks. 

How to truncate reference partitioning table in oracle database

How to truncate reference partitioning table in oracle database

there is no option to truncate reference partitioning tables in oracle database unless FK tables delete rule is set CASCADE. it is tested in 12.2

how do we get rid of PK table while keeping FK table exists , It depends on the way the tables are got created.

Possible ways:

1. use delete
2. drop FK tables, truncate PK tables since FK delete rule is 'NO ACTION'
3. truncate parent table would be successful only when FK tables delete rule is set to CASCADE.
4. TRUNCATE table partition (required partitions ) with cascade option.

Here it is the demo when FK tables delete rule is NO ACTION

SQL> create table p_emp(
   empno      number  primary key,
   job        varchar2(20),
   sal        number(7,2),
   deptno     number(2)
   )
   partition by list(job)
   ( partition p_job_dba values ('BA'),
     partition p_job_mgr values ('GR'),
     partition p_job_vp  values ('VP')
   );

SQL>  
Table created.

 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno foreign key(empno)
    references p_emp(empno)
 )
 partition by reference (fk_empno)
 /


SQL>  

Table created.

SQL> truncate table p_emp cascade;
truncate table p_emp cascade
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> delete from p_emp;

0 rows deleted.

SQL> truncate table r_emp;

Table truncated.

SQL>  truncate table p_emp cascade;
 truncate table p_emp cascade
                *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL>


Here it is the demo when FK tables delete rule is CASCADE





 SQL> create table p_emp(
    empno      number  primary key,
    job        varchar2(20),
    sal        number(7,2),
    deptno     number(2)
    )
    partition by list(job)
    ( partition p_job_dba values ('BA'),
      partition p_job_mgr values ('GR'),
      partition p_job_vp  values ('VP')
    );

Table created.



SQL>
 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno1 foreign key(empno)
    references p_emp(empno) on delete cascade
 )
 partition by reference (fk_empno1)
 /

SQL>   
Table created.

SQL> SQL>
SQL>  truncate table p_emp cascade;

Table truncated.

SQL>


 

How to check FK delete rule:

the below query help us to know the delete rule of FK tables FK constraints.

when delete rule is not set :

select table_name, delete_rule
from user_constraints
where table_name like 'TABLE_NAME_YOURS%'
and constraint_type = 'R';

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR        NO ACTION
TABLE_NAME_YOURS_LOG         NO ACTION
TABLE_NAME_YOURS_LOG_P       NO ACTION


when delete rule is set

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR          CASCADE
TABLE_NAME_YOURS_LOG           CASCADE
TABLE_NAME_YOURS_LOG_P         CASCADE

4.
SQL> ALTER TABLE xxxx TRUNCATE PARTITION P2019xxx      UPDATE INDEXES;

Table truncated.

SQL>

Errors in 19c rac cluster *root.sh script execution during GRID installation.

Errors in 19c rac cluster *root.sh script execution during GRID installation.

1. run(before gird install)  runcluvfy.sh stage -pre crsinst -n  node1, node2 and ensure output is endup with all r in PASSED state.

Error seen in log file:

  2020-02-13 20:12:33: Invoking "/u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -chkckpt -name ROOTCRS_FIRSTNODE -status"

2020-02-13 20:12:33: trace file=/u10/app/oracle/crsdata/nodemachine_1/crsconfig/cluutil9.log

2020-02-13 20:12:33: Running as user oracle: /u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -chkckpt -name ROOTCRS_FIRSTNODE -status

2020-02-13 20:12:33: Removing file /tmp/C3gYWRsAf7

2020-02-13 20:12:33: Successfully removed file: /tmp/C3gYWRsAf7

2020-02-13 20:12:33: pipe exit code: 0

2020-02-13 20:12:33: /bin/su successfully executed

2020-02-13 20:12:33: FAIL

2020-02-13 20:12:33: The 'ROOTCRS_FIRSTNODE' status is FAILED

2020-02-13 20:12:33: Global ckpt 'ROOTCRS_FIRSTNODE' state: FAIL

2020-02-13 20:12:33: First node operations have not been done, and local node is installer node.

2020-02-13 20:12:33: Local node: nodemachine_1 is the first node.

2020-02-13 20:12:33: ORACLE_BASE is shared: 0

2020-02-13 20:12:33: Invoking "/u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile"

2020-02-13 20:12:33: trace file=/u10/app/oracle/crsdata/nodemachine_1/crsconfig/cluutil10.log

2020-02-13 20:12:33: Running as user oracle: /u10/app/19.3.0/grid/bin/cluutil -ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile

2020-02-13 20:12:36: Removing file /tmp/PIG92egVCe

2020-02-13 20:12:36: Successfully removed file: /tmp/PIG92egVCe

2020-02-13 20:12:36: pipe exit code: 0

2020-02-13 20:12:36: /bin/su successfully executed

2020-02-13 20:12:36:

2020-02-13 20:12:36:  "-ckpt -global -oraclebase /u10/app/oracle -writeckpt -name ROOTCRS_FIRSTNODE -state FAIL -nodelist nodemachine_1,nodemachine_2 -transferfile" succeeded with status 0.

2020-02-13 20:12:36: succeeded to write global ckpt 'ROOTCRS_FIRSTNODE' with status 'FAIL'

[root@nodemachine_1 crsconfig]#

running command what root.sh attempting to do manually also gave same error..

Solution:

Issue with  IPMI Management Network configuration.. 

1. aborted the grid install  .

2. deinstalled the grid (whatever portion installed)

3. restarted with grid install without IPMI option.

and found grid install done correctly.. 

There will be another post on IPMI configuration.

what is IPMI?

For now :

IPMI provides a set of common interfaces to computer hardware and firmware that system administrators can use to monitor system health and manage the system. more details in coming blogs... 

oracle online document is always best 
https://docs.oracle.com/en/database/oracle/oracle-database/19/cwsol/enabling-intelligent-platform-management-interface-ipmi.html#GUID-96249852-936C-480C-A166-6926A654F014