Monthly Archives: January 2013

HugePages Configuration and Monitoring for Oracle

Here is a blog I did earlier last year when trying to get HugePages configured on a server that was running Oracle 10g.

Implementing HugePages has become common practice with Oracle 11g and is fairly well documented in MOS Note 361468.1.
The basics steps are as follows:

* Set the memlock ulimit for the oracle user.
* Disable Automatic Memory Managment if necesary as it is incompatible with HugePages.
* Run the Oracle supplied hugepages_settings.sh script to calculate the recommended value for the vm.nr_hugepages kernel parameter.
* Edit /etc/sysctl.conf with the vm.nr_hugepages with the recommeneded setting.
* Reboot the server

Unfortunately, The database we were working with was 10g. As it turns out, there are some differences between
Oracle 10 and 11, mainly that there is no HugePage logging in the alert log on version 10. See MOS Note: 1392543.1

Click here to see what to look for and how to troubleshoot HugePages implementation.

Configuring GoldenGate Replication and Heartbeat Table to Measure Lag

In my previous posts, I showed how you can install GoldenGate.  Now let’s setup replication while also presenting a reliable method of measuring the lag time between the source and target databases by using a “heartbeat table” as defined in the Oracle Note mentioned below.

Oracle GoldenGate ­ Heartbeat process to monitor lag and performance in GoldenGate [ID 1299679.1]

I will also be using the Integrated Capture environment here that will demonstrate how to register the capture process with the database.

The basic idea is this:

  • the heartbeat table on the source is updated at a predetermined interval
  • the extract capture process would capture the updates to the table
  • the extract capture and pump processes each add their group name and date to the heartbeat record
  • the information in the record will be used to calculate the lag between the processes on the source and target servers

Scripts

All the files and scripts mentioned in this article are in the zip file that can be downloaded from here.  Most of them were downloaded from Oracle’s note mentioned above, but they do have some modification due to differences in environments.  There is no guarantee that they will work for you.

Install the Database Objects

Create the heartbeat table in the source database

sourcedb> cd source/scripts
sourcedb> sqlplus ggate/ggate
SQL> @heartbeat_table_source_TS_v9.sql

Create the heartbeat and heartbeat_history tables in the target database

targetdb> cd target/scripts
targetdb> sqlplus ggate/ggate
SQL> @heartbeat_table_target_TS_v9.sql

Setup Replication

Configuration on Source Server

sourcedb> cd $GG_HOME
sourcedb> ggsci
GGSCI> DBLOGIN USERID ggate, PASSWORD ggate
GGSCI> MININGDBLOGIN USERID ggate, PASSWORD ggate
GGSCI> REGISTER EXTRACT cgghb DATABASE
GGSCI> ADD EXTRACT cgghb, INTEGRATED TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL /u01/app/oracle/product/gg/dirdat/Hb, EXTRACT cgghb
GGSCI> ADD EXTRACT pgghb, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/Hb
GGSCI> ADD RMTTRAIL /u01/app/oracle/product/gg/dirdat/hb, EXTRACT pgghb
GGSCI> exit

Create the following files in the $GG_HOME/dirprm directory. These can also found in the zip file downloaded earlier.  Keep in mind that you will need to modify the oby files for your environment.

sourcedb> cd dirprm
sourcedb> ls -lhF
total 80
-rw-r--r-- 1 oracle oracle 272B Jan 9 09:02 cgghb.inc
-rw-r--r-- 1 oracle oracle 808B Jan 9 09:02 cgghb.prm
-rw-r--r-- 1 oracle oracle 29B Jan 9 09:02 dblogin.oby
-rw-r--r-- 1 oracle oracle 138B Jan 9 09:02 env.oby
-rw-r--r-- 1 oracle oracle 54B Jan 9 09:02 iclogin.oby
-rw-r--r-- 1 oracle oracle 82B Jan 9 09:02 maint.oby
-rw-r--r-- 1 oracle oracle 208B Jan 9 09:02 pgghb.inc
-rw-r--r-- 1 oracle oracle 327B Jan 9 09:02 pgghb.prm

Startup the extract processes

