Michael Dinh
Systemd Persistent Logging
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.
Systemd Database GoldenGate Service Start Stop
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 ~]#Gather Oracle ExaData Cloud Service (ExaCS) Region/AD From Hostname
Here are all the ExaData hosts without the instance# as it does not make a difference in the results.
### cn is Client Name abbreviation.
cnexaashad2x8na-xxxxx
cnexaashad3x8na-xxxxx
cnexafraad2x8m-xxxxx-yyyyy
cnexalonad2x8m-xxxxx-yyyyy
cnexaphxad2x8m-xxxxx-yyyyy
cnexasydad1x8m-xxxxx-yyyyy
### Shell Script to extract info:
kucubiwopeto@pc-3fbecf:~$ cat exacs_name.sh
StringVal="cnexaashad2x8na-xxxxx cnexaashad3x8na-xxxxx cnexafraad2x8m-xxxxx-yyyyy cnexalonad2x8m-xxxxx-yyyyy cnexaphxad2x8m-xxxxx-yyyyy cnexasydad1x8m-xxxxx-yyyyy"
for val in $StringVal; do
cloud_service=${val:2:3}
region=${val:5:3}
availability_domain=${val:8:3}
echo $cloud_service-$region-$availability_domain
done
exit
kucubiwopeto@pc-3fbecf:~$
### Results:
kucubiwopeto@pc-3fbecf:~$ ./exacs_name.sh
exa-ash-ad2
exa-ash-ad3
exa-fra-ad2
exa-lon-ad2
exa-phx-ad2
exa-syd-ad1
kucubiwopeto@pc-3fbecf:~$Default SSH keys Algorithm
I encountered a peculiar incindent for not being able to connect to host after installing Git Bash latest version.
Basically old Git version 2.32.0.windows.1 SSH key default is RSA while new Git version 2.35.1.windows.2 SSH key default is ED25519.
One option is to restore to previous Git version (which I opted).
Alternatively, specify -oHostKeyAlgorithms when using new Git Version.
================================================
--- Find Windows OS
================================================
C:\Users\pucaximibepu>systeminfo | findstr OS
OS Name: Microsoft Windows Server 2019 Datacenter
OS Version: 10.0.17763 N/A Build 17763
OS Manufacturer: Microsoft Corporation
OS Configuration: Member Server
OS Build Type: Multiprocessor Free
BIOS Version: Amazon EC2 1.0, 10/16/2017
C:\Users\pucaximibepu>
==================================================
--- git version 2.35.1.windows.2 (default ed25519)
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ git --version
git version 2.35.1.windows.2
==================================================
--- Connect using ssh FAILED
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ ssh p_mdinh@172.22.37.10
Unable to negotiate with 172.22.37.10 port 22: no matching host key type found. Their offer: ssh-rsa,ssh-dss
==================================================
--- Specify -oHostKeyAlgorithms ssh-dss SUCCEEDED
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ ssh -oHostKeyAlgorithms=+ssh-dss p_mdinh@172.22.37.10
(p_mdinh@172.22.37.10)
**********************************************************
**** ATTENTION ** ATTENTION ** ATTENTION ** ATTENTION ****
**********************************************************
Use SecureAuth 2FA to login - RSA tokens no longer work.
Enter your RSD password (your computer/mail password)
and NOT your Linux password.
Password:
==================================================
--- Specify -oHostKeyAlgorithms ssh-rsa FAILED
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ ssh -oHostKeyAlgorithms=+ssh-rsa p_mdinh@172.22.37.10
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
SHA256:++6geRg12kREpx3l5ZjYKm+EhiDuG5nfsFOkavD6PUI.
Please contact your system administrator.
Add correct host key in /c/Users/pucaximibepu/.ssh/known_hosts to get rid of this message.
Offending DSA key in /c/Users/pucaximibepu/.ssh/known_hosts:2
Host key for 172.22.37.10 has changed and you have requested strict checking.
Host key verification failed.
pucaximibepu@tank-8753 MINGW64 ~
$
==================================================
--- Remove known_hosts
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ cd .ssh
pucaximibepu@tank-8753 MINGW64 ~/.ssh
$ ls -l
total 4
-rw-r--r-- 1 pucaximibepu 1049089 1214 Mar 26 16:25 known_hosts
pucaximibepu@tank-8753 MINGW64 ~/.ssh
$ rm known_hosts
==================================================
--- Specify -oHostKeyAlgorithms ssh-rsa SUCCEEDED
==================================================
pucaximibepu@tank-8753 MINGW64 ~/.ssh
$ ssh -oHostKeyAlgorithms=+ssh-rsa p_mdinh@172.22.37.10
The authenticity of host '172.22.37.10 (172.22.37.10)' can't be established.
RSA key fingerprint is SHA256:++6geRg12kREpx3l5ZjYKm+EhiDuG5nfsFOkavD6PUI.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '172.22.37.10' (RSA) to the list of known hosts.
(p_mdinh@172.22.37.10)
**********************************************************
**** ATTENTION ** ATTENTION ** ATTENTION ** ATTENTION ****
**********************************************************
Use SecureAuth 2FA to login - RSA tokens no longer work.
Enter your RSD password (your computer/mail password)
and NOT your Linux password.
Password:
==================================================
--- git version 2.32.0.windows.1 (default RSA)
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ git --version
git version 2.32.0.windows.1
==================================================
--- ssh SUCCEEDED without -oHostKeyAlgorithms
==================================================
pucaximibepu@tank-8753 MINGW64 ~
$ ssh p_mdinh@172.22.37.10
(p_mdinh@172.22.37.10)
**********************************************************
**** ATTENTION ** ATTENTION ** ATTENTION ** ATTENTION ****
**********************************************************
Use SecureAuth 2FA to login - RSA tokens no longer work.
Enter your RSD password (your computer/mail password)
and NOT your Linux password.
Password:
Validating RMAN Backup For Restore
Backup is only good if it can be used to restore.
Lately, I have been performing a lot of RMAN backup and validation.
In summary:
Restore validate completed in 0:24:17 (h:m:s)
comprising of 39 ARCH, 1 LEVEL0, 3 LEVEL1, and 2 TAG20220302T121110 (control files backup).
--- The only reason I am providing host info is because grep -A does not work!
Host: AIX dbhost01 1 7 00C7DE504B00
--- RMAN restore script:
restore_validate.rman
spool log to restore_validate.log
set echo on
connect target;
show all;
restore spfile validate;
restore controlfile validate;
restore database until time "SYSDATE" check logical validate;
restore archivelog from time "SYSDATE-1" check logical validate;
report schema;
exit
--- RMAN configuration:
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Mar 2 15:59:34 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: BANANA (DBID=2937483440)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name BANANA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so,SBT_PARMS=(CONFIG_FILE=/home/oracle/idpa_ddbea.config)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/11.2.0/11.2.0.4/dbs/snapcf_BANANA.f'; # default
RMAN>
--- Run RMAN restore_validate:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ export NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1 &
[1] 2359590
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ jobs
[1]+ Running nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1 &
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $
[1]+ Done nohup rman @restore_validate.rman > /tmp/restore_validate.rman_$ORACLE_SID.log 2>&1
--- Check policy:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -i "policy" restore_validate.log
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
--- Check restore timing:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep "restore at" restore_validate.log
Starting restore at 2022-MAR-02 14:57:58
Finished restore at 2022-MAR-02 14:58:01
Starting restore at 2022-MAR-02 14:58:01
Finished restore at 2022-MAR-02 14:58:04
Starting restore at 2022-MAR-02 14:58:04
Finished restore at 2022-MAR-02 15:21:11
Starting restore at 2022-MAR-02 15:21:11
Finished restore at 2022-MAR-02 15:22:15
--- Check number of backup piece:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c "piece handle" restore_validate.log
45
--- Backup tag=LEVEL0, tag=LEVEL1, tag=ARCH
--- Check number of backup piece type:
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ awk -F 'tag=' '{print $2}' restore_validate.log|sort|uniq -c
275
39 ARCH
1 LEVEL0
3 LEVEL1
2 TAG20220302T121110
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c TAG20220302T121110 restore_validate.log
2
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'LEVEL0$' restore_validate.log
1
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'LEVEL1$' restore_validate.log
3
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ grep -c 'ARCH$' restore_validate.log
39
--- If not using end with ('ARCH$'), will have incorrect results.
grep 'ARCH' restore_validate.log|head
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
--- This is a replacement for grep -A which is not available for AIX.
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $ awk 'c-->0;$0~s{if(b)for(c=b+1;c>1;c--)print r[(NR-c+1)%b];print;c=a}b{r[NR%b]=$0}' b=0 a=30 s="schema for database with db_unique_name" restore_validate.log
Report of database schema for database with db_unique_name BANANA
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 2565 SYSTEM *** /oradata/BANANA/datafile/system_01.dbf
2 4667 SYSAUX *** /oradata/BANANA/datafile/sysaux_01.dbf
3 1300 UNDOTBS1 *** /oradata/BANANA/datafile/undotbs1_01.dbf
4 50 EMSPROD_TS *** /oradata/BANANA/datafile/emsprod_ts_01.dbf
5 1650 MODPROD_TS *** /oradata/BANANA/datafile/modprod_ts_01.dbf
6 2039 AVAIL *** /oradata/BANANA/datafile/avail_01.dbf
7 32767 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_05.dbf
8 2548 AUDIT_TBS *** /oradata/BANANA/datafile/audit_tbs_01.dbf
9 512 USERS *** /oradata/BANANA/datafile/users_01.dbf
10 30720 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_04.dbf
11 30720 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_03.dbf
12 32767 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_02.dbf
13 30720 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_01.dbf
14 25536 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_06.dbf
15 25472 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_07.dbf
16 25920 PROD001_TS *** /oradata/BANANA/datafile/PROD001_ts_08.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
3 4807 TEMP 30720 /oradata/BANANA/datafile/temp_01.db
Recovery Manager complete.
oracle@dbhost01 ~/working/dinh/rman_restore (BANANA) $DataGuard : ORA-16809: multiple warnings detected – Solution
This is the solution for the issues identified from post above.
--- Use SYS password vs OS authenitication.
--- Note this is RAC.
$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 15 11:55:28 2022
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@EAGLE
Password:
Connected to "EAGLE"
Connected as SYSDBA.
DGMGRL> show configuration
Configuration - EAGLE
Protection Mode: MaxPerformance
Members:
EAGLE - Primary database
EAGLE_PHO - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 49 seconds ago)
DGMGRL> validate network configuration for all;
Connecting to instance "EAGLE1" on database "EAGLE" ...
Connected to "EAGLE"
Checking connectivity from instance "EAGLE1" on database "EAGLE to instance "EAGLE1" on database "EAGLE_PHO"...
Succeeded.
Checking connectivity from instance "EAGLE1" on database "EAGLE to instance "EAGLE2" on database "EAGLE_PHO"...
Succeeded.
Connecting to instance "EAGLE2" on database "EAGLE" ...
Connected to "EAGLE"
Checking connectivity from instance "EAGLE2" on database "EAGLE to instance "EAGLE1" on database "EAGLE_PHO"...
Succeeded.
Checking connectivity from instance "EAGLE2" on database "EAGLE to instance "EAGLE2" on database "EAGLE_PHO"...
Succeeded.
Connecting to instance "EAGLE1" on database "EAGLE_PHO" ...
Connected to "EAGLE_PHO"
Checking connectivity from instance "EAGLE1" on database "EAGLE_PHO to instance "EAGLE1" on database "EAGLE"...
Succeeded.
Checking connectivity from instance "EAGLE1" on database "EAGLE_PHO to instance "EAGLE2" on database "EAGLE"...
Succeeded.
Connecting to instance "EAGLE2" on database "EAGLE_PHO" ...
Connected to "EAGLE_PHO"
Checking connectivity from instance "EAGLE2" on database "EAGLE_PHO to instance "EAGLE1" on database "EAGLE"...
Succeeded.
Checking connectivity from instance "EAGLE2" on database "EAGLE_PHO to instance "EAGLE2" on database "EAGLE"...
Succeeded.
Oracle Clusterware on database "EAGLE" is available for database restart.
Oracle Clusterware on database "EAGLE_PHO" is available for database restart.
DGMGRL> validate static connect identifier for all;
Oracle Clusterware on database "EAGLE" is available for database restart.
Oracle Clusterware on database "EAGLE_PHO" is available for database restart.
DGMGRL> exit
$DataGuard : ORA-16809: multiple warnings detected
EM13c Daily Report was report contained the errors as shown below:
The Data Guard status of EAGLE_PHO is
Warning ORA-16809: multiple warnings detected for the member.
Started to investigate as shown below and what do you think is wrong?
Will give you a chance to guess and post solution later.
$ oerr ora 16809
16809, 00000, "multiple warnings detected for the member"
// *Cause: The broker detected multiple warnings for the member.
// *Action: To get a detailed status report, check the status of the member
// specified using either Enterprise Manager or the DGMGRL CLI SHOW
// command.
$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Feb 15 11:33:54 2022
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "EAGLE"
Connected as SYSDG.
DGMGRL> show configuration
Configuration - EAGLE
Protection Mode: MaxPerformance
Members:
EAGLE - Primary database
EAGLE_PHO - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 29 seconds ago)
DGMGRL> validate network configuration for all;
Connecting to instance "EAGLE1" on database "EAGLE" ...
ORA-01017: invalid username/password; logon denied
Warning: Cannot connect to instance "EAGLE1" on database "EAGLE".
Connecting to instance "EAGLE2" on database "EAGLE" ...
ORA-01017: invalid username/password; logon denied
Warning: Cannot connect to instance "EAGLE2" on database "EAGLE".
Connecting to instance "EAGLE2" on database "EAGLE_PHO" ...
ORA-01017: invalid username/password; logon denied
Warning: Cannot connect to instance "EAGLE2" on database "EAGLE_PHO".
Connecting to instance "EAGLE1" on database "EAGLE_PHO" ...
ORA-01017: invalid username/password; logon denied
Warning: Cannot connect to instance "EAGLE1" on database "EAGLE_PHO".
Oracle Clusterware on database "EAGLE" is available for database restart.
Oracle Clusterware on database "EAGLE_PHO" is available for database restart.
DGMGRL> validate static connect identifier for all;
Oracle Clusterware on database "EAGLE" is available for database restart.
Oracle Clusterware on database "EAGLE_PHO" is available for database restart.
DGMGRL> exitSilent Install Oracle DB 19.3
I was asked to Install Oracle 19c EE with the provided information:
DB: /app/oracle/product/19.3.0
SW: /backup/media/oracle19.3_database/LINUX.X64_193000_db_home.zipNot knowing anything about the environment, more due diligence is required and here are the steps taken:
--- Find OS info:
oracle@oradev001: ~ $ uname -an
Linux oradev001 3.10.0-1160.31.1.el7.x86_64 #1 SMP Wed May 26 20:18:08 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
--- Create new directory:
oracle@oradev001: ~ $ ls -ld /app/oracle/product/19.3.0
ls: cannot access /app/oracle/product/19.3.0: No such file or directory
oracle@oradev001: ~ $ mkdir -p /app/oracle/product/19.3.0
oracle@oradev001: ~ $ ls -ld /app/oracle/product/19.3.0
drwxr-xr-x 2 oracle dba 4096 Jan 26 12:44 /app/oracle/product/19.3.0
--- Extract software:
oracle@oradev001: ~ $ cd /app/oracle/product/19.3.0
oracle@oradev001: /app/oracle/product/19.3.0 $ unzip -oq /backup/media/oracle19.3_database/LINUX.X64_193000_db_home.zip; echo $?
0
--- Extract rspfmt_dbinstall_response_schema_v19.0.0 from db_install.rsp:
oracle@oradev001: /app/oracle/product/19.3.0 $ ls -l install/response/db_install.rsp
-rw-r--r-- 1 oracle dba 19932 Feb 6 2019 install/response/db_install.rsp
oracle@oradev001: /app/oracle/product/19.3.0 $ grep responseFileVersion install/response/db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
--- Find UNIX_GROUP_NAME (oinstall or dba):
oracle@oradev001: /app/oracle/product/19.3.0 $ id oracle
uid=3000(oracle) gid=3000(dba) groups=3000(dba),54329(asmadmin),54327(asmdba)
--- Find ORACLE_BASE:
oracle@oradev001: /app/oracle/product/19.3.0 $ env|grep ORACLE_BASE
ORACLE_BASE=/app/oracle
--- Find INVENTORY_LOCATION:
oracle@oradev001: /app/oracle/product/19.3.0 $ ls -l /etc/oraInst.loc
-rw-r--r-- 1 root root 47 Nov 7 2018 /etc/oraInst.loc
--- Create and copy response file to /tmp:
oracle@oradev001: /app/oracle/product/19.3.0 $ cat db_install.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/etc/oraInst.loc
SELECTED_LANGUAGES=en
ORACLE_HOME=/app/oracle/product/19.3.0
ORACLE_BASE=/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
--- Perform executePrereqs and Ignore:
oracle@oradev001: /app/oracle/product/19.3.0 $ ./runInstaller -silent -executePrereqs -waitforcompletion -force -responseFile /tmp/db_install.rsp
Launching Oracle Database Setup Wizard...
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-19-52PM/installActions2022-01-26_01-19-52PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-19-52PM/installActions2022-01-26_01-19-52PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
--- Install software only;
oracle@oradev001: /app/oracle/product/19.3.0 $ ./runInstaller -silent -waitforcompletion -responseFile /tmp/db_install.rsp
Launching Oracle Database Setup Wizard...
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
/app/oracle/product/19.3.0/install/response/db_2022-01-26_01-28-45PM.rsp
You can find the log of this install session at:
/app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
As a root user, execute the following script(s):
1. /app/oracle/product/19.3.0/root.sh
Execute /app/oracle/product/19.3.0/root.sh on the following nodes:
[oradev001]
Successfully Setup Software with warning(s).
oracle@oradev001: /app/oracle/product/19.3.0 $
--- Review ignorable errors: insufficient swap and OS kernel parameter "shmall"
oracle@oradev001: /app/oracle/product/19.3.0 $ grep -C 10 INS-13014 /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
INFO: [Jan 26, 2022 1:29:03 PM] ------------------List of failed Tasks------------------
INFO: [Jan 26, 2022 1:29:03 PM] *********************************************
INFO: [Jan 26, 2022 1:29:03 PM] Swap Size: This is a prerequisite condition to test whether sufficient total swap space is available on the system.
INFO: [Jan 26, 2022 1:29:03 PM] Severity:IGNORABLE
INFO: [Jan 26, 2022 1:29:03 PM] OverallStatus:VERIFICATION_FAILED
INFO: [Jan 26, 2022 1:29:03 PM] *********************************************
INFO: [Jan 26, 2022 1:29:03 PM] OS Kernel Parameter: shmall: This is a prerequisite condition to test whether the OS kernel parameter "shmall" is properly set.
INFO: [Jan 26, 2022 1:29:03 PM] Severity:IGNORABLE
INFO: [Jan 26, 2022 1:29:03 PM] OverallStatus:VERIFICATION_FAILED
INFO: [Jan 26, 2022 1:29:03 PM] -----------------End of failed Tasks List----------------
WARNING: [Jan 26, 2022 1:29:03 PM] [WARNING] [INS-13014] Target environment does not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /app/oraInventory/logs/InstallActions2022-01-26_01-28-45PM/installActions2022-01-26_01-28-45PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
INFO: [Jan 26, 2022 1:29:03 PM] Advice is CONTINUE
INFO: [Jan 26, 2022 1:29:03 PM] Completed validating state <performChecks>
INFO: [Jan 26, 2022 1:29:03 PM] Verifying route success
INFO: [Jan 26, 2022 1:29:03 PM] Executing initialize action at state showSummary
INFO: [Jan 26, 2022 1:29:03 PM] Completed executing initialize action at state <showSummary>
INFO: [Jan 26, 2022 1:29:03 PM] Waiting for completion of background operations
INFO: [Jan 26, 2022 1:29:03 PM] Completed background operations
INFO: [Jan 26, 2022 1:29:03 PM] Waiting for completion of background operations
oracle@oradev001: /app/oracle/product/19.3.0 $SIMPLIFY A COMPLICATED PROCESS USING SED
For every PDB, there is a perl script used to report tablespace free the that PDB.
While I am not able to change how the process was implemented, I can make it easier.
Here is the current process.
Edit the script "tablespace_free_PDB_NAME.pl".
Change the PDB name at the below SQL command:
"alter session set container=<Your PDB name_1>"
Rename the script to match your PDB name
like "tablespace_free_<Your PDB name>.pl".
The above instructions work but is time consuming, not scalable, and error prone.Here’s a demo how to simplify the process.
1. Create template temp_tablespace_free_PDB.pl:
[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;
2. Export variable PDB with <Your PDB name>
[oracle@ol7-19-dg1 ~]$ export PDB=SOAP
3. Create tablespace_free_<Your PDB name>.pl
[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
ls: cannot access tablespace_free_SOAP.pl: No such file or directory
[oracle@ol7-19-dg1 ~]$ sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl
[oracle@ol7-19-dg1 ~]$ ls tablespace_free_$PDB.pl
tablespace_free_SOAP.pl
[oracle@ol7-19-dg1 ~]$
[oracle@ol7-19-dg1 ~]$ cat tablespace_free_$PDB.pl
alter session set container=SOAP;
[oracle@ol7-19-dg1 ~]$The above solution is better but far from perfect.
If there are a dozen PDBs to implement, then manual work will have to be done a dozen time.
Here is an example using array and for loop.
There are 2 PDBs: SOAP and SCUM.
Here is the template:
[oracle@ol7-19-dg1 ~]$ cat temp_tablespace_free_PDB.pl
alter session set container=vPDB;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$
1. Create script to loop through list of PDBs:
[oracle@ol7-19-dg1 ~]$ cat create_tablespace_free_PDB.sh
#!/bin/bash
array=( SOAP SCUM )
for i in "${array[@]}"
do
echo "$i"
export PDB=$i
sed "s/vPDB/$PDB/g" temp_tablespace_free_PDB.pl > tablespace_free_$PDB.pl
ls -l tablespace_free_$PDB.pl
done
exit
[oracle@ol7-19-dg1 ~]$
2. Run create_tablespace_free_PDB.sh:
[oracle@ol7-19-dg1 ~]$ ./create_tablespace_free_PDB.sh
SOAP
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SOAP.pl
SCUM
-rw-r--r--. 1 oracle oinstall 60 Jan 22 03:52 tablespace_free_SCUM.pl
[oracle@ol7-19-dg1 ~]$
3. Review results:
[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SOAP.pl
alter session set container=SOAP;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$
[oracle@ol7-19-dg1 ~]$ cat tablespace_free_SCUM.pl
alter session set container=SCUM;
select sysdate from dual;
[oracle@ol7-19-dg1 ~]$COMPLICATED COMMINGLED DATABASE ENVIRONMENT
I have been reviewing RMAN RAC backup for environment having a total of 15 non-production and production databases on the same host excluding APX and MGMTDB.
That’s not a big deal, as I have once had to managed 28 databases residing on the same host, right?
I am just too lazy and too tedious to change RMAN configuration one database at a time.
Luckily, there is a convention where non-production instances ends with T1 and production instances ends with P1.
This allows me to make the same changes to non-production and production in 2 steps.
Goal is to configure RMAN PARALLELISM 2 for NON-PROD and PARALLELISM 4 for PROD and consistent RECOVERY WINDOW OF 14 DAYS.
### Current configuration is inconsistent across databases:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
====================
### NON-PROD:
====================
--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
show all;
exit
--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort
DB01T1
DB02T1
DB03T1
--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -Ev '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;
====================
### PROD:
====================
--- RMAN commands: cat configure.rman:
set echo on
connect target;
show all;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 4;
show all;
exit
--- Let's make sure the instances are correct:
$ ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort
DB01P1
DB02P1
DB03P1
--- Make the change:
$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|grep -E '\P1'|sort); do echo 'RMAN configure' $db; . oraenv <<< $db; rman @configure.rman; done;HOW TO LOAD BALANCE RMAN RAC DATABASE BACKUP
First, I will share the incorrect method since it is hard coded.
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/passwd@inst1';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/passwd@inst2';The goal is to configure RMAN backup with parallel 4 and load balance.
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT 'sys/***@DB_UNIQUE_NAME';
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Jan 10 17:24:15 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB_NAME (DBID=453022715)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DB_UNIQUE_NAME are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backups/DB_NAME/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT '*';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DB_NAME_DATA/DB_UNIQUE_NAME/controlfile/snapcf_DB_NAME.f';
RMAN>It’s that easy. Changing parallelism will automatically load balance across all nodes.
Here is an example where parallelism is configured and backup is not load balance.
All the channels are allocated to node1.
[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201010300_Sat.log
channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK
channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK
channel ORA_DISK_1: SID=760 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=956 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1331 instance=HAWK1 device type=DISK
[oracle@host01 log]$Here is the correct way and let the database determine the node.
[oracle@host01 log]$ grep 'channel ORA_DISK_[1-9]: SID' backup_HAWK_level1_202201101400_Mon.log
channel ORA_DISK_1: SID=199 instance=HAWK2 device type=DISK
channel ORA_DISK_2: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_4: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK
channel ORA_DISK_1: SID=2469 instance=HAWK1 device type=DISK
channel ORA_DISK_2: SID=196 instance=HAWK1 device type=DISK
channel ORA_DISK_3: SID=1139 instance=HAWK2 device type=DISK
channel ORA_DISK_4: SID=199 instance=HAWK2 device type=DISK
[oracle@host01 log]$Review RMAN Backup for All Databases On Host
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" */
exitOEM Log4j Vulnerability
Surprise that Oracle did not automate the solution vs manual work.
Test case below is for EM 13.5 only.
Hopefully, I did it right and would be nice to have some sort of validations.
Security Alert For CVE-2021-44228 & CVE-2021-45046 Patch Availability Document for Oracle Enterprise Manager Cloud Control (Doc ID 2828296.1)
Applies to Oracle Enterprise Manager 13.5 & 13.4 and underlying Oracle Fusion Middleware 12.2.1.4 and 12.2.1.3 products using Log4j 2.X jars
Components impacted with Log4j version 2 jars based on EM version
EM 13.5
FMW Component on OMS Home
DB Plugin Home
FMW Component on Agent Home
=====================================================
### Patch/Mitigate FMW component on OMS Home EM 13.5
=====================================================
Note : Perform these steps on all OMS homes in case of Multi OMS setup
find /u01/app/oracle/middleware -name setupinfo.txt
find /u01/app/oracle/middleware -name portlist.ini
--- Find FMW from ORACLE_BASE
[oracle@ol7-em135 ~]$ find /u01/app/oracle -name middleware
/u01/app/oracle/middleware
[oracle@ol7-em135 ~]$
--- Navigate to location
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/middleware/oracle_common/modules/thirdparty/
--- Run the below command
[oracle@ol7-em135 thirdparty]$ zip -q -d log4j-2.11.1.jar org/apache/logging/log4j/core/lookup/JndiLookup.class
--- Verify removal of class on the LOG4J core jar
[oracle@ol7-em135 thirdparty]$ unzip -l log4j-2.11.1.jar | grep JndiLookup.class
[oracle@ol7-em135 thirdparty]$
--- Restart OMS server
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/middleware/bin/emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
[oracle@ol7-em135 thirdparty]$
==================================================
### Patch/Mitigate Agent Home
==================================================
Note: These Steps have to be performed on each agent home
--- Find Agent Binaries
[oracle@ol7-em135 ~]$ ps -ef|grep [a]gent_inst
oracle 32531 1 0 22:53 pts/0 00:00:00 /u01/app/oracle/agent/agent_13.5.0.0.0/perl/bin/perl /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emwd.pl agent /u01/app/oracle/agent/agent_inst/sysman/log/emagent.nohup
[oracle@ol7-em135 ~]$
--- Navigate to location
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/agent/agent_13.5.0.0.0/oracle_common/modules/thirdparty/
[oracle@ol7-em135 thirdparty]$
--- Run the below command
[oracle@ol7-em135 thirdparty]$ zip -q -d log4j-2.11.1.jar org/apache/logging/log4j/core/lookup/JndiLookup.class
[oracle@ol7-em135 thirdparty]$
--- Verify removal of class on the LOG4J core jar
[oracle@ol7-em135 thirdparty]$ unzip -l log4j-2.11.1.jar | grep JndiLookup.class
[oracle@ol7-em135 thirdparty]$
--- Restart the Agent
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Starting agent .............. started.
[oracle@ol7-em135 thirdparty]$ /u01/app/oracle/agent/agent_13.5.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 13.5.0.0.0
OMS Version : 13.5.0.0.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/agent/agent_inst
Agent Log Directory : /u01/app/oracle/agent/agent_inst/sysman/log
Agent Binaries : /u01/app/oracle/agent/agent_13.5.0.0.0
Core JAR Location : /u01/app/oracle/agent/agent_13.5.0.0.0/jlib
Agent Process ID : 12927
Parent Process ID : 12873
Agent URL : https://ol7-em135.localdomain:3872/emd/main/
Local Agent URL in NAT : https://ol7-em135.localdomain:3872/emd/main/
Repository URL : https://ol7-em135.localdomain:4903/empbs/upload
Started at : 2021-12-16 23:34:05
Started by user : oracle
Operating System : Linux version 5.4.17-2136.300.7.el7uek.x86_64 (amd64)
Number of Targets : 35
Last Reload : (none)
Last successful upload : 2021-12-16 23:34:18
Last attempted upload : 2021-12-16 23:34:18
Total Megabytes of XML files uploaded so far : 0.02
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 55.66%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2021-12-16 23:34:11
Last successful heartbeat to OMS : 2021-12-16 23:34:11
Next scheduled heartbeat to OMS : 2021-12-16 23:35:13
---------------------------------------------------------------
Agent is Running and Ready
[oracle@ol7-em135 thirdparty]$
--- NOTE: thirdparty does not exists at agent_inst
[vagrant@ol7-em135 ~]$ cd /u01/app/oracle/agent/agent_inst
[vagrant@ol7-em135 agent_inst]$ ls
bin diag install internal oracle-dfw-0.tmp sysman
[vagrant@ol7-em135 agent_inst]$
==================================================
### Patch/Mitigate DB Plug-in Home
==================================================
--- Find gc_inst
[oracle@ol7-em135 ~]$ find /u01/app/oracle -name gc_inst
/u01/app/oracle/gc_inst
[oracle@ol7-em135 ~]$ cd /u01/app/oracle/gc_inst
--- Locate log4j*.jar on your system using the following command
[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -print
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar
./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar
[oracle@ol7-em135 gc_inst]$
--- To identify the Log4j version use the below command
--- (Ensure the log 4j version is indeed 2.8.2 )
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-core-2.8.2.jar META-INF/MANIFEST.MF
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-core-2.8.2.jar META-INF/MANIFEST.MF
[oracle@ol7-em135 gc_inst]$ unzip -p log4j-web-2.8.2.jar META-INF/MANIFEST.MF
--- Delete the following files
[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -exec ls -l {} \;
-rw-r-----. 1 oracle oinstall 228154 May 4 2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar
-rw-r-----. 1 oracle oinstall 1407853 May 4 2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar
-rw-r-----. 1 oracle oinstall 32684 May 4 2020 ./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar
[oracle@ol7-em135 gc_inst]$
[oracle@ol7-em135 gc_inst]$ find . -name log4j*2.8.2*.jar -exec rm -fv {} \;
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-api-2.8.2.jar’
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-core-2.8.2.jar’
removed ‘./user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/_WL_user/emdb/1danf1/database/jet/emsaasui/emcdbms-ui/ear/APP-INF/lib/log4j-web-2.8.2.jar’
[oracle@ol7-em135 gc_inst]$Find Statistics and SQL Using Index
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>How To Resolve Tablespace Created With Windows Path In Linux
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';Flashback DataGuard Primary Database
Practicing flashback just in case it is needed during production deployment.
It’s recommended to use DGMGRL where DataGuard Broker is used.
The disadvantage is commands will be different for each environment vs simple and consistent
recover managed standby database cancel and
recover managed standby database using current logfile disconnect
--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1056941 HAWK READ WRITE PRIMARY YES
SQL>
-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;
### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1037296 HAWK MOUNTED PHYSICAL STANDBY YES
SQL>
-- Enable flashback if required:
recover managed standby database cancel;
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;
--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------
### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;
Restore point created.
SQL>
--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------
Depending on database version, GRP is replicated to standby.
### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SCN NAME GUA
---------- ------------------------------ ---
1057112 RP_TEST YES
SQL>
### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
SQL>
##################################################
### FLASHBACK TO RESTORE POINT:
##################################################
### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1057767 HAWK READ WRITE PRIMARY YES
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 469765280 bytes
Database Buffers 1124073472 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SCN NAME GUA
---------- ------------------------------ ---
1057112 RP_TEST YES
SQL> flashback database to restore point RP_TEST;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old 1: flashback standby database to scn &scn
new 1: flashback standby database to scn 1057112
Flashback complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>
--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
hawk - Primary database
hawk_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database hawk
Database - hawk
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 12 seconds ago)
Apply Lag: 0 seconds (computed 12 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
Q.E.D.AHFCTL setresourcelimit CPU
It was detemrined that AHF was using too much CPU affecting RAC cluster performance.
AHF was shutdown temporarily over the weekend to prevent issues with application.
--- SHORT version to limit CPU resource:
# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully
# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set--- LONG version - start AHF and limit CPU resource:
# /opt/oracle.ahf/bin/ahfctl startahf -all
Starting TFA..
Waiting up to 100 seconds for TFA to be started..
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands
INFO: Starting exachk scheduler in background.
Details for the process can be found at /u01/app/grid/oracle.ahf/data/xxxxxxxxxxxxxx-21acd-9gu5j2/diag/exachk/compliance_start_251021_112642.log
# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully
# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set
# /opt/oracle.ahf/bin/ahfctl set autodiagcollect=ON
Successfully set autodiagcollect=ON
.-------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+-----------------------------------------+-------+
| Configuration Parameter | Value |
+-----------------------------------------+-------+
| Auto Diagcollection ( autodiagcollect ) | ON |
'-----------------------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl set chaautocollect=ON
Successfully set chaautocollect=ON
.---------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+-------------------------+-------+
| Configuration Parameter | Value |
+-------------------------+-------+
| chaautocollect | ON |
'-------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl set minicollection=ON
Successfully set minicollection=ON
.-----------------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+---------------------------------------------------+-------+
| Configuration Parameter | Value |
+---------------------------------------------------+-------+
| Generation of Mini Collections ( minicollection ) | ON |
'---------------------------------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl statusahf
.--------------------------------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+-----------------------------+---------------+--------+------+------------+----------------------+------------------+
| xxxxxxxxxxxxxx-21acd-9gu5j2 | RUNNING | 151372 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE |
| xxxxxxxxxxxxxx-21acd-9gu5j1 | RUNNING | 89572 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE |
'-----------------------------+---------------+--------+------+------------+----------------------+------------------'
------------------------------------------------------------
Master node = xxxxxxxxxxxxxx-21acd-9gu5j1
exachk daemon version = 21.1.4
Install location = /opt/oracle.ahf/exachk
Started at = Mon Oct 25 11:27:10 CDT 2021
Scheduler type = TFA Scheduler
------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
AUTORUN_FLAGS = -usediscovery -profile exatier1 -syslog -dball -showpass -tag autostart_client_exatier1 -readenvconfig
COLLECTION_RETENTION = 7
AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
AUTORUN_FLAGS = -usediscovery -syslog -tag autostart_client -readenvconfig
COLLECTION_RETENTION = 14
AUTORUN_SCHEDULE = 3 3 * * 0
------------------------------------------------------------
Next auto run starts on Oct 26, 2021 02:03:00
ID:exachk.AUTOSTART_CLIENT_EXATIER1
# /opt/oracle.ahf/bin/tfactl get collect -match
.------------------------------------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+----------------------------------------------------------------------+-------+
| Configuration Parameter | Value |
+----------------------------------------------------------------------+-------+
| ISA Data Gathering ( collection.isa ) | ON |
| collectTrm | OFF |
| collectAllDirsByFile | ON |
| Auto Diagcollection ( autodiagcollect ) | ON |
| Generation of Mini Collections ( minicollection ) | ON |
| chaautocollect | ON |
| Maximum File Collection Size (MB) ( maxFileCollectionSize ) | 5120 |
| Maximum Collection Size of Core Files (MB) ( maxCoreCollectionSize ) | 500 |
| minTimeForAutoDiagCollection | 300 |
'----------------------------------------------------------------------+-------'Recover Dropped User Using Standby Database
A critical user was accidentally dropped around one week ago and will need be recovered.
Here are semi detailed steps used to flashback standby database, export user from standby, and import to primary.
============================================================
### Recover user that was deleted about week ago.
============================================================
Request Created: 14/Oct/2021 6:41 AM
DB version: 12.1.0.2.0
==============================
### CURRENT FLASHBACK INFO:
==============================
SQL>
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
----- -------------- --------- ----------------
ORC1 STANDBY1 MOUNTED PHYSICAL STANDBY
INST OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_MB EST_FLASHBACK_MB
----- -------------------- --------------------- ---------------- ------------ ----------------
1 984681951010 04-OCT-2021 18:44:28 7200 1767000 895861
CURRENT_SCN
------------
985044762265
SQL>
==============================
### STOP STANDBY APPLY:
==============================
DGMGRL> edit database 'STANDBY1' set state='APPLY-OFF';
Succeeded.
DGMGRL>
========================================
### FLASHBACK USING TIMESTAMP FAILED:
========================================
SQL> shutdown abort;
SQL> startup mount restrict exclusive;
SQL> flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL>
========================================
### FLASHBACK USING SCN:
to_timestamp can't convert to SCN properly
========================================
SQL> flashback database to scn 984681951011;
========================================
### FLASHBACK WILL EVENTUALLY FAIL:
========================================
SQL> flashback database to scn 984681951011;
flashback database to scn 984681951011
*
ERROR at line 1:
ORA-38861: flashback recovery stopped before reaching recovery target
SQL>
========================================
### CHECK ALERT LOG:
========================================
Thu Oct 14 13:59:52 2021
Errors in file /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/ORC1_pr00_12838.trc:
ORA-00283: recovery session canceled due to errors
ORA-38861: flashback recovery stopped before reaching recovery target
ORA-16016: archived log for thread 1 sequence# 477183 unavailable
ORA-38861: signalled during: flashback database to scn 984681951011...
========================================
### RESTORE ARCHIVELOG from alert log:
========================================
RMAN> restore archivelog logseq 477183;
========================================
### CONTINUE FLASHBACK:
========================================
SQL> flashback database to scn 984681951011;
========================================
### DO NOT OPEN READ ONLY
WITHOUT CANCELING REDO APPLY WITHOUT ADG.
========================================
Active Data Guard enables read-only access to a physical standby database while Redo Apply is active.
SQL> recover managed standby database cancel;
SQL> alter database open read only;
========================================
### CHECK FOR DROPPED USER:
========================================
SQL> select created, username from dba_users where username='XXX';
CREATED USERNAME
-------------------------- --------
2008-SEP-18 20:19:33 XXX
SQL>
========================================
### USING DATAPUMP DID NOT WORK:
========================================
How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1)
--- For physical standby
We can execute exp in physical standby database when it is in read only
https://dbaminds.wordpress.com/2016/01/07/perform-export-expdp-from-physical-standby-and-logical-standby/
--- Use Snapshot Standby - did not test.
https://dohdatabase.com/2021/04/22/datapump-export-from-data-guard/
========================================
### EXP WORKED: PERFORMED BY CLIENT
========================================
### From STANDBY:
$ cat exp.par
file=exp.dmp
compress=n
grants=y
indexes=y
direct=y
log=exp.log
rows=y
consistent=y
owner=schema
triggers=y
constraints=y
exp userid=system parfile=exp.par
scp exp.dmp oracle@target:/home/oracle/
### From PRIMARY:
$ cat imppar
file=exp.dmp
grants=y
indexes=y
rows=y
log=imp.log
fromuser=schema
commit=n
constraints=y
compile=y
imp userid=system parfile=imp.par
========================================
### RECOVER STANDBY TO CURRENT TIME:
========================================
SQL> recover managed standby database using current logfile parallel 4 disconnect;
Media recovery complete.
SQL>
========================================
### RESTORE ARCHIVELOG TO FRA IS BEING DELETED:
========================================
Example:
Thu Oct 14 18:02:30 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477816.752.1085939417
Thu Oct 14 18:02:31 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477794.1120.1085939341
========================================
### RESTORE ARCHIVELOG TO NEW DESTINATION:
========================================
mkdir -p /ubb1/rman/ORC1/archivelog
========================================
### CHECK ALERT LOG FOR GAP SEQUENCE:
========================================
grep 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
========================================
### RESOLVE ARCHIVE GAP:
========================================
Fetching gap sequence in thread 1, gap sequence 477604-477604
RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477604 until sequence 477604;}2> 3>
========================================
### FOR REFERENCE: 157 gap sequences
========================================
$ grep -c 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
157
========================================
### RECOVER ALL ARCHIVELOG TO SAVE TIME:
========================================
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
8723 1 1 LGWR RFS RECEIVING 483532 119582 0
29047 1 1 N/A MRP0 APPLYING_LOG 477715 1545345 0
SQL>
========================================
### RESTORE ARCHIVELOG UNTIL SEQUENCE 483515:
========================================
RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;}2> 3>
ALTERNATIVE:
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;
========================================
### ENABLE REDO APPLY USING DGMGRL:
========================================
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
DGMGRL> show configuration
Configuration - linkdg
Protection Mode: MaxPerformance
Members:
PRIMARY - Primary database
STANDBY1 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database
STANDBY2 - Physical standby database
SBY3 - Physical standby database
Warning: ORA-16532: Oracle Data Guard broker configuration does not exist
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 28 seconds ago)
DGMGRL> show database STANDBY1
Database - STANDBY1
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 minute 6 seconds (computed 0 seconds ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORC1
Database Status:
SUCCESS
DGMGRL> edit database 'STANDBY1' set state='APPLY-ON';
Succeeded.
DGMGRL> show database STANDBY1
Database - STANDBY1
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.01 MByte/s
Real Time Query: OFF
Instance(s):
ORC1
Database Status:
SUCCESS
DGMGRL> validate database STANDBY1;
Database Role: Physical standby database
Primary Database: PRIMARY
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
DGMGRL> show configuration
Configuration - linkdg
Protection Mode: MaxPerformance
Members:
PRIMARY - Primary database
STANDBY1 - Physical standby database
STANDBY2 - Physical standby database
SBY3 - Physical standby database
Warning: ORA-16532: Oracle Data Guard broker configuration does not exist
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 15 seconds ago)
DGMGRL>
Q.E.D.How To Change RMAN Config For Standby DB
Here is the typical error when changing RMAN configuration for standby database.
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> configure retention policy to recovery window of 7 days;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$There are suggestions found from MOS. Yuck!
Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)
Thanks to teammate for the easy method.
[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
HAWK PHYSICAL STANDBY
SQL> select * from v$rman_configuration;
no rows selected
SQL> set serveroutput on
SQL> !vi t.sql
SQL> @t.sql
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
5 dbms_output.put_line('setconfig returned ' || x);
6 END;
7 /
setconfig returned 1
PL/SQL procedure successfully completed.
SQL> select * from v$rman_configuration;
CONF# NAME
---------- -----------------------------------------------------------------
VALUE CON_ID
-------------------------------------------------- ----------
1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS 0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg2 ~]$
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$Find Tablespace Info Using RMAN report schema
A quick and dirty post. This also works for PDBs.
[oracle@ol7-112-dg1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:16 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1040 SYSTEM YES /u01/oradata/hawk/system01.dbf
2 890 SYSAUX NO /u01/oradata/hawk/sysaux01.dbf
3 1025 UNDOTBS1 YES /u01/oradata/hawk/undotbs01.dbf
4 10 USERS NO /u01/oradata/hawk/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 37 TEMP 32767 /u01/oradata/hawk/temp01.dbf
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:44 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter tablespace users add datafile size 1m;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@ol7-112-dg1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:41:23 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HAWK (DBID=3331620895)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1040 SYSTEM YES /u01/oradata/hawk/system01.dbf
2 890 SYSAUX NO /u01/oradata/hawk/sysaux01.dbf
3 1025 UNDOTBS1 YES /u01/oradata/hawk/undotbs01.dbf
4 10 USERS NO /u01/oradata/hawk/users01.dbf
5 1 USERS NO /u01/oradata/HAWK/datafile/o1_mf_users_jldgmgxd_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 37 TEMP 32767 /u01/oradata/hawk/temp01.dbf
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$