Zero Downtime Installation Guide for Oracle

10.3 HotFix 2

Back Next

Prepare the Oracle Databases

Prepare the database on the source system and the target system. Before you begin, make sure that you opened the ports required by Oracle GoldenGate, and that the source and target schemas are the same.

Configure the Oracle databases with directory permissions, and then enable replication and logging on the databases. From the Hub Console, enable ZDT on the source schema and the target schema. A DBA must grant execute permissions to the GoldenGate user on both the source system and target system. Do not insert or update data in either database until you finish installing and configuring ZDT successfully.

If you must update GoldenGate Manager data in the source database while installing ZDT, use system change numbers (SCN) during the data export. Oracle GoldenGate can use the SCNs to replicate the changes after the installation is complete. For more information, see the Oracle GoldenGate documentation.

As the SYSTEM user, upgrade the source and target databases by running

update_javasp.sql

The migration process grants privileges automatically for the

GGS_PARAM_DIR

and the

GRANT SELECT ANY DICTIONARY

SQL> c:/<MDM installation directory>/hub/server/resources/database/oracle/locale/update_javasp.sql
SQL> Create or replace directory GGS_PARAM_DIR as ‘D:/GGS/dirprm’;
SQL> Grant read, write on directory GGS_PARAM_DIR to PUBLIC;

As the SYSTEM user, ensure that you have read and write access to the directory

GGS_PARAM_DIR

in the databases.

SQL > select * from all_directories where directory_name like 'GGS%';

OWNER      DIRECTORY_NAME     DIRECTORY_PATH
---------- ------------------ ----------------------------------------
SYS        GGS_PARAM_DIR      D:/ggs/dirprm

GoldenGate 12 only

. As the SYSDBA user, enable replication on both databases.

ALTER SYSTEM SET enable_goldengate_replication=true;

GoldenGate 12 only

. As the schema user, enable logging for all tables in the schema. Repeat on both databases.

For example, the following script iterates through the tables and creates a

nologging.sql

file in the current directory.

spool nologging.sql
set pagesize 5000
select 'ALTER TABLE ' || TABLE_NAME || ' LOGGING;' from user_tables where LOGGING != 'YES' and TABLE_NAME like 'C^_%' ESCAPE '^';
exit

After the script ends, open the

nologging.sql

file, remove headers, and save.

At an sqlplus prompt, log in to the schema as the ORS user and run the edited

nologging.sql

file.

SQL> @nologgging.sql

For each base object in C_REPOS_TABLE, set the

NOLOGGING

indicator to

. Repeat on both databases.

Exclude all tables ending in _STRP. The _STRP tables must have

NOLOGGING

set to

for the metadata validation to succeed.

For example, replace

C_REPOS_TABLE

in the following script with the name of a base object table and run the script:

UPDATE C_REPOS_TABLE SET NOLOGGING_IND = 0 WHERE NOLOGGING_IND = 1 and table_name != 'C_RBO_BO_CLASS' and table_name != 'C_RBO_BO_HIERARCHY' and table_name != 'C_RBO_REL_TYPE';

As the schema user, launch the MDM Hub Console, and enable the ZDT mode for both schemas.

Optionally, to verify that the settings are correct, open the repository table

C_REPOS_DB_RELEASE

as the schema user.

Ensure that

ZDT_IND

is set to

Ensure that

GLOBAL_NOLOGGING_IND

is set to

As the SYSDBA user, add supplemental log data for the Oracle source database.

C:> sqlplus connect sys as sysdba;
SQL> -- make sure you had previously shutdown normally
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> EXIT

As the schema user, enable the debug log on both the source schema and the target schema.

Start SQL*Plus and log in to the schema.

Open the repository table

C_REPOS_DB_RELEASE

Update the columns related to debug and log files with the following values:

Column Name	Value	Description
debug_ind	1	Enables the debug logging process.
debug_level	500	Enables the most detailed level of debugging. When the value is 500, the debug logging process captures errors, warnings, and informational messages, as well as debugging information.
debug_file_name	user-defined	Specifies a name for the file that contains the debug log information.
debug_file_path	user-defined	Specifies the directory where the file is stored. This is the same as the utl_file_dir directory in the database. A DBA creates the utl_file_dir directory within the spfile scope in the Oracle database instance. You can use Oracle Enterprise Manager or SQL*Plus to set the utl_file_dir.
log_file_size	5	Specifies the maximum size of the database log file in MB. The default is 5.
log_file_number	5	Specifies the number of log files used for log rolling. The default is 5.

Repeat on the other schema.

GoldenGate 12 only

. As the SYSDBA user, grant execute permissions to the GoldenGate user on both the source system and target system.

SQL> grant Execute on DBMS_STREAMS to PUBLIC;
SQL> grant Execute on DBMS_STREAMS_ADM to PUBLIC;

To use Oracle GoldenGate for Oracle version 11.0.2.4 or later, you must grant these execute permissions.

Rename Saved Search

Table of Contents

Zero Downtime Installation Guide for Oracle

Zero Downtime Installation Guide for Oracle

Prepare the Oracle Databases

Prepare the Oracle Databases