DMT - Oracle

Oracle

CAST extractor for Oracle
Reuse existing CAST extractor/delivery output

CAST extractor for Oracle

Configures a connection to a live Oracle database and will perform an extraction to file using CAST's own SQL extractor.

Note that:

- the database does not need to be online during the analysis process in the CAST Management Studio.
- Note that you do NOT need to install a compatible client connection layer (for the target RDBMS) on the machine on which you are running the CAST Delivery Manager Tool.

Host name

Used to configure the host name of your database server. You can also enter an IP address if required.

Port

Used to configure the port number for accessing your database. This field will only be displayed if you selected Port from the Instance identification option.

Default RDBMS port settings are as follows:

Oracle > 1521

Instance identification

Used to configure the database server access mode type (Service or SID).

Select an appropriate access mode for your database.

Service

Used to configure the Service name for accessing your database server.

This field will only be displayed if you selected Service from the Instance identification option.

Please note that when connecting to a "pluggable database" (for example in Oracle 12c and above) you MUST use this Service option - the SID option will not function.

SID

Used to configure the SID name for accessing your database server. This field will only be displayed if you selected SID from the Instance identification option.

Credentials

Select this option to enable the User/Password fields below. This is required if your target database server requires an authenticated connection.

Notes

Do not enable this option if a single sign on system is in operation in your environment.

User name

Used to configure the user name that has sufficient privileges to access the required schemas on the selected RDBMS for packaging purposes.

Schema access rights for extraction action

In order to carry out an extraction of the required schemas, the person in charge of the extraction (Delivery Manager or DBA) must use one of the following Oracle users:

Extraction user - CAST highly recommends using a dedicated extraction user with specific privileges in order to access the required data for extraction. When using this dedicated extraction user, CAST will query the DBA_* views to access the required data - these views give access to ALL objects. Please also read the section below entitled "Note about the DBA_ARGUMENTS view and the dedicated extraction user".

To create the dedicated extraction user, please run the following script as the SYS user - it will create the dedicated user and then grant the required privileges to the user (where USER_FOR_EXTRACTION is the dedicated extraction user):

create user USER_FOR_EXTRACTION identified by cast

/

grant connect to USER_FOR_EXTRACTION

/

grant create session to USER_FOR_EXTRACTION

/

grant select on dba_arguments to USER_FOR_EXTRACTION

/

grant select on dba_col_comments to USER_FOR_EXTRACTION

/

grant select on dba_tab_comments to USER_FOR_EXTRACTION

/

/*

* The view dba_mview_comments does not exist on Oracle 9.x, therefore the

* following grant should not be executed when running the extraction on Oracle 9i.

*/

grant select on dba_mview_comments to USER_FOR_EXTRACTION

/

grant select on dba_coll_types to USER_FOR_EXTRACTION

/

grant select on dba_cons_columns to USER_FOR_EXTRACTION

/

grant select on dba_constraints to USER_FOR_EXTRACTION

/

grant select on dba_db_links to USER_FOR_EXTRACTION

/

grant select on dba_dependencies to USER_FOR_EXTRACTION

/grant select on dba_ind_columns to USER_FOR_EXTRACTION

/

grant select on dba_ind_expressions to USER_FOR_EXTRACTION

/

grant select on dba_indexes to USER_FOR_EXTRACTION

/

grant select on dba_mviews to USER_FOR_EXTRACTION

/

grant select on dba_object_tables to USER_FOR_EXTRACTION

/

grant select on dba_objects to USER_FOR_EXTRACTION

/

grant select on dba_procedures to USER_FOR_EXTRACTION

/

grant select on dba_sequences to USER_FOR_EXTRACTION

/

grant select on dba_source to USER_FOR_EXTRACTION

/

grant select on dba_synonyms to USER_FOR_EXTRACTION

/

grant select on dba_tab_columns to USER_FOR_EXTRACTION

/

grant select on dba_tables to USER_FOR_EXTRACTION

