Thinking Out Loud

May 22, 2022

Systemd Persistent Logging

Filed under: 19c,linux,OEL7,oracle — mdinh @ 3:05 pm

Remembrance from an old friend: Curiosity killed the cat, but satisfaction brought it back.

Finally found how to keep systemd logging persistent.

==================================================
### Number of reboots:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# last reboot
reboot   system boot  5.4.17-2136.307. Sat May 21 17:53 - 18:01  (00:07)
reboot   system boot  5.4.17-2136.307. Sat May 21 17:48 - 17:51  (00:03)
reboot   system boot  5.4.17-2136.307. Sat May 21 11:01 - 17:48  (06:46)
reboot   system boot  5.4.17-2136.307. Fri May 20 23:12 - 11:01  (11:48)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:54 - 23:11  (00:17)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:49 - 22:54  (00:04)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:21 - 22:49  (00:27)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:04 - 22:21  (00:16)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:35 - 22:03  (22:28)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:19 - 23:34  (00:15)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:07 - 23:18  (00:11)
reboot   system boot  5.4.17-2136.307. Thu May 19 22:54 - 23:05  (00:11)
reboot   system boot  5.4.17-2136.307. Thu May 19 22:47 - 22:53  (00:06)
reboot   system boot  5.4.17-2136.307. Thu May 19 21:08 - 22:45  (01:37)
reboot   system boot  5.4.17-2136.307. Thu May 19 20:40 - 21:07  (00:27)
reboot   system boot  5.4.17-2136.307. Thu May 19 07:28 - 10:59  (03:31)
reboot   system boot  5.4.17-2136.307. Thu May 19 07:22 - 07:27  (00:05)
reboot   system boot  5.4.17-2036.100. Wed May 18 22:24 - 07:22  (08:57)

wtmp begins Wed May 18 22:24:31 2022
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Only 1 reboot recorded from journalctl:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# journalctl --list-boots
 0 016de73a7e81411bbd7c0b7657863852 Sat 2022-05-21 17:53:18 -04—Sat 2022-05-21 18:01:01 -04
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# journalctl | head -1
-- Logs begin at Sat 2022-05-21 17:53:18 -04, end at Sat 2022-05-21 18:01:01 -04. --
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Enable persistent systemd journal log:
==================================================

Enable persistent storage for the systemd journal log
https://gist.github.com/JPvRiel/b7c185833da32631fa6ce65b40836887

[root@oracle-goldengate-1910-vagrant ~]# mkdir /var/log/journal
[root@oracle-goldengate-1910-vagrant ~]# systemd-tmpfiles --create --prefix /var/log/journal
[root@oracle-goldengate-1910-vagrant ~]# echo "SystemMaxUse=100M" >> /etc/systemd/journald.conf
[root@oracle-goldengate-1910-vagrant ~]# grep -i SystemMaxUse /etc/systemd/journald.conf
#SystemMaxUse=
SystemMaxUse=100M
[root@oracle-goldengate-1910-vagrant ~]# systemctl restart systemd-journald.service

==================================================
### Moment of truth:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# last reboot
reboot   system boot  5.4.17-2136.307. Sat May 21 18:31 - 18:33  (00:01)
reboot   system boot  5.4.17-2136.307. Sat May 21 18:12 - 18:29  (00:16)
reboot   system boot  5.4.17-2136.307. Sat May 21 17:53 - 18:09  (00:15)

reboot   system boot  5.4.17-2136.307. Sat May 21 17:48 - 17:51  (00:03)
reboot   system boot  5.4.17-2136.307. Sat May 21 11:01 - 17:48  (06:46)
reboot   system boot  5.4.17-2136.307. Fri May 20 23:12 - 11:01  (11:48)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:54 - 23:11  (00:17)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:49 - 22:54  (00:04)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:21 - 22:49  (00:27)
reboot   system boot  5.4.17-2136.307. Fri May 20 22:04 - 22:21  (00:16)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:35 - 22:03  (22:28)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:19 - 23:34  (00:15)
reboot   system boot  5.4.17-2136.307. Thu May 19 23:07 - 23:18  (00:11)
reboot   system boot  5.4.17-2136.307. Thu May 19 22:54 - 23:05  (00:11)
reboot   system boot  5.4.17-2136.307. Thu May 19 22:47 - 22:53  (00:06)
reboot   system boot  5.4.17-2136.307. Thu May 19 21:08 - 22:45  (01:37)
reboot   system boot  5.4.17-2136.307. Thu May 19 20:40 - 21:07  (00:27)
reboot   system boot  5.4.17-2136.307. Thu May 19 07:28 - 10:59  (03:31)
reboot   system boot  5.4.17-2136.307. Thu May 19 07:22 - 07:27  (00:05)
reboot   system boot  5.4.17-2036.100. Wed May 18 22:24 - 07:22  (08:57)

wtmp begins Wed May 18 22:24:31 2022
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### More than 1 reboot recorded from journalctl:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# journalctl --list-boots|sort
 0 beedb88ca40c4c6187ae9d5c7ff1e96e Sat 2022-05-21 18:31:53 -04—Sat 2022-05-21 23:30:01 -04
