My misunderstandings regarding when indexes would not be used

It is normal for bloggers including myself to post about the great things they have done  – however in this case I am blogging about why I was surprised that adding a specific index had the substantial benefits it did have.

The table contains around 32M rows, is not partitioned and is on Linux 11.2.0.3


Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SELL_UNIT_ID                              NOT NULL VARCHAR2(20)
PRD_CD                                    NOT NULL VARCHAR2(75)
PRD_TYP_CD                                NOT  (more...)

Preventing standby databases opening in Active DataGuard mode + chopt !!!

It is long been a concern of mine that it is relatively easy to open a standby database  inadvertently and then be potentially liable to a licensing cost as usage is shown in the DBA_FEATURE_USAGE_STATISTICS view.

In fact we logged an SR on the subject based on experiences on failing over using DataGuard Broker

On dgmgrl switchover the Oracle restart database ‘start option’ property is not modified to ‘mount’ database even though dgmgrl modifies the (more...)

Valuable BAU OEM reports – some examples

A well configured OEM 12c (or any previous version of your choice) is a fantastic enterprise tool and the reporting capabilities are a major asset.

I am going to share three examples of reports we use and find valuable and if anybody has any  other good ideas I am happy  to hear about them.

I don’t think I wrote any of them but I almost certainly identified the requirement and instigated the creation of each (more...)

The value of audit_trail=DB,EXTENDED

I was recently reading a blog entry by Dominic Brooks regarding auditing and I was intrigued by the line referring to the audit_trail parameter being set to DB, EXTENDED

Behaves the same as AUDIT_TRAIL=DB, but also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available.

Nothing wrong with that and straight from the manual but I was surprised that they were CLOBS. However on looking at them they (more...)

Tidying up SYSAUX – removing old snapshots which you didn’t know existed

This post runs in parallel with a post I made 5 years ago and which is still the most read one on  this blog

http://jhdba.wordpress.com/2009/05/19/purging-statistics-from-the-sysaux-tablespace/

It will show how to reduce space taken up in the  SYSAUX tablespace by because you have many more AWR snapshots being retained than you think.

Firstly lets take an example database and we can see that we are using 92Gb  of space

 

set linesize 120
set  (more...)

Bugs with rm -rf from within asmcmd

Performing an rm -rf operation is normally a simple operation albeit risky if you are in the wrong folder. However within ASMCMD there are a couple of bugs associated with it.

Here are two examples and workrounds

FRA full - warning in the alert log  - action needs to be taken - this is an 11.2.0.4 database on OEL (5.8)
ORA-19815: WARNING: db_recovery_file_dest_size of 10484711424 bytes is 100.00% used, and  (more...)

Converting an SCN to a timestamp

Something nice and simple

It is easy to get the current SCN from the database and to map between SCN and timestamp because from 10g onwards there are 2 functions to convert between SCN and TIMESTAMP

In 9i there was also a means of mapping an SCN to a TIMESTAMP. This involves the system view SYS.SMON_SCN_TIME. The view no longer exists in 12c. One limitation is that it only holds 1440 rows (well it (more...)

Another really enjoyable UKOUG Database SIG

Today’s venue was the Metropole Hotel in the centre of Leeds and there was a good attendance, encouraged by a strong agenda.

After introductions, health and safety and UKOUG information it was straight into techie talk with Phil Davies from Oracle doing his normal support update (although he does share duties with Owen Ireland). Invariably I make more notes from this session than most others I hear because of the wealth of information it contains. (more...)

In praise of bloggers (along with sql_profiles and RAC interconnect pings)

Today I am going to mention two articles which I came across in the last few days whilst investigating problems and talk about the background to the problems but also praise the articles. I am sure many of us have run the SQL Tuning advisor from within OEM or using the DBMS_SQLTUNE package from the command line and often it recommends a sql_profile that should be applied, (invariably giving 99.98 perceived benefits). Now I (more...)

Don’t forget the emctl reload agent command

Just a quick entry to show the use of a command that I had forgotten existed but it seemed to work nicely

Problem  – OEM12C agent had been down for a few days on a non-production server and more than the maximum number of files had been created ready to be uploaded.

emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent  (more...)