Nested Tables 101

From An Expert’s Guide to Oracle Technology

A nested table is much like an associative array but you do not determine the index. The index grows by using the extend command and the index is always an incrementing integer value. You can use the DELETE attribute to delete individual elements so you will always want to

Record Types 101

From An Expert’s Guide to Oracle Technology

A record type is a simple structure that combines multiple datatypes into a single package.

DECLARE
  TYPE r_person IS RECORD (
    fname VARCHAR2(30),
    lname VARCHAR2(30),
    age NUMBER );
	
  v_person r_person;	
BEGIN

 v_person.fname := 'Lewis';
 v_person.lname := 'Cunningham';
 v_p

Learning More About Oracle Database Systems Change Number (“SCN”)

Hi, this is Eric Maurice again.

On January 17th 2012, Oracle released the January 2012 Critical Patch Update. This Critical Patch Update provided two new fixes for the Oracle Database. As usual, Oracle recommended a prompt application of the Critical Patch Update, but additionally, in the blog entry accompanying the release of the Critical Patch Update, I emphasized that Database customers should apply the Database fixes as soon as possible, explaining that the first, relatively easy to exploit, Database vulnerability could result in a complete denial of service of the Database, and that the second issue may have wider non-security implications for the databases of a very small number of customers.

In this blog entry, we are going to further discuss this second database issue, listed in the January 2012 Critical Patch Advisory as CVE-2012-0082. Note that Oracle has posted on My Oracle Support a detailed technical note on this issue along with specific recommendations for Oracle customers (See My Oracle Support Note 1376995.1).

First, let’s look at what Systems Change Numbers (SCNs) are, and why they’re important. As stated in My Oracle Support Note 1376995.1, the “System Change Number”, or SCN, is a special number used to identify database transactions. SCN values are used in many places – among other things, they are persisted within database blocks; are stored in redo records; and are used to help coordinate distributed transactions. Oracle has designed its database so that at any given point in time there is a maximum SCN value that the current SCN should not sensibly exceed – this is called the “Maximum reasonable SCN”. It is important to note that this maximum value is not a fixed value, but rather is a function of the current system time, and therefore grows over time.

In November 2011, journalists from InfoWorld contacted Oracle and stated that in a number of specific instances it appeared that the SCN of a database could grow at an excessive rate, and that this excessive SCN value could be propagated to other databases in the same environment through, among other things, database links. Oracle quickly determined that this temporary SCN exhaustion issue could have certain security implications, and as a result, in accordance with Oracle policies, Oracle handled this issue as a security bug. As a result of Oracle’s handling of the issue as a security bug, Oracle treated InfoWorld as a security researcher, and since the magazine followed responsible disclosure guidelines, InfoWorld received credit in the Critical Patch Update Advisory.

The specific conditions that could result in a temporary SCN exhaustion are complex. Oracle’s development and security teams quickly worked together to understand all the aspects of this multifaceted issue. These groups first needed to determine under which conditions SCN values could grow at an excessive rate. This meant producing diagnosing and troubleshooting scripts, documenting technical recommendations, and producing fixes for the components causing such a SCN growth to occur. In addition, this issue had to be explored from a security perspective to determine if it could be used by malicious attackers. Finally, fixes and utilities needed to be packaged for distribution (e.g. inclusion of a SCN-related Healthcheck on My Oracle Support, and patches provided through the January 2012 Critical Patch Update), and technical recommendations needed to be properly tested and documented so that they could be shared with the small number of customers who may have been at risk of running out of “SCN headroom”.

Now, let’s have a look at Oracle’s recommendations in regards to managing SCN growth in the Database environment. Oracle included in the January 2012 Critical Patch Update the “scnhealthcheck.sql” script (Patch:13498243). This script can be executed with DBA privileges and will report as to the health of the SCN growth in the database. This script is intended to provide customers with a sense of comfort that they’re not about to run out of SCN headroom, as well as potentially identify additional customers who may be running out of SCN values in their environment so that they can proactively take corrective actions.

The script will report a value of either “A”, “B”, or “C.”

If “A - SCN Headroom is good” is reported, then the SCN health in the audited database is good. The vast majority of databases are expected to fall into this group. Customers should then ensure that all their interconnected databases are patched to current level.  . No additional action is required once the databases have been patched other than to set the parameter  “_external_scn_rejection_threshold_hours” = 24 on some database versions. The script output will advise if this parameter needs to be set. 

If “B- SCN Headroom is low” is reported, then SCN headroom is limited. Customers should then ensure that their databases are patched to the current level as soon as possible, preferably within a week, and set “_external_scn_rejection_threshold_hours” = 24  if advised to do so by the script. Once patched, customers should continue to monitor their SCN health daily by running the script, and will notice after several days or weeks that the “scnhealthcheck.sql” script will report “A”.

“C - SCN Headroom is low” will be reported in the very rare cases that customers are running out of SCN headroom. This will occur when the audited database appears to experience an excessively high rate of SCN increase. In such very rare instances, customers should immediately patch their databases to its current recommended level as listed by “My Oracle Support,” and set “_external_scn_rejection_threshold_hours” if advised to do so. In addition, Oracle recommends that these customers also follow the instructions located in My Oracle Support Note Note:1388639.1 to log a Service Request with Oracle Support so that further advice can be given and additional diagnosis performed if required.

For More Information:

My Oracle Support Note 1376995.1 is located at https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1376995.1

January 2012 Critical Patch Update Released

Hi, this is Eric Maurice again.

Oracle just released the January 2012 Critical Patch Update.  This Critical Patch Update provides fixes for 78 new security vulnerabilities affecting a wide range of Oracle products families including: Oracle Database, Oracle Fusion Middleware, Oracle E-Business Suite, Oracle Supply Chain Products Suite, Oracle PeopleSoft Enterprise, Oracle JDEdwards EnterpriseOne, Oracle Virtualization, Oracle Sun product suite, and Oracle MySQL.  Note again that security fixes for Java SE continue to be released on a different schedule because of commitments made before the completion of the Sun acquisition.

Out of the 78 new fixes, 2 affect the Oracle Database.  The maximum CVSS Base Score for the Database vulnerabilities fixed in this Critical Patch Update is 5.5, however Oracle considers these fixes to be important.  In a previous blog entry, we discussed how CVSS Base Scores are computed, and we highlighted the fact that the CVSS Base Score scale is designed to rate the severity of vulnerabilities ranging up to complete exploitation of the affected system down to the Operating System layer (CVSS Base Score greater than 7.5). 
One of the database vulnerabilities fixed in this Critical Patch Update has received a CVSS Base Score of 5.0.  It is a relatively easy to exploit vulnerability, which can result in a shutdown of the database (without compromising confidentiality or integrity of the information contained in it).  In other words, this vulnerability could allow an unauthenticated attacker to carry a denial of service attack against the targeted database, for example if it were to be exposed to the Internet.

Though not remotely exploitable without authentication, the other database fix provided in this Critical Patch Update is also important.  This database bug, which was also reported to Oracle by InfoWorld, may have wider non-security related consequences for a small number of customers.  Database customers are therefore strongly encouraged to apply this Critical Patch Update and consult My Oracle Support Note 1376995.1 for additional instructions.

11 of the 78 new fixes provided by this Critical Patch Update are for Oracle Fusion Middleware.  The highest CVSS Base Score for these Oracle Fusion Middleware bugs is 6.4. 

An additional 17 fixes affect the Oracle Sun product suite, including Solaris, Glassfish Enterprise Server, and OpenSSO.  The highest CVSS Base Score for these Sun product suite vulnerabilities is 7.8.

3 new fixes affect Oracle virtualization.  The maximum CVSS Base Score for these vulnerabilities is 3.7.  This score is related to a vulnerability affecting Oracle VM VirtualBox.

Finally, Oracle MySQL receives 27 fixes.  The maximum CVSS Base Score for these MySQL vulnerabilities is 5.5.  One of these vulnerabilities is remotely exploitable without authentication.  Note that this is the first time that MySQL fixes are being included in the Critical Patch Update.

Oracle continues to recommend that customers apply all security patches and keep up with newer releases as a means to continue to preserve their security posture.  As highlighted in this Critical Patch Update, the decreasing number of fixes produced for the most mature product lines in recent Critical Patch Updates should not be construed as an indication that Critical Patch Updates are becoming less important to the security posture of Oracle customers.  Furthermore, security research continues to show that unpatched systems remain an attractive target for malicious hackers.  Fortunately, Oracle customers can leverage a number of tools, including My Oracle Support, to keep up with recommended security and non-security releases.

For More Information:

• The Advisory for the January 2012 Critical Patch Update is located at http://www.oracle.com/technetwork/topics/security/alerts-086861.html
• More information about Oracle Software Security Assurance is located at http://www.oracle.com/us/support/assurance/index.html
• My Oracle Support Note 1376995.1 is located at https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1376995.1 (My Oracle Support authentication required).
   
  

2011 in Review and 2012 Goals

To get where you want to be in life, you need to have goals. It doesn't matter if you want to advance professionally, improve a hobby or lose weight, goals help you achieve your desires.

I think it's important to look back at the previous year and see how you did as far as your goals. When I see how realistic my previous year goals were, then I can decide how to set my goals for the current year. Unfortunately, I didn't list my goals for 2

For those in the US – Stop SOPA!

From An Expert’s Guide to Oracle Technology

I rarely, if ever, bring up politics. Currently the US Congress is considering legislation that I think is reprehensible. I really can't believe that we, as a nation, have come to this. I have been reading about this for a while. I guess I have been in denial that it would really happen.

Have you heard of