Feed aggregator

Systemd Database GoldenGate Service Start Stop

Michael Dinh - Sat, 2022-05-21 13:13

A complicated solution for a simple process.

If you can’t beat them, join them.

==================================================
### Restart vagrant box:
==================================================

resetlogs@ghost MINGW64 /e/oracle/vagrant-projects/OracleGoldenGate/19.3.0 (main)
$ vagrant halt

resetlogs@ghost MINGW64 /e/oracle/vagrant-projects/OracleGoldenGate/19.3.0 (main)
$ vagrant up

resetlogs@ghost MINGW64 /e/oracle/vagrant-projects/OracleGoldenGate/19.3.0 (main)
$ vagrant ssh

==================================================
### System info and last reboot:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# cat /etc/system-release
Oracle Linux Server release 7.9
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# last reboot|head -1
reboot   system boot  5.4.17-2136.307. Sat May 21 11:01 - 11:02  (00:00)
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Verify goldengate.service started on reboot:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# systemctl status goldengate.service
● goldengate.service - Oracle GoldenGate Manager
   Loaded: loaded (/etc/systemd/system/goldengate.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2022-05-21 11:02:14 -04; 1min 9s ago
  Process: 4485 ExecStart=/home/oracle/scripts/start_goldengate.sh (code=exited, status=0/SUCCESS)
 Main PID: 4504 (mgr)
   CGroup: /system.slice/goldengate.service
           ├─4504 ./mgr PARAMFILE /u01/ogg/dirprm/mgr.prm REPORTFILE /u01/ogg/dirrpt/MGR.rpt PROCESSID MGR
           └─4522 /u01/ogg/extract PARAMFILE /u01/ogg/dirprm/ext.prm REPORTFILE /u01/ogg/dirrpt/EXT.rpt PROCESSID EXT

May 21 11:02:06 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Manager started.
May 21 11:02:06 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 3> START ER *
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Sending START request to MANAGER ...
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: EXTRACT EXT starting
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 11:02:13 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: MANAGER     RUNNING
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: EXTRACT     RUNNING     EXT         00:01:25      00:00:00
May 21 11:02:14 oracle-goldengate-1910-vagrant systemd[1]: Started Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Check logs for goldengate.service:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# journalctl -u goldengate.service
-- Logs begin at Sat 2022-05-21 11:01:41 -04, end at Sat 2022-05-21 11:03:24 -04. --
May 21 11:02:04 oracle-goldengate-1910-vagrant systemd[1]: Starting Oracle GoldenGate Manager...
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: The Oracle base has been set to /opt/oracle
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Oracle GoldenGate Command Interpreter for Oracle
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Operating system character set identified as UTF-8.
May 21 11:02:04 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
May 21 11:02:05 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 1>
May 21 11:02:05 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 2> START MGR
May 21 11:02:06 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Manager started.
May 21 11:02:06 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 3> START ER *
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Sending START request to MANAGER ...
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: EXTRACT EXT starting
May 21 11:02:07 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 11:02:13 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: MANAGER     RUNNING
May 21 11:02:14 oracle-goldengate-1910-vagrant start_goldengate.sh[4485]: EXTRACT     RUNNING     EXT         00:01:25      00:00:00
May 21 11:02:14 oracle-goldengate-1910-vagrant systemd[1]: Started Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### There is no log from journalctl before reboot:
==================================================

[root@oracle-goldengate-1910-vagrant ~]#  last reboot|head -1
reboot   system boot  5.4.17-2136.307. Sat May 21 11:01 - 11:32  (00:30)
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### NOTICE: Logs begin at Sat 2022-05-21 11:01:41
==================================================

[root@oracle-goldengate-1910-vagrant ~]# journalctl --no-pager|head -2
-- Logs begin at Sat 2022-05-21 11:01:41 -04, end at Sat 2022-05-21 11:30:01 -04. --
May 21 11:01:41 oracle-goldengate-1910-vagrant systemd-journal[151]: Runtime journal is using 8.0M (max allowed 96.8M, trying to leave 145.3M free of 960.7M available → current limit 96.8M).
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### stop_goldengate.sh attempts to gather info:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# ls -alrt /u01/ogg/ggse*
-rw-r-----. 1 oracle oinstall  7835 May 21 11:00 /u01/ogg/ggserr.log.20220521.1100
-rw-r--r--. 1 oracle oinstall   955 May 21 11:00 /u01/ogg/ggserr.log.stop
-rw-r-----. 1 oracle oinstall 10384 May 21 11:02 /u01/ogg/ggserr.log
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# cat /u01/ogg/ggserr.log.stop
2022-05-21T11:00:47.915-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): STOP MGR.
2022-05-21T11:00:47.930-0400  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host [127.0.0.1]:58056 (STOP).
2022-05-21T11:00:47.930-0400  WARNING OGG-00938  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager is stopping at user request.
2022-05-21T11:00:51.934-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): STOP ER *.
2022-05-21T11:00:52.175-0400  INFO    OGG-01021  Oracle GoldenGate Capture for Oracle, ext.prm:  Command received from GGSCI: STOP.
2022-05-21T11:00:52.179-0400  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, ext.prm:  EXTRACT EXT stopped normally.
2022-05-21T11:00:59.213-0400  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): INFO ALL.
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Continue with demo:
==================================================

