Thinking Out Loud

March 20, 2023

Use The Right Tool

Filed under: Uncategorized — mdinh @ 12:38 pm

I think I may have blogged about how most read from left to right and down unless you are reading Chinese.

Look at how nice that is versus using text file.

Back to the grind.

March 19, 2023

Datapump Import Performance Improvement for IOT with 1.9B rows

Filed under: 19c,DataPump,performance — mdinh @ 3:25 pm

The root cause of the issue is source (DW) and target (OLTP) have different partition design for index organized table (IOT).

Source: PARTITION BY HASH and Target: PARTITION BY VALUES

IOT has approximately 1.9B rows.

Version 19.12.2.0.0

### References:

In What Order Are Indexes Built During Datapump Import (IMPDP) and How to Optimize the Index Creation (Doc ID 1966442.1)	

Normally index creation in a schema level import will follow this order:
1. Metadata import (user, roles & system privileges)
2. Objects like type, sequences and related grants
3. Tables, table data, table grants
4. Indexes

### This is the parameter file for export.
exclude=STATISTICS
compression=ALL
# Is it necessary to export staging?
schemas=staging,s2,s3,s4,s5
flashback_scn=61727639035
# There are 8 CPUs and possible to use up to 1.5xCPU and monitor.
# FUD in play.
parallel=8
content=DATA_ONLY
# Added for improvements to avoid 1 process doing all the work.
# Tested using 1G/2G and no huge improvements.
filesize=4G
logfile=expdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

### This is the parameter file for import.
table_exists_action=TRUNCATE
# Added for performance improvements since there is no standby database.
transform=DISABLE_ARCHIVE_LOGGING:Y
logtime=ALL
metrics=Y
# There are 8 CPUs and possible to use up to 1.5xCPU and monitor.
# FUD in play.
parallel=8
cluster=N
schemas=staging,s2,s3,s4,s5
# STATISTICS was already exclude from import.
# Exclude is not necessary, since will import everything.
exclude=STATISTICS,REF_CONSTRAINT,GRANT,INDEX,TRIGGER
content=DATA_ONLY
logfile=impdp.log
dumpfile=schema%U.dmp
directory=dpump_dir

### Here is the export dmp.
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_09 is:
  /export/schema01.dmp
  /export/schema02.dmp
  /export/schema03.dmp
  /export/schema04.dmp
  /export/schema05.dmp
  /export/schema06.dmp
  /export/schema07.dmp
  /export/schema08.dmp
  /export/schema09.dmp
  /export/schema10.dmp
  /export/schema11.dmp
  /export/schema12.dmp
  /export/schema13.dmp
  /export/schema14.dmp
  /export/schema15.dmp
  /export/schema16.dmp
  /export/schema17.dmp
  /export/schema18.dmp
  /export/schema19.dmp

### Here is the import for IOT.
16-MAR-23 20:25:48.971: W-4 . . imported "H01" 3.946 GB 474235896 rows in 12183 seconds using external_table
16-MAR-23 14:42:56.470: W-7 . . imported "H02" 3.952 GB 475007010 rows in 7113  seconds using external_table
16-MAR-23 23:45:07.344: W-8 . . imported "H03" 3.945 GB 474120834 rows in 11956 seconds using external_table
16-MAR-23 17:02:44.346: W-6 . . imported "H04" 3.949 GB 474655428 rows in 8386  seconds using external_table

16-MAR-23 23:58:02.815: Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed at Thu Mar 16 23:58:02 2023 elapsed 0 11:18:20

The result for import was 50% faster for IOT with ~1.90B rows.

There are probably more options to test and tune; however, would be too time consuming.

Writing this blog post, I realized I have made the mistake of not requesting export/import parameter files and logs as there are redundancies for export and import parameters.

March 3, 2023

Some Quirks With oggca_response File Errors

Filed under: OCI — mdinh @ 2:39 am

I have just completed 6 deployments and thought I would things I was not are of.

First issue is relared to password requirements.

Password M1llionD0llar$ will not work.

Who doesn’t like million dollar?

