How to flashback a procedure, function or package?

If you accidentally dropped or modified a procedure, function or package. You can rollback to another version in a time.
If Flashback is disabled on your database, the rollback time has to be short in time because you will use your undo tablespace

Find the object:
select object_id from dba_objects
where object_name=’MY_OBJECT_NAME’ and owner=’OWNER_OF_THE_OBJECT’;
==> 1010952

Flashback table:
select SOURCE from sys.source$ as of timestamp
to_timestamp(’31-Mar-2017 10:00:52′,’DD-Mon-YYYY hh24:MI:SS’)
where obj#=1010952 ;

List of active transaction per users

If I have a lock problem (for example a select for update not committed), I can start with the list of active transactions per users:

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from gv$transaction t
inner join gv$session s on t.addr = s.taddr;

Trace a specific ORA- error

How to trace a specific ORA- error that is raised and can be reproduced?
We want to find the complete Oracle stack to understand the origin of this error.

For example, I want to understand why datapump expdp/impdp raises:
ORA-01422: exact fetch returns more than requested number of rows

This time, the alert file is not very helpful.

To determine the root cause, event 1422 can be set as follows:

connect /as sysdba 
alter system set events '1422 trace name ERRORSTACK level 3'; 

[…reproduce the issue…]

The event can be turned off again using:

connect /as sysdba

alter system set events '1422 trace name ERRORSTACK off';

In the event 1422 trace file, below sql query was causing error.

*** ACTION NAME:(Select sys_context into variable) 2017-01-27 14:15:24.257
*** MODULE NAME:(Trigger DDL_AUDIT) 2017-01-27 14:15:24.257
*** SERVICE NAME:(SYS$USERS) 2017-01-27 14:15:24.257
*** SESSION ID:(3265.22843) 2017-01-27 14:15:24.257
*** 2017-01-27 14:15:24.257
ksedmp: internal or fatal error
ORA-01422: exact fetch returns more than requested number of rows
Current SQL statement for this session:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’)

Of course, it’s easier to locate the target module if you have already instrumented your code with DBMS_APPLICATION_INFO.SET_MODULE and DBMS_APPLICATION_INFO.SET_ACTION;

In this case, the problem was due to SYS_CONTEXT(‘USERENV’,’SESSIONID’) returning 2 rows causing the ORA-01422 and subsequent datapump failure.

The workaround of this specific problem is to disable the Trigger DDL_AUDIT.
A solution is to modify the cursor or the statement in the trigger to retreive just one row, for example:
SELECT UPPER(OSUSER), PROGRAM, MODULE, CLIENT_INFO FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT(‘userenv’, ‘SESSIONID’) and rownum=1;
Another solution is to trap error with an excveption clause.

 
Author: Vincent Fenoll, Oracle DBA Montreal

ADRCI, How to create a package?

The goal is to upload a generated zip file from Montreal to the My Oracle Support website. This package will includes all the trace files, instance alert file and other diagnostic information for the critical error .

It is called a IPS package (Incident Packaging Services).

$adrci

adrci> show home

if you have multiple ORACLE_HOME:
adrci> set homepath database_home

adrci> show problem

PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
——– ———- ————- ————
2 ORA 4030 146025 2014-09-17 22:16:08.625000 -04:00
1 ORA 445 144125 2014-09-18 09:24:12.998000 -04:00

adrci> show incident -p “problem_key=’ORA 4030′”

ADR Home = /u01a/diag/rdbms/nhsmppr/NHsmPPr:
*************************************************************************
INCIDENT_ID PROBLEM_KEY CREATE_TIME
—————- ————– —————————————-
145585 ORA 4030 2014-09-17 22:16:05.493000 -04:00
144217 ORA 4030 2014-09-17 22:16:05.523000 -04:00
145441 ORA 4030 2014-09-17 22:16:05.639000 -04:00
144577 ORA 4030 2014-09-17 22:16:05.782000 -04:00
145985 ORA 4030 2014-09-17 22:16:05.919000 -04:00
5 rows fetched

adrci> ips pack incident 145585 in /tmp
Generated package 1 in file /tmp/ORA4030_20140918102208_COM_1.zip, mode complete

If at this step the ADRCI utility complains that the incident was flood-controlled and that no package can be generated for it, then instead of choosing the most recent incident to be packaged, choose the first incident that occurred after an instance startup.

Vincent Fenoll – Oracle DBA in Montreal

Database Health Check (DBMS_HM)

The Oracle DBA wants to run Health Checks with Health Monitor upon his database in Montreal (or elsewhere ;) ).

With Oracle 11g, these checks can be done:
– DB Structure Integrity Check
– CF Block Integrity Check
– Data Block Integrity Check
– Redo Integrity Check
– Transaction Integrity Check
– Undo Segment Integrity Check
– Dictionary Integrity Check
– ASM Allocation Check

Viewing the list of checks that can be done on your database:

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.

Displaying parameter information for all health checks:

  SELECT c.name check_name,
         p.name parameter_name,
         p.TYPE,
         p.DEFAULT_VALUE,
         p.description
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = c.id AND c.internal_check = 'N'
  ORDER BY c.name;

Running a check:

BEGIN
   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/

or

BEGIN
   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');
END;

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
       name,
       check_name,
       run_mode,
       status,
       src_incident,
       num_incident,
       error_number
  FROM v$hm_run;

Vincent Fenoll – Oracle OCP Database administrator in Montreal