Category Archives: Troubleshooting

How to Enable or Disable the FRA?

Using a flash recovery area simplifies the ongoing administration of your database by automatically naming recovery-related files, retaining them as long as they are needed for restore and recovery activities, and deleting them when they are no longer needed to restore your database and space is needed for some other backup and recovery-related purpose.

How to enable the Flash Recovery Area?

To enable FRA for the database:

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET db_recovery_file_dest=’my path’ (or ‘+FRA’ if ASM is used and FRA is your diskgroup)
ALTER SYSTEM SET db_recovery_file_dest_size=500G;

You can also set db_flashback_retention_target, default is 1440min or 1 day

To verify if FlashBack is enabled:
SELECT flashback_on, log_mode FROM v$database;

 

How to disable the Flash Recovery Area?

ALTER DATABASE FLASHBACK OFF;

To verify if FlashBack is disabled:
SELECT flashback_on, log_mode FROM v$database;

 

What files can be found in the Flash (Fast) Recovery Area:

Transient Files:
RMAN backups
archived redo logs
flashback logs

Permanent Files:
online redo log copies
control file
Guaranteed Restore Points

 

Flashback must be ON to put your database in ARCHIVELOG mode.

Author: Vincent Fenoll

How to see locks and blockers on table

I want to find oracle locked objects. How can I see lock on table ?

Sometimes, “select * from dba_blockers” does’nt return anything but somebody is blocking one or several row.

This SQL statement returns

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

OR

BEGIN
dbms_output.enable(1000000);
for do_loop in (select inst_id, session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM gv$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line(‘.’);
dbms_output.put_line(‘Blocking Session : ‘||do_loop.inst_id||’-‘||do_loop.session_id||’-‘||do_loop.oracle_username);
dbms_output.put_line(‘Object (Owner/Name): ‘||do_loop.owner||’.’||do_loop.object_name);
dbms_output.put_line(‘Object Type : ‘||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line(‘Sessions being blocked : ‘||next_loop.sid);
end loop;
end loop;
END;

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

Logminer utility

Sometimes we need to see redo information about executed sql in the database.In order to do this, Oracle offers the Logminer utility.
This native tool analyzes the content of redo and archived redologs.

Note: If it’s configured and it’s not too late, it’s easier to use Flashback query.

Here is a simple example to use Oracle Logminer
— Prerequisite
EXECUTE_CATALOG_ROLE and select on V$LOGMNR_CONTENTS

— Specify a LogMiner dictionary.
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

— Specify a list of redo log files for analysis.
— to find which log file: search first_time field (the hour should be the last before the hour we need the logminer)
select * from v$archived_log where trunc(first_time) = to_date(sysdate)
and name like ‘%specific-file-system%’

— Add required archives (here 2)
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_2_seq_366089.4743.958543215′, OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>’+archives/my-db/archivelog/2017_10_28/thread_1_seq_367742.3394.958545027′, OPTIONS => DBMS_LOGMNR.NEW);
[…]

— Start LogMiner.
— to retreive only commited transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 06:10:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

— to retreive both committed and uncommitted transactions
EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’),ENDTIME => TO_DATE(’28-10-2017 05:56:00′,’DD-MM-YYYY HH24:MI:SS’), OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

— Mine…
select count(*) from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

create table my-schema.logmnr-result as
select * from v$logmnr_contents a where a.TABLE_NAME = ‘MY_TABLE’;

— end of the session
EXECUTE DBMS_LOGMNR.END_LOGMNR();

— Analyze┬ámy-schema.logmnr-result Table