OGG-01224 TCP/IP error 111 (Connection refused), endpoint: :Port.

Well, this article is related to the previous one - click here where pump extract at source was getting ABEND and it is again a confusing error in Oracle GoldenGate 12c Release 2. In this case, for this reported error, Oracle Support Note(Doc ID 2139900.1) explains that the pump extract was getting abend due to " No ports available for the target manager to start a dynamic server collector", this MOS note applies to this kind of error but in my case it was fine since Manager process was already able to start collector processes dynamically on dynamic ports as you would notice in the log below.

2017-10-30 21:06:11  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #4).
2017-10-30 21:06:11  INFO    OGG-01677  Oracle GoldenGate Collector for Oracle:  Waiting for connection (started dynamically).
2017-10-30 21:06:11  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from SERVER on host [127.0.0.1]:60108 (REPORT 4202 7819).
2017-10-30 21:06:11  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #1).
2017-10-30 21:06:11  INFO    OGG-00974  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started collector process (Port 7819).

After further evaluation of OGG error log at target server, I could see the following ERROR (error: 2, No such file or directory)

2017-10-30 21:08:45  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #4).
2017-10-30 21:08:46  ERROR   OGG-01878  Oracle GoldenGate Collector for Oracle:  Failed resolving ALLOWOUTPUTDIR /u01/ogg_12c/rmttrail/em (error: 2, No such file or directory).
2017-10-30 21:08:46  ERROR   OGG-01668  Oracle GoldenGate Collector for Oracle:  PROCESS ABENDING.




Yes, directory /u01/ogg_12c/rmttrail/em was not existing on the target server so I corrected rmttrail parameter value in pump extract at source.

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail/em      ----- Wrong entry here
PASSTHRU
TABLE scott.emp;

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail   ---- Corrected the entry here
PASSTHRU
TABLE scott.emp;

After correcting the value for remote trail location, pump started up normally.

GGSCI (prodsrv) 18> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:00    
EXTRACT     RUNNING     PEMP        00:00:00      00:00:06    







OGG-01031 There is a problem in network communication

During starting up a new Pump Extract in Oracle GoldenGate 12c Release 2, I came through the following error that ABEND the pump extract at source database server.


ERROR   OGG-01031  There is a problem in network communication, a remote file problem, encryption keys f
or target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /u01/ogg_12c/rmttrai
l/em000000 is not in any allowed output directories.).

GGSCI (prodsrv) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:01    
EXTRACT     ABENDED     PEMP        00:00:00      00:24:10    










Cause : In Oracle GoldenGate 12c Release 2, by default, Pump extract try to push the EXTRACT TRAIL logs from source Database server to target database server to the default directory(i.e. ./dirdat) but in our case we are using the custom directory(/u01/ogg_12c/rmttrail) to store the remote trail files on the target server.


From OGG Error log at target server.

2017-10-30 21:06:16  INFO    OGG-01226  Oracle GoldenGate Collector for Oracle:  Socket buffer size set to 187200 (flush size 27985).
2017-10-30 21:06:16  WARNING OGG-01223  Oracle GoldenGate Collector for Oracle:  Output file /u01/ogg_12c/rmttrail/em000000 is not in any allowed output directories.
2017-10-30 21:06:16  INFO    OGG-01971  Oracle GoldenGate Collector for Oracle:  The previous message, 'WARNING OGG-01223', repeated 1 times.
2017-10-30 21:06:16  INFO    OGG-01676  Oracle GoldenGate Collector for Oracle:  Terminating after client disconnect.

Pump Extract Parameter file at source:

GGSCI (prodsrv) 15> view param pemp

EXTRACT PEMP
RMTHOST drsrv, mgrport 7809, timeout 30
RMTTRAIL /u01/ogg_12c/rmttrail/em
PASSTHRU
TABLE scott.emp;







Solutions: Go to the target Oracle GoldenGate server and add the following parameter(ALLOWOUTPUTDIR )in the ./GLOBALS parameter file to solve this problem.

GGSCI (DRsrv) 8> view param ./GLOBALS

GGSCHEMA ogg
ALLOWOUTPUTDIR /u01/ogg_12c/rmttrail


GGSCI (DRsrv) 9> 




Subsequently start the pump extract at source and it would move forward.

GGSCI (prodsrv) 16> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXEMP       00:00:00      00:00:04    
EXTRACT     RUNNING     PEMP        00:00:00      00:00:03    







Happy Learning....!!

ORION - ORacle I/O Numbers - I/O Bench-marking Tool in Oracle

ORION - ORacle I/O Numbers :  An I/O Bench-marking Tool in Oracle