/

grant select on dba_triggers to USER_FOR_EXTRACTION

/

grant select on dba_types to USER_FOR_EXTRACTION

/

grant select on dba_users to USER_FOR_EXTRACTION

/

grant select on dba_views to USER_FOR_EXTRACTION

/

SYSTEM - When it is not possible to use the dedicated extraction user, CAST recommends using the SYSTEM user instead. CAST will query the DBA_* views to access the required data - these views give access to ALL objects.

Other Oracle users - When it is not possible to use the the dedicated extraction user or the SYSTEM user as outlined above, it is possible to use any other user (i.e. non-DBA user) to run your extraction - for example: the schema owner user. However, there are several drawbacks to doing this and CAST therefore does not recommend using this type of user:

CAST will query the ALL_* views to access the required data - these views only give access to objects that the user is entitled to access. This means that CAST cannot guarantee that the extraction will contain all the required data.
Performance of the extraction will be reduced.

Note also that in order for the ALL_* views to be queried, the ALL access mode option (see below) must be explicitly selected. By default this option is not selected, which automatically prevents the CAST Delivery Manager Tool from using the ALL_* views to access the required data. If the option is not selected, then the extraction will fail.

Note about DBA_MVIEW_COMMENTS / ALL_MVIEW_COMMENTS views and Oracle 9.x

The views DBA_MVIEW_COMMENTS / ALL_MVIEW_COMMENTS do not exist on Oracle 9.x, therefore when running an extraction on Oracle 9.x in whatever mode (extraction user/SYSTEM/other Oracle user), the extractor will use DBA_TAB_COMMENTS to extract comments on materialized views instead. Results are not impacted. This will typically be displayed in the log as follows:

Unable to use access mode: Extracting comments on materialized views using DBA_MVIEW_COMMENTS

No access to: DBA_MVIEW_COMMENTS

Unable to use access mode: Extracting comments on materialized views using ALL_MVIEW_COMMENTS. Using ALL_MVIEW_COMMENTS has no impact on results, including AFP.

No access to: ALL_MVIEW_COMMENTS

Using access mode: Extracting comments on materialized views using DBA_TAB_COMMENTS on Oracle 9i

Note about the DBA_USERS view and the dedicated extraction user

Situation

If you are using the dedicated extraction user (as recommended and outlined above) to perform your extraction, a view called DBA_USERS is queried to gain access to the data required for extraction
If you do not want to grant the select right on this view for security reasons, you can change the script above to use a synonym instead of the a direct grant on the DBA_USERS view

Action

Comment the following lines in the above script as follows:

-- grant select on dba_users to USER_FOR_EXTRACTION

-- /

Add two new lines to the script as follows:

create synonym USER_FOR_EXTRACTION.DBA_USERS for SYS.ALL_USERS

/

Re-run the script as the SYS user
Use the dedicated extraction user in any future extractions

Impacts

There are no impacts - results when using the synonym instead of the grant select on the DBA_USERS view are identical.

Note about the DBA_ARGUMENTS view and the dedicated extraction user

Situation

If you are using the dedicated extraction user (as recommended and outlined above) to perform your extraction, a view called DBA_ARGUMENTS is queried to gain access to the data required for extraction.

On some Oracle Servers (Oracle 10.2.0.4.0 or any earlier Oracle 10 version and all Oracle 9 versions) this view is not present by default, therefore the following is true:

Impacts

The extraction will succeed, but:

Oracle extractor log contains 'Extraction error: ORA-00942: table or view does not exist' in section 'Extracting: Oracle wrapped valid functions...' ; and:
Extraction will be incomplete - i.e. the following information will be missing:
- IN/OUT parameters will be missing for all procedures and functions outside of the user's own schema, unless the user has the EXECUTE privilege on those procedures and functions. The same is true for the return code of functions. This will impact the analysis and Quality Rules based on datatypes of functions/procedures/parameters or the return code of functions.
- IN/OUT parameters will be missing from the source code generated for wrapped procedures or functions, therefore they will not be visible in CAST Enlighten or in the CAST Engineering Dashboard.
- Return code will be missing from the source code generated for wrapped functions, therefore they will not be visible in CAST Enlighten or in the CAST Engineering Dashboard.