-1 e12a9e02fd374350a5fcd3d4360b09fc Sat 2022-05-21 18:12:48 -04—Sat 2022-05-21 18:29:34 -04
-2 016de73a7e81411bbd7c0b7657863852 Sat 2022-05-21 17:53:18 -04—Sat 2022-05-21 18:09:16 -04
[root@oracle-goldengate-1910-vagrant ~]#

==================================================
### Review GoldengGate Start and Stop:
==================================================

[root@oracle-goldengate-1910-vagrant ~]# last reboot|head -5|sort -n
reboot   system boot  5.4.17-2136.307. Sat May 21 11:01 - 17:48  (06:46)
reboot   system boot  5.4.17-2136.307. Sat May 21 17:48 - 17:51  (00:03)

reboot   system boot  5.4.17-2136.307. Sat May 21 17:53 - 18:09  (00:15)

reboot   system boot  5.4.17-2136.307. Sat May 21 18:12 - 18:29  (00:16)
reboot   system boot  5.4.17-2136.307. Sat May 21 18:31 - 19:11  (00:39)
[root@oracle-goldengate-1910-vagrant ~]#

[root@oracle-goldengate-1910-vagrant ~]# journalctl --no-pager|grep "Manager st"
May 21 17:53:44 oracle-goldengate-1910-vagrant start_goldengate.sh[3115]: Manager started.

May 21 18:09:04 oracle-goldengate-1910-vagrant stop_goldengate.sh[6177]: Manager stopped.
May 21 18:13:15 oracle-goldengate-1910-vagrant start_goldengate.sh[3383]: Manager started.

May 21 18:29:21 oracle-goldengate-1910-vagrant stop_goldengate.sh[6290]: Manager stopped.
May 21 18:32:17 oracle-goldengate-1910-vagrant start_goldengate.sh[4134]: Manager started.
[root@oracle-goldengate-1910-vagrant ~]#

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

reboot system boot  5.4.17-2136.307. Sat May 21 17:48 - 17:51  (00:03) 
journalctl May 21 17:53:44 oracle-goldengate-1910-vagrant start_goldengate.sh[3115]: Manager started.

reboot system boot  5.4.17-2136.307. Sat May 21 17:53 - 18:09  (00:15)
journalctl May 21 18:09:04 oracle-goldengate-1910-vagrant stop_goldengate.sh[6177]: Manager stopped.
journalctl May 21 18:13:15 oracle-goldengate-1910-vagrant start_goldengate.sh[3383]: Manager started.

reboot system boot  5.4.17-2136.307. Sat May 21 18:12 - 18:29  (00:16)
journalctl May 21 18:13:15 oracle-goldengate-1910-vagrant start_goldengate.sh[3383]: Manager started.
journalctl May 21 18:29:21 oracle-goldengate-1910-vagrant stop_goldengate.sh[6290]: Manager stopped.

reboot system boot  5.4.17-2136.307. Sat May 21 18:31 - 10:57  (16:25)
journalctl May 21 18:32:17 oracle-goldengate-1910-vagrant start_goldengate.sh[4134]: Manager started.

May 21, 2022

Systemd Database GoldenGate Service Start Stop

Filed under: 19c,GoldenGate,linux,OEL7,oracle — mdinh @ 6:13 pm

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 ~]#

January 6, 2022

Review RMAN Backup for All Databases On Host

Filed under: oracle,RMAN — mdinh @ 1:18 am

There are 15+ databases on host.

Here’s a nice and easy method to run any SQL for all databases.

[oracle@xxxxxxxxxx dinh]$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;
Database check DEBUGX1
ORACLE_SID = [xxxxxxxxxx] ? The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
DEBUGX    DEBUGX                         PRIMARY


