Dedomena

Wednesday, 20 July 2016

India's First Exadata - Oracle Virtual Environment Setup.

After a long hiatus finally found something exciting to crib about. Just completed India's first Exadata Oracle Virtual Environment (OVM) setup.

It's a departure from the traditional Exadata Setup where you directly utilized the entire capacity of the two (or more) compute nodes and created RAC databases on it.

With this new setup here is a list of new possibilities and the basic architecture.

More stuff coming up shortly........

1. something called as DOM 0 or domain zero virtual machines comes up on BOTH the compute nodes of Exadata when you first configure the RACK.

2. Hence forth you will always login to the IP assigned to DOM 0 on node 1 and node 2 to carry out all future setups.

3. DOM 0 is the mother or controller of all user defined virtual machines that will come up on the compute nodes.

4. It is using the XEN Hypervisor virtualization engine.

5. Currently if you want to configure virtual machines. OEDA i.e. ORACLE EXADATA DEPLOYMENT ASSISTANT is the only tool. You configure your entire setup i.e. assigning IP address defining cluster names, diskgroup names and size etc in this tool. FTP the generated files on
/EXAVMIMAGES/linux-x86/ folder on dom 0 of compute node 1 and run ./install.sh -cf setup.xml -s 3

6. You fire up the step 3 of install.sh for any further vm creation.

7. Multiple IP segments can be created . i.e you can have one VM running ip segment 172.16. and other vm running 172.18.....

8. All VMs in a cluster should be under the same IP segment.

9. VLAN tagging is required at customer data center switch level to allow multiple IP segment.

10. Gateway of IP segment to be created must be pingable from the defined IP addresses. If your customer uses firewall at data center switch, ensure that he allows ping to gateway IP from the configured IP addresses.

BASIC ARCHITECTURE

Wednesday, 24 February 2016

How to force configuration changes in Weblogic

At a recent client deployment. I faced the issue that the datasource properties were not getting configured from the Weblogic Admin Console.

Symptoms -
Activate changes button click hangs in-definately.
JDBC_datasource.xml file on node 2 of weblogic cluster is zero bytes.
Weblogic Version 10.3.6

The downtime was limited and there was no scope of troubleshooting the console.

This is what you do in the above scenario and force the changes from the background instead of the console.

Solution -

1. Shut down the admin server. Make sure you don't have any applications running in the admin server.

2. Backup or tar the config directory in the domain_home

3. Go inside the DOMAIN_HOME/pending folder. There you can see the config.xml and other changed configuration files like JDBC and JMS config files.
These are temporary config files which are cached with changes made from the console. Once you do activate changes, config.xml and other config files from here replaces the original config.xml and other configuration files inside the DOMAIN_HOME/config folder.

4. Move (do not copy) config.xml and all other config files from DOMAIN_HOME/pending folder to DOMAIN_HOME/config folder and replace the ones which are there.

5. Delete edit.lok inside DOMAIN_HOME and config.lok inside DOMAIN_HOME/config folder and restart Admin Server.

6. Log into the console and verify that the changes have taken effect.

This issue usually occurs with the admin console running in https mode rather than http mode.

Friday, 20 November 2015

Goldengate Replication - Sybase to Sybase Database - Step By Step

This post covers Oracle Goldengate data replication between two sybase databases.

Installation is not covered in this post as it is a simple process of just extracting the
.tar file.

#### Setting Environment Variables on Sybase Server ####

go to gg install home

cd /GGS/home

$ vi .ggsprof and add following two entries

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/GGS/home

export PATH=$PATH:/GGS/home

$ chmod 775 .ggsprof

################################################

#### Preparing Sybase Database for GG Capture ####

isql -U dbadmin -P frodo -- logging into isql

>sp_helpdb
>Go

-- will list all databases

>use rknet -- switching to database we want to use
>Go

-- setting secondary truncation point in sybase logs

1> dbcc settrunc ('ltm','valid')
2> Go
ltm_truncpage ltm_trunc_state db_rep_stat gen_id dbid
dbname lti_version
------------- --------------- ----------- ----------- ------
------------------------------ -----------
2746034 1 1 0 9
rknet

#### Logging into db from ggsci prompt ####

$ cd /GGS/home
$ ggsci

GGSCI (kondorapp) 1> dblogin sourcedb rknet, USERID dbadmin
Password:

2015-11-20 12:41:30 INFO OGG-04508 Oracle GoldenGate is searching the Sybas e CS library version that is to be used. Please ignore any error that is reporte d by Sybase as a part of this search process.

The context allocation routine failed.

The following problem caused the failure:

Invalid context version.

The context allocation routine failed.

The following problem caused the failure:

Invalid context version.

2015-11-20 12:41:30 INFO OGG-04509 Oracle GoldenGate is using the Sybase CS library version: [15001].

2015-11-20 12:41:30 INFO OGG-04501 Charset information: Client charset: [is o_1], Database/Server charset: [iso_1], CHARSETCONVERT: [OFF].
Successfully logged into database. --- check this and ignore all errors above

GGSCI (kondorapp as dbadmin@KONDOR) 2>

#############################################

#### Adding Trandata on Table ####

NOTE - UNLIKE ORACLE, ADDING TRANDATA IN SYBASE CAN CAUSE DEADLOCKS SO EXECUTE IT DURING LEAN HOURS.

GGSCI (kondorapp as dbadmin@KONDOR) 2> add trandata dbo.ScheduleLog

Transaction logging enabled for table dbo.ScheduleLog.

GGSCI (kondorapp as dbadmin@KONDOR) 3> info trandata dbo.ScheduleLog

Transaction logging enabled for table dbo.ScheduleLog.

#################################################

#### Configuring Extract ####

ggsci > edit params ext1
EXTRACT ext1
SOURCEDB rknet, userid dbadmin, password frodo
EXTTRAIL ./dirdat/ET
TABLE dbo.ScheduleLog;

ggsci> add extract ext1, tranlog, begin now

ggsci> add exttrail ./dirdat/ET, extract ext1, megabytes 1024

ggsci> start ext1

##################################################

###### SYBASE DATABASE OPERATIONS #######

$ isql -U dbadmin -P frodo

>use rknet -- switching to database we want to use
>Go

--taking backup of schedulelog table

1> select * into ScheduleLog_backup from ScheduleLog
2> Go
(2887526 rows affected)
1> commit
2> Go

--creating empty table from schedulelog

1> select * into ScheduleLogRep from ScheduleLog where 1=2
2> Go
(0 rows affected)
1> select * from ScheduleLogRep
2> Go
DealLogId SettlementLeg FlowDate
FlowAmount FlowCustom ZeroIfNetting
SettlCoeff CLS
-------------------------- ------------- --------------------------
--------------------------- --------------------------- -------------
--------------------------- ------

(0 rows affected)
##################################################

###### CONFIGURING REPLICAT ON SAME SERVER SAME DB #######

NOTE - REPLICATE WILL READ TRAIL FILE PRODUCED BY EXTRACT, SINCE BOTH EXT AND REP ARE ON SAME
SERVER, THERE IS NO USE OF A DATA PUMP EXTRACT.

REPLICATION IS FROM DBO.SCHEDULELOG TO DBO.SCHEDULELOGREP IN THE SAME DATABASE RKNET

ggsci> edit params rep1

REPLICAT REP1
TARGETDB rknet, userid dbadmin, password frodo
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep1.dsc, purge
MAP dbo.ScheduleLog, TARGET dbo.ScheduleLogRep;

ggsci> add replicat rep1, exttrail ./dirdat/ET

ggsci> start rep 1

##################################################

#### Populating Data and Checking Status of Replication ####

SELF INSERT INTO THE SOURCE TABLE IS CARRIED OUT.

1> insert into ScheduleLog select * from ScheduleLog
2> Go
(2887526 rows affected)
1> commit
2> Go
1> select count(1) from dbo.ScheduleLogRep
2> Go

-----------
2887526

#### Checking Status of Replicat Process ####

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2015-11-20 14:32:07.

Replicating from dbo.ScheduleLog to dbo.ScheduleLogRep:

*** Total statistics since 2015-11-20 14:17:08 ***
Total inserts 2887526.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2887526.00

##################################################

Note -
There are lot of limitations w.r.t Sybase database. e.g. encrypted data types, lobs & DDL Replication. Do check out the install guide for the same here
https://docs.oracle.com/cd/E18101_01/doc.1111/e17806.pdf

Thursday, 5 November 2015

Analyzing DB issues without AWR or ASH reports - The Magic Query

It's a typical DBA scenario....you land up at your desk and are bombarded by users complaining that the system experienced a hiccup of 2 minutes or so in the morning when no one was in office. Transactions were dropped etc....

So what do you do as a DBA....fire up the AWR and ASH reports and try to link the data to the problem cause. Sometimes you just wish you were there to observe what the database was experiencing, in real-time, than doing a post apocalypse analysis.

Well here is a query that will take you back in time and show you events as if you were viewing the system in real time. The result is that you are able to address problems much quicker and find the guilty sql ids in a snap.

Lets say the user gives you a perfect time when the system went bonkers....

Step 1 - Find the SNAP ID's corresponding to this time frame.....

SELECT SNAP_ID,BEGIN_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 2;

Step 2  - Find the event, sql_ids, sessions IDs as they were during the issue time...

col event for a40

set lines 1000 pages 1000

SELECT SESSION_ID,SQL_ID,EVENT,SAMPLE_TIME FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE SNAP_ID BETWEEN 31565 AND 31566 ORDER
BY SAMPLE_TIME

HERE IS A SAMPLE OUTPUT FROM A REAL WORLD SCENARIO......

 35               log file sync                            03-NOV-15 11.05.10.019 AM
        39               log file sync                            03-NOV-15 11.05.10.019 AM
       152               log file sync                            03-NOV-15 11.05.10.019 AM
       236               log file sync                            03-NOV-15 11.05.10.019 AM
       238               log file sync                            03-NOV-15 11.05.10.019 AM
       265               log file sync                            03-NOV-15 11.05.10.019 AM
       379               log file sync                            03-NOV-15 11.05.10.019 AM
       383               log file sync                            03-NOV-15 11.05.10.019 AM
       384               log file sync                            03-NOV-15 11.05.10.019 AM
       528               log file sync                            03-NOV-15 11.05.10.019 AM
       534               log file sync                            03-NOV-15 11.05.10.019 AM
       556               log file sync                            03-NOV-15 11.05.10.019 AM
         5 a232czr2w2ryy direct path read                         03-NOV-15 11.05.20.039 AM
       842 a232czr2w2ryy direct path read                         03-NOV-15 11.05.20.039 AM
       527               log file sync                            03-NOV-15 11.05.20.039 AM
       474               log file sync                            03-NOV-15 11.05.20.039 AM
       470               log file sync                            03-NOV-15 11.05.20.039 AM
       417               log file sync                            03-NOV-15 11.05.20.039 AM
       414               log file sync                            03-NOV-15 11.05.20.039 AM
       382               log file sync                            03-NOV-15 11.05.20.039 AM
       326               log file sync                            03-NOV-15 11.05.20.039 AM
       297               log file sync                            03-NOV-15 11.05.20.039 AM
       272               log file sync                            03-NOV-15 11.05.20.039 AM
       265               log file sync                            03-NOV-15 11.05.20.039 AM
       238               log file sync                            03-NOV-15 11.05.20.039 AM
       209               log file sync                            03-NOV-15 11.05.20.039 AM
       121               log file sync                            03-NOV-15 11.05.20.039 AM
       558                                                        03-NOV-15 11.05.20.039 AM
       320               db file async I/O submit                 03-NOV-15 11.05.20.039 AM
       291               db file async I/O submit                 03-NOV-15 11.05.20.039 AM
       707               log file sync                            03-NOV-15 11.05.20.039 AM
       701               log file sync                            03-NOV-15 11.05.20.039 AM
       532               log file sync                            03-NOV-15 11.05.20.039 AM
       670 4ztz048yfq32s direct path read                         03-NOV-15 11.05.20.039 AM
         6               log file sync                            03-NOV-15 11.05.20.039 AM

SO NOW YOU KNOW WHAT WAS HAPPENING EXACTLY AT 11.05 IN YOUR DATABASE WITHOUT LOOKING AT ANY AWR OR ASH REPORTS.....

NOTE -- YOU CAN ALSO INCLUDE P1,P2,P3 VALUES FROM DBA_HIST_ACTIVE_SESS_HISTORY TO FURTHER DRILL DOWN INTO THE EVENTS....

Now Enjoy your AWR and ASH free troubleshooting journey........

Friday, 23 October 2015

Goldengate - Problems converting bit datatypes from sybase, MS Sql and My-Sql database to Oracle Number data type.

Recently encountered an issue where the client was taking data from MS-SQL Server to Oracle 11g Database. Both source and target environments were running the latest Goldengate Version 12.

Columns with Bit Data Type are not converted and sent to Oracle database. i.e. the corresponding column in Oracle db table is blank.

Goldengate Processes (Extracts and Replicats) don't give any kind of errors and their status is shown as Running.

Symptoms -

When Bit data type is used in sqlserver source, and the replicat site maps the BIT to char or number, the bit value will not show in target site.

Cause -

Bit values used to be treated as ASCII prior to 11.1. Now they are treated as binary, so a pre-11.1 trail with a bit value of 0 applied to a target with an 11.1 or later replicat will treat the ASCII value of 0x30 as 1 since it is not 0x00

NOTE - Although Oracle support lists the problem with Goldengate Ver 11. The issue was duplicated in Goldengate Ver 12 also.

Solution -

Apply @NUMTOBIN while converting and writing data on the target column.

e.g

MAP scott.emp, target ora.emp,
COLMAP (usedefaults, source_bit_column_name = @NUMBIN (target_bit_column_name);

Friday, 9 October 2015

LMHB Process Trace Files in Oracle RAC - Potential Database Terminator.

During a routine performance analysis of Exadata system I came along trace files by the names on dbname_lmhb_number.trc i.e. testdb_lmhb_129875.trc being generated in the alert log location with the following lines contained in them

LMDO (ospid: 95477) has not moved for 74 sec (123453423.14444321115)
kjfmGCR_HBCheckAll: LMD0 (ospid: 95477) has status 2
: waiting for event 'ges remote message' for 0 secs with wait_id 123.

There are repeated messages like the above with only the wait_id changing in each.

We can see that the highlighted call above is something related to heartbeat check in RAC.

What Exactly is a LMHB Process

Global Cache/Enqueue Service Heartbeat Monitor
Monitor the heartbeat of LMON, LMD, and LMSn processes
LMHB monitors LMON, LMD, and LMSn processes to ensure they are running normally without blocking or spinning.

Cause of these waits - Identified as BUG 13718279 - Affected Versions - 11.2.0.3

Effects of This Bug - DB instance terminated due to ORA-29770 in RAC.
.
Solution

Apply patch 13718279 or set the hidden init parameter _gc_defer_time=3.
Fixed in Version 11.2.0.4

Reference - MOS DOC ID - 1440112.1

Goldengate Replicats abending with Warnings related to Trigger and no clear error

Situation
GG replicats at client site abended with following errors in the report file

015-10-08 16:03:40 WARNING OGG-02904 Replication of PARTIAL XML containing NCHAR/NVARCHAR/NCLOB data may cause divergence.

2015-10-09 11:18:34  WARNING OGG-00869  OCI Error ORA-04098: trigger 'UATPROD.TRG_VALIDATE' is invalid and failed re-validation (status = 4098), SQL <INSERT INTO "UATPROD.TESTTAB"

2015-10-09 11:18:34  WARNING OGG-01004  Aborted grouped transaction on ' UATPROD.TESTTAB ', Database error 4098 (OCI Error ORA-04098:

2015-10-09 11:18:34  WARNING OGG-01003  Repositioning to rba 28437291 in seqno 3.2015-10-09 11:18:34

Analysis

Although there is no clear OGG error, the warnings themselves reveal the issue. Particularly the warning -- OGG-00869 OCI Error ORA-04098: trigger

In this case the entire schema was replicated at the target db and as a result, during impdp, its triggers also got imported.

When we have trigger firing at source and modifying or checking any data, then we can be rest assured that the data processed and committed as part of this trigger action will find its way into the Goldengate Trail.

Therefore there is no need to have these triggers replicated at the Target Site

The only exception to this case is if you are planning to do switchovers between source and target and have them acting as a data source at different points in time.

Solution

The simple solution was to drop the triggers present in the schema on the Target database and just start the Replicat processes again.