1. Cloud Control Database Lifecycle Management Administrator's Guide
  2. Manage Configuration, Compliance and Change Management
  3. Manage Database Schema Changes
  4. Using Change Plans

Using Change Plans

Change Plans are a new feature of the Cloud Control Database Lifecycle Management Pack. Change Plans complement and extend the capabilities of existing Change Management components by allowing users to select and package metadata changes for deployment to multiple databases. Change Plans support database application development methodologies that are not adequately supported by existing Database Lifecycle Management Pack tools such as Schema Synchronizations.

Change Plans are flexible enough to support a variety of development methodologies, yet powerful enough to automate many database administration tasks previously carried out with custom scripts. These tasks include:

  • Deploying project-specific development changes from a shared development database to one or more destination databases such as integration, test, or production staging.

  • Deploying development changes from a stand-alone project development database to an integration database that collects changes from multiple development databases.

  • Upgrading common modules in development databases from a central integration database.

Change Plans are tightly integrated with the other tools in the Database Lifecycle Management Pack. Specifically:

  • Change Plan change requests that create objects can get the object definitions from Change Management Schema Baselines.

  • Change requests that modify objects can use the contents of an object in a Change Management Schema Comparison to specify the change.

  • Change Plans complement Change Management Schema Synchronizations, allowing for finer control of changes and “change-only" change requests.

About Working with Change Plans

The first phase of using a change plan to create or modify object definitions is to plan and define the changes that you want to make. For example, you may want to make one or more changes to an existing object definition in one or more databases. Or, you may want to reproduce one or more object definitions from one schema or database in another schema or database.

Figure 24-1 Steps in a Change Plan


Change Plans sequence

The image shows the steps in a change plan. A change plan is a named container for change requests. You can define change requests to reproduce or modify object definitions at a destination database. A destination database is a database where you want to apply the change requests in a change plan. After you finish planning and defining the changes, evaluate the impact of the changes that you want to make.

To evaluate the impact of the change requests at a particular database, generate a script and an impact report for a change plan and that destination database. The impact report explains the changes that will be made by the script when it executes at the destination database. It also describes any change requests that cannot be applied at the destination database.

To implement the change requests in a change plan at a destination database, execute the script at the destination database.

Creating a Change Plan

This section explains the different methods of creating change plans.

You can create change plans through any of the following ways:

Creating and Applying a Change Plan From a Schema Comparison

This section explains how to create a change plan from a schema comparison.

Prerequisites for Creating a Change Plan

  • Ensure that the Application Developer (AD) is an Cloud Control user who has the following privileges:
    • Connect Target privilege to the development and production-staging databases targets or Connect Any Target privilege
    • DBA privileges to the development database
    • Create Privileges for Job System (Resource Privilege)
    • Create new Named Credential (Resource Privilege)
    • Edit Resource Privilege on the change plans
    • Execute Command Anywhere (Target Privilege)
    • EM_ALL_OPERATOR privilege
  • Ensure that the Database Administrator (DBA) is an Cloud Control user who has the following privileges:
    • Connect Target privilege to the development and production-staging databases targets or Connect Any Target privilege
    • DBA privileges to the development database
    • Create Privileges for Job System (Resource Privilege)
    • Create new Named Credential (Resource Privilege)
    • Manage Change Plans (Resource Privilege)
    • Execute Command Anywhere (Target Privilege)
    • EM_ALL_OPERATOR privilege
  • It is recommended that the development and destination databases are identical at the start of the development work. For example, they may both be at the current production version, or both updated to a common interim development version.
  • The Application Developer would have made changes in the development database. After creating a change plan, the application developer can create and update change items in the change plan through external clients such as SQL Developer. For more information, see Using External Clients to Create and Access Change Plans in Cloud Control.

Creating a Change Plan

Follow these steps to create a change plan:

  1. Log in to Cloud Control as a database administrator (DBA).
  2. Identify the schemas that contain application objects.
  3. Use Metadata Baselines wizard to define a baseline that includes the schemas of interest. Schedule a job to capture the first version of the baseline.
  4. Save the baseline.
  5. Use Schema Comparisons wizard to define a comparison between the baseline version and the development database.
  6. Schedule a job to create the first version of the comparison and save the comparison.
  7. In the Schema Change Plans page, click Create.
  8. Specify a Name and Description for the change plan and click OK to save the change plan.

    Creating a Change Plan
  9. In the Change Items page, click Create From Comparison.
  10. In the Create Change Items from Schema Comparison page, select the Comparison Version created earlier, specify the development database as the Change To side and the production-staging database as the Change From side in the Conversion Assignment and click OK.

    Create Change Items from Schema Comparison page
  11. In the Create Change Items from Schema Comparison: Select Differences page, select:
    • All Differences in the Schema Comparison to add all differences in the comparison to the change plan
    • Specific Differences in the Schema Comparison to select the differences in the comparison you want to add to the change plan. Select the differences.
    Click Finish.
  12. Submit request to apply the Change Plan on the destination database.

