Yann Neuhaus
PostgreSQL 13 – Autovacuum can now be triggered based on inserts
A common issue with insert only tables in PostgreSQL is, that autovacuum well never kick in as the formula for autovacuum considers obsoleted tuples since the last vacuum but not the number of inserted tuples. Because of this you usually run a manual vacuum when a table is bulk loaded right after the load or you schedule regular vacuums for tables you know are insert only. PostgreSQL 13 will finally solve that issue because autovacuum will now be also triggered based on the amount of inserts into a table (more on the exact formula below). As usual let’s do a small, reproducible, demo to see how that works.
I have the latest (as of today) PostgreSQL development version running in the default configuration, except for some logging parameters I usually change. The table I’ll be playing with is this one:
postgres=# create table t1 ( a int, b text, c date );
CREATE TABLE
postgres=# \d t1
Table "public.t1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | text | | |
c | date | | |
When I bulk load that table in PostgreSQL before 13, autovacuum will never kick in. With PostgreSQL 13 we have two new configuration parameters that control autovacuum when it comes to inserts:
postgres=# show autovacuum_vacuum_insert_threshold; autovacuum_vacuum_insert_threshold ------------------------------------ 1000 (1 row) postgres=# show autovacuum_vacuum_insert_scale_factor; autovacuum_vacuum_insert_scale_factor --------------------------------------- 0.2 (1 row) postgres=#
The formula is:
vacuum insert threshold = autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * number of tuples
When we start with an empty table autovacuum should kick in when we insert more than 1000 rows:
postgres=# insert into t1 (a,b,c) select i, i::text, now() from generate_series(1,1001) i; INSERT 0 1001
Wait some time (autovacuum_naptime is 1 minute in the default configuration, so this is the maximum you will need to wait) and check pg_stat_user_tables for the last autovacuum:
postgres=# select last_autovacuum, autovacuum_count from pg_stat_user_tables where relname = 't1';
last_autovacuum | autovacuum_count
------------------------------+------------------
2020-03-29 15:25:42.75171+02 | 1
(1 row)
Perfect, that works. This is really a great improvement as you do not need to take special care of mostly insert only tables that do not receive a lot of updates or deletes. In addition you can probably skip all the manual vacuums after bulk loading.
Cet article PostgreSQL 13 – Autovacuum can now be triggered based on inserts est apparu en premier sur Blog dbi services.
Documentum – D2 Folder structure import failure with wsctf browser plugin
Earlier today, I talked about the Export/Download that wasn’t working randomly. In the end, it was both linked to the wsctf browser plugin (that didn’t provide the necessary cookies in the download request) as well as the “loadBalancedContentServer” parameter (that was wrongly set to false). For the previous issue, setting the “loadBalancedContentServer” property to true was sufficient to solve it (see more details on the previous blog). For this blog, unfortunately, the issue has a very similar description but the solution applied previously won’t help and I will explain here why.
In the same HA environment, using D2 16.5.1 P04, a second error was spotted and that was while using the folder structure import into D2 (Import > Folder structure). At that time, the Export/Download problem was already solved and it was therefore working properly. This new issue could be replicated all the time because I was the only one working on this environment. With less D2 pod or with more users working at the same time, it could potentially work from time to time (if the good conditions are met). Looking at the D2 logs, while the folder structure import is started, showed that the process started properly on the D2 pod I was currently logged into but then a piece of the process ran on another D2 pod and finally a last piece ran on yet another D2 pod. In the end, three different pods were all working on the same import request. The root cause here is also that the wsctf browser plugin doesn’t include the cookies and therefore there is a random distribution of the requests but it fails in the end because of missing files and that’s what I wanted to show here.
To understand the below logs, here is the setup and distribution of the Managed Servers of this WebLogic Domain on Kubernetes (each pod = 1 MS):
- wsd2-0 (replica 0), hosting msD2-00, using -Djava.io.tmpdir=$WS_TMP_FOLDER/msD2-00
- wsd2-1 (replica 1), hosting msD2-01, using -Djava.io.tmpdir=$WS_TMP_FOLDER/msD2-01
- wsd2-2 (replica 2), hosting msD2-02, using -Djava.io.tmpdir=$WS_TMP_FOLDER/msD2-02
- wsd2-3 (replica 3), hosting msD2-03, using -Djava.io.tmpdir=$WS_TMP_FOLDER/msD2-03
Below are the logs that were generated for a single folder structure import. I put them in chronological order:
#######
### Logs on 1st D2 pod accessed (currently logged into - wsd2-3)
### Starting the process, filling the properties and clicking on the start import
#######
2020-03-28 16:18:29,096 UTC [INFO ] ([ACTIVE] ExecuteThread: '94' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.x.p.s.s.dialog.RpcDialogServiceImpl : Context Repo1-1585412250771-morgan_patou-1788227800 with ID = and dialogName ImportFolderDialog
...
2020-03-28 16:19:07,317 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d2fs.dctm.api.services.D2ServiceProfile : Plugins (0.003s)
2020-03-28 16:19:07,386 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.api.services.D2Service : D2SavePropertiesService - Standard Servlet :
2020-03-28 16:19:07,454 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.d.a.s.p.D2SavePropertiesService : Object ID = 090f1234809a993f
2020-03-28 16:19:07,474 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.000s
2020-03-28 16:19:07,517 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.000s
2020-03-28 16:19:07,543 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.api.services.D2Service : D2SavePropertiesService - Result : D2SavePropertiesResult => {}
2020-03-28 16:19:07,544 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.api.services.D2Service : D2SavePropertiesService - Free memory=481.97684 MB, Total memory=1.0 GB
2020-03-28 16:19:07,544 UTC [INFO ] ([ACTIVE] ExecuteThread: '46' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.api.services.D2Service : D2SavePropertiesService - END (0.230s) =====================================
2020-03-28 16:19:07,852 UTC [INFO ] ([ACTIVE] ExecuteThread: '54' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.d.w.s.download.D2DownloadService : Link targets : []
#######
### Logs on 2nd D2 pod accessed (wsd2-1)
### First sub-request (same import)
#######
2020-03-28 16:19:08,040 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - c.emc.x3.portal.server.X3HttpSessionListener : Created http session HK9n8dQhE2-WeriIi7UVynZIN7x-v6JQB2ehL7-o33jHyrtvY_g7!785191258!1585412348040
2020-03-28 16:19:08,045 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - START =====================================
2020-03-28 16:19:08,045 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - HTTP Headers
Remote : 10.10.10.10 (10-10-10-10.ingress-nginx.ingress-nginx.svc.cluster.local)
Locale : en_US
Request Protocol : HTTP/1.1
Request Method : POST
Context Path : /D2
Request URI : /D2/servlet/ImportStructure
Request encoding : null
Request Parameters :
masterId : 090f1234809a993f
contentId : 0b0f1234809a943e
strucConf : Default folder structure import
folder : C:\Users\morgan_patou\Downloads\TestImport
id : 090f1234809a993f
_locale : en
fileName : TestFile.txt
_username : morgan_patou
uid : Repo1-1585412250771-morgan_patou-1788227800
_docbase : Repo1
_timeZoneId : Europe/Zurich
last : false
_password : ********
Request Headers :
Host : d2.domain.com
X-Request-ID : c42495cfddb8895ef556d0b9499118a4
X-Real-IP : 10.10.0.1
X-Forwarded-For : 10.10.0.1
X-Forwarded-Host : d2.domain.com
X-Forwarded-Port : 443
X-Forwarded-Proto : https
X-Original-URI : /D2/servlet/ImportStructure
X-Scheme : https
Content-Length : 2391
User-Agent : python-requests/2.21.0
Accept-Encoding : gzip, deflate
Accept : application/json
Content-Type : multipart/form-data; boundary=92fbbeceb4bcc427446e4b4bc363d23
Http Session :
2020-03-28 16:19:08,045 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - Standard Servlet :
2020-03-28 16:19:08,046 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - Free memory=352.96313 MB. Total memory=1.0 GB.
2020-03-28 16:19:08,047 UTC [INFO ] ([ACTIVE] ExecuteThread: '29' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - END (0.006s) =====================================
#######
### Logs on 3rd D2 pod accessed (wsd2-0)
### Second sub-request (same import)
#######
2020-03-28 16:19:08,189 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - c.emc.x3.portal.server.X3HttpSessionListener : Created http session KVd0OhIANP6hOsaiqNiyk1btCYB-G3i8eTRjXWOj7ImCqIVnwPyp!-948416399!1585412348189
2020-03-28 16:19:08,198 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - START =====================================
2020-03-28 16:19:08,199 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - HTTP Headers
Remote : 10.10.10.10 (10-10-10-10.ingress-nginx.ingress-nginx.svc.cluster.local)
Locale : en_US
Request Protocol : HTTP/1.1
Request Method : POST
Context Path : /D2
Request URI : /D2/servlet/ImportStructure
Request encoding : null
Request Parameters :
masterId : 090f1234809a993f
contentId : 0b0f1234809a943e
strucConf : Default folder structure import
folder : C:\Users\morgan_patou\Downloads\TestImport
id : 090f1234809a993f
_locale : en
fileName : import_structure4ga5dgjs.xml
_username : morgan_patou
uid : Repo1-1585412250771-morgan_patou-1788227800
_docbase : Repo1
_timeZoneId : Europe/Zurich
last : true
_password : ********
Request Headers :
Host : d2.domain.com
X-Request-ID : 6ef85a137e9e20ccbbd81ddbb6898c6c
X-Real-IP : 10.10.0.1
X-Forwarded-For : 10.10.0.1
X-Forwarded-Host : d2.domain.com
X-Forwarded-Port : 443
X-Forwarded-Proto : https
X-Original-URI : /D2/servlet/ImportStructure
X-Scheme : https
Content-Length : 2600
User-Agent : python-requests/2.21.0
Accept-Encoding : gzip, deflate
Accept : application/json
Content-Type : multipart/form-data; boundary=df774e60a79eb3d3400d05417455cf4e
Http Session :
2020-03-28 16:19:08,199 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - Standard Servlet :
2020-03-28 16:19:09,657 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.001s
2020-03-28 16:19:09,702 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.000s
2020-03-28 16:19:14,197 UTC [ERROR] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Upload failed
com.documentum.fc.client.DfSysObjectException: [DM_SYSOBJECT_E_CANT_ACCESS_FILE]error: "Cannot access file '$WS_TMP_FOLDER/msD2-01/DefaultFileRenamePolicy.rename3568065702834064539.txt' due to Operating System error."
at com.documentum.fc.client.DfSysObjectException.newCantAccessFileException(DfSysObjectException.java:180)
at com.documentum.fc.client.DfSysObject.verifyFiles(DfSysObject.java:2198)
at com.documentum.fc.client.DfSysObject.doSetFile(DfSysObject.java:2134)
at com.documentum.fc.client.DfSysObject.setFileEx(DfSysObject.java:2125)
at com.documentum.fc.client.DfSysObject.setFile(DfSysObject.java:2120)
at com.documentum.fc.client.DfDocument___PROXY.setFile(DfDocument___PROXY.java)
at com.emc.d2.api.config.modules.structure.D2StructureConfig.importFiles(D2StructureConfig.java:1021)
at com.emc.d2.api.config.modules.structure.D2StructureConfig.importStructure(D2StructureConfig.java:748)
at com.emc.d2.api.config.modules.structure.D2StructureConfig.importStructureFiles(D2StructureConfig.java:452)
...
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:652)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
2020-03-28 16:19:14,202 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - Free memory=281.8924 MB. Total memory=1.0 GB.
2020-03-28 16:19:14,203 UTC [INFO ] ([ACTIVE] ExecuteThread: '36' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : ImportStructure - END (6.013s) =====================================
So what happen exactly? Well you probably understood already but basically the import request was triggered on the correct D2 pod. Unfortunately, after that, a first sub-request was sent to another D2 pod. This one succeeded so there is no issue. But then a second sub-request was sent to yet another D2 pod and this one failed saying that the file “$WS_TMP_FOLDER/msD2-01/DefaultFileRenamePolicy.rename3568065702834064539.txt” wasn’t accessible. If you look at the table I put earlier, the request failed on wsd2-0 while the message indicates a path of “$WS_TMP_FOLDER/msD2-01“: this is the temporary folder of the wsd2-1 pod… Therefore, the import started on the wsd2-1 pod, it created some temporary files locally but then the next sub-request was sent to another pod and this one do not have the temporary folder locally which resulted in an import failure.
A workaround would be to share the temporary folders between the D2 pods. I didn’t test that but I don’t see why it wouldn’t work (or at least work a little bit better). Obviously, this might cause some additional issues because I believe that the temporary folders are cleaned periodically if the files aren’t used. Since you would have a shared usage, it could probably remove files that are still used by other pods. It’s not a good workaround anyway so let’s just forget about it.
Except that, I don’t see another simple workaround. Regarding the previous blog I mentioned before, I would have opened an OpenText SR asking if it’s expected that the cookies aren’t sent with the wsctf browser plugin mode. Since I faced another issue also related to the same root cause but for which I don’t have a simple solution, I changed a little bit what I wanted to put in the ticket and opened the OpenText SR#4459303 to discuss this issue. Our dedicated support replied in a few minutes that it is a know issue, referenced as DTWO-48180. This is normally fixed in D2 16.5.1 P05 (latest patch, released on 31-Jan-2020). However, the P05 Patch Note doesn’t have any mention of this DTWO-48180 or anything related to wsctf+cookies. Therefore, they opened a documentation bug to add it in the Patch Note. I guess I will have to upgrade from the P04 to the P05 in the coming days to see if it does solve the missing cookies issue while using the wsctf browser plugin!
Cet article Documentum – D2 Folder structure import failure with wsctf browser plugin est apparu en premier sur Blog dbi services.
Using AWS Storage Gateway as a backup target for PostgreSQL – Cached volume gateway
In the last post we had a look on how you can use AWS Storage Gateway as File gateway to store your PostgreSQL backups safely offsite on AWS S3. Another method of doing that would be to use “Cached Volume gateway” instead of the File gateway we used in the last post. The volume gateways does not provide access via NFS or SMB but does provide a volume over iSCSI and the on-prem machines will directory work against the volumes. Let’s see how that works.
The procedure for creating a Volume gateway is more or less the same as the procedure we used in the last post, except that we are now going for the “Volume gateway” instead of the “File gateway”:
For setting up the EC2 instance you can refer to the last post, except that you’ll need two additional disks and not only one:
The first additional disk will be used as the cache and the second one will be used as the upload buffer. The upload buffer is used to upload the data encrypted to S3, check here for the concepts.
Make sure that you add the iSCSI initiator port to you incoming rules in the security group which is attached to the EC2 instance:
Again we’ll go for public access as we want to use the volumes on a local VM.
Connect to the gateway:
Activate the gateway:
Assign the disks for the cache and the upload buffer and go with the default for the next screens:
Last time we created a file share and now we are going to create a volume:
We are going for a brand new volume but we could have also used a snapshot as a base:
Specify the secrets:
You can find the initiator name on the local machine:
postgres@centos8pg:/home/postgres/ [pg13] cat /etc/iscsi/initiatorname.iscsi InitiatorName=iqn.1994-05.com.redhat:3ff52ad95a75
… and the new volume is ready:
Now we need to attach the volume to our local machine. First of all we need to install the required packages and then enable and start the service:
postgres@centos8pg:/home/postgres/ [pgdev] sudo dnf install -y iscsi-initiator-utils
postgres@centos8pg:/home/postgres/ [pgdev] sudo systemctl enable iscsid.service
Created symlink /etc/systemd/system/multi-user.target.wants/iscsid.service → /usr/lib/systemd/system/iscsid.service.
postgres@centos8pg:/home/postgres/ [pgdev] sudo systemctl start iscsid.service
postgres@centos8pg:/home/postgres/ [pgdev] sudo systemctl status iscsid.service
● iscsid.service - Open-iSCSI
Loaded: loaded (/usr/lib/systemd/system/iscsid.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2020-03-27 14:18:04 CET; 3s ago
Docs: man:iscsid(8)
man:iscsiadm(8)
Main PID: 5289 (iscsid)
Status: "Ready to process requests"
Tasks: 1
Memory: 3.6M
CGroup: /system.slice/iscsid.service
└─5289 /usr/sbin/iscsid -f
Mar 27 14:18:04 centos8pg systemd[1]: Starting Open-iSCSI...
Mar 27 14:18:04 centos8pg systemd[1]: Started Open-iSCSI.
Check that you are able to discover the target using the public IP address of the Storage Gateway EC2 instance:
postgres@centos8pg:/home/postgres/ [pgdev] sudo /sbin/iscsiadm --mode discovery --type sendtargets --portal xxx.xxx.xxx.xxx:3260 10.0.1.159:3260,1 iqn.xxxx-xx.com.amazon:dwe-isci-target
When that is wokring fine you should configure CHAP authentication (using the secrets we deinfed above):
postgres@centos8pg:/home/postgres/ [pg13] sudo vi /etc/iscsi/iscsid.conf node.session.auth.authmethod = CHAP # To set a CHAP username and password for initiator # authentication by the target(s), uncomment the following lines: node.session.auth.username = iqn.1994-05.com.redhat:3ff52ad95a75 node.session.auth.password = 123456789012 # To set a CHAP username and password for target(s) # authentication by the initiator, uncomment the following lines: node.session.auth.username_in = iqn.xxx-xx.com.amazon:dwe-isci-target node.session.auth.password_in = 098765432109
And then the big surprise when you try to login to the target:
postgres@centos8pg:/home/postgres/ [pg13] sudo /sbin/iscsiadm --mode node --targetname iqn.1997-05.com.amazon:dwe-isci-target --portal 10.0.1.159:3260,1 --login Logging in to [iface: default, target: iqn.1997-05.com.amazon:dwe-isci-target, portal: 10.0.1.159,3260] iscsiadm: Could not login to [iface: default, target: iqn.1997-05.com.amazon:dwe-isci-target, portal: 10.0.1.159,3260]. iscsiadm: initiator reported error (8 - connection timed out) iscsiadm: Could not log into all portals
That of course can not work from a local machine and the explanation is in the documentation:
I’ve quickly started another EC2 instance, installed the same packages, enabled the iSCSI service and added the new intiator name to the CHAP configuration of the volume:
Trying to discover and to login again from the new ec2 host:
[root@ip-10-0-1-252 ec2-user]# sudo /sbin/iscsiadm --mode discovery --type sendtargets --portal 10.0.1.159:3260 10.0.1.159:3260,1 iqn.1997-05.com.amazon:dwe-isci-target [root@ip-10-0-1-252 ec2-user]# /sbin/iscsiadm --mode node --targetname iqn.1997-05.com.amazon:dwe-isci-target --portal 10.0.1.159:3260,1 --login Logging in to [iface: default, target: iqn.1997-05.com.amazon:dwe-isci-target, portal: 10.0.1.159,3260] (multiple) Login to [iface: default, target: iqn.1997-05.com.amazon:dwe-isci-target, portal: 10.0.1.159,3260] successful.
… and now it succeeds. You will see a successful connection also in the volume overview:
You can also confirm that on the client:
[root@ip-10-0-1-75 ec2-user]$ lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT sda 8:0 0 50G 0 disk xvda 202:0 0 10G 0 disk |-xvda1 202:1 0 1M 0 part `-xvda2 202:2 0 10G 0 part / [root@ip-10-0-1-75 ec2-user]$ ls -la /dev/sda* brw-rw----. 1 root disk 8, 0 Mar 27 15:04 /dev/sda [root@ip-10-0-1-75 ec2-user]# [root@ip-10-0-1-75 ec2-user]$ fdisk -l /dev/sda Disk /dev/sda: 50 GiB, 53687091200 bytes, 104857600 sectors Units: sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes
Before you can put data on that block device their needs to be a file system on it and it needs to be mounted:
[root@ip-10-0-1-75 ec2-user]$ parted /dev/sda mklabel msdos
Information: You may need to update /etc/fstab.
[root@ip-10-0-1-75 ec2-user]$ parted -a opt /dev/sda mkpart primary xfs 0% 100%
Information: You may need to update /etc/fstab.
[root@ip-10-0-1-75 ec2-user]$ ls -la /dev/sda*
brw-rw----. 1 root disk 8, 0 Mar 27 15:07 /dev/sda
brw-rw----. 1 root disk 8, 1 Mar 27 15:07 /dev/sda1
[root@ip-10-0-1-75 ec2-user]$ mkfs.xfs /dev/sda1
meta-data=/dev/sda1 isize=512 agcount=4, agsize=3276736 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=1, sparse=1, rmapbt=0
= reflink=1
data = bsize=4096 blocks=13106944, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0, ftype=1
log =internal log bsize=4096 blocks=6399, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@ip-10-0-1-75 ec2-user]$ mount -t xfs /dev/sda1 /var/tmp/sgw/
[root@ip-10-0-1-75 ec2-user]$ df -h | grep sgw
/dev/sda1 50G 390M 50G 1% /var/tmp/sgw
So now we have block device that is coming from the storage gateway. Let’s put some data on it and check the statistics in the AWS console:
[root@ip-10-0-1-75 ec2-user]$ dd if=/dev/zero of=/var/tmp/sgw/ff1 bs=1M count=1000 1000+0 records in 1000+0 records out 1048576000 bytes (1.0 GB, 1000 MiB) copied, 27.5495 s, 38.1 MB/s
The statistics confirm that data is coming in:
One downside of the volume gateway is, that you won’t see anything in the S3 overview. The benefits are, that you can make snapshots of the volumes and also clone them. From a pure PostgreSQL backup perspective I would probably use the file gateway but for other use cases like application servers a cached volume gateway might be the right thing to use if you want to extend your local storage to AWS.
Cet article Using AWS Storage Gateway as a backup target for PostgreSQL – Cached volume gateway est apparu en premier sur Blog dbi services.
Documentum – D2 Export/Download random failure with wsctf browser plugin
The D2 clients were, until recently (D2 version 16.4 included), working by default with the java plugin mode and therefore, many actions were using a JVM on the client workstation. This setting can be controlled/set in the “WEB-INF/classes/settings.properties” file of D2 applications (D2, D2-REST, D2-Smartview) and the default value was therefore “browser.plugin.mode = java“. Starting with D2 16.5, this was changed and the default value is now “browser.plugin.mode = wsctf,thin“, meaning that it will try to use/load the wsctf (Web Socket Content Transfer Framework) and if it’s not possible it will fallback to the thin mode. This blog isn’t an introduction to the browser plugin mode, there would be a LOT to talk about but I needed to introduce a few things so the issue below will be clear/understandable by everyone.
So in a newly build environment on a K8s infrastructure, a user reported some random failures on D2 Export/Download. This specific environment was built in HA in Kubernetes so we had 3 Content Servers pods, 4 D2 pods (WebLogic Managed Server), 2 D2-Config pods (WebLogic Managed Server), and some other components. The session stickiness was handled by the Ingress Controller using Cookies and this was working properly, the HA was also working properly. So, login/logout/browse/search was fully working for all clients but when you started to Export/Download a document, then some randomness appeared. The environment was using D2 16.5.1, it’s important because of the thing I mentioned above: it was therefore using the wsctf plugin (we didn’t change it). At first, I tried to replicate the issue using Google Chrome, log in to D2, find a document, right-click on it and select Export. From what I could see on the D2 logs, it was always working: I tried 20 times in a 10/15 minutes period but each and every time, I could see the download request on the same WebLogic Managed Server that I was logged in. Therefore, on the 4 D2 pods I had, only 1 was used. Removing the cookies, restarting the Google Chrome browser and the same happened for another pod (no issue). So, what’s the issue then? Well actually, I wasn’t using the wsctf plugin on Chrome because it’s usually faster to use the thin mode and therefore that’s how I tried to replicate the issue but couldn’t. The logs of the download using the thin mode were like that:
2020-03-28 11:20:39,226 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - START =====================================
2020-03-28 11:20:39,227 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - HTTP Headers
Remote : 10.10.10.10 (10-10-10-10.ingress-nginx.ingress-nginx.svc.cluster.local)
Locale : en_US
Request Protocol : HTTP/1.1
Request Method : GET
Context Path : /D2
Request URI : /D2/servlet/Download
Request encoding : null
Request Parameters :
_docbase : Repo1
_locale : en
_password : ********
_timeZoneId : Europe/Zurich
_username : morgan_patou
event_name : d2_export
format :
hasRelate : 1
id : 090f1234809aa823
uid : Repo1-1585394439230-morgan_patou-401674627
Request Headers :
Host : d2.domain.com
X-Request-ID : 50a051639dc2a3971cfc3d6cb9010738
X-Real-IP : 10.10.0.1
X-Forwarded-For : 10.10.0.1
X-Forwarded-Host : d2.domain.com
X-Forwarded-Port : 443
X-Forwarded-Proto : https
X-Original-URI : /D2/servlet/Download?uid=Repo1-1585394439230-morgan_patou-401674627&_docbase=Repo1&_username=morgan_patou&_password=DM_TICKET%3DEwxMwp2gMAoZlOVC...BMOE1HWXlJXCg%3D%3D&_locale=en&_timeZoneId=Europe%2FZurich&id=090f1234809aa823&format=&event_name=d2_export&_timeZoneId=Europe%2FZurich&hasRelate=1
X-Scheme : https
upgrade-insecure-requests : 1
user-agent : Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.122 Safari/537.36
sec-fetch-dest : iframe
accept : text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9
sec-fetch-site : same-origin
sec-fetch-mode : navigate
referer : https://d2.domain.com/D2/?docbase=Repo1&locateId=090f1234809aa823
accept-encoding : gzip, deflate, br
accept-language : en-US,en;q=0.9
Cookies :
lastRepo : Repo1
locale : en_US
sessAcv : 1
https%3A%2F%2Fd2.domain.com%2FD2%2Fx3_portal%2Ftheme : %7B%22state%22%3A%7B%22id%22%3A%22s%3Aslate%22%2C%20%22file%22%3A%22s%3Aresources%2Fthemes%2Fslate%2Fcss%2Fxtheme-x3.css%22%7D%7D
D2_k8s_env1_sticky : 1585393439.816.1698.878338
JSESSIONID_D2_K8s_env1 : H04MOTyK_nAo4LXdEZlMoeCTMrq94rsx53ds2_3kuhKEG8240V9S!-1569294555
x-auto-271 : %7B%22state%22%3A%7B%22rowSel%22%3A%22i%3A7%22%7D%7D
sessExTm : 1585395180150
Http Session :
XSRF_TOKEN : 03c0e04906106d97cbe884a7d316deb168b1850959b_1572b22bbb360f265
CONTEXT : {Repo1-1585394439230-morgan_patou-401674627=com.emc.d2fs.models.context.Context@4d3e927a, Repo1-1585393445738-morgan_patou-1618912563=com.emc.d2fs.models.context.Context@589aaba3}
org.apache.shiro.web.session.HttpServletSession.HOST_SESSION_KEY : 10-10-10-10.ingress-nginx.ingress-nginx.svc.cluster.local
2020-03-28 11:20:39,228 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - Standard Servlet :
2020-03-28 11:20:40,642 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.000s
2020-03-28 11:20:41,682 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.download.Download : downloadFile:Test_document.docx
2020-03-28 11:20:41,682 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.download.Download : file stream is not null
2020-03-28 11:20:41,687 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - Free memory=242.65753 MB. Total memory=1.0 GB.
2020-03-28 11:20:41,687 UTC [INFO ] ([ACTIVE] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - END (2.462s) =====================================
With the above, everything is fine and all communications go through the same D2 pod thanks to the stickiness. So what’s the issue then? To be sure, I tried the same thing with Internet Explorer: login/logout/browse/search was also fully working, all targeting a single pod in the backend. However, when trying to Export/Download, I could see on the D2 logs that the download request was actually triggered to any of the D2 pod, meaning the stickiness was lost for this specific type of request. While looking at the download logs, I could see the following:
2020-03-28 11:23:16,734 UTC [INFO ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - START =====================================
2020-03-28 11:23:16,735 UTC [INFO ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - HTTP Headers
Remote : 10.10.10.10 (10-10-10-10.ingress-nginx.ingress-nginx.svc.cluster.local)
Locale : en_US
Request Protocol : HTTP/1.1
Request Method : GET
Context Path : /D2
Request URI : /D2/servlet/Download
Request encoding : null
Request Parameters :
_docbase : Repo1
_locale : en
_password : ********
_timeZoneId : Europe/Zurich
_username : morgan_patou
event_name : d2_export
format :
hasRelate : 1
id : 090f1234809aa823
uid : Repo1-1585394596738-morgan_patou-1829106889
Request Headers :
Host : d2.domain.com
X-Request-ID : 267f10747ab4e6df0b0b74c6be0e2f95
X-Real-IP : 10.10.0.1
X-Forwarded-For : 10.10.0.1
X-Forwarded-Host : d2.domain.com
X-Forwarded-Port : 443
X-Forwarded-Proto : https
X-Original-URI : /D2/servlet/Download?uid=Repo1-1585394596738-morgan_patou-1829106889&_docbase=Repo1&_username=morgan_patou&_password=DM_TICKET%3DEwxMwp2gMAoZlOVC...2UFK01ODR6dCg%3D%3D&_locale=en&_timeZoneId=Europe%2FZurich&id=090f1234809aa823&format=&event_name=d2_export&_timeZoneId=Europe%2FZurich&hasRelate=1
X-Scheme : https
User-Agent : python-requests/2.21.0
Accept-Encoding : gzip, deflate
Accept : */*
Http Session :
2020-03-28 11:23:16,735 UTC [INFO ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - Standard Servlet :
2020-03-28 11:23:18,044 UTC [ERROR] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.web.services.D2fsContext : {}
com.documentum.fc.client.DfServiceException: [DM_SESSION_E_LDAP_CHGPASS_USER]error: "The changepassword API failed with the following error: morgan_patou"
at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
at com.documentum.fc.client.impl.connection.docbase.MessageEntry.getException(MessageEntry.java:39)
at com.documentum.fc.client.impl.connection.docbase.DocbaseMessageManager.getException(DocbaseMessageManager.java:137)
at com.documentum.fc.client.impl.connection.docbase.netwise.NetwiseDocbaseRpcClient.checkForMessages(NetwiseDocbaseRpcClient.java:329)
at com.documentum.fc.client.impl.connection.docbase.netwise.NetwiseDocbaseRpcClient.applyForObject(NetwiseDocbaseRpcClient.java:672)
at com.documentum.fc.client.impl.connection.docbase.DocbaseConnection$8.evaluate(DocbaseConnection.java:1382)
...
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
2020-03-28 11:23:18,046 UTC [ERROR] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - c.emc.d2fs.dctm.aspects.InjectSessionAspect : {}
com.documentum.fc.client.DfServiceException: [DM_SESSION_E_LDAP_CHGPASS_USER]error: "The changepassword API failed with the following error: morgan_patou"
at com.documentum.fc.client.impl.docbase.DocbaseExceptionMapper.newException(DocbaseExceptionMapper.java:57)
...
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
2020-03-28 11:23:18,046 UTC [WARN ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.download.Download : error in downloading file:com.emc.d2fs.exceptions.D2fsException: [DM_SESSION_E_LDAP_CHGPASS_USER]error: "The changepassword API failed with the following error: morgan_patou"
2020-03-28 11:23:18,056 UTC [ERROR] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download failed
com.emc.d2fs.exceptions.D2fsException: [DM_SESSION_E_LDAP_CHGPASS_USER]error: "The changepassword API failed with the following error: morgan_patou"
at com.emc.d2fs.dctm.aspects.InjectSessionAspect.exceptionHandling(InjectSessionAspect.java:347)
at com.emc.d2fs.dctm.aspects.InjectSessionAspect.ajc$inlineAccessMethod$com_emc_d2fs_dctm_aspects_InjectSessionAspect$com_emc_d2fs_dctm_aspects_InjectSessionAspect$exceptionHandling(InjectSessionAspect.java:1)
at com.emc.d2fs.dctm.aspects.InjectSessionAspect.process(InjectSessionAspect.java:245)
at com.emc.d2fs.dctm.web.services.download.D2DownloadService.getDownloadObjectId(D2DownloadService.java:703)
at com.emc.d2fs.dctm.servlets.download.Download.processRequest(Download.java:166)
at com.emc.d2fs.dctm.servlets.D2HttpServlet.execute(D2HttpServlet.java:244)
...
2020-03-28 11:23:18,058 UTC [INFO ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - Free memory=560.1069 MB. Total memory=1.0 GB.
2020-03-28 11:23:18,058 UTC [INFO ] ([ACTIVE] ExecuteThread: '49' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download - END (1.324s) =====================================
That’s pretty different compared to the content of the download from Google Chrome, isn’t it? If you look carefully, you can see that there are Cookies in particular aren’t in the request made… One might think that’s it’s just IE that doesn’t provide it while Chrome does but actually, that’s not it. D2 is working in the same way (on this aspect at least…) for both Chrome and IE. In this case, the content of the log was different because for IE, the wsctf plugin was used. Therefore, the issue in this case is caused by the fact that the wsctf plugin doesn’t send the cookies in the download request while the thin mode does. That’s a bug, it shouldn’t happen. Initially, I wanted to open a SR with the OpenText Support to ask whether this is the expected behavior. However, a few minutes later, I found another related issue (see this blog) which clearly showed that it’s not.
Since there is no cookies, the Ingress Controller doesn’t know which D2 pod should receive the request and therefore it’s a random distribution. Obviously, this isn’t just for an Ingress Controller, you would face exactly the same thing with a standard Load Balancer that is configured to use cookies stickiness.
Is this really the issue? Well, not mandatorily… I mean yes it can be considered as the issue as the behavior is different between the wsctf and the thin mode, which in itself shouldn’t happen. However, the download request normally doesn’t need the session details because it’s a “standalone” request which could therefore normally be processed by any of the D2 pod running. There is one parameter that is often forgotten when setting up D2 and that is specific to the HA setup: the “loadBalancedContentServer” parameter inside the “WEB-INF/classes/D2FS.properties” file. This parameter is by default set to false but if you have more than one Content Server (so HA on the Repo layer), then you are supposed to set it to true. It’s often forgotten because keeping it to false will usually not cause much trouble, except under very specific circumstances. This parameter wasn’t set properly in this environment, so I just fixed it to solve this specific issue:
[weblogic@wsd2-0 ~]$ cd $APPLICATIONS
[weblogic@wsd2-0 apps]$
[weblogic@wsd2-0 apps]$ d2fs="D2/WEB-INF/classes/D2FS.properties"
[weblogic@wsd2-0 apps]$ grep loadBalancedContentServer ${d2fs}
loadBalancedContentServer=false
[weblogic@wsd2-0 apps]$
[weblogic@wsd2-0 apps]$ sed -i 's,^loadBalancedContentServer=.*,loadBalancedContentServer=true,' ${d2fs}
[weblogic@wsd2-0 apps]$
[weblogic@wsd2-0 apps]$ grep loadBalancedContentServer ${d2fs}
loadBalancedContentServer=true
[weblogic@wsd2-0 apps]$
Once it’s corrected, redeploy D2 and then you should still see the same behavior in terms of wsctf download requests, meaning that the requests will be sent to any of the D2 pods with cookies. However, thanks to this parameter, the download requests should all succeed, even if the D2 pod handling it isn’t the one you are currently logged into and that’s because the login ticket is now global so it will be accepted by all Content Servers, no matter which one is used.
Cet article Documentum – D2 Export/Download random failure with wsctf browser plugin est apparu en premier sur Blog dbi services.
A change in full table scan costs in 19c?
During tests in Oracle 19c I recently experienced this:
cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); ... --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 26439 (100)| | |* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 26439 (14)| 00:00:02 | ---------------------------------------------------------------------------
–> The costs of the full table scan are 26439.
Setting back the optimizer_features_enable to 18.1.0 showed different full table scan costs:
cbleile@orcl@orcl> alter session set optimizer_features_enable='18.1.0'; cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); ... --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 109K(100)| | |* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 109K (4)| 00:00:05 | ---------------------------------------------------------------------------
–> The costs are 109K versus around 26K in 19c.
Why do we have such a difference for the costs of a full table scan between 18c and 19c?
With the CPU-cost model full table scans are computed as follows:
FTS Cost = ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM + “CPU-costs”
REMARK: This is not 100% correct, but the difference is not important here.
In my case:
cbleile@orcl@orcl> select blocks from tabs where table_name='DEMO4';
BLOCKS
----------
84888
cbleile@orcl@orcl> select * from sys.aux_stats$;
SNAME PNAME PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ----------
...
SYSSTATS_MAIN SREADTIM 1
SYSSTATS_MAIN MREADTIM 10
SYSSTATS_MAIN CPUSPEED 2852
SYSSTATS_MAIN MBRC 8
...
I.e.
FTS Cost = ((BLOCKS/MBRC) x MREADTIM)/ SREADTIM + CPU = ((84888/8) x 10)/ 1 + CPU = 106110 + CPU
Considering the additional CPU-cost we are at the costs we see in 18c: 109K
Why do we see only costs of 26439 in 19c (around 25% of 18c)?
The reason is that the optimizer considers “wrong” system statistics here. I.e. let’s check the system statistics again:
SNAME PNAME PVAL1 PVAL2 ------------------------------ ------------------------------ ---------- --------- SYSSTATS_MAIN SREADTIM 1 SYSSTATS_MAIN MREADTIM 10 SYSSTATS_MAIN MBRC 8
In theory it’s not possible that MREADTIM > SREADTIM * MBRC. I.e. reading e.g. 8 contiguous blocks from disk cannot be slower than reading 8 random blocks from disk. Oracle has considered that and treats the available system statistics as wrong and takes different values internally. The change was implemented with bug fix 27643128. See My Oracle Support Note “Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1)” for details.
I.e. switching the bug fix off results in full table scan costs as in 18c:
cbleile@orcl@orcl> alter session set optimizer_features_enable='19.1.0'; cbleile@orcl@orcl> alter session set "_fix_control"='27643128:OFF'; cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); ... --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 109K(100)| | |* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 109K (4)| 00:00:05 | ---------------------------------------------------------------------------
To get the intended behavior in 19c you should make sure that
MREADTIM <= SREADTIM * MBRC
E.g. in my case
cbleile@orcl@orcl> alter system set db_file_multiblock_read_count=12
cbleile@orcl@orcl> exec dbms_stats.set_system_stats('MBRC',12);
cbleile@orcl@orcl> alter session set optimizer_features_enable='19.1.0';
cbleile@orcl@orcl> select * from demo4 where m=103;
cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74188 (100)| |
|* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 74188 (5)| 00:00:03 |
---------------------------------------------------------------------------
...
cbleile@orcl@orcl> alter session set optimizer_features_enable='18.1.0';
cbleile@orcl@orcl> select * from demo4 where m=103;
cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);
...
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 74188 (100)| |
|* 1 | TABLE ACCESS FULL| DEMO4 | 1 | 10 | 74188 (5)| 00:00:03 |
---------------------------------------------------------------------------
...
I.e. the costs in 19c and 18c are the same again.
Please consider the following:
– If you’ve gathered or set system statistics then always check that they are reasonable.
– If you do work with a very low SREADTIM and high MREADTIM to favor Index-access (to not use low values for OPTIMIZER_INDEX_COST_ADJ) then make sure that MREADTIM <= SREADTIM * MBRC. Otherwise you may see plan changes when migrating to 19c.
Cet article A change in full table scan costs in 19c? est apparu en premier sur Blog dbi services.
Using AWS Storage Gateway as a backup target for PostgreSQL
In the last post we had a quick look at AWS Kinesis data streams and how you can push data to AWS S3 using AWS Firehose. Another great AWS service is AWS Storage Gateway and in this post we’ll use that to push our PostgreSQL backups to AWS. The primary usage of AWS Storage Gateway is to extend you local storage to AWS so that you do not have to extend your local storage on one side and on the other side safely can store data offsite for backup purposes. We are already using that at some of our customers and experiences with that are quite well. One important point to keep in mind when you are using AWS Storage Gateway is: In case you need to restore the data to your on-prem location you will be charged for the outgoing traffic, so it might be a good idea to calculate the costs before you start using it.
The initial deployment of an AWS Storage Gateway, as usual, is quite simple. Using the AWS console this is matter of a few clicks:
AWS Storage gateway comes with three types you can chose from:
- File gateway: This is what we’ll be using and it provides a file interface to AWS S3 using standard file protocols like NFS and SMB
- Volume gateway: This provides AWS backed cloud volumes that can by mounted over iSCSI, data goes to AWS S3
- Tage gateway: This provides virtual tape storage you can use with your on-prem backup solutions and data goes to AWS Glacier
The AWS Storage Gateway comes as an appliance and depending on what you have available on-prem you import that either into your VMware ESXi, Microsoft Hyper-V 2012R2/2016 or KVM based virtual infrastructure. You can also use an EC2 instance and this is what we’ll be using here because it is the easiest way to deploy that for testing:
I am not going to provide all the screenshots for setting up the EC2 instance as that is straight forward, just the most important ones. For production usage AWS recommends several instance types. We’ll be using a small one here as it is only a test:
As we’ll be accessing the Storage Gateway from a location outside AWS we need a pubic IP address for the EC2 instance:
For the File Gateway we need one additional disk with a minimum recommended size of 150GB:
You need to make sure that the attached security group allows access from your local machines, at least for HTTP, HTTPS, NFS and SMB. An overview of the port requirements is here:
For the remaining EC2 settings this is standard stuff so no screenshots for that. Wait until the EC2 instance is running and make a note of the public IP address (you will need it in the next step of the Storage Gateway configuration):
Once the EC2 instance is ready we can go back to the Storage Gateway configuration and provide the public IP address of the EC2 instance we just created:
If the connection fails in the next step go back to your security group configuration because then required ports are probably missing. If all is fine the Storage Gateway can be activated in the next screen:
If we had skipped the additional disk setup when we created the EC2 instance we would be stuck in the next step as now we need to tell which disk we will use as the cache:
Logging should be enabled for production setups, we’ll skip it for this test and will go ahead and finally create the Storage Gateway:
The Storage Gateway is now ready and we can go ahead and create a file share we can later mount on the PostgreSQL machine:
As objects will go to S3 in the background you should have created a S3 bucket you can specify now and as PostgreSQL will be running on a Linux host we’ll go for NFS:
Go with the defaults on the next screen:
On the summary screen you should adjust the “Allowed clients” so only clients that you really know are safe to connect will be able to use the file share:
Once the share is ready there will be examples on how you can mount the file share for various operating systems (these examples will show the private IP addresses):
Time to mount the file share on our local PostgreSQL machine:
postgres@centos8pg:/home/postgres/ [pgdev] sudo mkdir /var/tmp/storageGateway postgres@centos8pg:/home/postgres/ [pgdev] sudo mount -t nfs -o nolock,hard xxx.xxx.xxx.xxx:/dwe-bucket-storage-gateway /var/tmp/storageGateway ostgres@centos8pg:/home/postgres/ [pgdev] df -h | grep storageGateway xxx.xxx.xxx.xxx:/dwe-bucket-storage-gateway 8.0E 0 8.0E 0% /var/tmp/storageGateway
This can now be used to write your PostgreSQL backups to and all the files will go to S3 in the background:
postgres@centos8pg:/home/postgres/ [pgdev] mkdir /var/tmp/storageGateway/basebackup_20200327 postgres@centos8pg:/home/postgres/ [pgdev] pg_basebackup -D /var/tmp/storageGateway/basebackup_20200327/ postgres@centos8pg:/home/postgres/ [pg13] ls -la /var/tmp/storageGateway/basebackup_20200327/ total 36 drwxr-xr-x. 1 postgres postgres 0 Mar 27 10:43 . drwxrwxrwx. 1 nobody nobody 0 Mar 27 10:41 .. -rw-------. 1 postgres postgres 224 Mar 27 10:41 backup_label drwx------. 1 postgres postgres 0 Mar 27 10:42 base -rw-------. 1 postgres postgres 33 Mar 27 10:43 current_logfiles drwx------. 1 postgres postgres 0 Mar 27 10:43 global drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_commit_ts drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_dynshmem -rw-------. 1 postgres postgres 4513 Mar 27 10:43 pg_hba.conf -rw-------. 1 postgres postgres 1636 Mar 27 10:43 pg_ident.conf drwxr-xr-x. 1 postgres postgres 0 Mar 27 10:43 pg_log drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_logical drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_multixact drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_notify drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_replslot drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_serial drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_snapshots drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_stat drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_stat_tmp drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_subtrans drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_tblspc drwx------. 1 postgres postgres 0 Mar 27 10:41 pg_twophase -rw-------. 1 postgres postgres 3 Mar 27 10:43 PG_VERSION drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_wal drwx------. 1 postgres postgres 0 Mar 27 10:43 pg_xact -rw-------. 1 postgres postgres 252 Mar 27 10:43 postgresql.auto.conf -rw-------. 1 postgres postgres 27343 Mar 27 10:43 postgresql.conf
If you wait some time and then check the monitoring section of the Storage Gateway the various graphs should give you an idea what is happening:
Checking the S3 bucket will also show you the files we have written to the Storage Gateway:
Combine that with a backup solution like pgBackRest, barman, EDB BART or whatever you use and you have your PostgreSQL backups safely written offsite to S3.
Cet article Using AWS Storage Gateway as a backup target for PostgreSQL est apparu en premier sur Blog dbi services.
SQL Server: Quickly clean backup history with dbatools
I just had to restore a database in production for my customer. Before doing the restore I have the habit to query the msdb.dbo.backupset table to get an overview of the last backups.
When running my query, I felt it was taking longer than usual. So out of curiosity, I looked at the SSMS standard report “Disk Usage by Top Tables”. Here is the output.
This instance contains dozens of databases in Always On Availability Groups with a transaction log backup frequency set to 30 minutes. The backup history has never been cleaned, which explain the large number of rows.
It’s not often that I see the msdb database with a size of 3.5GB, so I decided it’s time to delete the backup history. My customer got many instances that are configured and managed the same way so I’m sure this phenomenon will be present on many servers.
I could easily use the system stored procedure sp_delete_backuphistory but I instead decided to use PowerShell and dbatools. I just recently started to use dbatools and I want to practice more using PowerShell for tasks like this one that needs to be done on many instances.
First, like the SSMS report I’d like to get the row count and the amount of data used by the backup history tables in all my MSDB databases. I want to measure the actual gain in data space after the cleaning. To do this, I decided to use the Get-DbaDbTable function from dbatools.
Get-DbaDbTable -SqlInstance 'InstanceName' -Database msdb `
| Where-Object {$_.Name -Like 'backup*'} `
| Select-Object -Property Name, RowCount, DataSpaceUsed `
| Out-GridView
I use a Central Management Server as an inventory for my SQL servers.
The list of servers can be easily retrieved from the CMS with Get-DbaRegisteredServer.
$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};
I have 36 production servers.
PS C:\> $Servers.Count 36
Now, looping through each instance I do the sum of the backup history rows with the total space used.
$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};
foreach ($srv in $Servers) {
Get-DbaDbTable -SqlInstance $srv -Database msdb `
| Where-Object {$_.Name -Like 'backup*'} `
| ForEach-Object -Process {$rowsBefore+=$_.RowCount; $sizeBefore+=$_.DataSpaceUsed}
}
Write-Output "backup history total rows: $rowsBefore"
Write-Output "backup history total size: $sizeBefore"
PS C:\>
backup history total rows: 31989560
backup history total size: 10343088
I’ve got a total of almost 32 Million rows of backup history on my production servers for a total data size exceeding 10 GB.
To clean the backup history, I use the Remove-DbaDbBackupRestoreHistory function. I decide to keep a backup history of about 4 months, so I choose the arbitrary number of 120 as value for the KeepDays parameter.
foreach ($srv in $Servers) {
Remove-DbaDbBackupRestoreHistory -SqlInstance $srv -KeepDays 120 -Confirm:$false
}
After cleaning the backup history I run once again the first loop to get the msdb tables information so I can compare the row count and data space used before and after the Remove function.
Here is the result.
Diff rows: 24654309 Diff size: 8047072
I just deleted over 24 Million rows, about 8GB of data space in the msdb databases over 36 instances. All this was done with a few lines of PowerShell and dbatools in a really short time. As a DBA managing dozens of instances, automating and scripting tasks like this with dbatools becomes very easy and can save a lot of time.
You can find below the whole script, please feel free to comment if you think it can be written in a more efficient way. I will take any advice on PowerShell scripting.
$Servers = Get-DbaRegisteredServer -SqlInstance 'MyCmsInstance' | Where-Object {$_.Group -Like '*Prod*'};
foreach ($srv in $Servers) {
Get-DbaDbTable -SqlInstance $srv -Database msdb `
| Where-Object {$_.Name -Like 'backup*'} `
| ForEach-Object -Process {$rowsBefore+=$_.RowCount; $sizeBefore+=$_.DataSpaceUsed}
}
Write-Output "backup history total rows: $rowsBefore"
Write-Output "backup history total size: $sizeBefore"
foreach ($srv in $Servers) {
Remove-DbaDbBackupRestoreHistory -SqlInstance $srv -KeepDays 120 -Confirm:$false
}
Start-Sleep -Seconds 10
foreach ($srv in $Servers) {
Get-DbaDbTable -SqlInstance $srv -Database msdb `
| Where-Object {$_.Name -Like 'backup*'} `
| ForEach-Object -Process {$rowsAfter+=$_.RowCount; $sizeAfter+=$_.DataSpaceUsed}
}
$diffRows= $rowsBefore-$rowsAfter
$diffSize= $sizeBefore-$sizeAfter
Write-Output "Diff rows: $diffRows"
Write-Output "Diff size: $diffSize"
Cet article SQL Server: Quickly clean backup history with dbatools est apparu en premier sur Blog dbi services.
Oracle disables your multitenant option when you run on EC2
I have installed Oracle 19.6 on an EC2 for our Multitenant Workshop training. And of course, during the workshop we create a lot of PDBs. If you don’t have paid for the Enterprise Edition plus the Multitenant Option you can create at most 3 pluggable database. But with this option you can create up to 252 pluggable databases. Does it worth the price, which according to the public price list is USD 47,500 + 17,500 per processor, which means per-core because Oracle doesn’t count the core factor when your Intel processors are in AWS Cloud (according to the Authorized Cloud Environments paper)? Probably not because Oracle detects where you run and bridles some features depending whether you are on the Dark or the Light Side of the public cloud (according to their criteria of course).
At one point I have 3 pluggable databases in my CDB:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ____________ ____________ _____________
2 PDB$SEED READ ONLY NO
3 CDB1PDB01 MOUNTED
4 CDB1PDB03 MOUNTED
5 CDB1PDB02 MOUNTED
I want to create a 4th one:
SQL> create pluggable database CDB1PDB04 from CDB1PDB03;
create pluggable database CDB1PDB04 from CDB1PDB03
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created
It fails. The maximum number of pluggable databases is defined by MAX_PDBS, but I defined nothing in my SPFILE:
SQL> show spparameter max_pdbs
SID NAME TYPE VALUE
--- -------- ------- -----
* max_pdbs integer
I thought that the default was 4098 (which is incorrect anyway as you cannot create more than 4096) but it is actually 5 here:
SQL> show parameter max_pdbs
NAME TYPE VALUE
-------- ------- -----
max_pdbs integer 5
Ok… this parameter is supposed to count the number of user pluggable databases (the ones with CON_ID>2) and I have 3 of them here. The limit is 5 and I have an error mentioning that I’ve reached the limit. That’s not the first time I see wrong maths with this parameter. But there’s worse as I cannot change it:
SQL> alter system set max_pdbs=6;
alter system set max_pdbs=6
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-65334: invalid number of PDBs specified
I can change it in the SPFILE but it doesn’t help me to create more pluggable databases:
SQL> alter system set max_pdbs=200 scope=spfile;
System altered.
SQL> startup force;
Total System Global Area 2147482744 bytes
Fixed Size 9137272 bytes
Variable Size 587202560 bytes
Database Buffers 1543503872 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter max_pdbs
NAME TYPE VALUE
-------- ------- -----
max_pdbs integer 200
SQL> create pluggable database CDB1PDB04 from CDB1PDB03;
create pluggable database CDB1PDB04 from CDB1PDB03
*
ERROR at line 1:
ORA-65010: maximum number of pluggable databases created
Something bridles me. There’s a MOS Note ORA-65010 When Oracle Database Hosted on AWS Cloud (Doc ID 2328600.1) about the same problem but that’s in 12.1.0.2 (before MAX_PDBS was introduced) which is supposed to be fixed in AUG 2017 PSU. But here I am 3 years later in 19.6 (the January 2020 Release Update for the latest version available on-premises).
So, Oracle limits the number of pluggable databases when we are on a public cloud provider which is not the Oracle Public Cloud. This limitation is not documented in the licensing documentation which mentions 252 as the Enterprise Edition limit, and I see nothing about “Authorized Cloud Environments” limitations for this item. This, and the fact that it can come and go with Release Updates put customers at risk when running on AWS EC2: financial risk and availability risk. I think there are only two choices, on long term, when you want to run your database on a cloud: go to Oracle Cloud or leave for another Database.
How does the Oracle instance know on which public cloud you run? All cloud platforms provide some metadata through HTTP api. I have straced all sendto() and recvfrom() system calls when starting the instance:
strace -k -e trace=recvfrom,sendto -yy -s 1000 -f -o trace.trc sqlplus / as sysdba <<<'startup force'
And I searched for Amazon and AWS here:
This is clear: the instance has a function to detect the cloud provider (kgcs_clouddb_provider_detect) when initializing the SGA in a multitenant architecture (kpdbInitSga) with the purpose of detecting non-oracle clouds (kscs_is_non_oracle_cloud). This queries the AWS metadata (documented on Retrieving Instance Metadata):
[oracle@ora-cdb-1 ~]$ curl http://169.254.169.254/latest/meta-data/services/domain
amazonaws.com/
When Oracle software sees the name of the enemy in the domain name amazonaws.com, it sets an internal limit for the number of pluggable databases that overrides the MAX_PDBS setting. Ok, I don’t need this metadata and I’m root on EC2 so my simple workaround is to block this metadata API:
[root@ora-cdb-1 ~]# iptables -A OUTPUT -d 169.254.169.254 -j REJECT
[root@ora-cdb-1 ~]# iptables -L
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
REJECT udp -- anywhere 10.0.0.2 udp dpt:domain reject-with icmp-port-unreachable
REJECT all -- anywhere 10.0.0.2 reject-with icmp-port-unreachable
Then restart the instance and it works: I can set or reset MAX_PDBS and create more pluggable databases.
I can remove the rule
[root@ora-cdb-1 ~]# iptables -D OUTPUT -d 169.254.169.254 -j REJECT
If, for watever reason I want to revert back.
Finally, because they had many bugs with the MAX_PDBS soft limit, there’s a parameter to disable it and this disables also the hard limit:
SQL> alter system set "_cdb_disable_pdb_limit"=true scope=spfile;
System altered.
Thanks to Mauricio Melnik for the heads-up on that:
MOS 2538172.1 _cdb_disable_pdb_limit=true …. maybe?
— Mauricio Melnik (@maurimelnik) March 26, 2020
However, with this parameter you cannot control anymore the maximum number of PDBs so don’t forget to monitor your AUX_COUNT in DBA_FEATURE_USAGE_STATISTICS.
Here was my discovery when preparing the multitenant workshop lab environment. Note that given the current situation where everybody works from home when possible, we are ready to give this training full of hands-on exercises though Microsoft Teams and AWS EC2 virtual machines. Two days to be comfortable when moving to CDB architecture, which is what should be done this year when you plan to stay with Oracle Database for the future versions.
Update 27-MAR-2020In order not to sound too negative here, this limit on AWS platforms has been removed in the past and this may be a bug re-introduced with the change from 1 to 3 PDBs in Standard Edition.
Cet article Oracle disables your multitenant option when you run on EC2 est apparu en premier sur Blog dbi services.
Sending PostgreSQL data to AWS S3 by using AWS Kinesis data streams
Before we really start with this post: This is just an experiment and you should not implement it like this in real life. The goal of this post is just to show what is possible and I am not saying that you should do it (the way it is implemented here will be catastrophic for your database performance and it is not really secure). As I am currently exploring a lot of AWS services I wanted to check if there is an easy way to send data from PostgreSQL into an AWS Kinesis data stream for testing purposes and it turned out that this is actually quite easy if you have the AWS Command Line Interface installed and configured on the database server.
Creating a new Kinesis stream in AWS is actually a matter of a few clicks (of course you can do that with the command line utilities as well):
What I want is a simple data stream where I can put data into:
Obviously the new stream needs a name and as I will not do any performance or stress testing one shard is absolutely fine:
That’s all what needs to be done, the new stream is ready:
An AWS Kinesis stream is not persistent by default. That means, if you want to permanently store the output of a stream you need to connect the stream to a consumer that processes, eventually transforms, and finally stores the data somewhere. For this you can use AWS Kinesis Firehose and this is what I’ll be doing here:
As I want to use AWS S3 as the target for my data I need to use a delivery stream:
The delivery stream needs a name as well and we will use the stream just created above as the source:
We could go ahead and transform the data with an AWS Lambda function but we’re going to keep it simple for now and skip this option:
The next screen is about the target for the data. This could be AWS Redshift, AWS Elasticsearch, Splunk or AWS S3, what we’ll be doing here:
Finally specifying the target S3 bucket:
The settings for buffering at not really important for this test but will matter for real systems as these settings determine how fast your data is delivered to S3 (we also do not care about encryption and compression for now):
Error logging should of course be enabled and we need an IAM role with appropriate permissions:
Final review:
… and the stream and delivery stream are ready to use:
That’s it for the setup on the AWS side and we can continue with configuring PostgreSQL to call the AWS command line utility to write data to the stream. Callling system commands from inside PostgreSQL can be done in various ways, we’ll be using pl/Perl for that, and even the untrusted version so only superusers will be able to do that:
postgres=# create extension plperlu;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+----------------------------------------
plperlu | 1.0 | pg_catalog | PL/PerlU untrusted procedural language
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
Next we need a table that will contain the data we want to send to the stream:
postgres=# create table stream_data ( id serial primary key
, stream text );
A trigger will fire each time a new row is inserted and the trigger function will call the AWS command line interface:
create or replace function f_send_to_kinesis()
returns trigger
language plperlu
AS $$
system('aws kinesis put-record --stream-name postgres-to-kinesis --partition-key 1 --data '.$_TD->{new}{stream});
return;
$$;
create trigger tr_test
after insert or update
on stream_data
for each row
execute procedure f_send_to_kinesis();
This is all we need. Let’s insert a row into the table and check if it arrives in AWS S3 (remember that it will take up to 300 seconds or 5MB of data):
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# insert into stream_data (stream) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
INSERT 0 1
postgres=# select * from stream_data;
id | stream
----+----------------------------------
1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
3 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
5 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
6 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
7 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
8 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
9 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
(9 rows)
You will also notice that the insert takes quite some time because calling the AWS command line utility and waiting for the result takes ages compared to a normal insert.
While waiting for the data to arrive you can check the monitoring section of both, the stream and the delivery stream:
After a while the data appears in S3 and it is organized in [YEAR]/[MONTH]/[DAY]/[HOUR]:
Looking at the file itself all our data is there:
So, actually it is quite easy to send data to an AWS Kinesis stream. If you really need to send data out of PostgreSQL I probably would go for listen/notify to make the calls to the AWS command line utility not blocking the inserts or updates to the table that holds the data for the stream. Anyway, currently I am not aware of a good use case for sending streams of data out of PostgreSQL directly to AWS Kinesis. Maybe you do something like that and how?
Cet article Sending PostgreSQL data to AWS S3 by using AWS Kinesis data streams est apparu en premier sur Blog dbi services.
PostgreSQL message levels
When you start to write business logic in the database by using triggers or functions/procedures you usually want to report messages to the user that runs your code or you want to include some debugging output for your own. In Oracle a lot of people use the dbms_output package to return simple messages to the user’s screen. In PostgreSQL you can do the same but of course the way it is implemented is not the same. There are basically two parameters that control how much output is returned to the client or to the server’s log file: client_min_messages and log_min_messages.
Let’s start with client_min_messages: The default value of this parameter is ‘NOTICE’ and these are the valid values you cant set:
- DEBUG5
- DEBUG4
- DEBUG3
- DEBUG2
- DEBUG1
- LOG
- NOTICE
- WARNING
- ERROR
A given level excludes all the lower levels, e.g. “LOG” would exclude all the “DEBUG*” levels and the default “NOTICE” will exclude “LOG” as well. The DEBUG* levels are usually not required and if you check the PostgreSQL source code you’ll notice that the highest level of information (DEBUG5) is not used that often while lower levels are used more often:
postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG5" * | grep -v sgml | wc -l 20 postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG4" * | grep -v sgml | wc -l 78 postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG3" * | grep -v sgml | wc -l 64 postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG2" * | grep -v sgml | wc -l 236 postgres@centos8pg:/home/postgres/postgresql/ [pg13] grep -r "DEBUG1" * | grep -v sgml | wc -l 221
Setting the highest level would give a lot of information even for basic tasks:
postgres=# set client_min_messages = 'DEBUG5'; DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0 SET postgres=# create table tt1 ( a int ); DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 559/1/1 CREATE TABLE postgres=# create index ii1 on tt1 (a); DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 DEBUG: building index "ii1" on table "tt1" serially DEBUG: index "ii1" can safely use deduplication DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 560/1/2 CREATE INDEX
Lowering that to “DEBUG1” already reduces the amount of messages:
postgres=# set client_min_messages = 'DEBUG1'; SET postgres=# create table tt2 ( a int ); CREATE TABLE postgres=# create index ii2 on tt2 (a); DEBUG: building index "ii2" on table "tt2" serially DEBUG: index "ii2" can safely use deduplication CREATE INDEX postgres=#
You can use most of the levels to control how much information is given back by your code as well. Let’s assume we have a function like this:
postgres=# create or replace function f_test_msg () returns void postgres-# as postgres-# $$ postgres$# declare postgres$# begin postgres$# raise debug 'This is a DEBUG message'; postgres$# end; postgres$# $$ language plpgsql; CREATE FUNCTION
With the default setting of ‘NOTICE’ for client_min_messages you would not see any output when you execute the function:
postgres=# show client_min_messages; client_min_messages --------------------- notice (1 row) postgres=# select f_test_msg(); f_test_msg ------------ (1 row)
Setting client_min_messages to the appropriate level will give you the output:
postgres=# set client_min_messages = 'DEBUG'; SET postgres=# select f_test_msg(); DEBUG: This is a DEBUG message f_test_msg ------------ (1 row) postgres=#
It does not really matter to with “DEBUG” level you set the parameter, you’ll get the “raise debug” for all those levels:
postgres=# set client_min_messages = 'DEBUG1'; SET postgres=# select f_test_msg(); DEBUG: This is a DEBUG message f_test_msg ------------ (1 row) postgres=# set client_min_messages = 'DEBUG2'; SET postgres=# select f_test_msg(); DEBUG: This is a DEBUG message f_test_msg ------------ (1 row) postgres=#
In the documentation of RAISE the only DEBUG level is “DEBUG” anyway. Using this method you can easily control the amount of messages your code will return, e.g.:
postgres=# create or replace function f_test () returns void postgres-# as postgres-# $$ postgres$# declare postgres$# begin postgres$# raise debug 'This is a DEBUG message'; postgres$# raise log 'This is a LOG message'; postgres$# raise notice 'This is an NOTICE message'; postgres$# raise warning 'This is a WARNING message'; postgres$# end; postgres$# $$ language plpgsql; CREATE FUNCTION
Depending on you current setting of client_min_messages you’ll get more or less messages:
postgres=# set client_min_messages = 'WARNING'; SET postgres=# select f_test(); WARNING: This is a WARNING message f_test -------- (1 row) postgres=# set client_min_messages = 'NOTICE'; SET postgres=# select f_test(); NOTICE: This is an NOTICE message WARNING: This is a WARNING message f_test -------- (1 row) postgres=# set client_min_messages = 'LOG'; SET postgres=# select f_test(); LOG: This is a LOG message NOTICE: This is an NOTICE message WARNING: This is a WARNING message f_test -------- (1 row) postgres=# set client_min_messages = 'DEBUG'; SET postgres=# select f_test(); DEBUG: This is a DEBUG message LOG: This is a LOG message NOTICE: This is an NOTICE message WARNING: This is a WARNING message f_test -------- (1 row)
Use that wisely and you will easily be able to debug your code by just setting the correct level for the information you want to get.
log_min_messages on the other side controls how much information is written to PostgreSQL’s log file. There are two more levels you can set than for client_min_messages but the logic is the same:
- DEBUG5
- DEBUG4
- DEBUG3
- DEBUG2
- DEBUG1
- INFO
- NOTICE
- WARNING
- ERROR
- LOG
- FATAL
The default is “WARNING” so you might want to lower that if you need more information in the log file or you need to collect debugging information.
Cet article PostgreSQL message levels est apparu en premier sur Blog dbi services.
Documentum – Rename of Lockbox on RCS/CFS
As you probably know, Documentum introduced the optional use of a Lockbox since version 7. It was done initially to contribute to the security of the AEK key which is one of the central components of a Content Server. Time showed that, well, it’s not so easy and since then, nothing much changed on the Lockbox layer. With the version 16.4, OpenText introduced some new things regarding the Lockbox like the upgrade from version 3 to 4. On this blog, I will talk about one of the changes and that is renaming the file on RCS/CFS in case there is already one present with the same name. This blog will not discuss a technical issue because it’s not an issue, but it might become one, so I just wanted to share some thoughts about that.
As you know, when installing a repository in Documentum, you can either choose to use the basic AEK key or use a Lockbox that will contain it. Usually, people tend to use the basic AEK key, most probably because of the Documentum history: most companies are using Documentum for a very long time and therefore, there were no other choice at the beginning and it’s only an optional step to upgrade the AEK key to the Lockbox so it’s often left on the side. The second trend is to use a single Lockbox for all repositories of a specific server (Global Registry Repository + normal Repository(ies)). If you are in this case, then installing a Primary/First CS is the same thing as installing a Remote/Second CS (aka CFS). Or rather it was the case until Documentum 7.3 included.
Before talking about what changed, it’s important to know how Documentum works exactly in regard to the Lockbox. While installing an RCS/CFS, the installer will connect to the Primary CS to read data from the Repository. In the installation process, it will also execute the script “$DM_HOME/install/admin/dm_rcs_copyfiles.ebs”. What this script does is:
- Connect to the Primary CS
- Create a dm_method named dm_rcs_copyfiles
- Execute the dm_method dm_rcs_copyfiles on the Primary CS
- The above dm_method will create a dm_document named dm_rcs_copydoc that will contain all the global files of the Repository:
- AEK key or Lockbox file (page 0)
- dbpasswd.txt (page 1)
- server.ini (page 2)
- webcache.ini (page 3)
- rkmrootcert.pem – if any
- rkm_config.ini – if any
- ldap_*.cnt – if any (7.3+)
- Download the files from the dm_document on the RCS/CFS to a temporary folder
- Destroy the dm_document and dm_method
Therefore, in regard to the Lockbox, it will basically take the file from the Repository on the Primary CS and put it on the RCS/CFS for usage. This is needed because of encryption mechanisms that uses the Lockbox. So, what changed exactly? Well, until Documentum 7.3, the RCS/CFS did copy the Lockbox from the Primary CS for each and every Repositories but it was just overwriting any files with the same name at the same location. Therefore, if you are using the default $DOCUMENTUM/dba/secure/lockbox.lb name on the Primary CS and using it for all Repositories, then on the RCS/CFS, it would just create one Lockbox file with the same name at the same location and overwriting it each and every time a new RCS/CFS is created.
You can potentially see what could go wrong with that. Let’s say that you have two different environments:
- a first one with GR_A and RepoA, both using the same lockbox.lb
- a second one with GR_B and RepoB, both using the same lockbox.lb
With the above, if you were trying to install an RCS/CFS for RepoA and then for RepoB using the same binaries on the same server, then the Lockbox of RepoA would be overwritten by the one of RepoB. The change that has been introduced in 16.4 (it might have been backported to some latest patches of 7.3 but at least it wasn’t there in the early patches) is that when you are installing a RCS/CFS, if a Lockbox already exist with the same name (lockbox.lb), then the installer will rename it to <RepoName>_lockbox.lb on the RCS/CFS and it will then update the server.ini to match the new name obviously. This means that the first RCS/CFS installed on the remote server (usually the Global Registry Repository) will continue to use lockbox.lb because it doesn’t exist at that time BUT the second RCS/CFS (usually the normal Repository) will use the <RepoName>_lockbox.lb file because lockbox.lb is already taken… I assume that the above problem happened for some customers of OpenText and therefore, this solution/workaround was probably implemented. Another possibility is that they needed that for the cloud setup to make sure no repositories overlap each other’s.
So, in the end, this is a as-good-as-it-can-be solution for this specific installation problem. However, if you are part the second group of people that usually use a single Lockbox for all Repositories of a single host and you don’t mix-up environments, then you might want to keep the old name. There are already a lot of differences between a Primary CS and a CFS/RCS (start script name, server.ini name, log file name) so unless there is a good reason (i.e.: if it’s needed), I would personally continue to use lockbox.lb for all Repositories of the RCS/CFS. This is in the perspective of keeping the servers aligned as much as possible to simplify the maintenance work. Obviously, the final decision is up to you.
If you want to keep using the same Lockbox on the RCS/CFS for all Repositories, then after the installation, you can just update the server.ini. Please note that we are always using server.ini on all Content Servers, no matter if it’s a Primary or a Remote. On RCS/CFS, this is actually a symlink to the real file name to simplify our work and therefore the below command uses the “–follow-symlinks” option:
[dmadmin@cs-1 ~]$ repo=Repo1
[dmadmin@cs-1 ~]$ lockbox_name=lockbox.lb
[dmadmin@cs-1 ~]$
[dmadmin@cs-1 ~]$ ls -l $DOCUMENTUM/dba/config/${repo}/server.ini
lrwxrwxrwx 1 dmadmin dmadmin 67 Oct 14 12:03 $DOCUMENTUM/config/${repo}/server.ini -> $DOCUMENTUM/config/${repo}/server_cs-1_${repo}.ini
[dmadmin@cs-1 ~]$
[dmadmin@cs-1 ~]$ grep "crypto_" $DOCUMENTUM/dba/config/${repo}/server.ini
crypto_mode = AES256_RSA1024_SHA256
crypto_keystore = Local
crypto_lockbox = Repo1_lockbox.lb
crypto_keyname = CSaek
[dmadmin@cs-1 ~]$
[dmadmin@cs-1 ~]$ sed -i --follow-symlinks "s/^\(crypto_lockbox[[:space:]]*=[[:space:]]*\).*/\1${lockbox_name}/" $DOCUMENTUM/dba/config/${repo}/server.ini
[dmadmin@cs-1 ~]$
[dmadmin@cs-1 ~]$ grep crypto_ $DOCUMENTUM/dba/config/${repo}/server.ini
crypto_mode = AES256_RSA1024_SHA256
crypto_keystore = Local
crypto_lockbox = lockbox.lb
crypto_keyname = CSaek
[dmadmin@cs-1 ~]$
Then simply restart the Repository. In case you made a mistake and the file lockbox.lb wasn’t the same for all Repositories, then it just won’t start. That was just my 2-cents on this change, if you are a different opinion, feel free to share! I didn’t test because I usually use the Lockbox, but I assume OpenText implemented the same thing for the AEK key?
Cet article Documentum – Rename of Lockbox on RCS/CFS est apparu en premier sur Blog dbi services.
Documentum – dm_DMFilescan fails with invalid method_verb?
Recently in a project, one of the Documentum environments we were working on started showing failure on the execution of the dm_DMFilescan job and only this one. After a couple minutes of investigation, I quickly found the root cause of the issue which wasn’t very important, but I thought I would share it because of the implications it has. In addition to that, it’s also not the first time I’m seeing this kind of issue so let’s dig into it.
The error shown in the job log was the following one:
[dmadmin@cs-0 ~]$ cd $DOCUMENTUM/dba/log/000f1234/sysadmin [dmadmin@cs-0 sysadmin]$ [dmadmin@cs-0 sysadmin]$ cat ../agentexec/job_080f12348000035c Sun Mar 15 08:50:32 2020 [INFORMATION] [LAUNCHER 25934] Detected while preparing job dm_DMFilescan for execution: Agent Exec connected to server Repo01: [DM_SESSION_I_SESSION_START]info: "Session 010f123480032063 started for user dmadmin." [dmadmin@cs-0 sysadmin]$ [dmadmin@cs-0 sysadmin]$ cat DMFilescanDoc.txt DMFilescan Report For DocBase Repo01 As Of 3/15/2020 09:50:37 Remove orphan content older than 168 hours. Generated DMFilescan script will be executed... The trace level is set to 0... DMFilescan utility syntax: apply,c,NULL,DO_METHOD,METHOD,S,dmfilescan,ARGUMENTS,S,'-grace_period 168 ' Executing DMFilescan... Unable to execute DMFilescan method... [DM_METHOD_E_INVALID_MTHD_VERB]error: "The dm_method named (dmfilescan) of type dmbasic has invalid method_verb (./dmfilescan)." Exiting... Report End 3/15/2020 09:50:37 [dmadmin@cs-0 sysadmin]$
Before explaining what happened, I think it’s necessary to take some time to explain how the execution of the dm_DMFilescan is done. The job “dm_DMFilescan” is a content type job which is used to scan storage areas in order to find files that do not have any relation to database objects. You can use it in report mode only (nothing done) or not for example. There are more details on the OpenText documentation but the thing to note here is that this job will execute the method of the same name: “dm_DMFilescan”. This method is a dmbasic one and it’s actually just an encapsulation. What I mean by that is it will actually execute a common dmbasic for several jobs, the only thing that changes are the parameters of the method. There are four jobs that will use the exact same dmbasic script and you can find the list easily:
[dmadmin@cs-0 sysadmin]$ iapi ${repo} -Udmadmin -Pxxx << EOC
> ?,c,select object_name, method_verb from dm_method where method_verb like '%mthd1.ebs%'
> EOC
OpenText Documentum iapi - Interactive API interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0170.0080
Connecting to Server using docbase Repo01
[DM_SESSION_I_SESSION_START]info: "Session 010f123480032086 started for user dmadmin."
Connected to OpenText Documentum Server running Release 16.4.0170.0234 Linux64.Oracle
Session id is s0
API> object_name method_verb
-------------------- ---------------------------------------------------------
dm_ContentWarning ./dmbasic -f../install/admin/mthd1.ebs -eContentWarning
dm_DMClean ./dmbasic -f../install/admin/mthd1.ebs -eDMClean
dm_DMFilescan ./dmbasic -f../install/admin/mthd1.ebs -eDMFilescan
dm_DMArchive ./dmbasic -f../install/admin/mthd1.ebs -eDMArchive
(4 rows affected)
API> Bye
[dmadmin@cs-0 sysadmin]$
Take a look at these “mthdx.ebs” files if you aren’t familiar with them, it’s always good to know how it actually works. This dmbasic method will “only” prepare and execute another method which is then specific to each of the jobs. For the “dm_DMFilescan” dmbasic method for example, it will execute the “dmfilescan” method. This second method isn’t a dmbasic one, it’s a binary. So why am I saying all that? Well it’s simply to explain where the issue is coming from… When reading the error message from the log file, one might thing at first sight that the issue is with the “dm_DMFilescan” method but actually it’s not. The issue was with the second method and if you read it carefully, you actually have all key items to solve it. As said, the “dmfilescan” method is executing a binary but on the error message above, it is saying that this method is of type dmbasic currently. Comparing the final method for the “dm_DMFilescan” and “dm_DMClean” jobs, you can see the error:
[dmadmin@cs-0 sysadmin]$ iapi ${repo} -Udmadmin -Pxxx << EOC
> ?,c,select object_name, method_type, method_verb from dm_method where object_name in ('dmfilescan','dmclean')
> EOC
OpenText Documentum iapi - Interactive API interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0170.0080
Connecting to Server using docbase Repo01
[DM_SESSION_I_SESSION_START]info: "Session 010f12348003209b started for user dmadmin."
Connected to OpenText Documentum Server running Release 16.4.0170.0234 Linux64.Oracle
Session id is s0
API> object_name method_type method_verb
-------------------- ------------- --------------
dmclean ./dmclean
dmfilescan dmbasic ./dmfilescan
(2 rows affected)
API> Bye
[dmadmin@cs-0 sysadmin]$
What happened, in my opinion, is that someone opened the properties of the “dmfilescan” method in Documentum Administrator but instead of using “Cancel” to close it, he clicked on “OK” which then saved the current configuration. Unfortunately, with DA, there are often cases where opening a property page can load values that aren’t currently configured and therefore when you click on “OK”, it will save back a value that wasn’t the initial one… I have seen that quite often on job’s target_server for example when working on HA environment: you have a job configured to run on ANY (‘ ‘) and while opening it on DA, it can show the Primary CS target (‘Repo.Repo@cs-0’ for example). Therefore, while clicking on “OK”, it will change the target_server of this job. I believe this is what happened here for this specific method because by default it’s a binary and therefore the method_type should be ‘ ‘ but in this case, it was changed recently to dmbasic. Changing the method_type back to the correct value and the job is working again:
[dmadmin@cs-0 sysadmin]$ iapi ${repo} -Udmadmin -Pxxx << EOC
> ?,c,update dm_method object set method_type=' ' where object_name='dmfilescan'
> EOC
OpenText Documentum iapi - Interactive API interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0170.0080
Connecting to Server using docbase Repo01
[DM_SESSION_I_SESSION_START]info: "Session 010f1234800320a7 started for user dmadmin."
Connected to OpenText Documentum Server running Release 16.4.0170.0234 Linux64.Oracle
Session id is s0
API> objects_updated
---------------
1
(1 row affected)
[DM_QUERY_I_NUM_UPDATE]info: "1 objects were affected by your UPDATE statement."
API> Bye
[dmadmin@cs-0 sysadmin]$
[dmadmin@cs-0 sysadmin]$ cat DMFilescanDoc.txt | grep -v ^#
DMFilescan Report For DocBase Repo01 As Of 3/15/2020 10:03:21
Remove orphan content older than 168 hours.
Generated DMFilescan script will be executed...
The trace level is set to 0...
DMFilescan utility syntax: apply,c,NULL,DO_METHOD,METHOD,S,dmfilescan,ARGUMENTS,S,'-grace_period 168 '
Executing DMFilescan...
Shell was successful!
Generated script from the DMFilescan method:
----- Start $DOCUMENTUM/dba/log/000f1234/sysadmin/080f12348000035c.bat output ------------------------
------- End $DOCUMENTUM/dba/log/000f1234/sysadmin/080f12348000035c.bat output ------------------------
Destroying DMFilescan script with ID 090f12348003d932...
Report End 3/15/2020 10:05:27
[dmadmin@cs-0 sysadmin]$
So, take care while using Documentum Administrator because it might surprise you.
Cet article Documentum – dm_DMFilescan fails with invalid method_verb? est apparu en premier sur Blog dbi services.
Migrating an Oracle instance to AWS Aurora – 4 – AWS DMS Change Data Capture (CDC)
This is the last post in this little series about migrating on Oracle instance to AWS Aurora with PostgreSQL compatibility. For the previous one you can check here, here and here. In this post we’ll look at how you can configure change data capture so that changes on the source Oracle instance are automatically replicated to the target Aurora instance. In a real life migration this is what you probably need as long down times are usually not acceptable. We’ll be starting from exactly the same state we left of in the previous post.
We’ve initially loaded the source “SH” schema from Oracle to AWS Aurora and now we want to replicate all the changes that are happening on the source to the target as well. Remember that AWS DMS sits between the source and the target and the we need a source end point and a target end point:
We can use exactly the same end points as in the previous post (nothing to add or modify here):
What we need to configure is a new database migration task. We still have the task we used to do the initial loading which could be modified but I prefer to keep it separated and will go ahead with a new task:
We’ll be using the same replication instance and the same end points but “Migration type” will be “Replicate data changes only”. We already enabled “supplemental logging” in the last post so the warning can be ignored:
We’ll go with the default task settings:
As I know that nothing happened on the source I do not need to specify a specific start point for the replication. The same is true for the tables on the target: I already know that the tables are there and that the content of the tables on the target is the same as on the source. For the LOB stuff I’ll go with the defaults as well.
The table mappings section is configured the same as we did it for the initial load previously. We want to replicate all tables in the “SH” schema:
Finally all the defaults for the remaining parameters of the task:
While the task is creating:
… you’ll see that LogMiner is starting on the source Oracle instance:
2020-03-23T08:44:08.261039+01:00 Thread 1 advanced to log sequence 39 (LGWR switch) Current log# 3 seq# 39 mem# 0: /u03/oradata/DB19/redog3m1DB19.dbf Current log# 3 seq# 39 mem# 1: /u04/oradata/DB19/redog3m2DB19.dbf 2020-03-23T08:44:08.398220+01:00 ARC2 (PID:4670): Archived Log entry 15 added for T-1.S-38 ID 0x2c7a7c94 LAD:1 2020-03-23T08:54:41.598409+01:00 LOGMINER: summary for session# = 2147484673 LOGMINER: StartScn: 1084978 (0x0000000000108e32) LOGMINER: EndScn: 1085221 (0x0000000000108f25) LOGMINER: HighConsumedScn: 0 LOGMINER: PSR flags: 0x0 LOGMINER: Session Flags: 0x4000441 LOGMINER: Session Flags2: 0x0 LOGMINER: Read buffers: 4 LOGMINER: Region Queue size: 256 LOGMINER: Redo Queue size: 4096 LOGMINER: Memory LWM: limit 10M, LWM 12M, 80% LOGMINER: Memory Release Limit: 0M LOGMINER: Max Decomp Region Memory: 1M LOGMINER: Transaction Queue Size: 1024 2020-03-23T08:54:41.627157+01:00 LOGMINER: Begin mining logfile for session -2147482623 thread 1 sequence 39, /u03/oradata/DB19/redog3m1DB19.dbf 2020-03-23T08:54:46.780204+01:00 LOGMINER: summary for session# = 2147485697 LOGMINER: StartScn: 1085020 (0x0000000000108e5c) LOGMINER: EndScn: 1085288 (0x0000000000108f68) LOGMINER: HighConsumedScn: 0 LOGMINER: PSR flags: 0x0 LOGMINER: Session Flags: 0x4000441 LOGMINER: Session Flags2: 0x0 LOGMINER: Read buffers: 4 LOGMINER: Region Queue size: 256 LOGMINER: Redo Queue size: 4096 LOGMINER: Memory LWM: limit 10M, LWM 12M, 80% LOGMINER: Memory Release Limit: 0M LOGMINER: Max Decomp Region Memory: 1M LOGMINER: Transaction Queue Size: 1024 2020-03-23T08:54:46.788212+01:00 LOGMINER: Begin mining logfile for session -2147481599 thread 1 sequence 39, /u03/oradata/DB19/redog3m1DB19.dbf 2020-03-23T08:54:51.861617+01:00 LOGMINER: summary for session# = 2147486721 LOGMINER: StartScn: 1085087 (0x0000000000108e9f) LOGMINER: EndScn: 1085362 (0x0000000000108fb2) LOGMINER: HighConsumedScn: 0 LOGMINER: PSR flags: 0x0 LOGMINER: Session Flags: 0x4000441 LOGMINER: Session Flags2: 0x0 LOGMINER: Read buffers: 4 LOGMINER: Region Queue size: 256 LOGMINER: Redo Queue size: 4096 LOGMINER: Memory LWM: limit 10M, LWM 12M, 80% LOGMINER: Memory Release Limit: 0M LOGMINER: Max Decomp Region Memory: 1M LOGMINER: Transaction Queue Size: 1024 2020-03-23T08:54:51.868450+01:00 LOGMINER: Begin mining logfile for session -2147480575 thread 1 sequence 39, /u03/oradata/DB19/redog3m1DB19.dbf
The task itself will report it’s status as “Replication ongoing” a few moments later:
Having a look at the table statistics section of the tasks of course all is reported as zero at the moment:
Time to do some changes in Oracle and check if these changes will be properly replicated to the Aurora target instance. Let’s create a new table on the Oracle side:
SQL> create table sh.test ( a number ); Table created.
This table will show up in the AWS DMS console quite fast:
… and is also available on the target Aurora side:
postgres=> \d "SH"."TEST"
Table "SH.TEST"
Column | Type | Modifiers
--------+----------------+-----------
A | numeric(38,10) |
postgres=> select * from "SH"."TEST";
A
---
(0 rows)
Inserting data on the source:
SQL> insert into sh.test values(1); 1 row created. SQL> commit; Commit complete.
… and nothing happens on the target side. Why does DDL succeed but DML not? Well, first of all it takes some time for the changes to show up in the console. Doing some more inserts on the source and waiting some time:
SQL> insert into sh.test values(2); 1 row created. SQL> commit; Commit complete. SQL> insert into sh.test values(3); 1 row created. SQL> commit; Commit complete.
… the changes are actually recorded:
… but nothing arrives on the target:
postgres=> select * from "SH"."TEST"; A --- (0 rows)
Why that? Let’s do some more inserts:
SQL> insert into sh.test select -1 from dba_objects; 23522 rows created. SQL> commit; Commit complete.
This is reported as “1” insert in the console as the number of inserts switched from 6 to 7:
Some picture, nothing there on the target. The issues was, that supplemental logging needs to be enabled for each table or on the database level, and this is what I did:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
Re-creating the table on Oracle side:
SQL> drop table sh.test; Table dropped. SQL> create table sh.test ( a number primary key ); Table created. SQL> insert into sh.test values(1); 1 row created. SQL> insert into sh.test values(2); 1 row created. SQL> commit; Commit complete. SQL>
From now on new data is showing up in the target Aurora instance:
postgres=> select * from "SH"."TEST";
A
--------------
1.0000000000
2.0000000000
(2 rows)
postgres=>
So, again, quite easy to setup. Of course the usual bits apply as for every logical replication: You should have primary keys or at least unique keys on the source tables for the replication to run performant and smooth. Another point to add here: When you check the settings of the replication task there is one important parameter that should be enabled (you need to stop the task, otherwise it can not be modified):
Enabling this will create additional tables in the target Aurora instance and these give you more information on what is going on with the replication:
postgres=> select * from pg_tables where tablename like 'awsdms%'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity -------------+-------------------------+------------+------------+------------+----------+-------------+------------- public | awsdms_apply_exceptions | postgres | | f | f | f | f aws_postgis | awsdms_apply_exceptions | postgres | | f | f | f | f public | awsdms_history | postgres | | t | f | f | f public | awsdms_status | postgres | | t | f | f | f public | awsdms_suspended_tables | postgres | | t | f | f | f (5 rows)
Here is an example of the replication history:
postgres=> select * from awsdms_history;
server_name | task_name | timeslot_type | timeslot | timeslot_duration | timeslot_latency | timeslot_records | timeslot_volume
-----------------------+----------------------------+-------------------+---------------------+-------------------+------------------+------------------+-----------------
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 09:42:29 | 0 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 09:47:29 | 5 | 0 | 70592 | 28
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 09:52:29 | 5 | 0 | 6 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 09:57:30 | 5 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 10:02:31 | 5 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 10:07:31 | 5 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 10:12:32 | 5 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 10:17:32 | 5 | 0 | 0 | 0
localhost.localdomain | XEBQFBY6EH6SQNN6CBBMO25364 | CHANGE PROCESSING | 2020-03-23 10:22:39 | 0 | 0 | 0 | 0
(9 rows)
These tables can be used to monitor the replication, especially the apply exceptions.
To conclude this final post: The AWS Schema Conversion Tool is a great help for converting the schema and even comes with some Oracle compatibility. Use it, it saves a lot of manual work. AWS DMS on the other side is really easy to implement, the initial load is really easy to setup and change data capture works as expected. Of course this was only a playground and real issues will pop up when you do a real migration, especially when you have to migrate business logic inside the database.
Cet article Migrating an Oracle instance to AWS Aurora – 4 – AWS DMS Change Data Capture (CDC) est apparu en premier sur Blog dbi services.
Documentum – LSS registerOracleVersionView script with wrong content
As discussed in a previous blog, working with LSS might prove a little bit challenging from time to time. In this blog, I wanted to share an error I saw while installing LSS 16.6.1 on an Oracle database. Initially, I developed my silent installation for LSS (while encapsulate the LSS silent scripts provided by OpenText) using a PostgreSQL database because it’s usually easier to setup an environment on Kubernetes with PG because of licenses.
So, the silent installation scripts were created several months ago and working since then, apparently. Recently, I had to execute manually my silent install script of LSS on an environment which was using an Oracle database. The script completed properly, my automatic log file checking didn’t show any sign or errors or anything so for me it was fully installed. However, I still did a review of the logs printed on the screen to be sure and I did see a new “error” I wasn’t familiar with. I’m not sure you can call that an error because it’s just one line drowned in the flood of logs printed without any “ERROR” or “_E_” messages but it is clearly an error from a Linux point of view:
... ./registerOracleVersionView.sh: line 1: oracle: command not found ...
This message never appeared in the generated log file of the LSS installation, it’s only displayed on the screen, which makes it… quite difficult to see in automation. So, anyway, what’s the issue this time? Well looking at the message, it’s clear that the shell script has a wrong content because it is trying to execute a command “oracle” which doesn’t exist. Where is this file? What’s its content?
[dmadmin@cs-0 ~]$ workspace="/tmp/lss/"
[dmadmin@cs-0 ~]$
[dmadmin@cs-0 ~]$ cd ${workspace}/*/
[dmadmin@cs-0 LSSuite]$
[dmadmin@cs-0 LSSuite]$ ls -l *.sh
-rwxr-x--- 1 dmadmin dmadmin 13479 Oct 4 09:15 LSConfigImport.sh
-rwxr-x--- 1 dmadmin dmadmin 4231 Oct 4 09:15 iHubConfigImport.sh
-rwxr-x--- 1 dmadmin dmadmin 8384 Oct 4 09:15 install.sh
-rwxr-x--- 1 dmadmin dmadmin 3096 Oct 4 09:15 myInsightPostInstall.sh
[dmadmin@cs-0 LSSuite]$
[dmadmin@cs-0 LSSuite]$ find . -name registerOracleVersionView.sh
./scripts/registerOracleVersionView.sh
[dmadmin@cs-0 LSSuite]$
[dmadmin@cs-0 LSSuite]$ cd ./scripts/
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ cat registerOracleVersionView.sh
if "$4" == "oracle"
then
idql "$1" -U"$2" -P"$3" -R"./scripts/$4/oracleVersion.dql"
fi
[dmadmin@cs-0 scripts]$
If you are familiar with bash/shell scripting, you probably already saw what’s wrong with the script. It’s simply that this isn’t the correct way to write IF statements. I won’t go into the details of the correct formatting (one bracket, two brackets, with test command, aso…) because there are already plenty of documentation around that online but that’s definitively not a correct way to write IF statements. So, to correct this script, I opened the OpenText SR#4450083 and provided them the commands to fix it in a future patch/release. I didn’t receive a confirmation yet but it should be in the next LSS release. In the meanwhile, I put the workaround on my silent install script (if the correct format is already there it won’t be anything but if it’s not, then it will correct the file):
[dmadmin@cs-0 scripts]$ cat registerOracleVersionView.sh
if "$4" == "oracle"
then
idql "$1" -U"$2" -P"$3" -R"./scripts/$4/oracleVersion.dql"
fi
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ ./registerOracleVersionView.sh Repo01 dmadmin xxx oracle
./registerOracleVersionView.sh: line 1: oracle: command not found
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ sed -i -e 's,^if[[:space:]]*",if \[\[ ",' -e 's,^if \[\[ .*"$,& \]\],' registerOracleVersionView.sh
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ cat registerOracleVersionView.sh
if [[ "$4" == "oracle" ]]
then
idql "$1" -U"$2" -P"$3" -R"./scripts/$4/oracleVersion.dql"
fi
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ ./registerOracleVersionView.sh Repo01 dmadmin xxx oracle
OpenText Documentum idql - Interactive document query interface
Copyright (c) 2018. OpenText Corporation
All rights reserved.
Client Library Release 16.4.0170.0080
Connecting to Server using docbase Repo01
[DM_SESSION_I_SESSION_START]info: "Session 010f1234800113af started for user dmadmin."
Connected to OpenText Documentum Server running Release 16.4.0170.0234 Linux64.Oracle
1> 2> result
------------
T
(1 row affected)
1> 2> new_object_ID
----------------
190f1234800edc59
(1 row affected)
1>
[dmadmin@cs-0 scripts]$
[dmadmin@cs-0 scripts]$ cat ./scripts/oracle/oracleVersion.dql
execute exec_sql with query = 'create view oracle_version as select * from v$version'
go
REGISTER TABLE dm_dbo.oracle_version (banner String(80))
go[dmadmin@cs-0 scripts]$
As you can see above, the shell executes a DQL script “oracleVersion.dql”. This simply creates a new view “oracle_version”. I have no clue where this might be used in LSS but what I can tell you is that this script was already wrong in LSS 16.6.0 (released in Jul 2019 I believe) and nobody complained about it so far apparently, so maybe you can wait for the official fix from OpenText or you can fix it yourself like I did, up to you!
Cet article Documentum – LSS registerOracleVersionView script with wrong content est apparu en premier sur Blog dbi services.
Documentum – D2-Smartview class cast exception
D2-Smartview is a new UI that OpenText now provides starting with the version 16 of D2. It’s a lightweight UI that can perform some of the actions that D2 does. The list of features will probably increase with time but at the moment, I guess it’s more for simple users that have very basic needs, consumer like roles mainly. An interesting thing is that with a small configuration, users can switch between D2 and D2-Smartview on the fly.
The issue I wanted to talk about in this blog is a class cast exception that might while trying to download a document using D2-Smartview that has been deployed on a WebLogic Server:
2020-02-21 10:42:11,168 UTC [INFO ] ([ACTIVE] ExecuteThread: '30' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.a.c.documentset.D2DocumentSetSwitch : D2DocumentSetSwitch.getDocumentSetList end: 0.000s
2020-02-21 10:42:11,184 UTC [INFO ] ([ACTIVE] ExecuteThread: '30' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.utils.FormatUtils : Compiled format pattern [a-zA-Z_\-0-9]+{1,32}
2020-02-21 10:42:11,405 UTC [INFO ] ([ACTIVE] ExecuteThread: '30' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.common.dctm.utils.DfServerUtil : loadBalancedContentServer set to false
2020-02-21 10:42:11,681 UTC [INFO ] ([ACTIVE] ExecuteThread: '95' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.d.w.services.config.D2X3ConfigService : D2X3ConfigService.getAvailableWidgets end : 0.119s
2020-02-21 10:42:11,903 UTC [ERROR] ([ACTIVE] ExecuteThread: '34' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Download could not parse request body
2020-02-21 10:42:11,908 UTC [ERROR] ([ACTIVE] ExecuteThread: '34' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.d2fs.dctm.servlets.D2HttpServlet : Request parsing failed
java.lang.ClassCastException: weblogic.security.principal.WLSUserImpl cannot be cast to com.emc.d2fs.authc.HttpAuthPrincipal
at com.emc.d2fs.dctm.servlets.D2HttpContext.<init>(D2HttpContext.java:259)
at com.emc.d2fs.dctm.servlets.D2HttpServlet.doGetAndPost(D2HttpServlet.java:360)
at com.emc.d2fs.dctm.servlets.D2HttpServlet.doGet(D2HttpServlet.java:113)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:286)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:260)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:137)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:350)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:247)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3705)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3672)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:328)
at weblogic.security.service.SecurityManager.runAsForUserCode(SecurityManager.java:197)
at weblogic.servlet.provider.WlsSecurityProvider.runAsForUserCode(WlsSecurityProvider.java:203)
at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:71)
at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2443)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2291)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2269)
at weblogic.servlet.internal.ServletRequestImpl.runInternal(ServletRequestImpl.java:1705)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1665)
at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:272)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:652)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
2020-02-21 10:42:11,931 UTC [ERROR] ([ACTIVE] ExecuteThread: '34' for queue: 'weblogic.kernel.Default (self-tuning)') - com.emc.documentum.rest.util.LogHelper : LogId: 2516f8e0-f920-437c-86f3-502b3ec8ad14, Status: 500, code: E_INTERNAL_SERVER_ERROR, message: An internal server error occurs.
java.lang.ClassCastException: weblogic.security.principal.WLSUserImpl cannot be cast to com.emc.d2fs.authc.HttpAuthPrincipal
at com.emc.d2fs.dctm.servlets.D2HttpContext.<init>(D2HttpContext.java:259)
at com.emc.d2fs.dctm.servlets.D2HttpServlet.doGetAndPost(D2HttpServlet.java:360)
at com.emc.d2fs.dctm.servlets.D2HttpServlet.doGet(D2HttpServlet.java:113)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:286)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:260)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:137)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:350)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:247)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3705)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3672)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:328)
at weblogic.security.service.SecurityManager.runAsForUserCode(SecurityManager.java:197)
at weblogic.servlet.provider.WlsSecurityProvider.runAsForUserCode(WlsSecurityProvider.java:203)
at weblogic.servlet.provider.WlsSubjectHandle.run(WlsSubjectHandle.java:71)
at weblogic.servlet.internal.WebAppServletContext.doSecuredExecute(WebAppServletContext.java:2443)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2291)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2269)
at weblogic.servlet.internal.ServletRequestImpl.runInternal(ServletRequestImpl.java:1705)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1665)
at weblogic.servlet.provider.ContainerSupportProviderImpl$WlsRequestExecutor.run(ContainerSupportProviderImpl.java:272)
at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:652)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)
2020-02-21 10:42:13,972 UTC [ERROR] ([ACTIVE] ExecuteThread: '25' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.d.w.s.b.D2BravaCSRAnnotationService : Invalid object id:
2020-02-21 10:42:13,978 UTC [ERROR] ([ACTIVE] ExecuteThread: '25' for queue: 'weblogic.kernel.Default (self-tuning)') - c.e.d.d.d.i.D2BravaCSRAnnotationsManagerImpl : Error getting changemark for document 090f123480003979
D2-Smartview is using, in some part, REST services and therefore you might find several similarities between D2-REST and D2-Smartview for example. If you are familiar with the D2-REST application on WebLogic, you might know that OpenText usually asks you to remove a certain set of JARs, then disable some security, aso… It’s not surprising that the documentation for D2-Smartview also asks the same kind of thing. For D2-REST, I personally never had to remove JARs (except this one, which isn’t in the list from OpenText anyway) and I never had to disable the basic authentication. This is because I could always rely on LDAP authentication. Most companies rely on LDAP nowadays and therefore you usually don’t need to disable WebLogic basic authentication on D2-REST because you can just configure WebLogic to use the LDAP Server for authentication and D2-REST will use the same account from the Repository (assuming it is also integrated with the LDAP).
Therefore, when I started to do some engineering on D2-Smartview, to deploy and test it, I obviously didn’t follow all the documentation provided by OpenText, only the parts that I usually do for D2-REST because I know that’s sufficient. These are the things that OpenText asks you to do for D2-Smartview 16.5.1 that I simply ignored:
- Disable web service annotation scan
- Add “-Dweblogic.servlet.DIDisabled=true” to JVM parameters of the Managed Server
- Remove JARs files: jsr*.jar, stax-api-*.jar, stax2-api-*.jar, xml-apis-*.jar, xmlParserAPIs-*.jar
- Disable WebLogic basic authentication: <enforce-valid-basic-auth-credentials>false</enforcevalid-basic-auth-credentials>
With the above ignored, I could deploy successfully D2-Smartview 16.5.1. The login was working properly with my LDAP account, I could browse the repository, perform searches, see properties of documents, aso… However, there was one thing not working: the download of document which failed with the above exception. As you can probably tell yourself, the exception is related to class cast exception from WebLogic principal (weblogic.security.principal.WLSUserImpl) to D2-Smartview principal (com.emc.d2fs.authc.HttpAuthPrincipal). Therefore, this was most probably linked to the basic authentication that I kept enabled just like for D2-REST. It looked like for D2-Smartview, it was really needed to disable it.
Usually, I don’t like to disable security because you never know… This is especially true for the WebLogic basic authentication because this configuration applies to the whole WebLogic Domain! OpenText claims that disabling the basic authentication on WebLogic isn’t a security issue for their applications (DA, D2, D2-Config, D2-REST, D2-Smartview, d2ls, XMLViewer, DFS, …) because each of these handle the authentication directly. However, at some point in the future, some custom application might be deployed on that domain that expected WebLogic to perform the authentication so you might end-up with security holes. Unfortunately for D2-Smartview, it looks like there is no way around it at the moment (contrary to D2-REST), so disabling the basic authentication is needed if you expect it to work fully:
[weblogic@ws-0 ~]$ stopDOMAIN
The server 'msDA-01' has been stopped successfully.
The server 'msD2Conf-01' has been stopped successfully.
The server 'msD2SV-01' has been stopped successfully.
The server 'msD2-01' has been stopped successfully.
The server 'AdminServer' has been stopped successfully.
The NodeManager has been stopped successfully.
[weblogic@ws-0 ~]$
[weblogic@ws-0 ~]$ grep -A1 "valid-basic" $DOMAIN_HOME/config/config.xml
[weblogic@ws-0 ~]$
[weblogic@ws-0 ~]$ sed -i 's,.*</security-configuration>, <enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials>\n&,' $DOMAIN_HOME/config/config.xml
[weblogic@ws-0 ~]$
[weblogic@ws-0 ~]$ grep -A1 "valid-basic" $DOMAIN_HOME/config/config.xml
<enforce-valid-basic-auth-credentials>false</enforce-valid-basic-auth-credentials>
</security-configuration>
[weblogic@ws-0 ~]$
[weblogic@ws-0 ~]$ startDOMAIN
The NodeManager has been started successfully.
The server 'AdminServer' has been started successfully.
The server 'msDA-01' has been started successfully.
The server 'msD2Conf-01' has been started successfully.
The server 'msD2SV-01' has been started successfully.
The server 'msD2-01' has been started successfully.
[weblogic@ws-0 ~]$
As you can see above, I updated the config.xml directly but the preferred way to do it is using WLST. There are several explanations on that on the internet. Once that’s done, the download of files is then working as expected. Maybe OpenText will change the source code so that it doesn’t need that at some point in the future but until then at least, no real other solution. The other three requirements from the documentation are still not in place on my side and I didn’t find anything not working so far so I guess I will just continue to ignore them (especially the “DIDisabled” one).
Cet article Documentum – D2-Smartview class cast exception est apparu en premier sur Blog dbi services.
Migrating an Oracle instance to AWS Aurora – 3 – Data replication with AWS DMS
If you followed the last two posts in this little series (here and here) this is the current status of this demo migration project: We have converted the Oracle SH schema and applied it to the target AWS Aurora with PostgreSQL compatibility instance. This worked quite well but a few objects (mostly PL/SQL procedures) would need manual actions. The next and final step is to replicate the data from Oracle to the Aurora instance using AWS DMS (Data Migration Service). As with all logical replications this is a two step process: First a consistent state of the current data needs to be loaded to the target. Once that is completed changes that happened since the load on the source need to be captured and replayed on the target.
Coming back to our current setup:
What we do not need anymore is the Windows host, this one was only required for the AWS Schema Conversion Utility (although you can install that on Linux and macOS as well). The target setup will look like this:
The key point here is the DMS replication instance. This one will connect to a source end point, mines the transaction logs and replays all the changes to the target end point. The replication instance will also take care of the initial load of the data. You have two choices for log mining, either Oracle LogMiner or the AWS DMS Binary Reader which is an AWS implementation for mining the log files. AWS recommends to use Oracle LogMiner in most situation except:
Before we go on and create the replication instance we need to prepare the Oracle instance. The first requirement is, that the Oracle instance need to be in archiving mode:
[oracle@ip-10-0-1-146 ~]$ sqlplus sys/manager@localhost/XE as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 20 10:46:49 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to: Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 SQL> shutdown immediate; ... [oracle@ip-10-0-1-146 ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 - Production on Fri Mar 20 10:48:14 2020 Version 18.4.0.0.0 Copyright (c) 1982, 2018, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 1191181104 bytes Fixed Size 8895280 bytes Variable Size 436207616 bytes Database Buffers 738197504 bytes Redo Buffers 7880704 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/product/18c/dbhomeXE/dbs/arch Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4
The second requirement is that supplemental logging needs to be enabled:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ALTER DATABASE ADD SUPPLEMENTAL LOG DATA * ERROR at line 1: ORA-44608: Supplemental logging settings cannot be modified in Oracle Database Express Edition. SQL> SELECT supplemental_log_data_min FROM v$database; SUPPLEME -------- NO SQL>
So, with our setup this is not possible as Oracle XE 18 does not support supplemental logging. What we can do is to load the complete data, but we can not capture changes. For some migrations that might actually be fine for others the downtime for fully loading the data is not acceptable. So lets see how far we will come.
Step one is creating the end points. One for Oracle as the source:
… and the other one for the target:
Both end points are now ready:
Next we need the replication instance:
The creation of the replication instance will take some time:
Now we need something that connects the end points with the replication instance and that is the goal of the “Database migration tasks”:
Without log mining we can only go for “Migrate existing data”:
Nothing really happens, and the reason is this:
So, with our current setup we can not proceed. What I did is: I took one of our training EC2 instances which runs Oracle 19c, enabled archive mode, supplemental logging and installed the same sample schemas. Now the connection is successful:
Restart the task:
After a few seconds the load is complete:
That was quite easy, lets see if there really is something in the Aurora instance:
postgres=> select * from "SH"."PRODUCTS";
PROD_ID | PROD_NAME | PROD_DESC | PROD_SUBCATEGORY | PROD_SUBCATEGORY_ID | PROD_SUBCATEGORY_DESC | PROD_CATEGORY | PROD_CATEGORY_ID | PROD_CATEGORY_DESC | PROD_WEIG
HT_CLASS | PROD_UNIT_OF_MEASURE | PROD_PACK_SIZE | SUPPLIER_ID | PROD_STATUS | PROD_LIST_PRICE | PROD_MIN_PRICE | PROD_TOTAL | PROD_TOTAL_ID | PROD_SRC_ID | PROD_EFF_FROM | PROD_EFF_TO | PROD_VALID
---------+------------------------------------------------+-----------------------------------------------------+----------------------+---------------------+-----------------------+-----------------------------+------------------+-----------------------------+----------
---------+----------------------+----------------+-------------+-------------+-----------------+----------------+------------+---------------+-------------+---------------------+-------------+------------
13 | 5MP Telephoto Digital Camera | 5MP Telephoto Digital Camera | Cameras | 2044.0000000000 | Cameras | Photo | 204.0000000000 | Photo |
1 | U | P | 1 | STATUS | 899.99 | 899.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
14 | 17" LCD w/built-in HDTV Tuner | 17" LCD w/built-in HDTV Tuner | Monitors | 2035.0000000000 | Monitors | Peripherals and Accessories | 203.0000000000 | Peripherals and Accessories |
1 | U | P | 1 | STATUS | 999.99 | 999.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
15 | Envoy 256MB - 40GB | Envoy 256MB - 40Gb | Desktop PCs | 2021.0000000000 | Desktop PCs | Hardware | 202.0000000000 | Hardware |
1 | U | P | 1 | STATUS | 999.99 | 999.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
16 | Y Box | Y Box | Game Consoles | 2011.0000000000 | Game Consoles | Electronics | 201.0000000000 | Electronics |
1 | U | P | 1 | STATUS | 299.99 | 299.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
17 | Mini DV Camcorder with 3.5" Swivel LCD | Mini DV Camcorder with 3.5" Swivel LCD | Camcorders | 2041.0000000000 | Camcorders | Photo | 204.0000000000 | Photo |
1 | U | P | 1 | STATUS | 1099.99 | 1099.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
18 | Envoy Ambassador | Envoy Ambassador | Portable PCs | 2022.0000000000 | Portable PCs | Hardware | 202.0000000000 | Hardware |
1 | U | P | 1 | STATUS | 1299.99 | 1299.99 | TOTAL | 1.0000000000 | | 1998-01-01 00:00:00 | | A
Looks fine. Once you get used to the DMS interface and know what the differnent pieces are really there for it is quite easy to get a simple migration going. In the next post we’ll see how chance capture can be configured so that changes on the source are automatically replicated to the target.
Cet article Migrating an Oracle instance to AWS Aurora – 3 – Data replication with AWS DMS est apparu en premier sur Blog dbi services.
Migrating an Oracle instance to AWS Aurora – 2– The AWS Schema Conversion Tool (SCT)
Having the required infrastructure ready now it is time to have a look at the AWS Schema Conversion Tool (SCT). The goal of this tool is to convert a source schema (Oracle in our case) to a target schema (AWS Aurora in our case). From the description this should also migrate stored functions and procedures and we will see later on how well that works. Automatically converting Oracle’s PL/SQL to PostgreSQL’s PL/pgSQL quickly becomes tricky and often there is no way around re-implementing a major part of the business logic in the database. There is EDB Postgres Advanced Server which comes with Oracle compatibility and greatly reduces migration time but this is not the topic of this post.
Quickly coming back to the basic infrastructure, this is what we have now:
We’ll use the Windows host to install and configure AWS SCT and then convert the Oracle source schema to the Aurora PostgreSQL target schema. To connect to the Windows host you can right-click the Windows instance to get the required information:
Now that we are connected to the Windows host we can continue with installing AWS SCT. The latest version for Windows can be downloaded here. Having that downloaded and extracted simply execute it, you will not be asked any questions and once completed you will find AWS SCT in the start menu:
Simply start it and accept the license:
You will be directly asked what type of migration you want to do, so a new migration project can be created:
The next screen will directly ask you for the connection details to the Oracle instance. Before you do anything here you need to download the Oracle jdbc driver from here:
I’ve put the driver here:
,,. and now can select the driver in the SCT screen:
Before your connection test will succeed you will need to adjust the inbound rules of the security which is attached to the Oracle instance:
Once the connection fine SCT will load all the meta data of the source (we are only interested in the “HR” schema for now):
The next step is to connect to the target Aurora instance:
Some procedure here: Before you can connect you’ll need the driver (download from here):
Once ready (and again you need to adjust the security group to allow inbound connection on port 5432):
Now we have both sides connected: Oracle on the left and AWS Aurora on the right:
From now on the schema can be migrated from the source to the target:
Completed with a few issues:
Aurora for PostgreSQL comes with little helpers as you can see in the screenshot below:
You can see that on the right side, there is a new schema “aws_oracle_ext” that holds all these objects:
To actually apply the schema to the target database you have to tell SCT to do so:
Again, you will need to check the issues on the target side:
By connecting to the Aurora instance with psql you can actually confirm that the objects have been created:
postgres# \dn
List of schemas
Name | Owner
--------------------+----------
aws_oracle_context | postgres
aws_oracle_data | postgres
aws_oracle_ext | postgres
aws_postgis | postgres
hr | postgres
public | postgres
(6 rows)
postgres=> select * from hr.employees;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+-------+--------------+-----------+--------+--------+----------------+------------+---------------
(0 rows)
Quite helpful is the report you can generate for getting an idea how many objects can be converted without any issue and how many of them need manual action:
Before proceeding further with loading the data using AWS DMS all the issues reported by SCT should be fixed, like this one:
That’s it for this post. The AWS Schema Conversion Tool in general does a good job. Even functions and procedures are migrated but of course not everything can be done automatically and some of the features/implementations that Oracle provides need to be re-implemented/adjusted on the AWS Aurora side.
In the next post we’ll look at the actual data migration using AWS DMS.
Cet article Migrating an Oracle instance to AWS Aurora – 2– The AWS Schema Conversion Tool (SCT) est apparu en premier sur Blog dbi services.
Can I put my temporary tablespaces on a RAM disk with PostgreSQL?
The short answer to the title of this blog post is: yes, of course you can. The more important question is: should you? The PostgreSQL documentation about tablespaces contains an explicit warning: “Placing a tablespace on a temporary file system like a RAM disk risks the reliability of the entire cluster”. This is for sure true for tablespaces containing persistent data, but what about objects/files that are created in a temporary tablespace like temporary tables or sorts that are going to disk? Does the warning from the documentation apply to these as well? You can check the last blog about temporary tablespaces for getting an idea what actually goes to temporary tablespaces in PostgreSQL.
On Linux a RAM disk can be created quite easily:
postgres@centos8pg:/home/postgres/ [pgdev] sudo mkfs /dev/my_ram_disk 81920
mke2fs 1.44.6 (5-Mar-2019)
Creating regular file /dev/my_ram_disk
Creating filesystem with 81920 1k blocks and 20480 inodes
Filesystem UUID: ecc6d90e-2d59-47f8-a598-7726c309c389
Superblock backups stored on blocks:
8193, 24577, 40961, 57345, 73729
Allocating group tables: done
Writing inode tables: done
Writing superblocks and filesystem accounting information: done
Of course that needs to be mounted somewhere so that we can put a temporary tablespace on it:
postgres@centos8pg:/home/postgres/ [pgdev] sudo mkdir /my_ram_disk postgres@centos8pg:/home/postgres/ [pgdev] sudo mount /dev/my_ram_disk /my_ram_disk/ postgres@centos8pg:/home/postgres/ [pgdev] df -h | grep my_ram /dev/loop0 78M 1.6M 72M 3% /my_ram_disk postgres@centos8pg:/home/postgres/ [pgdev] sudo chown postgres:postgres /my_ram_disk
I am going to create a new PostgreSQL cluster from scratch:
postgres@centos8pg:/home/postgres/ [pgdev] initdb -D /var/tmp/pg postgres@centos8pg:/home/postgres/ [pgdev] export PGPORT=8888 postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pg/ start -l /dev/null
Preparing the temporary tablespace and setting it as the default:
postgres@centos8pg:/home/postgres/ [pgdev] psql -X -p 8888 postgres
psql (13devel)
Type "help" for help.
postgres=# \! mkdir /my_ram_disk/tbs/
postgres=# create tablespace my_temp_tbs location '/my_ram_disk/tbs/';
CREATE TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
-------------+----------+------------------+-------------------+---------+---------+-------------
my_temp_tbs | postgres | /my_ram_disk/tbs | | | 0 bytes |
pg_default | postgres | | | | 22 MB |
pg_global | postgres | | | | 559 kB |
(3 rows)
postgres=# alter system set temp_tablespaces = 'my_temp_tbs';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show temp_tablespaces ;
temp_tablespaces
------------------
my_temp_tbs
(1 row)
Creating a new temporary table should now create the underlying file on the RAM disk:
postgres=# create temporary table tmp1 ( a int, b date );
CREATE TABLE
postgres=# select pg_relation_filepath('tmp1');
pg_relation_filepath
------------------------------------------------
pg_tblspc/16384/PG_13_202003051/12732/t3_16387
(1 row)
postgres-# \! ls -la /var/tmp/pg/pg_tblspc/
total 4
drwx------. 2 postgres postgres 19 Mar 16 21:32 .
drwx------. 19 postgres postgres 4096 Mar 16 21:32 ..
lrwxrwxrwx. 1 postgres postgres 16 Mar 16 21:32 16384 -> /my_ram_disk/tbs
Everything as expected. What could possibly go wrong with that? Will PostgreSQL start just fine if we put data in that table and then crash the postmaster?
postgres=# insert into tmp1 select * from generate_series(1,10000);
INSERT 0 10000
postgres=# \! ps -ef | grep postgres | grep "var/tmp"
postgres 25900 1 0 21:31 ? 00:00:00 /u01/app/postgres/product/DEV/db_1/bin/postgres -D /var/tmp/pg
postgres 25992 25911 0 21:47 pts/0 00:00:00 sh -c ps -ef | grep postgres | grep "var/tmp"
postgres 25995 25992 0 21:47 pts/0 00:00:00 grep var/tmp
postgres=# \! kill -9 25900
postgres=# select 1;
FATAL: terminating connection due to unexpected postmaster exit
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?> select 1;
You are currently not connected to a database.
!?>
Can we start normally from here on?
postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pg/ start pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2020-03-16 21:49:33.034 CET [26010] LOG: starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit 2020-03-16 21:49:33.035 CET [26010] LOG: listening on IPv6 address "::1", port 8888 2020-03-16 21:49:33.035 CET [26010] LOG: listening on IPv4 address "127.0.0.1", port 8888 2020-03-16 21:49:33.037 CET [26010] LOG: listening on Unix socket "/tmp/.s.PGSQL.8888" 2020-03-16 21:49:33.041 CET [26011] LOG: database system was interrupted; last known up at 2020-03-16 21:36:18 CET 2020-03-16 21:49:33.263 CET [26011] LOG: database system was not properly shut down; automatic recovery in progress 2020-03-16 21:49:33.264 CET [26011] LOG: redo starts at 0/156D588 2020-03-16 21:49:33.264 CET [26011] LOG: invalid record length at 0/1574240: wanted 24, got 0 2020-03-16 21:49:33.264 CET [26011] LOG: redo done at 0/1574068 2020-03-16 21:49:33.277 CET [26010] LOG: database system is ready to accept connections done server started
All is fine. Of course the temporary table is gone but that would also have been the case if we just ended our session instead of killing the postmaster:
postgres@centos8pg:/home/postgres/ [pgdev] psql -X -p 8888 postgres
psql (13devel)
Type "help" for help.
postgres=# \d tmp1
Did not find any relation named "tmp1".
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
-------------+----------+------------------+-------------------+---------+------------+-------------
my_temp_tbs | postgres | /my_ram_disk/tbs | | | 1024 bytes |
pg_default | postgres | | | | 22 MB |
pg_global | postgres | | | | 559 kB |
(3 rows)
postgres=#
Sort operations will just work fine on that tablespace as well:
postgres=# select * from generate_series(1,1000000) order by random();
generate_series
-----------------
943370
301661
...
… but you need to be careful with the size of the RAM disk:
postgres=# select * from generate_series(1,5000000) order by random(); 2020-03-16 22:04:47.738 CET [26026] ERROR: could not write block 629 of temporary file: No space left on device 2020-03-16 22:04:47.738 CET [26026] STATEMENT: select * from generate_series(1,5000000) order by random(); ERROR: could not write block 629 of temporary file: No space left on device
So actually you can put a temporary tablespace on a RAM disk and I am not aware of serious issues, even if you lose the RAM disk that can easily be fixed:
postgres@centos8pg:/home/postgres/ [pgdev] sudo umount /my_ram_disk postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pg/ restart waiting for server to shut down....2020-03-16 22:06:23.118 CET [26010] LOG: received fast shutdown request 2020-03-16 22:06:23.124 CET [26010] LOG: aborting any active transactions 2020-03-16 22:06:23.133 CET [26010] LOG: background worker "logical replication launcher" (PID 26017) exited with exit code 1 2020-03-16 22:06:23.133 CET [26012] LOG: shutting down 2020-03-16 22:06:23.148 CET [26010] LOG: database system is shut down done server stopped waiting for server to start....2020-03-16 22:06:23.281 CET [26236] LOG: starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit 2020-03-16 22:06:23.282 CET [26236] LOG: listening on IPv6 address "::1", port 8888 2020-03-16 22:06:23.282 CET [26236] LOG: listening on IPv4 address "127.0.0.1", port 8888 2020-03-16 22:06:23.286 CET [26236] LOG: listening on Unix socket "/tmp/.s.PGSQL.8888" 2020-03-16 22:06:23.289 CET [26236] LOG: could not open directory "pg_tblspc/16384/PG_13_202003051": No such file or directory 2020-03-16 22:06:23.293 CET [26237] LOG: database system was shut down at 2020-03-16 22:06:23 CET 2020-03-16 22:06:23.293 CET [26237] LOG: could not open directory "pg_tblspc/16384/PG_13_202003051": No such file or directory 2020-03-16 22:06:23.299 CET [26236] LOG: database system is ready to accept connections done server started
PostreSQL will complain but it will start and once the RAM disk is avaialble again there is business as usual:
postgres@centos8pg:/home/postgres/ [pgdev] sudo mount /dev/my_ram_disk /my_ram_disk/ 22:07:33 postgres@centos8pg:/home/postgres/ [pgdev] psql -X -p 8888 postgres psql (13devel) Type "help" for help. postgres=# create temporary table tmp1 ( a int ); CREATE TABLE postgres=# \! ls -la /my_ram_disk/ total 19 drwxr-xr-x. 4 postgres postgres 1024 Mar 16 21:31 . dr-xr-xr-x. 23 root root 4096 Mar 16 21:28 .. drwx------. 2 root root 12288 Mar 16 21:27 lost+found drwx------. 3 postgres postgres 1024 Mar 16 21:32 tbs postgres=# \! ls -la /my_ram_disk/tbs/ total 5 drwx------. 3 postgres postgres 1024 Mar 16 21:32 . drwxr-xr-x. 4 postgres postgres 1024 Mar 16 21:31 .. drwx------. 4 postgres postgres 1024 Mar 16 22:02 PG_13_202003051 postgres=#
If you know any issues with that or have any comments I would be happy if you can share your thoughts.
Another option would by to use tmpfs:
postgres@centos8pg:/home/postgres/ [pgdev] sudo mkdir /my_ram_disk_2/ postgres@centos8pg:/home/postgres/ [pgdev] sudo mount -t tmpfs -o size=2G tmpfs /my_ram_disk_2/ postgres@centos8pg:/home/postgres/ [pgdev] df -h | grep disk_2 tmpfs 2.0G 0 2.0G 0% /my_ram_disk_2
This can be used for temporary tablespaces as well:
postgres@centos8pg:/home/postgres/ [pgdev] psql -X -p 8888 postgres
psql (13devel)
Type "help" for help.
postgres=# \! sudo chown postgres:postgres /my_ram_disk_2/
postgres=# \! mkdir /my_ram_disk_2/tbs2
postgres=# create tablespace my_temp_tbs2 location '/my_ram_disk_2/tbs2';
CREATE TABLESPACE
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
--------------+----------+---------------------+-------------------+---------+------------+-------------
my_temp_tbs | postgres | /my_ram_disk/tbs | | | 2048 bytes |
my_temp_tbs2 | postgres | /my_ram_disk_2/tbs2 | | | 0 bytes |
pg_default | postgres | | | | 22 MB |
pg_global | postgres | | | | 559 kB |
(4 rows)
postgres=# alter system set temp_tablespaces = 'my_temp_tbs2';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show temp_tablespaces ;
temp_tablespaces
------------------
my_temp_tbs2
(1 row)
Same test as above:
postgres=# create temporary table tmp3 ( a int );
CREATE TABLE
postgres=# insert into tmp3 select * from generate_series(1,10000);
INSERT 0 10000
postgres=# select pg_relation_filepath('tmp3');
pg_relation_filepath
------------------------------------------------
pg_tblspc/24583/PG_13_202003051/12732/t3_24587
(1 row)
postgres=# \! ls -la /var/tmp/pg/pg_tblspc/
total 4
drwx------. 2 postgres postgres 32 Mar 16 22:31 .
drwx------. 19 postgres postgres 4096 Mar 16 22:32 ..
lrwxrwxrwx. 1 postgres postgres 16 Mar 16 21:32 16384 -> /my_ram_disk/tbs
lrwxrwxrwx. 1 postgres postgres 19 Mar 16 22:31 24583 -> /my_ram_disk_2/tbs2
postgres=# \! ps -ef | grep postgres | grep "var/tmp"
postgres 26236 1 0 22:06 ? 00:00:00 /u01/app/postgres/product/DEV/db_1/bin/postgres -D /var/tmp/pg
postgres 26412 26379 0 22:35 pts/0 00:00:00 sh -c ps -ef | grep postgres | grep "var/tmp"
postgres 26415 26412 0 22:35 pts/0 00:00:00 grep var/tmp
postgres=# \! kill -9 26236
postgres=# select 1;
FATAL: terminating connection due to unexpected postmaster exit
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?> select 1;
You are currently not connected to a database.
!?>
PostgreSQL will start just fine:
postgres@centos8pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pg/ start pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2020-03-16 22:37:44.903 CET [26431] LOG: starting PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit 2020-03-16 22:37:44.903 CET [26431] LOG: listening on IPv6 address "::1", port 8888 2020-03-16 22:37:44.903 CET [26431] LOG: listening on IPv4 address "127.0.0.1", port 8888 2020-03-16 22:37:44.906 CET [26431] LOG: listening on Unix socket "/tmp/.s.PGSQL.8888" 2020-03-16 22:37:44.910 CET [26432] LOG: database system was interrupted; last known up at 2020-03-16 22:31:24 CET 2020-03-16 22:37:45.172 CET [26432] LOG: database system was not properly shut down; automatic recovery in progress 2020-03-16 22:37:45.173 CET [26432] LOG: redo starts at 0/15A1EC8 2020-03-16 22:37:45.174 CET [26432] LOG: invalid record length at 0/15B9E88: wanted 24, got 0 2020-03-16 22:37:45.174 CET [26432] LOG: redo done at 0/15B9CD0 2020-03-16 22:37:45.195 CET [26431] LOG: database system is ready to accept connections done server started
To conclude: I am not sure if I would do this in real life but it seems to work quite well. As long as no persistent objects go into these tablespaces all should be fine. I’ve asked the mailing list for any experience with this, you can follow that here.
Cet article Can I put my temporary tablespaces on a RAM disk with PostgreSQL? est apparu en premier sur Blog dbi services.
Migrating an Oracle instance to AWS Aurora – 1 – Setting up the base infrastructure
Migrating database to the cloud is a hot topic since a few years. As more and more of our customers are in the cloud, and some of them in AWS, migrating some of their Oracle databases to AWS Aurora becomes a topic from time to time. In this little blog series we’ll have a look at how you can use the AWS Database Migration Service (DMS) and the AWS Schema Conversion Tool (SCT) for simplifying such a task. In this very first post we’ll setup the basic infrastructure we need for the demo.
I’ll be using Terraform once again for bringing up all the basic stuff. For the purpose of VPCs, Subnets, route tables and Security Groups please check the AWS documentation, this is not in the scope of this post.
The first thing to do in the Terraform script is to specify the AWS profile and region we want to use:
// set the provider to AWS and the AWS region to eu-central-1
provider "aws" {
profile = "test"
region = "eu-central-1"
}
After that there are two variables: the first one defines my local IP-address which is used in the security group definitions below, so connections via SSH and RDP will be possible from my current location. The second one defines the User data that will be passed to the EC2 instance that will host the Oracle source database. Basically it installs the Oracle Database Express Edition (XE) Release 18.4.0.0.0 (18c) and the Oracle sample schemas:
locals {
my_ip = ["XXX.XXX.XXX.XXX/32"]
instance-userdata = <<EOF
#!/bin/bash
sudo yum update -y
sudo yum install -y wget perl
wget https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm -O /home/ec2-user/oracle-database-xe-18c-1.0-1.x86_64.rpm
wget https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm -O /home/ec2-user/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
wget wget http://mirror.centos.org/centos/7/os/x86_64/Packages/compat-libstdc++-33-3.2.3-72.el7.i686.rpm -O /home/ec2-user/compat-libstdc++-33-3.2.3-72.el7.i686.rpm
sudo yum localinstall -y /home/ec2-user/compat-libstdc++-33-3.2.3-72.el7.i686.rpm
sudo yum localinstall -y /home/ec2-user/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
sudo yum localinstall -y /home/ec2-user/oracle-database-xe-18c-1.0-1.x86_64.rpm
(echo "manager"; echo "manager";) | /etc/init.d/oracle-xe-18c configure
sudo echo ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE/ >> /home/oracle/.bash_profile
sudo echo PATH=\$PATH:\$ORACLE_HOME/bin >> /home/oracle/.bash_profile
sudo echo ORACLE_SID=xe >> /home/oracle/.bash_profile
sudo echo export ORACLE_HOME PATH ORACLE_SID >> /home/oracle/.bash_profile
wget https://github.com/oracle/db-sample-schemas/archive/v19.2.tar.gz -O /home/oracle/v19.2.tar.gz
sudo su - oracle -c "tar -axf v19.2.tar.gz"
sudo su - oracle -c "cd db-sample-schemas-19.2; perl -p -i.bak -e 's#__SUB__CWD__#/home/oracle/db-sample-schemas-19.2#g' *.sql */*.sql */*.dat"
sudo su - oracle -c "cd db-sample-schemas-19.2; sqlplus system/manager@localhost/XEPDB1 @mksample manager manager manager manager manager manager manager manager users temp /tmp/ localhost/XEPDB1"
chkconfig --add oracle-xe-18c
EOF
}
The next lines of the Terraform script will setup all the network related stuff which I am not going to explain here:
// create the virtual private network
resource "aws_vpc" "dwe-vpc" {
cidr_block = "10.0.0.0/16"
enable_dns_hostnames = true
enable_dns_support = true
tags = {
Name = "dwe-vpc"
}
}
// create the internet gateway
resource "aws_internet_gateway" "dwe-igw" {
vpc_id = "${aws_vpc.dwe-vpc.id}"
tags = {
Name = "dwe-igw"
}
}
// create a dedicated subnet
resource "aws_subnet" "dwe-subnet" {
vpc_id = "${aws_vpc.dwe-vpc.id}"
cidr_block = "10.0.1.0/24"
availability_zone = "eu-central-1a"
tags = {
Name = "dwe-subnet"
}
}
// create a second dedicated subnet, this is required for RDS
resource "aws_subnet" "dwe-subnet-2" {
vpc_id = "${aws_vpc.dwe-vpc.id}"
cidr_block = "10.0.2.0/24"
availability_zone = "eu-central-1b"
tags = {
Name = "dwe-subnet-2"
}
}
// create routing table which points to the internet gateway
resource "aws_route_table" "dwe-route" {
vpc_id = "${aws_vpc.dwe-vpc.id}"
route {
cidr_block = "0.0.0.0/0"
gateway_id = "${aws_internet_gateway.dwe-igw.id}"
}
tags = {
Name = "dwe-igw"
}
}
// associate the routing table with the subnet
resource "aws_route_table_association" "subnet-association" {
subnet_id = "${aws_subnet.dwe-subnet.id}"
route_table_id = "${aws_route_table.dwe-route.id}"
}
// create a security group for ssh access to the linux systems
resource "aws_security_group" "dwe-sg-ssh" {
name = "dwe-sg-ssh"
description = "Allow SSH inbound traffic"
vpc_id = "${aws_vpc.dwe-vpc.id}"
ingress {
from_port = 22
to_port = 22
protocol = "tcp"
cidr_blocks = local.my_ip
}
// allow access to the internet
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = {
Name = "dwe-sg-ssh"
}
}
// create a security group for rdp access to the windows systems
resource "aws_security_group" "dwe-sg-rdp" {
name = "dwe-sg-rdp"
description = "Allow RDP inbound traffic"
vpc_id = "${aws_vpc.dwe-vpc.id}"
ingress {
from_port = 3389
to_port = 3389
protocol = "tcp"
cidr_blocks = local.my_ip
}
// allow access to the internet
egress {
from_port = 0
to_port = 0
protocol = "-1"
cidr_blocks = ["0.0.0.0/0"]
}
tags = {
Name = "dwe-sg-rdp"
}
}
Once the network is ready we’ll deploy the EC2 instance that will run the Oracle database (Red Hat 7.7 in this case):
// setup a red hat 7 system for the oracle source
resource "aws_instance" "dwe-oracle-source" {
ami = "ami-05798e9b15f285b27"
instance_type = "t2.medium"
key_name = "dwe-key-pair"
vpc_security_group_ids = ["${aws_security_group.dwe-sg-ssh.id}"]
subnet_id = "${aws_subnet.dwe-subnet.id}"
associate_public_ip_address = "true"
user_data = "${base64encode(local.instance-userdata)}"
root_block_device {
volume_size = "30"
volume_type = "standard"
delete_on_termination = "true"
}
tags = {
Name = "dwe-oracle-source"
}
}
As the target for the migration will be an Aurora cluster we will need that as well. This are basically three steps:
- Create a DB Subnet Group
- Creating the RDS cluster
- Creating and attaching the RDS instance to the RDS cluster
// create the subnet group for RDS instance
resource "aws_db_subnet_group" "dwe-rds-subnet-group" {
name = "dwe-rds-subnet-group"
subnet_ids = [
"${aws_subnet.dwe-subnet.id}",
"${aws_subnet.dwe-subnet-2.id}"
]
}
// create the RDS cluster
resource "aws_rds_cluster" "aws_rds_cluster_dwe" {
backup_retention_period = "7"
cluster_identifier = "aurora-dwe"
db_cluster_parameter_group_name = "default.aurora-postgresql10"
db_subnet_group_name = "${aws_db_subnet_group.dwe-rds-subnet-group.id}"
deletion_protection = "false"
engine = "aurora-postgresql"
engine_mode = "provisioned"
engine_version = "10.11"
master_password = "manager123"
master_username = "postgres"
port = "5432"
skip_final_snapshot = true
}
// create the RDS instance
resource "aws_rds_cluster_instance" "aws_db_instance_dwe" {
auto_minor_version_upgrade = "true"
publicly_accessible = "false"
monitoring_interval = "0"
instance_class = "db.r5.large"
cluster_identifier = "${aws_rds_cluster.aws_rds_cluster_dwe.id}"
identifier = "aurora-1-instance-1"
db_subnet_group_name = "${aws_db_subnet_group.dwe-rds-subnet-group.id}"
engine = "aurora-postgresql"
engine_version = "10.11"
}
For running the AWS Schema Conversion Tool we’ll finally setup a Windows instance so we are able to connect via RDP and install the AWS Schema Conversion Tool in the next post:
// create a windows instance for the AWS SCT
resource "aws_instance" "dwe-oracle-sct" {
ami = "ami-0cc2a6842e0da929f"
instance_type = "t2.micro"
key_name = "dwe-key-pair"
vpc_security_group_ids = ["${aws_security_group.dwe-sg-rdp.id}"]
subnet_id = "${aws_subnet.dwe-subnet.id}"
associate_public_ip_address = "true"
root_block_device {
volume_size = "30"
volume_type = "standard"
delete_on_termination = "true"
}
tags = {
Name = "dwe-oracle-sct"
}
}
Put all these steps together in a file and let Terraform do the work for you:
$ terraform init $ terrafrom plan $ terraform apply
The whole Oracle stuff will take some time to complete as downloading the Oracle XE rpm and the installation is nothing you can do in seconds.
In the next post we’ll look at the AWS Schema Conversion Utility and how that can be used to convert an Oracle schema to an AWS Aurora with PostgreSQL compatibility schema.
Cet article Migrating an Oracle instance to AWS Aurora – 1 – Setting up the base infrastructure est apparu en premier sur Blog dbi services.
AWS Automation – Stop a RDS instance
I created a RDS SQL Server instance on AWS. I use it for test purpose and to minimize the costs I needed to ensure that my instance is stopped automatically at the end of the working day and also because on RDS instances are automatically activated when its schedule maintenance is triggered.
In this article I will share the solution I implemented to achieve my goal.
I assume you have already created a RDS instance.
I also mentioned that during the walkthrough even some parameters are optional like name and description, I recommend to set it for a better clarity of your environment.
So first login to your AWS console and let you walkthrough
1- Preparation of your RDS InstanceOpen the RDS service console and select your RDS instance
Adding tags on the RDS instance is the first step of the process, it will be used later
Click Add and key in a Tag Key and a Tag Value
Now open the IAM service, select the Policies section and create a new policy
Select the RDS service, typing RDS in the search textbox and selecting RDS in the result list
Then choose the action by typing StopDBInstance in the search textbox and select the it in the result list.
Type now DescribeDBInstance in the search textbox and select it in the result list
Check if you have both actions DescribeDBInstance and StopDBInstance listed and select All resources in the Resources part. You can also select specific DB instances if you will.
Than click Review Policy button
Key in a name for your policy and a description (optional) and click Create Policy
So we are done with the security policy creation, we can proceed with the next step
3- Create a roleNow we have to create a role that will be granted with the policy we just created. The role will be use by System Manager to execute the needed task.
Again in the IAM service console, chose the Role option and create a new role.
Select AWS service in the type of trusted entity and select EC2 in the Use Case list
In the Select your use case chose EC2 again and click on Next:Permissions button
In the search text box type AmazonSSMMaintenanceWindowRole and select it in the list
Repeat permission selection selecting the security policy you created (in my case secpol_RDS_StopDBInstance) and click Next:Tags and Next:Review on the following screen
Enter a role name and a description (optional), check the policies and click Create Role
On the Role screen, notice the information that your role has been created and make sure to see it in the list.
You can also notice that the Trusted entities for your role is EC2
As we want to use the role in SSM we have to change the Trusted entity accordingly.
To achieve that, click on the role you just created, select the Trust relationships tab and click and Edit trust relationship.
You can see that the service is ec2.amazomaws.com.
Change the service replacing ec2 with SSM and click on Update Trust Policy
We are done concerning the IAM service
4- Create a resource groupNow switch to the System Manager service
Click on the menu Services, type System Manager in the search textbox and select System Manager
Select Resource Groups in the left pane and click on Create resource Group button.
Chose the Tag based option, search for DBInstance and select it as resource type, than search for the tag set on your RDS instance (in my case tag_RDSInstance) and its value (in my case RDS_awsvdata. Enter than a name and a description and click on Create group.
Switch back on the System Manager service home screen and select Maintenance Windows on the left pane and click on the Create maintenance Window button.
Capture a name and a description
Define your schedule when your maintenance window must run. Do not omit to specify your time zone to avoid your maintenance window to execute at an unexpected time. Click on the Create maintenance window button.
Check if your maintenance window has been created and it appears in the list. Check has well that the next execution time match the expected schedule.
Select the maintenance window you just created and select target tab and click on Register target button
In the Register target screen, defined a name a description and defined the target selecting the Choose a resource group option and selecting the resource group created in point 4. Select also your resource types if you have other resources than DBInstance in your resource group. Click register target button.
Next step is to register an Automation task. Select again your maintenance window and chose the Tasks tab and click on Register task selecting the Register Automation task.
Set a name and a description.
Select the Automation document, scrolling in the pages to find and select AWS-StopRDSInstance
Define the target selecting the registered target group and the one you created previously. Set the rate control according to your need and constrains.
Then important is to defined the IAM service role selecting the role you created in point 3.
In the input parameter use enter the ID of your RDS database instance.
Finally click the Register Automation task.
If you can stop your instance whenever you want, adjust your Cron settings to a near time and check the history of your maintenance window.
After the schedule time check the maintenance window history
There are indeed some tricks to go through and to know in order to setup this but it is worth doing it to avoid costs surprises.
Hoping you enjoy reading
Cet article AWS Automation – Stop a RDS instance est apparu en premier sur Blog dbi services.