DATAFILE_CT
-----------
         66


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-20 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          902          691
2021-DEC-20 COMPLETED                          1 DB INCR       DISK                     1              1       17,563           41
2021-DEC-21 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          970          730
2021-DEC-21 COMPLETED                          1 DB INCR       DISK                     1              1       17,527           36
2021-DEC-22 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          977          732
2021-DEC-22 COMPLETED                          1 DB INCR       DISK                     1              1       17,537           38
2021-DEC-23 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          934          719
2021-DEC-23 COMPLETED                          1 DB INCR       DISK                     1              1       17,523           38
2021-DEC-24 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          937          720
2021-DEC-24 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-25 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,024          746
2021-DEC-25 COMPLETED                          1 DB INCR       DISK                     1              1       17,507           36
2021-DEC-26 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,040          750
2021-DEC-26 COMPLETED                          9 DB INCR       DISK                     1              1       20,617        3,515
2021-DEC-27 COMPLETED                          9 ARCHIVELOG    DISK                    22             22          906          692
2021-DEC-27 COMPLETED                          2 DB INCR       DISK                     1              1       17,528           39
2021-DEC-28 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          967          729
2021-DEC-28 COMPLETED                          1 DB INCR       DISK                     1              1       17,544           39
2021-DEC-29 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          988          736
2021-DEC-29 COMPLETED                          1 DB INCR       DISK                     1              1       17,546           38
2021-DEC-30 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          921          716
2021-DEC-30 COMPLETED                          1 DB INCR       DISK                     1              1       17,578           48
2021-DEC-31 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          945          723
2021-DEC-31 COMPLETED                          1 DB INCR       DISK                     1              1       17,517           36
2022-JAN-01 COMPLETED                          8 ARCHIVELOG    DISK                    23             23        1,016          744
2022-JAN-01 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           38
2022-JAN-02 COMPLETED                          9 ARCHIVELOG    DISK                    23             23        1,009          742
2022-JAN-02 COMPLETED                          9 DB INCR       DISK                     1              1       20,627        3,514
2022-JAN-03 COMPLETED                          8 ARCHIVELOG    DISK                    22             22          903          691
2022-JAN-03 COMPLETED                          1 DB INCR       DISK                     1              1       17,567           42
2022-JAN-04 COMPLETED                          8 ARCHIVELOG    DISK                    23             23          958          727
2022-JAN-04 COMPLETED                          1 DB INCR       DISK                     1              1       17,530           36
2022-JAN-05 COMPLETED                          5 ARCHIVELOG    DISK                    14             14          511          421
2022-JAN-05 COMPLETED                          1 DB INCR       DISK                     1              1       17,511           37
                                                                                                         ------------ ------------
sum                                                                                                           320,167       19,616

34 rows selected.

Database check RHPNEWDB1
ORACLE_SID = [XXXXXXXX] ? ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle

Session altered.


NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
RHPNEWDB  RHPNEWDB_XXXXXXX               PRIMARY


DATAFILE_CT
-----------
         33


START_DATE  STATUS                       MINUTES INPUT_TYPE    OUTPUT_TYPE  INPUT_TYPE_CT OUTPUT_TYPE_CT     INPUT_MB    OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-26 COMPLETED WITH WARNINGS           17 ARCHIVELOG    DISK                     5              5       18,692        6,516
2021-DEC-26 COMPLETED WITH WARNINGS          414 DB INCR       DISK                     1              1    1,183,591      182,525
2021-DEC-27 COMPLETED WITH WARNINGS           59 ARCHIVELOG    DISK                    17             17       67,941       23,384
2021-DEC-27 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,048        3,537
2021-DEC-28 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,189       23,623
2021-DEC-28 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,403        3,826
2021-DEC-29 COMPLETED WITH WARNINGS           65 ARCHIVELOG    DISK                    22             22       68,835       23,807
2021-DEC-29 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       19,801        3,934
2021-DEC-30 COMPLETED WITH WARNINGS           61 ARCHIVELOG    DISK                    22             22       68,416       23,680
2021-DEC-30 COMPLETED WITH WARNINGS           11 DB INCR       DISK                     1              1       22,870        4,739
2021-DEC-31 COMPLETED WITH WARNINGS           58 ARCHIVELOG    DISK                    22             22       64,633       22,392
2021-DEC-31 COMPLETED WITH WARNINGS           10 DB INCR       DISK                     1              1       20,545        4,125
2022-JAN-01 COMPLETED WITH WARNINGS           57 ARCHIVELOG    DISK                    22             22       61,385       21,477
2022-JAN-01 COMPLETED WITH WARNINGS            9 DB INCR       DISK                     1              1       17,463        3,689
2022-JAN-02 COMPLETED WITH WARNINGS           52 ARCHIVELOG    DISK                    21             21       55,319       19,407
2022-JAN-02 COMPLETED WITH WARNINGS          419 DB INCR       DISK                     1              1    1,193,071      185,202
2022-JAN-03 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    17             17       68,181       23,433
2022-JAN-03 COMPLETED WITH WARNINGS           12 DB INCR       DISK                     1              1       23,149        5,028
2022-JAN-04 COMPLETED WITH WARNINGS           62 ARCHIVELOG    DISK                    22             22       68,714       23,797
2022-JAN-04 COMPLETED WITH WARNINGS            8 DB INCR       DISK                     1              1       18,222        3,418
2022-JAN-05 COMPLETED WITH WARNINGS           38 ARCHIVELOG    DISK                    16             16       41,959       14,496
                                                                                                         ------------ ------------
sum                                                                                                         3,188,427      626,035

21 rows selected.

[oracle@xxxxxxxxxx dinh]$

----- COMPLETED WITH WARNINGS
Check RMAN log file to find
RMAN-08120: warning: archived log not deleted, not yet applied by standby

for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;

