Oracle Database 12g New Features is out!

I apologize for the absence. I've been covered up with projects and writing and realized today that I've missed writing a blog entry for a while. So, I have some ideas and in the next few days I hope to correct that problem and bring you some helpful information in the next few days.

First, exciting news! My new book Oracle Database 12c New Features is out. I'm really excited about this book! I think it may well be my best new features book to date. There is so much in Oracle Database 12c - real break through new features like Oracle multitennant. Great administrative new features such as even more online operations are available for commands such as drop index, drop constraint, alter index unusable and alter index visible and alter index invisible.

I'm pleased to see the book doing well on Amazon. To have a technical book sit somewhere below 100,000 on the sales ranking is a very good sign on sales. The book has been sitting in the 30 to 50k range since it was released - I have you to thank for that! I worked hard on this book along with the contributors Scott Black and Eric Yen. Tom Kyte also did a stellar job on his introduction and in the insights he adds with his Tom Says sections. I hope you will enjoy the book. I will be adding an errata page to this blog in the near future (as soon as I get the first errata entry!).

Something else that always seems to happen to me is that I discover a new feature, after the New Feature book is released, that I really wish I'd written about. I will be setting up a page specific to these new features as soon as the first one surfaces. I'll also be blogging about them here as well.

You will be able to access both pages from the main page of this blog easily. So stand-by for that.

In the near future I will be writing more about Oracle Database 12c, and I will also be writing about Exadata some more. The next QFSDP is around the corner (January) and from what I've heard it may well be one of the more significant QFSDP patches we have seen in a while. More on that as things develop.

More soon!

Did you make a mistake patching the Oracle Home and Grid Home on Exadata?

Part of the process of applying Oracle patches can involve relinking the Oracle Executable. For example if your using Database Vault, you might need to relink your Oracle Executable to either enable or disable that feature. Some patches might require that you manually relink the software.

The thing is, in Exadata, it's important to link the software the correct way. This is because the Infiniband fabric uses a protocol called the RDS protocol to communicate. In order to use the RDS protocol, you need to make sure the database is relinked using the correct libraries.

So, if you need to relink the ORACLE_HOME or GRID_HOME software make sure you include the ipc_rds library in the make command as seen here:

make -f ins_rdbms.mk ipc_rds ioracle

If you run Exacheck it will tell you if you are not linked using the RDS protocol.

So, if you find your having network issues or node fencing - check to make sure you are using the RDS protocol. Oracle provides a way to check if you are using the RDS protocol. Simply set the ORACLE_HOME correctly (and other variables) and issue the following command:

$ORACLE_HOME/bin/skgxpinfo

If it says rds, your in good shape on that node if you get the rds response from each Oracle Home and Grid Home.

Also, are the databases in your Exadata communicating over infiniband? Did you know that by default they do not. I'll talk about that in my next post!

Robert

The Exadata October QFSDP is out!

Patch number 17452393 has been released. This is the October QFSDP for Linux Exadata. You can find it on Metalink by going to the patches and updates page and putting in the patch number.

Here is a list of whats new in the October QFSDP delta'd against the July:

Databsae Patch to 11.2.0.3.21

Opatch to 11.2.0.3.5

Oplan to 12.1.0.1.3

The Cloud Control  patches are a bit changed too:

OMS Base patch set number is 16290212 (changed from 16236221)

Two new agent patches: 14075824 and 14509490 (along with the previous 4 patches in July which are still there).

DBPlugin has 2 new patches on top of the 3 that were already there :

16910687

17052137

Also the DBNodeUpdate Utility has appeared in the patch. Note 1553103.1 provides information on the DBNodeUpdate utility.

Also check out document 888828.1 for important information on all Exadata patches.

For those of you running 12c GI on Exadata, there is a patch available for you. This is an upgrade to 12.1.0.1.1 GI. (this is a PSU, and not contained in the QFSDP). See patch 1727829 for more information on this patch. This patch set also says it contains a DB update (I've  not yet installed the patch so I can't as yet verify this is true).

So - go, test, test, test and then patch!!

Oracle Database 12c New Features is almost here!

