RSS

Oracle Database 11g: The Top New Features for DBAs and Developers

10 Jul

RMAN

Advice on data recovery, parallel backup of the same file, and virtual catalogs for security are just a few of the new gems available from RMAN in Oracle Database 11g.

Download Download Oracle Database 11g

Data Recovery Advisor

Consider the error shown below:

SQL> conn scott/tiger
Connected.
SQL> create table t (col1 number);
create table t (col1 number)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/oradata/PRODB3/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Does it look familiar? Regardless of your experience as a DBA, you probably have seen this message more than once. This error occurs because the datafile in question is not available—it could be corrupt or perhaps someone removed the file while the database was running. In any case, you need to take some proactive action before the problem has a more widespread impact.

In Oracle Database 11g, the new Data Recovery Advisor makes this operation much easier. The advisor comes in two flavors: command line mode and as a screen in Oracle Enterprise Manager Database Control. Each flavor has its advantages for a given specific situation. For instance, the former option comes in handy when you want to automate the identification of such files via shell scripting and schedule recovery through a utility such as cron or at. The latter route is helpful for novice DBAs who might want the assurance of a GUI that guides them through the process. I’ll describe both here.

Command Line Option

The command line option is executed through RMAN. First, start the RMAN process and connect to the target.

 
$ rman target=/

Recovery Manager: Release 11.1.0.5.0 - Beta on Sun Jul 15 19:43:45 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: PRODB3 (DBID=3132722606)

Assuming that some error has occurred, you want to find out what happened. The list failure command tells you that in a jiffy.

RMAN> list failure;

If there is no error, this command will come back with the message:

no failures found that match specification

If there is an error, a more explanatory message will follow:

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      15-JUL-07     One or more non-system datafiles are missing

This message shows that some datafiles are missing. As the datafiles belong to a tablespace other than SYSTEM, the database stays up with that tablespace being offline. This error is fairly critical, so the priority is set to HIGH. Each failure gets a Failure ID, which makes it easier to identify and address individual failures. For instance you can issue the following command to get the details of Failure 142.

RMAN> list failure 142 detail;

This command will show you the exact cause of the error.

Now comes the fun part: How do you rectify the error? Seasoned DBAs will probably ace this without further help but novice DBAs (and even experienced but tired ones) will welcome some guidance here. They can turn to Data Recovery Advisor for assistance:

RMAN> advise failure;

It responds with a detailed explanation of the error and how to correct it:

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
142        HIGH     OPEN      15-JUL-07     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /home/oracle/oradata/PRODB3/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm

This output has several important parts. First, the advisor analyzes the error. In this case, it’s pretty obvious: the datafile is missing. Next, it suggests a strategy. In this case, this is fairly simple as well: restore and recover the file. (Please note that I have deliberately chosen a simple example to focus the attention on the usage of the tool, not to discuss the many cases where the database could fail and how they can be recovered. The dynamic performance view V$IR_MANUAL_CHECKLIST also shows this information.)

However, the most useful task Data Recovery Advisor does is shown in the very last line: it generates a script that can be used to repair the datafile or resolve the issue. The script does all the work; you don’t have to write a single line of code.

Sometimes the advisor doesn’t have all the information it needs. For instance, in this case, it does not know if someone moved the file to a different location or renamed it. In that case, it advises to move the file back to the original location and name (under Optional Manual Actions).

OK, so the script is prepared for you. Are you ready to execute it? I don’t know about you, but I would verify what the script actually does first. So, I issue the following command to “preview” the actions the repair task will execute:

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_741461097.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

This is good; the repair seems to be doing the same thing I would have done myself using RMAN. Now I can execute the actual repair by issuing:

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/diag/rdbms/prodb3/PRODB3/hm/reco_3162589478.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)?

Assuming I’m OK, I answer YES and the action goes on:

executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 15-JUL-07
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=5 STAMP=628025835 file name=/home/oracle/flasharea
    /PRODB3/datafile/o1_mf_users_39ocxbv3_.dbf
destination for restore of datafile 00004: /home/oracle/oradata/PRODB3/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/home/oracle/oradata/PRODB3/users01.dbf RECID=0 STAMP=0
Finished restore at 15-JUL-07

Starting recover at 15-JUL-07
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 51 is already on disk as file /home/oracle/
   flasharea/PRODB3/archivelog/2007_07_15/o1_mf_1_51_39ocxxdw_.arc
