The initial motivation to move towards the new Unified Audit trail is audit performance. The audit records will be written into the read-only table AUDSYS in SYSAUX tablespace. But there are other benefits such as no dependency on init.ora parameters, one location - one format, and close interaction with Oracle Audit Vault and Database Firewall. And of course tiny things such as the immediate write, which avoids losing any audit records during an instance crash.

In addition to user SYS all users having the roles AUDIT_ADMIN and AUDIT_VIEWER can query the AUDSYS table.

After upgrade to Oracle Database 12c Unified Auditing is not enabled by default in order to prevent customers having "old" auditing on already from enabling both auditing facilities at the same time. This is something you need to be aware of: Unified Auditing can be on together with the "old" auditing at the same time.

To link it into the kernel or enable it use the following commands/actions - and the documentation states that you'll have to shut down the listener and restart it again afterwards:

UNIX

cd $ORACLE_HOME/rdbms/libmake -f ins_rdbms.mk uniaud_on ioracle

Windows

Rename the file %ORACLE_HOME%/bin/orauniaud12.dll.option to %ORACLE_HOME%/bin/orauniaud12.dll

The tricky part is now that - even though Unified Auditing is not enabled by default - Unified Auditing is enabled in a Mixed Mode, i.e. there are two auditing policies enabled - but the option is not linked into the kernel.

To disable these policies you'll execute:

SQL> noaudit policy ORA_SECURECONFIG;

SQL> noaudit policy ORA_LOGON_FAILURES;

Don't get me wrong: This is not a recommendation to disable Unified Auditing. I just would like to explain what's on and the possibilities to turn things into the desired direction. The documentation says about Mixed Mode:

Mixed mode is intended to introduce unified auditing, so that you can have a feel of how it works and what its nuances and benefits are. Mixed mode enables you to migrate your existing applications and scripts to use unified auditing. Once you have decided to use pure unified auditing, you can relink the oracle binary with the unified audit option turned on and thereby enable it as the one and only audit facility the Oracle database runs.

The size of the queue by default is 1MB. If you'd like to change it (maximum: 30MB) the initialization parameter UNIFIED_AUDIT_SGA_QUEUE_SIZE has to be changed.

What happens now to the traditional AUDIT_TRAIL parameter and what effect does it have?

AUDIT_TAIL will still trigger and direct the "old" auditing facilitiy (SYS.AUD$ for the database audit trail, SYS.FGA_LOG$ for fine-grained auditing, DVSYS.AUDIT_TRAIL$ for Oracle Database Vault, Oracle Label Security, and so on). So be aware to have both auditing facilities on at the same time as this won't make much sense. Our recommendation since Oracle Database 11g is generally to set AUDIT_TRAIL in every 11g/12c database explicitly to the value you want. Otherwise it could always happen (and happens many times) that your database accidentally writes audit records into AUD$. Reason why this happens so often: the default setting for AUDIT_TRAIL since Oracle Database 11g is "DB" unless you change this via the non-standard parameter listening in the DBCA (Database Configuration Assistant).

Therefore always set AUDIT_TRAIL explicitly to the value you want to prevent the database from accidental auditing.

Summary - Steps to migrate to Unified Auditing?

Turn off traditional auditing with AUDIT_TRAIL=NONE

Link Unified Auditing into the kernel or enable it on Windows

Define your auditing policies

Monitor it with the views UNIFIED_AUDIT_TRAIL and in multitenant environments with CDB_UNIFIED_AUDIT_TRAIL

A final question remains unanswered:What happens to your existing audit records in AUD$ and the other auditing tables?

Actually I can't answer this question but to me there seems to be no way to migrate existing audit records into the new Unified Auditing facility. But I don't think that this will cause any issues as you can keep and safely store the contents of the traditional auditing. They don't get overwritten or deleted during an upgrade.

Friday Dec 19, 2014

