Monday, August 30, 2010

Curious RMAN-05001 in database duplicate

RMAN duplicate is one of the coolest Oracle features. Idea is to clone database in a most easiest way, allowing DBAs to create database replica in any possible way in shortest possible time.

Regardless scenario, new directory structure in auxiliary database (replica database) is totally independent from target database (one that is use as a source for cloning). For that you have two options which allows DBA redirection of datafile's location:
DB_FILE_NAME_CONVERT
NOFILENAMECHECK
First one, DB_FILE_NAME_CONVERT is like mapper for directory structures, while second NOFILENAMECHECK allows to reuse the same target locations for the auxiliary (without any mapping).

Do not use NOFILENAMECHECK when target and destination database are on the same box because datafiles from target database will be overwritten!

The problem

So according mentioned in praxis there are 3 possible scenarios according new datfiles location (in our example let db_name remains the same):
  1. Same: Directory structure are identical.
    Target database Auxiliary database
    C:\oradata\hcpro = C:\oradata\hcpro
    D:\oradata\hcpro = D:\oradata\hcpro
    E:\oradata\hcpro = E:\oradata\hcpro
    F:\oradata\hcpro = F:\oradata\hcpro
    This is in case of cloning to different box only.

  2. Totally different: Directory structure differs in all locationn.
    Target database Auxiliary database
    C:\oradata\hcpro --> C:\neworadata\hcpro
    D:\oradata\hcpro --> D:\neworadata\hcpro
    E:\oradata\hcpro
    F:\oradata\hcpro --> E:\neworadata\hcpro
    This may be in both cases. Same or different box!

  3. Mixed: Some locations are same and some are different.
    Target database Auxiliary database
    C:\oradata\hcpro = C:\oradata\hcpro
    D:\oradata\hcpro --> D:\oradata\hcpro
    F:\oradata\hcpro
    E:\oradata\hcpro --> E:\oradata\hcpro
    G:\oradata\hcpro
    This is in case of cloning to different box only!
let me show real RMAN DUPLICATE commands for all three cases. In examples auxiliary database has the same name (hcpro) and all needed changes are done through RMAN (not through init.ora parameter).

1. Same

Tipical example for RMAN DUPLICATE command would be:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       NOFILENAMECHECK
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
As you can see NOFILENAMECHECK option ensure that all is automatic-same as on target database.

2. Totally different

RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('C:\ORADATA\HCPRO\' , 'C:\NEWORADATA\HCPRO\'
                              'D:\ORADATA\HCPRO\' , 'D:\NEWORADATA\HCPRO\'
                              'F:\ORADATA\HCPRO\' , 'D:\NEWORADATA\HCPRO\'
                              'E:\ORADATA\HCPRO\' , 'E:\NEWORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\NEWORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\NEWORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\NEWORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
} 
As you can see DB_FILE_NAME_CONVERT option make mappings from target to auxiliary locations. Image this option like "global" replace path string function which Oracle do on the fly.

3. Mixed

And now we come to situation which is (at least for me) curious. Because it is based for duplicate on different box, many users think that this is valid command:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
                              'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
Directories that are not mentioned should remain as they are! But this gives mentioned RMAN-05001 error:
allocated channel: aux1
channel aux1: sid=4 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/27/2010 07:13:20
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\DWH_NDX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES02.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSAUX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\UNDOTBS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSTEM01.DBF conflicts with a file used by the target database

RMAN>

When you change command in a way:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       DB_FILE_NAME_CONVERT =('C:\oradata\hcpro\' , 'C:\oradata\hcpro\'
                              'D:\oradata\hcpro\' , 'D:\oradata\hcpro\'
                              'F:\oradata\hcpro\' , 'D:\oradata\hcpro\'
                              'E:\oradata\hcpro\' , 'E:\oradata\hcpro\'
                              'G:\oradata\hcpro\' , 'E:\oradata\hcpro\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
that produce the same error:
allocated channel: aux1
channel aux1: sid=3 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10
released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/27/2010 15:32:31
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\DWH_NDX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES02.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename E:\ORADATA\HCPRO\INDEXES01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSAUX01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\UNDOTBS01.DBF conflicts with a file used by the target database
RMAN-05001: auxiliary filename D:\ORADATA\HCPRO\SYSTEM01.DBF conflicts with a file used by the target database

RMAN>
<>

The solution

After some investigation I come to the solution which is composed of both options in RMAN command: NOFILENAMECHECK and DB_FILE_NAME_CONVERT, regardless this looks funny!
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
     DUPLICATE TARGET DATABASE TO HCPRO
       NOFILENAMECHECK
       DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
                              'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
                             )
       PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
       LOGFILE
         GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
         GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
         GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
     ;
     RELEASE CHANNEL aux1;
}
The result comes OK:
allocated channel: aux1
channel aux1: sid=3 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 27-AUG-10

