Database Change Management - Capturing and Comparing

Purpose

This OBE describes and shows you how to create a Dictionary Baseline and how to perform a database comparison.

Time to Complete

Approximately ¾ hour

Topics

This OBE covers the following topics:

	Overview
	Viewing Dictionary Baselines
	Creating a Dictionary Baseline
	Comparing Baseline Objects With Upgraded Versions
	Comparing Database Initialization Parameters
	Summary
	Related information

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment you are using. They are provided to give you an idea of where to locate specific functionality in the Oracle Enterprise Manager 10g Grid Control console.

Overview

In the course of day-to-day operations as system or application administrators, you experience the need for a single point-of-access to keep track of metadata changes in your databases. Change Management's simple and easy to use interface provides you with the necessary insights for identifying, correlating and resolving problems that maybe caused by metadata changes. The Change Management Pack tightly integrates the ability to capture dictionary baselines and compare metadata definitions into Enterprise Manager Grid Control. Users access Dictionary Baselines and Dictionary Comparisons from the Databases page, and use the Enterprise Manager Job system to schedule Capture and Comparison jobs.

Back to Topic List

Viewing Dictionary Baselines

After logging into Oracle Enterprise Manager 10g Grid Control Release 2 (10.2.0.2.0) perform the following steps to capture a data dictionary baseline:

1.	To navigate to Database Targets page, click the Targets tab.
2.	Click Databases
3.	Click the database instance name that you want you use, for example click Apps11509. Note: There are two entry points to the Change Management screens. Across Databases: You can access Change Management from the Database Targets page to capture and compare schema object definitions. Single Database Target: Alternatively, you can access Change Management from the Administration tab of the target database. You will access Change Management directly from the database target.
4.	Click the Administration tab.
5.	On the Administration page scroll down to the Schema section. Under the Change Management heading click Dictionary Baselines.
6.	The Dictionary Baseline page shows all created baselines. To view the baseline click on its name, or select the baseline and click View. Click the baseline name, for example click Apps11509_baseline.
7.	On the View baseline page, you see the scope specification, all baseline versions created or being created, and the job status associated with each baseline version. If there is more than one baseline version, you may click View Changes since Previous Version. The page will show what objects were added, removed or modified since previous baseline version, giving you a quick overview of the changes occurring between two baseline versions. Click View.
8.	Browse through the different kinds of objects in this baseline. In the Objects section, select Table from the Object Type drop-down list. Type PO in the Schema text box. Enter PO_V into the Object Name text box. Then click Go.
9.	You see a listing of the tables in this schema. Click on any of the table names, for example, `PO_VENDORS`.
10.	Browse the page to review all settings. To return click OK.
11.	To return to the Dictionary Baselines page, click the first element in the locator link.

Back to Topic List

Creating a Dictionary Baseline

You will create a new baseline using the Dictionary Baseline Wizard screens. By creating a Dictionary Baseline you are capturing the definition of the database objects at a specific point in time. Once the baseline is created, it can be referenced anytime in the future.

1.	On the Dictionary Baselines page, click Create. The Create Dictionary Baselines wizard starts.
2.	Step 1: Enter a name for the baseline, for example Apps11509_custom_check_001, and a description if wanted. Click Next.
3.	Step 2: On the Create Dictionary Baseline: Objects page, under the Schema Objects heading as Object Selection Method select Select individual objects from any schema. Note: When you have a large number of application schemas, you may decide to use "Select object types to include and schemas to exclude" rather than schemas to include; for example to include all schema except SYS, SYSTEM and DBSNMP. Under the Schemas to include heading click Add. Enter `PO` into the Schema field and `PO_VENDORS` into the Object Name field. Then click Go. From the list, select t `PO_VENDORS` and then click OK. This selects the object and returns you to the "Create Dictionary Baseline: Objects" page. Note: You have the option to filter selected objects by specifying a name prefix clicking Advanced Options. To include initialization parameters, click Database Attributes. Under Objects to Include click Add again. Enter `APPS` into the Schema field and `AP_CHECKS_PKG` into the Object Name field. Then click Go. Select `AP_CHECKS_PKG` package body and click OK. Click Next.
4.	Step 3: Set the options to schedule the job immediately or at a later time. You can also schedule the job to run repeatedly. By default, the job is run immediately, one time only. Click Next.
5.	Step 4: Review the choices you have made, then click Submit. A job will be submitted with the specified schedule (immediate in this case) to create the baseline definition which will stay persistent in the repository.
6.	Check the confirmation message indicating that the job was submitted successfully. Note: The baseline you have just created is shown in the table, but there is no entry under Versions or Most Recent Version because the baseline version has not been captured yet. The version will show up soon after the capture job starts running.
7.	To view the baseline, select the name, for example Apps11509_custom_check001 and click View.
8.	Select the version that you want to see and click View.
9.	To obtain the definition of an object, click its name, or select its row and click View. For example, select `AP_CHECKS_PKG` and click View.
10.	Click OK to leave the object viewer and return to the table of objects.
11.	Click the last element in the breadcrumb trail to return to the baseline page. For example, click Baseline: Apps11509_custom_check_001.
12.	Select the baseline version and click Generate DDL.
13.	A confirmation message appears and the DDL column of the baseline version indicates Being Generated. Refresh the browser until this column changes to Generated, then click on that link to view the DDL for all the objects in the baseline.
14.	Click Dictionary Baselines, the first element in the breadcrumb trail to return to the Dictionary Baselines page.

Back to Topic List

Comparing Baseline Objects With a Upgraded Versions

You will create a Dictionary Comparison. Dictionary Comparison compares the definitions of database objects and the Comparison can be stored and reused in the future.

1.	Click Targets to navigate to Database Targets .
2.	Click Databases
3.	Click the database instance name that you want you use, for example click Apps11509.
4.	Click Administration .
5.	On the Administration page scroll down to the Schema section. Under the Change Management heading click Dictionary Comparisons.
6.	On the Dictionary Comparisons page click Create. The Create Database Comparisons wizard opens.
7.	Step 1: On the Create Dictionary Comparisons: Left Source page, enter the name for the comparison, for example Apps11509_Comparison001. Select Baseline Version as the left source, and click the flashlight icon to the right of the Baseline field. On the Search and Select: Baseline pop-up page, select a baseline, for example the one you just created Apps11509_custom_check_001. From the Version drop-down list select the appropriate version. Select 1. Click Select. On the Create Dictionary Comparisons: Left Source page click Next.
8.	Step 2: On the Create Dictionary Comparisons: Right Source page, select Database Instance as the right source, and click the flashlight icon to the right of the Database field. On the Search for Database pop-up page, select `Apps11510`. Click Select. On the Create Dictionary Comparisons: Right Source page. Click Next.
9.	Step 3: On the Create Dictionary Comparison: Objects page, under the Schema Objects heading, select "Select individual objects from any schema" from the Object Selection Method drop-down list. In the Schemas to Include section, select Add. Click Go. Select PO and APPS in the list and then click OK. This returns you to the Create Dictionary Comparison: Objects page. Click Next.
10.	Step 4: On the Create Dictionary Comparison: Options page, select Ignore Physical Attributes and Ignore Tablespace. Click Next. Note: This page allows to select other optional comparison attributes like Compare Statistics and others.
11.	Step 5: On the Create Dictionary Comparison: Job Options page, take the default settings. By default, the job is run immediately, one time only. Select Next. Note: You can schedule the job to be executed immediately or at a later time. You can also schedule the job to run repeatedly.
12.	Step 6: Review the choices you have made, then click Submit.
13.	A confirmation message appears. The comparison you have created is listed in the table. There is no entry under Versions or Most Recent Version because the baseline version has not been captured yet. The version will show up soon after the comparison job starts running. To view the comparison select it, for example, Apps11509_Comparison001 and click View.
14.	The Comparison page shows that Version 1 of the comparison is being processed, or that it has completed. After the status changes to Succeeded the table row shows how many objects are the same in the baseline and schema, how many are different, how many are in the baseline only (Left Only) and in the database only (Right Only). Click View.
15.	Click the "not equal" sign next to the `PO_VENDORS` table. The result lists the detailed differences between the objects.
16.	On the result page expand a column, for example, `CIS_ENABLED_FLAG`. Scroll down on the results page. Click Left DDL and Right DDL to view the DDL definitions of the objects side by side.
17.	Click Return. Click the "not equal" sign next to `AP_CHECKS_PKG`. The result page lists the differences line per line. The page lists portions that are the same or different in both sources, for example:

Back to Topic List

Comparing Database Initialization Parameters

The Database Change Management Pack provides the administrator a quick way to compare initialization parameters between two databases. You can save current initialization parameter settings in Dictionary Baselines and compare two sets of initialization parameters as Dictionary Comparisons.

1.	To navigate to Database Targets page click the Targets tab.
2.	Click Databases.
3.	In the Related Links section at the bottom click Dictionary Comparisons
4.	On the Dictionary Comparisons page, click Create.
5.	Step 1: Enter a name for the comparison, for example, type CompareInit001. Select Database Instance as the left source, and click the flashlight icon to the right of the Database field. In the Search for Database pop-up page, select `Apps11509` as the first database for the comparison. Click Select to return to the wizard. Click Next.
6.	Step 2: Select Database Instance as the right source, and click the flashlight icon to the right of the Database field. In the Search for Database pop-up page, select `Apps11510` as the second database for the comparison. Click Select to return to the wizard. Click Next.
7.	Step 3: At the bottom of the page under Database Attributes check Initialization Parameters. Click Next.
8.	Step 4: Select the database comparison options. In this case, leave the default values. Click Next.
9.	Step 5: Set the options to schedule the job to be executed immediately or at a later time. You can also setup a repeatedly job to compare the database initialization parameters periodically. In this case, keep the default, which is running immediately one time only. Click Next.
10.	Step 6: Review the choices you have made, then click Submit. Note: This creates the Comparison definition, which is stored in the repository and can be referenced later. This also submits the job with the specified schedule which is immediate in this case.
11.	You are returned to the Dictionary Comparison page. Review the confirmation message that the job was successfully submitted. The comparison you have just created is shown in the table, but there is no entry under Versions or Most Recent Version because the baseline version has not been captured yet.
12.	To view the comparison select the comparison, for example CompareInit001 and click View.
13.	On the View comparison page, you see the scope specification, comparison options, all comparison versions created or being created, and the job status associated with each comparison version. Click View.
14.	Click on the number next to Different to see a list of the differences in the initialization parameters between the two databases
15.	Review the list of the differences in the initialization parameters between the two databases