Roy and I would like to thank you for attending at our workshops throughout the globe. We've delivered more workshops than ever before. Most of them in Europe and Asia, but we did visit also South and Middle America, Australia and - well - the US of course.

Thursday Nov 13, 2014

Traveling right now through Asia. It was Beijing for 32 hours, Toyko for 24 hours - and now we are running an internal 2-day workshop with colleagues from Korea, New Zealand, India and some other countries in Seoul. And yesterday I had the pleasure to listen to Tom Kyte to his optimizer talk at the OTN Conference in Tokyo. And I learned a lot - as always when having the chance to listen to Tom, Graham Wood and the other great experts.

Oracle Database 11.1 offered a great new feature: Incremental Statistics Collection which helped a lot to make stats collection on partitioned tables way more efficient. But it had a few flaws and it took a while to work as expected. And it had one side effect when you used it heavily: It stored tons of data in WRI$_OPSTAT_SYNOPSIS. We saw it on some databases at almost 300GB.

Now the thing with such a huge WRI$_OPSTAT_SYNOPSIS can be: It gets a new partitioning layout during upgrades twice:

Upgrade from Oracle 11.1.0.x/11.2.0.1 to Oracle 11.2.0.2/3/4:

Change to Range.Hash Partitioning for WRI$_OPSTAT_SYNOPSIS

This can cause a lot of data movement.

Upgrade from Oracle 11.2.0.2/3/4 to Oracle 12.1.0.x:

Change to List-Hash Partitioning

This will cause not as much data movement as in the previous change

Tom explained yesterday that in Oracle Database 12c Incremental Statistics Collection has gotten a few excellent extensions making it more efficient:

Smaller footprint on disk for synopses compared to previous releases

Incremental stats with partition exchange operations

Ability to define a stale percentage for existing partitions

The latter one is very interesting as it meant: Before Oracle Database 12c whenever you did change a single row within an existing partition during a recalculation of the Global Stats this particular partition need to be examined again - even though just one record has been changed - instead of still using the stored synopsis.

In Oracle Database 12c you can now define a stale percentage. First you'll have to enable it, second you can set a stale percentage by yourself - otherwise the default of 10% would apply - but only if it has been enabled. Otherwise the pre-12c default will be kept (and this is the behavior in Oracle Database 12c out of the box):

Monday May 27, 2013

Almost every week Roy, Carol and I receive one or more emails in the following style:

"Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold 9i) databases. Can we get an advice please?"

or another one here ...

"Upgrade from 8i to 11g. The customer's database is 28 TB (quite big!). Downtime is 5-6 hours. It's on AIX. And it's an it's an Oracle EBS database"

Well, in both cases we lack a lot of useful information - or sometimes things are almost impossible or simply wishful thinking. So we have a collection of (we call them) The Magic Questions. Once those are answered upfront it is way easier to give a helpful advice.

Will you exchange the hardware?

Will you change to a new OS version?

Will you change to an entire new OS architecture?

Will you change the database characterset?

Do you plan to consolidate (schema/database/...)?

Number of databases you plan to upgrade or migrate?

Size of database(s)?

Exact source and target Oracle versions?

Maximum allowed downtime per database?

Fallback requirements?

Test environment available? Testing tools?

Does a performance baseline exist?

Changes required to enable new features?

RAC/Grid Infrastructure already in use or planned?

Once we get the answer and (even more helpful) a sheet describing the entire landscape in more detail we will be able to give some advice.

Friday Oct 19, 2012

Database migrations from an 32bit environment to an 64bit environment keeping the same platform architecture (e.g. moving an Oracle 10.2.0.5 database from MS Windows XP 32bit to MS Windows Server 2003 64bit) does not happen that often anymore. But still we see them getting done. And there are a few things to note when doing such a move.

First of all the important questionis:Will you upgrade your database as part of this move - Yes or No?

If you say "Yes" then you are almost done with that topic as we will take care of that bitnes move during the upgrade. The only thing you have to take care is OLAP in case you are using OLAP Option with Analytic Workspaces (AW) by yourself. Those store data in Binary LOBs - and in order to move AWs from 32bit to 64bit you have to export your AWs prior to the move - and import them later on. People who don't use OLAP don't have to take care on this. In that case you'll have to drop AWs after the export - please see MOS Note:386990.1 for further details.

But if you say "No" (meaning: no upgrade actions involved - you keep your database version) then you have to make sure to invalidate all packages and stored code in the database before you shutdown your database in the 32bit environment and prior to moving it over. And the same rule as above for OLAP applies once you use the OLAP Option.

Thursday Sep 06, 2012

Oracle Consulting did launch a while ago the Oracle Consulting Migration Factory. The purpose of this offer is to support customers in large upgrade and migration projects with hundreds or thousands of databases. If you'll need this support in your projects please check out the Oracle Consulting Migration Factory page.

New:Behaviour Changes between Oracle 8i and Oracle 11.2Please note: This is not a 100% complete summary but it will give you a rough overview about parameter and dictionary changes from release to release plus a summary of documented behaviour changes. Sources were the release notes, the documentation, MOS notes and some others.

Tuesday Apr 03, 2012

First of all we have just upoaded a new version of the Upgrade and Migration Workshop slides with some added information. So please feel free to download them from here.The slides have one new interesting information which lead to a discussion I've had in the past days with a very large customer regarding their upgrades - and internally on the mailing list targeting an EBS database upgrade from Oracle 10.2 to Oracle 11.2.

Why are we creating dictionary statistics during upgrade?

I'd believe this forced dictionary statistics creation got introduced with the desupport of the Rule Based Optimizer in Oracle 10g. The goal: as RBO is not supported anymore we have to make sure that the data dictionary has fresh and non-stale statistics. Actually that would have led in Oracle 9i to strange behaviour in some databases - so in Oracle 9i this was strongly disrecommended.

The upgrade scripts got hardcoded to create these stats. But during tests we had the following findings:

It's important to create dictionary statistics the night before the upgrade. Not two weeks before, not 60 minutes before your downtime begins. But very close to the upgrade. From Oracle 10g onwards you'd just say:

$ execute DBMS_STATS.GATHER_DICTIONARY_STATS;

This is important to make sure you have fresh dictionary statistics during upgrade for performance reasons. Tests have shown that running an upgrade without valid dictionary statistics might slow down the whole upgrade by factors of 2x-3x.

And it would be also a great idea post upgrade to create again fresh dictionary statistics when you've did suppress the stats creation during the upgrade process. Suppress? Yes, you could set this underscore parameter in the init.ora:

_optim_dict_stats_at_db_cr_upg=FALSE

to suppress the forced dictionary statistics collection during an upgrade. We believe strongly that (a) people using the default statistics creation process which will create dictionary statistics by default and (b) create fresh stats before upgrade on the dictionary. Therefore we find it save once you have followed our advice to use the underscore during upgrade. And we've taken out that forced statistics collection during upgrade in the next release of the database.

Please note: If you are using the DBUA for the upgrade it will remove underscore parameters for the upgrade run to improve performance - which is generally a good idea. So you'll have to start the DBUA with that call:

Thursday Dec 22, 2011

Thanks again to our colleagues of Oracle Japan for the (as always) excellent organization of our journey to Japan in December 2011. And thanks to all the customers and colleagues we've met and which did visit the workshops We'll hope to meet you next time again!

And here are some impressions:(you might click on the pictures to get a larger view)

Tuesday Nov 08, 2011

Thanks to everybody being there today for our newUpgrade and Migration to Oracle Database 11.2workshop.

In order to get the new slidesfor this workshop you might download them from here.

Thanks for your time and have successful upgrades and migrations!

Information: I had to reupload the slides on Nov 9th, 4:30am CET (11:30am local KL time). In case you have downloaded them beforehand you may please download them again as the original PDF missed over 300 slides Sorry for the inconvenience!!!

About

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.