contents of Memory Script:
{
   set until scn  14251842631;
   set newname for datafile  1 to
 "D:\ORADATA\HCPRO\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORADATA\HCPRO\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "D:\ORADATA\HCPRO\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\ORADATA\HCPRO\GIS01.DBF";
   set newname for datafile  5 to
 "E:\ORADATA\HCPRO\INDEXES01.DBF";
   set newname for datafile  6 to
 "D:\ORADATA\HCPRO\USERS01.DBF";
   set newname for datafile  7 to
 "E:\ORADATA\HCPRO\USERS_2_01.DBF";
   set newname for datafile  8 to
 "E:\ORADATA\HCPRO\USERS_2_O2.DBF";
   set newname for datafile  9 to
 "E:\ORADATA\HCPRO\TOOLS01.DBF";
   set newname for datafile  10 to
 "D:\ORADATA\HCPRO\USERS02.DBF";
   set newname for datafile  11 to
 "E:\ORADATA\HCPRO\INDEXES02.DBF";
   set newname for datafile  12 to
 "E:\ORADATA\HCPRO\DWH_DATA01.DBF";
   set newname for datafile  13 to
 "E:\ORADATA\HCPRO\DWH_NDX01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 27-AUG-10

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\HCPRO\SYSTEM01.DBF
restoring datafile 00003 to D:\ORADATA\HCPRO\SYSAUX01.DBF
restoring datafile 00005 to E:\ORADATA\HCPRO\INDEXES01.DBF
restoring datafile 00006 to D:\ORADATA\HCPRO\USERS01.DBF
restoring datafile 00008 to E:\ORADATA\HCPRO\USERS_2_O2.DBF
restoring datafile 00010 to D:\ORADATA\HCPRO\USERS02.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6435_S1
...

The End

My testing environment was:
  • Windows 2008 x64
  • Oracle Enterprise 10.2.0.4 x64

Hope this will help someone in the future with similar problems.

Cheers!

Wednesday, August 25, 2010

What new 11g features are DBAs implementing?


For anybody who is reading through any discussion who has not upgraded to 11g yet and is looking for some clear and concise information, resources and demo's for Oracle Database 11g Options and features, this is not a bad place to start your learning.

Free and easy to understand!

For the end one amazing picture that I find very useful for this subject:

Cheers!

Sunday, August 22, 2010

SQL*Plus 11.2 SET System Variable Summary


Regardless many of us do really know a lot about SQL*Plus and it's system variables, you may be surprised how many new enhancements are in latest version....

So this short topic is more to have handy reference on the WEB then some expected Oracle topic.

Hope you'd like it like I do ... especially when knowing that this is really new subject...

At least enjoy in Chuck Norris teachers theme....

Cheers!

Thursday, August 12, 2010

Patching and Upgrading (Oracle Clusterware 11gR2)

As with other database software Oracle Clusterware 11g Release 2 will require occasional patches and upgrades. Patches are interim releases between major versions of the Oracle Clusterware product. For example, moving from 11.2.0.1 to 11.2.0.2 would be considered a patch. Upgrades occur when you are moving between major versions of the Oracle Clusterware product. An example of an upgrade would be moving from Oracle Clusterware version 11.1 to 11.2. In this section we will first look at patching of Oracle Clusterware 11g Release 2 and then we will look at upgrading.

Patching Oracle Clusterware 11g Release 2

If you have patches to apply to Oracle Clusterware and Oracle RAC, you should always apply the Clusterware patches first, followed by the patches to the Oracle Database. You can choose to patch Oracle Clusterware and not the Oracle Database but the reverse is not true. If you wish to patch the Oracle Database you must patch the Oracle Clusterware first. Thus the Oracle Clusterware version number must always be higher or the same as the patch set level as the Oracle Database.

So it more then normal to have 11gR2 Clusterware for Oracle 10g database!

There are three basic kinds of Oracle patches. These include:
  • CRS Merge Label Request (MLR) patches, also known as One-Off patches
  • CRS Bundle Patches (BP) and PSU Patches
  • Critical Patch Updates (CPU)
  • Patch Set Updates (PSU)
Let’s look at each of these in more detail next. We will then look at how to search for existing patch sets on the Oracle Support Website.

CRS Merge Label Request (MLR) Patches

When you experience bugs in your database that require immediate correction, you should contact Oracle support and open a support request (SR). After opening the SR the Oracle support representative may determine that your problem is an existing bug, and may identify an existing MLR patch for that bug. Sometimes the MLR patch will not be available for your operating system platform, in which case you can ask that Oracle support request Oracle development port the patch to the operating system that you are using.

Other times you may have found a bug that is new. As a result Oracle will open a support request and will request that development review the bug. As a result of this review by development they may produce a patch for you to apply and this would also be considered and MLR patch. It may also be that Oracle will not be able to create an MLR patch and that they will indicate that you will need to wait for the next patch set (see CRS bundle patches and CRS patch sets later in this section).

It is important to note that MLR patches are not regression tested. Therefore it’s important that you carefully test your database and make sure that it operates normally after the application of a patch. MLR patches are generally installed using the Oracle opatch utility. You should always read the associated patch set install instructions before you attempt to install the patch set.

One off patches are installed in-place. This means that the patch is applied to the existing GRID_HOME or ORACLE_HOME. Also in many cases one-off patches can be applied in a rolling fashion. This means that the patch is applied to each node, one at a time. This results in limited service interruptions to your users. Check the patch install instructions to determine if it can be installed using a rolling upgrade method.

CRS Bundle Patches (BP) and Patch Set Updates (PSU’s)

A CRS Bundle Patch Set is a fully regression tested patch set. It typically contains a number of MLR patches and other patches that Oracle things are important to include in the BP. BP’s are installed with the opatch utility.

BP patches can be installed in-place or out of place. As with One-off patches in place installs means that the patch is applied to the existing GRID_HOME or ORACLE_HOME. An out-of-place install means that the patch is applied to a new GRID_HOME or ORACLE_HOME rather than the existing home, and that you swing over the cluster to using that new home location to start using the newly applied patches.

Many BP patches can be applied in a rolling fashion. This means that the patch is applied to each node, one at a time. This results in limited service interruptions to your users. Check the patch install instructions to determine if it can be installed using a rolling upgrade method.

CRS (clusterware) BP’s are now released on a regularly scheduled basis in January, April, July and October and are called Patch Set Updates (PSU). PSU patch sets contain recommended bug fixes from Oracle plus the security patches contained in the CPU patches (see more about CPU patches later in this section). PSU’s are numbered using the fifth minor number of the database version. For example, if you have Oracle version 11.2.0.1.0 installed, the next PSU you would install would be 11.2.0.1.1.

PSU’s are intended to be low risk patch sets. They contain fixes that fix critical technical issues, that impact a large number of Oracle customers and have already been proven effective in the field. Since they also include CPU updates, PSU’s also address current security issues. PSU’s do not contain any patches that would require re-certification or that would require configuration changes.

There are a number of different PSU’s including Grid Infrastructure PSU’s and Oracle Database PSU’s. The GI PSU’s contain all the Database PSU’s and therefore you need only run the GI PSU’s if you are running Oracle RAC. If you are not running a clustered environment then you would only need to install the database PSU’s. As always consult MOS and the readme documents of the patch sets to ensure that you are properly installing the patch.

Critical Patch Updates (CPU)

CPU patch sets are patch sets that are targeted towards security fixes associated with Oracle Database products. CPU’s are released four times a year in the middle part of January, April, July and October and they are cumulative in nature. CPU patch sets are designed to only address security issues and no other bug fixes are intended to be included in CPU patch sets with the exception to any fixes that might conflict with the related security fixes. You should see no functionality changes with a CPU patch set.
!!!Searching For Existing Patch Sets
Oracle provides an easy way to search for existing patch sets. There are three methods that you can use. These methods are:
  1. Finding the patch on Oracle MOS, download and install.
  2. Using Oracle Enterprise Manager.
  3. If you are applying a CPU, use the www.oracle.com website.

Manually Searching for Patches

If you have access to the My Oracle Support (MOS) support page you can search for recommended patches for various Oracle products. Note that the MOS pages sometimes change their look and feel, so these instructions may not be 100% accurate.

After logging into the MOS homepage, you will see a tab at the top of the page titled “Patches and Updates”. Click on the “Patches and Updates” tab. On the next page you will see an option to select specific products. Click on the link that indicates that the link is for Oracle patches (at this time it says “Oracle, Siebel and Hyperion products”.

Next the patches and Updates screen will appear. From this page you can search for specific patch sets. The page also offers a quick link to the latest patch sets for the Oracle Database and a link to the Oracle recommended patches. After you have selected the link you are interested, complete the search information requested. Typically you will be asked for the database release number you currently are on, the platform it is running on and other related information. Enter the information requested (some type of information are optional) and select the go button. MOS will then present a list of patches and you will have an opportunity to review the readme file associated with the patch and then download the patch.

Using OEM to Search For Patches

You can configure Oracle Enterprise Manager to manage database patching for you. OEM will interface with Metalink Oracle Support (MOS) via your Metalink support ID. OEM will notify you if patches are available for both Clusterware and the Oracle database. You can opt to download and install those patches using OEM.

Personally, as member of old school I do avoid this kind of patching....suggesting to you to decide for your own. Do remember that in many situations EM may not be available!

Downloading CPU Patches from www.oracle.com

You can find information on CPU patch sets at http://www.oracle.com/technology/deploy/security/alerts.htm . You will need a valid Oracle support contract to download the CPU patch sets. To download patch sets for Oracle log into Metalink Oracle Support (MOS). There is a tab on the MOS homepage that is called Patches and Updates (as of this writing). Click on that tab. There you will have several options to choose from including an option to quick links to the latest available patch sets.

Using OPatch

OPatch is an Oracle supplied Java utility that supports patch application and rollback. OPatch keeps track of the patches that have been installed. Also with OPatch you can perform rolling upgrades of an Oracle Cluster. In this section we will provide a general overview of the patch install process. We will then look at the OPatch utility in more detail and then finally we will look at the application of a patch with the OPatch command.

Overview of Oracle Clusterware/RAC Patching with OPatch

Applying a patch with OPatch varies based on the instructions associated with the patch to be applied. Each patch has a readme file associated with it that contains the instructions you should follow to install the patch. Instructions will differ for RAC and non-RAC databases.

OPatch supports three modes of patching. These modes are:
  • All-Node patching (all at once)
  • Minimum downtime patching
  • Rolling patching

All-Node patching

OPatch will apply the patch on the local node first, then propagate the patch to the other nodes. All instances are shutdown during the patching process. The summary of operations for all-node patching includes:
  • Shutdown all Oracle instances on all nodes.
  • Apply the patch to all nodes.
  • Bring all the nodes up

Minimum downtime patching

OPatch will apply the patch to the local node. It then will start patching a sub-set of other nodes, that it will apply the patch too. Once the patch is applied to those nodes, it will propagate the patch to the remaining nodes. This method leads to limited downtime during patching. The only downtime would occur between the point-in-time that you shutdown the second sub-set of nodes and start-up the first set of nodes that was patched. The summary of operations for minimum downtime patching includes:
  • Assume 3 nodes are being patched.
  • Shutdown all instances for the Oracle Home to be patched on Node 1.
  • Apply the patch to the Oracle Home on node 1.
  • Shutdown all instances for the Oracle Home to be patched on Node 2.
  • Apply the patch to the Oracle Home on node 2.
  • Shutdown all instances for the Oracle Home to be patched on Node 3.
  • Bring up the instances on Nodes 1 and 2.
  • Apply the patch to the Oracle Home on node 3.
  • Startup the instance on Node 3.

Rolling patching

 Each node is patched and brough up while the other nodes stay up and running. There is no downtime when using this patch method. The summary of operations for minimum downtime patching includes:
  • Assume 3 nodes are being patched.
  • Shutdown all instances for the Oracle Home to be patched on Node 1.
  • Apply the patch to the Oracle Home on node 1.
  • Start the instance on Node 1.
  • Shutdown all instances for the Oracle Home to be patched on Node 2.
  • Apply the patch to the Oracle Home on node 2.
  • Start the instance on Node 2.
  • Shutdown all instances for the Oracle Home to be patched on Node 3.
  • Apply the patch to the Oracle Home on node 3.
  • Start the instance on Node 3.
The patch readme file will indicate if rolling patching is available for the patch. Carefully review the patch set readme and any other files that come with the patch set to determine the proper way to install the patch.

The OPatch Utility

The OPatch utility is used to patch Oracle Clusterware software and Oracle RAC and Non-RAC Database software. The OPatch utility requires that the variable ORACLE_HOME be set. When running OPatch you should set the value of ORACLE_HOME to the location of the product to be patched. Thus, if you are patching Oracle Clusterware you will set ORACLE_HOME to the location of GRID_HOME. Because of this, and other occasions when it is helpful to be able to set the Oracle environment to point to GIRD_HOME, we recommend that you actually add an entry in the /etc/oratab file called grid which points to the GRID_HOME. Then you can use the oraenv program (or coraenv) to configure your environment easily. You can also include the ORACLE_HOME location when running commands from OPatch. You can also use the –oh parameter when calling OPatch and include the ORACLE_HOME location there as seen in this example:
Opatch lsinventory –detail –oh /u01/app/11.2.0/grid
Once you have configured $ORACLE_HOME correctly, you can begin the OPatch patch application process. Following the install instructions, you will typically run the pre-patch scripts, OPatch and then the post-patch scripts.

You can use the OPatch –help switch to get help for the OPatch utility. You can run OPatch –help and OPatch will present a general help screen. If you want help for a specific function you can type in the name of that function followed by the –help switch as seen in this example:
Opatch apply –help
Opatch lsinventory -help

Patch Set Conflicts

Before applying a patch set, OPatch will check for conflicts with other patch sets that you may have already installed. If it detects a conflict you will need to contact Oracle Support and as them to open an SR and request that a patch set merge request be submitted to support. You will not be able to install the patch set until this merge patch set is created. Because of the possibility of conflicts between patch sets, it’s a good idea to keep track of the patch sets that you have installed and submit this list to Oracle when requesting the creation of a patch set. This can reduce the time it take to create a patch set or the time it will take to wait for a merge patch set to be created.

OPatch can detect the following types of conflicts:
  • Bug Superset
  • Bug Conflict
  • File Conflict
  • Combination Conflict

Bug Superset

If all the bugs fixed by a patch in the system are also fixed by the patch to be applied, then this patch (the patch to be applied) is considered to be a superset of the patch already applied. If a bug superset condition is detected, it is not considered an error situation. All the subset patches are removed from the system and the new patch is applied.
For example, consider a scenario where there are four patches A,B,C, and D applied in a system, each of which fixes 2 bugs as shown:
If you apply a patch E that fixes bugs 5,6,7,8,9, and 10 then patch E will be the superset of patch C and D.

If you want OPatch to error out if the current patch bugs-to-fix is a superset or the same as an installed patch bugs-fixed in the Oracle home directory, you can use the -no_bug_superset flag.
$ OPatch/opatch apply -no_bug_superset

Bug Conflict

If a set of bugs to be fixed by the current interim patch includes some but not all bugs already fixed by one or more previously installed interim patches it is called a bug conflict. You must remove the bug conflict before you proceed with the patching by using the apply command with -force flag, that rolls back the conflicting patches before applying the new one.

For example, consider a scenario where there are four patches A,B,C, and D applied in a system, each of which fixes 2 bugs as shown in previous picture. If you apply a patch E that fixes bugs 1,3,5,7,9, and 10, you will find that this patch has fixed bugs 1,3,5,7,9, and 10, but has opened bugs 2,4,6, and, 8. This is a conflict situation.

File Conflict

If a set of files to be patched by the current interim patch include files already patched by one or more previously installed interim patches and it is not a bug superset, it is called a file conflict. You must remove the file conflict before you proceed with the patching by using the apply command with -force flag, that rolls back the conflicting patches before applying the new one.

Combination Conflict

If a set of patches has a combination of bug superset, and bug or file conflict, it is called a Combination Conflict. It is an error situation. In this case, OPatch removes all conflicting patches as well as the subset patches and then re-applies the new patch.

For example, consider a scenario where there are four patches A,B,C, and D applied in a system, each of which fixes 2 bugs as shown in previous picture. Patch C is the subset of patch D. Patch A and patch B are conflicting patches of patch D. If you apply this patch D that fixes bugs 1,3,5,6,7, and 8 with -force flag, you will find that OPatch would have rolled back patches A, B, and C and would have applied patch D.

The End

Keep in mind that in patching is one of the most risking operation. So having a proper database backup as well as general "rollback plan" is something that will save hours of bad mouth taste!

Cheers!

Tuesday, August 10, 2010

ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors

The problem

Today I have a simple task to export schema from one database to another. Export was really simple and correct one. On import side at the beginig of log there was:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by ZUCRI, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
IMP-00008: unrecognized statement in the export file: 
  
. importing ZUCRI's objects into TAB
...
Oracle does not allow exp from higher version (10.2.0.4) and then imp into lower (10.1.0.4) version. If 10.1.04 imp is to be used, then 10.1.0.4 exp must be first used.

Unfortunately as mentioned, this was impossible because mine source was on 10.2.0.4 and have to be ported to 10.1.0.4 .
So only solution was to use Oracle Data Pump utility with "version" parameter which allow import from higher version.

C:\>expdp '/ as sysdba' DUMPFILE="zucri.dmp" LOGFILE="exp_zucri.log" DIRECTORY=DATA_PUMP_DIR VERSION=10.1.0.4 COMPRESSION=METADATA_ONLY CONTENT=ALL SCHEMAS=('ZUCRI')

Export: Release 10.2.0.4.0 - 64bit Production on Utorak, 10 Kolovoz, 2010 13:45:02

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Testing options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_EXPORT_SCHEMA_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20100810134502" and "" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-24203: operation failed, queue table SYS.KUPC$DATAPUMP_QUETAB has errors
Notice "VERSION=10.1.0.4" in expdp command. In this case this was crucial part.

But it seems that some sys parts are invalid so I have to manage different approach to be able to run expdp utility.
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Uto Kol 10 13:47:11 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining Scoring Engine and Real Application Tes
ting options

SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

OBJECT_NAME
------------------------------
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTK+Hp6PazTJaPOReVq6YXqQ==
SYSNTsDyaSCdvTmySdAs5Ztvhbw==
SYSNTWBJ9iDeeSuyp/p6B7f1iEg==

8 rows selected.

Quick help with utlrp (quick output):
SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN  2010-08-10 13:49:07

...

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END  2010-08-10 13:50:00


PL/SQL procedure successfully completed.
...
ERRORS DURING RECOMPILATION
---------------------------
                          4


PL/SQL procedure successfully completed.
And object validity checking again:
SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

OBJECT_NAME
------------------------------
AQ$KUPC$DATAPUMP_QUETAB
AQ$_KUPC$DATAPUMP_QUETAB_E
AQ$_KUPC$DATAPUMP_QUETAB_F
AQ$_KUPC$DATAPUMP_QUETAB_V
KUPC$DATAPUMP_QUETAB
SYSNTK+Hp6PazTJaPOReVq6YXqQ==
SYSNTsDyaSCdvTmySdAs5Ztvhbw==
SYSNTWBJ9iDeeSuyp/p6B7f1iEg==

8 rows selected.
wasn't successful in any way-so now I was really stuck!

The solution

Mine database version was 10.2.0.4, and this database was hugely patched with all kinds of patches from 10.1 version. So this error raised somewhere in that time.

After searching Metalink, solution seems to be very easy. Recreate queue tables, that were invalid:
SQL> exec dbms_aqadm.drop_queue_table(queue_table =>'SYS.KUPC$DATAPUMP_QUETAB', force=> TRUE);

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
  3    multiple_consumers => TRUE,
  4    queue_payload_type =>'SYS.KUPC$_MESSAGE',
  5    comment => 'DataPump Queue Table',
  6    compatible=>'10.1.0');
  7  EXCEPTION
  8    WHEN OTHERS THEN
  9      IF SQLCODE = -24001 THEN NULL;
 10      ELSE RAISE;
 11      END IF;
 12  END;
 13  /

PL/SQL procedure successfully completed.

Let's check invalids again:
SQL> select object_name from all_objects where owner = 'SYS' AND status != 'VALID' order by 1;

no rows selected

SQL>
All seems fine!

Try expdp:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by ZUCRI, not by you

import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
IMP-00008: unrecognized statement in the export file: 
  
. importing ZUCRI's objects into TAB
. . importing table                 "ADR_CVOROVI2"          0 rows imported
...
Works!

The End

According metalink notes, there is another solution:
@?\rdbms\admin\catproc.sql  
@?\rdbms\admin\utlrp.sql 
This scripts will re-create all the pl/sql in database, and may cause some user sp/function/package invalid during executing, and it costs long time - more then dozen of minutes with total database inactivity to other users-unacceptable.

Cheers!

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign