1. Upgrading to Oracle GoldenGate Monitor
  2. Upgrading for SQL Server

11 Upgrading for SQL Server

This chapter describes the tasks you perform to upgrade Oracle GoldenGate Monitor with a SQL Server database.

This chapter includes the following sections:

11.1 Upgrade Readiness Check

Before beginning an upgrade, you must use the UA to perform an upgrade readiness check. The UA runs a read-only check on various database contents including the schema version, required tables, and columns. If the schema version is already at the upgraded or latest version of Oracle GoldenGate Monitor or there are unexpected or missing tables or columns found, the readiness test will fail. If a failure occurs, the UA provides the test details and any exceptions in the UA logs, and the readiness test results are provided in the ORACLE_HOME/oracle_common/upgrade/logs/postreadiness.txt file. Like RCU, UA is included in the product installation.

Use one of the following sections to perform your readiness check:

Parent topic: Upgrading for SQL Server

11.1.1 Performing an 11g Readiness Check

On your existing 11g Oracle GoldenGate Monitor server, create a database user in the database with same default schema as dbo using the following:

use <monitor_11g_database_name>
CREATE LOGIN dbo WITH PASSWORD = <password_of_your_choice>';
create USER <username_of_your_choice> FOR LOGIN dbo  WITH DEFAULT_
SCHEMA = dbo;
grant select to <username_of_your_choice>

This user is used only in the readiness check wizard and not in the upgrade schema wizard. In the Monitor 11g upgrade schema wizard, use the DBA user only.

Navigate to ORACLE_HOME/oracle_common/upgrade/bin and enter:

For Linux or UNIX:

$ ./ua -readiness

For Windows:

C:\ ua.bat -readiness

Navigate the UA screens as instructed in the following table to upgrade your server.

Screen Upgrade Action

Welcome

Click Next.

Schemas

Ensure Schemas is selects and click Next.

Available Components

  1. Select Oracle GoldenGate Monitor.

  2. Click Next.

OGGMON Schema

  1. Enter the Oracle GoldenGate Monitor schema name and password.

  2. Click Next.

Readiness Summary

Click Next.

Readiness Check

Click Continue. You can use the View Readiness Report button to view your results.

Readiness Success

Click Close.

Parent topic: Upgrade Readiness Check

11.1.2 Performing a 12.1.3 Domain Readiness Check

On your existing Oracle GoldenGate Monitor server, navigate to ORACLE_HOME/oracle_common/upgrade/bin and enter:

For Linux or UNIX:

$ ./ua -readiness

For Windows:

C:\ ua.bat -readiness

Navigate the UA screens as instructed in the following table to upgrade your server.

Screen Upgrade Action

Welcome

Click Next.

Schemas

Ensure Schemas is selects and click Next.

Available Components

  1. Select Oracle GoldenGate Monitor.

  2. Click Next.

OGGMON Schema (SQLSERVER)

  1. Enter the Oracle GoldenGate Monitor schema name and password.

  2. Click Next.

OGGMON Schema Prefix

  1. Enter SQLSERVER.

  2. Click Next.

Readiness Summary

Click Next.

Readiness Check

Click Continue. You can use the View Readiness Report button to view your results.

Readiness Success

Click Close.

Parent topic: Upgrade Readiness Check

11.2 Schema Upgrade Instructions for SQL Server Database Users

Upgrading Oracle GoldenGate Monitor Server for SQL Server databases is a two-stage process:

  1. Update the SQL Server Database by Running RCU

  2. Upgrade the Database by Running UA

Oracle GoldenGate Monitor 12c (12.2.1.3.0) supports SQL Server versions 2008 and 2012. See "Install the Database Software" in Installing and Configuring Oracle GoldenGate Monitor.

Parent topic: Upgrading for SQL Server

11.2.1 Update the SQL Server Database by Running RCU

Since Oracle GoldenGate Monitor Server is released with WebLogic Server 12c (12.2.1) with JRF and to comply with Fusion Middleware standards and requirements, you must upgrade the existing Oracle GoldenGate Monitor by adding STB schemas to the SQL Server database.

Before you begin, do the following:

  • set: 

    ISOLATION LEVEL, ALTER DATABASE $(DATABASE_NAME) SET READ_COMMITTED_SNAPSHOT ON

  • Ensure the database is case-sensitive:

    DECLARE @collate sysname
   select @collate = convert(sysname, serverproperty('Collation'))
IF ( charindex(N'_CI', @collate) > 0 )
BEGIN
   select @collate = replace(@collate, N'_CI', N'_CS')
   exec ('ALTER database $(DATABASE_NAME) COLLATE ' + @collate)
END

To add these dependent schemas, use RCU, which is included in the product installation. To launch RCU, navigate to ORACLE_HOME/oracle_common/bin and enter: 

$ ./rcu

RCU opens, displaying the Welcome screen.

Navigate the RCU screens as instructed in the following table to update the database. If you want further information on any screen, click its name in the left-hand column.

Screen Upgrade Action

Welcome

Click Next.

Create Repository

  1. Select System Load and Product Load.

  2. Click Next.

Database Connection Details

  1. Enter the Oracle GoldenGate Monitor database details.

  2. Click Next.

Select Components

  1. Under the AS Common Services node, select the following schemata:

    • Audit Services

    • Audit Services Append

    • Audit Services Viewer

    • Oracle Platform Security Services

    Note: Do not select the Monitor Server schema in this step.

  2. Click Next.

    A progress window appears, checking off each schema as its prerequisites are met.

  3. Click OK.

Schema Passwords

  1. Enter the necessary password, if it's not already there, and confirm it.

  2. Click Next.

Summary

Review the schema information and click Create.

Creation Summary

Click Close.

Parent topic: Schema Upgrade Instructions for SQL Server Database Users

11.2.2 Upgrade the Database by Running UA

With the repository created, next run the Upgrade Assistant to upgrade the database to version 12.2.1. Navigate to ORACLE_HOME/oracle_common/upgrade/bin and enter:

For Linux or UNIX:

$ ./ua

For Windows:

C:\ ua.bat

The UA Welcome screen appears.

Navigate the UA screens as instructed in the following table to upgrade your server. If you want further information on any screen, click its name in the left-hand column.

Screen Upgrade Action

Welcome

Click Next.

Schemas

Ensure Schemas is selects and click Next.

Available Components

  1. Select Oracle GoldenGate Monitor.

  2. Click Next.

Prerequisites

  1. Select all three prerequisites.

  2. Click Next.

OGGMON Schema

  1. Ensure that the proper information is supplied.

  2. Click Next.

OGGMON Schema Prefix

  1. Enter the same schema prefix you used when creating the STB schema in RCU.

  2. Click Next.

Examine

Click Next.

Upgrade Summary

Click Upgrade.

Upgrade Progress

Click Next.

Upgrade Success

Click Close.

This completes the Oracle GoldenGate Monitor Server for SQL Server databases upgrade process.

Parent topic: Schema Upgrade Instructions for SQL Server Database Users

11.3 Upgrading the WebLogic Domain

Next, you must create a new domain with an 11g upgrade and with a 12c upgrade you update the existing 12.1.3 WebLogic domain configuration to a 12.2.1 domain. Navigate to ORACLE_HOME/wlserver/common/bin and enter:

For Linux or UNIX:

$ ./config.sh

For Windows:

C:\ config.bat

The Configuration Wizard starts and the Configuration Type screen appears.

To create the domain, navigate the Configuration Wizard screens providing the necessary information as described on the following table and providing the details from your previous domain (Tomcat for 11g and WebLogic domain for 12c):

Screen Configuration Action

Create Domain

Ensure Create a new domain is selected then:

  • In Domain location, enter the absolute path outside of ORACLE_HOME.; for example: 

    /scratch/my_user/oggmon1221_domain

  • Click Next.

Templates

  1. Select Oracle GoldenGate Monitor Server Domain-12.2.1 (oggmon). Additional packages are automatically selected.

  2. Click Next.

Administrative Account

  1. Enter a Name and Password (and confirm the password). These are your WebLogic Server console log-in credentials.

  2. Click Next.

Domain Mode and JDK

  1. In Domain Mode, select Production.

  2. In JDK, select your preferred JDK; for example:

    Oracle HotSpot 1.8.0_40-ea /usr/java/jdk1.8.0_40

  3. Click Next.

Database Configuration Type

  1. Ensure RCU data radio button is selected.

  2. Enter the database connection information.

  3. Enter the Schema owner, which is the prefix you specified when creating your repository appended with _STB; for example, DEV_STB.

  4. Enter the Password.

  5. Click Get RCU Configuration.

  6. Click Next.

JDBC Component Schema

Verify the schema information and click Next.

JDBC Component Schema Test

Verify the schema information and click Next.

Credentials

  1. For each Key Name, enter a Username and Password.

  2. Click Next.

Advanced Configuration

  1. Select Administrative Server and Managed Serves, Cluster, and Coherence.

  2. Click Next.

Administration Server

By default, the server name is AdminServer and the port is 7001, although you can always change default values. For example, you can change the server name AdminServer to OGGMONAdminServer.

If port 7001 is already in use, replace it with an unused port number. Make selections as necessary and click Next.

Managed Servers

The managed server name is already populated with the default, MONITORSERVER_server1, and the port number with 7003. You can change these values, if desired. Also, if port 7003 is already in use, replace it with an unused port number.

Either accept the defaults of update the data and click Next.

Clusters

Click Next.

Coherence Cluster

Click Next.

Machines

Click Next.

Configuration Summary

Review the configuration details and click Create.

Configuration Progress

When configuration creation is complete, click Next.

Configuration Success

  1. Make note of the Administration Server URL for MonitorServer administration.

  2. Click Finish.

Parent topic: Upgrading for SQL Server

11.4 Upgrading the Oracle GoldenGate Monitor Server Configuration

The UA updates the Monitor Server configuration files to the latest 12.2.1 configuration by maintaining your existing set property values. It creates a oggmon.properties file in the domain_name/config/monitorserver/cfg/ directory, with an encrypted password entry that is used in JMX communication between Monitor Server and Agent.

Navigate to ORACLE_HOME/oracle_common/upgrade/bin and enter:

For Linux or UNIX:

$ ./ua

For Windows:

C:\ ua.bat

Navigate the UA screens as instructed in the following table to upgrade your server.

The UA Welcome screen appears.

Screen Upgrade Action

Welcome

Click Next.

Schemas

Ensure Schemas is selects and click Next.

WebLogic Components

  1. Select WebLogic Component Configurations and enter the domain directory to be upgraded.

  2. Click Next.

Component List

Click Next.

Prerequisites

  1. Select all three prerequisites.

  2. Click Next.

OGG Monitor Installed Version

  1. Select OGG Monitor 11g or OGG Monitor 12.1.3.

  2. Click Next.

OGGMON Schema

  1. Enter or select the OGGMON schema connect string, and the DBA and schema user names and passwords.

  2. Click Next.

Examine

Click Next.

Upgrade Summary

Click Upgrade.

Upgrade Progress

Click Next.

Upgrade Success

Click Close.

This completes the Oracle GoldenGate Monitor Server for Oracle database and domain upgrade process.

Parent topic: Upgrading for SQL Server

11.5 Next Step: Perform Post-Upgrade Tasks

With the upgrade process complete for your database type, you next need to perform certain post-upgrade tasks, which are described in Performing Post-Upgrade Tasks.

Parent topic: Upgrading for SQL Server