sourcedb> cd $GG_HOME
sourcedb> ggsci
GGSCI> start extract *
Sending START request to MANAGER ...
EXTRACT CGGHB starting
Sending START request to MANAGER ...
EXTRACT PGGHB starting
GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING 
EXTRACT RUNNING CGGHB 00:00:03 00:00:06 
EXTRACT RUNNING PGGHB 00:00:00 00:00:01

Configuration on Target Server

targetdb> cd $GG_HOME
targetdb> ggsci
GGSCI> dblogin userid ggate, password ggate
GGSCI> add checkpointtable ggate.checkpoint
GGSCI> add replicat rgghb, exttrail /u01/app/oracle/product/gg/dirdat/hb, checkpointtable ggate.checkpoint
GGSCI> exit

Copy the target files over to the $GG_HOME/dirprm directory. These can also found in the zip file downloaded earlier.

targetdb> cd dirprm
targetdb> ls -lhF
-rw-r--r-- 1 oracle oracle 29B Jan 9 09:02 dblogin.oby
-rw-r--r-- 1 oracle oracle 142B Jan 9 09:02 env.oby
-rw-r--r-- 1 oracle oracle 139B Jan 9 09:02 hb_setup.oby
-rw-r--r-- 1 oracle oracle 82B Jan 9 09:02 maint.oby
-rw-r--r-- 1 oracle oracle 1.0K Jan 9 09:02 rgghb.inc
-rw-r--r-- 1 oracle oracle 431B Jan 9 09:02 rgghb.prm

Startup the replicat process

targetdb> cd $GG_HOME
targetdb> ggsci
GGSCI> start replicat *
Sending START request to MANAGER ...
REPLICAT RGGHB starting
GGSCI> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING 
REPLICAT RUNNING RGGHB 00:00:00 00:00:03

Start the Heartbeat Timestamp Script

On the Source Database Server, start the heartbeat_timestamp_TS_v9.sh script. This script will initially populate the table with a row containing information about the extract capture process. Afterwards, it will update the record with the current time at the specified interval. Both the capture name and the interval time are specified within the script. This is an Oracle supplied script that is supplied in Note ID 1299679.1 mentioned earlier; however, it was designed to work with classic capture, not integrated capture, so I made some modification to it accordingly since the output of the “info showch” command differs between the two capture modes.

sourcdb> nohup heartbeat_timestamp_TS_v9.sh &

 Monitoring the Lag Time

On the target database server, connect to Oracle and run the heartbeat_lag.sql script.
Note that the output from the sql script was modified for brevity and formatting.

> sqlplus ggate/ggate

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

GGATE:RPT> @heartbeat_lag.sql

hb_lag_01

hb_lag_02

IE9 Certificate Error When Trying to Connect to OEM

When trying to access OEM, the following certificate error is displayed in IE9, and clicking on “Continue to this website” does not do anything. One possible cause of this error is a result of installing Windows update KB 2661254 .

Certificate Error

Certificate Error

To get by this error, run a command prompt as administrator and execute the command shown below. This will enable logging only and not block key lengths of less than 1024 bits.

certutil -setreg chain\EnableWeakSignatureFlags 8
Admin cmd terminal

Admin cmd terminal

Then restart IE and then you can get by the certificate error.

Oracle GoldenGate Installation

Introduction

This document describes how to Install GoldenGate 11.2.1.0.1 for one way replication with sequence and DDL support.

Environment

Source Database Server

Server Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Database version is 11.2.0.2
Hostname is server1

Target Database Server

Server Solaris 5.11
Database version is 11.2.0.3
Hostname is server2

GoldenGate Software Downloads

Based on your environment, download the proper version of GoldenGate and copy it over to source and target servers. Downloads an be found on Oracle’s website.

GoldenGate Installation on Source Server

Unzipping the Software

Login to the source server as the Oracle software owner, usually oracle, and copy the downloaded software to the golden_gate directory shown below.

> cd /u01/app/oracle/product/golden_gate
> unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc

> tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

Environment Settings

Add the following lines to $HOME/.bash_profile, or whatever file is used to set oracle’s environment when logging into the server.

export PATH=$GG_HOME:$PATH
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export GG_HOME=/u01/app/oracle/product/golden_gate

Source the file to set your current environment.

> . .bash_profile

Installation

After setting your environment, you can then login to ggsci and install the GoldenGate software

