| Oracle® Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35270-01 |
|
|
PDF · Mobi · ePub |
| Oracle® Business Intelligence Applications Upgrade Guide for Informatica PowerCenter Users Release 7.9.6.4 Part Number E35270-01 |
|
|
PDF · Mobi · ePub |
This section contains instructions for upgrading Oracle BI Applications when you are running Oracle EBS as your source system.
This section includes the following topics:
Section 6.1, "Prerequisites for Upgrading Oracle BI Applications"
Section 6.2, "Upgrading Oracle BI Infrastructure to Version 11g"
Section 6.4, "Upgrading to Informatica PowerCenter Version 9.0.1 Hotfix 2"
Section 6.9, "Upgrading the Data Warehouse Schema and Migrating Data for Non-Teradata Databases"
Section 6.10, "Upgrading the Data Warehouse Schema and Migrating Data for Teradata Databases"
Section 6.14, "Merging Oracle BI Applications Version 7.9.6.4 Repository With Customer Repository"
Section 6.16, "Regression Testing the Oracle BI Repository Merge"
Complete the following prerequisites before upgrading to Oracle BI Applications release 7.9.6.4:
The DAC Client requires an ODBC connection to the Oracle Business Analytics Warehouse when the database type is Oracle, SQL Server, DB2, and DB2-390 in order to upgrade the data warehouse schema using the DDL Import Utility.
Before you upgrade to Oracle BI Applications verify whether you have the appropriate ODBC connection set. If you do not, see Appendix F, "Creating ODBC Database Connections for DAC."
You must upgrade Oracle BI Infrastructure to version 11g. For the specific release of Oracle BI Infrastructure 11g that is supported for this release of Oracle BI Applications, see the System Requirements and Supported Platforms for Oracle Business Intelligence Applications. For information on installing the supported version of Oracle BI Infrastructure, see the Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.
Run the Oracle BI Applications installer to upgrade your Oracle BI Applications environment to the current version. For instructions on running the installer, see the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
Note the following points:
Review "Chapter 3: Preinstallation and Predeployment Requirements for Oracle BI Applications," in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users. You must satisfy these requirements in order to upgrade to the current version of Oracle BI Applications.
Perform only the step that addresses the running of the installer. Do not perform the configuration instructions that follow the running of the installer.
If you have a previous release of Oracle BI Applications installed, you must uninstall it before you run the installer for the current release. If you do not uninstall the old release, some folders from the current release will not be correctly installed. (Make a back-up of your DAC folder before you uninstall the old release. This will be the backup of your DAC Client and Server and the DAC metadata files.)
You must install Informatica PowerCenter 9.0.1 Hotfix 2 to run the current version of Oracle BI Applications. See the System Requirements and Supported Platforms for Oracle Business Intelligence Applications for information about additional hotfixes or emergency bug fixes that may be required to support the current version of Informatica.
Note:
Oracle BI Applications release 7.9.6.4 is also certified to run with Informatica PowerCenter version 9.1 Hotfix 2. For instructions on upgrading the Informatica Repository if you have previously upgraded to Informatica PowerCenter 9.1 Hotfix 2, see Section 6.6, "Upgrading the Informatica Repository When You Are Already Running Informatica PowerCenter 9.1."
The components and architecture for Informatica PowerCenter 9.x differ significantly from Informatica PowerCenter 7.x and 8.x. Oracle recommends that you carefully review the Informatica PowerCenter 9.0.1 documentation.
For a summary of installation instructions for installing Informatica PowerCenter 9.0.1 on a single machine in an Oracle BI Applications deployment, see the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
For detailed information about deploying Informatica PowerCenter 9.0.1, refer to the Informatica PowerCenter Installation and Configuration Guide, Informatica Upgrade Guide for PowerCenter 8.1.x, Informatica Upgrade Guide for PowerCenter 8.5.x and 8.6.x, Informatica Administrator Guide, and related documentation. See the Oracle Business Intelligence Applications Release Notes, available on the Oracle Technology Network, for information about how to locate the Informatica documentation.
To upgrade to Informatica PowerCenter 9.0.1 Hotfix 2:
Perform the pre-upgrade, upgrade, and post-upgrade steps documented in Informatica Upgrade Guide for PowerCenter 8.1.x or Informatica Upgrade Guide for PowerCenter 8.5.x and 8.6.x, depending on which version of Informatica PowerCenter you are upgrading from.
Note the following points:
The Informatica PowerCenter 9.0.1 installation process includes upgrading your current Informatica Repository to the version 9.0.1 format. This process is necessary so that you will be able to access your current repository using the version 9.0.1 client tools and so that you can perform the procedure Section 6.5, "Upgrading the Informatica Repository."
In Section 6.5, "Upgrading the Informatica Repository," you back up and rename your current repository and then restore the Informatica Repository that is installed during the Oracle BI Applications installation (for example, Oracle_BI_DW_Base.rep). You then copy the folders containing the Oracle BI Applications content for release 7.9.6.4 to your existing repository.
If you install Informatica PowerCenter 9.0.1 using the installer Upgrade option rather than the Install option, the property $PMRootDir is not updated to the upgraded service version installation directory. You need to configure this property to specify the upgraded service installation directory in order for DAC to be able to locate the Informatica source files.
To configure the $PMRootDir property: launch Informatica Administrator, select the Integration Service from the Navigator, click the Processes tab, and click Edit. Then, enter the appropriate value for $PMRootDir.
Follow this procedure to upgrade the Informatica Repository when you are upgrading the Informatica PowerCenter platform from version 7.x or 8.x to 9.0.1 Hotfix 2. If you have already upgraded to Informatica PowerCenter 9.1, follow the procedure in Section 6.6, "Upgrading the Informatica Repository When You Are Already Running Informatica PowerCenter 9.1."
In this procedure, you will copy the folders containing the Oracle BI Applications 7.9.6.4 content into your current, existing Informatica Repository. The result of this action is an Informatica Repository that contains your customizations along with the new Oracle BI Applications 7.9.6.4 content.
For detailed instructions on backing up and restoring the Informatica Repository, see the topic titled, "Backing Up and Restoring the PowerCenter Repository," in "Chapter 15: PowerCenter Repository Management," in the Informatica PowerCenter Administrator Guide. See the Oracle Business Intelligence Applications Release Notes, available on the Oracle Technology Network, for information about how to locate the Informatica documentation.
To upgrade the Informatica Repository:
Make sure you have backed up and renamed your current, existing Informatica Repository.
Copy the Oracle_BI_DW_Base.rep file (or Oracle_BI_DW_Teradata.rep for repositories on Teradata databases) from the folder OracleBI\dwrep\Informatica\Repository into the folder <INFA_HOME>\server\infa_shared\Backup.
Note:
The Oracle_BI_DW_Base.rep file is installed in the OracleBI root directory when you run the Oracle BI Applications installer, as described in Section 6.3, "Upgrading Oracle BI Applications."
Using Informatica PowerCenter Administrator, create a new Repository Service for the Oracle_BI_DW_Base.rep repository. See "Setup Step: Creating the Informatica Repository Service," in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users for instructions.
Using the Repository Service you created in the step above, restore the Oracle_BI_DW_Base.rep repository into an empty schema. See "Restoring the Prebuilt Informatica Repository for Environments in English" or "Restoring the Prebuilt Informatica Repository on a Non-English Operating System" in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users for instructions.
This step gives you the Oracle BI Applications 7.9.6.4 Informatica Repository in the Informatica 9.0.1 format.
Launch the Informatica PowerCenter Repository Manager, and connect to the repository you restored in step 4 (for example, Oracle_BI_DW_Base.rep), and connect to your current, existing Informatica Repository.
Note: Your current, existing repository should have been updated to the version 9.0.1 format during the Informatica PowerCenter 9.0.1 installation process.
In your current, existing repository, make sure all of your customizations are in a "Custom" folder.
In your current, existing repository, delete the following folders:
PLP
All folders with the prefix SDE_
SILOS
SIL_VERT
UA_SDE
UA_SIL
Make sure you do not delete the "Custom" folder.
Copy the following folders containing the Oracle BI Applications content for release 7.9.6.4 from the newly restored repository (Oracle_BI_DW_Base.rep) into your current, existing repository.
PLP
All folders with the prefix SDE_
SILOS
SIL_VERT
UA_SDE
UA_SIL
This action updates your current, existing repository with the Oracle BI Applications 7.9.6.4 content; this is the repository you will use to run ETL in Oracle BI Applications 7.9.6.4.
The version 7.9.6.4 repository (Oracle_BI_DW_Base.rep) and the Repository Service for this repository are no longer needed.
If you are upgrading to Oracle BI Applications 7.9.6.4 and previously upgraded Informatica PowerCenter to version 9.1 Hotfix 2, you must first restore the Oracle BI Applications version 7.9.6.4 Informatica Repository using Informatica PowerCenter 9.0.1 Hotfix 2. Then, you can upgrade this repository to the Informatica 9.1 format by pointing to it using Informatica 9.1 client tools. After the Oracle BI Applications 7.9.6.4 Informatica Repository has been upgraded to the Informatica 9.1 format, you can then copy the folders containing the Oracle BI Applications 7.9.6.4 content into your current, existing Informatica Repository. The result of this action is an Informatica Repository that contains your customizations along with the new Oracle BI Applications 7.9.6.4 content.
For detailed instructions on backing up and restoring the Informatica Repository, see the topic titled, "Backing Up and Restoring the PowerCenter Repository," in "Chapter 15: PowerCenter Repository Management," in the Informatica PowerCenter Administrator Guide. See the Oracle Business Intelligence Applications Release Notes, available on the Oracle Technology Network, for information about how to locate the Informatica documentation.
To upgrade the Informatica Repository when already running Informatica PowerCenter 9.1:
Make sure you have backed up and renamed your current, existing Informatica Repository.
Install Informatica PowerCenter Services version 9.0.1 Hotfix 2. You do not need to install the Informatica PowerCenter Client Tools. See Section 6.4, "Upgrading to Informatica PowerCenter Version 9.0.1 Hotfix 2" for instructions.
Copy the Oracle_BI_DW_Base.rep file (or Oracle_BI_DW_Teradata.rep for repositories on Teradata databases) from the folder OracleBI\dwrep\Informatica\Repository into the Informatica PowerCenter 9.0.1 folder <INFA_HOME>\server\infa_shared\Backup.
Note:
The Oracle_BI_DW_Base.rep file is installed in the OracleBI root directory when you run the Oracle BI Applications installer, as described in Section 6.3, "Upgrading Oracle BI Applications."
Using Informatica PowerCenter Administrator, create a new Repository Service for the Oracle_BI_DW_Base.rep repository. See "Setup Step: Creating the Informatica Repository Service," in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users for instructions.
Using the Repository Service you created in the step above, restore the Oracle_BI_DW_Base.rep repository into an empty schema. See "Restoring the Prebuilt Informatica Repository for Environments in English" or "Restoring the Prebuilt Informatica Repository on a Non-English Operating System" in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users for instructions.
This step gives you the Oracle BI Applications 7.9.6.4 Informatica Repository in Informatica 9.0.1 format.
Launch the Informatica PowerCenter Repository Manager, and connect to the repository you restored in step 5. When prompted to upgrade the repository to the 9.1 format, do so.
In Repository Manger, connect to your current, existing Informatica Repository.
In your current, existing repository, make sure all of your customizations are in a "Custom" folder.
In your current, existing repository, delete the following folders:
PLP
All folders with the prefix SDE_
SILOS
SIL_VERT
UA_SDE
UA_SIL
Make sure you do not delete the "Custom" folder.
Copy the following folders containing the Oracle BI Applications content for release 7.9.6.4 from the newly restored repository (Oracle_BI_DW_Base.rep) into your current, existing repository.
PLP
All folders with the prefix SDE_
SILOS
SIL_VERT
UA_SDE
UA_SIL
This action updates your current, existing repository with the Oracle BI Applications 7.9.6.4 content; this is the repository you will use to run ETL in Oracle BI Applications 7.9.6.4.
The version 7.9.6.4 repository (Oracle_BI_DW_Base.rep) and the Repository Service for this repository are no longer needed.
Informatica PowerCenter version 9.0.1 Hotfix 2 requires configuration for it to work with Oracle BI Applications and DAC. The configuration settings from your earlier Informatica environment may have been retained when you upgraded to Version 9.0.1 Hotfix 2. Verify or perform the procedures listed below, as documented in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users:
Copying Source Files and Lookup Files
Setting PowerCenter Integration Services Relaxed Code Page Validation
Setting PowerCenter Integration Services Custom Properties
Creating the Repository Administrator User in the Native Security Domain
This section includes instructions for upgrading the DAC platform and repository to version 11g as well as information about configurations you must set or verify.
This section includes the following topics:
The current release of DAC 11g is installed by its own installer and not the Oracle BI Applications installer. For information about the specific release of DAC 11g that is supported for this release of Oracle BI Applications, see System Requirements and Supported Platforms for Oracle Business Intelligence Applications.
After you install DAC 11g, you then need to copy metadata files from the machine hosting Oracle BI Applications to the machines hosting the DAC Client and Server. You then need to import the new metatdata into the DAC Repository. For instructions on performing these tasks, see the following sections in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users:
Installing and Setting Up the DAC Platform
Note:
You must perform all of the procedures in this section, including installing JDBC drivers, creating ODBC database connections, and installing pmrep and pmcmd command line programs.
Installing DAC Metadata Files
Logging into DAC for the First Time and Importing Metadata into the DAC Repository
Note:
When you perform this step, DAC will prompt you to upgrade the repository. Click Yes. This action will upgrade your repository in the 10g format to the 11g format.
After you complete this procedure, you will have the default DAC Repository for Oracle BI Applications release 7.9.6.4.
The DAC Client and Server require configuration to work with Oracle BI Applications and Informatica PowerCenter. The configuration settings from your earlier DAC environment may have been retained when you upgraded to version 11g. Verify or perform the procedures listed below, as documented in the Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users:
Configuring the DAC Server
Configuring DAC Integration Settings
Configuring the SiebelUnicodeDB Custom Property
Note:
This procedure is required only if your source to target data movement configuration is Unicode to Unicode.
Setting Up DAC to Receive Email Notification
Additional Configuration Tasks
In this section, perform only the tasks that apply to your environment.
To upgrade the DAC Repository, you perform a two-step process:
You first upgrade the existing DAC Repository from the 10g format to the 11g format. When you log into DAC 11g for the first time, DAC will prompt you to upgrade your repository. Click Yes.
For more information, see "Upgrading to DAC 11g" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console and "Logging Into DAC for the First Time and Importing Metadata into the DAC Repository" in Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users.
You then use the DAC's Upgrade/Merge Wizard to complete the upgrade of your existing DAC Repository.
If you are upgrading to Oracle BI Applications release 7.9.6.4, and you are remaining on the same version of the transactional source system, you would use the Refresh Base option of the DAC Upgrade/Merge Wizard to complete the upgrade of your existing DAC Repository. For information about how to use the Refresh Base option, see the topic titled, "About the Refresh Base Option," in the chapter "Upgrading, Comparing and Merging DAC Repositories," in the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.
If you are upgrading your transactional system, for example, from Oracle EBS release 12 to release 12.2, you would use the Replace Base option of the DAC Upgrade/Merge Wizard to complete the upgrade of your existing DAC Repository. For information about how to use the Replace Base option, see the topic titled, "About the Replace Base Option," in "Upgrading, Comparing and Merging DAC Repositories," in the Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console.
The Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console is available in the Oracle Business Intelligence Data Warehouse Administration Console Documentation Library on the Oracle Technology Network.
This section contains the following topics:
Section 6.9.1, "Performing Data Warehouse Schema Upgrade Steps Common to All Previous Releases"
Section 6.9.2, "Upgrading the Data Warehouse Schema From Version 7.9.x to 7.9.3 and Migrating Data"
Section 6.9.3, "Upgrading the Data Warehouse Schema From Version 7.9.3 to 7.9.4 and Migrating Data"
Section 6.9.4, "Upgrading the Data Warehouse Schema From Version 7.9.4 to 7.9.5. and Migrating Data"
Section 6.9.7, "Upgrading the Data Warehouse Schema From Version 7.9.6 to 7.9.6.1 and Migrating Data" (Applicable only if you are using an Oracle database as your data warehouse database.)
If you are currently running Oracle BI Applications release 7.9.0, 7.9.1 or 7.9.2, you need to perform the procedures in Section 6.9.1, Section 6.9.2, Section 6.9.3, Section 6.9.4, Section 6.9.5, Section 6.9.6, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.3, you need to perform the procedures in Section 6.9.1, Section 6.9.3, Section 6.9.4, Section 6.9.5, Section 6.9.6, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.4, you need to perform the procedures in Section 6.9.1, Section 6.9.4, Section 6.9.5, and Section 6.9.6, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.5, you need to perform the procedures in Section 6.9.1 and Section 6.9.5, and Section 6.9.6, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.5.1, you need to perform the procedures in Section 6.9.1 and Section 6.9.6, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.6, you need to perform the procedures in Section 6.9.1, Section 6.9.7, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.6.1, you need to perform the procedures in Section 6.9.1, Section 6.9.8, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.6.2, you need to perform the procedures in Section 6.9.1, Section 6.9.9, and Section 6.9.10.
If you are currently running Oracle BI Applications release 7.9.6.3, you need to perform the procedures in Section 6.9.1 and Section 6.9.10.
These procedures add new tables, columns, and indexes to the existing data warehouse schema. They also modify the existing data warehouse schema objects.
The steps in this procedure are required for upgrading to Oracle BI Applications release 7.9.6.4 from all previous releases.
To perform common data warehouse upgrade steps:
If you are using a DB2 database, you need to execute the stored procedure siebproc.sql, which creates procedures and functions needed by the DDLimp utility. This file is located in \dwrep\siebproc\db2udb.
Copy all of the domain value files in the folder \OracleBI\dwrep\Informatica\LkpFiles into the folder <INFA_HOME>\server\infa_shared\LkpFiles.
Restore the Upgrade repository:
Copy the file Upgrade.rep from the folder OracleBI\dwrep\Upgrade\Informatica\Repository into the folder <INFA_HOME>\server\infa_shared\Backup.
Launch the Informatica Administrator, and restore Upgrade.rep (located in <INFA_HOME>\server\infa_shared\Backup).
In Informatica Workflow Manager, open the Relational Connection Browser (in the menu bar, select Connections, and then select Relational), and edit the connect string, user and password for the relational connections as follows:
For the OLTP connection:
If you are using the Oracle EBS 11i source system, edit the connection PARAM_OLTP_ORA11i.
If you are using Oracle EBS R12, source system, edit the connection PARAM_OLTP_ORAR12.
For the OLAP connection, edit the connection PARAM_OLAP.
For the DAC database connection, edit the connection PARAM_DAC.
Note:
If you are connected to an Oracle database, use the Oracle native driver instead of ODBC.
If you are connected to a SQL Server database, use the ODBC driver rather than the native SQL Server driver.
Perform this procedure if you are upgrading from Oracle BI Applications releases 7.9.0, 7.9.1 or 7.9.2.
To upgrade the data warehouse schema and migrate data into the upgraded data warehouse:
Run the UPGRADE_793.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the ddl_793.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 793_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Migrate data into the upgraded data warehouse.
Configure the Informatica parameter file 793_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 793_UPG_PARAMS.txt."
In Informatica Workflow Manager, navigate to the folder UPGRADE_790_to_793 and run the Update_Dimensions workflow, and then run the Update_Facts workflow.
If you are using the Oracle EBS 11.5.10 source system, navigate to the folder UPGRADE_790_to_793_ORA11510, and run the SIL_PurchaseAmount_Patch workflow.
Verify the data migrated successfully.
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\SessLogs directory indicate errors or failures.
Check whether the script 793_UPGRADE_PRE_DIMENSION_SCRIPT.sql that you ran in the SQL client of the database failed or errored out while executing.
Check the log file for the scripts ddl_793.ctl and UPGRADE_793.ctl, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as, W_xxxx_x_79x tables, LKP_xxxx_x and 79x_XXXX_TMP.
This step frees the space occupied by these backup tables.
Perform the procedure in this section to upgrade from Oracle BI Applications release 7.9.3 to 7.9.4.
To upgrade the data warehouse schema and migrate data into the upgraded data warehouse:
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Run the 794_UPGRADE_PRE_CTL_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the UPGRADE_794.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the ddl_794.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 794_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Migrate data into the upgraded data warehouse.
Configure the Informatica parameter file 794_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 794_UPG_PARAMS.txt.".
In Informatica Workflow Manager, navigate to the folder UPGRADE_793_to_794_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Verify the data migrated successfully.
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\SessLogs directory indicate errors or failures.
Check whether the script 794_UPGRADE_PRE_DIMENSION_SCRIPT.sql that you ran in the SQL client of the database failed or errored out while executing.
Check the log file for the scripts ddl_794 and UpgradPGRADE_794.ctl, which you ran using the DDLimp command to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as, W_xxxx_x_79x tables, LKP_xxxx_x and 79x_XXXX_TMP.
This step frees the space occupied by these backup tables.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.4 to 7.9.5.
To upgrade the data warehouse schema and migrate data into the upgraded data warehouse:
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Run the 795_UPGRADE_PRE_CTL_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the UPGRADE_795.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the ddl_795.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 795_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Migrate data into the upgraded data warehouse.
Configure the Informatica parameter file 795_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 795_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Verify the data migrated successfully.
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the script 795_UPGRADE_PRE_DIMENSION_SCRIPT.sql that you ran in the SQL client of the database failed or errored out while executing.
Check the log file for the scripts ddl_795.ctl and UPGRADE_795.ctl, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.5 to 7.9.5.1.
To upgrade the data warehouse schema and migrate data into the upgraded data warehouse:
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Run the 7951_UPGRADE_PRE_CTL_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the UPGRADE_7951.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the ddl_7951.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 7951_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Migrate data into the upgraded data warehouse:
Configure the Informatica parameter file 7951_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7951_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_7951_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_7951_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Verify the data migrated successfully.
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the script 7951_UPGRADE_PRE_DIMENSION_SCRIPT.sql that you ran in the SQL client of the database failed or errored out while executing.
Check the log file for the scripts ddl_7951.ctl and Upgrade_7951.ctl, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.5.1 to 7.9.6.1.
This section includes the following procedures:
Section 6.9.6.1, "Upgrading the Data Warehouse Schema to Version 7.9.6.1"
Section 6.9.6.2, "Migrating Data into the Upgraded Data Warehouse"
Follow this procedure to upgrade the data warehouse schema to version 7.9.6.1.
Note: Upgrading to version 7.9.6.1 may involve running scripts that have a "796_" prefix.
To upgrade the data warehouse schema to version 7.9.6.1:
If you are using an Oracle database, run the 796_UPGRADE_DROP_INDEXES.sql script.
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Oracle.
Open the 796_UPGRADE_DROP_INDEXES. sql file, and copy the contents into the SQL client.
Execute the script.
If you are using a SQL Server or DB2 database, run the 7961_UPGRADE_DROP_INDEXES.sql script.
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Open the 7961_UPGRADE_DROP_INDEXES. sql file, and copy the contents into the SQL client.
Execute the script.
If you are using a SQL Server database, run the 7961_UPGRADE_PRE_UPG_CTL_SCRIPT.sql file. This file is located in OracleBI\dwrep\Upgrade\DbScripts\SQLServer.
Run the UPGRADE_796.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 7961_UPGRADE_PRE_CTL_SCRIPT.sql:
Go to OracleBI\dwrep\Upgrade\DbScripts\<database type> folder, and open the 7961_UPGRADE_PRE_CTL_SCRIPT.sql file.
Search for the Datasource_Num_ID parameter, and make sure the value is correct for your existing implementation.
If you are using a DB2 database, search for the text "TBS_32K," and replace it with the appropriate 32K table space name that you have configured.
Execute the script.
Run the ddl_7961.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Follow this procedure to migrate data into the upgraded data warehouse.
To migrate data into the upgraded data warehouse:
Run the 796_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order they appear:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_ORAR12 and execute the following workflows in the order they appear:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Run the 7961_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Section A.3.1, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Section A.3.3, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Section A.3.2, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute the first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Run the 7961_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.
To verify the data migrated successfully:
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:
7961_UPGRADE_DROP_INDEXES.sql
7961_UPGRADE_PRE_CTL_SCRIPT.sql
796_UPGRADE_PRE_DIMENSION.sql
796_UPGRADE_POST_SCRIPT.sql
7961_UPGRADE_POST_SCRIPT.sq
Check the log files for the ddl_7961.ctl and Upgrade_796.ctl scripts that you ran using the DDLimp command to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
After verifying the data was migrated successfully into the upgraded data warehouse, follow this procedure to reset refresh dates.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_TO_796_ORA11i and execute the RESET_DAC_REFRESH_DATES workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_TO_796_ORAR12 and execute the RESET_DAC_REFRESH_DATES workflow.
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the table FND_LOOKUP_VALUES, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.6 to release 7.9.6.1.
Note:
This procedure is applicable only if you are using an Oracle database for your data warehouse database.
This section includes the following procedures:
Section 6.9.7.1, "Upgrading the Data Warehouse Schema to Version 7.9.6.1"
Section 6.9.7.2, "Migrating Data into the Upgraded Data Warehouse"
Follow this procedure to upgrade the data warehouse schema to version 7.9.6.1.
To upgrade the data warehouse schema to version 7.9.6.1:
Run the UPGRADE_7961.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 796ORA_TO_7961ORA_UPGRADE_PRE_CTL_SCRIPT.sql. This file is located in the folder OracleBI\dwrep\Upgrade\DbScripts\Oracle.
Run the ddl_7961.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Follow this procedure to migrate data into the upgraded data warehouse.
To migrate data into the upgraded data warehouse:
Run the 7961_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Oracle.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Section A.3.1, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Section A.3.3, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Section A.3.2, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute the first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Run the 7961_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Oracle.
Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.
To verify the data migrated successfully:
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:
7961_UPGRADE_DROP_INDEXES.sql
7961_UPGRADE_PRE_CTL_SCRIPT.sql
7961_UPGRADE_PRE_DIMENSION.sql
7961_UPGRADE_POST_SCRIPT.sql
Check the log files for the ddl_7961.ctl and Upgrade_7961.ctl scripts, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.6.1 to 7.9.6.2.
This section contains the following procedures:
Section 6.9.8.1, "Upgrading the Data Warehouse Schema to Version 7.9.6.2"
Section 6.9.8.2, "Migrating Data into the Upgraded Data Warehouse"
Follow this procedure to upgrade the data warehouse schema to version 7.9.6.2.
To upgrade the data warehouse schema to version 7.9.6.2:
Generate the scripts to drop and create indexes. For instructions, see Section B.1, "Generating Scripts to Drop and Create Indexes for Upgrading to Release 7.9.6.2."
Run the script to drop all indexes. For instructions, see Section B.2, "Running the Script to Drop All Indexes for Upgrading to Release 7.9.6.2."
If you are using a SQL Server database, run the 7962_UPGRADE_PRE_UPG_CTL_SCRIPT.sql file. This file is located in OracleBI\dwrep\Upgrade\DbScripts\SQLServer.
Run the UPGRADE_7962.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 7962_UPGRADE_PRE_CTL_SCRIPT.sql. This file is located in the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the DDL_7962.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files.".
Follow this procedure to migrate data into the upgraded data warehouse.
To migrate data into the upgraded data warehouse:
Run the 7962_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Appendix A, "Setting the COGS Fact Mapping for Oracle EBS R12."
Run the script to create ETL indexes. For instructions, see Section B.3, "Running the Scripts to Create ETL and Query Indexes for Upgrading to Release 7.9.6.2."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute the first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Run the script to create Query indexes. For instructions, see Section B.3, "Running the Scripts to Create ETL and Query Indexes for Upgrading to Release 7.9.6.2."
Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.
To verify the data migrated successfully:
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:
dropallindexes.sql
7962_UPGRADE_PRE_CTL_SCRIPT.sql
7962_UPGRADE_PRE_DIMENSION.sql
createETLIndexes.sql
createQueryIndexes.sql
Check the log files for the ddl_7962.ctl and Upgrade_7962.ctl scripts, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.6.2 to 7.9.6.3.
This section contains the following procedures:
Section 6.9.9.1, "Upgrading the Data Warehouse Schema to Version 7.9.6.3"
Section 6.9.9.2, "Migrating Data into the Upgraded Data Warehouse"
Follow this procedure to upgrade the data warehouse schema to version 7.9.6.3.
To upgrade the data warehouse schema to version 7.9.6.3
Generate the scripts to drop and create query indexes. For instructions, see Section B.4, "Generating Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3."
Run the script to drop query indexes. For instructions, see Section B.5, "Running the Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3."
Note: You must drop the query indexes before running the scripts to upgrade the schema.
If you are using a SQL Server database, run the 7963_UPGRADE_PRE_UPG_CTL_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\SQLServer.
Run the UPGRADE_7963.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 7963_UPGRADE_PRE_CTL_SCRIPT.sql script. This file is located in the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the DDL_7963.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Follow this procedure to migrate data into the upgraded data warehouse.
To migrate data into the upgraded data warehouse:
Run the 7963_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the 7963_UPGRADE_DROP_ETLINDICES.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12x, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Run the 7963_UPGRADE_CREATE_ETLINDICES.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Run the createQueryIndices.sql script. For instructions, see Section B.5, "Running the Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.3."
Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.
To verify the data migrated successfully:
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:
7963_UPGRADE_PRE_CTL_SCRIPT.sql
7963_UPGRADE_PRE_DIMENSION.sql
7963_UPGRADE_DROP_ETLINDICES.sql
7963_UPGRADE CREATE_ETLINDICES.sql
dropQueryIndices.sql
createQueryIndices.sql
Check the log files for the ddl_7963.ctl and Upgrade_7963.ctl scripts, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Perform the procedures in this section to upgrade from Oracle BI Applications release 7.9.6.3 to 7.9.6.4.
This section contains the following procedures:
Section 6.9.10.1, "Upgrading the Data Warehouse Schema to Version 7.9.6.4"
Section 6.9.10.2, "Migrating Data into the Upgraded Data Warehouse"
Section 6.9.10.3, "Verifying the Data Migrated Successfully"
Follow this procedure to upgrade the data warehouse schema to version 7.9.6.4.
To upgrade the data warehouse schema to version 7.9.6.4:
If you are using a DB2 database, run siebproc.sql. This file is located in OracleBI\dwrep\siebproc\db2udb.
Generate the scripts to drop and create query indexes. For instructions, see Section B.6, "Generating Scripts to Drop and Create Query and ETL Indexes for Upgrading to Release 7.9.6.4."
Run the script to drop query indexes. For instructions, see Section B.7, "Running the Scripts to Drop and Create Query Indexes for Upgrading to Release 7.9.6.4."
Note: You must drop the query indexes before running the scripts to upgrade the schema.
If you are using a SQL Server database, run the 7964_UPGRADE_PRE_UPG_CTL_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\SQLServer.
Run the UPGRADE_7964.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Run the 7964_UPGRADE_PRE_CTL_SCRIPT.sql script. This file is located in the folder OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the DDL_7964.ctl script. For instructions, see Appendix E, "Using the DDLimp Utility to Run CTL Files."
Follow this procedure to migrate data into the upgraded data warehouse.
To migrate data into the upgraded data warehouse:
Run the 7964_UPGRADE_PRE_DIMENSION_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the script to drop ETL indexes. For instructions, see Section B.8, "Running the Scripts to Drop and Create ETL Indexes for Upgrading to Release 7.9.6.4."
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12x, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are upgrading the Oracle Human Resource Analytics Workforce module, you must perform a full load of data. To complete this step you will set the refresh dates to NULL for the Workforce-related tables.
Log into DAC.
Go to the Physical Data Sources tab in the Setup view.
In the top pane window, select the appropriate data warehouse connection.
Go to the Refresh Dates subtab.
Reset the refresh date to Null for the following tables by clicking in the Refresh Date column, displaying the Date dialog, and clicking Null. Click Save for each record.
W_ORA_SUPV_STATUS_PS
W_ORA_WEVT_ASG_PS
W_ORA_WEVT_FTE_PS
W_ORA_WEVT_HDC_PS
W_ORA_WEVT_PERF_PS
W_ORA_WEVT_PTYP_PS
W_ORA_WEVT_SAL_PS
W_PSFT_POSN_HLDR_PS
W_PSFT_POSN_WRKR_PS
W_PSFT_SUPV_STATUS_PS
W_PSFT_SUPV_WRKR_PS
W_PSFT_WEVT_AGE_PS
W_PSFT_WEVT_IASG_PS
W_PSFT_WEVT_JOB_PS
W_PSFT_WEVT_PERF_PS
W_WRKFC_EVT_FS
W_WRKFC_EVT_F
W_WRKFC_EVT_MERGE_F
W_WRKFC_EVT_MONTH_F
W_WRKFC_EVT_POW_F
W_WRKFC_EVT_AGE_F
W_WRKFC_EVT_A
W_WRKFC_BAL_A
W_WRKFC_EVENT_GROUP_D
W_WRKFC_EVENT_TYPE_D
If you are upgrading the Oracle Human Resource Analytics Recruitment module, do the following:
Execute the following SQL statement to clean the data:
DELETE FROM W_RCRTMNT_EVENT_F WHERE RCRTMNT_EVENT_CLASS = 'JOB_RQSTN' AND DATASOURCE_NUM_ID IN <>
Run the following upgrade map worklet:
HumanResourceAnalytics_Recruitment_JobRequisitions_UPG7964
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Run the script to create ETL indexes. For instructions, see Section B.8, "Running the Scripts to Drop and Create ETL Indexes for Upgrading to Release 7.9.6.4."
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep). For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Base Informatica Repository."
Follow this procedure to verify that the data was migrated successfully into the upgraded data warehouse.
To verify the data migrated successfully:
Validate the data in the upgraded data warehouse.
Check whether any of the Informatica mapping log files stored in the <INFA_HOME>\server\infa_shared\Sesslogs directory indicates errors or failures.
Check whether the following scripts that you ran in the SQL client of the database failed or errored out while executing:
7964_UPGRADE_PRE_CTL_SCRIPT.sql
7964_UPGRADE_PRE_DIMENSION.sql
dropEtlIndices.sql
createEtlIndices.sql
dropQueryIndices.sql
createQueryIndices.sql
Check the log files for the ddl_7964.ctl and Upgrade_7964.ctl scripts, which you ran using the DDLimp command, to determine whether any of the runs failed.
If you did not detect any errors or failures in the steps above, then the data migration was successful.
Note:
Ignore any error messages that indicate tables or indexes could not be dropped from the database. These error messages are displayed when the specified objects do not exist in the database. This can occur because each source system container holds a subset of all data warehouse tables
If the data migration was successful, drop the tables that were created during the upgrade process, such as W_xxxx_x_79x, LKP_xxxx_x and 79x_xxxx_TMP.
This step frees the space occupied by these backup tables.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
If your data warehouse database platform is Teradata, you can upgrade to Oracle BI Applications version 7.9.6.4 from Oracle BI Applications 7.9.x.
This section includes the following topics:
Section 6.10.1, "Upgrading Oracle BI Applications From Version 7.9.0 to 7.9.6.4"
Section 6.10.2, "Upgrading Oracle BI Applications From Version 7.9.3 to 7.9.6.4"
Section 6.10.3, "Upgrading Oracle BI Applications From Version 7.9.4 to 7.9.6.4"
Section 6.10.4, "Upgrading Oracle BI Applications From Version 7.9.5 to 7.9.6.4"
Section 6.10.5, "Upgrading Oracle BI Applications From Version 7.9.5.1 to 7.9.6.4"
Section 6.10.6, "Upgrading Oracle BI Applications From Version 7.9.6.1 to 7.9.6.4"
Section 6.10.7, "Upgrading Oracle BI Applications From Version 7.9.6.2 to 7.9.6.4"
Section 6.10.8, "Upgrading Oracle BI Applications From Version 7.9.6.3 to 7.9.6.4"
Follow this procedure to upgrade from Oracle BI Applications version 7.9.0 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the following scripts:
793_UPGRADE_TMP_TABLES.sql
794_UPGRADE_TMP_TABLES.sql
795_UPGRADE_TMP_TABLES.sql
7951_UPGRADE_TMP_TABLES.sql
7961_UPGRADE_TMP_TABLES.sql
7962_UPGRADE_TMP_TABLES.sql
7963_UPGRADE_TMP_TABLES.sql
7964_UPGRADE_TMP_TABLES.sql
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
Edit and execute the 793_RENAME_AND_EXCEPTIONS.sql script.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 793_RENAME_AND_EXCEPTIONS.sql script.
Search for the following statements to locate columns with RENAME commands.
ALTER TABLE <Table_Name> RENAME <Column Name> TO <New Column Name>;
For all columns with RENAME commands, check to see whether any COLUMN STATISTICS are defined on them.
For all columns that have STATISTICS defined on them, back up the COLUMN STATISTICS definition and then drop them.
Execute the 793_RENAME_AND_EXCEPTIONS.sql script.
Recreate STATISTICS on any column on which they were dropped.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the following scripts:
794_UPDATE_DEFAULTS.sql
795_UPDATE_DEFAULTS.sql
7951_UPDATE_DEFAULTS.sql
7961_UPDATE_DEFAULTS.sql
7962_UPDATE_DEFAULTS.sql
7963_UPDATE_DEFAULTS.sql
7964_UPDATE_DEFAULTS.sql
Execute the 793_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 793_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 793_UPG_PARAMS.txt."
In Informatica Workflow Manager, navigate to the folder UPGRADE_790_to_793 and run the Update_Dimensions workflow, and then run the Update_Facts workflow.
If you are using the Oracle EBS 11.5.10 source system, navigate to the folder UPGRADE_790_to_793_ORA11510, and run the SIL_PurchaseAmount_Patch workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 794_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 794_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 794_UPG_PARAMS.txt."
In Informatica Workflow Manager, navigate to the folder UPGRADE_793_to_794_ORA11i and execute the UPGRADE_DIMENSIONS and UPGRADE_FACTS workflows.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 795_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 795_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 795_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7951_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository. For instructions, see Appendix D, "Running reset_infa_seq_gen.bat on Upgrade Informatica Repository."
Configure the Informatica parameter file 7951_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7951_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 796_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORAR12 and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7961_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the FND_LOOKUP_VALUES table, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle BI Applications version 7.9.3 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the following scripts:
794_UPGRADE_TMP_TABLES.sql
795_UPGRADE_TMP_TABLES.sql
7951_UPGRADE_TMP_TABLES.sql
7961_UPGRADE_TMP_TABLES.sql
7962_UPGRADE_TMP_TABLES.sql
7963_UPGRADE_TMP_TABLES.sql
7964_UPGRADE_TMP_TABLES.sql
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
Edit and execute the 794_RENAME_AND_EXCEPTIONS.sql script.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 794_RENAME_AND_EXCEPTIONS.sql script.
Search for the following statements to locate columns with RENAME commands.
ALTER TABLE <Table_Name> RENAME <Column Name> TO <New Column Name>;
For all columns with RENAME commands, check to see whether any COLUMN STATISTICS are defined on them.
For all columns that have STATISTICS defined on them, back up the COLUMN STATISTICS definition and then drop them.
Execute the 794_RENAME_AND_EXCEPTIONS.sql script.
Recreate STATISTICS on any column on which they were dropped.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the following scripts:
794_UPDATE_DEFAULTS.sql
795_UPDATE_DEFAULTS.sql
7951_UPDATE_DEFAULTS.sql
7961_UPDATE_DEFAULTS.sql
7962_UPDATE_DEFAULTS.sql
7963_UPDATE_DEFAULTS.sql
7964_UPDATE_DEFAULTS.sql
Execute the 794_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 794_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 794_UPG_PARAMS.txt."
In Informatica Workflow Manager, navigate to the folder UPGRADE_793_to_794_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 795_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 795_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 795_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7951_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7951_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7951_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 796_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORAR12 and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7961_Backup Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade repository.
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the FND_LOOKUP_VALUES table, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle BI Applications version 7.9.4 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the following scripts:
795_UPGRADE_TMP_TABLES.sql
7951_UPGRADE_TMP_TABLES.sql
7961_UPGRADE_TMP_TABLES.sql
7962_UPGRADE_TMP_TABLES.sql
7963_UPGRADE_TMP_TABLES.sql
7964_UPGRADE_TMP_TABLES.sql
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
Edit and execute the 795_RENAME_AND_EXCEPTIONS.sql script.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 795_RENAME_AND_EXCEPTIONS.sql script.
Search for the following statements to locate columns with RENAME commands.
ALTER TABLE <Table_Name> RENAME <Column Name> TO <New Column Name>;
For all columns with RENAME commands, check to see whether any COLUMN STATISTICS are defined on them.
For all columns that have STATISTICS defined on them, back up the COLUMN STATISTICS definition and then drop them.
Execute the 795_RENAME_AND_EXCEPTIONS.sql script.
Recreate STATISTICS on any column on which they were dropped.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the following scripts:
795_UPDATE_DEFAULTS.sql
7951_UPDATE_DEFAULTS.sql
7961_UPDATE_DEFAULTS.sql
7962_UPDATE_DEFAULTS.sql
7963_UPDATE_DEFAULTS.sql
7964_UPDATE_DEFAULTS.sql
Execute the 795_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 795_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 795_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_794_to_795_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7951_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7951_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7951_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 796_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Section A.3.3, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Section A.3.2, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORAR12 and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7961_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the table FND_LOOKUP_VALUES, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle BI Applications version 7.9.5 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the following scripts:
7951_UPGRADE_TMP_TABLES.sql
7961_UPGRADE_TMP_TABLES.sql
7962_UPGRADE_TMP_TABLES.sql
7963_UPGRADE_TMP_TABLES.sql
7964_UPGRADE_TMP_TABLES.sql
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
Edit and execute the 7951_RENAME_AND_EXCEPTIONS.sql script.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 7951_RENAME_AND_EXCEPTIONS.sql script.
Search for the following statements to locate columns with RENAME commands.
ALTER TABLE <Table_Name> RENAME <Column Name> TO <New Column Name>;
For all columns with RENAME commands, check to see whether any COLUMN STATISTICS are defined on them.
For all columns that have STATISTICS defined on them, back up the COLUMN STATISTICS definition and then drop them.
Execute the 7951_RENAME_AND_EXCEPTIONS.sql script.
Recreate STATISTICS on any column on which they were dropped.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the following scripts:
7951_UPDATE_DEFAULTS.sql
7961_UPDATE_DEFAULTS.sql
7962_UPDATE_DEFAULTS.sql
7963_UPDATE_DEFAULTS.sql
7964_UPDATE_DEFAULTS.sql
Execute the 7951_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7951_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7951_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_795_to_7951_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 796_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORAR12 and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7961_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt.
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the table FND_LOOKUP_VALUES, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle BI Applications version 7.9.5.1 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the following scripts:
7961_UPGRADE_TMP_TABLES.sql
7962_UPGRADE_TMP_TABLES.sql
7963_UPGRADE_TMP_TABLES.sql
7964_UPGRADE_TMP_TABLES.sql
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
Edit and execute the 7961_RENAME_AND_EXCEPTIONS.sql script.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 7961_RENAME_AND_EXCEPTIONS.sql script.
Search for the following statements to locate columns with RENAME commands.
ALTER TABLE <Table_Name> RENAME <Column Name> TO <New Column Name>;
For all columns with RENAME commands, check to see whether any COLUMN STATISTICS are defined on them.
For all columns that have STATISTICS defined on them, back up the COLUMN STATISTICS definition and then drop them.
Execute the 7961_RENAME_AND_EXCEPTIONS.sql script.
Recreate STATISTICS on any column on which they were dropped.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the following scripts:
7961_UPDATE_DEFAULTS.sql
7962_UPDATE_DEFAULTS.sql
7963_UPDATE_DEFAULTS.sql
7964_UPDATE_DEFAULTS.sql
Execute the 796_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 796_UPG_PARAMS.txt file. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, configure parameters specific to Oracle 11i source systems. For instructions, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle EBS 11i Source Systems."
If you are upgrading Oracle Financial Analytics, you need to configure specific parameters and update mappings. For more information, see Appendix A, "Setting Parameters and Mappings in 796_UPG_PARAMS.txt for Oracle Financial Analytics."
If you are upgrading Oracle Supply Chain and Order Management Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Supply Chain and Order Management Analytics."
If you are deploying Oracle Project Analytics, you need to configure specific parameters. For more information, see Appendix A, "Setting Parameters in 796_UPG_PARAMS.txt for Oracle Project Analytics."
Back up and truncate the table W_POSITION_DH.
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORA11i and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7951_to_796_ORAR12 and execute the following workflows in the order specified below:
UPGRADE_DIMENSIONS
UPGRADE_FACTS
RESET_DAC_REFRESH_DATES
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7961_Backup Tables.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7961_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7961_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_796_to_7961_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the table FND_LOOKUP_VALUES, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle Business Applications version 7.9.6.1 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the 7962_UPGRADE_TMP_TABLES.sql, 7963_UPGRADE_TMP_TABLES.sql, and 7964__UPGRADE_TMP_TABLES.sql scripts.
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the 7962_UPDATE_DEFAULTS.sql, 7963_UPDATE_DEFAULTS.sql, and 7964_UPDATE_DEFAULTS.sql scripts.
Execute the 7962_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7962_UPG_PARAMS.txt. Appendix A, "Setting Parameters in 7962_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Configure application-specific parameters and update mappings. For instructions, see Section A.3.4, "Setting Parameters and Mappings in 7962_UPG_PARAMS.txt for Oracle Financial Analytics."
Make sure specific indexes are created. For instructions, see Appendix C, "Creating Indexes for Oracle Financial Analytics."
If you are using Oracle EBS R12, you need to configure specific parameters and update mappings for COGS. For instructions, see Section A.3.4.4, "Setting the COGS Fact Mapping for Oracle EBS R12."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7961_to_7962_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7963_UPG_PARAMS.txt. For instructions, see Section A.1.8, "Setting Parameters in 7963_UPG_PARAMS.txt."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are upgrading Oracle Human Resources Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Human Resources Analytics."
If you are upgrading Oracle Procurement and Spend Analytics, set the parameters as specified in Appendix A, "Setting Parameters in 7963_UPG_PARAMS.txt for Oracle Procurement and Spend Analytics on Oracle EBS 11i and 12 Source Systems."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
In DAC, do the following:
Navigate to the Setup view, and click the Physical Data Sources tab.
In the top pane list, select DataWarehouse. (If you customized the name of the connection for the data warehouse, select the appropriate connection for the data warehouse database.)
Click the Refresh Dates subtab (in the lower pane).
Query for the table FND_LOOKUP_VALUES, and set the value in the Refresh Date column to NULL.
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle Business Applications version 7.9.6.2 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the 7963_UPGRADE_TMP_TABLES.sql and 7964_UPGRADE_TMP_TABLES.sql script.
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the <version>_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the 7963_UPDATE_DEFAULTS.sql and 7964_UPDATE_DEFAULTS.sql scripts.
Execute the 7963_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
If you are upgrading Oracle Financial Analytics, do the following:
Set the parameters as specified in Appendix A, "Setting Parameters for All Source Systems."
If you are using Oracle EBS 11.5.10 family pack OIE.I or OIE.J, set the parameters as specified in Appendix A, "Setting Parameters Specific to Oracle EBS 11.5.10 Family Pack OIE.I and OIE.J."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7962_to_7963_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Upgrade geography-related attributes if necessary for your environment:
Determine whether a geography-related upgrade is necessary by executing the following query:
SELECT COUNT(1) as row_count,'0' as X_CUSTOM FROM W_GEO_D_7963 WHERE COUNTRY LIKE '% 'OR COUNTRY LIKE ' %'OR STATE_PROV LIKE '% 'OR STATE_PROV LIKE ' %'OR CITY LIKE '% 'OR CITY LIKE ' %'OR ZIPCODE LIKE '% 'OR ZIPCODE LIKE ' %'
If the query returns a count greater than zero, run the 7963_UPGRADE_POST_SCRIPT.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
If the query returns a count of zero, you do not need to run the 7963_UPGRADE_POST_SCRIPT.sql script.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
Follow this procedure to upgrade from Oracle Business Applications version 7.9.6.3 to version 7.9.6.4 if your data warehouse database platform is Teradata.
To upgrade the data warehouse schema and migrate data for a Teradata database:
Execute the 7964_UPGRADE_TMP_TABLES.sql script.
Open the SQL client for your database type.
Navigate to the folder OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Open the 7964_UPGRADE_TMP_TABLES.sql file, and copy the contents into the SQL client.
Execute the script.
The teradata.missing.defaults.properties file contains global default column values for data warehouse tables with NOT NULL columns for which default values are not specified in DAC. You can change the default values specified in the teradata.missing.defaults.properties file to match your environment. The file is located in \bifoundation\dac\conf\sqlgen\sql\teradata.
Generate upgrade scripts from DAC.
Log into DAC.
From the menu bar, select Tools, then ETL Management, and then Configure.
In the Sources dialog box, select Teradata as the target data warehouse database platform, and select the appropriate source data warehouse database platform.
The Configuration tab of the Data Warehouse Configuration Wizard is active.
Select "Generate upgrade statements for Data Warehouse Tables," and click Next.
The Data Warehouse SQL tab is active.
Do the following:
- Select the appropriate physical data source name for the Teradata database.
- In the container field, enter a custom container name for which you want to generate upgrade scripts. Alternatively, leave the field blank to generate upgrade scripts for all containers.
The Data Warehouse Configuration Wizard generates the following files and stores them in \bifoundation\DAC\conf\sqlgen\sql\teradata:
upgrade-regular.sql. This file contains scripts that DAC has verified are necessary to upgrade the data warehouse schema. The scripts handle new or altered tables and columns. For example, they may increase the size of simple data types or change a column from null to not null.
upgrade-questionable.sql. This file contains scripts that DAC suggests may be required to upgrade the data warehouse schema. These scripts are intended to be an aid in determining upgrade requirements and must be reviewed by a Teradata DBA and corrected if necessary.
upgradedwtables_sql.log. This file is the log file that corresponds to the upgrade-regular.sql file. It is for information purposes only.
upgrade-issues.log. This file is the log file that corresponds to the file upgrade-questionable.sql. It is for information purposes only.
Open the upgrade-regular.sql and upgrade-questionable.sql files, and search for the following statement to locate columns that have been added or changed.
ALTER TABLE <Table Name> ADD <Column Name><attributes>;
For all columns with the ADD command, check to see whether any columns exist already, and if they do, check whether they have STATISTICS defined on them.
For all columns that have STATISTICS defined on them, back up the column definition and then drop the STATISTICS.
Execute the scripts in the upgrade-regular.sql file.
Review the scripts in the upgrade-questionable.sql file. Edit the scripts as needed, and execute them.
Recreate STATISTICS on any column on which they were dropped.
Execute the 7964_UPDATE_DEFAULTS.sql scripts.
Execute the 7964_Backup_Tables.sql script. This file is located in OracleBI\dwrep\Upgrade\DbScripts\Teradata.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the Upgrade Informatica Repository.
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Upgrade Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Upgrade Repository for DW on Teradata and Repository on MSSQL Server."
Configure the Informatica parameter file 7964_UPG_PARAMS.txt. For instructions, see Appendix A, "Setting Parameters in 7964_UPG_PARAMS.txt."
If you are using Oracle EBS 11i, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORA11i and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
If you are using Oracle EBS R12, R12.1.1, or R12.1.2, in Informatica Workflow Manager, navigate to the folder UPGRADE_7963_to_7964_ORAR12 and execute first the UPGRADE_DIMENSIONS workflow, and then the UPGRADE_FACTS workflow.
Run the 7964_UPGRADE_POST_SCRIPT.sql. This file is located in OracleBI\dwrep\Upgrade\DbScripts\<database type>.
Use the reset_infa_seq_gen.bat script to initialize the Informatica sequence generator for incremental runs on the base Informatica Repository (for example, Oracle_BI_DW_Base.rep).
If your Informatica Repository runs on an Oracle database, for instructions, see Appendix D, "Running reset_infa_seq_gen_ORA.bat on Base Repository for DW on Teradata and Repository on Oracle."
If your Informatica Repository runs on an MSSQL Server database, for instructions, see Appendix D, "Running reset_infa_seq_gen_MSSQL.bat on Base Repository for DW on Teradata and Repository on MSSQL Server."
Once the data migration steps above are complete, you can delete the Upgrade repository to avoid any accidental use or confusion.
In the Informatica Administrator, select the Upgrade repository service.
In the General Properties area of the Properties tab, click Edit.
Make sure the operating mode of the repository service is set to Exclusive.
Click OK.
Choose Actions, and then click Delete Contents.
In the Delete contents for <repository name> dialog, enter the repository username and password, and then click OK.
The Oracle BI Applications 7.9.6.4 installer contains different policy stores for different languages. The default policy store that is deployed during the installation is in English. If you want to configure the policy store for any of the other available languages, perform the procedure below.
To configure the policy store for languages other than English:
Stop the Oracle BI Services.
Copy the system-jazn-data_<LN>.xml file from $ORACLE_BI_HOME\biapps\admin\provisioning\localization\ to $DOMAIN_HOME\config\fmwconfig\.
For example, to implement the policy store for French, you would copy the file system-jazn-data_fr.xml into the \fmwconfig directory.
Back up the existing system-jazn-data.xml file in $DOMAIN_HOME\config\fmwconfig\.
Rename $DOMAIN_HOME\config\fmwconfig\system-jazn-data_<LN>.xml to system-jazn-data.xml.
Start Oracle BI Services.
In this procedure, you will import the standard (out-of-the-box) Oracle BI Applications version 7.9.6.4 LDIF file into the WebLogic Server embedded LDAP server.
To import the Oracle BI Applications version 7.9.6.4 LDIF file:
Log in to the WebLogic Server Administration Console using the URL http://<host name>:7001/console.
Select the name of the security realm into which the LDIF file is to be imported (for example, myrealm).
Select Providers and then the type of provider into which the LDIF file is to be imported (for example, Providers, and then Authentication).
Select the security provider in which the LDIF file is to be imported and select Migration, and then select Import.
Click Save.
Use the Oracle BI Enterprise Edition Upgrade Assistant to upgrade the following repositories and web catalog from the Oracle BI Enterprise Edition 10g format to the 11g format:
The standard (out-of-the-box) Oracle BI Repository. This repository is the one that you originally received from Oracle as part of your current Oracle BI Applications installation (the version from which you are upgrading). Standard repositories from previous releases are available in the installation folder $ORACLE_BI_HOME\biapps\upgrade.
The Oracle BI Repository you are currently using (the repository deployed in production).
The Presentation Catalog you are currently using (the catalog deployed in production).
For instructions on using the Upgrade Assistant, see Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.
Note:
Your current policy store will be merged with the Oracle BI Applications release 7.9.6.4 policy store while upgrading the Oracle BI repository from the 10g format to the 11g format. The final policy store will be the super set of Oracle BI Applications release 7.9.6.4 policy store and the custom and current policy information from the 10g repository.
Also, your current users and groups from the repository will be merged with the Oracle BI Applications release 7.9.6.4 identity store while upgrading the Oracle BI repository from the 10g format to the 11g format. The final identity store will be the super set of the Oracle BI Applications release 7.9.6.4 identity store and the identity store information (users and groups) from the 10g repository.
If you require the policy store to be in a language other than English, perform the steps in Section 6.11, "Localizing the Policy Store."
Note:
Before you begin this procedure, you must have already upgraded the Oracle BI repository you are currently using and the standard (out-of-the-box) Oracle BI repository version 7.9.x (the version from which you are upgrading) to the Oracle BI Enterprise Edition 11g format. For more information, see Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version."
This process merges your customizations of a prior release of the Oracle BI repository with the new release of the Oracle BI repository. Before you begin this process, make sure you have backed up and renamed your existing repository.
To upgrade the repository, perform the following tasks:
The tasks in this section refer to multiple releases of the Oracle BI repository. Table 6-1 provides the names and descriptions of the repositories used in the examples in this section.
Table 6-1 Names of Analytics Repositories used in Examples
| Name of Repository | Description |
|---|---|
|
OracleBIAnalyticsApps_79x.rpd |
The standard Oracle BI repository for the version you are upgrading from. Note: Standard repositories from previous releases are available in the folder $ORACLE_BI_HOME\biapps\upgrade. |
|
OracleBIAnalyticsApps.rpd |
The standard Oracle BI repository for the version you are upgrading to. |
|
Customer_OracleBIAnalyticsApps.rpd |
The Oracle BI repository that contains your customizations for the version you are upgrading from. |
|
Merged_Repository_OracleBI.rpd |
The Oracle BI repository that contains your customizations for the version you are upgrading to. |
Follow this procedure to prepare for the repository upgrade.
To prepare for the Analytics repository upgrade:
Set up a directory for the merge process, such as \OracleBIUpgrade, and create the following subfolders:
Original
AfterEqualize
AfterMerge
AfterManualWork
AfterRegressions
Copy the following repositories to the folder \OracleBIUpgrade\Original:
The standard repository that you upgraded to the 11g format in Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version" (for example, OracleBIAnalyticsApps_79x.rpd).
The production repository that you also upgraded to the 11g format in Section 6.13 (for example, Customer_OracleBIAnalyticsApps.rpd.
The Oracle BI repository from the latest installation (for example, OracleBIAnalyticsApps.rpd)
If, in your current environment, you are running Oracle BI Applications for one or more modules using a Oracle BI repository in which you extracted the corresponding projects for the modules from the standard Oracle BI repository file you received from the previous release, you need to extract the same projects from the OracleBIAnalyticsApps_79x.rpd file and use this as your original repository. (If you have the original repository that you extracted during the last upgrade, you can use it as the original repository file.) This will prevent you from losing any new metadata you would like to add in this upgrade.
Also, if you customized the Oracle BI repository by trimming a large number of objects and you would like to get those objects back during the current upgrade, you need to trim the OracleBIAnalyticsApps_79x.rpd file in the same way and use the modified version as the original repository file. This will prevent you from losing any new metadata you would like to add in this upgrade.
The Merge feature in the Administration Tool relies on a change detection algorithm to determine the changes that need to be made to upgrade repositories correctly. For the algorithm to work correctly, it has to determine which objects in the three repositories (for example, OracleBIAnalyticsApps_79x.rpd, OracleBIAnalyticsApps.rpd, and Customer_OracleBIAnalyticsApps.rpd) are equivalent.
The point of this step is to determine for every object in the OracleBIAnalyticsApps.rpd and the Customer_OracleBIAnalyticsApps.rpd whether it is coming from the OracleBIAnalyticsApps_79x.rpd.
Equivalence between objects is established using the Administration Tool's Equalize feature. The file that you specify in the Output option (-O) is the only file that is modified during the equalization process.
The Equalize feature has several mechanisms for determining whether an object in two different repositories is semantically the same:
Fully Qualified Name. If an object in one repository has the same fully qualified name as another object of the same class in another repository, then the two objects are declared equal.
Simple String Substitution. Equivalence can be declared between two objects of the same class in two repositories whose only difference is that some key characters in their names differ. The equalizerpds executable file ignores those characters while checking fully qualified names. For example, "Core"."W_DAY_D" might be considered equivalent to "Core"."W DAY D" if the characters "_" and " " have been declared as equivalent.
Rename File. When none of the preceding rules are applicable, equivalence can be manually declared using a script as input to the equalizerpds executable file. Oracle ships the rename files (MAP) for the major releases. The files are located in the $ORACLE_BI_HOME\biapps\upgrade folder. You can also create your own rename files for customizations not covered in the files that Oracle ships. You can open and edit the rename files in Microsoft Excel.
Table 6-2 provides a list of the available MAP files and the Siebel Analytics or Oracle BI Applications release version associated with the file.
Table 6-2 Rename MAP Files to Be Used for Various Releases
| Siebel Analytics / Oracle Business Intelligence Applications Release Version (Upgrading from DW Version) | Rename MAP File to Be Used |
|---|---|
|
Siebel Business Analytics Applications 7.0.x |
Not available |
|
Siebel Business Analytics Applications 7.5.x |
Not available |
|
Siebel Business Analytics Applications 7.7.x (with Siebel CRM OLTP Pre-7.7.0) |
Rename77-7963.map |
|
Siebel Business Analytics Applications 7.7.x (with Siebel CRM OLTP 7.7.0) |
Rename771-7963.map |
|
Siebel Business Analytics Applications 7.8.2 and all 7.8.x versions before this release |
Rename782-7963.map |
|
Siebel Business Analytics Applications 7.8.3 and all 7.8.x versions after this release |
Rename783-7963.map |
|
Oracle BI Applications 7.9.0 |
Rename79x-7963.map |
|
Oracle BI Applications 7.9.1 |
Rename79x-7963.map |
|
Oracle BI Applications 7.9.2 |
Rename79x-7963.map |
|
Oracle BI Applications 7.9.3 |
Rename793to7963.map |
|
Oracle BI Applications 7.9.4 |
Rename794to7963.map |
|
Oracle BI Applications 7.9.5 |
Rename79x-7963.map |
|
Oracle BI Applications 7.9.5.1 |
Rename7951to7963.map |
|
Oracle BI Applications 7.9.5.2 |
Rename7951to7963.map |
|
Oracle BI Applications 7.9.6 |
Rename79x-7963.map |
|
Oracle BI Applications 7.9.6.2 |
rename7963to7964.map Not required for upgrades from 7.9.6.2 to 7.9.6.3. |
|
Oracle BI Applications 7.9.6.3 |
rename7963to7964.map |
The syntax of the equalizerpds command is as follows:
equalizerpds.exe [-B [password1]] -C base_repository_name [-E [password2]] -F repository2_name [-J udml_utf8_file_name_equalization] [-O ouput_repository_name] [-Y equalStringSet] -Y Treat the characters as equals. /? Display this usage information and exit.
To equalize a repository:
Copy the appropriate MAP file from the $ORACLE_BI_HOME\biapps\upgrade folder into the folder where you will execute equalizerpds.exe, for example, \OracleBIUpgrade\Original.
Run equalizerpds.exe to equalize the repository from the latest installation (for example, OracleBIAnalyticsApps.rpd) with the original repository (for example, OracleBIAnalyticsAppss_793.rpd). An example of the equalizerpds command is as follows:
equalizerpds -B SADMIN -C \\OracleBIUpgrade\Original\OracleBIAnalyticsApps_791.rpd -D Administrator -E SADMIN -F \\OracleBIUpgrade\Original\OracleBIAnalyticsApps.rpd -O \\OracleBIAnalyticsUpgrade\AfterEqualize\OracleBIAnalyticsApps.rpd -X -J rename7x-79.map
The MAP files are located in the $ORACLE_BI_HOME\biapps\upgrade folder.
If the equalizerpds.exe executable file runs correctly, no errors are returned.
Run equalizerpds.exe to equalize your customized repository (for example, Customer_OracleBIAnalyticsApps.rpd) with the original repository (for example, OracleBIAnalyticsApps_79x.rpd). An example of the equalizerpds command is as follows:
equalizerpds -B SADMIN -C \\OracleBIUpgrade\Original\OracleBIAnalyticsApps_791.rpd -E SADMIN -F \\OracleBIUpgrade\Original\Customer_OracleBIAnalyticsApps.rpd -O \\OracleBIUpgrade\AfterEqualize\Customer_OracleBIAnalyticsApps.rpd
The execution of equalizerpds that equalizes the customer repository with the original repository does not use the rename file.
Make sure that the original repository is copied unchanged into its new location so that after running the script, all three repositories are contained within the \OracleBIUpgrade\AfterEqualize directory.
To verify the process completed successfully, compare the size of the repositories. The output repository (-O) should be close to the same size as the repository you equalized (-F).
Follow this procedure to compare your existing repository with the new version to which you are upgrading.
To compare the Oracle BI repositories:
Use the Administration Tool's Compare Repositories feature to analyze the differences between your existing repository and the new version of the repository to which you are upgrading. Note where elements have been created, removed, or changed in the new version. Consider whether you can use the new metadata and retire customizations you made in the existing repository.
For instructions on how to use the Administration Tool's Compare Repositories feature, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
In this procedure, you execute the main algorithm to upgrade the repository. For more information on merging the repositories, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
To merge versions of the Oracle BI repositories:
Copy the three repositories (for example, OracleBIAnalyticsApps_79x.rpd, OracleBIAnalyticsApps.rpd, and Customer_OracleBIAnalyticsApps.rpd) to the AfterMerge folder.
Open the repository from the latest installation (for example, OracleBIAnalyticsApps.rpd) in the \OracleBIUpgrade\AfterMerge folder.
From the Administration Tool menu bar, select File, then select Merge.
Select the Merge Type option Full Repository Merge.
In the Select Original Repository dialog box, select the original repository (for example, OracleBIAnalyticsApps_79x.rpd).
Enter the password for the original repository.
In the Select Modified Repository dialog box, select the repository that contains the customizations you made to the previous version of the Analytics repository.
Enter the password for the modified repository.
In the Saved Merged Repository as dialog box, enter the name for the merged repository, for example, Merged_Repository_OracleBIAnalyticsApps.rpd.
The new repository will contain the final results of the merge.
Select the Equalize during merge check box.
Click Next.
In the Decision drop-down list, select the action you want to take regarding the repository change, or accept the default action.
To locate subsequent rows with empty Decision fields, click the Decision header cell.
When all rows have a value in the Decision field, the Finish button is enabled.
Select the Check consistency of the merged RPD check box.
Click Finish.
This process can take up to 40 minutes, depending on the size of the repositories you are working with. A message will alert you when the merge is complete.
The number of errors returned by the consistency check is an indication of how successful the merge process was. If you receive many errors, for example, over 300, you should analyze the reason for the errors. If the merge process failed to recognize that two objects are the same, you may need to edit the rename file if the object is in the Current repository, or add your own rename file if you have renamed many of the objects and the upgrade engine failed to relate them to the original objects.
You also may need to change the actions you selected in the Decision drop-down list before rerunning the merge. This could save you time by reducing the number of errors that you will need to fix manually.
Once you are satisfied with the results of the merge, you should fix the remaining errors manually. It is important that you fix all errors before moving on to the next step. This repository serves as the input for the next stage.
You should also check that all of your customized objects are present and that no duplicate physical tables were introduced. To check for duplicate tables, search for physical tables using a query such as:
where name like '*#1'
Copy the repository to the folder \OracleBIUpgrade\AfterManualWork.
Note:
Before you begin this procedure, you must have already upgraded your current (custom) catalog to the Oracle BI Enterprise Edition 11g format. For more information, see Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version."
You need to perform the following tasks to complete the presentation merge process.
These procedures use the following terminology to identify the various presentation catalogs you will use in the merge process:
The original presentation catalog. This catalog is the output from the step you performed in Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version." It is the version of the unmodified presentation catalog you received with the Oracle BI Applications release you are upgrading from that you upgraded to the 11g format.
The new presentation catalog. This is the standard Oracle BI Applications 7.9.6.4 presentation catalog that is installed with Oracle BI Applications.
The current presentation catalog. This catalog is the output from the step you performed in Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version." It is the presentation catalog currently in use at your organization that you upgraded to the 11g format.
Caution:
In releases of Oracle BI Applications previous to 7.9, the Presentation Catalog (formerly known as the Siebel Analytics Web Catalog) was stored in a single file rather than in a directory structure of individual files. If you have a previous version of the Presentation Catalog, you will need to convert it to the new format. For more information about how to convert the Presentation Catalog to the new format, see the Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence
Before you upgrade and merge your current Presentation Catalog with the new Presentation Catalog, determine which of the existing content you want to keep and which new content you want to incorporate. Review your existing Presentation Catalog and determine the usage patterns of reports and dashboards. Note that some of the preconfigured content in the existing catalog may appear in the new version in a redesigned format. In addition, the new version includes completely new content. After you have decided the content that is to make up your enterprise Presentation Catalog, trim the input catalogs using the Catalog Manager. For information on trimming catalogs, see Oracle Business Intelligence Presentation Services Administration Guide.
Note:
Before you begin this procedure, you must have already upgraded your current (custom) catalog to the Oracle BI Enterprise Edition 11g format. For more information, see Section 6.13, "Upgrading the Oracle BI Repository and Presentation Catalog to the Oracle BI Enterprise Edition 11g Version."
Follow the instructions in this section to merge the Oracle BI Applications version 7.9.6.4 presentation catalog with the catalog you are currently using. This procedure uses the Oracle BI Enterprise Edition Catalog Manager. High-level steps for the merge process are provided in this section. For detailed instructions on using the Catalog Manager, see the chapter "Configuring and Managing the Oracle BI Presentation Catalog," in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).
To merge the Oracle BI Applications version 7.9.6.4 presentation catalog with the current (custom) catalog:
Start the Catalog Manager, and open your current (custom) presentation catalog in offline mode.
For instructions, see the section "Starting Catalog Manager and Opening Catalogs," in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).
Start another instance of the Catalog Manager, and open the Oracle BI Applications version 7.9.6.4 (out-of-the-box) presentation catalog in offline mode.
Copy and paste the custom objects from your current presentation catalog into the Oracle BI Applications version 7.9.6.4 (out-of-the-box) presentation catalog.
For instructions, see the section "Working with Objects in Catalog Manager," in the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).
If you created custom catalog groups that are not in the version 7.9.6.4 presentation catalog, you need to convert the custom catalog groups to application roles in order to maintain consistency with the version 7.9.6.4 presentation catalog.
To convert custom catalog groups to application roles, run the following command:
runcat.cmd/runcat.sh -cmd replaceAccountInPermissions -old <Catalog Group Name> -oldType group -new <App Role Name> -newType role -offline <catalog path>
For example:
runcat.cmd -cmd replaceAccountInPermissions -old "AP Analyst" -oldType group -new "AP Analyst" -newType role -offline c:/SampleWebcat
This command replaces a specified account with another in all catalog object ACLs and privileges in the presentation catalog, entirely in the offline mode. If an entirely new application role is specified as the replacement account, then it is necessary to refresh the GUIDs in the presentation catalog before it can be used.
Note: If the specified replacement user or group is not already present in the presentation catalog 11g Release 1 (11.1.1) already, then this operation will fail.
For more information, see the Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1).
Note:
Before you perform this step, you must first migrate the data into the upgraded data warehouse.
The Presentation Catalog upgrade functionality does not automatically carry over object permissions; therefore, you should review the Presentation Catalog object permissions before you perform this step.
This step ensures that the upgraded reports and the new preconfigured reports are functional and render correct results within the new, merged Presentation Catalog. This step is typically performed by visually inspecting the final results of the complete end-to-end upgrade process.
For upgraded reports, the preferred approach for comparison purposes is to have side-by-side environments, and have users review specific dashboard content between the two environments. Examine not only the look and feel of the application but also the data contained in the reports to make sure the content remains the same. It is recommended that you request users to use various elements of the user interface to validate results, such as global prompts, column selectors, report filters, drills, and navigations, as they normally do on a day-to-day basis.
Also review the overall visibility and administrative settings in the new Presentation Catalog to ensure they are correct. Pay careful attention to the visibility rules that are established for any content that was migrated during the upgrade. You might have to manually adjust these settings.
In performing a regression test for the repository merge, the objective is to collect a set of logical SQL statements that are used for reports and to verify that they continue to work with the new metadata. For this purpose, it is recommended that you perform the following procedure.
To perform regression testing:
Run the reports that are necessary to include in the regression suite. These reports might be a subset of the reports in the Presentation Catalog.
Collect the logical SQL generated in the previous step. You can do this using Usage Tracking or by parsing the query log file.
For information about Usage Tracking, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
Execute the logical SQL against the old repository using the command line utility nQCmd.exe located in $ORACLE_BI_HOME\bifoundation\server\bin, and save the results to a file.
For information about the nQCmd.exe utility, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.
Edit the logical SQL test scripts to account for the name changes or modifications resulting from the upgrade.
Execute the edited logical SQL against the merged repository, and save the results.
Compare the results from the steps above and try to explain the differences. If it is determined that these differences are due to the upgrade process, then you have to correct them manually.
This repository now contains the merged content from the new OracleBIAnalyticsApps.rpd and the production repository.
|
 Copyright © 2010, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