==================================================
### Stop goldengate.service using systemctl stop:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# systemctl stop goldengate.service
[root@oracle-goldengate-1910-vagrant ~]# systemctl status goldengate.service
● goldengate.service - Oracle GoldenGate Manager
   Loaded: loaded (/etc/systemd/system/goldengate.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Sat 2022-05-21 11:16:37 -04; 13s ago
  Process: 5432 ExecStop=/home/oracle/scripts/stop_goldengate.sh (code=exited, status=0/SUCCESS)
  Process: 4485 ExecStart=/home/oracle/scripts/start_goldengate.sh (code=exited, status=0/SUCCESS)
 Main PID: 4504 (code=exited, status=0/SUCCESS)

May 21 11:16:28 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Manager stopped.
May 21 11:16:28 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 3> STOP ER *
May 21 11:16:29 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Sending STOP request to EXTRACT EXT ...
May 21 11:16:30 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Request processed.
May 21 11:16:30 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 11:16:36 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: MANAGER     STOPPED
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: EXTRACT     STOPPED     EXT         00:00:00      00:00:07
May 21 11:16:37 oracle-goldengate-1910-vagrant systemd[1]: Stopped Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Check journalctl -u goldengate.service:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# journalctl -u goldengate.service|grep -i stop
May 21 11:16:24 oracle-goldengate-1910-vagrant systemd[1]: Stopping Oracle GoldenGate Manager...
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: The Oracle base has been set to /opt/oracle
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: ‘/u01/ogg/ggserr.log’ -> ‘/u01/ogg/ggserr.log.20220521.1116’
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Oracle GoldenGate Command Interpreter for Oracle
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Operating system character set identified as UTF-8.
May 21 11:16:24 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
May 21 11:16:25 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 1>
May 21 11:16:25 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 2> STOP MGR !
May 21 11:16:26 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Sending STOP request to MANAGER ...
May 21 11:16:26 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Request processed.
May 21 11:16:28 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Manager stopped.
May 21 11:16:28 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 3> STOP ER *
May 21 11:16:29 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Sending STOP request to EXTRACT EXT ...
May 21 11:16:30 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Request processed.
May 21 11:16:30 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 11:16:36 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: MANAGER     STOPPED
May 21 11:16:37 oracle-goldengate-1910-vagrant stop_goldengate.sh[5432]: EXTRACT     STOPPED     EXT         00:00:00      00:00:07
May 21 11:16:37 oracle-goldengate-1910-vagrant systemd[1]: Stopped Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### List ggserr logs:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# ls -alrt /u01/ogg/ggse*
-rw-r-----. 1 oracle oinstall  7835 May 21 11:00 /u01/ogg/ggserr.log.20220521.1100
-rw-r-----. 1 oracle oinstall 10384 May 21 11:16 /u01/ogg/ggserr.log.20220521.1116
-rw-r-----. 1 oracle oinstall   955 May 21 11:16 /u01/ogg/ggserr.log
-rw-r--r--. 1 oracle oinstall   955 May 21 11:16 /u01/ogg/ggserr.log.stop
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Services configured for rdbms and goldengate:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# systemctl list-unit-files|egrep -i 'oracle|gate'
goldengate.service                            enabled
oracle-rdbms.service                          enabled
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Configuration for goldengate.service:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# ls -l /etc/systemd/system/goldengate.service
-rw-r--r--. 1 root root 713 May 19 23:16 /etc/systemd/system/goldengate.service
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# cat /etc/systemd/system/goldengate.service
[Unit]
Description=Oracle GoldenGate Manager
After=syslog.target network.target
# systemctl list-unit-files|grep -i oracle
After=oracle-rdbms.service

[Service]
Type=forking
RemainAfterExit=yes
KillMode=none
User=oracle
Group=oinstall
TimeoutStopSec=60
TimeoutSec=300
Restart=no

# systemd ignores PAM limits, so set any necessary limits in the service.
# Not really a bug, but a feature.
# https://bugzilla.redhat.com/show_bug.cgi?id=754285
# /etc/security/limits.d/oracle-database-preinstall-19c.conf

LimitMEMLOCK=infinity
LimitNOFILE=131072
LimitNPROC=131072

ExecStart=/home/oracle/scripts/start_goldengate.sh
ExecStop=/home/oracle/scripts/stop_goldengate.sh
Restart=no

[Install]
WantedBy=multi-user.target
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### GoldenGate processes:
==================================================

oracle@oracle-goldengate-1910-vagrant ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (oracle-goldengate-1910-vagrant) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:00:00


GGSCI (oracle-goldengate-1910-vagrant) 2> info mgr detail

Manager is running (IP port TCP:oracle-goldengate-1910-vagrant.7809, Process ID 5539).


GGSCI (oracle-goldengate-1910-vagrant) 3> info extract *

EXTRACT    EXT       Last Started 2022-05-20 23:31   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           5546
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2022-05-20 23:35:08
                     SCN 0.3722791 (3722791)


GGSCI (oracle-goldengate-1910-vagrant) 4> exit
[oracle@oracle-goldengate-1910-vagrant ogg]$

==================================================
### start_goldengate.sh & start_goldengate.obey:
==================================================

[oracle@oracle-goldengate-1910-vagrant ~]$ cat /home/oracle/scripts/start_goldengate.sh
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

ORAENV_ASK=NO; ORACLE_SID=ORCLCDB; OGG_HOME=/u01/ogg
. /usr/local/bin/oraenv

$OGG_HOME/ggsci << EOF
  OBEY /home/oracle/scripts/start_goldengate.obey
  exit
EOF
exit
[oracle@oracle-goldengate-1910-vagrant ~]$

[oracle@oracle-goldengate-1910-vagrant ~]$ cat /home/oracle/scripts/start_goldengate.obey
START MGR
START ER *
shell sleep 5
INFO ALL
[oracle@oracle-goldengate-1910-vagrant ~]$

==================================================
### stop_goldengate.sh & stop_goldengate.obey:
==================================================

[oracle@oracle-goldengate-1910-vagrant ~]$ cat /home/oracle/scripts/stop_goldengate.sh
#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin

ORAENV_ASK=NO; ORACLE_SID=ORCLCDB; OGG_HOME=/u01/ogg
. /usr/local/bin/oraenv

# Rotate existing GGSERR_LOG with append date
GGSERR_LOG=$OGG_HOME/ggserr.log
cp -fv ${GGSERR_LOG%%.*}.{${GGSERR_LOG#*.},${GGSERR_LOG#*.}."$(date +%Y%m%d.%H%M)"}
cat /dev/null > $GGSERR_LOG

$OGG_HOME/ggsci << EOF
  OBEY /home/oracle/scripts/stop_goldengate.obey
  exit
EOF
tail -9 $GGSERR_LOG > $GGSERR_LOG.stop
exit
[oracle@oracle-goldengate-1910-vagrant ~]$

[oracle@oracle-goldengate-1910-vagrant ~]$ cat /home/oracle/scripts/stop_goldengate.obey
STOP MGR !
STOP ER *
shell sleep 5
INFO ALL
[oracle@oracle-goldengate-1910-vagrant ~]$

==================================================
### GoldenGate credential store:
==================================================

GGSCI (oracle-goldengate-1910-vagrant) 1> INFO CREDENTIALSTORE

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: c##ggadmin
  Userid: c##ggadmin@ORCLCDB

GGSCI (oracle-goldengate-1910-vagrant) 2>

==================================================
### GoldenGate mgr.prm:
==================================================

[oracle@oracle-goldengate-1910-vagrant ogg]$ cat dirprm/mgr.prm
PORT 7809
USERIDALIAS c##ggadmin
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
AUTOSTART ER *
AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 60
CHECKMINUTES 5
LAGINFOMINUTES 0
LAGCRITICALMINUTES 5
[oracle@oracle-goldengate-1910-vagrant ogg]$

==================================================
### GoldenGate ext.prm:
==================================================

[oracle@oracle-goldengate-1910-vagrant ogg]$ cat dirprm/ext.prm
EXTRACT ext
SETENV (ORACLE_SID = "ORCLCDB")
SETENV (ORACLE_HOME = "/opt/oracle/product/19c/dbhome_1")
USERIDALIAS c##ggadmin
EXTTRAIL ./dirdat/et
GETUPDATEBEFORES
UPDATERECORDFORMAT COMPACT

SOURCECATALOG ORCLPDB1
TABLE ogguser.*;
SEQUENCE ogguser.*;
[oracle@oracle-goldengate-1910-vagrant ogg]$

==================================================
### tnsnames.ora requires (UR=A):
==================================================

ORCLPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLPDB1)(UR=A)
    )
  )
  
