Monthly Archives: September 2014

Changing Timezone Settings on Oracle Database Servers

Twice now in the past month, I have run across some clients that are wanting to change the timezone of their database due to a server relocation. Both cases ended up being a bit similar, one a single instance and the other on Exadata. Since I had to lookup how to do it the seconds time, I thought I would blog about it. Here’s the info on how to view the timezone settings and how to change them.

These settings shown were valid for DB and above on Redhat/Oracle Linux 6.

OS Settings

Look at /etc/localtime to see the timezone setting for the server. The
setting should be set to an entry in the /usr/share/zoneinfo directory/subdirectory.

ls -lhF /etc/localtime
lrwxrwxrwx 1 root root 35 May  9 17:54 /etc/localtime -> /usr/share/zoneinfo/America/Chicago

Grid Timezone Setting

Config File in Grid Home

The TZ entry in $GRID_HOME/crs/install/s_crsconfig__env.txt the timezone entry for the Grid Home; hence, the ASM and listener.

> grep TZ $GRID_HOME/crs/install/cat s_crsconfig_db1_env.txt 

Grid Infrastructure Settings

If TZ is set via in the cluster via srvctl, it will take precedence over the s_crsconfig__env.txt setting.

>srvctl getenv listener -l listener

>srvctl getenv database -D DB1

The TZ setting can be set via the following srvctl command:

srvctl setenv listener -l listener -t 'TZ=America/Chicago'

srvctl setenv database -d DB1 -t 'TZ=America/Chicago'

Database Scheduler

The database scheduler works under the timezone setting at statup time. The timezone can be seen and modified with the following statements.

SQL> select dbms_scheduler.stime from dual;

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Central');

SQL> select dbms_scheduler.stime from dual;


How To Change Timezone for 11gR2 Grid Infrastructure (Doc ID 1209444.1)
Dates & Calendars – Frequently Asked Questions ( Doc ID 227334.1 )
Incorrect SYSDATE shown when connected via Listener in RAC ( Doc ID 1390015.1 )

GoldenGate Initial-Load Trail to Replicat – Part 2

This article will demonstrate how to perform a OGG Initial Load using the Trail to Replicat technique. You can see part 1 here . In this article, we get by the bug since we are on a version higher than

Using this method, there is no limit on the amount of data that can be loaded initially. This particular run created 117 2GB trail files on the target to be loaded by the initial load replicat.

Other Initial Load techniques and their advantages and disadvantages can be found here .


OGG is installed on source and target systems


The source database is DB2 on z/OS mainframe with OGG

The target database is Oracle:
Database :
Linux : OEL 6 ( 64 bit )

Trail to Replicat Steps

1. Configure the initial-load processes


extract ildb2
sourcedb db2t, userid ggcap, password ggcap
rmthost target_server, mgrport 7809, tcpbufsize 1048576, tcpflushbytes 1048576
rmtfile ./dirdat/la, megabytes 2048, maxfiles 999
TABLE user.table1;


replicat ilrdb2
UserID ggrep, Password ggrep
SOURCEDEFS ./dirdef/db2t_source_tabs.def
DISCARDFILE ./dirrpt/ilrdb2.dsc, Purge
MAP user.table1, TARGET user.table1;

2. Configure the change-synchronization processes

This is the normal replication methods, so I will not cover them here to keep things short. You can see example of this in some of my other posts.

3. Start the change-synchronization Extract

GGSCI> start extract cdb2 

4. Start the initial-load extract

GGSCI> add extract ildb2 , sourceistable 
GGSCI> start extract ildb2 

You should observe the following:

* the collector starting on target as seen in the ggserr.log
* the trail files in dirdat directory on target ( $GG_HOME/dirdat/rt* )
* report file in dirrpt directory on source

Monitor the ildb2.rpt report file for any errors.

5. Start the initial-load replicat on the target environment

Since we are using normal trail files here, the replicat can be started w/o having to wait on the initial-load extract to complete. We are also using the checkpoint table here so the process can be restarted if necessary.

GGSCI> DBLogin UserID ggrep, Password ggrep
GGSCI> add replicat ilrdb2, ExtTrail ./dirdat/aa, CheckPointTable ggrep.ggs_checkpoint
GGSCI> start replicat ilrdb2

Monitor the report file for the initial-load replicat and wait until it the intiial-load extract is complete and the replicat is at the EOF with no more records to process. If there are no errors, then you can continue on to the next step.

6. Start the change-synchronization Replicat

GGSCI> start replicat rdb2



Lastly, edit the replicat parameter file and remove HANDLECOLLISIONS so that it is not enabled next time the replicat restarts.