--- backup_review.sql
col input_mb  format 999,999,999
col output_mb format 999,999,999
col output_type for a12
col status for a25
set lines 200 pages 5000
break on report
compute SUM of input_mb on report
compute SUM of output_mb on report
break on report
alter session set nls_date_format = 'YYYY-MON-DD';
set echo on
select name, db_unique_name, database_role from v$database;
select count(*) datafile_ct from v$datafile;
select
TRUNC(start_time) start_date,
status,
round(sum(elapsed_seconds)/60) minutes,
input_type,
output_device_type output_type,
count(input_type) input_type_ct,
count(output_device_type) output_type_ct,
round(sum(input_bytes)/1024/1024) input_mb,
round(sum(output_bytes)/1024/1024) output_mb
from V$RMAN_BACKUP_JOB_DETAILS
where TRUNC(start_time)>=TRUNC(sysdate)-16
group by TRUNC(start_time), input_type, output_device_type, status
order by 1 asc, input_type asc, 2
;
/* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
exit

December 8, 2021

Find Statistics and SQL Using Index

Filed under: 11g,oracle,performance — mdinh @ 10:05 pm

Database is facing issues with enq: TX – allocate ITL entry.

Research to find Troubleshooting waits for ‘enq: TX – allocate ITL entry’ (Doc ID 1472175.1)

Index DINH.INFO_IX1 was rebuilt.

To find SQL using index DINH.INFO_IX1- https://github.com/jkstill/oracle-script-lib/blob/master/sql/find-index-sql.sql

After running find-index-sql.sql, get execution statistics and history for a SQL using Doc ID 1371778.1.

The document illustrates how to get execution statistics and history for a SQL using sql_id using Data Dictionary queries for both queries in memory and those in the AWR.

DEMO:

-- Find SQL where an index has been used

SQL> @find-index-sql.sql

old   2:    :b_index_owner := '&&v_index_owner';
new   2:    :b_index_owner := 'DINH';
old   3:    :b_index_name := '&&v_index_name';
new   3:    :b_index_name := 'INFO_IX1';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00


SQL_ID        PLAN_HASH_VALUE
------------- ---------------
272kpc0q766tx      1690776192
7wd9njg9sy9mk      1635894828
gu9t5kgmy3pp6       698638001

Elapsed: 00:00:46.75

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 272kpc0q766tx using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 272kpc0q766tx
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '272kpc0q766tx'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
272kpc0q766tx            0      1690776192 2018-10-23/11:20:35  2021-10-21/05:55:23                                                                29
272kpc0q766tx            1      1690776192 2018-10-23/11:20:35  2021-07-29/19:55:22                                                          29268833
272kpc0q766tx            3      1690776192 2018-10-23/11:20:35  2021-12-08/08:00:26                                                               441
272kpc0q766tx            4      1690776192 2018-10-23/11:20:35  2021-05-07/05:16:48                                                              3583
272kpc0q766tx            6      1690776192 2018-10-23/11:20:35  2021-12-01/21:18:11                                                            413747
272kpc0q766tx            8      1690776192 2018-10-23/11:20:35  2021-12-01/22:07:51                                                               164
272kpc0q766tx           37      1690776192 2018-10-23/11:20:35  2021-04-24/10:51:28                                                           3394967
272kpc0q766tx           38      1690776192 2018-10-23/11:20:35  2021-04-24/16:02:52                                                             43383
272kpc0q766tx           49      1690776192 2018-10-23/11:20:35  2021-07-21/18:27:05                                                            466811
272kpc0q766tx           51      1690776192 2018-10-23/11:20:35  2021-07-21/22:27:11                                                              5238
272kpc0q766tx           54      1690776192 2018-10-23/11:20:35  2021-08-07/22:48:03                                                           3286771
272kpc0q766tx           56      1690776192 2018-10-23/11:20:35  2021-08-08/05:01:11                                                              9835
272kpc0q766tx           67      1690776192 2018-10-23/11:20:35  2021-10-02/06:52:24                                                            126753
272kpc0q766tx           68      1690776192 2018-10-23/11:20:35  2021-10-02/10:41:18                                                               138

14 rows selected.

--- From AWR

Enter value for sql_id: 272kpc0q766tx
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '272kpc0q766tx'

SQL_ID           SNAP_ID PLAN_HASH_VALUE SQL_PROFILE                      EXECUTIONS_TOTAL
------------- ---------- --------------- -------------------------------- ----------------
272kpc0q766tx     353952      1690776192                                          35878169
272kpc0q766tx     355246      1690776192                                          37443248

SQL>

======================================================================
--- sql_history.sql for SQL_ID: 7wd9njg9sy9mk using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: 7wd9njg9sy9mk
old  16: where sql_id = '&sql_id'
new  16: where sql_id = '7wd9njg9sy9mk'

no rows selected

--- From AWR

Enter value for sql_id: 7wd9njg9sy9mk
old  15: where sql_id = '&sql_id'
new  15: where sql_id = '7wd9njg9sy9mk'

no rows selected

SQL>

======================================================================
--- sql_history.sql for SQL_ID: gu9t5kgmy3pp6 using index DINH.INFO_IX1
======================================================================

SQL> @sql_history.sql

--- From Memory

Enter value for sql_id: gu9t5kgmy3pp6
old  16: where sql_id = '&sql_id'
new  16: where sql_id = 'gu9t5kgmy3pp6'

SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE FIRST_LOAD_TIME      LAST_LOAD_TIME       OUTLINE_CATEGORY     SQL_PROFILE                      EXECUTIONS
------------- ------------ --------------- -------------------- -------------------- -------------------- -------------------------------- ----------
gu9t5kgmy3pp6            0       698638001 2018-10-23/11:20:27  2021-12-08/08:05:10                                                               268
gu9t5kgmy3pp6            1       698638001 2018-10-23/11:20:27  2021-08-07/23:19:17                                                           5343273
gu9t5kgmy3pp6            5       698638001 2018-10-23/11:20:27  2021-12-01/18:42:33                                                            190004
gu9t5kgmy3pp6           23       698638001 2018-10-23/11:20:27  2021-04-24/14:27:12                                                           5512867
gu9t5kgmy3pp6           32       698638001 2018-10-23/11:20:27  2021-07-21/19:22:08                                                           2831328
gu9t5kgmy3pp6           39       698638001 2018-10-23/11:20:27  2021-10-02/04:16:50                                                            287105

6 rows selected.

--- From AWR

Enter value for sql_id: gu9t5kgmy3pp6
old  15: where sql_id = '&sql_id'
new  15: where sql_id = 'gu9t5kgmy3pp6'

no rows selected

SQL>

UPDATE for sql_id 7wd9njg9sy9mk:

sql_id 7wd9njg9sy9mk exist from gv$sql_plan and dba_hist_sql_plan (using find-index-sql.sql)

but not from v$sql and dba_hist_sqlstat (using Doc ID 1371778.1) which is strange.

Manually checking using DBMS_XPLAN.display_cursor and dbms_xplan.display_awr shows:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(sql_id=>'7wd9njg9sy9mk',format=>'ALLSTATS LAST +cost +bytes')); 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID: 7wd9njg9sy9mk, child number: 0 cannot be found


Elapsed: 00:00:00.00

SQL>

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('7wd9njg9sy9mk'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
SQL_ID 7wd9njg9sy9mk
--------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 1635894828

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |                         |       |       |   538 (100)|          |
|   1 |  DELETE             | *******_UPDATE_INFO     |       |       |            |          |
|   2 |   COUNT STOPKEY     |                         |       |       |            |          |
|   3 |    NESTED LOOPS     |                         |    50M|   958M|   538 (100)| 00:00:07 |
|   4 |     INDEX FULL SCAN | SYS_C00137313           |    50M|   620M|     4   (0)| 00:00:01 |
|   5 |     INDEX RANGE SCAN| **************_INFO_IX1 |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


21 rows selected.

Elapsed: 00:00:00.03

SQL>

December 4, 2021

How To Resolve Tablespace Created With Windows Path In Linux

Filed under: 11g,linux,oracle — mdinh @ 3:20 pm

Now I understand the rationale for not providing DBA privileges to inexperience developers.

Currently, I am uncertain if the current environment is production or not.

Here is demo for 11.2.0.4 to demonstrate the issue and resolution.

Reference:
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles005.htm#i1006478

[oracle@ol7-112-dg1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 4 14:46:37 2021

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u01/oradata

----------------------------------------
--- Windows path used for datafile:
----------------------------------------

SQL> create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M;

Tablespace created.

SQL> set lines 200
SQL> col name for a80
SQL> select file#,name from v$datafile order by 1;

     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 /u01/oradata/hawk/system01.dbf
         2 /u01/oradata/hawk/sysaux01.dbf
         3 /u01/oradata/hawk/undotbs01.dbf
         4 /u01/oradata/hawk/users01.dbf
         5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf

SQL> alter tablespace TEST offline normal;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> host

----------------------------------------
--- Rename datafile from OS
----------------------------------------

[oracle@ol7-112-dg1 trace]$ mv -fv /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf /u01/oradata/test.dbf
‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf’ -> ‘/u01/oradata/test.dbf’

[oracle@ol7-112-dg1 trace]$ exit
exit

SQL> alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf';

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> alter tablespace TEST online;

Tablespace altered.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> drop tablespace TEST including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Alert Log: NO ERRORS!
----------------------------------------

[oracle@ol7-112-dg1 trace]$ tail -25 alert_hawk.log
LNS: Standby redo logfile selected for thread 1 sequence 126 for destination LOG_ARCHIVE_DEST_2
Sat Dec 04 14:47:17 2021
create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Completed: create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Sat Dec 04 14:48:08 2021
alter tablespace TEST offline normal
Completed: alter tablespace TEST offline normal
Sat Dec 04 14:48:08 2021
Starting background process SMCO
Sat Dec 04 14:48:08 2021
SMCO started with pid=36, OS id=5159
Sat Dec 04 14:48:19 2021
Checker run found 2 new persistent data failures
Sat Dec 04 14:49:33 2021
alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Completed: alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Sat Dec 04 14:49:42 2021
Checker run found 1 new persistent data failures
Sat Dec 04 14:50:00 2021
alter tablespace TEST online
Completed: alter tablespace TEST online
Sat Dec 04 14:50:33 2021
drop tablespace TEST including contents and datafiles
Deleted file /u01/oradata/test.dbf
Completed: drop tablespace TEST including contents and datafiles
[oracle@ol7-112-dg1 trace]$

----------------------------------------
--- Resolution for 19c with ASM
----------------------------------------

SQL> alter session set container=PDB;
SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '+DATAC1';

May 14, 2021

Gather Your Session Info For Killing

Filed under: 11g,linux,oracle — mdinh @ 4:40 am

So there I was, running emremove.sql as part of pre-upgrade task; however, it was taking longer than expected.

Session was stuck at the output shown below and desperately CTRL-C did not work.

14:35:05 472  /
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

^C

^C^C

^C

I checked for blocking session and there were blocking locks from SYS which was really strange.

I made a gutsy call and kill SYS session from OS prompt based on timestamp.

~ $ ps -ef|grep sysdba
oracle    57147 231962  0 May12 pts/4    00:00:00 sqlplus   as sysdba
oracle   155919 139352  0 14:34 pts/1    00:00:00 sqlplus   as sysdba
oracle   244619 216760  0 15:25 pts/5    00:00:00 grep --color=auto sysdba

~ $ kill -9 155919

As it turns out, another DBA was logged in as sysdba causing havoc.

I was lucky to have killed the correct SYS session and will you be as lucky as I was?

Based on my near disaster, it would be better to create good practice of gathering your session info to be able to kill the correct session.

Here is current session info.

SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        101 16428                    16427

SQL>

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16427
oracle   16427  8573  0 03:44 pts/0    00:00:00 sqlplus   as sysdba
oracle   16428 16427  0 03:44 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16461 11677  0 03:45 pts/1    00:00:00 grep --color=auto 16427
[oracle@ol7-112-dg1 ~]$

Kill OS process using sqlplus PROCESSID – don’t know session is killed until DML is performed.

[oracle@ol7-112-dg1 ~]$ kill -9 16428

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 16428
Session ID: 5 Serial number: 101


SQL>

Another test

--- Session Info
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        103 16533                    16532

SQL>

--- From another session, check waits for above session
SQL> r
  1  select NVL(s.username,'(oracle)') AS username, s.sid, s.serial#,
  2  sw.event, sw.seconds_in_wait, sw.state
  3  from v$session_wait sw, v$session s
  4  where s.sid = sw.sid and s.sid=&sid
  5*
Enter value for sid: 5
old   4: where s.sid = sw.sid and s.sid=&sid
new   4: where s.sid = sw.sid and s.sid=5

USERNAME               SID    SERIAL# EVENT                          SECONDS_IN_WAIT STATE
--------------- ---------- ---------- ------------------------------ --------------- -------------------
SYS                      5        115 SQL*Net message from client                169 WAITING

SQL>

Kill OS process using sqlplus CLIENTPID – immediate feedback –

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16532
oracle   16532  8573  0 03:46 pts/0    00:00:00 sqlplus   as sysdba
oracle   16533 16532  0 03:46 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16557 11677  0 03:47 pts/1    00:00:00 grep --color=auto 16532
[oracle@ol7-112-dg1 ~]$


[oracle@ol7-112-dg1 ~]$ kill -9 16532


SQL> Killed
[oracle@ol7-112-dg1 ~]$

Hopefully you will never have to kill your own session.

When you need kill your session, it’s better to have the correct information versus guessing.

May 1, 2021

Extract DB User Password

Filed under: 19c,oracle — mdinh @ 12:40 am

For some reason, I had a mental block in trying to extract password for database users.

Here are some options.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

-- From notes:
SQL> set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);

SQL> r
  1  select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' ddl
  2  from dba_users where username='CTXSYS'
  3*
alter user CTXSYS identified by values 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A4B2E98D77B9504BC2EBB457B63600127E34D';

SQL>


-- From colleague
SQL> select name, spare4 from sys.user$ where name='CTXSYS';
CTXSYS
S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D

SQL>

-- From asktom
SQL> r
  1  with t as
  2  (select TO_CHAR(dbms_metadata.get_ddl('USER','CTXSYS')) ddl from dual )
  3  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' sql
  4* from t

ALTER USER "CTXSYS" IDENTIFIED BY VALUES 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D';

SQL>

March 27, 2021

Cleanup Trace Files For Multiple Oracle Homes

Filed under: adrci,awk_sed_grep,linux,oracle — mdinh @ 4:36 pm

I know what you are probably thinking. What’s the big deal and how many homes can there be?

For Exadata Cloud, I recalled seeing as many as 18 database homes.

As shown below, there are 5 database homes with version 12.2 and 1 database home with version 19.0.

# dbaascli dbhome info
DBAAS CLI version 21.1.1.0.1
Executing command dbhome info
Enter a homename or just press enter if you want details of all homes

1.HOME_NAME=OraHome101
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_4
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=
   OH Backup=NOT Configured 

2.HOME_NAME=OraHome100
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_7
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=d21b07df-20f2-439e-bc40-78a9597af362
 OH Backup=NOT Configured

3.HOME_NAME=OraHome105_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_6
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=******
   Agent DB IDs=f7d46615-a223-4002-9270-fa69465a7f2a
 OH Backup=NOT Configured

4.HOME_NAME=OraHome102_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_3
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=dceed071-9655-4c84-bef4-74b20180c99b
 OH Backup=NOT Configured

5.HOME_NAME=OraHome101_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*******
   Agent DB IDs=b2a5220d-844b-49b6-9351-7c72cf3c9d9b
 OH Backup=NOT Configured

6.HOME_NAME=OraHome100_19800_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/19.0.0.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0
  DBs installed=********
   Agent DB IDs=feedb0e0-2d10-4db7-997a-a78e4ab083ef

Checking oratab for Oracle Homes

$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/19.0.0.0/grid
/u02/app/oracle/product/12.2.0/dbhome_2
/u02/app/oracle/product/12.2.0/dbhome_3
/u02/app/oracle/product/12.2.0/dbhome_4
/u02/app/oracle/product/12.2.0/dbhome_6
/u02/app/oracle/product/12.2.0/dbhome_7
/u02/app/oracle/product/19.0.0.0/dbhome_2

Here is the crontab schedule:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;
00 04 * * * find /u02/app/oracle/diag/rdbms/*/*/cdump -name "core*" -mtime +200 -exec rm -rf {} \;