==================================================
!!! IMPORTANT !!!
==================================================

systemctl is NOT aware when goldengate start/stopp from ggsci!

GGSCI (oracle-goldengate-1910-vagrant) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:00:09


GGSCI (oracle-goldengate-1910-vagrant) 2> stop mgr !

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.


GGSCI (oracle-goldengate-1910-vagrant) 3> stop er *

Sending STOP request to EXTRACT EXT ...
Request processed.


GGSCI (oracle-goldengate-1910-vagrant) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT         00:00:00      00:00:04


GGSCI (oracle-goldengate-1910-vagrant) 5>

==============================

[root@oracle-goldengate-1910-vagrant ~]# systemctl status goldengate.service
● goldengate.service - Oracle GoldenGate Manager
   Loaded: loaded (/etc/systemd/system/goldengate.service; enabled; vendor preset: disabled)
   Active: active (exited) since Sat 2022-05-21 12:00:46 -04; 1min 40s ago
  Process: 5432 ExecStop=/home/oracle/scripts/stop_goldengate.sh (code=exited, status=0/SUCCESS)
  Process: 7921 ExecStart=/home/oracle/scripts/start_goldengate.sh (code=exited, status=0/SUCCESS)
 Main PID: 4504 (code=exited, status=0/SUCCESS)

May 21 12:00:37 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 2> START MGR
May 21 12:00:38 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: MGR is already running.
May 21 12:00:38 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 3> START ER *
May 21 12:00:39 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: EXTRACT EXT is already running.
May 21 12:00:39 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 12:00:45 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: MANAGER     RUNNING
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: EXTRACT     RUNNING     EXT         00:00:00      00:00:00
May 21 12:00:46 oracle-goldengate-1910-vagrant systemd[1]: Started Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#

==============================

GGSCI (oracle-goldengate-1910-vagrant) 1> sh date

Sat May 21 12:07:40 -04 2022


GGSCI (oracle-goldengate-1910-vagrant) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EXT         00:00:00      00:05:45


GGSCI (oracle-goldengate-1910-vagrant) 3> start mgr
Manager started.


GGSCI (oracle-goldengate-1910-vagrant) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT         00:00:00      00:00:03


GGSCI (oracle-goldengate-1910-vagrant) 5>

==============================

[root@oracle-goldengate-1910-vagrant ~]# systemctl status goldengate.service
● goldengate.service - Oracle GoldenGate Manager
   Loaded: loaded (/etc/systemd/system/goldengate.service; enabled; vendor preset: disabled)
   Active: active (exited) since Sat 2022-05-21 12:00:46 -04; 7min ago
  Process: 5432 ExecStop=/home/oracle/scripts/stop_goldengate.sh (code=exited, status=0/SUCCESS)
  Process: 7921 ExecStart=/home/oracle/scripts/start_goldengate.sh (code=exited, status=0/SUCCESS)
 Main PID: 4504 (code=exited, status=0/SUCCESS)

