Oracle DBA – A lifelong learning experience

Using grant connect through to manage database links

Posted by John Hallas on December 4, 2014

Nobody can say that I am not current and topical with my posts. This post refers to functionality that was introduced in 9i, however I have just come across it and thought it useful enough to blog about it.

The command ALTER USER USERB GRANT CONNECT THROUGH USERA allows a proxy connection to be made using the username and password of USERA but  to connect in as USERB. The purpose is so that management of a user can be done without knowing that users password or changing it. This is most commonly going to be used by support teams.

I will give an example

SQL> create user test1 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test1;

Grant succeeded.

SQL> create user test2 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test2;

Grant succeeded.

SQL> alter user test2 grant connect through test1;

User altered.

SQL> connect test1[test2]/password
SQL> show user

Here is a way that I can create a database link as user2 without knowing that users password. User2 has to have create database link privilege.

SQL> connect / as sysdba
SQL> grant create database link to test2;

Grant succeeded.

SQL> connect test1[test2]/password
SQL> show user
SQL> create database link TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

By the way as a privileged user you can apparerently create a link in another schema

SQL> connect / as sysdba
SQL> create database link test2.TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

Database link created.

But the link is still owned by SYS but has the name TEST2.TSTAUDIT

SQL> select owner, DB_LINK username, host from dba_db_links;

OWNER                          USERNAME    HOST
SYS                            TEST2.TSTAUDIT TSTSAUDIT

drop database link "TEST2.TSTAUDIT";



Posted in Oracle | Tagged: , , | 1 Comment »

Controlling RMAN channels on RAC

Posted by John Hallas on December 3, 2014

This was sent to me for posting  by my friend and ex-colleague Vitaly Kaminsky ….