Here is the explanation for what (*) represents and examples:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;

ls -ld /u01/app/grid/diag/crs/*/crs/trace
* = hostname

Example:
$ ls -ld /u01/app/grid/diag/crs/*/crs/trace
drwxrwxr-x 2 grid oinstall 135168 Mar 26 18:40 /u01/app/grid/diag/crs/hostname/crs/trace

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

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;

ls -ld /u02/app/oracle/product/*/*/rdbms/audit
*/* = version/dbhome

Example:
$ ls -ld /u02/app/oracle/product/*/*/rdbms/audit
drwxr-xr-x 9 oracle oinstall  614400 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/audit
drwxr-xr-x 2 oracle oinstall  253952 Mar 26 18:40 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/audit
drwxr-xr-x 2 oracle oinstall  294912 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/audit
drwxr-xr-x 4 oracle oinstall   94208 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/audit
drwxr-xr-x 2 oracle oinstall    4096 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/audit
drwxr-xr-x 3 oracle oinstall 5783552 Mar 26 18:32 /u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/audit

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

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;

ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
*/* = version/dbhome

Example:
$ ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
-rw-r----- 1 oracle asmadmin 868 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_57506.trc
-rw-r----- 1 oracle asmadmin 868 Dec  4 18:06 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_66404.trc
-rw-r----- 1 oracle asmadmin 862 Mar 24 19:38 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/log/*****2_ora_217755.trc
-rw-r----- 1 oracle asmadmin 869 Feb 18 21:51 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_351349.trc
-rw-r----- 1 oracle asmadmin 867 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_57519.trc
-rw-r----- 1 oracle asmadmin 866 Mar  1 20:01 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/log/******2_ora_167170.trc
-rw-r----- 1 oracle asmadmin 831 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/log/*****2_ora_314160.trc

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

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;

ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
*/* = db_unique_name/db_name