[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85080] Invalid password.
   CAUSE: The provided password is invalid for the current strong password policy settings. The strong password policy is enabled. 
   Password should contain at least: 
   one lowercase [a..z] character, 
   one uppercase [A..Z] character, 
   one digit [0..9], and 
   one special character [- ! @ % & * . # _]. 
   Length should be between 8..30 characters.
   ACTION: Specify a valid password.
[oracle@dinh ~]$

EZCONNECT will be used for the deployment; there is no need to install client or database.

[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85077] Empty value for TNS_ADMIN environment variable.
ACTION: Specify a value for the TNS_ADMIN environment variable.
[oracle@dinh ~]$

The work around is just to create an empty diretory. In my case, just create it under $OGG_HOME

ENV_TNS_ADMIN=/u01/oracle/app/goldengate/ogg21ma/network/admin

SERVICEMANAGER_DEPLOYMENT_HOME

[oracle@dinh ~]$ /u01/oracle/app/goldengate/ogg21ma/bin/oggca.sh -silent -responseFile /tmp/dinh_PROD1-PROD2_oggca_response.rsp
[FATAL] [INS-85103] The path /u01/oracle/app/ogg_deploy/ServiceManager/PROD1-PROD2 cannot be used as a deployment directory.
   CAUSE: Deployment directories cannot be located inside the Service Manager deployment directory.
   ACTION: Provide a valid path for the deployment directory that is not located inside the Service Manager deployment home.
[oracle@dinh ~]$

Move it

SERVICEMANAGER_DEPLOYMENT_HOME=/u01/oracle/app/ogg_deploy/ServiceManager
OGG_DEPLOYMENT_HOME=/u01/oracle/app/ogg_deploy/PROD1-PROD2

I know that you are thinking -RTFM. I would still be reading vs delivering.

Just like learning how to ride a bike. You fall and get back on.

It’s also good to have someone there when youu fall.

March 2, 2023

Oracle GoldenGate Microservices Architecture Silent Deployment Creation – Better Port Selection

Filed under: OCI — mdinh @ 1:50 am

I was working on 6 deployments – 3 primary and 3 standy.

Was heading to home base but then tripped and fell – had port collision.

How fastt can you spot the error?

### DEPLOYMENT #1
PORT_ADMINSRVR=21001
PORT_DISTSRVR=21002
PORT_RCVRSRVR=21003
PORT_PMSRVR=21004
UDP_PORT_PMSRVR=21005

### DEPLOYMENT #2
PORT_ADMINSRVR=21021
PORT_DISTSRVR=21012
PORT_RCVRSRVR=21013
PORT_PMSRVR=21014
UDP_PORT_PMSRVR=21015

### DEPLOYMENT #3
PORT_ADMINSRVR=21021
PORT_DISTSRVR=21022
PORT_RCVRSRVR=21023
PORT_PMSRVR=21024
UDP_PORT_PMSRVR=21025

Remember, we read from left to right.

Now, how fast can you spot the error?

### DEPLOYMENT #1
PORT_ADMINSRVR=10001
PORT_DISTSRVR=10002
PORT_RCVRSRVR=10003
PORT_PMSRVR=10004
UDP_PORT_PMSRVR=10005

### DEPLOYMENT #2
PORT_ADMINSRVR=20001
PORT_DISTSRVR=20002
PORT_RCVRSRVR=20003
PORT_PMSRVR=20004
UDP_PORT_PMSRVR=20005

### DEPLOYMENT #3
PORT_ADMINSRVR=20001
PORT_DISTSRVR=30002
PORT_RCVRSRVR=30003
PORT_PMSRVR=30004
UDP_PORT_PMSRVR=30005

Some good news, with 21c version, it was an easy fix.

Click on bad port to edit and save.

February 25, 2023

OGG Microservices Silent Installation and Deployment – FUBAR?

Filed under: OCI — mdinh @ 4:09 pm

There are 2 steps to Implement Oracle GoldenGate Microservices Architecture.

Step1 – Install Oracle GoldenGate – oggcore.rsp is provided

Step2 – Create the Oracle GoldenGate Deployment – oggca.rsp is not provided

To get oggca.rsp, one will need to perform installation using GUI and save the response file as oggca.rsp.

Here is an example for differences for response file header – ogginstall vs oggca – don’t get this wrong.

Install Oracle GoldenGate
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0

Create the Oracle GoldenGate Deployment
oracle.install.responseFileVersion=/oracle/install/rspfmt_oggca_response_schema_v21_1_0

Next, PMSRVR_DATASTORE_TYPE : LMDB or BDB?

System Properties Comparison LMDB vs. Oracle Berkeley DB vs. SQLite

Retrieving and understanding a response file for Oracle GoldenGate Deployment configuration

How To Install Linux locate

Filed under: linux — mdinh @ 2:36 am

The command locate is one of my favorite and it’s depressing if not installed.

Still remembered when asked by my manager from a decade ago, “What’s the difference between effective and efficient?”

To me locate is an efficient method vs find for Linux.

Here’s how to install and use.

[root@oracle-goldengate-1910-vagrant ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# yum install mlocate -y
[root@oracle-goldengate-1910-vagrant ~]# updatedb

[root@oracle-goldengate-1910-vagrant ~]# locate ggsci
/u01/ogg/ggsci
/u01/oggbd/ggsci
[root@oracle-goldengate-1910-vagrant ~]#

February 19, 2023

Why Do You Read My Blog?

Filed under: Uncategorized — mdinh @ 4:32 pm

English is my second language (I always use this as an excuse but then I was one told “It doesn’t matter if you are right or wrong as long as you have an excuse – LOL”)

Reading and writing are my weakness.

Tell me?

LinkedIn Resume Builder

Filed under: Uncategorized — mdinh @ 4:20 pm

Imagine being laid off after working for 10ys at a company.

I don’t remembered what I had for dinner last night and how am I supposed to remember what I did in the last 10 years to update my resume?

LinkedIn Resume Builder is a great feature provided you used LinkedIn to keep track of all your major accomplishments.

This is mine Michael T. Dinh LinkedIn as an example and as you can see, I started slacking on the updates.

No mention of database upgrade using AutoUpgrade for RAC and Data Guard among other things.

Why does this matter?

I was let go on Thursday. Friday was still digesting. Saturday received communication from a company. We like to entertain the prospect of you joining our company. Send me your resume.

What resume! I am still in shock from the lay off.

Guess what? Resume sent. Wish me luck.

February 18, 2023

Missing Dependencies Python Core / win32api in VirtualBox 7.0

Filed under: Vagrant,VirtualBox,Windows — mdinh @ 9:01 pm

After download and install VirtualBox-7.0.6-155176-Win, I encounter error – “Missing Dependencies Python Core / win32api” in VirtualBox 7.0

Instead of following the video to correct the error, I removed all current installation and install the latest version to correct the issues.

Step 1 – Install 2.39.2 64-bit version of Git for Windows.

https://git-scm.com/download/win

From Additional icons, select On the desktop

Step 2 – Install Vagrant and restart (vagrant_2.3.4_windows_amd64.msi)

https://releases.hashicorp.com/vagrant/

https://releases.hashicorp.com/vagrant/2.3.4/

Step 3 – Install VirtualBox-7.0.6-155176-Win

https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html

OS info:

C:\Windows\System32>systeminfo | findstr /B /C:"OS Name" /B /C:"OS Version"
OS Name:                   Microsoft Windows 11 Pro
OS Version:                10.0.22621 N/A Build 22621

C:\Windows\System32>

Hope this helps you.

UPDATE:

Vagrant VirtualBox was hanging and downloaded VirtualBox-6.1.42-155177-Win completed but still took a long time.

January 28, 2023

Create Future Blackout Using emcli

Filed under: emcli — mdinh @ 3:43 am

There is scheduled maintenance starting at 6am CST and ending 6pm CST.

That’s 3am in my timezone.

Option 1 – create blackout in advance and sleep like a baby.

Option 2 – create alarm to wake up to create blackout.

I opted fir Option 1.

Use link below for all the details.

https://docs.google.com/document/d/1UQLNj6FjtV-NbMIdW0MBUg5btvOwzweIFC3Wd978wJ0

Next Page »

Blog at WordPress.com.