Applying a Change Plan

Follow these steps to apply a change plan:
  1. Log in to Cloud Control as a database administrator (DBA).
  2. In the DBA role, examine the Change Plan, evaluating its suitability for application to the proposed database. Remove individual Change requests, if required.
  3. From the Schema Change Plans page, select Create Synchronization from Change Plan.
  4. Specify the details in the Schema Synchronization wizard with the source as the Change Plan instance created earlier. For information about using the Schema Synchronization wizard, see Synchronizing with Production Staging. By default, the synchronization created from change works in the interactive mode.
  5. Schedule script generation.
  6. Check Impact Report and schedule script execution.
  7. Check completed script execution job for errors. If the change plan job failed, do the following:
    • If the failure is due to a condition noted in an impact report error warning, perform the suggested user action.
    • If the failure is due to a condition in the source or destination database that can be fixed manually, fix the problem and perform the operation again.
    • If the failure is in the script execution phase, view the script output in the job details. If the problem can be resolved by actions such as issuing missing grants, fix the problem manually in the database and then click Retry Script Execution.
  8. Fix the errors and submit the change plan creation job again.

Using External Clients to Create and Access Change Plans in Cloud Control

Cloud Control provides support for external clients such as SQL Developer to create and access change plans. You can use these applications to connect to the Cloud Control repository and create change plans and add and update change items in them.

Client users are of two types:

  • Users who can create and access all change plans

  • Users who can access (view and possibly edit) specific change plans

Following are the steps:

  1. Configure the repository database listener to allow access by a trusted client. It is recommended that you make the repository database inaccessible to login from non-trusted clients. For information about configuring the listener, see Oracle® Database Net Services Administrator's Guide 11g Release 2 (11.2).

  2. Set up an Cloud Control administrator for use by an external client.

The following section describes how to set up administrators for change plans, for access from Cloud Control and from external clients.

Setting Up Cloud Control Administrator For Change Plans

Follow these steps:

  1. Log in to Cloud Control as a super administrator.

  2. From the Setup menu, click Security and then select Administrators.

  3. In the Administrators page, click Create.

  4. In the Create Administrator: Properties page, specify the Name and Password for the user. This creates a database user with the specified name and password, as well as creating the Cloud Control administrator. Click Next.

  5. In the Create Administrator: Roles page, click Next.

  6. In the Create Administrator: Target Privileges page, click Next.

  7. In the Create Administrator: Resource Privileges page, select Change Plan Security Class and click the Manage Privilege Grants icon.

  8. In the Create Administrator: Manage Privileges page, do the following:

    • If you want to create an administrator who has all access to all change plans, select Manage Change Plans in the Resource Type Privileges section.

    • If you want to create an administrator who has specific access to one or more change plans, click Add in the Resource Privileges section. In the list of change plans that have been created already, select one or more and click Select. The selected plans are added to the Resource Privileges section. By default, the administrator is granted View Change Plan privilege; you can edit this to grant Edit Change Plan privilege.

  9. Click Continue.

  10. In the Create Administrator: Review page, click Finish to create the new administrator.

  11. For an external client to be able to access change plans using any of these privilege types, follow these steps:

    1. Log in to the repository database as a user with DBA privileges.

    2. Grant the CHANGE_PLAN_USER database role to the database user corresponding to the new administrator (through Schema->Users in Enterprise Manager Administrator, or in SQL Plus).

Submitting Schema Change Plans From SQL Developer Interface

To enable developers to submit their schema changes to Enterprise Manager Schema Change Plans through SQL Developer interface, perform the following manual configuration steps:

  1. Ensure that the repository administrator has configured the repository database to accept remote database connection from SQL developer. You can do this by configuring the repository listener process.
  2. Create a local administrator account on the OMS.
  3. Provide the repository user of the local OMS account privileges to be a change plan user by running the following SQL commands on the repository database as user SYS:
    grant CHANGE_PLAN_USER to PUBLIC;

    or:

    grant CHANGE_PLAN_USER to <repos_user>;
  4. Edit the OMS users resource privileges to give the user access to edit the change plans.