Example:
$ ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  3  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  2  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 17 02:35 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 18 21:51 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 25 07:13 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump

It’s also possible to use adrci to configure SHORTP_POLICY and LONGP_POLICY.

If new homes are created, then would SHORTP_POLICY and LONGP_POLICY need up be updated for the new homes?

Alternatively, can download and use purgeLogs: Cleanup traces, logs in one command (Doc ID 2081655.1)

October 18, 2020

Followup with Database runInstaller applyRU Failed Me

Filed under: 19c,oracle,Patching,PSU,runInstaller — mdinh @ 3:57 am

This is a followup Database runInstaller applyRU Failed Me

I finally figured out my error which I should have seen from the beginning and better error reporting would have helped.

Can you guess what’s wrong?

unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_LINUX.zip; echo $?
versus
unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?

The wrong platform for opatch was used.
Here is what should have been deployed.

[oracle@ol7-112-dg1 ~]$ unzip -qod $ORACLE_HOME /vagrant_software/p6880880_190000_Linux-x86-64.zip; echo $?
0
[oracle@ol7-112-dg1 ~]$
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
29517242;Database Release Update : 19.3.0.0.190416 (29517242)
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$

[oracle@ol7-112-dg1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0.0/db_1
[oracle@ol7-112-dg1 ~]$

### This failed:
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1
[oracle@ol7-112-dg1 ~]$

### This works but why?
[oracle@ol7-112-dg1 ~]$ $ORACLE_HOME/OPatch/opatch version -jdk $ORACLE_HOME/jdk
OPatch Version: 12.2.0.1.21
OPatch succeeded.
[oracle@ol7-112-dg1 ~]$

### Here is java version and noticed it's 64-Bit
[oracle@ol7-112-dg1 bin]$ $ORACLE_HOME/jdk/bin/java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[oracle@ol7-112-dg1 bin]$

$ORACLE_HOME/runInstaller -applyRU /home/oracle/patch/31305339 should now work.

October 17, 2020

Database runInstaller applyRU Failed Me

Filed under: 19c,oracle,Patching,PSU,runInstaller — mdinh @ 5:24 pm

I still remembered the cliche a manager used to tell me, “Slow and steady win the race.”

Looks like it is true with Oracle software as you never know what’s going to get.

I am trying to install 19.3 database software and apply Patch 31305339 – GI Release Update 19.8.0.0.200714 which failed misserably.

There is little to no information for the failure (at least from what I was able to ascertain).

Resolving the issue requires starting over from the beginning.

Here are the steps taken:

[oracle@ol7-112-dg1 ~]$ cat /etc/system-release
Oracle Linux Server release 7.7

[oracle@ol7-112-dg1 ~]$ ll /etc/ora*
-rw-r--r--. 1 root   root      32 Aug  8  2019 /etc/oracle-release
-rw-rw-r--. 1 oracle oinstall 790 Oct 14 19:13 /etc/oratab

[oracle@ol7-112-dg1 ~]$ echo $new_db_home
/u01/app/oracle/product/19.3.0.0/db_1

[oracle@ol7-112-dg1 ~]$ rm -rf $new_db_home

[oracle@ol7-112-dg1 ~]$ ls $new_db_home
ls: cannot access /u01/app/oracle/product/19.3.0.0/db_1: No such file or directory

[oracle@ol7-112-dg1 ~]$ echo $zip_loc
/vagrant_software

[oracle@ol7-112-dg1 ~]$ ls -l $zip_loc/LINUX.X64_193000_db_home.zip
-rwxrwxrwx. 1 vagrant vagrant 3059705302 Sep  5  2019 /vagrant_software/LINUX.X64_193000_db_home.zip

[oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/LINUX.X64_193000_db_home.zip -d $new_db_home; echo $?; ls $new_db_home
0
addnode     crs   dbjava       dmu      hs             jdbc  md       olap     ords  plsql    rdbms          runInstaller   sqlj      ucp
apex        css   dbs          drdaas   install        jdk   mgw      OPatch   oss   precomp  relnotes       schagent.conf  sqlpatch  usm
assistants  ctx   deinstall    dv       instantclient  jlib  network  opmn     oui   QOpatch  root.sh        sdk            sqlplus   utl
bin         cv    demo         env.ora  inventory      ldap  nls      oracore  owm   R        root.sh.old    slax           srvm      wwg
clone       data  diagnostics  has      javavm         lib   odbc     ord      perl  racg     root.sh.old.1  sqldeveloper   suptools  xdk

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version
OPatch Version: 12.2.0.1.17

OPatch succeeded.

[oracle@ol7-112-dg1 ~]$ unzip -qo $zip_loc/p6880880_190000_LINUX.zip -d $new_db_home; echo $?
0

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
/u01/app/oracle/product/19.3.0.0/db_1/OPatch/opatch: line 839: [: too many arguments
Java (1.7) could not be located. OPatch cannot proceed!
OPatch returns with error code = 1

[oracle@ol7-112-dg1 ~]$ $new_db_home/OPatch/opatch version -jdk $new_db_home/jdk
OPatch Version: 12.2.0.1.21

OPatch succeeded.

[oracle@ol7-112-dg1 ~]$ cd $new_db_home
[oracle@ol7-112-dg1 db_1]$ ls -l /home/oracle/patch/31305339
total 132
drwxr-x---. 5 oracle oinstall     81 Jul 10 05:20 31281355
drwxr-x---. 5 oracle oinstall     62 Jul 10 05:17 31304218
drwxr-x---. 5 oracle oinstall     62 Jul 10 05:18 31305087
drwxr-x---. 4 oracle oinstall     48 Jul 10 05:20 31335188
drwxr-x---. 2 oracle oinstall   4096 Jul 10 05:18 automation
-rw-rw-r--. 1 oracle oinstall   5054 Jul 10 05:46 bundle.xml
-rw-rw-r--. 1 oracle oinstall 120878 Jul 20 19:04 README.html
-rw-r--r--. 1 oracle oinstall      0 Jul 10 05:17 README.txt

[oracle@ol7-112-dg1 db_1]$ pwd
/u01/app/oracle/product/19.3.0.0/db_1

[oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339
Preparing the home to patch…
Applying the patch /home/oracle/patch/31305339…
OPatch command failed while applying the patch. For details look at the logs from /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto/.
[oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto
-bash: cd: /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/opatchauto: No such file or directory
[oracle@ol7-112-dg1 db_1]$ cd /u01/app/oracle/product/19.3.0.0/db_1/cfgtoollogs/
[oracle@ol7-112-dg1 cfgtoollogs]$ ll
total 4
drwxrwx---. 2 oracle oinstall 4096 Oct 17 16:44 oui
[oracle@ol7-112-dg1 cfgtoollogs]$

[oracle@ol7-112-dg1 db_1]$ ./runInstaller -debug -applyRU /home/oracle/patch/31305339

ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home. Use a different home to proceed.

[oracle@ol7-112-dg1 db_1]$


Executing "gridSetup.sh" Fails with "ERROR: The home is not clean"(Doc ID 2279633.1)
Fails with "ERROR: The home is not clean" .

[root]# rm-Rf 
[oracle]$ unzip linuxx64_12201_grid_home.zip -d 

Next Page »

Create a free website or blog at WordPress.com.