I have recently worked with the customer where standard RMAN backups of production 2-node cluster ( were getting too big and taking longer than 24 hours to run.

The problem with this particular cluster was the fact that ALLOCATION of RMAN connections to the instances of the cluster was controlled by SCAN and driven by the load-balancing algorithm of SCAN.

The result was rather undesirable allocation of all channels to a single instance and the only way to control it was to reduce the workload on all nodes at the time when backup is started which was unacceptable on this 24/7 constant workload system.

This single-node allocation caused the network connections (10GB network) to become saturated, throttling the backup rates and degrading the “user experience” for user sessions on the same node.

After considerable search, I found a way of controlling this allocation I did not know about before (just showing my lack of attention when reading low-level manuals).

The way RMAN is using parameters is as follows:

  1. Use the instructions from the script
  2. If there are no script instructions, use the defaults.

How to force RMAN to “distribute” the channels across instances:

  1. Set required degree of parallelism in default parameters:



RMAN> show all

  1. “Hard-code” the channel allocation in the defaults:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT='sys/password@node1:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';

CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT='sys/password@node2:1521/clusterservice' FORMAT='/backup/FULL/%d_%U';
  1. DO NOT allocate channels in the script:
RMAN> run {

2> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

3> }
  1. Check how channels are allocated:
RMAN> backup as COMPRESSED BACKUPSET tag ‘FULL_112014100000' database;

Starting backup at 24-NOV-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=871 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=970 instance=racservice1 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1453 instance=racservice2 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=399 instance=racservice2 device type=DISK

channel ORA_DISK_1: starting compressed full datafile backup set


There are 2 issues I can think of with this approach:

I am not keen on passing the passwords to any services, but RMAN is smart enough to cater for this:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name RACSERVICE are:





  1. Correcting mistakes.

While typing, I made some mistakes and ended up with the wrong CONFIGURE CHANNEL lines.

How do you delete those lines?  Simples:


The result of the above?

We now have even distribution of RMAN workload and much higher utilisation of available network bandwidth on both nodes!


Posted in Oracle | Tagged: , , , | Leave a Comment »

Loading timestamp with timezone into a table using SQL Developer

Posted by John Hallas on November 22, 2014

I will start this post off stating  that I know there are better and more efficient methods of doing this but this is the best method I could come up with.

I had a csv file with a number of columns, one of which was data in a timestamp with timezone format.

A few rows looked like this – note I have stripped the other columns out for ease of display.

DeviceId    Timestamp
7941    11/9/2014 12:46:49 AM +00:00
8533    11/9/2014 12:46:49 PM +00:00
3692    11/9/2014 1:10:36 PM +00:00

So data is in 12 hour format and the line length varies between 28 and 29 depending on whether the hour has 1 or 2 digits.

I decided to import the data using SQL Developer and that turned out to be surprisingly easy

Log on – open up the Tables filter and right click and select import data

Then go through the wizard until you get to the screen to format the column you are interested in. Believe me I tried every method known to man with no success.I tried date, timestamp, timestamp with timezone and every permutation of format I could try in the wizard but I could not get the data into the table in anything other than varchar2 format.



I do not doubt that there is a method but I could not find it. So I ended up taking a roundabout route which did work.

I brought  the column in as a varchar2 column, added a new column, converted/copied the data into the new column using a date mask and then dropped the original column


alter table wireless add col2 timestamp with time zone;
update wireless set col2=to_timestamp_tz(timestamp,'mm/dd/yyyy hh12:mi:ss AM TZR');
alter table wireless drop column timestamp;
alter table wireless rename column col2 to timestamp; 

Hey presto job done.

Not the most elegant solution but I used SQL Developer to import data in from a csv file for the first time and  achieved the end result I wanted.


Posted in Oracle | Tagged: , , | Leave a Comment »

Progress update on my AWR repository

Posted by John Hallas on November 20, 2014

I received an email from a team member yesterday

Have you seen this?

Interesting idea – ever thought of implementing it?

Well of course I have implemented a AWR data repository and I thought I would catch-up on where I am with it and how it is being used.

The database started out on HPUX on about 2 years ago and is now on Linux on The repository now holds a total of 139 DBIDs and we have data going back 400 days for the majority of them. The storage is over 2Tb, of which the SYSAUX tablespace contains about 95% – we are compressing some data but that is an area I need to look into further

Why 400 days you might ask – well I work in a Retail organisation and as Easter is a movable feast (pun intended) and that period allows to provide data covering Easter whenever it might fall. It is possible that we would not have data for Easter if we only kept a 365 day period and Easter is a key trading period, second only to Xmas.

I suppose the obvious question is how we have used the data and was it all worth the effort.

The repository has been used for workload forecasting, security reviews, auditing, performance reviews and validation of our existing systems.

Workload forecasting is an interesting and unexpected benefit of the repository. When I originally proposed the idea it wasn’t something that had occurred to me but it has proved very useful. The process is to review how much work a system has done a year ago, look at current usage, calculate the difference and then use that to project forward. For the system that I am discussing which looks at how long it takes us to process sales data from stores we have used a combination of metrics including db time, disk read times, both sequential and scattered to produce a magic number which is what we are working against. That forecast is now being mapped against actuals and the forecast is proving quite accurate. That has allowed us to prepare the database server to support increased volumes by changing disk, adding CPU and memory to maintain and improve against last year’s performance with increased data capture and store numbers. Read the rest of this entry »

Posted in 11g new features, 12c new features, Oracle, security | Tagged: , | 5 Comments »

How does statistic gathering fit in with ITIL Change Management processes

Posted by John Hallas on September 16, 2014

In the (good) old days there was only one optimizer and plans were developed based on the precedence of the rules. For some reason I have always thought there were 11 rules but looking at the manual it tells me there are 15 rules

Nowadays the vast majority of databases will be using the CBO and gathering statistics either by using the overnight maintenance functions or manually gathering as required. I suppose there is always also a third option which is to load pre-saved statistics in on a newly created partition and then lock them.

The other day I identified some code that would perform better if we fixed a profile. Co-incidentally the profile would fix some statistics as the table was constantly having stale stats. I debated with myself whether that needed a change control and in the end decided it did not. That got me thinking about how we constantly allow changes to happen that can seriously change performance, for the better or worse with no change control at all. That of course happens when we gather statistics. I doubt many sites raise an ITIL change record when they manually gather stats and I would have thought nobody would do so for the scheduled maintenance activities and yet when, as DBAs, the first thing we ask when a job suddenly goes horribly wrong is – have the statistics changed?

If the issue gets escalated to a problem record then there is no change in the ITIL recording system that any change has been applied. I am not actually suggesting that we do raise change to gather stats as it would be well-nigh impossible to manage on some systems and we would also need to lock statistics to have a point to return to, which again is possible but a massive overhead.

So there is no point to my post really, just a general observation and a chance to air something that has crossed my mind a few times. I am happy to hear views on whether creating a profile should be flagged as a change – I suggest most will say yes as that is a definite action you are taking to make a change to the current operational process whereas gathering statistics is more like an engine fine tuning the ratio of air to petrol as it is running.

Posted in Oracle | Tagged: , , | 2 Comments »

My misunderstandings regarding when indexes would not be used

Posted by John Hallas on August 7, 2014

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

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SELL_UNIT_ID                              NOT NULL VARCHAR2(20)
PRD_CD                                    NOT NULL VARCHAR2(75)
PRD_TYP_CD                                NOT NULL CHAR(3)
ITEM_LOCATION_STATUS                               CHAR(1)
LAUNCH_DATE                                        DATE
OFF_SALE_DT                                        DATE
LST_MTC_TS                                NOT NULL DATE
LST_MTC_USR_ID                            NOT NULL NUMBER
BATCHNUMBER                               NOT NULL NUMBER
ACTION                                    NOT NULL CHAR(1)
CRT_TS                                    NOT NULL DATE
CRT_USR_ID                                NOT NULL NUMBER
PROC_TS                                            DATE
S_UOM                                              CHAR(255)
FORCE_ENTER_PRICE                                  CHAR(255)
M_F_PROD_NUMBER                                    CHAR(255)

The two interesting columns are ACTION which contains one of 4 values but currently only has 3 values in and until recently did not have any ‘D’ values in – which may be quite pertinent
Statistics are up to date and gathered nightly across tables and indexes

select /*+ parallel (8) */ action, count(*) from TABLENAME  group by action;
A   COUNT(*)
- ----------
U    2019249
I   27663840
D    2030224

There were 2 indexes on the table

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
EXTSTRPRDRNG_X1                SELL_UNIT_ID                                 1
                               PRD_CD                                       2
                               PRD_TYP_CD                                   3
EXTSTRPRDRNG_X2                LST_MTC_TS                                   1
                               LAUNCH_DATE                                  2


So I will  expose myself to the whole world now and say that I thought :- 1) the trim function would negate any use of an index on the column M_F_PROD_NUMBER 2) the predicate ACTION != ‘D’ would stop any index being used on the ACTION column – and that would be not used anyway as the non ‘D’s were 100% of the table, at that time   The plan was as expected

| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                 |   416 |   110K|   992K  (1)| 03:18:31 |
|   1 |  HASH UNIQUE       |                 |   416 |   110K|   992K  (1)| 03:18:31 |
|*  2 |   TABLE ACCESS FULL| TABLE_NAME      |   417 |   110K|   992K  (1)| 03:18:31 |


So I figured that an index on a combination of SELL_UNIT_ID and a function based index on TRIM(M_F_PROD_NUMBER) would be useful – maybe add ACTION in just to see if that made a difference

The existing index (also the PK) on  SELL_UNIT_ID and PRC_CD would  be also used in combination with my new index.  I did work out how to create a function on the ACTION != ‘D’ predicate and then create a FBI based on that but that is a separate matter , to follow in my next post


To my surprise the following index made a significant  difference to performance and I am struggling to really understand why. The original two indexes were still in place

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------

EXTSTRPRDRNG_X3                SELL_UNIT_ID                                 1
                               M_F_PROD_NUMBER                              2



The index I really wanted to create was this one, but I never got a chance to use it as the one we created did the job and saved a lot of agony and the benefits of trying a different one will be minimal and I had to take the pragmatic approach of not pushing a test


The difference in performance was quite significant  – just taking a 1 hour time slice shows the difference and later on in the day we are at 6K executions an hour

------------------------ ----- ---------- ---------- ----------
04-AUG-14 06:00          07:00       4812     6.9738      .3663
05-AUG-14 06:00          07:00       2265    11.9742      .3955
06-AUG-14 06:00          07:00       1264      .2523      .0832

So we have a good result, but in a different manner to the way I thougt we would get it.

