Exadata - some helpful MOS notes

I travel all over the place working on Exadata Database Machines. I often do patching and other kinds of work. As you might expect, patching is usually a nervous time for the customer. It's a nervous time for me too! Sitting there and waiting for the storage servers to reboot, or the compute nodes to reboot is nerve wracking.... but it does provide a moment or two for a good conversation with the people sitting around you.

I've found that over and over there are certain MOS notes that I reference time and time again. Some of them I use so much I actually have the note numbers memorized. Isn't that sad? What I decided to do about six months ago was to create a cheat sheet of notes that I use often. That way I can just cut and paste the note number into MOS and *poof* - there it is. I also find this handy when MOS has timed out my session and the note I previously had up, is no longer there (which I find irritating).

I've removed the internal notes from this list, since you would not have access to them anyway. If you look for one of these notes and don't have access to them because they are not public or the note does not appear to exist, please let me know as a comment here.

I hope you find these notes helpful. If you have any you use a lot, please leave a comment here and I'll take a look at it and update the list if it's list worthy! Also, I'll update this list from time to time here so it stays current.

Here is my Exadata Note cheat sheet...

As my friend Tim Hall says on his site oracle-base.com - Hope this helps!

Information Center

1306791.2 – Information Center: Oracle Exadata Database Machine

Best Practices

757552.1 - Oracle Exadata Best Practices 

 

Operational

1473002.1 – Using dbserver_backup.sh to backup compute nodes. 

(Section 7 – run backup – Section 8 – roll back)

1538068.1 – Remove partition if dbserver_backup.sh fails.

1428394.1 – Password stuff (pam_talley2)