> cd /u01/app/oracle/product/golden_gate
> ggsci
GGSCI> create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/golden_gate
Parameter files /u01/app/oracle/product/golden_gate/dirprm: already exists
Report files /u01/app/oracle/product/golden_gate/dirrpt: created
Checkpoint files /u01/app/oracle/product/golden_gate/dirchk: created
Process status files /u01/app/oracle/product/golden_gate/dirpcs: created
SQL script files /u01/app/oracle/product/golden_gate/dirsql: created
Database definitions files /u01/app/oracle/product/golden_gate/dirdef: created
Extract data files /u01/app/oracle/product/golden_gate/dirdat: created
Temporary files /u01/app/oracle/product/golden_gate/dirtmp: created
Stdout files /u01/app/oracle/product/golden_gate/dirout: created

GGSCI> exit

Create Database User in Source Database

> sqlplus / as sysdba

SQL> CREATE USER GGATE
IDENTIFIED BY ggate
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

SQL> ALTER USER GGATE DEFAULT ROLE ALL;

SQL> GRANT UNLIMITED TABLESPACE TO GGATE;

Here are the privileges required for the GoldenGate Extract process.

SQL> grant create session to ggate;

SQL> grant alter session to ggate;

SQL> grant resource to ggate;

SQL> grant connect to ggate;

SQL> grant select any dictionary to ggate;

SQL> grant flashback any table to ggate;

SQL> grant select any table to ggate;

SQL> grant select on dba_clusters to ggate;

SQL> grant execute on dbms_flashback to ggate;

SQL> grant select any transaction to ggate;

Enabling Database-Level Supplemental Logging

GGSCI provides commands to configure the source database to log the appropriate key

values whenever it logs a row change, so that they are available to Oracle GoldenGate in

the redo record. By default, the Oracle database only logs column values that are changed.

The appropriate command must be issued before you start Oracle GoldenGate processing.

Because redo volume is increased as the result of logging key values, you might want to

wait until just before you start Oracle GoldenGate processing to enable the logging.

Otherwise, you can issue them any time before processing starts.

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify that supplemental logging is enabled at the database level with this command :

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

The output must be YES or IMPLICIT.

Create the GLOBALS file

> cd $GG_HOME

> ggsci

GGSCI> edit params ./GLOBALS
-- DDL support
GGSCHEMA ggate

Exit out of the editor when done.

Enabling Sequence Support

Oracle GoldenGate ensures that the target sequence values will always be higher than

those of the source (or equal to them, if the cache is 0).

The following SQL script will prompt for a username; enter ggate at the prompt.

> sqlplus / as sysdba

SQL> @sequence

SQL> alter table sys.seq$ add supplemental log data (primary key) columns;

Enabling DDL Support

The following SQL scripts will prompt for a username; enter ggate at the prompt.

> sqlplus / as sysdba

SQL> grant execute on utl_file to ggate;

SQL> @marker_setup

SQL> @ddl_setup

SQL> @role_setup

SQL> GRANT GGS_GGSUSER_ROLE TO ggate;

SQL> @ddl_enable

Configure the MGR File

GGSCI> edit params mgr

port 7809
userid ggate@SID1, password ggate

Exit out of the editor when done. Then, start the manager process.

GGSCI> start mgr

GoldenGate Installation on Target Server

Unzipping the Software

Login to the source server as the Oracle software owner, usually oracle, and copy the downloaded software to the golden_gate directory shown below.

> cd /u01/app/oracle/product/golden_gate
> unzip ogg112101_fbo_ggs_Solaris_sparc_ora11g_64bit.zip

Archive: ogg112101_fbo_ggs_Solaris_sparc_ora11g_64bit.zip
inflating: fbo_ggs_Solaris_sparc_ora11g_64bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc</pre>

> tar -xf fbo_ggs_Solaris_sparc_ora11g_64bit.tar

Environment Settings

Add the following lines to $HOME/.bash_profile, or whatever file is used to set oracle’s environment when logging into the server.

export PATH=$GG_HOME:$PATH
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export GG_HOME=/u01/app/oracle/product/golden_gate

Source the file to set your current environment.

> . .bash_profile

Installation

After setting your environment, you can then login to ggsci and install the GoldenGate software

> cd /u01/app/oracle/product/golden_gate
> ggsci
GGSCI> create subdirs