May 21 12:00:37 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 2> START MGR
May 21 12:00:38 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: MGR is already running.
May 21 12:00:38 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 3> START ER *
May 21 12:00:39 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: EXTRACT EXT is already running.
May 21 12:00:39 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 4> shell sleep 5
May 21 12:00:45 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: GGSCI (oracle-goldengate-1910-vagrant) 5> INFO ALL
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: Program     Status      Group       Lag at Chkpt  Time Since Chkpt
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: MANAGER     RUNNING
May 21 12:00:46 oracle-goldengate-1910-vagrant start_goldengate.sh[7921]: EXTRACT     RUNNING     EXT         00:00:00      00:00:00
May 21 12:00:46 oracle-goldengate-1910-vagrant systemd[1]: Started Oracle GoldenGate Manager.
[root@oracle-goldengate-1910-vagrant ~]#
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-62892c6c4d466', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });

Removing a MySQL Heatwave Cluster

DBASolved - Sat, 2022-05-21 11:36

Recently I’ve been playing with Oracle’s MySQL Database Service (MDS) and testing out the Heatwave option.  Heatwave is very impressive […]

The post Removing a MySQL Heatwave Cluster appeared first on DBASolved.

Categories: DBA Blogs

音楽専門学校で自分の進路を極める

Marian Crkon - Sat, 2022-05-21 00:15
将来、音楽家を夢見て様々なアクションを起こす方は多いのですが、あらゆる知識を独学で習得するという事は困難な道で...

音楽専門学校で自分の進路を極める

The Feature - Sat, 2022-05-21 00:15

将来、音楽家を夢見て様々なアクションを起こす方は多いのですが、あらゆる知識を独学で習得するという事は困難な道でもあります。そういった悩みを抱えている場合は、音楽専門学校に通うという事が有効です。音楽専門学校においては、音楽に関する多くの学科が用意されており、進みたい道に応じて学科を選定することができるのです。一言で音楽業界と言っても様々なお仕事が存在しています。

ミュージシャンとして活躍する方が居る一方で、録音に携わるエンジニアやマネージメントに関わる業務など多岐にわたります。自分が進みたい道を見極めて、事前にしっかりと考慮した上で学ぶという意識を持つ事が重要になります。そして、音楽専門学校に進学すると現役でミュージシャンとして活動している方やエンジニアとして音楽制作に携わっている方の授業を受けることができます。現役で現場で働いている方のお話というのは、将来音楽業界に進みたいと考えている学生にとってはとても刺激的なものです。

自分の知識を増幅させることもでき、夢を実現させるために大きなステップアップを行うことができます。さらに、音楽専門学校ではアルバイトをしながら現場に通うという事が可能です。学生という身分でありながらも、本場の現場に早い段階から身を投じることにより、机上では学ぶことができない知識を得ることができるのです。当然、学校なので自分と同一の志を持っている方が多く仲間を作るといった事もできます。

Categories: APPS Blogs

Quiz Night

Jonathan Lewis - Fri, 2022-05-20 12:09

It’s a long time since I’ve done a quiz night – but here’s something that fooled me (briefly) when it appeared on the Oracle Developers’ Forum. Here’s a table definition – and I’m not going to make it easy by giving you a “create table” statement, but it’s just a simple heap table:

sql> desc interr_skuplannparam
 name                             null?    type
 -------------------------------- -------- -------------------
 atpdur                                    number(38)
 depdmdopt                                 number(38)
 externalskusw                             number(1)
 firstreplendate                           date
 lastfrzstart                              date
 lastplanstart                             date
 plandur                                   number(38)
 planleadtime                              number(38)
 planleadtimerule                          number(38)
 planshipfrzdur                            number(38)
 restrictdur                               number(38)
 allocbatchsw                              number(1)
 cmpfirmdur                                number(38)
 custservicelevel                          float(126)
 maxchangefactor                           float(126)
 mfgleadtime                               number(38)
 recschedrcptsdur                          number(38)
 cpppriority                               number(38)
 cpplocksw                                 number(1)
 criticalmaterialsw                        number(1)
 aggexcesssupplyrule                       number(38)
 aggundersupplyrule                        number(38)
 bufferleadtime                            number(38)
 maxoh                                     float(126)
 maxcovdur                                 number(38)
 drpcovdur                                 number(38)
 drpfrzdur                                 number(38)
 drprule                                   number(38)
 drptimefencedate                          date
 drptimefencedur                           number(38)
 incdrpqty                                 float(126)
 mindrpqty                                 float(126)
 mpscovdur                                 number(38)
 mfgfrzdur                                 number(38)
 mpsrule                                   number(38)
 mpstimefencedate                          date
 mpstimefencedur                           number(38)
 incmpsqty                                 float(126)
 minmpsqty                                 float(126)
 shrinkagefactor                           number(38)
 item                                      varchar2(50 char)
 loc                                       varchar2(50 char)
 expdate                                   date
 atprule                                   number(38)
 prodcal                                   varchar2(50 char)
 prodstartdate                             date
 prodstopdate                              date
 orderingcost                              float(126)
 holdingcost                               float(126)
 eoq                                       float(126)
 ff_trigger_control                        number(38)
 workingcal                                varchar2(50 char)
 lookaheaddur                              number
 orderpointrule                            number
 orderskudetailsw                          number(1)
 supsdmindmdcovdur                         number(38)
 orderpointminrule                         number(38)
 orderpointminqty                          float(126)
 orderpointmindur                          number(38)
 orderuptolevelmaxrule                     number(38)
 orderuptolevelmaxqty                      float(126)
 orderuptolevelmaxdur                      number(38)
 aggskurule                                number(38)
 fwdbuymaxdur                              number(38)
 costuom                                   number(38)
 cumleadtimedur                            number(38)
 cumleadtimeadjdur                         number(38)
 cumleadtimerule                           number(38)
 roundingfactor                            float(126)
 limitplanarrivpublishsw                   number(1)
 limitplanarrivpublishdur                  number
 maxohrule                                 number(1)
 integration_stamp                         date
 integration_jobid                not null varchar2(32 char)
 error_str                                 varchar2(2000 char)
 error_stamp                               date

