| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
The Metadata Loader (MDL) enables you to populate a new repository as well as transfer, update, or restore a backup of existing repository metadata. You can also take snapshots of your metadata and use them for backup, compare, and restore purposes.
This section contains the following topics:
Warehouse Builder provides several features that enable you to copy and move metadata for the purposes of backup, history management and version management.
You can import and export metadata for any type of object on the navigation tree using the Metadata Loader (MDL) utility. Access MDL through the Warehouse Builder client or through the OMB Plus scripting interface. Use the import and export functionality to backup metadata or to migrate metadata when upgrading Warehouse Builder.
You can also then move exported files into a third-party version control tool such as Oracle Repository, ClearCase, or SourceSafe. If you enter version numbers in your project properties, it is easier to track your export and import versions in this setting.
You can also perform metadata change management by taking snapshots of your metadata using the OMB Plus scripting interface. Snapshots enable you to capture definitions of metadata objects using Warehouse Builder scripts. Use snapshots for metadata backup and version management. For more information about metadata change management, see Chapter 16, "Metadata Change Management".
The MDL enables you to copy or move metadata objects between repositories, even if those repositories reside on platforms with different operating systems.
The MDL consists of two utilities: metadata export and metadata import. The export utility extracts metadata objects from a repository and writes the information into a text file. The import utility reads the metadata information from an exported text file and inserts the metadata objects into a repository. MDL uses its own format, and the MDL import utility only reads files of MDL format (files created by MDL Export).
You can operate the MDL from the Warehouse Builder console or by using a command-line interface. For instructions on using MDL through the command-line interface, refer to "Using the Metadata Loader Command Line Utility". If you use the console menu, a graphical interface guides you through the export or import processes.
Use the metadata loader to perform any of the following tasks:
Backup metadata: The MDL is an important part of your disaster recovery strategy. You can export a file with your existing repository metadata as a backup and use that exported file to restore a repository if necessary.
Seed a new repository: You can export data from an existing repository and use it as the basis for a new repository.
Migrate a repository: You can export metadata to a file and then re-import it. This is commonly done when upgrading to a newer version of Warehouse Builder. For details on upgrading from a previous version of Warehouse Builder, see the Oracle Warehouse Builder Installation and Configuration Guide.
Copy metadata: A multiple user development environment can result in multiple copies of the same metadata. The MDL makes it easy to load single set metadata into more than one repository.
This section contains the following topics:
The Warehouse Builder repository allows multiple clients to access the same repository schema concurrently. Warehouse Builder uses locks to allow only one client to access to change repository objects. While an object is locked, other clients can only view it as it existed after the last transaction instigated by any user is committed.
Tip:
To ensure that you are exporting the most up-to-date metadata, you need to be the sole client accessing the repository.If you click OK when prompted, the MDL commits changes made to the repository after a successful metadata import (any import with no error messages, including imports with only information or warning messages). The MDL also executes a rollback after an unsuccessful import. This means that Warehouse Builder attempts to acquire one lock for each primary object (an object in the first level on the navigation tree) in the repository that matches an object in the MDL file. These objects include, but are not limited to, projects, modules, and tables—individual columns are not locked. Therefore, you must be able to hold the locks for these objects while you import metadata. If other users hold locks for objects to which you are importing, the MDL will fail.
Tip:
To ensure a successful metadata import, you need to be the sole client accessing the repository.If the MDL import affects too many objects in the repository, the MDL automatically switches to single user mode. This means that no other users can log on to the repository until after the MDL import completes. Single-user mode allows the MDL to avoid the performance degradation that results from using a large number of locks. In single-user mode, the MDL is less likely to deplete the repository enqueue resources. If other users are logged into this repository when MDL attempts to switch to single-user mode, MDL cannot switch to single-user mode and subsequently fails.
You also need to have MDL_IMPORT security privileges in order to import metadata. For more information on security, see "Managing Security with PL/SQL".
Each time you use the export or import utilities, the MDL reports the results of an action and writes diagnostic and statistical information to a log file.
The MDL reports the results after any import or export task with a dialog. If you want detailed information, you can view a detailed log by clicking View Log File from the Metadata Export Results dialog as shown in Figure 15-1.
The results dialog lists the metadata objects found in either the file or the repository, and the number of each object that was exported or imported. You can use the results dialog to ensure that all of the objects were exported or imported. The MDL identifies the objects that were exported or imported and compares it with the eligible objects list. A zero in the Number Exported or Number Imported column for any object indicates that the MDL found no object of that type in the repository. However, if a zero appears for any object that exists in the repository or imported MDL file, then MDL encountered a problem when importing or exporting that object.
Whenever you export or import repository metadata, the MDL writes diagnostic and statistical information to a log file. By default, the log file is located in the directory and path specified in the Message Log tab located in the Preferences dialog. You can specify an alternative location for the log file when invoking MDL.
Example 15-1 displays the contents of a typical import log file.
Example 15-1 Log File Showing Import Results
Import started at 04/25/2001 4:59:46 PM ******************************************************************************** * Import for OWB Release: 3.0.0.0.0 Version: 3.0.0.3.0 * User: user30_3i Connect String: epaglina-pc:1521:ora8i * Data File: d:\owb3000\sco_dim_time_phy_m_tgt.mdl * Log File: d:\owb3000\imp_dim_time_phy_m_tgt.log * Trace: B * Trace File: d:\owb3000\imp_dim_time_phy_m_tgt.trc * Physical Names: Y Mode: CREATE * Ignore Universal Identifier: Y Commit At End: Y ******************************************************************************** Informational at line 15: MDL-1207 PROJECT with physical name <PRJ_Dimension> not imported because it already exists. Informational at line 21: MDL-1207 DATAWAREHOUSE with physical name <WH> not imported because it already exists. Informational: MDL-1134 COMMIT issued at end of import data file. Counts for OWB Import Utility ----------------------------- Total Projects Processed by Import = 1 ------------------------------- Project = PRJ_Dimension Entity in Project Added Replaced Skipped ------------------------------- ----- -------- ------- DATAWAREHOUSE: 0 0 1 DIMENSION: 1 0 0 LEVEL: 3 0 0 HIERARCHY: 2 0 0 LEVELRELATIONSHIP: 4 0 0 COLUMN: 18 0 0 UNIQUEKEY: 8 0 0 PRIMARYKEY: 2 0 0 CONFIGPARAM: 60 0 60 CHILDCONFIG: 14 0 0 Import ended at 04/25/2001 4:59:52 PM
The log file enables you to monitor and troubleshoot export and import activities in detail. The log file contains the following types of status messages:
Informational: Provides information about the import or export, such as missing metadata objects, whether or not objects were imported, and any reasons why they were not imported or exported.
Warning: Cautions you about the import or export of an object but does not indicate a failed or aborted load. A warning notifies you of the possibility of unexpected load results for the load.
Error: Indicates that the MDL export or import was aborted and did not complete successfully. The error message offers a brief reason for the failure.
This log also displays the total number of objects that have been added, replaced, and skipped. A zero in any column for any object indicates that the MDL found no object of that type in the repository or in the imported MDL file.
If you are running an MDL Import and encounter an error, an error message displays.
Click Detail to display a detailed error log that lists the repository object and the object line in which the error occurred. Detailed messages are useful whenever you import metadata into repositories with existing metadata because they alert you to problems such as improperly defined metadata objects and object duplication.
Figure 15-2 is an example of a detailed error message.
In this example, the repository object is the CUST dimension and the imported object is the TOTAL level. The dialog explains that the TOTAL level cannot be imported into the CUST dimension because a level named TOTAL already exists.
The Metadata Loader can export all repository objects. The MDL also exports information belonging to metadata objects such as table columns and their constraints, data loading configuration parameters, and named attribute sets. You can use the MDL to export an entire project or a subset of objects within a project.
When you export repository metadata, the Metadata Loader writes the extracted metadata to a delimited text file. The MDL stores this file outside the repository by assigning a default path and file name to the exported MDL file.
This section contains the following topics:
Before you attempt to export metadata, ensure you have the following:
Required access privileges. To ensure that you are exporting the most up-to-date metadata, verify that you are the sole client accessing the repository in read/write mode. For more details, see "Required Access Privileges for MDL" .
Sufficient disk storage. If you lack sufficient disk space on the machine to which you export the metadata, the export fails. Your destination machine must be able to contain the entire metadata file. The export utility cannot save portions of the metadata file.
You can export metadata from a Warehouse Builder repository using one of the following:
Metadata Loader command line utility. You can use the command line utility to perform tasks additional tasks not available from the client interface. For instructions on exporting from the command line, see "Using the Metadata Loader Command Line Utility".
Warehouse Builder client interface. For instructions on using the client interface, see "Exporting Metadata".
Using the command line or the client interface, you can export an entire project, collection, or module, or any subset of objects. If you export a subset of objects, the MDL exports definitions for each object you have selected and the parent objects to which the subset belongs. This enables the MDL to maintain the tree relationships for those objects during metadata import.
For example, if you export a single dimension, the export file contains definitions for:
The dimension (and its hierarchies and levels)
The dimension node to which the dimension belongs
The module to which the dimension node belongs
The project to which the module belongs
If you are exporting a subset of objects, make sure you export all referenced objects and import them as well. The Metadata Import Utility enables you to import repository objects even if the references for those objects cannot be satisfied.
For example, if you export a cube, the foreign key references will be exported, but the dimensions to which they refer will not. If the metadata in the dimension tables changed, then the foreign key references imported to the new repository would become incorrect.
Use the metadata export utility to export objects from a Warehouse Builder repository into an MDL file.
To export metadata from a repository using the Warehouse Builder client interface:
From the Warehouse Builder Console, select the object or objects you want to export.
You can export individual objects such as tables or groups of objects. When you export projects nodes, or modules, you also export the objects they contain. When you export collections, you also export the objects they reference.
From the Project menu, select Metadata Export and then File.
The Metadata Export dialog displays the names and types of the objects you are exporting. The Metadata Export dialog also displays default settings for the export file as shown in Figure 15-3.
You can accept or change the following default settings:
File Name: Type the name of the export file to create or click Browse to locate a directory or file. The filename you assign must end with .mdl.
Log File: Warehouse Builder records information about the export in a log file. You can change this location by entering a new path and filename. Type the path and file name in the field or click Browse to locate a directory or filename.
Field Separator: Table fields in the export file are separated with a pipe (|) by default. If your file already has the pipe (|) symbol as part of its data, you can change the default field separator to a caret (^) by selecting it from the list.
Character Set: Select the character set to use in the export file. The default character set is defined by the Warehouse Builder client system. Use the list to change the output character set.
Click Export.
If you made changes to the repository metadata prior to running the export utility, the Metadata Export Confirmation dialog displays. Click Commit to save changes or Rollback to revert to the previously saved version. You must have read/write access to the repository in which you are exporting metadata to commit changes.
The Metadata Export Progress dialog displays the progress. When the export completes, the Metadata Export Results dialog displays.
Click View Log File for a detailed view of the export process.
The Metadata Loader formats the .mdl export file using keywords and position as shown in Example 15-2.
Example 15-2 Sample Records from an Export File
#Project data <PhysicalName> <LogicalName> <UniversalID> <Version Label> PROJECT|WarehouseName|Warehouse Name|A86184D5336911D58E9000B0D02A59E4|null #Dimension <PhysicalName> <LogicalName> <UniversalID> <Prefix> <UsageType> <Imported> <Generated> DIMENSION|Channels|Channels Dimension Data Mart|7E727655029911D58DC900C04F48E9ED|ch|null|N|N
In this example, each record in the file begins with a keyword followed by one or more variable-length fields. Table fields are separated by a pipe (|) by default.
Archiving a project enables you to copy metadata stored within a Warehouse Builder repository to an external location for securing that data at a fixed point in time. Warehouse Builder provides an Archive Wizard to assist you in this process. The Archive and Restore utilities initially write to a file system. You can then move files from this file system into a third-party version control tool such as Oracle Repository, ClearCase, or SourceSafe.
Note:
The Archive and Restore utilities will be desupported in the next release of Oracle Warehouse Builder .You must set up your Archive/Restore settings on the Preferences page before you can archive or restore your project. If you attempt to archive or restore without setting these preferences, you get an error.
Before you archive your project, you can update the project version label with the Project Properties dialog. There are two places in Warehouse Builder where you can set up the version label used in the archive/restore:
The first is in the New Project Wizard. The New Project Wizard contains a step that enables you to define version properties. The version label that you set here is the version label that is used when that project is archived.
After you have created a project, you can edit the version label by opening the Properties dialog for the project. Click the Version Properties tab to modify the project version label.
Archive and Restore are different from Import and Export. Table 15-1 describes the differences between Archive and Export.
Table 15-1 Differences Between Archive and Export
| Feature | Archive | Export |
|---|---|---|
|
Character Set |
UTF8 |
User Configured |
|
Field Separator |
Pipe Character (|) |
User Configured |
|
Read-only Detection |
Detects and prompts you to re-try |
Detects and then fails |
|
Dump Format |
MDL |
MDL |
|
Log File Name |
Generated |
Generated and User configured |
|
File Location |
Configured by preferences in the following structure:
|
User-defined |
Before you archive your project, you can update the project version label with the Project Properties dialog (see "Project Version Labels").
To archive a project:
Select Archive from the Project menu.
You can also select Archive from the right-click menu when a project is selected.
The Archive Wizard Welcome page displays, as shown in Figure 15-4.
Click Next.
The Summary page displays a summary of the archive settings prior to running the archive process. If you want to see the details of your archive after the archive process is complete, check the Show details dialog following a successful archive box.
No changes can be made from the wizard. If you notice an error in the Archive Wizard Summary page, click Cancel and make the appropriate changes to your Archive/Restore Preferences before continuing with the archive.
Click Finish.
This begins the archive process. A progress window appears. When the progress bar reaches 100%, the archive process is complete.
If you checked the Show details dialog following a successful archive box, the Archive Results dialog displays. This dialog displays the name of each object type and how many of each were archived, as shown in Table 15-1.
For a more detailed look at the archive process, click View Log File. This displays the entire log file, as shown in Table 15-1.
The import utility reads the metadata information from an exported text file and inserts the metadata objects into a repository. The metadata import utility only reads files created by the metadata export utility.
The MDL imports information belonging to exported metadata objects such as table columns and their constraints, data loading configuration parameters, and named attribute sets. You can use the MDL to import objects into a project or a collection.
If you import an .mdl file containing metadata for gateway Modules, such as DB2 or Informix, from an older version of Warehouse Builder, the file may not import the metadata into the corresponding source module folders in a project. The imported files are stored under the Others node in the navigation tree. You need to manually copy the metadata for the gateway modules into the correct source module folders.
The following sections describe how to use the Metadata Import Utility:
Before you attempt to import metadata, ensure you have the following:
Required security privileges: You need the MDL_IMPORT privilege before you begin an import. For more information on security, see "Managing Security with PL/SQL".
Required access privileges: Only a user with read/write access can use the metadata loader import utility. Because the import utility is altering the repository, the metadata objects must be locked prior to importing. For more details, see "Required Access Privileges for MDL".
A backup of your current repository: Consider taking a backup of your existing repository (either in the form of an export or a metadata snapshot) before attempting a large or complex import. For more information on exporting metadata, see "Exporting Metadata". For more information on metadata snapshots, see Chapter 16, "Metadata Change Management".
Multiple Language Support base language compatibility: The base language is the default language used in the repository and is set using the Repository Assistant during installation. This setting cannot be altered after installing the repository. Loading differing base language metadata objects into a repository results in error. For more information on setting the base language in a repository, see the Oracle Warehouse Builder Installation and Configuration Guide.
You can import metadata into a Warehouse Builder repository using one of the following:
Metadata Loader Command Line Utility: You can use the command line utility to perform tasks additional tasks not available from the client interface. For example, you can override default values for configuration parameters for loading data. For instructions on importing from the command line, see "Using the Metadata Loader Command Line Utility".
Warehouse Builder Client Interface: For instructions on using the client interface, see "Importing Metadata".
When you import a set of definitions from exported metadata, the import utility can update existing definitions in a Warehouse Builder project. However, certain metadata definitions require attention to ensure that they are updated. The following are examples of some of the errors you can see:
Mapping Definitions. The Metadata Import Utility does not bind imported mapping operators to their physical objects. If a mapping definition appearing in the source MDL file replaces a mapping definition in the target repository, then the new repository mapping definition is unbound. You may need to reconcile new mapping operators with the physical objects they represent. The MDL generates a warning message to the log file stating that the mapping operators are not bound.
Foreign Key Definitions. It is possible that a source MDL file can contain foreign key references to unique or primary keys that are not in the target repository. If the referenced unique or primary keys for any foreign key appearing in the MDL file does not exist in the target repository, the MDL will generate a warning message in the log file. This message will state that the repository does not contain a referenced key for the foreign key.
Use the metadata import utility to import objects from an MDL file into a Warehouse Builder repository.
To import objects from an export file using the Warehouse Builder client:
Select the project to which you will import metadata.
From the Warehouse Builder Console, select Project and select MetaData Import.
Warehouse Builder displays the Metadata Utility Import dialog as shown in Figure 15-7.
Specify the names and locations for the import file and its log:
File Name: Type the name of the MDL file or click Browse to find the MDL file you want to import.
Log File: Warehouse Builder records information about the import in a log file. You can change this location by entering a new path and filename. Type the path and file name in the field or click Browse to locate a directory or filename.
Select an Import Option. For more information on import options, see "Import Modes". You can select from the following import options:
Add new metadata only: Adds new objects to a repository.
Add new metadata and replace existing objects: Adds new objects to a repository and replaces existing objects.
Add new metadata and merge existing objects: Adds new objects and merges columns into existing objects in your repository.
Replace existing objects only: Replaces existing objects in your repository.
In Match By, specify the matching criteria the utility uses to compare the metadata in the import file against the metadata existing repository. For more information, see "Metadata Matching Criteria".
Ignore Universal Identifier: The import utility does not use Universal Identifiers to search for objects you are importing.
Name: Searches your repository using the physical names of the objects you are importing to make sure the objects do not already exist.
Character Set: Select the type of character set used to create the import file. The default character set is defined by the Warehouse Builder client machine. Use the drop-down list to change the output character set.
You can add new languages and character sets using the Repository Assistant. For more information, refer to the Oracle Warehouse Builder Installation and Configuration Guide.
Click Scan to display the exported metadata header information as shown in Figure 15-8. The Header Information dialog displays a summary of the total number of object types contained in the metadata file you selected.
Click Import.
If you have made any changes before starting the import, the Metadata Import Confirmation dialog displays. Click Commit to save any changes or Rollback to ignore changes and revert to the previously saved version.
The Metadata Import Confirmation dialog displays as shown in Figure 15-9 if the exported metadata data information has not been reviewed.
Click Import to continue.
The Metadata Import Progress panel displays, as shown in Figure 15-10.
This dialog displays the object types and the number of each type that were imported or skipped. For a detailed view of the import process, click View Log File.
The graphical user interface for the metadata import utility operates in one of the following modes:
Add new metadata only (Create Mode): Adds new objects to a repository. If objects from the MDL file already exist in the repository, they remain unchanged.
Add new metadata and replace existing objects (Update Mode): Adds new objects to a repository and overwrites existing objects with those in the MDL file.
Add new metadata and merge existing objects (Merge Mode): Adds new objects and overwrites existing objects in your repository only if they differ with those in the MDL file.
Replace existing objects only (Replace Mode): Replaces only existing objects in your repository. When importing metadata objects, the MDL will overwrite any existing metadata unless you use this mode.
When you import using the Update or the Replace modes, the import completely replaces the existing object's children so that the final object is exactly the same as the source object. Any existing children of a repository object that are not replaced or added are deleted. This occurs regardless of whether a child object occurs in a mapping or is a foreign, primary, or unique key column in a table or view.
For example, in the MDL export file, the CUST table contains three columns with the physical names: Last_Name, First_Name, and Middle_Init. In the repository, the same table already exists, and contains four columns with the physical names: Last_Name, First_Name, Status, and license_ID. During a replace operation, the columns Last_Name and First_Name are replaced, column Middle_Init are added, and column Status and license_ID are deleted. The final result is that the CUST table in the Warehouse Builder repository contains the same metadata from the CUST table in the export file.
Tip:
Using the replace mode can result in lost data constraints, metadata physical property settings, data loading properties, and mapping definitions. If you choose to use replace mode, ensure that you can restore your repository from backup to its state prior to importing in replace mode.When you use the metadata import utility, it first searches the repository for metadata objects that exist in the repository and compares them to those in the file you are importing. How the comparison is made is determined by the loading mode and by the search method you choose. The following methods are available:
Physical Name: Physical names are exported to the export file. The physical name determines whether an object needs to be created, replaced, or merged during an import operation. Use this method when object names in the target directory change, and you want to create new UOIDs for those objects.
Universal Object Identifier: The metadata export utility assigns a unique system-generated identifier to each exported row object called the Universal Object Identifiers or UOIDs. The purpose of the UOID for a row object is to uniquely identify it in an object table. The MDL import utility uses these UOIDs to determine whether a row object needs to be created, replaced, or merged during an import operation. Use this method if you want to maintain UOIDs across different repositories even when object names in the target repository have changed.
By default, the import utility searches by UOIDs. However, the import utility ignores the UOIDs for mappings in the MDL file that already exist in the target repository.
Note:
MDL imports that run in merge mode must use UOIDs for the search criteria in order to merge into existing mappings. Also, if the mapping in the MDL file does not have a Universal Identifier, the mapping cannot be merged into a mapping that matches by name. For more information, see "Import Modes".Each search method can be combined with an import mode in several different combinations. Each combination can offer different results in the import process. The mode that you select determines how the metadata import utility will search for metadata objects in the repository prior to importing.
For example, if the search is by the logical name of a repository object in the export file, the Metadata Import Utility searches the repository for the object's logical name. If an object with the corresponding logical name is not found, the resulting actions are based on the import mode you select.
Table 15-2 describes what happens in the available import modes for repository objects that do not match the MDL file names.
Table 15-2 Import Mode without Matching Names
| Import Mode | Result |
|---|---|
|
Create Mode |
A new object is created. |
|
Replace Mode |
A warning message is written to the log file that the object cannot be found to replace and the object is skipped. |
|
Update Mode |
A new object is created. |
|
Merge Mode |
A new object is created. |
Table 15-3 describes what happens in the available import modes for repository objects that match the MDL file names.
Table 15-3 Import Mode with Matching Names
| Import Mode | Result |
|---|---|
|
Create Mode |
A message is written to the log file that the object already exists and the object is skipped. |
|
Replace Mode |
The object is replaced. |
|
Update Mode |
The object is replaced. |
|
Merge Mode |
The object is merged. |
The MDL reads and processes the imported metadata and writes status and diagnostic information in the log file. When the import is complete, the Metadata Import Results dialog displays.
Restoring a project enables you to re-create metadata within a Warehouse Builder repository from an external location. Warehouse Builder provides a Restore Wizard to assist you in this process.
Note:
The Archive and Restore utilities will be desupported in the next release of Oracle Warehouse Builder .You must set up your Archive/Restore settings on the Preferences page before you can archive or restore your project. If you attempt to archive or restore without setting these preferences, you get an error.
Archive and Restore are different from Import and Export. Table 15-4 describes the differences between Restore and Import.
Table 15-4 Differences Between Restore and Import
| Feature | Restore | Import |
|---|---|---|
|
Character Set |
UTF8 |
User Configured |
|
Complete Project Replacement |
Yes |
Does not delete the project; may replace it, depending on MDL mode |
|
Dump Format |
MDL |
MDL |
|
UniversalID Preservation |
Always |
User Configured |
|
Name Preservation |
Always |
User Configured |
|
Log File Name |
Generated |
Generated and User configured |
|
Mode |
Replace |
Create/Update/Replace/ Merge |
Follow these instructions to restore a project.
To restore a project:
Select Restore from the Project menu.
The Restore Wizard Welcome page displays.
Click Next.
The Select Archive page displays, as shown in Figure 15-11. Browse to or type the Archive File you want to restore.
Click Next.
The Summary page displays a summary of the restore settings prior to running the restore process, as shown in Figure 15-12. If you want to see the details of your restore after the restore process is complete, check the Show details dialog following a successful restore box.
Click Finish.
This begins the restore process. A progress window appears. When the progress bar reaches 100%, the restore process is complete.
If you checked the Show details dialog following a successful restore box, the Restore Results dialog displays, as shown in Figure 15-13. This dialog displays the name of each object type, how many of each were restored, and how many of each were skipped.
For a more detailed look at the archive process, click View Log File. This displays the entire log file, as shown in Figure 15-14.
You can operate the MDL from the command line instead of the user interface.
This section contains the following topics:
For related information on using the Metadata Loader's command line utility for upgrade purposes, refer to the Oracle Warehouse Builder Installation and Configuration Guide.
If you use the command-line interface, you can customize how to move your metadata at a more detailed level than you can when using the GUI. For example, when exporting metadata, the command line enables you the flexibility to disable the export of configuration values, vary the separator character within an export file, and maintain parameter files for selected export operations.
For importing metadata, the command line offers the flexibility of creating specific import actions for each object. These operations automate the consolidation or synchronization of metadata in multiple repositories that have a similar project structure.
The scripts for executing both the export and import utilities reside in the $OWBHOME\owb\bin\win32 directory. Both the export and import utilities are driven by a set of parameters. You can specify the MDL parameters by:
Providing MDL parameters as a response to command-line prompts.
Creating an MDL parameters file.
Providing MDL parameters as a response to command-line prompts, and create a MDL parameters file.
By default, the MDL also exports values for configuration parameters for loading data, but you can override this setting at the command line. You can choose how you want to export the files within a project. For example, if you are exporting three source modules and two target modules, you can choose to export them separately or together.
To export a project at the command line:
Create the MDL parameters file.
Execute the Metadata Export Utility.
The following command invokes the Metadata Export Utility and specifies the preceding parameters file:
w:\owb\bin\win32>exp parfile=e:\MDL\EXP_Directives Processing ... Export successful.
The objects are exported to the file and can be imported into a repository using the metadata import utility.
An MDL parameters file is a text file that contains a set of parameters for the export utility. The format for an export parameter is:
Keyword=Value
You can also form an export parameters by replacing the value with the wildcard character (*), which matches any string, or with a list of named objects:
Keyword=*
Keyword=(value-1, value-2, …, -k)
For example, you can specify a set of tables to be exported as:
TABLES=(Customers, Products, Days)
Example 15-3 shows a typical parameters file for importing a module.
Example 15-3 Parameters File format
USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816 PROJECT=GCCWarehouse FILE=e:\MDL\GCCWarehouse-exp-JUL01 FIELDSEPARATOR=| LOG=e:\MDL\GCCWarehouse-exp-JUL01-LOG CONFIGPARAM=N
Table 15-5 summarizes the keywords used to form export parameters. You can use the comment indicator (#) to document the scripts. Put the indication in the first column of a record and follow it with text.
Table 15-5 Keywords for Export Utility Parameters
| Utility Prompt | Keyword | Description |
|---|---|---|
|
Username/password@host:port:sid |
USERID |
Username, password and connection as a string. |
|
N/A |
USERNAME |
The user name for accessing Warehouse Builder repository. |
|
N/A |
PASSWORD |
The password that corresponds to the USERNAME. |
|
N/A |
HOST |
Machine name for Warehouse Builder repository. |
|
N/A |
PORT |
Port for Warehouse Builder repository database listener. |
|
N/A |
SID |
SID for Warehouse Builder repository database. |
|
Project Name |
PROJECT |
Project name. Wildcard format supported for Project, but if used, no other object type keywords can follow. In order to export shared transformations, use |
|
Export File |
FILE |
File name for the exported data. |
|
Field Separator |
FIELDSEPARATOR |
Field separators: |
|
Log File |
LOG |
File name for the status and statistics of the export. |
|
Parameter File |
PARFILE |
Parameter file containing keywords. |
|
N/A |
CONFIGPARAM |
Export configuration values (Y/N). Default is Y. |
|
N/A |
TRACE |
Debug messages. Options: S - write messages to screen Y - write messages to a file B - write messages to screen and a file |
|
N/A |
TRACEFILE |
Trace file name. |
|
N/A |
PHYSICALNAMES |
Use physical names (Y/N) for lookup of objects to be exported. Default is N. |
|
N/A |
CHARACTERSET |
The character set to be used for the export data file. |
|
N/A |
MODULES |
If a wildcard or multi-value format is used for MODULE, no other object type keywords can follow. If a simple format is used, this keyword can appear multiple times, directly followed by keywords for any of its owned object types which can be selected using any format (simple, wildcard, multiple). |
|
N/A |
TABLES |
N/A |
|
N/A |
VIEWS |
N/A |
|
N/A |
FILES |
N/A |
|
N/A |
SEQUENCES |
N/A |
|
N/A |
MATERIALIZED VIEWS |
N/A |
|
N/A |
DIMENSIONS |
N/A |
|
N/A |
FACTS |
N/A |
|
N/A |
TRANSFORM CATEGORIES |
For wildcard or multi-value format, no FUNCTIONS keyword can follow. If simple format then this keyword can appear multiple times, directly followed by a FUNCTIONS keyword, which can use any format (simple, wildcard, multiple). |
|
N/A |
FUNCTIONS |
N/A |
|
N/A |
MAPPINGS |
N/A |
|
N/A |
COLLECTIONS |
N/A |
|
N/A |
LOCATIONS |
N/A |
|
N/A |
CONNECTORS |
N/A |
|
N/A |
RUNTIMEREPOSITORYCONNECTIONS |
N/A |
|
N/A |
STANDALONEFUNCTIONS |
N/A |
|
N/A |
STANDALONEPROCEDURES |
N/A |
|
N/A |
ADVANCEDQUEUES |
N/A |
|
N/A |
EXTERNALTABLES |
N/A |
|
N/A |
PROCESSES |
N/A |
|
N/A |
SNAPSHOTS |
N/A |
|
N/A |
QUERYOBJECTS |
N/A |
|
N/A |
REPORTS |
N/A |
|
N/A |
REPORTGROUPS |
N/A |
|
N/A |
IOBUSINESSAREAS |
N/A |
|
N/A |
HELP |
Use HELP=Y for a complete list. |
|
N/A |
# |
Comment line used in a parameter file. |
To import selected modules:
Create an MDL parameter file.
Execute the Metadata import utility.
The following command invokes the Import Utility and specifies the preceding MDL parameter file:
w:\owb\bin\win32>imp parfile=e:\MDL\IMP_Directives.txt Processing ... Import successful.
Like the MDL export, you can direct the MDL import to import objects from a file by answering prompts or by creating a file with a set of parameters. Example 15-4 shows a typical parameters file for importing a module.
Example 15-4 Parameters File format
USERID=GCCWH/GCCWH@dwdoc11-pc:1521:ora816 FILE=e:\MDL\gccstar-exp LOG=e:\MDL\gccstar-imp-LOG MODE=CREATE CONFIGPARAM=N
Table 15-6 summaries the keywords used to form import parameters.
Table 15-6 Keywords for Import Utility Parameters
| Utility Prompt | Keyword | Description |
|---|---|---|
|
Username/passw@host:port:sid |
USERID |
Username, password and connection as a string. |
|
N/A |
USERNAME |
The user name for accessing Warehouse Builder repository. |
|
N/A |
PASSWORD |
The user password that matches USERNAME. |
|
N/A |
HOST |
Machine name for Warehouse Builder repository. |
|
N/A |
PORT |
Port for Warehouse Builder repository. |
|
N/A |
SID |
SID for Warehouse Builder repository. |
|
Import File |
FILE |
File name for the data to be imported. |
|
Import Mode |
MODE |
CREATE, REPLACE, UPDATE, or INCREMENTALUPDATE. |
|
Log File |
LOG |
File name for the status and statistics of the export. |
|
Parameter File |
PARFILE |
Parameter file containing keywords. |
|
N/A |
CONFIGPARAM |
Import configuration values (Y/N). Default is Y. |
|
N/A |
TRACE |
Debug messages. Options: S - write messages to screen Y - write messages to a file B - write messages to screen and a file |
|
N/A |
TRACEFILE |
Trace file name. |
|
N/A |
PHYSICALNAMES |
Use physical names (Y/N) to lookup objects to be imported. Default is Y. |
|
N/A |
CHARACTERSET |
The character set to use for the export data file. |
|
N/A |
HELP |
Use HELP=Y for a complete lis.t |
|
N/A |
# |
Comment line used in a parameter file. |
|
N/A |
IGNOREUniversalID |
Ignore (Y/N) the universal id as the search criteria. Default is N. |
|
N/A |
PRESERVEDESCRIPTION |
Preserve the description (Y/N) of already existing objects if the MDL data file does not have a description for the object. Default is N. |
|
N/A |
SINGLEUSER |
Request a single user lock (Y/N) for running the import. Default is N. |
If a MODE parameter is not included, then the default is CREATE.
In addition to running an MDL parameter file from the import utility, you can also specify an action plan within the file that will allow you to specifically define what you want to do with each object in the imported file. First you need to specify if you want the object imported, skipped, or deleted. If you choose to import the object, you can set the import mode to CREATE, UPDATE, REPLACE, or INCREMENTAL UPDATE.
Example 15-5 shows an example of an MDL parameter file that contains an action plan.
Example 15-5 MDL Action Plan
USERID=user_sample/user_sample@test-pc:1521:ora8i # FILE=e:\test\data\sample_file.mdl LOG=e:\test\log \imp_sample_file.log # MODE=ACTIONPLAN PHYSICALNAMES=Y IGNOREUOID=Y # # User-Specified Action Plan # ACTION=NONE PROJECT=MY PROJECT MODULES=(DATAWAREHOUSE) # ACTION=CREATE TABLES=(TABLE_3) FACTS=(FACT1, FACT2, FACT3) SEQUENCES=(SEQ_A, SEQ_B, SEQ_C) # ACTION=REPLACE TABLES=(TABLE_1, TABLE_2) DIMENSIONS=(DIM1, DIM2, DIM3) # ACTION=DELETE TABLES=(TABLE_A, TABLE_B) # # Switching to a different module ACTION=REPLACE MODULES=(FLAT_FILE) FILES=(FILE_1, FILE_2) # ACTION=CREATE FILES=(FILE_3) # ACTION=DELETE FILES=(FILE_X)
The Split utility provides a workaround for the memory limitations of the MDL import utility when you are importing a large number of mappings. This utility generates export and import scripts for migrating mappings in pieces as opposed to migrating them all at the same time. The generated scripts have matching MDL parameter files that utilize the CREATE mode. These files can be edited.
If the MDL import fails because of large data, the split utility can be used to re-export and import the mapping data in smaller pieces. All other object types must be exported and imported using the standard MDL utilities. Only mappings can be split into smaller pieces. To export all entities other than mappings, a parameter file containing the following can be used:
VIEWS=*
TABLES=*
SEQUENCES=*
MATERIALIZEDVIEWS=*
CUBES=*
FILES=*
DIMENSIONS=*
VIRTUALTABLES=*
TEMPORARYTABLES=*
TRANSFORMCATEGORIES=*
The split utility splits the mappings within a module in a Warehouse Builder project. The size of the pieces is determined by a parameter located in a file provided with this application.
The expsplit batch script accepts the following arguments:
A parameter file, specified in the form c:\temp\owb_apps.txt. The parameter file has special keywords outlined in the following section that identify the number of mappings, data file names, and extensions.
A parameter target file prefix, specified in the form c:\temp\owb_apps the piece number (numbered from 1). A .txt suffix is added to the generated parameter file, a .bat suffix is added for the export batch file, and an _imp.bat suffix is added to the import batch file.
The following is an example of how to start the split utility:
expsplit exampleparams.txt c:\temp\ora_apps
The following example uses a parameter file exampleparams.txt. This file contains the following parameters:
userid=apps/apps@130.35.12.73:1521:orcl0
PHYSICALNAMES=Y
LOG=c:\temp\owb_data_apps
LOGEXT=log
FILE=c:\temp\owb_data_apps
FILEEXT=dat
FIELDSEPARATOR=^
PROJECT=EDWPRJ
MODULES=EDW_COMMON_MODULE
TYPE=MAPPINGS
COUNT=70
This file is similar to the export parameter file for Warehouse Builder Metadata Loader, with the changes listed in Table 15-7.
Table 15-7 Split Utility Export Parameter Keyword Descriptions
| Keyword in Parameter File | Description |
|---|---|
|
FILE |
Prefix of data file, the chunk number, and file extension (FILEEXT) define the data file name where you exported the data. |
|
FILEEXT |
The data file extension. |
|
PHYSICALNAMES |
Used for name matching. |
|
LOG |
Prefix of the log file, the chunk number, and file extension (FILEEXT) define the log file name. |
|
LOGEXT |
The log file extension. |
|
PROJECT |
A single project name must be specified. |
|
MODULES |
A single module name must be specified. |
|
TYPE |
Must be MAPPINGS. |
|
COUNT |
The number of mappings to be written to each export chunk. |
If the mappings for a Warehouse Builder project are split, the generated parameter files are named as follows:
owb_apps1.txt owb_apps2.txt
A batch file is generated: c:\temp\owb_apps.bat (given the parameter target file prefix) to export the data from the repository. An import batch file is created to import, using create mode, into the same repository. These files can be edited if different target databases are required.
To migrate data using the split utility:
Using command line or Warehouse Builder, perform MDL export of all objects other then mappings.
To export all objects other than mappings in a command line, use a parameter file with the following keywords:
VIEWS=*
TABLES=*
SEQUENCES=*
MATERIALIZEDVIEWS=*
FACTS=*
FILES=*
DIMENSIONS=*
VIRTUALTABLES=*
TEMPORARYTABLES=*
TRANSFORMCATEGORIES=*
If you are using Warehouse Builder to perform export, use multi-select to select and export objects other than mappings.
Import the new export file into target repository.
Split the mappings and export them using split utility
expsplit exampleparams.txt c:\temp\ora_apps
The utility connects to the source repository, splits mappings, and creates multiple parameter files according to exampleparams.txt. These parameter files are used during the export. The utility also creates an export batch file and an import batch file.
Table 15-8 lists the files that are created.
Table 15-8 Files Created by the Split Utility
| Description | File Name |
|---|---|
|
Batch file to perform export |
c:\temp\ora_apps.bat |
|
Batch file to perform import |
c:\temp\ora_apps_imp.bat |
|
Multiple parameter files to be used by export and import batch files |
c:\temp\ora_apps1.txt c:\temp\ora_apps2.txt c:\temp\ora_apps3.txt |
Run the export batch file to export mappings into the location specified in the parameter file (variable FILE specified in step 3).
Modify generated parameter files c:\temp\ora_apps1.txt, c:\temp\ora_apps2.txt. Edit the connection information to point to the target repository.
Run import batch file c:\temp\ora_apps_imp.bat to complete the import.