... and so on ...
name=/home/oracle/flasharea/PRODB3/archivelog/2007_07_15/o1_mf_1_55_39ocy9ox_.arc thread=1 sequence=55
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-JUL-07

sql statement: alter database datafile 4 online
repair failure complete

RMAN>

Note how RMAN prompts you before attempting to repair. In a scripting case, you may not want to do that; rather, you would want to just go ahead and repair it without an additional prompt. In such a case, just use repair failure noprompt at the RMAN prompt.

Proactive Health Checks

It helps you sleep better at night knowing that the database is healthy and has no bad blocks. But how can you ensure that? Bad blocks show themselves only when they are accessed so you want to identify them early and hopefully repair them using simple commands before the users get an error.

The tool dbverify can do the job but it might be a little inconvenient to use because it requires writing a script file contaning all datafiles and a lot of parameters. The output also needs scanning and interpretation. In Oracle Database 11g, a new command in RMAN, VALIDATE DATABASE, makes this operation trivial by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output that is partially shown below:

RMAN> validate database;

Starting validate at 09-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=110 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/home/oracle/oradata/ODEL11/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/oradata/ODEL11/system01.dbf
input datafile file number=00003 name=/home/oracle/oradata/ODEL11/undotbs01.dbf
input datafile file number=00004 name=/home/oracle/oradata/ODEL11/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:02:18
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12852        94720           5420717   
  File Name: /home/oracle/oradata/ODEL11/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              65435           
  Index      0              11898           
  Other      0              4535            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              30753        115848          5420730   
  File Name: /home/oracle/oradata/ODEL11/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              28042           
  Index      0              26924           
  Other      0              30129           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              5368         25600           5420730   
  File Name: /home/oracle/oradata/ODEL11/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              20232           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              2569         12256           4910970   

... <snipped> ...

Otherwise, in case of a failure you will see on parts of the above output:

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              0            128             5556154   
  File Name: /home/oracle/oradata/ODEL11/test01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              108             
  Index      0              0               
  Other      10             20

You can also validate a specific tablespace:

RMAN> validate tablespace users;

Or, datafile:

RMAN> validate datafile 1;

Or, even a block in a datafile:

RMAN> validate datafile 4 block 56;

The VALIDATE command extends much beyond datafiles however. You can validate spfile, controlfilecopy, recovery files, Flash Recovery Area, and so on.

Enterprise Manager Interface

Let’s see how failures can also be detected and corrected via Enterprise Manager.

First go to the Database homepage; top part is shown below.

Figure 1

Scroll down further until you see the Alerts section, as shown below:

This window shows the actual RMAN command to be issued. At this time you can press Submit to execute the RMAN job. Note the contents of the window: It’s a real RMAN command, which you can copy and paste into an RMAN prompt.

The Enterprise Manager interface to RMAN provides the best of both worlds: the power of RMAN without the complexities of its command language. Advanced users of RMAN may not find it that useful, but for a novice, it’s a lifesaver—especially when considering how a relatively complicated block media recovery was done using a simple interface.

Flashback Logs to Rescue

Remember Flashback Logging introduced in Oracle Database 10g? It records the optimized versions of the before-images of changed blocks into flashback logs that are generated in the Flashback Recovery Area, provided Flashback has been enabled in the database. These logs help you to flash the database back to a point in the time in the past without doing a point-in-time recovery from backups.

Well, since these flashback logs contain past images of the blocks, why not use them for recovery as well? Oracle Database 11g does exactly that. When you recover the specific block (or blocks), Oracle looks in the Flashback logs (instead of datafiles) to find a good copy of the past image of that block and then applies the archived logs to roll it forward. This technique saves a lot of time by not going to the backups, especially if the backup is on tape.

ZLIB Compression

RMAN offered compression of backup pieces in Oracle Database 10g to conserve network bandwidth but many people were slow to use it. Why? Because third-party compression utilities provided faster alternatives to RMAN’s own. Nevertheless, RMAN 10g compression has some neat features that third-party ones do not provide. For instance, when RMAN 10g restores datafiles, it doesn’t need to uncompress the files first, provided it performed the compression. This approach offers significant bandwidth savings during restores.

In Oracle Database 11g, RMAN offers another algorithm, ZLIB, in addition to the previously available BZIP2. ZLIB is a much faster algorithm but it does not compress a whole lot. On the other hand, it does not consume much CPU either. So if you are CPU starved, it’s a blessing to have ZLIB compression. (Note that BZIP2 is default in version 11.1; you need to license a new option called Advanced Compression Option to use ZLIB.)