1269133.1 – Password stuff (pam_talley2 – resetting the /etc/pam.d/sshd file.

1093890.1 – Shutdown and startup Exadata and Compute nodes on rack.

1446274.1 – ILOM command reference (startup and shutdown Exadata from ILOM).

1520896.1 – DBFS Configuration Health Check

1054431.1 – Configure DBFS on Exadata Checklist

Cell Related

1306635.1 – Replacement of flash – how to check firmware and status. Resetting status.

Patching Notes

1262380.1 – Master note on Exadata patching.

888828.1 – Master note on Exadata supported versions. Really important note!

1473002.1 – Using ULN to install server patches with YUM. This covers both the compute nodes and cell node kernels.

1545789.1 – ISO install Cheat Sheets

1136544.1 – Relinking notes.

1553103.1 – Exadata Database Server Patching using the DB Node Update Utility

Notes on patching related problems I’ve encountered

1.       Node rebooting after backup of compute node or after patch is complete.

# This note discusses and issue with compute node backups. The method of backup in the documentation and the recommended method of backup

# of the compute nodes before a patch (as per 1473002.1 – see above in operational section) are different. You can run into this problem if they have been

# using the manual backup (in the docs) and then change to using db_server_backup.ksh.

1538068.1 -  Exadata compute node continuously booting with kernel panic

2.       Node won’t boot after starting patching process.

# Related to the problem above – if they are doing manual backups they have to drop and re-create a partition. I’ve had one customer who accidently dropped the wrong partition (the boot partition). If they started patching and all of a sudden a node panics or won’t boot, check on this as the likely issue. You can log onto the ILOM and use lvdisplay as seen here:

lvdisplay|grep 'LV Name'
  LV Name                /dev/VGExaDb/LVDbSys1  -- boot partition
  LV Name                /dev/VGExaDb/LVDbSys2 – backup boot partition
  LV Name                /dev/VGExaDb/LVDbSwap1
  LV Name                /dev/VGExaDb/LVDbOra1

if LVDBSys1 does not show up, or it’s empty – then you have hit this problem. Open SR and you will need to do a restore of that node.

Current patches notes (As of 9/25/2013) on patches from 888828.1

Latest Releases and Patching News

• Oracle Database 11.2.0.4 is supported on Exadata Database Machine.
• New 11.2.0.3 Database release - Patch 16869210 - 11.2.0.3 recommended Quarterly Database Patch for Exadata Jul 2013 - 11.2.0.3.20
• New QFSDP release - Patch 16784347 - Quarterly Full Stack Download Patch (QFSDP) Jul 2013
• New 11.2.0.2 Database release - Patch 16824987 - 11.2.0.2 Bundle Patch 21 for Exadata
• Oracle Database 12c is supported on Exadata Database Machine.  See Document 1537407.1 for details.
• In Exadata release 11.2.3.2.0 and 11.2.3.2.1, the Oracle Linux Unbreakable Enterprise Kernel (UEK) and non-UEK kernels were both shipped and switching between them was supported on 2-socket database servers. The large majority of Exadata customers installed the default UEK kernel.  UEK was the only option on 8-socket database servers.  Starting with the next Exadata release after 11.2.3.2.1,  the only installable kernel will be the UEK2 kernel. The UEK2 kernel is specifically tuned for performance and availability for Engineered Systems.
• New OneCommand release - Patch 16921136 - Supports 11.2.0.3.19 and 11.2.0.2 BP20
• New Exadata Storage Server release - Patch 14522699 - Exadata Storage Server software 11.2.3.2.1 (Note 1485475.1)
• Minimum version required if using Write-back Smart Flash Cache
• Oracle Solaris 11.1 is supported on database servers.

Deprecation, Desupport, Superseded, Unavailable and the Oracle Database. What's that all about?

In the Oracle Database world we hear two terms with some frequency, deprecated, desupported. We hear other terms with less frequency: superseded and unavailable. I thought I'd take a moment to explain each of these terms in Oracle speak, and what they mean to you, the DBA, architect, or whatever your title is should you manage, work with or design Oracle databases.

Let's start with the term superseded. Obviously this implies that something is replacing something else. For example, in the 12.1 Oracle Database documentation we find this term used with reference to the dbms_job package:

"The DBMS_JOB package has been superseded by the DBMS_SCHEDULER package. In particular, if you are administering jobs to manage system load, you should consider disabling DBMS_JOB by revoking the package execution privilege for users. 

This simply  means that the dbms_job package is being replaced by the dbms_scheduler package. In this case though, dbms_job is still being supported for backwards compatibility. This implies that you should start making plans to migrate to the new feature that supersedes the old feature. While I can't find anything that specifically indicates this fact, I'd assume that anything that is superseded will someday find itself deprecated or desupported.

That leads us to the term deprecated. When an Oracle component, feature, package, etc is deprecated that means the following:

• Product/feature is still in the database and usable.
• Oracle support will allow you to open SR's.
• Oracle support will assist you with your problem.
• Patches are allowed to fix bugs.
• One off patches are possible.
• Ongoing development is not occurring.

Notice that if your product/option is deprecated that you can get bug fixes (most of the time)... It is a common misconception that you can't get patches, and clearly this isn't the case. There are going to be times that a bug can't be fixed for various reasons (this happens with supported features!) so, just be aware of that fact.

As I said, I consider deprecation of something in the database to be a bit like a shot across the bow. It's a notice from Oracle that sooner or later, this feature will become desupported. 

Which leads us to the term  desupported... This is no-mans land, and you really don't want to find yourself using something that is desupported. This is like being caught out on a desert island with no-one there to help you. When something is desupported in Oracle it means:

• Oracle support will assist with SR's.
• No bug fixes are available. 
• A product will be desupported only on major releases.

Again, note the Oracle support will help you with SR's, it's just that if you find a bug, your out of luck. No patches, no one-off's, no nothing. Your on your own. This then leads to the final death knell of a product, unavailable or decommissioned.

When a product is unavailable or decommissioned it's just plan old gone. This is when it's too late. That is when you start running, not walking, to find an answer.

Typically a product will go through a desupport cycle - moving from deprecation to desupport to unavailable. In my career I've seen few products that are a part of the core database actually become unavailable, even after they are desupported. A few have, but not many. I've also seen products go from supported to unavailable in one major release, such as Oracle Database Control did in Oracle Database 12c. Granted, it got replaced by Oracle Database Express, but it is a huge change without much notice (there was a MOS note created to make everyone aware this change was coming - I gladly stand as one of the main reasons (along with a certain VP at Oracle) that this MOS note was created in the first place!).

How do you know what products are deprecated or desupported? The Oracle documentation is the place to go. The Oracle Database Upgrade Guide is the main source of this information for each release. The link that I have posted here goes to the Oracle Database 12c documentation. Each version of Oracle Database has it's own Upgrade Guide with this information in it.

Finally, you may the terms desupported thrown around with a bit of a different meaning at times. Sometimes (as with the case with Oracle Enterprise Manager Database Control, Oracle uses the term Desupported when I believe it should probably be denoted as decommissioned or superseded by. Since it really no longer exists, I believe it's in the Oracle graveyard and thus, deserves the keyword decommissioned on it's tombstone. That's just my opinion.... I've written the powers that be to ask them about this - so let's see what they tell me. :)

Edits:
9/23 - One edit to correct a sentence that was not clear and correct spelling.

GoldenGate Podcast is out!

Now for something not Exadata!

You may be aware that I've got a new book out on Oracle GoldenGate. I've been really happy with the feedback and if you are using Oracle GoldenGate, or just want to learn about it, I hope you will check out the book.

I just completed a podcast with Nick Wagner, the Director of Product Management for Oracle GoldenGate where we discuss the content of the book, and also the future of Oracle GoldenGate. You can listen to this Podcast here. Nick and I discuss a number of things related to Oracle GoldenGate in this podcast including some comments on changes you will see coming up in future releases of GoldenGate.

Also, we discuss some of the Oracle Database features that are being deprecated in Oracle Database 12c that are related to replication. This includes deprecation of Oracle Streams and Oracle Advanced Replication. The guidance from Oracle for some time has been that Oracle GoldenGate will become the replication tool for Oracle Database. The deprecation of Streams and Advanced Replication is a shot across the bow for those who didn't believe what Oracle has been saying for some time.

Don't wait for the direct hit on your database hull to start planning and moving to Oracle GoldenGate.

More on deprecation, desupport and a less used term, superseded with respect to the Oracle database in another post!

I hope you will check out my podcast. Please feel free to leave feedback and comment here!

Robert

Oracle 11.2.0.4 has been released - Redaction has been backported!

Greetings!!

Oracle 11.2.0.4 had been released for 32-Bit and 64-Bit Linux and Solaris (SPARC and X86 versions). It's available for download on the Metalink Oracle Support (MOS) page at support.oracle.com. Of course you have to have an Oracle support contract to download the update.

One new feature in Oracle Database 12c was Oracle Data Redaction. This new feature has been backported to Oracle Database 11.2.0.4!

Oracle data redaction helps you to mask data that is returned by SQL statements. The data is not stored in the database in redacted form, but is only displayed in it's redacted form.

There are three different kinds of redaction:

• Full redaction - The complete column is redacted. For example, a number might be replaced with all x's characters, or the column might return as just a blank.
• Partial redaction - A regular expression is used to format the redaction such that only part of the column value is redacted. For example, a social security number of  000-00-0000 might be displayed as xxx-xx-0000.
• Random redaction - The database will just display the data in the column as randomly generated characters.

You enable redaction on a column by column basis using the dbms_redact.add_policy package. In this call you define the owner, table and column name to be redacted. You also define the name of the policy, the function type and then expression. In some cases, Oracle provides functions that you can use (such as the dbms_redact.redact_use_ssn_f5 function which redacts SSN's).

Here is a basic example of redaction:

We have this table in the scott schema:

sql> connect scott/password

SQL> desc emp
 Name                Type
 ------------------- ----------------------------
 EMPNO               NUMBER(4)
 ENAME               VARCHAR2(10)
 JOB                 VARCHAR2(9)
 MGR                 NUMBER(4)
 HIREDATE            DATE
 SAL                 NUMBER(7,2)
 COMM                NUMBER(7,2)
 DEPTNO              NUMBER(2)


select ename, sal from emp;

SQL> select ename, sal from emp;

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300

14 rows selected.

We probably don't want to be showing the SAL column to just anyone. So, let's add a redact policy to redact the values in the SAL column:

First we are going to drop any pre-existing policy. Otherwise we would get an error if the policy already existed when we try to create it:

BEGIN 

 DBMS_REDACT.DROP_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMP' 

,  policy_name      => 'mask_sal'

);

end;

/

Now let's add the policy:

begin 

DBMS_REDACT.ADD_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMP'
,  column_name      => 'SAL'
,  policy_name      => 'mask_sal'
,  function_type    => DBMS_REDACT.FULL
,  expression       => '1=1'
); 

end; 

/

Note that in the ADD_POLICY call, we name the schema, object and column we want to redact. 

We then name the policy. The function_type parameter defines the type of redaction that is occurring here. In our case, we will redact the whole thing.

The expression parameter provides a lot of flexability with respect to which user 

accounts the the redaction policy policy applies to and which users it does not 

apply too. In this case, the 1=1 means that the policy will apply to all users 

and roles. Having added the policy, let's note the following:

Any user with DBA or SYSDBA privileges will not have the result set redacted. As such, 

this new feature does not replace Oracle Data Vault in any way. The user that owns the 

object that has the redaction policy defined on it, will not have the result set 

redacted. So, if we are logged in as SCOTT we would see the same results before. 

This is because SCOTT owns the table. Let's see what happens though when another 

user that has SELECT access queries the table:

connect testing/password

SQL> select ename, sal from scott.emp;

ENAME             SAL
---------- ----------
SMITH               0
ALLEN               0
WARD                0
JONES               0
MARTIN              0
BLAKE               0
CLARK               0
SCOTT               0
KING                0
TURNER              0
ADAMS               0
JAMES               0
FORD                0
MILLER              0

14 rows selected.

Let's assume that we have a table that looks like this:

SQL> select * from empssn;

     EMPNO SSN
---------- -----------
      7369 000-00-010
      7499 000-00-011
      7521 000-00-012
      7566 000-00-013
      7654 000-00-014
      7698 000-00-015
      7782 000-00-016
      7788 000-00-017
      7839 000-00-018
      7844 000-00-019
      7876 000-00-020
      7900 000-00-021
      7902 000-00-022
      7934 000-00-023

14 rows selected.

Let's redact the SSN. In this case we can use a pre-defined function, which makes the redaction process quite easy:

connect scott/password

BEGIN 

 DBMS_REDACT.DROP_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMPSSN' 

,  policy_name      => 'MASK_SSN'

);

end;

/

begin 

DBMS_REDACT.ADD_POLICY(
   object_schema    => 'SCOTT'
,  object_name      => 'EMPSSN'
,  column_name      => 'SSN'
,  policy_name      => 'MASK_SSN'

,  function_type       => DBMS_REDACT.PARTIAL
,  function_parameters => DBMS_REDACT.REDACT_US_SSN_F5

, expression => '1=1' ); 

end; 

/

Note here that we have added a function_parameters parameter. The function_type is

dbms_redact.partial. The function_parameters parameter provides a way for us to send various

parameters to the function that indicate how the redaction should occur.

In this case, we used the REDACT_US_SSN_F5 shortcut that is provided with the 

DBMS_REDACT package. There are a number of different shortcuts available to use, 

so you don't need to constantly be customizing your redaction patterns.

If there is a need then you can customize the redaction pattern fairly easily. 

Much more detailed information on this feature can be found in the 

Oracle Database 12c Release 1 Advanced Security Administrators Guide 

and the  

Oracle Database 11g Release 2 Advanced Security Administrators Guide.

Concluding the last example, by connecting to the testing schema, we can now see that the SSN column is redacted:

connect testing/password

  1* select * from scott.empssn
SQL> /

     EMPNO SSN
---------- -----------
      7369 XXX-XX-010
      7499 XXX-XX-011
      7521 XXX-XX-012
      7566 XXX-XX-013
      7654 XXX-XX-014
      7698 XXX-XX-015
      7782 XXX-XX-016
      7788 XXX-XX-017
      7839 XXX-XX-018
      7844 XXX-XX-019
      7876 XXX-XX-020
      7900 XXX-XX-021
      7902 XXX-XX-022
      7934 XXX-XX-023

14 rows selected.

Pretty cool stuff.

Remember, this data isn't changed in the database. So when you export it or back it up, the data is backed up in it's original version. The redaction itself is only taking place as the rows are processed and returned to the user.

Edits: Content and format.

Oracle Database 12c - RMAN now restores tables!

Have you ever wanted to just restore a given table, or partition of a table, from an RMAN backup using point in time recovery? While there were hack ways to do it in the past, Oracle Database 12c fully supports this table level and partition level recovery method.

First - as always - a few rules.

1. The database must be in ARCHIVELOG mode.
2. You must have a backup of the datafile(s) that contain the object.
3. That backup must have been completed before the point-in-time that you want to restore the table or table partition too.
4. You need all of the archived redo logs from the point-in-time that the backup of the datafile(s) until the point-in-time that you wish to restore too.

If you have met all of these requirements, then you can restore a table to an earlier point-in-time.

It would be really nice to be able to restore a table to a later-point-in time... I'm calling that the Quantum Entanglement Restore and I'm sure it will be coming soon! Imagine how analytics would change with such a feature!

Here is an example of recovering four tables: EMP, DEPT, BONUS and SALGRADE in the SCOTT schema:

recover table scott.emp, scott.dept, scott.bonus, scott.salgrade
until time "to_date('09/09/2013 12:44:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/app/oracle/aux'
remap table scott.emp:rest_emp, scott.dept:rest_dept,
scott.bonus:rest_bonus, scott.salgrade:rest_salgrade;