Generally, you do the workload testing of your production database system or the new hardware arrived in your data center when your Oracle RDBMS server already running with actual application data but you can simulate the similar I/O testing with Oracle provided Tool called "Orion", an I/O bench-marking Tool. It helps you predict the performance of I/O Bench-marking for new hardware system for Oracle database.

The ORION tool has been available for some time now and honestly I was not aware of it so far. While exploring the advanced performance tuning of Oracle RDBMS, I came to know about this nice pre-bundled script to evaluate the I/O performance of your Oracle database on new hardware where you are going to deploy or migrate your database.

Initially this tool was available to download from Oracle OTN but since Oracle 11.2 onward, it is bundled inside Orale RDBMS database software package itself. So you can invoke from $ORACLE_HOME/bin directory to work with it.

ORION works best during the evaluation phase of new platform without application-data on your logical unit numbers(LUNs). It is specifically designed to simulate the I/O workload on your new hardware system for Oracle database. This package uses asynchronous I/O and large pages for read and writes to concurrently submit I/O requests to operating system. Asynchronous I/O uses "libaio" on Linux platform. You can quite easily see that the ORION slave processes use "io_submit" and "io_getevents" by tracing strace on this.


Here, let's go ahead and fake some LUNs to perform evaluation of I/O on it.

-bash-4.1$ dd if=/dev/zero of=lun01.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 24.0865 s, 44.6 MB/s


-bash-4.1$ dd if=/dev/zero of=lun02.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 12.9167 s, 83.1 MB/s


-bash-4.1$ dd if=/dev/zero of=lun03.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 1.00802 s, 1.1 GB/s


-bash-4.1$ dd if=/dev/zero of=lun04.file bs=1G count=1
1+0 records in
1+0 records out
1073741824 bytes (1.1 GB) copied, 1.01502 s, 1.1 GB/s

You can see LUNs are faked inside /u01 mount by listing the directory.

bash-4.1$ pwd
/u01
-bash-4.1$ ls -ltr *.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:53 lun01.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:53 lun02.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:54 lun03.file
-rw-r--r--. 1 oracle oinstall 1073741824 Oct 17 06:54 lun04.file




Once the LUNs are created, make a group file with list of LUNs we created earlier with its obsolute file path. I created a group file /u01/orion.lun as below.

-bash-4.1$ cat /u01/orion.lun 
/u01/lun01.file
/u01/lun02.file
/u01/lun03.file
/u01/lun04.file

Finally, invoke orion executable from $ORACLE_HOME/bin location as below. As soon as you start the command to evaluate the I/O testing, this tool will tell you the approximate time it will take to complete.

-bash-4.1$ $ORACLE_HOME/bin/orion -run oltp -testname /u01/orion -hugenotneeded

Output should be like below.

Below I have generated a graph based on above out to relate Latency and and corresponding # No. of I/Os(Read) per second. You can realise that lower the latency the higher #No. of I/O(Reads)

As part of the I/O performance evaluation, orion tool creates multiple CSV files, text files with detailed metrics you should look at for evaluating IOPS, MBPS operations.

-rw-r--r--. 1 oracle oinstall   355 Oct 17 07:00 orion_20171017_0700_summary.txt
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_mbps.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_lat.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_iops.csv
-rw-r--r--. 1 oracle oinstall     0 Oct 17 07:00 orion_20171017_0700_hist.txt
-rw-r--r--. 1 oracle oinstall   515 Oct 17 07:00 orion_20171017_0700_trace.txt
-rw-r--r--. 1 oracle oinstall 56161 Oct 17 07:22 orion_20171017_0702_trace.txt
-rw-r--r--. 1 oracle oinstall   570 Oct 17 07:22 orion_20171017_0702_mbps.csv
-rw-r--r--. 1 oracle oinstall   811 Oct 17 07:22 orion_20171017_0702_lat.csv
-rw-r--r--. 1 oracle oinstall   742 Oct 17 07:22 orion_20171017_0702_iops.csv
-rw-r--r--. 1 oracle oinstall 19494 Oct 17 07:22 orion_20171017_0702_hist.txt
-rw-r--r--. 1 oracle oinstall  2008 Oct 17 07:22 orion_20171017_0702_summary.txt










Below is the contents I extracted from orion_20171017_0700_iops.csv

file and it has more details for Small I/O and Large I/O statistics in there.

 

This comma-separated-value file contains the rates sustained by small I/Os in IOPS.

Each value corresponds to a data point test that used a fixed number of outstanding small and large I/Os.

The number of outstanding small I/Os for a value is specified by its column header in the first row.

The number of outstanding large I/Os for a value is specified by its row header in the first column.




Small I/O 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 Large I/O 587 667 595 428 361 338 340 340 336 341 342 351 339 335 346 350 351 353 352 351





Similarly, you can review all required .csv and other files to study the evaluation in details to get an idea how your oracle database will perform on new hardware.