Posts
Right now I am working hard on making sure we have all of our connections to sources, targets, the metadata repository, and any other loose ends tied up on a nice start to yet another Data Warehousing and BI project- using the Oracle suite of tools!
One thing I don't have much experience with that I wanted to share pitfalls about (and accept tips from readers as well) is using an Oracle RAC instance as our ultimate 'Target Schema' in our ETL using OWB. We have a multitude of data sources, of course, and we have our unified repository on the RAC instance. We also have our Target schema and tables on this same RAC instance. Nothing crazy just yet! Here is where the fun begins (note to self: it is always interesting to say its fun when it is actually a bit painful)....
First, the Oracle RAC instance is using ASM. A bit abour ASM- ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.
Oracle ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, control files, and the RMAN Flash Recovery Area. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).
ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64MB for most systems. In Oracle RAC environments, an ASM instance must be running on each cluster node.
The diagram below shows a very basic topology, and yes this RAC is using ASM.
Now that we have a few of the basic ideas out in the open...the real issues come into play for people like myself that don't know exactly how RAC is going to work with OWB services that are trying to deploy tables, data, and other things to a RAC target. Furthermore, what is going to happen with the OWB Repository, and the properties files that are created when a repository is created via OWB? Well we're learning.
First I'll mention a few details about installing OWB in a RAC environment, and in particular- the repository setup on the RAC cluster.
To install OWB in a RAC environment:
Create the RAC environment as described in the Oracle Clusterware and Oracle Real Application Clusters Installation Guide specific to your platform. If you have not already done so, it won't hurt to review the most recent Oracle Warehouse Builder Release Notes available for your version of the install!
For each computer to host Warehouse Builder components, configure the tnsnames.ora file located in the OWB_ORACLE_HOME\network\admin directory. Also be sure to set the initialization parameter MAX_COMMIT_PROPAGATION_DELAY to a value of zero.
Definitely configure tnsnames.ora for each Oracle Database server that will be a Warehouse Builder data source OR target. If you fail to configure tnsnames.ora for any host or database server, you may encounter a repository connection error such as "The connection to the repository was lost, because of the following database error: ORA-12154:TNS:could not resolve the connect identifier specified". This is a common error!
Then review any last-minute install requirements before launching the Universal Installer and installing OWB.
To install an OWB Repository in a RAC environment:
When OWB is installed, you'll then want to install an OWB repository. To launch the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant. (To launch the Repository Assistant on UNIX, locate OWB_ORACLE_HOME/owb/bin/unix and execute reposinst.sh.)
The Repository Assistant prompts you to define users and an owner for the repository.
Now for each RAC node, make sure to register each one! So for each node, launch the Repository Assistant and select the Advanced Set up option. Connect to the node using the net service name. Select the option for registering the RAC instance. (see below)
One thing I don't have much experience with that I wanted to share pitfalls about (and accept tips from readers as well) is using an Oracle RAC instance as our ultimate 'Target Schema' in our ETL using OWB. We have a multitude of data sources, of course, and we have our unified repository on the RAC instance. We also have our Target schema and tables on this same RAC instance. Nothing crazy just yet! Here is where the fun begins (note to self: it is always interesting to say its fun when it is actually a bit painful)....
First, the Oracle RAC instance is using ASM. A bit abour ASM- ASM is a new feature in Oracle Database 10g that provides the services of a filesystem, logical volume manager, and software RAID in a platform-independent manner. Oracle ASM can stripe and mirror your disks, allow disks to be added or removed while the database is under load, and automatically balance I/O to remove "hot spots." It also supports direct and asynchronous I/O and implements the Oracle Data Manager API (simplified I/O system call interface) introduced in Oracle9i.
Oracle ASM is not a general-purpose filesystem and can be used only for Oracle data files, redo logs, control files, and the RMAN Flash Recovery Area. Files in ASM can be created and named automatically by the database (by use of the Oracle Managed Files feature) or manually by the DBA. Because the files stored in ASM are not accessible to the operating system, the only way to perform backup and recovery operations on databases that use ASM files is through Recovery Manager (RMAN).
ASM is implemented as a separate Oracle instance that must be up if other databases are to be able to access it. Memory requirements for ASM are light: only 64MB for most systems. In Oracle RAC environments, an ASM instance must be running on each cluster node.
The diagram below shows a very basic topology, and yes this RAC is using ASM.
Now that we have a few of the basic ideas out in the open...the real issues come into play for people like myself that don't know exactly how RAC is going to work with OWB services that are trying to deploy tables, data, and other things to a RAC target. Furthermore, what is going to happen with the OWB Repository, and the properties files that are created when a repository is created via OWB? Well we're learning.
First I'll mention a few details about installing OWB in a RAC environment, and in particular- the repository setup on the RAC cluster.
To install OWB in a RAC environment:
Create the RAC environment as described in the Oracle Clusterware and Oracle Real Application Clusters Installation Guide specific to your platform. If you have not already done so, it won't hurt to review the most recent Oracle Warehouse Builder Release Notes available for your version of the install!
For each computer to host Warehouse Builder components, configure the tnsnames.ora file located in the OWB_ORACLE_HOME\network\admin directory. Also be sure to set the initialization parameter MAX_COMMIT_PROPAGATION_DELAY to a value of zero.
Definitely configure tnsnames.ora for each Oracle Database server that will be a Warehouse Builder data source OR target. If you fail to configure tnsnames.ora for any host or database server, you may encounter a repository connection error such as "The connection to the repository was lost, because of the following database error: ORA-12154:TNS:could not resolve the connect identifier specified". This is a common error!
Then review any last-minute install requirements before launching the Universal Installer and installing OWB.
To install an OWB Repository in a RAC environment:
When OWB is installed, you'll then want to install an OWB repository. To launch the Repository Assistant on Windows, from the Windows Start menu, select Programs and navigate to the Oracle product group you installed in the previous step. Select Warehouse Builder, Administration, and then Repository Assistant. (To launch the Repository Assistant on UNIX, locate OWB_ORACLE_HOME/owb/bin/unix and execute reposinst.sh.)
The Repository Assistant prompts you to define users and an owner for the repository.
Now for each RAC node, make sure to register each one! So for each node, launch the Repository Assistant and select the Advanced Set up option. Connect to the node using the net service name. Select the option for registering the RAC instance. (see below)
If the software is installed on separate disks, copy rtrepos.properties to each node in the cluster. If you did not install to a shared disk, then you must manually copy the file /owb/bin/admin/rtrepos.properties from the primary node to each node in the cluster. <-- This is yet another point I forgot so please make a note of it! See metalink note below!
Then install the Warehouse Builder software on the client machines.
When complete the installation process, you can launch all the Warehouse Builder components.
When launching OWB components such as the Design Center, Control Center Manager, and Repository Assistant, select the log on option SQL*Net connection and specify the net service name you assigned in the tnsnames.ora file. Since you can connect to Warehouse Builder repositories using a net service name, you can embed RAC properties into the connect string to utilize RAC functionalities such as connect time failover, load balancing on server and load balancing of connections. Finally, the Control Center Service requires that service names for the individual nodes in the cluster be available. If these are not present after the RAC installation, you must manually ensure the availability.
For RAC, it is recommended that you install the Warehouse Builder components on each node of the cluster. The Control Center Service is required on to each node of the RAC cluster.You can achieve this in a single installation of the Warehouse Builder software if you install on a shared disk such as an OCFS or NTS shared disk.
Metalink Note On OWB + RAC:
Applies to:
Oracle Warehouse Builder - Version: 10.2.0.1.31This problem can occur on any platform.
Symptoms
The Warehouse Builder Runtime Service fails to startup on a Real Application Cluster (RAC).The following can be found in the Runtime Service log file or in sqlplus depending on the way the Service is started.service startup failure reason ORA-29532: Java call terminated by uncaught Javaexception: oracle.jdbc.driver.OracleSQLException: ORA-28239: no key providedORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 40ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 286ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 314ORA-06512: at line 1
Changes
Switch from one RAC node to another RAC node.
Cause
The file rtrepos.properties created into the owb_home/owb/bin/admin during the Unified Repository creation has not been deployed (copy) into the owb_home/aow/bin/admin on the other node(s).
Solution
On the RAC node used to perform the Unified Repository creation, locate the file rtrepos.properties in owb_home/owb/bin/admin and copy it on the other node(s) into the owb_home/owb/bin/admin directory.
Then install the Warehouse Builder software on the client machines.
When complete the installation process, you can launch all the Warehouse Builder components.
When launching OWB components such as the Design Center, Control Center Manager, and Repository Assistant, select the log on option SQL*Net connection and specify the net service name you assigned in the tnsnames.ora file. Since you can connect to Warehouse Builder repositories using a net service name, you can embed RAC properties into the connect string to utilize RAC functionalities such as connect time failover, load balancing on server and load balancing of connections. Finally, the Control Center Service requires that service names for the individual nodes in the cluster be available. If these are not present after the RAC installation, you must manually ensure the availability.
For RAC, it is recommended that you install the Warehouse Builder components on each node of the cluster. The Control Center Service is required on to each node of the RAC cluster.You can achieve this in a single installation of the Warehouse Builder software if you install on a shared disk such as an OCFS or NTS shared disk.
Metalink Note On OWB + RAC:
Applies to:
Oracle Warehouse Builder - Version: 10.2.0.1.31This problem can occur on any platform.
Symptoms
The Warehouse Builder Runtime Service fails to startup on a Real Application Cluster (RAC).The following can be found in the Runtime Service log file or in sqlplus depending on the way the Service is started.service startup failure reason ORA-29532: Java call terminated by uncaught Javaexception: oracle.jdbc.driver.OracleSQLException: ORA-28239: no key providedORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 40ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 153ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 286ORA-06512: at "OWB_REP_OWNER.WB_RT_SERVICE_CONTROL", line 314ORA-06512: at line 1
Changes
Switch from one RAC node to another RAC node.
Cause
The file rtrepos.properties created into the owb_home/owb/bin/admin during the Unified Repository creation has not been deployed (copy) into the owb_home/aow/bin/admin on the other node(s).
Solution
On the RAC node used to perform the Unified Repository creation, locate the file rtrepos.properties in owb_home/owb/bin/admin and copy it on the other node(s) into the owb_home/owb/bin/admin directory.
Excellent! It took me a few seconds to solve a similar problem when deploying from a different machine.
ReplyDeleteThanks,
This is what we have as well. But we haven't been successful in getting OWB to load balance across RAC, just fail over (which is a part of 'normal' Oracle Clusterware anyway). Have you guys been able to load balance things such as ETL ?
ReplyDelete