Lucky or not?

 

Flashback technology has been around for quite awhile.   However, today was the first day I actually ever had to use it in a real life scenario.   Does that mean I am lucky or not?   

A few minutes ago developer came by and asked if Oracle kept versions of pl/sql code in the database.   I replied no and asked why.   Turns out that he was working on some code, compiled it, etc and it didn’t work properly.   He didn’t have a copy of the original package.

I wasn’t sure if it would work but I thought I would give flashback query a try.  Here is the SQL I used:

1  select text from all_source
2  as of timestamp
3  to_timestamp('29-MAY-2012 15:50:00', 'DD-MON-YYYY HH24:MI:SS')
4 where type = 'PACKAGE BODY' and name = 'MYNAME’

I saved the output and sent it over to the developer.   He was very happy.   Always nice to end a day on a high note!

11.2.0.2 Database Online Mode patching

I've read on a few blogs, such as Pythians, about the new online mode patching for 11.2.0.2.   However, up until this point I have not seen any applicable patches.     Currently i'm in the process of putting together a patch analysis for the Grid Control Application Management Pack.    As part of the pre-reqs for an R12 environment I have to apply a number of database patches.

Two of the patches 10160615 and 12400751 are able to be applied in Online Mode.   Its too bad 2 other patches can't be..  Regardless its nice to see that some patches are now able to be applied while the system is up.

For more information on Online Mode (Hot Patching) take a look at Metalink note:

RDBMS Online Patching Aka Hot Patching [ID 761111.1]

AUDIT_TRAIL = DB, Portal and Grid Control

If your not familiar with Oracle’s auditing features then a good place to start is with Oracle’s Documentation.   I personally think Oracle’s documentation, especially for the database products is great. 

http://docs.oracle.com/cd/B28359_01/network.111/b28531/auditing.htm#autoId1

The above documentation linked to above describes what auditing is, why you would need it, etc.   There’s no need for me to repeat it here.

Our environment is pretty small, with a limited number of people who have access to the databases, etc.   So normally I have the audit trail disabled, however for this particular database we did need to increase logging to investigate some issues.  After we were finished, we used NOAUDIT to disable the extra logging we enabled.   However we left AUDIT_TRAIL set to DB.

Move to a few months later and on a routine scan of database performance I noticed a query consuming a fair amount of resources. 

SQL Details: gh9pd08vhptgr
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00')




By looking at the screenshot above it seems pretty nasty but in reality it was just a small spike on the Grid Control Top Activity chart.    As I mentioned, our environment is small and our servers have more than enough horsepower, so it didn’t have a significant impact on performance. 

From Metalink note: Slow Performance Of DBA_AUDIT_SESSION Query From EM [ID 829103.1]  “There is a known performance issue with DBA_AUDIT_SESSION table per non-published Bug 7633167”

However with AUDIT_TRAIL=DB, LOGON and LOGOFF’s were still be recorded.  Since this database hosts our Portal schema it mean upwards of 200k of entries going into the sys.aud$ daily, for a grand total of 32 million rows!

The note provides two options for the query above, the first is to disable the Failed Login Count Metric, the second is to purge the sys.aud$ table.  Note 73408.1 How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

Since at the moment we don’t require auditing to be enabled, then I am simply going to truncate the sys.aud$ table and disable auditing by setting the database initialization parameter AUDIT_TRAIL=none and restarting the database at the next maintenance window.

If you do require auditing then you should setup a purging strategy.  If you need to do this, some good blog articles to read are:

http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/
http://damir-vadas.blogspot.com/2010/06/auditing-database.html

Receiving Clear alerts after a Blackout–Grid Control

Grid Control allows you to set blackouts for targets so that while your performing maintenance you won’t get notified    You can also use them to disable notifications while your working on an issue.  Nothing worse than being paged multiple times while your trying to fix an issue. 


One of our applications has a component which requires a quick nightly bounce schedule via cron.   So I setup a blackout in Grid Control to start 5 minutes before and extend to 5 minutes after the restart.   However, at 3am I received a lovely page letting me know that an alert has cleared:


Subject: EM Alert: Clear:MyApp PROD - Test MyApp Login Page is now up

Target Name=MyApp
Target type=Web Application
Host=
Occurred At=Mar 5, 2012 3:15:00 AM EST
Message=Test MyApp Login Page is now up: MyApp Login Page has status 6 since 03/05/12 03:15:00 till 03/05/12 03:15:00 in America/New_York. Beacon RCPSC Status: 1 from 02/28/12 09:54:31 till 03/05/12 03:17:13 in -05:00. No new severities found after the blackout Metric data found after blackout, using the latest severity Latest severity from the beacon is 15 at 02/29/12 21:13:04 Beacon votes up Beacon Tenzing Status: 1 from 02/28/12 10:20:19 till 03/05/12 03:16:48 in America/New_York. No new severities found after the blackout Metric data found after blackout, using the latest severity Latest severity from the beacon is 15 at 02/29/12 19:26:14 Beacon votes up The final status of the test is UP from 03/05/12 03:15:00 till 03/05/12 03:17:13
Metric=[Test Response] Status


Strange.    Initially I thought that there may have been a system time issue between the servers, or that the restart had taken longer than I expected.   Looking into it tho that was not the case. A search on metalink turns up that there is a bug:

Bug 10210193 WEB APPS SERVICE GENERATE ERROR WHEN BLACKOUT START the Notification rule used has the metric [Test Response] Status which is generating the notification.


The solution is to remove the remove the [Test Response] metric from the rule.    No sleep interruptions the following night!

CPU Patches and Minimum Baselines

As you may or may not be aware, your environment has to meet a minimum baseline before you can apply CPU patches.  Typically Oracle supplies patches for both the latest patch set of a product and the previous patch set for a certain grace period. For example, below is clip taken from the EBS ECS Policy document  (Note: 1195034.1):

In addition, a given release update pack for the Applications Technology
product family will be treated as the minimum baseline 18 months after its
release. Specifically:

The Applications Technology RUP from 12.1.2 (R12.ATG_PF.B.Delta.2, Note
ID 845809.1) becomes the minimum prerequisite baseline on July 1, 2011.
The Applications Technology RUP from 12.1.3 (R12.ATG_PF.B.Delta.3, Note
ID 1066312.1) becomes the minimum prerequisite baseline on February 1,
2012

So the grace period for the ATG product family is 18 months.   ATG_PF.B.Delta.2 is the current baseline, however, after Feb. 1st the new baseline is ATG_PF.B.Delta.3.    That means if your not already on Delta.3, your going to upgrade before or as part of the July CPU release.

For other products the grace period may be different.  For example, for the database it is a minimum of 3 months, maximum of 1 year.  For more details see:  Database, FMW, EM Grid Control, and OCS Software Error Correction Support Policy [ID 209768.1]


The January 2012 CPU release information can be found here:

http://www.oracle.com/technetwork/topics/security/cpujan2012-366304.html

For Fusion Middleware and Database customers the best way to find this information is to keep an eye on the “Final Patch History” section of the patch availability document.  

For E-Business Suite customers make sure you read the yellow highlighted sections of the patch availability document.

We use E-Business Suite, Weblogic, Grid Control, Database and Fusion Middleware products.  So some key dates for us are:

• January 2012:
• Oracle Fusion Middleware 11.1.1.3
• Oracle WebLogic Server 10.3.3.0
• E-Business Suite:  ATG_PF.B.Delta.2
• April 2012:
• Oracle Fusion Middleware 11.1.1.4
• Oracle WebLogic Server 10.3.4.0
• July 2012:
• Oracle Database 11.2.0.2

The reason I mention this is because it catches people off guard all the time.  Upgrading to new baselines can add a significant amount of time to applying CPU’s, and its nice to make sure management, business owners, etc have as much time as possible to prepare.