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 11.2.0.2 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 
TZ=America/Chicago

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
LISTENER:
TZ=America/Chicago

>srvctl getenv database -D DB1
DB1:
TZ=America/Chicago

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;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.57.49.394023000 AM EST5EDT

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

SQL> select dbms_scheduler.stime from dual;
STIME
---------------------------------------------------------------------------
17-SEP-14 11.59.49.313001000 AM US/CENTRAL

References

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 11.2.1.0.1.

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 .

Pre-requisites

OGG is installed on source and target systems

Environment

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

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

Trail to Replicat Steps

1. Configure the initial-load processes

Extract

 
extract ildb2
sourceistable
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

 
replicat ilrdb2
END RUNTIME
SETENV(ORACLE_HOME="/u01/app/oracle/product/11.2.0.3/dbhome_1")
SETENV(ORACLE_SID="ORA11G")
SETENV(NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
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

7. Turn off HANDLECOLLISIONS

SEND REPLICAT rdb2, NOHANDLECOLLISIONS

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