The column integration_jobid (the single “not null” column) has been defined with the default value of “INT_JOB”, which takes 7 bytes to store. What’s the result of the query at the end of this little script:

truncate table interr_skuplannparam;

insert into interr_skuplannparam (atpdur) 
select   0 
from     all_objects 
where    rownum <= 10000
/

commit;

execute dbms_stats.gather_table_stats(user,'interr_skuplannparam')

select avg_row_len from user_tables;

Hint: the value zero is represented internally as a single byte holding the value 0x80 (decimal 128).

Helvetia used AWS SCT & DMS to migrate to AWS RDS for PostgreSQL

Yann Neuhaus - Thu, 2022-05-19 04:32

One of our long term-time customers, Helvetia, successfully migrated on-prem Oracle databases to AWS, not only because of the licenses, but more importantly: to deploy faster, innovate faster, and use the state-of-the-art open source database system.


When you plan such a project, you need to know which tools you want to use and what the target architecture shall look like. There are several options to choose from but finally Helvetia decided to use the AWS native services AWS DMS and AWS RDS for PostgreSQL.

AWS DMS gives you the option to initially populate the target instance from the source, and right afterwards logically replicates ongoing changes from the source to the target. However, before you can do that, you need the schema to be ready in the target. To prepare this, there is AWS SCT. This is not an AWS service, but a free tool you can use to convert a schema from a database system to another. If you want to go from Oracle to PostgreSQL, this tool also performs an automatic conversion from Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL. Although this tool does a great job, you have to be very careful with the result, and invest a good amount of time in testing. Autonomous transactions, for example, do not exist in PostgreSQL and the AWS schema conversion utility implements a workaround using database links. This can be fine if you rarely use it (because it needs to establish a new connection) but if you rely heavily on this feature, you’d better re-implement in a way that is native to PostgreSQL.

Another area you need to pay attention to are the data types. PostgreSQL comes with many of them. A NUMBER in Oracle can mean many things in PostgreSQL. It could be an integer or a numeric in PostgreSQL. Depending on what you go for, this comes with space and performance impacts in PostgreSQL. PostgreSQL comes with a boolean data type. In Oracle, this is usually implemented as a character or a numeric value. Do you want to keep it that way or do you want to convert to a boolean? Converting means that you also need to adjust the business logic in the database.

Another issue that took quite some to solve was this. The very simplified test case attached to the initial Email showed massive performance drops in PostgreSQL compared to Oracle. The reason is that Oracle’s PL/SQL is a compiled language and PostgreSQL’s PL/pgSQL is interpreted. If you have a case that more or less matches what is described in the thread linked above, you need to re-write this. The same applies when you have commits or rollbacks in PL/SQL functions. PostgreSQL does not allow you to commit or rollback in a function. You need to use procedures for that.

These are just a few hints of what might come along the way when migrating to AWS RDS for PostgreSQL. Once you have solved all this, the migration can be really smooth and will most probably be a success. Here are some posts that describe how to set this up using an Oracle sample schema as the source:

If you follow that, you should have enough knowledge to get started with your journey to AWS RDS.

Cet article Helvetia used AWS SCT & DMS to migrate to AWS RDS for PostgreSQL est apparu en premier sur Blog dbi services.

Announcement: Dates Confirmed For Upcoming Webinars (“Here Today, Gone Tomorrow”)

Richard Foote - Thu, 2022-05-19 01:53
As promised last week, I have now finalised the dates for my upcoming webinars. They will be run as follows: “Oracle Indexing Internals“ Webinar: 18-22 July 2022 (between 09:00 GMT and 13:00 GMT daily) “Oracle Performance Diagnostics and Tuning“ Webinar: 8-11 August 2022 (between 09:00 GMT and 13:00 GMT daily) I’ll detail costings and how […]
Categories: DBA Blogs

音楽専門学校はサービスの内容を調査する

Marian Crkon - Wed, 2022-05-18 00:14
深い専門知識を得る事ができる専門学校の中には、音楽に関係している所もあります。音楽専門学校に通学することで、通...

音楽専門学校はサービスの内容を調査する

The Feature - Wed, 2022-05-18 00:14

深い専門知識を得る事ができる専門学校の中には、音楽に関係している所もあります。音楽専門学校に通学することで、通常の学習では学ぶ事が無い点も学ぶ事ができるようになります。そのため、音楽について興味がある方は迷わず音楽専門学校に進学を決めるのです。ニーズの高まりを受けて学校数は増加しており、行われる授業にも差異が現れるようになってきています。

こうした差異によって、これまで以上に自分の学習意欲や目的とマッチしている音楽専門学校を選ぶという事が重要になりました。つまり、進学する上では各々で異なるサービスについて知識を深める事が大切です。まず、大きな差異点として挙げられるのが講義の進め方についてです。一般的に専門学校で学ぶ場合は、大人数が収容できるスペースで勉強を進めていきますが、大人数の場合一人一人に講師がサポートすることができず、追いつくことができない生徒が出てきてしまうのです。

そこで、たくさんの人数で学ぶ事に苦手意識を抱いている方は少人数で授業が進められる音楽専門学校を探して学ぶと良いでしょう。少人数制であれば、周囲の人々に合わせるという必要が無いので焦ることなく落ち着いて自分のペースで学習を進めることができます。そして、音楽専門学校と言えば何よりも費用が心配になりますが、学費に関してしっかりとサポートしてくれる体制が整備されているので、金銭的に余裕が無いという場合でも諦める必要はありません。

Categories: APPS Blogs

Maximum number of Autonomous Databases on an ExaCC Full Rack