Note that there is no impact on the source code for unwrapped function/procedures because this code is not generated and is instead extracted as is.

Options

Two options exist if you find yourself in this situation:

Manually create the DBA_ARGUMENTS view on the Oracle server prior to running the extraction. To do so, please use this script. Note that the script must be run by the Oracle SYS user. When the view has been created, you can perform an extraction and analysis results will be correct.
If it is not possible to create the DBA_ARGUMENTS view:
1. then if the "ALL Access" mode is unchecked the extraction will not be possible and will fail
2. then if the "ALL Access" mode is checked the extraction will be possible but you must accept that the extraction results will be incomplete.

Password

Used to configure the password that corresponds to your User name configured above.

Remember password

This option enables you to force the CAST Delivery Manager Tool to save the database access credentials you have entered above.

Choosing an option or not has no impact on the extraction (i.e. the CAST Delivery Manager Tool can still access the required resources). However, if you are creating subsequent Versions of the same schemas on the same server, you can choose to store the password in which case you will not need to re-enter it.

There are two save options:

Local > The credentials are saved in the user's local workspace on the current machine. Choose this option if you do not want the password to be available to other Delivery Managers.
Server > The credentials are saved locally (as above) and are also synchronized back to the CAST AIC Portal (i.e. the Source Code Delivery Folder). Choose this option if you want the password to be available to other Delivery Managers.

Schemas to extract

This section enables you to limit the packaging to certain schemas on the target RDBMS:

	Use this option to browse the live server (using the credentials you supplied above) and display a list of the schemas that are available. Place a check mark next to the schema(s) that you want to package: Click OK when you have selected the items you require.
	Use this option if you know the name of the schema you want to package. A dialog box will be displayed enabling you to enter the name of the schema. You can only enter one schema name at a time using this option - to enter multiple schemas, either repeat this action, or use the option above. Please ensure that you input the name of the schema/database in the correct case (i.e. upper or lower) as this field is case sensitive - remember that the vast majority of Oracle schemas are in upper case.
	Use this button to edit an existing schema - a dialog box will appear in which you can modify the existing schema name.
	Use this button to remove an existing schema from the list.

JVM Memory Size

The CAST extractors are Java based and require the use of the JRE JVM (Java Virtual Machine). This option allows you to configure the Maximum Java Heap Size of your JVM for use during the database/schema browse and extraction processes. By default 1GB of memory is allocated and in most scenarios this value can be left as it is.

You may need to change the amount of memory allocated to the JVM if you are getting out of memory exceptions for the Java Heap Space when you either browse to select a database/schema or when you use the Package action to invoke the extraction - this can occur more specifically when attempting to extract a large single database/schema or multiple databases/schemas. Please increase the memory allocation until you no longer receive errors, however, please bear the following in mind:

32bit JRE: If you are running the CAST Delivery Manager Tool via the CAST AIC Portal and have installed a 32bit JRE for this purpose, or when the CAST Delivery Manager Tool is launched from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation), CAST recommends that you do not increase the JVM Memory Size value to above 2GB, otherwise you risk running out of system memory.
If your database/schema extraction consistently runs out of memory despite having allocated the maximum possible with a 32bit JRE, you may need to ensure you are using a 64bit JRE to take advantage of increased memory availability. To do so, you must launch the CAST Delivery Manager Tool from the CAST AIC Portal, rather than directly from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation). And before you do so, you must manually modify the shortcut to the CAST Delivery Manager Tool (available in the Windows Start menu) to replace the existing path to the 32bit JRE (C:\Windows\SysWOW64\javaws.exe) with your 64bit JRE path ("C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" for example). You can add the -Xmx parameter (maximum memory allocation), that will allow the process to use the memory defined. The shortcut should now look similar to this:

"C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" -Xmx4096M -localfile
"C:\Users\<user_name>\AppData\LocalLow\Sun\Java\Deployment\cache\6.0\6\1c274146-29c73860"

Anonymize log file

Use this option to hide sensitive information in the log file. When activated, the log file will contain stars in place of the host name/server name, schema name, username and password (i.e. server connection information):

Note that this option is not activated by default because anonymizing connection information can make it more difficult to troubleshoot errors or issues during the extraction phase.

ALL access mode

This option (when selected) explicitly allows the CAST Delivery Manager Tool to query the ALL_* views to access the required data - these views only give access to objects that the user is entitled to access. This means that CAST cannot guarantee that the extraction will contain all the required data. In addition, performance of the extraction will be reduced.

By default this option is not selected, which automatically prevents the CAST Delivery Manager Tool from using the ALL_* views to access the required data. In other words, if the user name that you have entered into the "User Name" field above does not have sufficient rights to query the DBA_* views then the CAST Delivery Manager Tool will attempt to query the ALL_* views - if the ALL access mode option is not selected, then the extraction will fail.

Dealing with Oracle Schemas that move from one Server to another or from one Instance to another

When a schema is delivered in V1 from Server A / Instance 1 and is then delivered in V2 from Server B / Instance 2 (i.e. in the CAST Delivery Manager Tool the cloned package in V2 has been modified to point to the same identical schema on Server B / Instance 2) the default behavior on re-analysis of the same schema on a different Server or Instance will consider that the schema is in fact two distinct schemas even though they have the same name. This causes "noise" when consulting version comparison data (the schema will appear as deleted / added between V1 and V2 snapshots), in violation monitoring (removed / added) and it invalidates the content of the Action Plan.

If you find yourself in this situation, you can use a tool provided by CAST that will automatically modify the V2 schema extraction data to contain the same Server / Instance name as used in V1. In this way, the schema will not be considered new in snapshot V2. The tool must be run BEFORE you generate the V2 snapshot. You can find out more information about this in the CAST Management Studio help.

Note about moving schemas from on server/instance to another:

If a schema has been moved from one server/instance to another, you should ensure that the original and the new schema are fully identical. Any differences will be picked up by the CAST analyzer. In particular, the Oracle IDs (OIDs) must be maintained: CAST recommends using the impdp parameter transform=OID:y:type to move the schema. This will preserve the original OID values.

Reuse existing CAST extractor/delivery output

If you have output from previous extractions carried out with a standalone CAST SQL Extractor, then you can use these options to package this output for subsequent analysis in the CAST Management Studio.

In the standalone extractors, extraction is a two step process and results in two outputs:

extraction to file (step 1)
transformation of these files and delivery (step 2)

The option you choose here in the CAST Delivery Manager Tool depends which output you want to use:

Reuse existing CAST extractor output

Requires a .castextraction file. Corresponds to the output of step 1.

Reuse existing CAST delivery output

Requires a .uaxdirectory file. Corresponds to the output of step 2.

Please note that if your existing CAST extractor output is for an entire Oracle instance you must modify the .uaxdirectory file before you can:

use the browse button in the CAST Delivery Manager Tool to select the required schemas > no schemas will be found
manually enter a schema name or names and try to Package the schemas in the CAST Delivery Manager Tool > the Package action will fail

Please add the following line to your .uaxdirectory file (you can edit it with a Text editor) within the <UAXFiles> tag:

<UAXOption name="schema" value="SCHEMA1,SCHEMA2,SCHEMA3"/>

Where SCHEMA1 refers to an Oracle schema you want to package. If you need to package ALL the schemas, you must manually enter them all by name in this line.

An extraction of the entire Oracle instance would have been achieved as follows:

- by entering nothing in the Database Parameters field in the GUI based extractors - by omitting the -parameters schema=SCHEMA_NAME in command line based extractions

Regardless of the option you choose, the configuration is identical:

Server Version File

Use this option to select either the .castextraction or the .uaxdirectory file.

Schemas to extract

See identical option above - this option functions in exactly the same way, however, for the

option, no connection to a server is made. Instead, the contents of the .castextraction or the .uaxdirectory are explored and the schemas that were previously extracted will be offered for selection.

Notes:

When using a .castextraction file:

- In CAST AIP ≥ 8.3.4 this option is not mandatory. If you do not explicitly select a schema, ALL schemas available in the .castextraction file will be automatically selected for packaging.

- In CAST AIP ≤ 8.3.3 this option is mandatory and you must explicitly select a schema either using the option or the option for packaging. Therefore, if you want to select all schemas in the selected .castextraction then you need to specifically choose them using this option.

When using a .uaxdirectory file:

- This option is mandatory and you must explicitly select a schema either using the option or the option for packaging. Therefore, if you want to select all schemas in the selected .uaxdirectory then you need to specifically choose them using this option.

When using either .castextraction file or .uaxdirectory file:

- When packaging Version N+1 where Version N was packaged with the "live" CAST extractor for Oracle option, then you MUST ensure that you explicitly select the "PUBLIC" schema for packaging as well as the other schema(s) you require in Version N+1. This is because when a "live" packaging is completed using the CAST extractor for Oracle option, objects belonging to the PUBLIC schema will also be automatically packaged when they are used by objects in other schemas that are selected for packaging. Omitting the PUBLIC schema in Version N+1 will mean that there will be a difference in the packaging results between Version N and N+1. In CAST AIP ≥ 8.3.4 and when using a .castextraction file, this can be achieved by not selecting anything since doing so will ensure ALL schemas are packaged, but this may also include schemas you do not want.

JVM Memory Size

This option is only visible when you have selected the Reuse existing CAST extractor output option. When you have chosen this option you will select a .castextraction file - this file is in a format that requires further processing by CAST using Java based extractors before it can be packaged for delivery.

As such, this option allows you to configure the Maximum Java Heap Size of your JVM for use during the database/schema browse and extraction processes. By default 1GB of memory is allocated and in most scenarios this value can be left as it is.

You may need to change the amount of memory allocated to the JVM if you are getting out of memory exceptions for the Java Heap Space when you either browse to select a database/schema or when you use the Package action to invoke the extraction - this can occur more specifically when attempting to extract a large single database/schema or multiple databases/schemas. Please increase the memory allocation until you no longer receive errors, however, please bear the following in mind:

32bit JRE: If you are running the CAST Delivery Manager Tool via the CAST AIC Portal and have installed a 32bit JRE for this purpose, or when the CAST Delivery Manager Tool is launched from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation), CAST recommends that you do not increase the JVM Memory Size value to above 2GB, otherwise you risk running out of system memory.
If your database/schema extraction consistently runs out of memory despite having allocated the maximum possible with a 32bit JRE, you may need to ensure you are using a 64bit JRE to take advantage of increased memory availability. To do so, you must launch the CAST Delivery Manager Tool from the CAST AIC Portal, rather than directly from the CAST Management Studio (when it will use a 32bit JRE that is provided with the CAST installation). And before you do so, you must manually modify the shortcut to the CAST Delivery Manager Tool (available in the Windows Start menu) to replace the existing path to the 32bit JRE (C:\Windows\SysWOW64\javaws.exe) with your 64bit JRE path ("C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" for example). You can add the -Xmx parameter (maximum memory allocation), that will allow the process to use the memory defined. The shortcut should now look similar to this:

"C:\Program Files\java\jre1.7.0_51\bin\javaws.exe" -Xmx4096M -localfile
"C:\Users\<user_name>\AppData\LocalLow\Sun\Java\Deployment\cache\6.0\6\1c274146-29c73860"