Putting a 10053 trace on it only select one join order and the base statistics on the table and index shows

Table Stats::
    #Rows: 31719095  #Blks:  5976448  AvgRowLen:  814.00  ChainCnt:  0.00
Index Stats::
  Index: EXTSTRPRDRNG_X1  Col#: 1 2 3
    LVLS: 2  #LB: 114050  #DK: 29355425  LB/K: 1.00  DB/K: 1.00  CLUF: 29124675.00
  Index: EXTSTRPRDRNG_X2  Col#: 7 5
    LVLS: 3  #LB: 121445  #DK: 160563  LB/K: 1.00  DB/K: 185.00  CLUF: 29806195.00
  Index: EXTSTRPRDRNG_X3  Col#: 1 16
    LVLS: 3  #LB: 1174735  #DK: 30493285  LB/K: 1.00  DB/K: 1.00  CLUF: 30586340.00
  Column (#2):
    NewDensity:0.000008, OldDensity:0.000013 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:132798
  Column (#2): PRD_CD(
    AvgLen: 10 NDV: 132798 Nulls: 0 Density: 0.000008
Final cost for query block SEL$1 (#1) - All Rows Plan:
  Best join order: 1
  Cost: 1936.3367  Degree: 1  Card: 387.0000  Bytes: 104877
  Resc: 1936.3367  Resc_io: 1934.0000  Resc_cpu: 60545244
  Resp: 1936.3367  Resp_io: 1934.0000  Resc_cpu: 60545244

Plan Table
| Id  | Operation                     | Name           | Rows  | Bytes | Cost  | Time      |
| 0   | SELECT STATEMENT              |                |       |       |  1936 |           |
| 1   |  HASH UNIQUE                  |                |   386 |  102K |  1936 |  00:00:24 |
| 2   |   TABLE ACCESS BY INDEX ROWID | EXT_STR_PRD_RNG|   387 |  102K |  1935 |  00:00:24 |
| 3   |    INDEX RANGE SCAN           | EXTSTRPRDRNG_X3|   414 |       |  1536 |  00:00:19 |


What I want to do now is see if the line ACTION != can be changed to ACTION in (‘A’,’B’,’C’)  and understand what is in the M_F_PROD_NUMBER column which is character column but only contains numbers and why it needs a trim on it  rather than getting the data into a properly designed column. I also need to do more research on why this index worked well despite my two reasons why it would not.

Richard Foote’s blog on indexing a not equal to predicate is useful and gives me the idea of trying to  amend the query  ACTION <> ‘D’ to ACTION >’D’ and adding an index on ACTION and seeing how that works out. Looks like my invisible index blog entry will come in useful for that

Posted in Oracle | Tagged: , , , | Leave a Comment »

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

Posted by John Hallas on August 6, 2014

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 Oracle restart database role property to PHYSICAL_STANDBY. Nor does a dgmgrl switchover modify the Oracle restart database ‘start option’ property to ‘open’ database even though dgmgrl modifies the Oracle restart database role property to PRIMARY. The potential is there to accidentally open a standby database and invoke Active dataguard on any reboot/restart of services which is what we do not want.

After a fair bit of discussion we got this rather unhelpful response

Starting in 11, broker no longer changes the state of the database, i.e., from mount to open.  It is up to the user what state they want their database in and to setup start_option appropriately.  
In the course of role changes or other operations that involve database restarts, the broker will restart the database based on the role of the database, i.e., open for PRIMARY database and mounted for PHYSICAL STANDBY.  
If the user wants the new standby to only be in the mounted mode for “srvctl start database …”, they must manually change the start_options for the databases appropriately after the role change – they will have to change it to open for the new primary as well.
In other words, what you are seeing is as designed!
We recommend that you leave the start option to OPEN for both databases, so that regardless of who the primary is, you can be sure it will be opened after a role change. This of course means the standby will be opened, which is okay as long as the user has paid for the Active DG option.
If the user has not paid for the Active DG option, you should modify the start option for both databases prior to the role change, so that after the role change the new primary will open and the new standby will be mounted.
The broker does not modify the startup options during a role change in the Clusterware Repository. It only modifies the role of the database.
Set the start options to OPEN for both databases and you won’t run into this issue.

They did provide a script but we ended up using a startup trigger something like this

CTL varchar(10);
execute immediate 'alter system set dg_broker_start=FALSE scope=memory';

However that is not the purpose of this post. It is more about coming across an undocumented parameter from 11Gr2 which enforces that a standby cannot be opened (and activate the need for an ADG license) providing the MRP process is running. I think that came from a  post by Fritz Hoogland on the Oracle-L list. This is exactly what we wanted and a colleague Hamid Ansari prepared a summary and test of the parameter Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , , | Leave a Comment »

Valuable BAU OEM reports – some examples

Posted by John Hallas on July 18, 2014

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 report. I have masked out obvious details so the screen shots do look messy.

For anybody new to OEM reports I blogged a post about creating a new report some time ago

Location of Primary and Standby databases

Like many organisations we have 2 data centres and we use DataGuard extensively. We have a naming convention that has the 8th character of the database name being either A or B which signifies Primary or Standby database. Over time as we do failovers and move things around you cannot always assume that A is the primary database. So I wanted a way of telling quickly what was impacted if we had issues at one site or another.

The report is produced daily and emailed out to the DBA team. In the event of a DR that is our first point of call to see what work is required. The name of each site shows against the server name. This can be achieved because we have a file on a Unix server that contains all the server names and their site location. We create a small table (server_sites)  in the OEM database with the name of the production servers in and the site and then we can join it to the mgmt$target table. We don’t add new production servers that often so it is not too much of an overhead to maintain the table.

a.target_name, a.target_type, a.type_qualifier3, a.type_qualifier4, a.host_name, b.value,
(select *
from mgmt$target
-- type_qualifier2 in('Primary','Physical Standby')
type_qualifier4 in('Primary','Physical Standby')) a,
mgmt$db_init_params b,
where a.target_name = b.target_name
and a.target_name like ('%PRD%')
and = 'dg_broker_start'
and c.server = a.host_name
order by target_name


Guaranteed Restore Points

The availability of guaranteed restore points is a great idea and we use it a lot, especially before data fixes and new code releases. We will create a GRP, apply the code pack, update data and then smoke test. If all fails miserably then we have a quick and easy way to get back, as well as the normal backup and recovery methods. A true belt and braces approach. The downside is that it is easy to forget to remove the GPR and then we have problems with the FRA running out of space.

select fo.target_name "Database",
fo.value "FB On",
to_date(oft.value,'YYYY-MM-DD HH24:MI:SS') "Oldest Flashback Time",
fo.collection_timestamp "Flashback Time Collected",
dip.collection_timestamp "Retention Target Collected",
to_number(dip.value)/60 "Retention (Hours)",
round(to_number(fo.collection_timestamp-to_date(oft.value,'YYYY-MM-DD HH24:MI:SS'))*24) "FB Available (Hours)",
round((to_number(fo.collection_timestamp-to_date(oft.value,'YYYY-MM-DD HH24:MI:SS'))*24)-(to_number(dip.value)/60)) "Difference (Hours)",
case when dip.collection_timestamp 0 then 'Yes' else 'No' end "Target Met"
from (select target_name, target_guid, value, collection_timestamp from SYSMAN.MGMT$METRIC_CURRENT where metric_label = 'Flash Recovery' and column_label = 'Flashback On' fo,
(select target_guid, value from SYSMAN.MGMT$METRIC_CURRENT where metric_label = 'Flash Recovery' and column_label = 'Oldest Flashback Time') oft,
(select target_guid, value, collection_timestamp from MGMT$DB_INIT_PARAMS where name = 'db_flashback_retention_target') dip
where fo.target_guid = oft.target_guid
and fo.target_guid = dip.target_guid
and fo.target_guid in (select distinct member_target_guid from MGMT$TARGET_FLAT_MEMBERS where aggregate_target_name in ('Production_Systems','Non_Production_Systems'))


Databases with no flashback enabled

Reasonably self-evident and all databases should have flashback enabled so it is good to pick up on those that have not got it enabled

t.type_qualifier4  AS Role,
m.column_label     AS Flashback,
m.value            AS Status
mgmt$metric_current m,
mgmt$target t
WHERE m.metric_label = 'Flash Recovery'
AND m.column_label = 'Flashback On'
AND m.value = 'NO'
AND m.target_name like '%PRD%'
AND t.type_qualifier4 in('Primary','Physical Standby')
AND t.target_name=m.target_name
AND t.target_guid=m.target_guid
order by t.type_qualifier4 ,


Posted in Grid control and agents, Oracle | Tagged: , , | 1 Comment »

The value of audit_trail=DB,EXTENDED

Posted by John Hallas on July 15, 2014

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 are NVARCHAR2(2000) so CLOB-type. No issues there.

I have long thought that there were 3 benefits of having the extended value set in audit_trail

1)      Adds the DBID to the syslog file when using OS auditing for SYSDBA and SYSOPER activities. No real issue if you only have a single database on the server but if running several databases it is really mandatory otherwise you cannot tell the entries apart.

My research shows that this is no longer true from 11GR2 onwards and the DBID is always written to the syslog file now

2)      To capture the sql binds and text of any statement run as SYSDBA. This is fundamentally the purpose of using a syslog file, otherwise the DBA can do bad things and delete the entries from the AUD$ table. Having the syslog file set to capture that information and not be editable by other than the root user means that a record of all activity is kept.

Doing some testing, again with the jolt provided by Dom’s blog I found that it did not matter whether the audit_trail was DB or DB,EXTENDED, all the activity and values carried out by SYSDBA were written to that file.

From the documentation

Auditing User SYS and Users Who Connect as SYSDBA and SYSOPER
You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. This enables you to write the actions of administrative users to an operating system file, even if the AUDIT_TRAIL parameter is set to NONE, DB, or DB, EXTENDED. Writing the actions of administrator users to an operating system audit file is safer than writing to the SYS.AUD$ table, because administrative users can remove rows from this table that indicate their bad behavior.


3)      To catch the activities of ‘ordinary’ users and to record the values of their sql statements, provided sufficient auditing has been enabled

A simple example

User AUDIT_TEST owns a table TEST1

 audit update table, delete table, insert table by AUDIT_TEST by access



For i in 1..5loop

Insert into test1 values ('Peter',i);

End loop;




select sql_bind,sql_text from dba_audit_trail where username='AUDIT_TEST'
------------ ---------- ----------------------------------------
INSERT       #1(1):1   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):2   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):3   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):4   INSERT INTO TEST1 VALUES ('Peter',:B1 )
INSERT       #1(1):5   INSERT INTO TEST1 VALUES ('Peter',:B1 )

Setting the audit_trail parameter to DB we do not see the values used which really makes whole exercise of auditing rather pointless.



So in summary, 2 out of my 3 reasons for enabling EXTENDED auditing have been blown out of the water. However the remaining one is justification in itself and I see no reason why every production system should not have it enabled.

At my site ENABLED and writing to a  SYSLOG  file is enabled by default as part of the build on every database we have . We have gone a little too far as the DBAs do not have access to even read the file never mind delete entries but as I sit down with external auditors a couple of times a year I do know they are always impressed that I can demonstrate that even with SYSDBA privileges all my activities are securely audited.

Posted in 11g new features, security | Tagged: , , , , , | 2 Comments »

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

Posted by John Hallas on July 9, 2014

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

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 pagesize 100
col ash form a30
col retention form a30
col snap form a30

COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Move Procedure" FORMAT A40

SELECT  occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR'


Item                     Space Used (GB) Schema                   Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------

SM/AWR                             91.88                                      SYS

Now we are looking to see how long we should be retaining AWR snapshots for and what we actually have

select sysdate - a.sample_time ash,
sysdate - s.begin_interval_time snap,
from sys.wrm$_wr_control c,
select db.dbid,
min(w.sample_time) sample_time
from sys.v_$database db,
sys.Wrh$_active_session_history w
where w.dbid = db.dbid group by db.dbid
) a,
select db.dbid,
min(r.begin_interval_time) begin_interval_time
from sys.v_$database db,
sys.wrm$_snapshot r
where r.dbid = db.dbid
group by db.dbid
) s
where a.dbid = s.dbid
and c.dbid = a.dbid;


ASH                           SNAP                           RETENTION

—————————— —————————— ——————————

+000000875 22:22:41.045       +000000030 12:22:28.323       +00030 00:00:00.0

We want to keep 30 days worth of snapshots – we have set the retention period to that  – but we have 875 days worth of active session history.

I rather smugly said let’s show an example and then I produce  a schema with nearly 3 years worth of snapshots being maintained but there is no trickery involved. Looking around at other systems I can find a few similar examples and it is always down to the same issue. Let’s look further.

select table_name, count(*)
from dba_tab_partitions
where table_name like 'WRH$%'
and table_owner = 'SYS'
group by table_name
order by 1;


TABLE_NAME                       COUNT(*)
------------------------------ ----------
WRH$_DB_CACHE_ADVICE                    2
WRH$_DLM_MISC                           2
WRH$_EVENT_HISTOGRAM                    2
WRH$_FILESTATXS                         2
WRH$_INST_CACHE_TRANSFER                2
WRH$_INTERCONNECT_PINGS                 2
WRH$_LATCH                              2
WRH$_LATCH_CHILDREN                     2
WRH$_LATCH_PARENT                       2
WRH$_OSSTAT                             2
WRH$_PARAMETER                          2
WRH$_ROWCACHE_SUMMARY                   2
WRH$_SEG_STAT                           2
WRH$_SERVICE_STAT                       2
WRH$_SERVICE_WAIT_CLASS                 2
WRH$_SGASTAT                            2
WRH$_SQLSTAT                            2
WRH$_SYSSTAT                            2
WRH$_SYSTEM_EVENT                       2
WRH$_SYS_TIME_MODEL                     2
WRH$_TABLESPACE_STAT                    2
WRH$_WAITSTAT                           2

24 rows selected.

There is the problem, all the WRH$ data is held in 2 partitions and the overnight house-keeping job does not have time to finish it’s tidy-up before it’s 15 minutes of fame is over again for another 24 hours.

Two components of Server Manageability (SM) components that reside in the SYSAUX tablespaces cause the problem. The components involved are the Automatic Workload Repository (AWR) and Optimizer Statistics History (OPTSTAT). Both of these components have retention periods associated with their data, and the MMON process should run nightly, as part of the scheduled maintenance tasks, to purge data that exceeds these retention periods. From version 11G onwards, the mmon purging process has been constrained to a time-limited window for each of the purges, if this window is exceeded the purging stops and an ORA-12751 error is written to an m000 trace file.

For the AWR data, held in tables with names commencing with WRH$, the probable cause is due to fact that a number of the tables are partitioned. New partitions are created for these tables as part of the mmon process. Unfortunately, it seems that the partition splitting process is the final task in the purge process. As the later partitions are not split they end up containing more data. This results in partition pruning within the purge process becoming less effective.

For the OPTSTAT data, held in tables with names commencing with WRI$, the cause is more likely to be related to the volume of data held in the tables. WRI$ tables hold historical statistical data for all segments in the database for as long as specified by the stats history retention period. Thus, if there database contains a large number of tables with a long retention period – say 30 days, then the purge process will have an issue trying to purge all of the old statistics within the specified window.

I also think that two scenarios lead to this problem

1) SYSAUX running out of space, which I know has happened on at least one occasion and the volume of data left in a partition is too much to handle in a constrained time-window and so continues to grow in the same partition.

2) If the database is shut down over the period of the maintenance task and again the volume in the partition becomes too large to handle in a short-time.


The resolution to this problem is split into two stages.

Firstly a temporary manual fix is required to rectify the partition splitting failure. A job needs to scheduled on the database, to be run as the sys user which executes the following code:This job will force the splitting of partitions for the WRH$ tables, and should be scheduled to run on a daily basis until the number of partitions per table exceeds the AWR retention period.

 execute immediate 'alter session set "_swrf_test_action" = 72';

What I do is drop the retention period to 8 days ( or maybe less) and then run this procedure once a day. It can take up to an hour but afterwards you can see the additional partitions have been created.

TABLE_NAME                       COUNT(*)
------------------------------ ----------
WRH$_DB_CACHE_ADVICE                    3
WRH$_DLM_MISC                           3
WRH$_EVENT_HISTOGRAM                    3
WRH$_FILESTATXS                         3
WRH$_INST_CACHE_TRANSFER                3
WRH$_INTERCONNECT_PINGS                 3
WRH$_LATCH                              3
WRH$_LATCH_CHILDREN                     2
WRH$_LATCH_PARENT                       2
WRH$_OSSTAT                             3
WRH$_PARAMETER                          3
WRH$_ROWCACHE_SUMMARY                   3
WRH$_SEG_STAT                           3
WRH$_SERVICE_STAT                       3
WRH$_SERVICE_WAIT_CLASS                 3
WRH$_SGASTAT                            3
WRH$_SQLSTAT                            3
WRH$_SYSSTAT                            3
WRH$_SYSTEM_EVENT                       3
WRH$_SYS_TIME_MODEL                     3
WRH$_TABLESPACE_STAT                    3

Once the correct number of partitions have been created the old data may be automatically purged by the mmon process. If this does not occur, it will be necessary to manually purge the data from the AWR tables.

Firstly turn off the production of snapshots for the duration of this activity:


get the dbid for this database

select dbid from v$database;

<strong>-- get current snap interval (in minutes)</strong>

select extract( day from snap_interval) *24 *60

       + extract( hour from snap_interval) *60

       + extract( minute from snap_interval) snap_interval

       from wrm$_wr_control where dbid = ;

<strong>-</strong><strong>- disable snaps by setting interval to 0</strong>

exec dbms_workload_repository.modify_snapshot_settings(interval=> 0, dbid => <dbid>)

Then determine the maximum snapshot to be purged from the database by querying the maximum snap-id outside of the retention period:

select max(snap_id) max_snap

from wrm$_snapshot

where begin_interval_time

< (sysdate - (select retention

from wrm$_wr_control

where dbid = ));

This will return the maximum snapshot to be purged

exec dbms_workload_repository(1, <max_snap>, <dbid>)

The AWR tables will not purge if baselines exist, so check and remove as required. The following should return only 1 row called SYSTEM_MOVING_WINDOW:

select baseline_name,creation_time

from dba_hist_baseline;

If other baselines are found they will need to be removed:

exec dbms_workload_repository.drop_baseline(baseline_name => <baseline>);

If we look at the current retention period in this code it is set to 15 days, so we set it to 8 days

select d.dbid,w.snap_interval,w.retention from DBA_HIST_WR_CONTROL w, v$database d where w.dbid = d.dbid;

begin</strong> DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(129600,0); <strong>end</strong>;

Then we purge the snapshots ( – in ranges if there a are a lot as in this example). This can take several hours

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>100000, high_snap_id=>114159);

EXEC dbms_workload_repository.drop_snapshot_range(low_snap_id=>116261, high_snap_id=>116265);

Once all that is complete it is just a  matter of tidying up the indexes

ASH    SNAP    RETENTION —————————————————————————

+000000008 23:47:01.421    +000000008 23:46:55.865    +00008 00:00:00.0

Item                     Space Used (GB) Schema                   Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
SM/AWR                             93.70 SYS
select 'alter index '||segment_name||' rebuild online parallel (degree 4);' from dba_segments where tablespace_name= 'SYSAUX' and segment_name like 'WRH$_%' and segment_type='INDEX' order by segment_name;

double check for unusable indexes


select 'alter index '||s.segment_name||' rebuild online parallel (degree 4);'

from dba_segments s,  dba_indexes i where s.tablespace_name= 'SYSAUX'

and s.segment_name like 'WRH$_%'

and s.segment_type='INDEX'

and i.status = 'UNUSABLE'

and i.index_name = s.segment_name

order by s.segment_name


select 'alter table '||segment_name||' move tablespace sysaux ;' from dba_segments where tablespace_name= 'SYSAUX' and segment_name like 'WRH$_%' and segment_type = 'TABLE'   order by segment_name
Item                     Space Used (GB) Schema                  Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
SM/AWR                             10.05 SYS


I hope that has been useful and I would be pleased to hear if others see the same issues on some databases whereby the actual data retained is much lonher that the snapshot retention period would suggest

Posted in Oracle | Tagged: , , , , , , , , | 11 Comments »


Get every new post delivered to your Inbox.

Join 217 other followers