Tom Kyte - Tue, 2022-05-17 12:26
How many autonomous db we could have for ExaCC full Rac?
Categories: DBA Blogs

Number of cores before requring RAC licencing for BYOL

Tom Kyte - Tue, 2022-05-17 12:26
The maximum number of cores before needing RAC licensing is per Cluster or for the sum of the Clusters?
Categories: DBA Blogs

Automatic Indexes: Automatically Rebuild Unusable Indexes Part III (“Waiting For The Man”)

Richard Foote - Tue, 2022-05-17 01:43
I’ve previously discussed how Automatic Indexing (AI) will not only create missing indexes, but will also rebuild unusable indexes, be it a Global or Local index. However, all my previous examples have been with Automatic Indexes. How does AI handle unusable indexes in which the indexes were manually created? In my first demo, I’ll start […]
Categories: DBA Blogs

Connecting to MySQL Database Service (MDS) via DBeaver

DBASolved - Mon, 2022-05-16 10:37

With every new service on any cloud platform, the need to make connections is essential .This is the case with […]

The post Connecting to MySQL Database Service (MDS) via DBeaver appeared first on DBASolved.

Categories: DBA Blogs

A quick way of generating Informatica PowerCenter Mappings from a template

Rittman Mead Consulting - Mon, 2022-05-16 04:52
Generating Informatica PowerCenter Content - the Options

In our blogs we have discussed the options for Oracle Data Integrator (ODI) content generation here and here. Our go-to method is to use the ODI Java SDK, which allows querying, manipulating and generating new ODI content.

Can we do the same with Informatica PowerCenter? In the older PC versions there was the Design API that enabled browsing the repository and creating new content. However, I have never used it. My impression is that Oracle APIs are more accessible than Informatica APIs in terms of documentation, help available online and availability for download and tryout.
If we want to browse the PowerCenter repository content, there is an easy way - query the repository database. But what about content generation? Who will be brave or foolish enough to insert records directly into a repository database!? Fortunately, there is a way, and a fairly easy one, if you don't mind doing a bit of Python scripting.

Generate PowerCenter Mappings - an Overview

Selective Informatica PC repository migrations are done via XML export and import - it is easy and mostly fool-proof. If we can generate XMLs for import, then we have found a way of auto-generating PowerCenter content. Informatica seems to support this approach by giving us nice, descriptive error messages if something is wrong with import XMLs. Only completely valid XMLs will import successfully. I have never managed to corrupt my Informatica repository with a dodgy XML import.

Let us look at an example - we need to extract a large number of OLTP tables to a Staging schema. The source and staging tables have very similar structures, except the staging tables have MD5 codes based on all non-key source fields to simplify change data capture (CDC) and also have the extract datetime.

  1. We start by creating a single mapping in Designer, test it, make sure we are 100% happy with it before proceeding further;
  2. We export the mapping in XML format and in the XML file we replace anything unique to the source and target table and their fields with placeholder tags: [[EXAMPLE_TAG]]. (See the XML template example further down.)
  3. Before we generate XMLs for all needed mappings, we need to import Source and Target table definitions from the databases. (We could, if we wanted, generate Source and Target XMLs ourselves but PC Designer allows us to import tables in bulk, which is quicker and easer than generating the XMLs.)
  4. We export all Sources into a single XML file, e.g. sources.xml. Same with all the Targets - they go into targets.xml. (You can select multiple objects and export in a single XML in Repository Manager.) The Source XML file will serve as a driver for our Mapping generation - all Source tables in the sources.xml file will have a Mapping generated for them.
  5. We run a script that iterates through all source tables in the source XML, looks up its target in the targets XML and generates a mapping XML. (See the Python script example further down.) Note that both the Source and Target XML become part of the Mapping XML.
  6. We import the mapping XMLs. If we import manually via the Designer, we still save time in comparison to implementing the mappings in Designer one by one. But we can script the imports, thus getting both the generation and import done in minutes, by creating an XML Control File as described here.
Scripting Informatica PowerCenter Mapping generation

A further improvement to the above would be reusable Session generation. We can generate Sessions in the very same manner as we generate Mappings.

The Implementation