To use ZLIB compression, merely set the RMAN configuration parameter:

RMAN> configure compression algorithm 'ZLIB' ;

You have to issue the above command if you have changed it earlier. To change it to BZIP2, issue:

RMAN> configure compression algorithm 'bzip2';

All the compressed backups will use the new algorithm now.

Parallel Backup of the Same Datafile

You probably already know that you can parallelize the backup by declaring more than one channel so that each channel becomes a RMAN session. However, very few realize that each channel can back up only one datafile at a time. So even through there are several channels, each datafile is backed by only one channel, somewhat contrary to the perception that the backup is truly parallel.

In Oracle Database 11g RMAN, the channels can break the datafiles into chunks known as “sections.” You can specify the size of each section. Here’s an example:

RMAN> run {
2>      allocate channel c1 type disk format '/backup1/%U';
3>      allocate channel c2 type disk format '/backup2/%U';
4>      backup 
5>      section size 500m 
6>      datafile 6;
7> }

This RMAN command allocates two channels and backs up the users’ tablespace in parallel on two channels. Each channel takes a 500MB section of the datafile and backs it up in parallel. This makes backup of large files faster.

When backed up this way, the backups show up as sections as well.

RMAN> list backup of datafile 6;
... 
<snipped>
... 
    List of Backup Pieces for backup set 901 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2007    1   AVAILABLE   /backup1/9dhk7os1_1_1
    2008    2   AVAILABLE   /backup2/9dhk7os1_1_1
    2009    3   AVAILABLE   /backup1/9dhk7os1_1_3
    2009    3   AVAILABLE   /backup2/9dhk7os1_1_4

Note how the pieces of the backup show up as sections of the file. As each section goes to a different channel, you can define them as different mount points (such as /backup1 and /backup2), you can back them to tape in parallel as well.

However, if the large file #6 resides on only one disk, there is no advantage to using parallel backups. If you section this file, the disk head has to move constantly to address different sections of the file, outweighing the benefits of sectioning.

Backup Committed Undo? Why?

You already know what undo data is used for. When a transaction changes a block, the past image of the block is kept it the undo segments. The data is kept there even if the transaction is committed because some long running query that started before the block is changed can ask for the block that was changed and committed. This query should get the past image of the block—the pre-commit image, not the current one. Therefore undo data is kept undo segments even after the commit. The data is flushed out of the undo segment in course of time, to make room for the newly inserted undo data.

When the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed, since they are already in the redo log streams, or even in the datafiles (provided the dirty blocks have been cleaned out from buffer and written to the disk) and can be recovered from there. So, why bother backing up the committed undo data?

In Oracle Database 11g, RMAN does the smart thing: it bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup (and the recovery as well).

In many databases, especially OLTP ones where the transaction are committed more frequently and the undo data stays longer in the undo segments, most of the undo data is actually committed. Thus RMAN has to backup only a few blocks from the undo tablespaces.

The best part is that you needn’t do anything to achieve this optimization; Oracle does it by itself.

Virtual Private Catalog

You are most likely using a catalog database for the RMAN repository. If you are not, you should seriously consider using one. There are several advantages, such as reporting, simpler recovery in case the controlfile is damaged, and so on.

Now comes the next question: How many catalogs? Generally, it makes sense to have only one catalog database as the repository for all databases. However, that might not be a good approach for security. A catalog owner will be able to see all the repositories of all databases. Since each database to be backed up may have a separate DBA, making the catalog visible may not be acceptable.

So, what’s the alternative? Of course, you could create a separate catalog database for each target database, which is probably impractical due to cost considerations. The other option is to create only one database for catalog yet create a virtual catalog for each target database. Virtual catalogs are new in Oracle Database 11g. Let’s see how to create them.

First, you need to create a base catalog that contains all the target databases. The owner is, say, “RMAN”. From the target database, connect to the catalog database as the base user and create the catalog.

$ rman target=/ rcvcat rman/rman@catdb Recovery Manager: Release 11.1.0.6.0 – Production on Sun Sep 9 21:04:14 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ODEL11 (DBID=2836429497) connected to recovery catalog database RMAN> create catalog; recovery catalog created RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete

This is called the base catalog, owned by the user named “RMAN”. Now, let’s create two additional users who will own the respective virtual catalogs. For simplicity, let’s gives these users the same name as the target database. While still connected as the base catalog owner (RMAN), issue these statement:

RMAN> grant catalog for database odel11 to odel11;

Grant succeeded.

Now connect using the virtual catalog owner (odel11), and issue the statement create virtual catalog

:

$ rman target=/ rcvcat odel11/odel11@catdb

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

Now, register a different database (PRONE3) to the same RMAN repository and create a virtual catalog owner “prone3” for its namesake database.

RMAN> grant catalog for database prone3 to prone3;

Grant succeeded.

$ rman target=/ rcvcat prone3/prone3@catdb

RMAN> create virtual catalog;

found eligible base catalog owned by RMAN
created virtual catalog against base catalog owned by RMAN

Now, connecting as the base catalog owner (RMAN), if you want to see the databases registered, you will see:

$ rman target=/ rcvcat=rman/rman@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
285     PRONE3   1596130080       PRIMARY          PRONE3              
1       ODEL11   2836429497       PRIMARY          ODEL11

As expected, it showed both the registered databases. Now, connect as ODEL11 and issue the same command:

$ rman target=/ rcvcat odel11/odel11@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
1       ODEL11   2836429497       PRIMARY          ODEL11

Note how only one database was listed, not both. This user (odel11) is allowed to see only one database (ODEL11), and that’s what it sees. You can confirm this by connecting to the catalog as the other owner, PRONE3:

$ rman target=/ rcvcat prone3/prone3@catdb

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
285     PRONE3   1596130080       PRIMARY          PRONE3

Virtual catalogs allow you to maintain only one database for the RMAN repository catalog yet establish secure boundaries for individual database owners to manage their own virtual repositories. A common catalog database makes administration simpler, reduces costs, and enables the database to be highly available, again, at less cost.

Merging Catalogs

While on the subject of multiple catalogs, let’s consider another issue. Now that you’ve learned how to create virtual catalogs on the same base catalogs, you may see the need to consolidate all these independent repositories into a single one.

One option is to deregister the target databases from their respective catalogs and re-register them to this new central catalog. However, doing so also means losing all those valuable information stored in those repositories. You can, of course, sync the controlfiles and then sync back to the catalog, but that will inflate the controlfile and be impractical.

Oracle Database 11g offers a new feature: merging the catalogs. Actually, it’s importing a catalog from one database to another, or in other words, “moving” catalogs.

Let’s see how it is done. Suppose you want to move the catalog from database CATDB1 to another database called CATDB2. First, you connect to the catalog database CATDB2 (the target):

$ rman target=/ rcvcat rman/rman@catdb2

Recovery Manager: Release 11.1.0.6.0 - Production on Sun Sep 9 23:12:07 2007

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ODEL11 (DBID=2836429497)
connected to recovery catalog database

If this database already has a catalog owned by the user “RMAN”, then go on to the next step of importing; otherwise, you will need to create the catalog:

RMAN> create catalog;

recovery catalog created

Now, you import from the remote catalog (catdb1):

RMAN> import catalog rman/rman@catdb1;

Starting import catalog at 09-SEP-07
connected to source recovery catalog database
import validation complete
database unregistered from the source recovery catalog
Finished import catalog at 09-SEP-07
starting full resync of recovery catalog
full resync complete

There are several important information in the above output. Note how the target database got de-registered from its original catalog database. Now if you check the database names in this new catalog:

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
286     PRONE3   1596130080       PRIMARY          PRONE3              
2       ODEL11   2836429497       PRIMARY          ODEL11

You will notice that the DB Key has changed. ODEL11 was 1 earlier; it’s 2 now.

The above operations will import the catalogs of all target databases registered to the catalog database. Sometimes you may not want that—rather, you may want to import only one or two databases. Here is a command to do that:

RMAN> import catalog rman/rman@catdb3 db_name = odel11;

Doing so changes the DB Key again.

What if you don’t want to deregister the imported database from the source database during import? In other words, you want to keep the database registered in both catalog databases. You will need to use the “no unregister” clause:

RMAN> import catalog rman/rman@catdb1 db_name = odel11 no unregister;

This will make sure the database ODEL11 is not unregistered from catalog database catdb1 but rather registered in the new catalog.

Back to “Oracle Database 11g: Top Features for DBAs and Developers” homepage

Advertisements
 
Leave a comment

Posted by on July 10, 2009 in Database Administration

 

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: