RMAN Enhancements
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/PROD3/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
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.
Command Line Options:
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
Connected to target database: PROD3 (DBID=3132722606)
Assuming that some error has occured, 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 failures 142.
RMAN> list failure detail;
This command will show you the exact cause of the error.
Use Data Recovery Advisor for assistance:
RMAN>advice failure;
It responds with a detailed explanation of the error and how to correct it:
The 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 simply as well: restore and recover the file.(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 advices to move the file back to the original location and name (under Optional Manual Actions).
Issue the following command to "preview" the actions the repair task will execute:
RMAN> repair failure preview;
RMAN>repair failure;
Proactive Health Checks:
Bad blocks show themselves only when they are accessed so you want to identify them early and hopefully repair 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 containing 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 trival by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output.
RMAN>validate database;
We can also validate a specific tablespace:
RMAN>validate tablespace users;
We can also validate a specific datafile:
RMAN>validate datafile 1;
We can also validate even a block in a datafile:
RMAN>validate datafile 4 block 56;
The VALIDATE command extends much beyond datafiles however. We can validate spfile, controlfile copy, recovery files, Flash Recovery Area, and so on.
Parallel Backup of the Same Datafile:
We 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 though 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". We can specify the size of each section. Here's an example:
RMAN>run
{
allocate channel c1 type disk format '/backup1/%U';
allocate channel c2 type disk format '/backup2/%U';
backup
section size 500m
datafile 6;
}
This RMAN command allocates two channels and backs up the user's 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;
Now how the pieces of the backup show up as sections of the file. As each section goes to a different channel, we can define them as different mount points (such as /backup1 and /backup2), we 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 we section this file, the disk head has to move constantly to address different sections of the file, outweighing the benefits of sectioning.
Virtual Private Catalog:
We are most likely using a catalog database for the RMAN repository. There are several advantages, such as reporting, simpler recovery in case the controlfile is damaged, and so on.
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. The alternative is of course, we 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, we 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
RMAN> create catalog;
RMAN> register database;
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 the statement:
RMAN> grant catalog for database ode111 to ode111;
Now connect using the virtual catalog owner (ode111), and issue the statement create virtual catalog:
$ rman target=/ rcvcat ode111/ode111@catdb
RMAN> create virtual catalog;
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;
$ rman target=/ rcvcat prone3/prone3@catdb
RMAN> create virtual catalog;
Now, connecting as the base catalog owner (RMAN), if we want to see the database registered, we will see:
$ rman target=/ rcvcat=rman/rman@catdb
RMAN> list db_unique_name all;
As expected, it shows both the registered databases. Now, connect as ODE111 and issue the same command:
$ rman target=/ rcvcat ode111/ode111@catdb
RMAN> list db_unique_name all;
Note: Only one database was listed, not both. This user(ode111) is allowed to see only one database (ODE111), and that's what it sees. We 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;
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 Catalog:
let's consider another issue. Now that we've learned how to create virtual catalogs on the same base catalogs, we 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. We can, of course, sync the controlfiles and then sync back to the catalog, but that will initiate 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 we want to move the catalog from database CATDB1 to another database called CATDB2.
First, connect to the catalog database CATDB2 (the target):
$ rman target=/ rcvcat rman/rman@catdb2
If this database already has a catalog owned by the user, "RMAN", then go on to the next step of importing: otherwise, we will need to create the catalog:
RMAN>create catalog;
Now, import from the remote catalog (catdb1):
RMAN> import catalog rman/rman@catdb1;
There are several important information in the output. Note how the target database got de-registered from its original catalog database. Now if we check the database names in this new catalog:
RMAN> list db_unique_name all;
Note that the DB key has changed.
The above operations will import the catalogs of all target databases registered to the catalog database. Sometimes we may not want to import only one or two databases. Here is a command to do that:
RMAN> import catalog rman/rman@catdb3 db_name = ode111
If we want to keep the database registered in both catalog databases. We will need to use the "no unregister" clause:
RMAN> import catalog rman/rman@catdb1 db_name = ode111 no unregister;
This will make sure the databases ODE111 is not unregistered from catalog database catdb1 but rather registered in the new catalog.
Configuring the Backup Compression Algorithm:
ZLIB compression, which is very fast but has a compression ratio that is not as good as the ratio of other algorithms. BZIP2 has a very good compression ratio, but is slower than ZLIB. The default compression algorithm is BZIP2.
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
We can configure the compression algorithm with the following syntax
RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
Note that the COMPATIBLE initialization parameter must be set to 11.0.0 or higher for ZLIB compression.
Read-Only Transported Tablespaces Backup:
Alter a tablespace readonly, in the below list we can find logmnrts1.dbf of tablespace logmnrts1 as readonly
SQL> select name||' '||plugged_in||' '||enabled from v$datafile;
In this output we can find the datafile logmnrts1.dbf readonly. In Oracle 11g we can take the backup of the readonly tablespace normally.
RMAN> backup tablespace logmnrts1;
Duplicate Command:
A clone database on a remote site can now be easily created directly over the network with the enhanced DUPLICATE command without existing backups. ASM-to-ASM DUPLICATE over the network is also supported. This feature eliminates the need to copy or move backups to the remote site before executing the DUPLICATE command. This reduces DBA time and effort, and eliminates storage for the additional copy at the remote site.
RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
To duplicate a database to a remote host with the same directory structure:
- Preparing the Auxiliary Instance. (the initialization parameter file contains only DB_NAME set to an arbitrary value.)
- Configuring RMAN Before Duplication the database is open, RMAN has automatic channels already configured. We connect to the database instances as follows
CONNECT TARGET SYS/password@prod
CONNECT AUXILIARY SYS/password@dupdb
- Execute the DUPLICATE command.
- Use DUPLICATE for active duplication, this requires the NOFILENAMECHECK option because the source database files have the same names as the duplicate database files.
- Duplicating to a Host with the Same Directory Structure
DUPLICATE TARGET DATABASE
TO dupdb
FROM ACTIVE DATABASE
SPFILE
NOFILENAMECHECK;
RMAN automatically copies the server parameter file to the destination host, starts the auxiliary instance with the server parameter file, copies all necessary database files and archives redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with the RESETLOGS option to create the online redo logs.
*************************************************************************
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/PROD3/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
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.
Command Line Options:
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
Connected to target database: PROD3 (DBID=3132722606)
Assuming that some error has occured, 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 failures 142.
RMAN> list failure detail;
This command will show you the exact cause of the error.
Use Data Recovery Advisor for assistance:
RMAN>advice failure;
It responds with a detailed explanation of the error and how to correct it:
The 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 simply as well: restore and recover the file.(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 advices to move the file back to the original location and name (under Optional Manual Actions).
Issue the following command to "preview" the actions the repair task will execute:
RMAN> repair failure preview;
RMAN>repair failure;
Proactive Health Checks:
Bad blocks show themselves only when they are accessed so you want to identify them early and hopefully repair 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 containing 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 trival by checking database blocks for physical corruption. If corruption is detected, it logs into the Automatic Diagnostic Repository. RMAN then produces an output.
RMAN>validate database;
We can also validate a specific tablespace:
RMAN>validate tablespace users;
We can also validate a specific datafile:
RMAN>validate datafile 1;
We can also validate even a block in a datafile:
RMAN>validate datafile 4 block 56;
The VALIDATE command extends much beyond datafiles however. We can validate spfile, controlfile copy, recovery files, Flash Recovery Area, and so on.
Parallel Backup of the Same Datafile:
We 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 though 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". We can specify the size of each section. Here's an example:
RMAN>run
{
allocate channel c1 type disk format '/backup1/%U';
allocate channel c2 type disk format '/backup2/%U';
backup
section size 500m
datafile 6;
}
This RMAN command allocates two channels and backs up the user's 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;
Now how the pieces of the backup show up as sections of the file. As each section goes to a different channel, we can define them as different mount points (such as /backup1 and /backup2), we 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 we section this file, the disk head has to move constantly to address different sections of the file, outweighing the benefits of sectioning.
Virtual Private Catalog:
We are most likely using a catalog database for the RMAN repository. There are several advantages, such as reporting, simpler recovery in case the controlfile is damaged, and so on.
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. The alternative is of course, we 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, we 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
RMAN> create catalog;
RMAN> register database;
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 the statement:
RMAN> grant catalog for database ode111 to ode111;
Now connect using the virtual catalog owner (ode111), and issue the statement create virtual catalog:
$ rman target=/ rcvcat ode111/ode111@catdb
RMAN> create virtual catalog;
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;
$ rman target=/ rcvcat prone3/prone3@catdb
RMAN> create virtual catalog;
Now, connecting as the base catalog owner (RMAN), if we want to see the database registered, we will see:
$ rman target=/ rcvcat=rman/rman@catdb
RMAN> list db_unique_name all;
As expected, it shows both the registered databases. Now, connect as ODE111 and issue the same command:
$ rman target=/ rcvcat ode111/ode111@catdb
RMAN> list db_unique_name all;
Note: Only one database was listed, not both. This user(ode111) is allowed to see only one database (ODE111), and that's what it sees. We 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;
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 Catalog:
let's consider another issue. Now that we've learned how to create virtual catalogs on the same base catalogs, we 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. We can, of course, sync the controlfiles and then sync back to the catalog, but that will initiate 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 we want to move the catalog from database CATDB1 to another database called CATDB2.
First, connect to the catalog database CATDB2 (the target):
$ rman target=/ rcvcat rman/rman@catdb2
If this database already has a catalog owned by the user, "RMAN", then go on to the next step of importing: otherwise, we will need to create the catalog:
RMAN>create catalog;
Now, import from the remote catalog (catdb1):
RMAN> import catalog rman/rman@catdb1;
There are several important information in the output. Note how the target database got de-registered from its original catalog database. Now if we check the database names in this new catalog:
RMAN> list db_unique_name all;
Note that the DB key has changed.
The above operations will import the catalogs of all target databases registered to the catalog database. Sometimes we may not want to import only one or two databases. Here is a command to do that:
RMAN> import catalog rman/rman@catdb3 db_name = ode111
If we want to keep the database registered in both catalog databases. We will need to use the "no unregister" clause:
RMAN> import catalog rman/rman@catdb1 db_name = ode111 no unregister;
This will make sure the databases ODE111 is not unregistered from catalog database catdb1 but rather registered in the new catalog.
Configuring the Backup Compression Algorithm:
ZLIB compression, which is very fast but has a compression ratio that is not as good as the ratio of other algorithms. BZIP2 has a very good compression ratio, but is slower than ZLIB. The default compression algorithm is BZIP2.
RMAN> CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
We can configure the compression algorithm with the following syntax
RMAN> CONFIGURE COMPRESSION ALGORITHM 'ZLIB';
Note that the COMPATIBLE initialization parameter must be set to 11.0.0 or higher for ZLIB compression.
Read-Only Transported Tablespaces Backup:
Alter a tablespace readonly, in the below list we can find logmnrts1.dbf of tablespace logmnrts1 as readonly
SQL> select name||' '||plugged_in||' '||enabled from v$datafile;
In this output we can find the datafile logmnrts1.dbf readonly. In Oracle 11g we can take the backup of the readonly tablespace normally.
RMAN> backup tablespace logmnrts1;
Duplicate Command:
A clone database on a remote site can now be easily created directly over the network with the enhanced DUPLICATE command without existing backups. ASM-to-ASM DUPLICATE over the network is also supported. This feature eliminates the need to copy or move backups to the remote site before executing the DUPLICATE command. This reduces DBA time and effort, and eliminates storage for the additional copy at the remote site.
RMAN> DUPLICATE TARGET DATABASE TO dupdb FROM ACTIVE DATABASE SPFILE NOFILENAMECHECK;
To duplicate a database to a remote host with the same directory structure:
- Preparing the Auxiliary Instance. (the initialization parameter file contains only DB_NAME set to an arbitrary value.)
- Configuring RMAN Before Duplication the database is open, RMAN has automatic channels already configured. We connect to the database instances as follows
CONNECT TARGET SYS/password@prod
CONNECT AUXILIARY SYS/password@dupdb
- Execute the DUPLICATE command.
- Use DUPLICATE for active duplication, this requires the NOFILENAMECHECK option because the source database files have the same names as the duplicate database files.
- Duplicating to a Host with the Same Directory Structure
DUPLICATE TARGET DATABASE
TO dupdb
FROM ACTIVE DATABASE
SPFILE
NOFILENAMECHECK;
RMAN automatically copies the server parameter file to the destination host, starts the auxiliary instance with the server parameter file, copies all necessary database files and archives redo logs over the network to the destination host, and recovers the database. Finally, RMAN opens the database with the RESETLOGS option to create the online redo logs.
*************************************************************************
No comments:
Post a Comment