An example XML template for a simple Source-to-Staging mapping that includes Source, Source Qualifier, Expression and Target:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE POWERMART SYSTEM "powrmart.dtd">
<POWERMART CREATION_DATE="05/26/2021 11:55:12" REPOSITORY_VERSION="188.97">
<REPOSITORY NAME="DemoETL" VERSION="188" CODEPAGE="UTF-8" DATABASETYPE="Oracle">
<FOLDER NAME="Extract" GROUP="" OWNER="Developer" SHARED="NOTSHARED" DESCRIPTION="" PERMISSIONS="rwx---r--" UUID="55321111-2222-4929-9fdc-bd0dfw245cd3">

    [[SOURCE]]
	
    [[TARGET]]
	
    <MAPPING DESCRIPTION ="[[MAPPING_DESCRIPTION]]" ISVALID ="YES" NAME ="[[MAPPING_NAME]]" OBJECTVERSION ="1" VERSIONNUMBER ="2">
	
        <TRANSFORMATION DESCRIPTION ="" NAME ="SQ_EXTRACT" OBJECTVERSION ="1" REUSABLE ="NO" TYPE ="Source Qualifier" VERSIONNUMBER ="1">
            [[SQ_TRANSFORMFIELDS]]
            <TABLEATTRIBUTE NAME ="Sql Query" VALUE =""/>
            <TABLEATTRIBUTE NAME ="User Defined Join" VALUE =""/>
            <TABLEATTRIBUTE NAME ="Source Filter" VALUE =""/>
            <TABLEATTRIBUTE NAME ="Number Of Sorted Ports" VALUE ="0"/>
            <TABLEATTRIBUTE NAME ="Tracing Level" VALUE ="Normal"/>
            <TABLEATTRIBUTE NAME ="Select Distinct" VALUE ="NO"/>
            <TABLEATTRIBUTE NAME ="Is Partitionable" VALUE ="NO"/>
            <TABLEATTRIBUTE NAME ="Pre SQL" VALUE =""/>
            <TABLEATTRIBUTE NAME ="Post SQL" VALUE =""/>
            <TABLEATTRIBUTE NAME ="Output is deterministic" VALUE ="NO"/>
            <TABLEATTRIBUTE NAME ="Output is repeatable" VALUE ="Never"/>
        </TRANSFORMATION>
		
        <TRANSFORMATION DESCRIPTION ="" NAME ="EXPTRANS" OBJECTVERSION ="1" REUSABLE ="NO" TYPE ="Expression" VERSIONNUMBER ="2">
            [[EXP_TRANSFORMFIELDS]]
            <TRANSFORMFIELD DATATYPE ="nstring" DEFAULTVALUE ="ERROR(&apos;transformation error&apos;)" DESCRIPTION ="" EXPRESSION ="[[MD5_EXPRESSION]]" EXPRESSIONTYPE ="GENERAL" NAME ="CDC_MD5" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="32" SCALE ="0"/>
            <TRANSFORMFIELD DATATYPE ="date/time" DEFAULTVALUE ="ERROR(&apos;transformation error&apos;)" DESCRIPTION ="" EXPRESSION ="SYSTIMESTAMP()" EXPRESSIONTYPE ="GENERAL" NAME ="EXTRACT_DATE" PICTURETEXT ="" PORTTYPE ="OUTPUT" PRECISION ="29" SCALE ="9"/>
            <TABLEATTRIBUTE NAME ="Tracing Level" VALUE ="Normal"/>
        </TRANSFORMATION>

        [[SOURCE_INSTANCE]]
		
        <INSTANCE DESCRIPTION ="" NAME ="SQ_EXTRACT" REUSABLE ="NO" TRANSFORMATION_NAME ="SQ_EXTRACT" TRANSFORMATION_TYPE ="Source Qualifier" TYPE ="TRANSFORMATION">
            <ASSOCIATED_SOURCE_INSTANCE NAME ="[[SOURCE_INSTANCE_NAME]]"/>
        </INSTANCE>
		
        <INSTANCE DESCRIPTION ="" NAME ="EXPTRANS" REUSABLE ="NO" TRANSFORMATION_NAME ="EXPTRANS" TRANSFORMATION_TYPE ="Expression" TYPE ="TRANSFORMATION"/>
		
        [[TARGET_INSTANCE]]

        [[SRC_2_SQ_CONNECTORS]]

        [[SQ_2_EXP_CONNECTORS]]

        [[EXP_2_TGT_CONNECTORS]]

        <CONNECTOR FROMFIELD ="CDC_MD5" FROMINSTANCE ="EXPTRANS" FROMINSTANCETYPE ="Expression" TOFIELD ="CDC_MD5" TOINSTANCE ="[[TARGET_INSTANCE_NAME]]" TOINSTANCETYPE ="Target Definition"/>
        <CONNECTOR FROMFIELD ="EXTRACT_DATE" FROMINSTANCE ="EXPTRANS" FROMINSTANCETYPE ="Expression" TOFIELD ="EXTRACT_DATE" TOINSTANCE ="[[TARGET_INSTANCE_NAME]]" TOINSTANCETYPE ="Target Definition"/>

        <TARGETLOADORDER ORDER ="1" TARGETINSTANCE ="[[TARGET_INSTANCE_NAME]]"/>

        <ERPINFO/>
        <METADATAEXTENSION COMPONENTVERSION ="1000000" DATATYPE ="STRING" DESCRIPTION ="" DOMAINNAME ="User Defined Metadata Domain" ISCLIENTEDITABLE ="YES" ISCLIENTVISIBLE ="YES" ISREUSABLE ="YES" ISSHAREREAD ="NO" ISSHAREWRITE ="NO" MAXLENGTH ="256" NAME ="Extension" VALUE ="" VENDORNAME ="INFORMATICA"/>
    </MAPPING>
</FOLDER>
</REPOSITORY>
</POWERMART>

Python script snippets for generating Mapping XMLs based on the above template:

  1. To translate database types to Informatica data types:
mapDataTypeDict = {
	"nvarchar": "nstring",
	"date": "date/time",
	"timestamp": "date/time",
	"number": "decimal",
	"bit": "nstring"
}

2. Set up a dictionary of tags:

xmlReplacer = {
	"[[SOURCE]]": "",
	"[[TARGET]]": "",
	"[[MAPPING_DESCRIPTION]]": "",
	"[[MAPPING_NAME]]": "",
	"[[SQ_TRANSFORMFIELDS]]": "",
	"[[EXP_TRANSFORMFIELDS]]": "",
	"[[MD5_EXPRESSION]]": "",
	"[[SOURCE_INSTANCE]]": "",
	"[[SOURCE_INSTANCE_NAME]]": "",
	"[[TARGET_INSTANCE]]": "",
	"[[TARGET_INSTANCE_NAME]]": "",
	"[[SRC_2_SQ_CONNECTORS]]": "",
	"[[SQ_2_EXP_CONNECTORS]]": "",
	"[[EXP_2_TGT_CONNECTORS]]": ""
}

3. We use the Source tables we extracted in a single XML file as our driver for Mapping creation:

sourceXmlFilePath = '.\\sources.xml'

# go down the XML tree to individual Sources
sourceTree = ET.parse(sourceXmlFilePath)
sourcePowerMart = sourceTree.getroot()
sourceRepository = list(sourcePowerMart)[0]
sourceFolder = list(sourceRepository)[0]

for xmlSource in sourceFolder:
	# generate a Mapping for each Source
    
    # We also need to go down the Field level:    
    for sourceField in xmlSource:
    	# field level operations

4. Generate tag values. This particular example is of a Column-level tag, a column connector between Source Qualifier and Expression:

sqToExpConnectorTag = f'<CONNECTOR FROMFIELD ="{columnName}" FROMINSTANCE ="SQ_EXTRACT" FROMINSTANCETYPE ="Source Qualifier" TOFIELD ="{columnName}" TOINSTANCE ="EXPTRANS" TOINSTANCETYPE ="Expression"/>'

5. We assign our tag values to the tag dictionary entries:

xmlReplacer["[[SQ_2_EXP_CONNECTORS]]"] = '\n'.join(sqToExpConnectors)

6. We replace the tags in the XML Template with the values from the dictionary:

for replaceTag in xmlReplacer.keys():
	mappingXml = mappingXml.replace(replaceTag, xmlReplacer[replaceTag])

Interested in finding out more about our approach to generating Informatica content, contact us.

Categories: BI & Warehousing

Data Annotation with SVG and JavaScript

Andrejus Baranovski - Mon, 2022-05-16 01:35
I explain how to build a simple data annotation tool with SVG and JavaScript in HTML page. The sample code renders two boxes in SVG on top of the receipt image. You will learn how to select and switch between annotation boxes. Enjoy!

 

Maximum number of concurrent sessions in multi instance database

Tom Kyte - Sun, 2022-05-15 23:46
Hi, We have Oracle 12C on 2 instances. I know GV$license can give maximum number of concurrent sessions since start of instances. But is there a way to get maximum we had accessing the database from both together ? Syed
Categories: DBA Blogs

Index on XMLTYPE with XPATH Expression including a XPATH Function

Tom Kyte - Sun, 2022-05-15 23:46
Is there a way to create a index for a xpath that is including a xpath function? Please consider that xmltype index creation fails at oracle livesql.
Categories: DBA Blogs

Cannot Upload git-upload-pack error while cloning Azure Git Repository

Tom Kyte - Sun, 2022-05-15 23:46
Hi, <i>Background and Requirement</i> - I am working for a firm that uses <b>Oracle SQL Developer</b> for Data Cleaning and Manipulation of the data residing in the Oracle Database. We use <b>Microsoft Azure</b> for complete lifecycle management and work planning. So, we decided to use an <b>Azure-hosted cloud Git Repository</b> to host our code remotely and leverage its version control capabilities. We have a Git repository on Azure and are trying to clone the same in Oracle SQL Developer. <i>Steps followed to fulfill the requirement</i> - The following steps were followed for cloning the existing remote repository in Oracle SQL Developer. 1. Go to Teams Menu. 2. Hover over Git. 3. Select Clone option. 4. After the Clone from Git wizard opens up, entered the correct Repository URL, Username and password. 5. We work on a VPN so, I have set the corresponding proxy settings too. When testing the proxy, it gives a success message. (So, no issue in the proxy settings) 6. Click next to fetch remote repository branches. An error appears at this stage. <i>Error that occured</i> - A popup with the title <b>Validation failed</b> and the content as https://<remote repo url>/_git/<remote repo name>:cannot open git-upload-pack appears. <i>Troubleshooting Methods Tried</i> - The following troubleshooting methods have been tried. 1. A lot of troubleshooting methods online suggested that the Local git config has sslVerify set to false could help. Did that, no gain. 2. Tried cloning my personal git repository to test the working of the Git integration on Oracle SQL Developer. It was able to successfully fetch the remote branches. Hence, the error is coming up only while cloning an Azure Repository. 3. Looked at almost all the solution links online, but most of them were for Eclipse. Since both Eclipse and SQL Developer are Java-based applications, I tried doing those resolutions but most of them are regarding SSL Verify setting to false. At the end I have raised the issue here. Hoping to find some help here. Thanks in advance.
Categories: DBA Blogs

音楽専門学校におけるインターン制度で就職

Marian Crkon - Sun, 2022-05-15 00:14
音楽専門学校に通学する方々の動機というのは人それぞれ大きく異なります。プロになりたいと考えている方が居る一方で...

音楽専門学校におけるインターン制度で就職

The Feature - Sun, 2022-05-15 00:14

音楽専門学校に通学する方々の動機というのは人それぞれ大きく異なります。プロになりたいと考えている方が居る一方で、業界に入り何らかの立場で働きたいと考えている方も居ます。数ある進路の中で音楽専門学校という選択肢を選ぶのは、独学には存在しないメリットがあるためです。音楽に携わってお仕事をしていくという場合は、どのような形態であっても様々なスキルや知識を持ち合わせている事は必須です。

専門性が高い上に、感性も大きく関係するためインターネットや書物を用いても知識を得る事は困難で、指導員から直接的に学ぶ事に越したことはありません。歌手や楽器を演奏するといった事は、特に感性により左右されるので座学でプロの腕前になる事は不可能です。そして、指導員の多くは普段スタジオなどで録音作業に携わっているプロのミュージシャンです。いわゆる二足のわらじを履いている状態なので、指導員を介する事で業界に足を踏み入れるきっかけができるという可能性は十分にあるのです。

しかも、音楽専門学校としても就職率を高めたり優秀な人材を業界に投入させるという意味合いから、業界と学生の接点を数多く用意しているのです。また、音楽専門学校は就職希望者を中心に一定期間提携している企業で働くという制度を導入している事もあります。刻々と変化する音楽の業界で活動することにより、学校では学べない事を学べます。卒業後は、インターン制度を利用した企業にそのまま入社するというケースも珍しくありません。

Categories: APPS Blogs

Pages

Subscribe to Oracle FAQ aggregator