Creating subdirectories under current directory /u01/app/oracle/product/golden_gate
Parameter files /u01/app/oracle/product/golden_gate/dirprm: already exists
Report files /u01/app/oracle/product/golden_gate/dirrpt: created
Checkpoint files /u01/app/oracle/product/golden_gate/dirchk: created
Process status files /u01/app/oracle/product/golden_gate/dirpcs: created
SQL script files /u01/app/oracle/product/golden_gate/dirsql: created
Database definitions files /u01/app/oracle/product/golden_gate/dirdef: created
Extract data files /u01/app/oracle/product/golden_gate/dirdat: created
Temporary files /u01/app/oracle/product/golden_gate/dirtmp: created
Stdout files /u01/app/oracle/product/golden_gate/dirout: created

GGSCI> exit

Create Database User in Target Database

> sqlplus / as sysdba

SQL> CREATE USER GGATE
IDENTIFIED BY ggate
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

SQL> ALTER USER GGATE DEFAULT ROLE ALL;

SQL> GRANT UNLIMITED TABLESPACE TO GGATE;

Here are the privileges required for the GoldenGate Replicat process.

SQL> grant create session to ggate;

SQL> grant alter session to ggate;

SQL> grant resource to ggate;

SQL> grant connect to ggate;

SQL> grant select any dictionary to ggate;

SQL> grant select any table to ggate;

-- ddl support

SQL> grant create table to ggate;

SQL> grant create any table to ggate;

SQL> grant lock any table to ggate;

SQL> grant execute on utl_file to ggate;

– the following can be done at a later time when the target tables are know

SQL> grant insert,update,delete on [ALL TARGET TABLES]

Add Checkpoint Table

> cd $GG_HOME
> ggsci
GGSCI> dblogin userid ggate, password ggate
GGSCI> add checkpointtable ggate.checkpoint

Create the GLOBALS file

> cd $GG_HOME
> ggsci
GGSCI> edit params ./GLOBALS

-- DDL support
GGSCHEMA ggate
CHECKPOINTTABLE ggate.checkpoint

Exit out of the editor when done.

Configure the MGR File

GGSCI> edit params mgr

port 7809
userid ggate@SID2, password ggate

Exit out of the editor when done, then start the manager process.

GGSCI> start mgr

This completes the GoldenGate Software Installation.

The software is now ready to be used for replication.

GoldenGate Integrated Capture

What is Integrated Capture

Instead of reading from the redo/archive logs, the Oracle GoldenGate Extract process interacts directly with a database logmining server to receive data changes in the form of logical change records (LCR).

Benefits

Some of the benefits of Integrated Capture are :

  • supports more data and storage types as compared to classic capture
  • automatic integration with the RMAN log retention feature to ensure that required archive logs are not deleted
  • transparent support with RAC for the addition or removal of nodes
  • automatic support of Transparent Data Encryption

To support all Oracle data and storage types, the compatibility setting of the source database must be at least 11.2.0.3.0 with the 11.2.0.3 Database specific bundle patch for Integrated Extract 11.2.x (Doc ID 1411356.1).

Database User Privileges

In addition the privileges required for Classic Capture, two more privileges are required.

exec dbms_goldengate_auth.grant_admin_privilege(‘GGATE’);
grant select on V_$DATABASE to ggate;

Replication

The GoldenGate installation for Integrated Capture is no different than if the Classic Capture was being used.  In fact, you may run both types of extracts on the same server simultaneously.  The differences are in the login and how the extract capture process is created.  For a complete installation, you check look at one of my previous posts here.  Let’s see how it works.

Adding the Capture Process

GGSCI> DBLOGIN USERID ggate, PASSWORD ggate
GGSCI> MININGDBLOGIN USERID ggate, PASSWORD ggate
GGSCI> REGISTER EXTRACT cname DATABASE
GGSCI> add extract cname, <em><strong>integrated tranlog</strong></em>, begin now
GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/lt, extract cname

The differences from Classic Capture are highlighted and italicized above.

In addition, you should put the miningdblogin in the capture parameter file as well.

TRANLOGOPTIONS MININGUSER ggate, MININGPASSWORD ggate

The capture process above was tested using GG 11.2.1.0.1 on an Oracle 11.2.0.3 database.