Note that RMAN uses an auxiliary instance to perform the restore (not surprisingly).

An error will be raised if the table you are trying to recover is present in the database (so you will need to rename old copies of the table, etc). If you want to keep the original tables in place, then you can use the remap table clause (as I did in the example) to rename the tables being recovered. You can also remap recovered tables into new tablespaces and schema's if you like.

In my mind, this functionality, along with other functionality like flashback database, pretty much eliminates any reason for using Oracle Data Pump in any kind of daily backup strategy. I'm not saying there isn't a place for Data Pump, clearly there is when doing things like transportable tablespaces. However, I fail to find much rationale for it's use as a daily backup strategy more and more as time goes on.

First post - More on Exacheck....

It's time for my first post on my new Oracle blog. I split this blog off from my personal blog, so those of you who were just Oracle interested could come here and not need to worry about my personal stuff.

In my other blog I posted a few things about Exadata and Exacheck. Here are the links to those previous articles:

Exadata Patching Part One

Exadata Patching Part Two

Exadata Patching Part Three

Exadata Patching Part Four

Exadata Patching Part Five

For my first new post here, I thought I'd continue my thoughts on Exadata and in particular on Exacheck.

While there is a lot of gold in the header of the Exacheck report, there are some things that don't get flagged there that you won't catch unless you look through the detail findings. Also, there is so much information in the Exacheck output, it's easy to just miss something. At the end of the day, it seems that it's the little details that can make all the difference.

For example:

At the top of the report there is the section titled Findings Needing Attention. This section tends to get a lot of attention, in fact I think it gets a majority of the attention. But, lurking below are interesting things that don't surface to the Findings Needing Attention list.

In the report, after the Findings Needing Attention section is a section titled MAA Scorecard. When you review your Exacheck report, how carefully do you review this section?

For example, at one customer lately I found an entry in the MAA Scorecard that might be of concern.

As I parsed through the MAA scorecard, it looked fine until I hit the section titled:

DATA CORRUPTION PREVENTION BEST PRACTICES

Low and behold, I found this jewel:

FAIL

SQL Check

The data files should be recoverable

All Databases

View

Now, that little gem is somewhat of concern. I would think that it should be in red, blinking lights with warning horns going off. Of course, it might be a totally normal condition (such as in the case of nologging direct loads into tables) but it might also be a serious indication that there are things happening to your database that you don't want to happen. For example, maybe nologging direct loads are happening and this has not been integrated into your backup/recovery planning.

So.... even though the Exacheck report is long and full of data, I'd recommend that, at least the first time, you look through the whole thing and determine which pieces you consider to be important to look at.

Bonus thought....
Monitoring is important. I'm wondering how hard it would be to automate the output from Exacheck, and then create an external table that reads those results. Then you could build some PL/SQL to automate the checking out the output for the sections that are truly important.

The challenge that I find in this is that Oracle constantly improves Exacheck and changes the output format. So keeping up with those changes would be an interesting problem.

Thoughts?

Edit History
9/6/2013 - Edited to make the links to my other articles weblinks instead of just text.