View Sidebar

Welcome Oracle DBA!

This site is intended for helping Oracle DBA’s to Administer Databases and make them running faster. We will try our best to publish useful scripts ans methods. The views expressed on this blog are ours and do not reflect the views of Oracle Corporation. The views and opinions expressed by visitors on this blog are theirs, not ours. These programs/scripts are for informational purposes only and we disclaim liability for any damages caused by such use of the Programs.  
Vincent FENOLL Cyrille MODIANO Yann COUSIN
View Vincent Fenoll's profile on LinkedIn View Cyrille Modiano's profile on LinkedIn View Yann Cousin's profile on LinkedIn
 
ADRCI, How to create a package?

ADRCI, How to create a package?

The goal is to upload a generated zip file on 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.

2014/09/18Read More
ZFS snapshots (Overview)

ZFS snapshots (Overview)

A snapshot is a read-only copy of a file system or volume. Snapshots can be created almost instantly, and they initially consume no additional disk space within the pool.

How can I create a new ZFS snapshot?

$sudo zfs snapshot rpool/DBORA/u01@u01snapshop

How can I list all ZFS files system and snapshots (in Bold)?

$ zfs list -r -t all

Or

$ zfs list -o space -r rpool

How can I know the creation date of the ZFS snapshots?

$ zfs list -r -t snapshot -o name,creation

How can I delete a ZFS snapshop?

$ sudo zfs destroy rpool/DBORA/u01@now

How can I use my ZFS snapshop to restore a single file?

I want to restore the file /u01/test.sh

$cd /u01
$cd .zfs/snapshot/u01snapshop/
$cp test.sh /u01/

or to restore a copie without replace current one :

$cp test.sh /u01/test2.sh

You can use a snapshot like any other (read-only) FS to move files or folder to another place.

How can I use my ZFS snapshop to restore a File system?

This command will discard all changes made to the file system since the snapshot was created:

$sudo zfs rollback rpool/DBORA/u01@u01snapshop
cannot rollback to ‘rpool/DBORA/u01@u01snapshop': more recent snapshots exist
use ‘-r’ to force deletion of the following snapshots:
rpool/DBORA/u01@u01snapshop2
rpool/DBORA/u01@u01snapshop3

$sudo zfs rollback -r rpool/DBORA/u01@u01snapshop


More informations in the Oracle documentation.


Vincent Fenoll DBA Oracle Montreal

2014/09/12Read More
Latch contention

Latch contention

Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements.

Cache buffers chain latches contention is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).

Redo copy/redo allocation latches contention are rarely encountered today. They protect the redolog buffer

A useful and very complete article/scripts on latch contention by Tanel Poder (Oracle 9i to 12c):

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

The scripts:



Vincent Fenoll DBA Oracle Montreal

2014/05/13Read More
Database Health Check (DBMS_HM)

Database Health Check (DBMS_HM)

The Oracle DBA wants to run Health Checks with Health Monitor upon his database.

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;
2014/01/09Read More
Kill Session Oracle

Kill Session Oracle

The Oracle DBA wants to kill a session that is consuming abnormally too many resources.

alter system kill session SID, SERIAL# [MyRACInstance]' [immediate];

SID, SERIAL#are provided by V$SESSION
The third and optional parameter is for RAC environment


You can also kill the process directly with an operating system command:
$ kill -9 myPID
2014/01/08Read More

Switch to our mobile site