I've had several emails asking me about my new book Oracle Database 12c New Features. Well it's almost here! For those of you who are interested in gory details, writing a book is a multi-phased process.

1. You do a proposal, they accept... or don't.
2. You write your chapters.
3. The chapters go through technical editing - I get the chapter back and have to review and reply to each comment by the technical reviewer. Then I send the chapter back for copy editing.
4. The chapters go through copy editing - this is where they correct grammar, sentence structure, the general outline and so on.


5. The publisher then typesets the pages. In this process the pages are transferred from a word document, into a format that the printer can use. After the pages are all set, a PDF of the page is produced and sent to the author.
6. Review the page proofs and return comments and corrections.
7. The publisher does more of whatever they do before the book goes to the printing press.
8. The book is printed. It used to be that the print runs were larger. My 9i New Features book first print run was 5,000 copies I think, maybe even 10,000. Now days, they can print on demand much easier so the initial print runs are usually not as large. I'm not sure how big this print run will be.
9. The book is shipped out to be sold by Web and brick and mortar stores.

So... I just finished step 6, the page proofs.... which pretty much ends the writing process for the book. Now, it's just a matter of getting it printed. I am hopeful that we will get it in the stores before the currently reported publish date of 12/13/2013.

I'm now taking a 2-3 day holiday and then starting on my next book project - OCP - Oracle 12c Administrator Certified Professional!

I have another project coming sometime in the future that I call "Scorched Earth", but that's a totally different story and has nothing to do with Oracle. Someday I will write a book about it though.

Enjoy your Columbus day!!

More Exadata Patching Gottya's (Part Two)

In my last post I produced quite a list of Oracle Exadata patching gottya's. I think shared my thoughts about the first two:

1. Didn't run/address Exacheck findings
2. Root FS full or nearly full

Let's talk about the next two on my list:

1. Didn't backup the compute node boot partition.
2. Lost passwords

Backup and Restore of the Compute Nodes

Each time you apply a patch, the patch set instructions tell you to backup the boot partition of the compute node (you don't need to do this on the cell servers). There are two logical partitions on the Exadata box, the first is the boot partition and the second is the backup boot partition. I mentioned these earlier, but they deserve mention again:

/dev/VGExaDb/LVDbSys1 - Boot partition
/dev/VGExaDb/LVDbSys2 - Backup partition

You can use the imageinfo command to verify the boot partition (since things do change from time to time).  

In the early days of Exadata you had to manually perform the backup of the boot partition to the backup partition. Oracle then added the dbserver_backup.sh script to make backing up the boot partition to the backup partition easier. The dbserver_backup.sh script will backup both the root file system and the /boot file systems.  Note that the /u01 file system is not backed up with the dbserver_backup.sh command. You will want to make sure that /u01 gets backed up as well before you start patching. This can easily be done via a tar command. Make sure you backup /u01 and the root FS on a regular basis.

On his blog, Vishal Desai provides a nice script to snapshot the root and /u01 file system and then back it up to a mount point on ZFS. You could, of course, use any NFS mount point to do this, but if your connected to ZFS via Infiniband your going to get the benefit of the speed of Infiniband. Please be aware that I've looked at this backup script and it seems ok, but use it at your own risk. I've not tested it on an Exadata box (yet). The next time I'm on one I'll do so if I have time. Please, always use the dbserver_backup.sh script whenever recommended by Oracle.

You should schedule regular backups of root, and /u01 on your Exadata boxes. It's that important. Bare metal restores can be a pain! If you want to see how to restore the system from your backups you can look in MOS document 1556257.1 titled "Exadata YUM Repository Population, One-time Setup Configuration and YUM Upgrades". Section  7 titled Rolling Backup Software Changes, contains a list of instructions on how to restore your boot partition, should it be lost, if you used the dbserver_backup.sh script. If you used Vishal's method, then section 7 should give you some idea how to restore the boot partition based on the backup.

Lost Passwords

I've run into more than one case where the customer has lost the root password of some critical part of the Exadata infrastructure. One of the most common situations is the loss of the root password on the Infiniband switches. All I can say is that if you loose the root password to the infiniband switches and you need to access those switches (to install an update for example) then you are in for some significant time and trouble. The process of recovering the password is both painful and laborious. Experience has also shown that it's time consuming. So, please, don't loose your passwords.

Next time we will discuss the next two bullet points:

1. Node misconfigurations
2. Didn't RTFM

Applying Exadata Patches - The Biggest Gottyas

Trouble in Exadata City

If you have applied an Exadata patch or two, you know that there are a few gottyas that can lurk in a stab you in the back. I thought I'd provide my top-n list of Exadata Patching Gottyas in a hope that you won't suffer from them.

1. Didn't run/address Exacheck findings
2. Root FS full or nearly full
3. Didn't backup the compute node boot partition.
4. Lost passwords
5. Node misconfigurations
6. Didn't RTFM
7. Backups only to Exadata cell disks
8. ILOM not working
9. Don't know how to use ILOM
10. "This is the way we do things" itis. 
11. The patches are cumulative - Really?
12. Forgetting to patch GHomes.
13. Forgetting to patch all the OHomes.
14. Forgetting to relink Oracle properly (this is a major performance problem).
15. Opening SR's without  using your Oracle Exadata CSI.

In the next several posts I will address a few of these items in order. As I move along, I might add a few here if they come to me, or are suggested by others.

Note that in my stories, the names have been changed to protect the innocent. Nothing I say will have any relationship to Wikileaks at all, nor will I be revealing any government secrets. I'd tell you that the UFO's are real and that the aliens have landed, but then I'd have to kill you.

So, let's start with the first few:

Didn't run/address Exacheck findings 

I've done a few posts on Exacheck. It is, perhaps, one of the most underused tools that the DBA or Exadata Machine administrator, has available to them. Exacheck gets better and better and really should be part of your daily administrative reports in my opinion.

To not run an Exacheck report before you start patching (both in the planning stages and also just before the actual application) and *read* it (yes - there are those who run reports and then don't read them! - I KNOW!!) You should clearly understand the meaning of every item that is listed on that exacheck and why it's showing up on your system. You should document the reoccurring items that you already know are OK (but always check the details in case something small and significant has changed) and deal with those errors or warnings that are new and not on your "safe to ignore" list.

In my opinion, I will not start a patch set application until I'm happy that the Exacheck is clean. That does not mean a score of 100%, that means that I understand the reasons, clearly, for all of the errors and warnings and that  you could describe each of those reasons to me, so that I'd understand. If you can't verbalize the reasons for them (short of being mute) then you don't understand them in most cases.

I can't point my finger at any Exadata upgrades that I've been involved in where checking the Exacheck report would have solved a major problem that we ran into. I can point to more than one or two where checking the Exacheck report likely saved us a problem.

Root FS full or nearly full

I've actually run into this more than a few times. I'll find that on one of the compute nodes that the root file system is dangerously close to filling up. In fact, I just had a case where the root FS did fill up, which caused a system panic and rebooted the node.

Monitoring the root file system is, frankly, a basic production monitoring responsibility. If you have Oracle Platinum services and the Platinum gateway - understand that they are not monitoring the size of the root file system for you. They will notice when it fills up and the node crashes, but then it's a bit to late. You should configure monitoring for the space in file systems, including root, on any Oracle Database machine, including Exadata. OEM is the proper place to be doing this monitoring.

Often I find that the Root FS is filled with the following:

1. Files transfered into /tmp - typically Oracle Datapump dump files or sometimes files related to data feeds that were sftp to or from the Exadata Machine.
2. Core dump files that never got cleaned up.
3. Oracle related files such as accidentally creating output files in the root file system and the like.

You should regularly scour your / file system and clean it up. Also, note that Oracle provides a script that you can use to backup the boot partition (/dev/VGExaDb/LVDbSys1) to a backup partition (/dev/VGExaDb/LVDbSys2).

You should use that script anytime something major changes on the root file system (for example, you add a new OH). The script name is dbserver_backup.sh. You can find more information on this script in MOS note 1473002.1. In my mind, I'd also schedule a regular backup of the root file system to some non-Exadata media such as an attached ZFS storage device.



Next time we will cover another 2-3 items on my list. Stay tuned!

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.