Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 13 hours 57 min ago

Documentum – Unable to install xCP 2.3 on a CS 7.3

Sat, 2017-08-05 02:53

Beginning of this year, we were doing our first silent installations of the new Documentum stack. I already created a few blogs to talk about some issues with CS 7.3 and xPlore 1.6. This time, I will talk about xCP 2.3 and in particular the installation on a CS 7.3. The Patch of xCP as well as the patch for the CS 7.3 doesn’t matter since all versions are affected. Please just note that the first supported patch on a CS 7.3 is xCP 2.3 P03 so you shouldn’t be installing a previous patch on 7.3.

So, when installing an xCP 2.3 on a Content Server 7.3, you will get a pop-up in the installer with the following error message: “Installation of DARs failed”. You will only have an “OK” button on this pop-up which will close the installer. Ok so there is an issue with the installation of the DARs but what’s the issue exactly?

 

On the installation log file, we can see the following:

[dmadmin@content_server_01 ProcessEngine]$ cat logs/install.log
13:44:45,356  INFO [Thread-8] com.documentum.install.pe.installanywhere.actions.PEInitializeSharedLibrary - Done InitializeSharedLibrary ...
13:44:45,395  INFO [Thread-10] com.documentum.install.appserver.jboss.JbossApplicationServer - setApplicationServer sharedDfcLibDir is:$DOCUMENTUM_SHARED/dfc
13:44:45,396  INFO [Thread-10] com.documentum.install.appserver.jboss.JbossApplicationServer - getFileFromResource for templates/appserver.properties
13:44:45,532  WARN [Thread-10] com.documentum.install.pe.installanywhere.actions.DiWAPeInitialize - init-param tags found in Method Server webapp:

<init-param>
      <param-name>docbase_install_owner_name</param-name>
      <param-value>dmadmin</param-value>
</init-param>
<init-param>
      <param-name>docbase-GR_DOCBASE</param-name>
      <param-value>GR_DOCBASE</param-value>
</init-param>
<init-param>
      <param-name>docbase-DocBase1</param-name>
      <param-value>DocBase1</param-value>
</init-param>
<init-param>
      <param-name>docbase-DocBase2</param-name>
      <param-value>DocBase2</param-value>
</init-param>
13:44:58,771  INFO [AWT-EventQueue-0] com.documentum.install.pe.ui.panels.DiWPPELicenseAgreementPanel - UserSelection: "I accept the terms of the license agreement."
13:46:13,398  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - The batch file: $DOCUMENTUM_SHARED/temp/installer/wildfly/dctm_tmpcmd0.sh exist? false
13:46:13,399  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - The user home is : /home/dmadmin
13:46:13,405  INFO [AWT-EventQueue-0] com.documentum.install.appserver.jboss.JbossApplicationServer - Executing temporary batch file: $DOCUMENTUM_SHARED/temp/installer/wildfly/dctm_tmpcmd0.sh for running: $DOCUMENTUM_SHARED/java64/1.8.0_77/bin/java -cp $DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/dfc.jar:$DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/aspectjrt.jar:$DOCUMENTUM_SHARED/wildfly9.0.1/modules/system/layers/base/emc/documentum/security/main/DctmUtils.jar com.documentum.install.appserver.utils.DctmAppServerAuthenticationString $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer jboss
13:46:42,320  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeInstallActions - starting DctmActions
13:46:42,724  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - user name = admin
13:46:42,724  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - Server DctmServer_MethodServer already exists!
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - Deploying to Group MethodServer... bpm (bpm.ear): does not exist!
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/APP-INF/classes/dfc.properties
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/APP-INF/classes/log4j.properties
13:46:42,725  INFO [installer] com.documentum.install.appserver.jboss.JbossApplicationServer - resolving $DOCUMENTUM_SHARED/wildfly9.0.1/server/DctmServer_MethodServer/deployments/bpm.ear/bpm.war/WEB-INF/web.xml
13:46:42,727  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeInstallActions - Finished DctmActions.
13:46:44,885  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: DocBase2
13:52:20,931  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: DocBase2
13:52:20,932  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: DocBase1
13:57:59,510  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: DocBase1
13:57:59,511  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Start to deploy dars for docbase: GR_DOCBASE
14:04:03,231  INFO [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - End to deploy dars for repository: GR_DOCBASE
14:04:03,268 ERROR [installer] com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars - Installation of DARs failed
com.documentum.install.shared.common.error.DiException: 3 DAR(s) failed to install.
        at com.documentum.install.shared.common.services.dar.DiDocAppFailureList.report(DiDocAppFailureList.java:39)
        at com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars.deployDars(DiPAPeProcessDars.java:123)
        at com.documentum.install.pe.installanywhere.actions.DiPAPeProcessDars.setup(DiPAPeProcessDars.java:71)
        at com.documentum.install.shared.installanywhere.actions.InstallWizardAction.install(InstallWizardAction.java:75)
        at com.zerog.ia.installer.actions.CustomAction.installSelf(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.GhostDirectory.install(Unknown Source)
        at com.zerog.ia.installer.InstallablePiece.install(Unknown Source)
        at com.zerog.ia.installer.Installer.install(Unknown Source)
        at com.zerog.ia.installer.actions.InstallProgressAction.ae(Unknown Source)
        at com.zerog.ia.installer.actions.ProgressPanelAction$1.run(Unknown Source)
14:04:03,269  INFO [installer]  - The INSTALLER_UI value is SWING
14:04:03,269  INFO [installer]  - The env PATH value is: /usr/xpg4/bin:$DOCUMENTUM_SHARED/java64/JAVA_LINK/bin:$DOCUMENTUM/product/7.3/bin:$DOCUMENTUM/dba:$ORACLE_HOME/bin:$DOCUMENTUM_SHARED/java64/JAVA_LINK/bin:$DOCUMENTUM/product/7.3/bin:$DOCUMENTUM/dba:$ORACLE_HOME/bin:$DOCUMENTUM/product/7.3/bin:$ORACLE_HOME/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/dmadmin/bin:/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin
[dmadmin@content_server_01 ProcessEngine]$

 

It is mentioned that three DARs failed to be installed but since there are three docbases here, that’s actually one DAR per docbase. The only interesting information we can find from the install log file is that some DARs were installed properly so it’s not a generic issue but more likely an issue with one specific DAR. The next step is therefore to check the log file of the DAR installation:

[dmadmin@content_server_01 ProcessEngine]$ grep -i ERROR logs/dar_logs/GR_DOCBASE/peDars.log | grep -v "^\[INFO\].*ERROR"
[INFO]  dmbasic.exe output : dmbasic: Error 35 in line 585: Sub or Function not defined
[ERROR]  Unable to install dar file $DOCUMENTUM/product/7.3/install/DARsInternal/BPM.dar
com.emc.ide.installer.InstallException: Error handling controllable object Status = New; IsInstalled = true; com.emc.ide.artifact.bpm.model.bpm.impl.ActivityImpl@5e020dd1 (objectTypeName: null) (objectName: DB Inbound - Initiate, title: , subject: , authors: [], keywords: [], applicationType: , isHidden: false, compoundArchitecture: , componentLabel: [], resolutionLabel: , contentType: xml, versionLabel: [1.0, CURRENT], specialApp: DB-IN-IN.GIF, languageCode: , creatorName: null, archive: false, category: , controllingApp: , effectiveDate: [], effectiveFlag: [], effectiveLabel: [], expirationDate: [], extendedProperties: [], fullText: true, isSigned: false, isTemplate: false, lastReviewDate: null, linkResolved: false, publishFormats: [], retentionDate: null, status: , rootObject: true) (isPrivate: false, definitionState: installed, triggerThreshold: 0, triggerEvent: , execType: manual, execSubType: inbound_initiate, execMethodName: null, preTimer: 0, preTimerCalendarFlag: notusebusinesscal, preTimerRepeatLast: 0, postTimer: 0, postTimerCalendarFlag: notusebusinesscal, postTimerRepeatLast: 0, repeatableInvoke: true, execSaveResults: false, execTimeOut: 0, execErrHandling: stopAfterFailure, signOffRequired: false, resolveType: normal, resolvePkgName: , controlFlag: taskAssignedtoSupervisor, taskName: null, taskSubject: , performerType: user, performerFlag: noDeligationOrExtention, transitionMaxOutputCnt: 0, transitionEvalCnt: trigAllSelOutputLinks, transitionFlag: trigAllSelOutputLinks, transitionType: prescribed, execRetryMax: 0, execRetryInterval: 0, groupFlag: 0, template: true, artifactVersion: D65SP1);  Object ID = 4c0f123450002b1e;
Caused by: DfException:: THREAD: main; MSG: Error while making activity uneditable: com.emc.ide.artifactmanager.model.artifact.impl.ArtifactImpl@4bbc02ef (urn: urnd:com.emc.ide.artifact.bpm.activity/DB+Inbound+-+Initiate?location=%2FTemp%2FIntegration&name=DB+Inbound+-+Initiate, locale: null, repoLocation: null, categoryId: com.emc.ide.artifact.bpm.activity, implicitlyCreated: false, modifiedByUser: true); ERRORCODE: ff; NEXT: null
Caused by: DfException:: THREAD: main; MSG: [DM_WORKFLOW_E_NAME_NOT_EXIST]error:  "The dm_user object by the name 'dm_bps_inbound_user' specified in attribute performer_name does not exist."; ERRORCODE: 100; NEXT: null
[ERROR]  Failed to install DAR
Caused by: com.emc.ide.installer.InstallException: Error handling controllable object Status = New; IsInstalled = true; com.emc.ide.artifact.bpm.model.bpm.impl.ActivityImpl@5e020dd1 (objectTypeName: null) (objectName: DB Inbound - Initiate, title: , subject: , authors: [], keywords: [], applicationType: , isHidden: false, compoundArchitecture: , componentLabel: [], resolutionLabel: , contentType: xml, versionLabel: [1.0, CURRENT], specialApp: DB-IN-IN.GIF, languageCode: , creatorName: null, archive: false, category: , controllingApp: , effectiveDate: [], effectiveFlag: [], effectiveLabel: [], expirationDate: [], extendedProperties: [], fullText: true, isSigned: false, isTemplate: false, lastReviewDate: null, linkResolved: false, publishFormats: [], retentionDate: null, status: , rootObject: true) (isPrivate: false, definitionState: installed, triggerThreshold: 0, triggerEvent: , execType: manual, execSubType: inbound_initiate, execMethodName: null, preTimer: 0, preTimerCalendarFlag: notusebusinesscal, preTimerRepeatLast: 0, postTimer: 0, postTimerCalendarFlag: notusebusinesscal, postTimerRepeatLast: 0, repeatableInvoke: true, execSaveResults: false, execTimeOut: 0, execErrHandling: stopAfterFailure, signOffRequired: false, resolveType: normal, resolvePkgName: , controlFlag: taskAssignedtoSupervisor, taskName: null, taskSubject: , performerType: user, performerFlag: noDeligationOrExtention, transitionMaxOutputCnt: 0, transitionEvalCnt: trigAllSelOutputLinks, transitionFlag: trigAllSelOutputLinks, transitionType: prescribed, execRetryMax: 0, execRetryInterval: 0, groupFlag: 0, template: true, artifactVersion: D65SP1);  Object ID = 4c0f123450002b1e;
Caused by: DfException:: THREAD: main; MSG: Error while making activity uneditable: com.emc.ide.artifactmanager.model.artifact.impl.ArtifactImpl@4bbc02ef (urn: urnd:com.emc.ide.artifact.bpm.activity/DB+Inbound+-+Initiate?location=%2FTemp%2FIntegration&name=DB+Inbound+-+Initiate, locale: null, repoLocation: null, categoryId: com.emc.ide.artifact.bpm.activity, implicitlyCreated: false, modifiedByUser: true); ERRORCODE: ff; NEXT: null
Caused by: DfException:: THREAD: main; MSG: [DM_WORKFLOW_E_NAME_NOT_EXIST]error:  "The dm_user object by the name 'dm_bps_inbound_user' specified in attribute performer_name does not exist."; ERRORCODE: 100; NEXT: null
[dmadmin@content_server_01 ProcessEngine]$

 

With the above, we know that the only failed DAR is the BPM.dar and it looks like we have the reason for this: the DAR needs a user named “dm_bps_inbound_user” to proceed with the installation but couldn’t find it and therefore the installation failed. But actually that’s not the root cause, it’s only a consequence. The real reason why the DAR installation failed is displayed in the first line above.

[INFO]  dmbasic.exe output : dmbasic: Error 35 in line 585: Sub or Function not defined

 

For some reason, a function couldn’t be executed because not defined properly. This function is the one that is supposed to create the “dm_bps_inbound_user” user but with a CS 7.3 this function cannot be executed properly. As a result, the user isn’t created and then the DAR installation fail. For more information, you can refer to the BPM-11223.

 

This issue will – according to EMC – not be fixed in any patch of the xCP 2.3, even if this issue has been spotted quickly after the release of the xCP 2.3. Therefore, if you want to avoid this issue, you will have to wait several months for the xCP 2.4 to be released (not really realistic ;)) or you will need to create this user manually before installing the xCP 2.3 on a CS 7.3. You don’t need special permissions for this user and you don’t need to know its password so it’s rather simple to create it for all installed docbases in a few simple commands:

[dmadmin@content_server_01 ProcessEngine]$ echo "?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';" > create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "create,c,dm_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "set,c,l,user_name" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "dm_bps_inbound_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "set,c,l,user_login_name" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "dm_bps_inbound_user" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "save,c,l" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$ echo "?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';" >> create_user.api
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ cat create_user.api
?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';
create,c,dm_user
set,c,l,user_name
dm_bps_inbound_user
set,c,l,user_login_name
dm_bps_inbound_user
save,c,l
?,c,select r_object_id, user_name, user_login_name from dm_user where user_login_name like 'dm_bps%';
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ sep="***********************"
[dmadmin@content_server_01 ProcessEngine]$ for docbase in `cd $DOCUMENTUM/dba/config; ls`;do echo;echo "$sep";echo "Create User: ${docbase}";echo "$sep";iapi ${docbase} -Udmadmin -Pxxx -Rcreate_user.api;done

***********************
Create User: GR_DOCBASE
***********************


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0000.0205


Connecting to Server using docbase GR_DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 010f12345001c734 started for user dmadmin."


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f12345000093c
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f12345000093c     dm_bps_inbound_user   dm_bps_inbound_user
(1 row affected)

API> Bye

***********************
Create User: DocBase1
***********************


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0000.0205


Connecting to Server using docbase DocBase1
[DM_SESSION_I_SESSION_START]info:  "Session 010f234560052632 started for user dmadmin."


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f234560001532
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f234560001532     dm_bps_inbound_user   dm_bps_inbound_user                                                                                                                                                            
(1 row affected)

API> Bye

***********************
Create User: DocBase2
***********************


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2016
        All rights reserved.
        Client Library Release 7.3.0000.0205


Connecting to Server using docbase DocBase2
[DM_SESSION_I_SESSION_START]info:  "Session 010f345670052632 started for user dmadmin."


Connected to Documentum Server running Release 7.3.0000.0214  Linux64.Oracle
Session id is s0
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------

(0 row affected)

API> ...
110f345670001532
API> SET> ...
OK
API> SET> ...
OK
API> ...
OK
API> r_object_id     user_name             user_login_name                                                                                                                                                             
-------------------  --------------------- ---------------------
110f345670001532     dm_bps_inbound_user   dm_bps_inbound_user                                                                                                                                                            
(1 row affected)

API> Bye
[dmadmin@content_server_01 ProcessEngine]$
[dmadmin@content_server_01 ProcessEngine]$ rm create_user.api
[dmadmin@content_server_01 ProcessEngine]$

 

The users have been created properly in all docbases so just restart the xCP installer and this time the BPM.dar installation will succeed.

 

 

Cet article Documentum – Unable to install xCP 2.3 on a CS 7.3 est apparu en premier sur Blog dbi services.

Documentum – Using DA with Self-Signed SSL Certificate

Sat, 2017-08-05 01:58

A few years ago, I was working on a Documentum project and one of the tasks was to setup all components in SSL. I already published a lot of blogs on this subject but there is one I wanted to do but never really took the time to publish it. In this blog, I will therefore talk about Documentum Administrator in SSL using a Self-Sign SSL Certificate. Recently, a colleague of mine had the same issue at another customer so I provided him the full procedure that I will describe below. However, since the process below requires the signature of a jar file and since this isn’t available for all companies, you might want to check out my colleague’s blog too.

A lot of companies are working with their own SSL Trust Chain, meaning that they provide/create their own SSL Certificate (Self-Signed) including their Root and Intermediate SSL Certificate for the trust. End-users will not really notice the difference but they are actually using Self-Sign SSL Certificate. This has some repercussions when working with Documentum since you need to import the SSL Trust Chain on the various Application Servers (JMS, WebLogic, Dsearch, aso…). This is pretty simple but there is one thing that is a little bit trickier and this is related to Documentum Administrator.

Below, I will use a DA 7.2 P16 (that is therefore pretty recent) but the same applies to all patches of DA 7.2 and 7.3. For information, we didn’t face this issue with DA 7.1 so something most probably changed between DA 7.1 and 7.2. If you are seeing the same thing with a DA 7.1, feel free to put a comment below, I would love to know! When you are accessing DA for the first time, you will actually download a JRE which will be put under C:\Users\<user_name>\Documentum\ucf\<machine_name>, by default. This JRE is used for various stuff including the transfer of files (UCF), display of DA preferences, aso… DA isn’t taking the JRE from the website of Oracle, it is, in fact, taking it from the da.war file. The DA war file always contains two or three different JREs versions. Now if you want to use DA in HTTPS, these JREs will also need to contain your custom SSL Trust Chain. So how can you do that?

Well a simple answer would be: just like for the JMS or WebLogic, just import the custom SSL Trust Chain in the “cacerts” of these JREs. That will actually not work for a very vicious reason: EMC is now signing all the files provided and that also include the JREs inside da.war (well actually they are signing the checksums of the JREs, not the JREs themselves). Because of this signature, if you edit the cacerts file of the JREs, DA will say something like that: “Invalid checksum for the file ‘win-jre1.8.0_91.zip'”. This checksum ensures that the JREs and all the files you are using on your local workstation that have been downloaded from the da.war are the one provided by EMC. This is good from a security point of view since it prevents intruders to exchanges the files during transfer or directly on your workstation but that also prevents you from updating the JREs with your custom SSL Trust Chain.

 

So what I will do below to update the Java cacerts AND still keep a valid signature is:

  1. Extract the JREs and ucfinit.jar file from da.war
  2. Update the cacerts of each JREs with a custom SSL Trust Chain (Root + Intermediate)
  3. Repackage the JREs
  4. Calculate the checksum of the JREs using the ComputeChecksum java class
  5. Extract the old checksum files from ucfinit.jar
  6. Replace the old checksum files for the JREs with the new one generated on step 4
  7. Remove .RSA and .SF files from the META-INF folder and clean the MANIFEST to remove Documentum’s digital signature
  8. Recreate the file ucfinit.jar with the clean manifest and all other files
  9. Ask the company’s dedicated team to sign the new jar file
  10. Repackage da.war with the updated JREs and the updated/signed ucfinit.jar

 

I will use below generic commands that do not specify any version of the JREs or DA because there will be two or three different JREs and the versions will change depending on your DA Patch level, so better stay generic. I will also use my custom SSL Trust Chain which I put under /tmp.

In this first part, I will create a working folder to avoid messing with the deployed applications. Then I will extract the needed files and finally remove all files and folders that I don’t need. That’s the step 1:

[weblogic@weblogic_server_01 ~]$ mkdir /tmp/workspace; cd /tmp/workspace
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ cp $WLS_APPLICATIONS/da.war .
[weblogic@weblogic_server_01 workspace]$ ls
da.war
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ jar -xvf da.war wdk/system/ucfinit.jar wdk/contentXfer/
  created: wdk/contentXfer/
 inflated: wdk/contentXfer/All-MB.jar
 ...
 inflated: wdk/contentXfer/Web/Emc.Documentum.Ucf.Client.Impl.application
 inflated: wdk/contentXfer/win-jre1.7.0_71.zip
 inflated: wdk/contentXfer/win-jre1.7.0_72.zip
 inflated: wdk/contentXfer/win-jre1.8.0_91.zip
 inflated: wdk/system/ucfinit.jar
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ cd ./wdk/contentXfer/
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
All-MB.jar                                    jacob.dll                 libUCFSolarisGNOME.so   ucf-client-installer.zip  win-jre1.8.0_91.zip
Application Files                             jacob.jar                 libUCFSolarisJNI.so     ucf.installer.config.xml
Emc.Documentum.Ucf.Client.Impl.application    libMacOSXForkerIO.jnilib  licenses                UCFWin32JNI.dll
ES1_MRE.msi                                   libUCFLinuxGNOME.so       MacOSXForker.jar        Web
ExJNIAPI.dll                                  libUCFLinuxJNI.so         mac_utilities.jar       win-jre1.7.0_71.zip
ExJNIAPIGateway.jar                           libUCFLinuxKDE.so         ucf-ca-office-auto.jar  win-jre1.7.0_72.zip
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ for i in `ls | grep -v 'win-jre'`; do rm -rf "./${i}"; done
[weblogic@weblogic_server_01 contentXfer]$ rm -rf ./*/
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip
[weblogic@weblogic_server_01 contentXfer]$

 

At this point, only the JREs are present in the current folder (wdk/contentXfer) and I also have another file in another folder (wdk/system/ucfinit.jar). Once that is done, I’m creating a list of the JREs available that I will use for the whole blog and I’m also performing the steps 2 and 3, to extract the cacerts from the JREs, update them and finally repackage them (this is where I use the custom SSL Trust Chain):

[weblogic@weblogic_server_01 contentXfer]$ ls win-jre* | sed -e 's/.*win-//' -e 's/.zip//' > /tmp/list_jre.txt
[weblogic@weblogic_server_01 contentXfer]$ cat /tmp/list_jre.txt
jre1.7.0_71
jre1.7.0_72
jre1.8.0_91
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do unzip -x win-${line}.zip ${line}/lib/security/cacerts; done < /tmp/list_jre.txt
Archive:  win-jre1.7.0_71.zip
  inflating: jre1.7.0_71/lib/security/cacerts
Archive:  win-jre1.7.0_72.zip
  inflating: jre1.7.0_72/lib/security/cacerts
Archive:  win-jre1.8.0_91.zip
  inflating: jre1.8.0_91/lib/security/cacerts
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do keytool -import -noprompt -trustcacerts -alias custom_root_ca -keystore ${line}/lib/security/cacerts -file /tmp/Company_Root_CA.cer -storepass changeit; done < /tmp/list_jre.txt
Certificate was added to keystore
Certificate was added to keystore
Certificate was added to keystore
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do keytool -import -noprompt -trustcacerts -alias custom_int_ca -keystore ${line}/lib/security/cacerts -file /tmp/Company_Intermediate_CA.cer -storepass changeit; done < /tmp/list_jre.txt
Certificate was added to keystore
Certificate was added to keystore
Certificate was added to keystore
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do zip -u win-${line}.zip ${line}/lib/security/cacerts; done < /tmp/list_jre.txt
updating: jre1.7.0_71/lib/security/cacerts (deflated 35%)
updating: jre1.7.0_72/lib/security/cacerts (deflated 35%)
updating: jre1.8.0_91/lib/security/cacerts (deflated 33%)
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ while read line; do rm -rf ./${line}; done < /tmp/list_jre.txt
[weblogic@weblogic_server_01 contentXfer]$

 

At this point, the JREs have been updated with a new “cacerts” and therefore its checksum changed. It doesn’t match the signed checksum anymore so if you try to deploy DA at this point, you will get the error message I put above. So, let’s perform the steps 4, 5 and 6. For that purpose, I will use the file /tmp/ComputeChecksum.class that was provided by EMC. This class is needed in order to recalculate the new checksum of the JREs:

[weblogic@weblogic_server_01 contentXfer]$ pwd
/tmp/workspace/wdk/contentXfer
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ cp /tmp/ComputeChecksum.class .
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
ComputeChecksum.class  win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ java ComputeChecksum .
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ ls
ComputeChecksum.class           win-jre1.7.0_71.zip           win-jre1.7.0_72.zip           win-jre1.8.0_91.zip
ComputeChecksum.class.checksum  win-jre1.7.0_71.zip.checksum  win-jre1.7.0_72.zip.checksum  win-jre1.8.0_91.zip.checksum
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ rm ComputeChecksum.class*
[weblogic@weblogic_server_01 contentXfer]$
[weblogic@weblogic_server_01 contentXfer]$ cd /tmp/workspace/wdk/system/
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ pwd
/tmp/workspace/wdk/system
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ ls
ucfinit.jar
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ jar -xvf ucfinit.jar
 inflated: META-INF/MANIFEST.MF
 inflated: META-INF/COMPANY.SF
 inflated: META-INF/COMPANY.RSA
  created: META-INF/
 inflated: All-MB.jar.checksum
  created: com/
  created: com/documentum/
  ...
 inflated: UCFWin32JNI.dll.checksum
 inflated: win-jre1.7.0_71.zip.checksum
 inflated: win-jre1.7.0_72.zip.checksum
 inflated: win-jre1.8.0_91.zip.checksum
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ mv /tmp/workspace/wdk/contentXfer/win-jre*.checksum .
[weblogic@weblogic_server_01 system]$

 

With this last command, the new checksum have replaced the old ones. The next step is now to remove the old signatures (.RSA and .SF files + content of the manifest) and the repack the ucfinit.jar file (step 7 and 8):

[weblogic@weblogic_server_01 system]$ rm ucfinit.jar META-INF/*.SF META-INF/*.RSA
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ sed -i -e '/^Name:/d' -e '/^SHA/d' -e '/^ /d' -e '/^[[:space:]]*$/d' META-INF/MANIFEST.MF
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cat META-INF/MANIFEST.MF
Manifest-Version: 1.0
Ant-Version: Apache Ant 1.8.4
Title: Documentum Client File Selector Applet
Bundle-Version: 7.2.0160.0058
Application-Name: Documentum
Built-By: dmadmin
Build-Version: 7.2.0160.0058
Permissions: all-permissions
Created-By: 1.6.0_30-b12 (Sun Microsystems Inc.)
Copyright: Documentum Inc. 2001, 2004
Caller-Allowable-Codebase: *
Build-Date: August 16 2016 06:35 AM
Codebase: *
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ vi META-INF/MANIFEST.MF
    => Add a new empty line at the end of this file with vi, vim, nano or whatever... The file must always end with an empty line.
    => Do NOT use the command "echo '' >> META-INF/MANIFEST.MF" because it will change the fileformat of the file which complicate the signature (usually the FF is DOS...)
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ jar -cmvf META-INF/MANIFEST.MF ucfinit.jar *
added manifest
adding: All-MB.jar.checksum(in = 28) (out= 30)(deflated -7%)
adding: com/(in = 0) (out= 0)(stored 0%)
adding: com/documentum/(in = 0) (out= 0)(stored 0%)
adding: com/documentum/ucf/(in = 0) (out= 0)(stored 0%)
...
adding: UCFWin32JNI.dll.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.7.0_71.zip.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.7.0_72.zip.checksum(in = 28) (out= 30)(deflated -7%)
adding: win-jre1.8.0_91.zip.checksum(in = 28) (out= 30)(deflated -7%)
[weblogic@weblogic_server_01 system]$

 

At this point, the file ucfinit.jar has been recreated with an “empty” manifest, without signature but with all the new checksum files. Therefore, it is now time to send this file (ucfinit.jar) to your code signing team (step 9). This is out of scope for this blog but basically what will be done by your signature team is the creation of the .RSA and .SF files inside the folder META-INF as well as the repopulation of the manifest. The .SF and the manifest will contain more or less the same thing: the different files of the ucfinit.jar files will have their entries in these files with a pair filename/signature. At this point, we therefore have re-signed the checksum of the JREs.

 

The last step is now to repack the da.war with the new ucfinit.jar file which has been signed. I put the new signed file under /tmp:

[weblogic@weblogic_server_01 system]$ pwd
/tmp/workspace/wdk/system
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ rm -rf *
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ ll
total 0
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cp /tmp/ucfinit.jar .
[weblogic@weblogic_server_01 system]$
[weblogic@weblogic_server_01 system]$ cd /tmp/workspace/
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ ls wdk/*
wdk/contentXfer:
win-jre1.7.0_71.zip  win-jre1.7.0_72.zip  win-jre1.8.0_91.zip

wdk/system:
ucfinit.jar
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ jar -uvf da.war wdk
adding: wdk/(in = 0) (out= 0)(stored 0%)
adding: wdk/contentXfer/(in = 0) (out= 0)(stored 0%)
adding: wdk/contentXfer/win-jre1.7.0_71.zip(in = 41373620) (out= 41205241)(deflated 0%)
adding: wdk/contentXfer/win-jre1.7.0_72.zip(in = 41318962) (out= 41137924)(deflated 0%)
adding: wdk/contentXfer/win-jre1.8.0_91.zip(in = 62424686) (out= 62229724)(deflated 0%)
adding: wdk/system/(in = 0) (out= 0)(stored 0%)
adding: wdk/system/ucfinit.jar(in = 317133) (out= 273564)(deflated 13%)
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ mv $WLS_APPLICATIONS/da.war $WLS_APPLICATIONS/da.war_bck_beforeSignature
[weblogic@weblogic_server_01 workspace]$
[weblogic@weblogic_server_01 workspace]$ mv da.war $WLS_APPLICATIONS/
[weblogic@weblogic_server_01 workspace]$

 

Once this has been done, simply redeploy the Documentum Administrator and the next time you will access it in HTTPS, you will be able to transfer files, view the DA preferences, aso… The JREs are now trusted automatically because the checksum of the JRE is now signed properly.

 

 

Cet article Documentum – Using DA with Self-Signed SSL Certificate est apparu en premier sur Blog dbi services.

Developer GUI tools for PostgreSQL

Fri, 2017-08-04 13:33

There was a recent thread on the PostgreSQL general mailing list asking for GUI tools for PostgreSQL. This is question we get asked often at customers so I though it might be good idea to summarize some of them in a blog post. When you know other tools than the ones listed here which look promising, let me know so I can add them. There is a list of tools in the PostgreSQL Wiki as well.

Name Linux Windows MacOS Free Screenshot pgAdmin Y Y Y Y pg_gui_pgadmin DBeaver Y Y Y Y pg_gui_dbeaver EMS SQL Manager for PostgreSQL N Y N N pg_gui_ems_sql_manager JET BRAINS DataCrip Y Y Y N pg_gui_datagrip PostgreSQL Studio Y Y Y Y pg_gui_pgstudio Navicat for PostgreSQL Y Y Y N pg_gui_navicat execute Query Y Y Y Y pg_gui_executequery SQuirreL SQL Client Y Y Y Y pg_gui_aquirrel pgModeler Y Y Y Y pg_gui_pgmodeler DbSchema Y Y Y N pg_gui_dbschema Oracle SQL Developer Y Y Y Y pg_gui_sqldeveloper PostgreSQL Maestro N Y N N pg_gui_sqlmaestro SQL workbench Y Y Y Y pg_gui_sqlworkbench Nucleon Database Master N Y N N pg_gui_databasemaster Razor SQL Y Y Y N pg_gui_razorsql Database Workbench N Y N N pg_gui_databaseworkbench  

Cet article Developer GUI tools for PostgreSQL est apparu en premier sur Blog dbi services.

Exadata Capacity on Demand and Elastic Rack

Fri, 2017-08-04 11:18

Since X4 we can do Capacity on Demand on Exadata: disable some CPU cores to lower cost of Oracle Database licenses. Depending on the models, and the configuration, there are different minimums and here is a recap table about those.

Here is the summary of Capacity on Demand minimum, maximum and increment. Those numbers come from the configuration file of OEDA, the Oracle Exadata Deployment Assistant (es.properties) and you can see that it already has an option for Exadata X7-2

Exadata model sockets cores per socket cores per server thread per core Capacity on Demand minimum Cod maximum CoD increment X2-2 2 6 12 2 X3-2 2 8 16 2 X4-2 2 12 24 2 12
(not for 1/8th) 24 2 X5-2 2 18 36 2 14 36 2 X6-2 2 22 44 2 14
(8 for 1/8th) 44 2 X7-2 2 24 48 2 14
(8 for 1/8th) 48 2 X2-8 8 8 12 2 X3-8 8 10 12 2 X4-8 8 15 32 2 48 120 8 X5-8 8 18 32 2 56 144 8 X6-8 8 18 32 2 56 X7-8 8 24 32 2 SL6 2 32 64 8 14
(8 for 1/8th) 64 2 T7-2 2 32 62 8

 

Special minimums for 1/8th of Rack

The smallest configuration (1/8th of Rack) is a bit special. First, because it is physically identical to the 1/4th one with just some processors and disks disabled. But also, for this entry-level, the minimum required is lower – 8 cores per node – in X6.

Here is the Oracle Exadata Deployment Assistant for X6-2 1/8th of Rack:

CaptureOEDAx68002

When having selected 1/8th of Rack we are allowed to enable a minimum of 8 cores per nodes, as mentioned in the table above:

CaptureOEDAx68006

Elastic Rack

Elastic Rack configuration allows to configure any combination of database nodes and storage cells:

CaptureOEDAx68004

With Elastic Rack configuration, the next screen is not only displaying the configuration, but you can customize it.
Here I define the same configuration as an 8th of RAC:

CaptureOEDAx68005

However, because it is not an 1/8th Rack configuration, the minimum is 14 cores per node and not 8:

CaptureOEDAx68001

So be careful. Elastic configuration gives more flexibility, but CoD minimums are is different than the equivalent configuration.

/opt/oracle.SupportTools/resourcecontrol

As I’m talking about elastic configuration here is how the cores are enabled. The configuration assistant calls /opt/oracle.SupportTools/resourcecontrol which displays or updates the BIOS configuration. You may wonder why you can do that here and not in your own servers? Because here Oracle can trace what happened. You will find the log in /var/log/oracleexa/systemconfig.log and here is an example where the Elastic Rack has been deployed with 16 cores per database node Capacity on Demand:

Fri Aug 04 16:12:18 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -show
[INFO] Validated hardware and OS. Proceed.
[SHOW] Number of physical cores active per socket: 22
[SHOW] Total number of cores active: 44
 
Mon Aug 07 11:24:31 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -core 16 -force
[INFO] Validated hardware and OS. Proceed.
[INFO] Enabling 8 cores on each socket.
[INFO] Import all bios settings
[INFO] All bios settings have been imported with success
[ACTION] Reboot server for settings to take effect
[SHOW] Number of physical cores active per socket: 8
[SHOW] Total number of cores active: 16
 
Mon Aug 07 11:31:24 CEST 2017
Executing command: /opt/oracle.SupportTools/resourcecontrol -show
[INFO] Validated hardware and OS. Proceed.
[SHOW] Number of physical cores active per socket: 8
[SHOW] Total number of cores active: 16

This does not stay on your server. There is a rule that you can do Capacity on Demand only if you have configured Platinum support, or use Oracle Configuration Manager, or Enterprise Manager. All those may store history of the CPU count, which means that it is auditable.

 

Cet article Exadata Capacity on Demand and Elastic Rack est apparu en premier sur Blog dbi services.

A wonderful PostgreSQL feature: default privileges

Fri, 2017-08-04 02:52

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as well. In PostgreSQL there is an easier solution. Lets go …

Again we start by creating two users each with its own schema:

postgres=# create user a with login password 'a';
CREATE ROLE
postgres=# create schema a authorization a;
CREATE SCHEMA
postgres=# alter user a set search_path=a;
ALTER ROLE
postgres=# create user b with login password 'b';
CREATE ROLE
postgres=# create schema b authorization b;
CREATE SCHEMA
postgres=# alter user b set search_path=b;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 a         |                                                            | {}
 b         |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 a      | a
 b      | b
 public | postgres
(3 rows)

User “a” shall be the one owning the objects:

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=> create table t2 ( a int );
CREATE TABLE
postgres=> insert into t1 (a) values (1);
INSERT 0 1
postgres=> insert into t2 (a) values (2);
INSERT 0 1
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
(2 rows)

When you want to give user “b” access to these tables you could do:

postgres=> grant select on table t1 to b;
GRANT
postgres=> grant select on table t2 to b;
GRANT

From now on user “b” should be able to select from the two tables owned by user “a”, right?:

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t1;
ERROR:  permission denied for schema a
LINE 1: select count(*) from a.t1;

This is not how it works in PostgreSQL. What you need to do is this:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> grant usage on schema a to b;
GRANT

This allows user “b” access to the schema “a” (remember that a user and a schema are different things in PostgreSQL):

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t1;
 count 
-------
     1
(1 row)

postgres=> select count(*) from a.t2;
 count 
-------
     1
(1 row)

What happens now when user “a” creates another object:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t3 as select * from t1;
SELECT 1
postgres=> \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
 a      | t3   | table | a
(3 rows)

Will user “b” be able to select data from it?

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3

Of course not. The “usage” on a schema grants only access to that schema but not access to the objects in the schema. When we want user “b” being able to select from all tables in schema “a” even when user “a” creates new objects then we can modify the default privileges:

postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter default privileges in schema a grant select on tables to b;
ALTER DEFAULT PRIVILEGES

Should user “b” now be able to select from the “t3″ table in schema “a”?

postgres=> select current_user;
 current_user 
--------------
 b
(1 row)

postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3
postgres=> 

No. When you modify the default privileges this will affect only objects created after your modification. Lets create a new table with user “a” in schema “a”:

postgres=> \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t4 as select from t1;
SELECT 1

As this table was created after the modification to the default privileges user “b” is allowed to select from it automatically:

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t4;
 count 
-------
     1
(1 row)

When you check the link to the documentation above you’ll notice that you can not only grant select on tables but much more. Hope this helps …

 

Cet article A wonderful PostgreSQL feature: default privileges est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths III – Partial Index

Thu, 2017-08-03 10:58

In the previous post I said that an Index Only Access needs to find all rows in the index. Here is a case where, with similar data, Postgres can find all rows but Oracle needs additional considerations.

In the previous post I’ve executed:
select sum(n) from demo1
The execution plan was:

Aggregate (cost=295.29..295.30 rows=1 width=8) (actual time=2.192..2.193 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=30
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.277 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30

Basically, this reads all values of the column N and then aggregates them to the sum.
If I remove the SUM() I have only the part that reads all values from N:

explain (analyze,verbose,costs,buffers) select n from demo1 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.284 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.440 ms
Execution time: 1.972 ms

Oracle

This sounds logical. Now let’s run the same query, a simple ‘select n from demo1′ in Oracle:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 397 (100)| 10000 |00:00:00.01 | 1451 |
| 1 | TABLE ACCESS FULL| DEMO1 | 1 | 10000 | 397 (0)| 10000 |00:00:00.01 | 1451 |
--------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Here the access path is different: a full table scan instead of an index only access (Index Fast Full Scan). It is not a cost decision. If we try to force an index access, with INDEX_FFS() or INDEX() hints, the query will still do a Full Table Scan. The reason is that and index only access is possible only if all columns and all rows are present in the index. But Oracle does not always index all rows. The Oracle index has no entry for the rows where all the indexed columns are nulls.

Where n is not null

If I run the same query with the purpose of showing only non-null values, with a ‘where n is not null’ predicate, then an index only access is possible:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2gbjpw5u0v9cw, child number 0
-------------------------------------
select /*+ */ n from demo1 where n is not null
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 10000 |00:00:00.01 | 28 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 28 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N" IS NOT NULL)

Constraints

An alternative, if we know that we will never have null values here, is to give the information to the optimizer that there are no null values in the column N:
In Oracle:
alter table demo1 modify n not null;
This is the equivalent of the PostgreSQL
alter table demo1 alter column n set not null;
Then, in addition to ensuring the verification of the constraint, the constraint informs the optimizer that there is no null values and that all rows can be find in the index:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 10000 |00:00:00.01 | 28 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 28 |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Additional columns

Even if the column can have some null values, it is easy to have an index on null values in Oracle, just by adding a non-null column or expression. And if you don’t need this additional column, you can even add a constant, such as in the following index definition:

create unique index demo1_n on demo1(n,0);

This works because all index entries have at least one non null value. But looking at the buffers you can see that this additional byte (0 is stored in 1 byte) has a little overhead (31 blocks read here instead of 28):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID ad4z7tpt0dkta, child number 0
-------------------------------------
select /*+ */ n from demo1
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 10000 |00:00:00.01 | 31 |
| 1 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 8 (0)| 10000 |00:00:00.01 | 31 |
-------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]

Oracle Partial Indexes

In Oracle, all indexes that include a nullable column are partial indexes: not all rows are indexed, and an index access is possible only if the WHERE clause, or a constraint, guarantees that we don’t need the non-indexed rows. Combined with expression, it can be a way to implement partial indexes when the expression returns null for a specific condition. Oracle even provides computed columns (aka virtual columns) so that the expression does not have to be coded in the where clause of the query.

As an example with expressions, the following index has entries only for the values lower than 10:
create index demo_top10 on demo1(case when n<=10 then n end)

However, to use it, we must mention the expression explicitly:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 863drbjwayrt7, child number 0
-------------------------------------
select /*+ */ (case when n<=10 then n end) from demo1 where (case when
n<=10 then n end)<=5
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 4 |00:00:00.01 | 2 |
|* 1 | INDEX RANGE SCAN| DEMO1_N_TOP10 | 1 | 5 | 1 (0)| 4 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEMO1"."SYS_NC00004$"<=5)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEMO1"."SYS_NC00004$"[NUMBER,22]

We can see that internally, a virtual column (“SYS_NC00004$”) has been created for the indexed expression, and is used for the predicate and the projection which uses the same expression. There is another possibility with the ‘partial index’ feature introduced in 12c but it has not the flexibility of a predicate: it is based on partitioning where only some partitions can be indexed.

Postgres Partial Indexes

Postgres does not need those workarounds. An index indexes all rows, including null entries, and partial indexes can be defined with a where clause:
create index demo_top10 on demo1(n) where n<=10

No need to change the query. As long as the result can come from the partial index, we can use the column without an expression on it:

explain (analyze,verbose,costs,buffers) select n from demo1 where n<=5 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n_top10 on public.demo1 (cost=0.14..4.21 rows=4 width=4) (actual time=0.114..0.114 rows=5 loops=1)
Output: n
Index Cond: (demo1.n <= 5)
Heap Fetches: 0
Buffers: shared hit=2
Planning time: 0.557 ms
Execution time: 0.129 ms

Here the smaller partial index (demo1_n_top10) has been chosen by the query planner.

As you see I’ve not used exactly the same condition. The query planner understood that n<=5 (in the WHERE clause) is a subset of n<=10 (in the index definition). However, if the predicate is too different, it cannot use the index:

fpa=# explain (analyze,verbose,costs,buffers) select n from demo1 where 2*n<=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using demo1_n on public.demo1 (cost=0.29..320.29 rows=3333 width=4) (actual time=0.020..1.086 rows=5 loops=1)
Output: n
Filter: ((2 * demo1.n) <= 10)
Rows Removed by Filter: 9995
Heap Fetches: 0
Buffers: shared hit=30

Here, instead of “Index Cond” we have a simple “Filter”. The Index Only Scan has read all the rows, and they were filtered afterward (“Rows Removed by Filter”).

Index condition

With the VERBOSE option of EXPLAIN we see the condition used by the index access:
Index Cond: (demo1.n <= 5)
‘Index Cond.’ is not a simple filter removing rows after an operation, but it is the condition which is used for fast access to the index entries in the sorted index structure. We have the equivalent in Oracle with the ‘+predicate’ format of dbms_xplan:

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"<=5)

Before going further on index access for WHERE clause predicate, the next post will show the major characteristic of indexes (besides the fact that it stores a redundant subset of columns and rows): they are maintained sorted and may return the resulting rows in order.

 

Cet article Postgres vs. Oracle access paths III – Partial Index est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths II – IndexOnlyScan

Wed, 2017-08-02 10:00

In the previous post I’ve explained a sequential scan by accident: my query needed only one column which was indexed, and I expected to read the index rather than the table. And I had to hint the Oracle example to get the same because the Oracle optimizer chooses the index scan over the table scan in that case. Here is where I learned a big difference between Postgres and Oracle. They both use MVCC to query without locking, but Postgres MVCC is for table rows (tuples) only whereas Oracle MVCC is for all blocks – tables and indexes.

So this second post is about Index Only Scan and the second constant you find in the documentation for the query planner:
random_page_cost (floating point)
Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0.


I am here in the situation after the previous post: created table and index, have run a query which did a sequential scan on the table:

explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=17.430..17.430 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.031..13.011 rows=10000 loops=1)
Output: n, a, x
Buffers: shared hit=1429
Planning time: 1.791 ms
Execution time: 17.505 ms

Index Only Scan

I want to understand why the query planner did not choose an access to the index only. This is where hints are useful: force a plan that is not chosen by the optimizer in order to check if this plan is possible, and then check its cost:

/*+ IndexOnlyScan(demo1) */
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1727.29..1727.30 rows=1 width=8) (actual time=5.424..5.425 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429 read=29
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..1702.29 rows=10000 width=4) (actual time=0.177..4.613 rows=10000 loops=1)
Output: n
Heap Fetches: 10000
Buffers: shared hit=1429 read=29
Planning time: 0.390 ms
Execution time: 5.448 ms

From there you see that an Index Only Scan is possible but more expensive. The estimated cost is higher than the Seq Scan (cost=0.29..1702.29 instead of cost=0.00..1529.00). And the execution statistics shows that I’ve read the 1429 table pages in addition to the 29 pages of the index.

From the hit/read statistics we can note that the create table has left all the table pages in the buffer cache, but this is not the case for the create index. But that’s another story. My concern is why and index only access goes to read all table blocks in addition to the index ones, which brings the cost to 1727.30-1554.01=173.29 higher than the sequential scan.

The clue is in this line showing that all my rows were fetched from heap page, which is the table: Heap Fetches: 10000

Tuple visibility

In ACID databases, a modification must not be visible by others until the transaction completion (commit). There are two ways to achieve that. The first way is to read the latest version of data: lock in share mode what you read, so that no concurrent update can happen. The other solution is to query a previous version of data (MVCC – Multi Version Concurrency Control) where uncommitted changes are not visible. Both Oracle and Postgres use MVCC which is great because you can have transactions and queries on the same database. But they do the versioning at a different level.

Oracle MVCC is physical, at block level. Then everything is versioned: tables as well as index, with their transaction information (ITL) which, with the help of the transaction table, give all information about visibility: committed or not, and with the commit SCN. With this architecture, a modified block can be written to disk even with uncommitted changes and there is no need to re-visit it later once the transaction is committed.

Postgres MVCC is logical at row (‘tuple’) level: new version is a new row, and committed changes set the visibility of the row. The table row is versioned but not the index entry. If you access by index, you still need to go to the table to see if the row is visible to you. This is why I had heap fetches here and the table blocks were read.

This explains that the cost of Index Only Scan is high here. In addition to about 30 index blocks to read, I’ve read about 1429 table blocks. But that can be worse. For each index entry, and I have 10000 of them, we need to go to the table row, which is exactly what the 10000 heap fetches are. But I’m lucky because I have a very good clustering factor: I have created the table with increasing values for the column N (generated by generate_series). With a bad clustering factor (physical storage of rows in the table not correlated with the order of index) you would see up to 10000 additional shared hits. Thankfully, the query planner estimates this and has switched to table scan which is cheaper in this case.

Vacuum and Visibility Map

Always going to the table rows to see if they are committed would always be more expensive than a table scan. The Postgres vacuum process maintains a Visibility Map as a bitmap of pages that have been vacuumed and have no more tuples to vacuum. This means that all rows in those pages are visible to all transactions. When there is an update on the page, the flag is unset, and remains unset until the modification is committed and the vacuum runs on it. This visibility flag is used by the Index Only Scan to know if it is needed to get to the page.

Let’s run the vacuum and try again the same query:

vacuum demo1;
VACUUM
 
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=295.29..295.30 rows=1 width=8) (actual time=2.192..2.193 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=30
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.277 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.450 ms
Execution time: 2.213 ms

Here, without any hint, the query planner has chosen the Index Only Scan which is now less expensive than a Seq Scan: cost=0.29..270.29

Cost of Index Only Scan

There is an initial cost of 0.29 is calculated from cpu_operator_cost which defaults 0.0025 which means that about 0.29/0.0025=116 operations were charged here. This cost is minimal and I don’t go into details.
CaptureIndexScanpgora
Then, to get rows we have to

  • read 30 blocks from the index. Those seem to be random scan (with random_page_cost=4) and then the cost for all rows is 4*30=120
  • process the index entries (with cpu_index_tuple_cost=0.005) and then the cost for all 10000 rows is 0.005*10000=50
  • process the result rows (with cpu_tuple_cost=0.01) and then the cost for all 10000 rows is 0.01*10000=100

This brings the cost to the total of 270.29

For the above operation, the SUM(N) this is exactly the same as in the previous post on Seq Scan: cost=25 (cpu_operator_cost=0.0025 for 10000 rows) and is this initial cost because the sum is now only when all rows are processed, and an additional 0.01 for the result row.

Oracle

In the previous post I used the FULL() hint to compare Oracle Full Table Scan to Postgres Seq Scan, but by default, Oracle chose an index only access because the index covers all the rows and columns we need.

All columns that we need:

In the previous post we have seen the column projection (from the +projeciton format of dbms_xplan):

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - (rowset=256) "N"[NUMBER,22]

I need only the column N from the table DEMO1, and this column is in the index DEMO1_N

All rows that we need:

In Oracle an index does not have an entry for every row but only for rows where at least one of the indexed columns is not null. Here because we have no where clause predicate on N, and because we have not declared the column N as NOT NULL, the access by index may not return all rows. However, the SUM() function does not need to know about the null values, because they don’t change the sum and then the optimizer can safely choose to do an index only access.

Here is the query without hints:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6z194712fvcfu, child number 0
-------------------------------------
select /*+ */ sum(n) from demo1
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 1 |00:00:00.01 | 26 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 26 |
| 2 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 26 |
--------------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - "N"[NUMBER,22]

This plan looks very similar to the Postgres one after the vacuum: 51 buffers which is approximately the number of blocks in my index here. However, Oracle does not have the ‘vacuum’ requirement because the MVCC applies to the index and Oracle does not need to go to the table to undo the uncommitted changes. But there is something else here. If you remember the previous post, the Oracle cost=1 is equivalent to the cost of a random read (single block) and the cost of reading one block through a larger I/O (multiblock read) is, with default statistics, about 0.278 times cheaper. Here, 7/26= 0.2692 which proves that the cost is based on multiblock reads. Oracle can read indexes with INDEX FAST FULL SCAN in the same way it reads table with FULL TABLE SCAN: with larger I/O. We don’t need any ordering of rows here, because we just do the sum, and then we don’t need to follow the chain of leaf blocks, scattered within the index segment. Just read all blocks as they come, with fast I/O.

Index Fast Full Scan is possible in Oracle because MVCC is at block level for indexes as well as tables. You can just read the blocks as of the point in time of the query, without being concerned by concurrent operations that update the index entries or split the blocks. Postgres Index Only Scan is limited because MVCC is on tables only, and then must scan the index in the order of leaves, and must read the visibility map and maybe the table pages.

In Oracle, an index can be used to partition vertically a table, asa redundant storage of a few columns in order to avoid full table scans on large rows, allowing queries to avoid completely to read the table when the index covers all required rows and columns. We will see more about the ‘all rows’ requirement in the next post.

 

Cet article Postgres vs. Oracle access paths II – IndexOnlyScan est apparu en premier sur Blog dbi services.

Oracle Cloud: script to stop all PaaS services

Wed, 2017-08-02 00:56

With metered cloud services, keeping all your instances running may become expensive. The goal is to start them only when you need them. Here is a script that stops all instances you have on the Oracle Cloud Service PaaS. You can schedule it for example to stop them at the end of the business day, or when they are not active for a long time. The scripts use the REST API called with curl, JSON output parsed with jq, HTTP status explained with links.

In the first part of the script, I set the variables. Set them to your user:password, identity domain, cloud service url, ssh key:

u="MyEmail@Domain.net:MyPassword"
i=a521642
r=https://dbcs.emea.oraclecloud.com
k="ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCxAEm1WHYbJa50t61YhM53u4sljjSFGK458fgdljjkNqfihcRxSf2ENw6iaYhiBTPogG9IDaEqW+SbwpororD2/bep16/hHybGswD34jU7bf9kaaKi5gOgASChid4e322zrnwOtlzfHiiquhiUDgLwpQxCYVV5zU1RQ2NS3F3a45bepqkn/GuPY5x/KSn576+7HBCYVbao/PTwZAeTVbo6Xb4ZQQrOIsLJxxDCQsr0/g7ZS8/OJHt8kotydu13n3rANB2y312XtTsW9mAwpfXuCuqDM5+dIjUdmtflkYtqsfrqSpLevVfVt1L7hqo+JGo7OBO0doVs6lQSCblZhYHh Me@MyLaptop"

Here is the script. It starts to download the certificate if not already there. Then queries for all non stopped services and stops them. Finally, the last line displays the status of all services.


[ -f cacert.pem ] || curl --remote-name --time-cond cacert.pem https://curl.haxx.se/ca/cacert.pem
 
for s in $( curl -s --request GET --cacert cacert.pem --user $u --header "X-ID-TENANT-NAME:$i" $r/paas/service/dbcs/api/v1.1/instances/$i | jq -r '.services[]|select(.status!="Stopped")|.service_name' )
do
# call the 'Stop service' REST API and get the http status
httpstatus=$(curl --include --request POST --cacert cacert.pem --user $u --header "X-ID-TENANT-NAME:$i" --header "Content-Type:application/json" --data '{"lifecycleState":"Stop"}' $r/paas/service/dbcs/api/v1.1/instances/$i/$s | awk '{print >"/dev/stderr"} /^HTTP/{print $2}')
# look for http status in documentation
links -dump -width 300 https://docs.oracle.com/en/cloud/paas/java-cloud/jsrmr/Status%20Codes.html | grep -B 1 -A 1 " $httpstatus "
done
 
sleep 1
curl -s --request GET --cacert cacert.pem --user $u --header "X-ID-TENANT-NAME:$i" $r/paas/service/dbcs/api/v1.1/instances/$i | jq .

The script requires:

  • curl to call the REST API
  • jq to format and extract the returned JSON
  • links to get the HTTP status description from the documentation

The Cloud is all about automation and the REST API makes it very easy to do from command line or script.

 

Cet article Oracle Cloud: script to stop all PaaS services est apparu en premier sur Blog dbi services.

Display Data Guard configuration in SQL Developer

Tue, 2017-08-01 13:34

The latest version of SQL Developer, the 17.2 one released after Q2 of 2017, has a new item in the DBA view showing the Data Guard configuration. This is the occasion to show how you can cascade the log shipping in Oracle 12c

A quick note about this new versioning: this is the release for 2017 Q2 and the version number has more digits to mention the exact build time. Here this version is labeled 17.2.0.188.1159 and we can see when it has been built:

SQL> select to_date('17.x.0.188.1159','rr."x.0".ddd.hh24mi') build_time from dual;
 
BUILD_TIME
--------------------
07-JUL-2017 11:59:00

Non-Cascading Standby

Here is my configuration with two standby databases:

DGMGRL> show configuration
Configuration - orcl
 
Protection Mode: MaxPerformance
Members:
orcla - Primary database
orclb - Physical standby database
orclc - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS (status updated 9 seconds ago)

I have only the LogXptMode defined here, without any RedoRoutes

DGMGRL> show database orcla LogXptMode
LogXptMode = 'SYNC'

with this configuration, the broker has set the following log destination on orcla, orclb and orclc:

INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLA log_archive_dest_1 location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)
ORCLA log_archive_dest_2 service="ORCLB", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="orclb" net_timeout=30, valid_for=(online_logfile,all_roles)
ORCLA log_archive_dest_3 service="ORCLC", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="orclc" net_timeout=30, valid_for=(online_logfile,all_roles)
 
INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLB log_archive_dest_1 location=/u01/fast_recovery_area
 
INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLC log_archive_dest_1 location=/u01/fast_recovery_area

In the latest SQL Developer you have the graphical representation of it from the DBA view / Dataguard / console:

SDDG001

Cascading Standby

In 12c we can define cascading standby: instead of the primary shipping the redo to all standby databases, you can have the primary shipping to one standby only, and this one can forward the redo to another one. You define that with the RedoRoute property:


DGMGRL> edit database orcla set property redoroutes = '(local:orclb) (orclb:orclc async)';
Property "redoroutes" updated
DGMGRL> edit database orclb set property redoroutes = '(orcla:orclc async) (local:orcla)';
Property "redoroutes" updated

The first route defined in each property is applied when orcla is the primary database:

  • on orcla (local:orclb) means that orcla sends redo to orclb when primary
  • on orclb (orcla:orclc async) means that orclb sends redo to orclc when orcla is primary. LogXptMode is SYNC but overriden here with ASYNC

The second route defined in each property is applied when orclb is the primary database:

  • on orcla (orclb:orclc async) means that orclb sends redo to orclc when orclb is primary. LogXptMode is SYNC but overriden here with ASYNC
  • on orclb (local:orcla) means that orclb sends redo to orcla when primary

With this configuration, and orcla still being the primary, the broker has set the following log destination on orcla, orclb and orclc:


INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLA log_archive_dest_1 location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES, ALL_ROLES)
ORCLA log_archive_dest_2 service="ORCLB", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300
db_unique_name="orclb" net_timeout=30, valid_for=(online_logfile,all_roles)
 
INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLB log_archive_dest_1 location=/u01/fast_recovery_area
ORCLB log_archive_dest_2 service="ORCLC", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=3
00 db_unique_name="orclc" net_timeout=30, valid_for=(standby_logfile,all_roles)
 
INSTANCE_NAME NAME VALUE
---------------- -------------------- -------------------------------------------------------------------------------------------------------------
ORCLC log_archive_dest_1 location=/u01/fast_recovery_area

The show configuration from DGMGRL displays them indented to see the cascading redo shipping:

DGMGRL> show configuration
Configuration - orcl
 
Protection Mode: MaxPerformance
Members:
orcla - Primary database
orclb - Physical standby database
orclc - Physical standby database (receiving current redo)
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS (status updated 27 seconds ago)

And SQL Developer Data Guard console shows:
SDDG002

Switchover

Now the goal of defining several routes is to have all log destination automatically changed when the database role change.
I’m doing a switchover:


Connected to "orclb"
Connected as SYSDG.
DGMGRL> switchover to orclb;
Performing switchover NOW, please wait...
New primary database "orclb" is opening...
Operation requires start up of instance "ORCLA" on database "orcla"
Starting instance "ORCLA"...
ORACLE instance started.
Database mounted.
Database opened.
Connected to "orcla"
Switchover succeeded, new primary is "orclb"

Now it is orcla which cascades the orclb redo to orclc:

DGMGRL> show configuration;
Configuration - orcl
 
Protection Mode: MaxPerformance
Members:
orclb - Primary database
orcla - Physical standby database
orclc - Physical standby database (receiving current redo)
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS (status updated 74 seconds ago)

Here is how it is displayed from SQL Developer:

SDDG003

We have seen how the configuration is displayed from DGMGRL and graphically from SQL Developer. Of course, you can also query the Data Guard configuration:

SQL> select * from V$DATAGUARD_CONFIG;
 
DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID
-------------- ----------- --------- ----------- ------
orcla orclb PHYSICAL STANDBY 3407900 0
orclc orcla PHYSICAL STANDBY 3408303 0
orclb NONE PRIMARY DATABASE 0 0

and the broker configuration:

SQL> select * from V$DG_BROKER_CONFIG;
 
DATABASE CONNECT_IDENTIFIER DATAGUARD_ROLE REDO_SOURCE ENABLED STATUS VERSION CON_ID
-------- ------------------ -------------- ----------- ------- ------ ------- ------
orcla ORCLA PHYSICAL STANDBY -UNKNOWN- TRUE 0 11.0 0
orclb ORCLB PRIMARY -N/A- TRUE 0 11.0 0
orclc ORCLC PHYSICAL STANDBY orcla TRUE 0 11.0 0

This another reason to use the broker. Once the configuration is setup and tested, you have nothing else to think about when you do a switchover. The log archive destination is automatically updated depending on the database roles.

 

Cet article Display Data Guard configuration in SQL Developer est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths I – Seq Scan

Tue, 2017-08-01 10:58

Here is the first test I’ve done for my Postgres vs. Oracle access paths series and the first query did a sequential scan. It illustrates the first constant you find in the documentation for the query planner:
seq_page_cost (floating point)
Sets the planner’s estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0.

Table creation

I start by creating a very simple table with 10000 rows and 3 columns. The first column(n) is indexed:

create table demo1 as select generate_series n , 1 a , lpad('x',1000,'x') x from generate_series(1,10000);
SELECT 10000
create unique index demo1_n on demo1(n);
CREATE INDEX
 
analyze verbose demo1;
INFO: analyzing "public.demo1"
INFO: "demo1": scanned 1429 of 1429 pages, containing 10000 live rows and 0 dead rows; 10000 rows in sample, 10000 estimated total rows
ANALYZE
select relkind,relname,reltuples,relpages from pg_class where relname='demo1';
relkind | relname | reltuples | relpages
---------+---------+-----------+----------
r | demo1 | 10000 | 1429
 
select relkind,relname,reltuples,relpages from pg_class where relname='demo1_n';
relkind | relname | reltuples | relpages
---------+---------+-----------+----------
i | demo1_n | 10000 | 30

I checked the table and index statistics that will be used by the optimizer: 10000 rows, all indexed, 1429 table blocks and 30 index blocks. Note that blocks are called pages, but that’s the same idea: the minimal size read and written to disk. They are also called buffers as they are read into a buffer and cached in the buffer cache.

Here is how I create a similar table in Oracle:

create table demo1 as select rownum n , 1 a , lpad('x',1000,'x') x from xmltable('1 to 10000');
Table created.
create unique index demo1_n on demo1(n);
Index created.
exec dbms_stats.gather_table_stats(user,'demo1');
PL/SQL procedure successfully completed.
 
select table_name,num_rows,blocks from user_tables where table_name='DEMO1';
 
TABLE_NAME NUM_ROWS BLOCKS
---------- ---------- ----------
DEMO1 10000 1461
 
select index_name,num_rows,leaf_blocks,blevel from user_indexes where table_name='DEMO1';
 
INDEX_NAME NUM_ROWS LEAF_BLOCKS BLEVEL
---------- ---------- ----------- ----------
DEMO1_N 10000 20 1

The same rows are stored in 1421 table blocks and the index entries in 20 blocks. Both use 8k blocks, but different storage layout and different defaults. This is about 7 rows per table blocks, for rows that are approximately larger than 1k and about 500 index entries per index block to store the number for column N plus the pointer to table row (a few bytes called TID in Postgres or ROWID for Oracle). I’ll not get into the details of the number here. More about the row storage:

My goal is to detail the execution plans and the execution statistics.

Postgres Seq Scan

I start with a very simple query on my table: SELECT SUM(N) from DEMO1;


explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=4.616..4.616 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.011..3.614 rows=10000 loops=1)
Output: n, a, x
Buffers: shared hit=1429
Planning time: 0.468 ms
Execution time: 4.661 ms

This query does a sequential scan (Seq Scan), which is the equivalent of Oracle Full Table Scan: read all rows from the table. You might tell me that it would be cheaper to scan the index because the index I’ve created holds all required columns. We will see that in the next post. Here, after having created the table as I did above, the query planner prefers to scan the table.

Here are the maths: my table has 1429 pages and each page access during a sequential scan has cost=1 as defined by:

show seq_page_cost;
seq_page_cost
---------------
1

Here, I see a cost estimated from 0 to 1529 for the Seq Scan operation.
The first number, 0.00 is the initialization cost estimating the work done before returning any rows. A Seq Scan has nothing to do before, and reading the first block can already return rows.
The second number is the cost to return all rows. We have seen that the scan itself costs 1429 but the rows (tuples) must be read and processed. This is evaluated using the following constant:

show cpu_tuple_cost;
cpu_tuple_cost
----------------
0.01

For 10000 rows, the cost to process them is 0.01*10000=100 which is an additional cost over the Seq Scan 1429 to get it to 1529. This explains cost=0.00..1529.00

Then there is a SUM operation applied to 10000 rows and there is a single parameter for the CPU cost of operators and functions:

show cpu_operator_cost;
cpu_operator_cost
-------------------
0.0025

Capturepgoraseqscan001
The sum (Aggregate) operation adds 0.0025*10000=25 to the cost and then the cost is 1554. You can see this cost in the minimal cost for the query, the first number in cost=1554.00..1554.01, which is the cost before retrieving any rows. This makes sense because before retrieving the first row we need to read (Seq Scan) and process (Aggregate) all rows, which is exactly what the cost of 1554 is.

Then there is an additional cost when we retrieve all rows. It is only one row here because it is a sum without group by, and this adds the default cpu_tuple_cost=0.01 to the initial cost: 1554.01

In summary, The total cost of the query is cost=1554.00..1554.01 and we have seen that it depends on:
– number of pages in the table
– number of rows from the result of the scan (we have no where clause here)
– number of rows summed and retrieved
– the planner parameters seq_page_cost, cpu_tuple_cost, and cpu_operator_cost

Oracle Full Table Scan

When I run the same query on Oracle, the optimizer chooses an index fast full scan rather than a table full scan because all rows and columns are in the index that I’ve created:

  • all rows because the SUM(N) do not need to get rows where N is not null (which are not stored in the index)
  • all columns because I need nothing else than the values for N

We will see that in the next post, for the moment, in order to compare with Postgres, I forced a full table scan with the FULL() hint.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bhsjquhh6y08q, child number 0
-------------------------------------
select /*+ full(demo1) */ sum(n) from demo1
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 397 (100)| 1 |00:00:00.01 | 1449 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 1449 |
| 2 | TABLE ACCESS FULL| DEMO1 | 1 | 10000 | 397 (0)| 10000 |00:00:00.01 | 1449 |
---------------------------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - (rowset=256) "N"[NUMBER,22]

We have seen that Postgres cost=1 is for sequential scans (similar to what we call multiblock reads in Oracle) and random reads (single block reads) have by default cost=4 according to random_page_cost.

Oracle cost unit is based on single block reads and this is why the cost here (397) is lower than the number of blocks (1461). Different units. Postgres counts cost=1 for reads and counts a higher cost when a seek is involved. Oracle counts cost=1 for single block reads (including seek) and lower cost for larger I/O size.
Capturepgoraseqscan002
With the default system statistics, where latency is estimated 10 milliseconds and transfer is estimated to 4KB/ms. The single block read time is estimated to 12 milliseconds (10 + 8192/4096).
Again with the default system statistics where optimizer estimates 8 blocks per multiblock read, the multiblock read time is estimated to 26 milliseconds (10 + 8*8192/4096) which is on average 26/8=3.25 millisecond per block. This means that the ratio of single vs. multi block read is very similar for Oracle (3.25/12=0.27833333) and Postgres (seq_page_cost /random_page_cost=1/4=0.25) with default parameters.

Our table is stored in 1461 blocks and the full table scan involves reading all of them plus some segment header blocks. 1461*0.27833333=396

There is also the costing of CPU (the equivalent to cpu_tuple_cost) which is included here but I’ll not go into the details which are more complex than in Postgres and depends on your processor frequency. The goal of those posts is about Postgres. For Oracle, all this is explained in Jonathan Lewis and Chris Antognini books.

But basically, the idea is the same: Postgres Seq Scan and Oracle Full table Scan read the contiguous table blocks sequentially and the cost mainly depends on the size of the table (number of blocks) and the estimated time for sequential I/O (where bandwidth counts more than latency).

Buffers

In my tests, I’ve not only explained the query, but I executed it to get execution statistics. This is done with EXPLAIN ANALYZE in Postgres and DBMS_XPLAN.DISPLAY_CURSOR in Oracle. The statistics include the number of blocks read at each plan operation, with the BUFFERS option in Postgres and with STATISTICS_LEVEL=ALL in Oracle.


explain (analyze,buffers) select sum(n) from demo1 ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=3.622..3.622 rows=1 loops=1)
Buffers: shared hit=1429
-> Seq Scan on demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.008..1.724 rows=10000 loops=1)
Buffers: shared hit=1429
Planning time: 0.468 ms
Execution time: 4.661 ms

‘Buffers’ displays the number of blocks that have been read by the Seq Scan and is exactly the number of pages in my table. ‘shared hit’ means that they come from the buffer cache.

Let’s run the same when the cache is empty:

explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=13.837..13.837 rows=1 loops=1)
Output: sum(n)
Buffers: shared read=1429
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.042..12.506 rows=10000 loops=1)
Output: n, a, x
Buffers: shared read=1429
Planning time: 3.754 ms
Execution time: 13.906 ms

The buffers are now ‘shared read’ instead of ‘shared hit’. In Postgres, the number of logical reads, as we know them in Oracle, is the sum of hits and reads. In Oracle, all blocks are counted as logical reads, which includes the smaller set of physical reads.

IO calls

Here is more about the reads when the block is not in the buffer cache. On Linux, we can trace the system calls to see how those sequential I/Os are implemented.

I get the ‘relfilenode':

postgres=# select relname,relnamespace,reltype,relowner,relfilenode,relpages,reltuples from pg_class where relname='demo1';
relname | relnamespace | reltype | relowner | relfilenode | relpages | reltuples
---------+--------------+---------+----------+-------------+----------+-----------
demo1 | 2200 | 42429 | 10 | 42427 | 1429 | 10000

I get the pid of my session process:

select pg_backend_pid();
-[ RECORD 1 ]--+------
pg_backend_pid | 30732

I can trace system calls:

strace -p 30732

And look at the trace concerning my file (identified with its ‘relfilenode’):

30732 open("base/12924/42427", O_RDWR) = 33
30732 lseek(33, 0, SEEK_END) = 11706368
30732 open("base/12924/42427_vm", O_RDWR) = 43
30732 lseek(33, 0, SEEK_END) = 11706368
30732 lseek(33, 0, SEEK_END) = 11706368
30732 lseek(33, 0, SEEK_SET) = 0
30732 read(33, "\4004\220\3 \4 \360\233\30\10\340\227\30\10"..., 8192) = 8192
30732 read(33, "\4004\220\3 \4 \360\233\30\10\340\227\30\10"..., 8192) = 8192
30732 read(33, "\4004\220\3 \4 \360\233\30\10\340\227\30\10"..., 8192) = 8192
... 1429 read(33) in total

We see two open() calls with the relfilenode of my table in the file name: one for the table and one for the visibility map
The file descriptor for the table file is 33 and I’ve grepped only the related calls.
The lseek(33,0,SEEK_END) goes to the end of the file (11706368 bytes, which is 11706368/8192=1429 pages.
The lseek(33,0,SEEK_SET) goes to the beginning of the file.
Subsequent read() calls read the whole file, reading page per page (8192 bytes), in sequential order.

This is how sequential reads are implemented in Postgres: one lseek() and sequential read() calls. The I/O size is always the same (8k here). The benefit of sequential scan is not larger I/O calls but simply the absence of seek() in between. The optimization is left to the underlying layers filesystem and read-ahead.

This is very different from Oracle. Not going into the details, here are the kind of system calls you see during the full table scan:

open("/u01/oradata/CDB1A/PDB/users01.dbf", O_RDWR|O_DSYNC) = 9
fcntl(9, F_SETFD, FD_CLOEXEC) = 0
fcntl(9, F_DUPFD, 256) = 258
...
pread(258, "\6\242\2\5\3\276\25%\2\4\24\270\1\313!\1x\25%"..., 1032192, 10502144) = 1032192
pread(258, "\6\242\202\5\3\300\25%\2\4\16\247\1\313!\1x\25%"..., 1032192, 11550720) = 1032192
pread(258, "\6\242\2\6\3\302\25%\2\4x\226\1\313!\1x\25%"..., 417792, 12599296) = 417792

Those are also sequential reads of contiguous blocks but done with larger I/O size (126 blocks here). So in addition to the absence of seek() calls, it is optimized to do less I/O calls, not relying on the underlying optimization at OS level.

Oracle can also trace the system calls with wait events, which gives more information about the database calls:

WAIT #140315986764280: nam='db file scattered read' ela= 584 file#=12 block#=1282 blocks=126 obj#=74187 tim=91786554974
WAIT #140315986764280: nam='db file scattered read' ela= 485 file#=12 block#=1410 blocks=126 obj#=74187 tim=91786555877
WAIT #140315986764280: nam='db file scattered read' ela= 181 file#=12 block#=1538 blocks=51 obj#=74187 tim=91786556380

The name ‘scattered’ is misleading. ‘db file scattered read’ are actually multiblock reads: read more than one block in one I/O call. Oracle does not rely on the Operating System read-ahead and this is why we can (and should) use direct I/O and Async I/O if the database buffer cache is correctly sized.

Output and Projection

I’ve run the EXPLAIN with the VERBOSE option which shows the ‘Output’ for each operation, and I’ve done the equivalent in Oracle by adding the ‘+projection’ format in DBMS_XPLAN.

In the Oracle execution plan, we see the columns remaining in the result of each operation, after the projection:

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) SUM("N")[22] 2 - (rowset=256) "N"[NUMBER,22]

The operation 2, the Full Table Scan, reads all rows with all columns, but selects only the one we need: N

In the Postgres equivalent, it seems that the Output mentions the columns available before the projection because we see all table columns here:

explain verbose select sum(n) from demo1 ;
QUERY PLAN
-------------------------------------------------------------------------
Aggregate (cost=1554.00..1554.01 rows=1 width=8)
Output: sum(n)
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4)
Output: n, a, x

I prefer to see the columns after the projection and I use it a lot in Oracle to know which columns are needed from the table. A great optimization can be done when we have a covering index where all selected columns are present so that we don’t have to go to the table. But we will see that in the next post about Index Only Scan.

 

Cet article Postgres vs. Oracle access paths I – Seq Scan est apparu en premier sur Blog dbi services.

Postgres unique constraint

Tue, 2017-08-01 02:11

I’ll start a series on Postgres vs. Oracle access paths because I know Oracle and I learn Postgres. While preparing it, I came upon some surprises because I’m so used to Oracle that I take some behavior as granted for any SQL databases. I recently posted a tweet about one of them, comparing latest Postgres version to earliest Oracle version I have on my laptop.
The goal of the tweet was exactly what I said above: show my surprise, using Oracle 7 as a reference because this is the version where I started to learn SQL. And there’s no judgment behind this surprise: I can’t compare a software I use for more than 20 years with one I’m just learning. I have a big admiration for the Oracle design and architecture choices. But I’ve also a big admiration for what the Postgres community is doing.

In my tweet I’ve updated a primary key. I think I’ve never designed in real life a primary key that has to be updated later. For each table we need a key that is immutable to identify rows for referential integrity constraints, or for replication. The value must be known from the first insert (which means the columns are declared not null) and the value is never updated. It makes sense to use a primary key for that as it is unique and not null.

Actually, a better case would be a simple unique constraint where we just exchange two rows. A real-life example is a list of items, having probably a surrogate key as the primary key, and a unique key including an item number. When the user wants to move up one item, we just run an update on two rows, exchanging their numbers. The unique constraint just ensures that we have only distinct values so that a select … order by will always return the values in the same order.

All similar cases have the same consequence: when you process row by row the update, the uniqueness may be violated. But at the end of the statement, the constraint is still valid.

Here is the initial example with updating all rows:


create table demo as select generate_series n from generate_series(1,2);
SELECT 2
alter table demo add constraint demo_pk primary key(n);
ALTER TABLE
select * from demo;
n
---
1
2
(2 rows)
 
begin transaction;
BEGIN
update demo set n=n-1;
UPDATE 2
select * from demo;
n
---
0
1
(2 rows)

This works. I’ve inserted the rows in ascending order of n. Decreasing the value doesn’t violate the uniqueness at any time because it reads rows from the beginning to the end.

However, when we increase the value, we have a duplicate value until we process the next row. And by default, Postgres fails:

update demo set n=n+1;
ERROR: duplicate key value violates unique constraint "demo_pk"
DETAIL: Key (n)=(1) already exists.

The bad thing is that the behavior of the application depends on the physical order of the rows and the order where they are processed. This violates the Codd rule about physical independence. In addition to that, the SQL statements should behave as processing the set of rows rather than low-level row-by-row processing.

But there is also a very good thing: because the constraint is validated row by row, you know which value violates the constraint (here: “DETAIL: Key (n)=(1) already exists” ).

So my statement failed and this in Postgres seems to fail the whole transaction:

commit;
ROLLBACK

My second surprise is that the failure of one statement cancels the whole transaction. I see no error at commit, but it simply tells me that it has done a rollback instead of the commit.

deferrable

So, I compared with Oracle where this statement is always successful, because temporary violations that are resolved later, within the same statement, do not violate the constraint. I compared it with the oldest version I have on my laptop (Oracle 7.3) to show that it is something I’ve never seen as a new feature because I started with Oracle 7. And this kind of thing is the reason why I like SQL. Doing the same with a procedural language requires an intermediate update to be sure that there is no duplicate at any time.

The Postgres community is very responsive, especially when we may think that something works better in Oracle than Postgres (which was not the case here and which was not the goal of my tweet anyway – but tweets are short and may not express the tone properly).

Quickly a solutions were proposed: deferred constraint (example in this blog post).

I know deferred constraints in Oracle. They are similar in Postgres and here is the solution proposed:


alter table demo drop constraint demo_pk;
ALTER TABLE
alter table demo add constraint demo_pk primary key(n) deferrable initially deferred;
ALTER TABLE
begin transaction;
BEGIN
update demo set n=n-1;
UPDATE 2
select * from demo;
n
---
0
1
(2 rows)
 
update demo set n=n+1;
UPDATE 2

That seems good. Because the constraint validation is deferred, the update is successful.

However, this is not what I want. I want the previous statement to succeed, but I want the following statement to fail:

insert into demo values(1);
INSERT 0 1

Because constraint is deferred, this statement is successful and it is only at commit that it fails:

commit;
ERROR: duplicate key value violates unique constraint "demo_pk"
DETAIL: Key (n)=(1) already exists.

Why do I think this is not the good solution? First, because I want the statement to fail as soon as possible. And in addition to that, I want the commit to be fast. Doing expensive things at commit should be avoided, if possible. It is the point where all work is supposed to be done and you just want to save it (make it durable and visible to others).

deferrable initially immediate

Actually, the solution is to declare the constraint as deferrable, but not deferred.

alter table demo drop constraint demo_pk;
ALTER TABLE
alter table demo add constraint demo_pk primary key(n) deferrable initially immediate;
ALTER TABLE

This says that it is deferrable, but not deferred (except if you decide to set the constraint deferred for your transaction). That way it accepts temporary constraint violation if they are resolved at the end of the statement.

Now, my update statement is sucessful:

begin transaction;
BEGIN
update demo set n=n-1;
UPDATE 2
select * from demo;
n
---
0
1
(2 rows)
update demo set n=n+1;
UPDATE 2

Any other statement that violates the constraint fails immediately:

insert into demo values(1);
ERROR: duplicate key value violates unique constraint "demo_pk"
DETAIL: Key (n)=(1) already exists.
commit;
ROLLBACK

Documentation

The nice thing is that this is documented! I didn’t find it immediately because it is in the ‘Compatibility’ part of the ‘create table’ documentation. I’m not yet used to the Postgres documentation. I stopped at the ‘DEFERRED’ definition which mentions: A constraint that is not deferrable will be checked immediately after every command

But later Compatibility adds something more specific to the unique constraint:

Non-deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

That’s another good point. Postgres documentation is clear and gives the right solution. We just have to read it to the end.

A side note for my French speaking readers here to mention that the Postgres documentation has been translated into French by Guillaume Lelarge, who also translated Markus Winand book and website. Translation is as good as the original in both cases.

Performance

The documentation mentions ‘significantly slower’. Here is a test on 100000 rows with non deferable constraint:

create table demo as select generate_series n from generate_series(1,100000);
SELECT 100000
alter table demo add constraint demo_pk primary key(n);
ALTER TABLE
vacuum demo;
VACUUM
select * from pgstatindex('demo_pk');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 1 | 2260992 | 3 | 1 | 274 | 0 | 0 | 89.83 | 0
(1 row)

Here is the update n=n-1 where all rows are updated but none violates the constraint at any time:

explain (analyze,verbose,costs,buffers)update demo set n=n-1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update on public.demo (cost=0.00..1693.00 rows=100000 width=10) (actual time=425.699..425.699 rows=0 loops=1)
Buffers: shared hit=578646 read=1202 dirtied=1267
-> Seq Scan on public.demo (cost=0.00..1693.00 rows=100000 width=10) (actual time=0.013..16.186 rows=100000 loops=1)
Output: (n - 1), ctid
Buffers: shared hit=443

This update has read 578646+1202=579848 buffers.

Now creating the deferrable constraint:

alter table demo drop constraint demo_pk;
ALTER TABLE
alter table demo add constraint demo_pk primary key(n) deferrable initially immediate;
ALTER TABLE
vacuum demo;
VACUUM
select * from pgstatindex('demo_pk');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 1 | 2260992 | 3 | 1 | 274 | 0 | 0 | 89.83 | 0

And do the n=n+1 update:

explain (analyze,verbose,costs,buffers)update demo set n=n+1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update on public.demo (cost=0.00..2135.00 rows=100000 width=10) (actual time=481.868..481.868 rows=0 loops=1)
Buffers: shared hit=679405 read=760 dirtied=825
-> Seq Scan on public.demo (cost=0.00..2135.00 rows=100000 width=10) (actual time=0.268..16.329 rows=100000 loops=1)
Output: (n + 1), ctid
Buffers: shared hit=885
Planning time: 0.237 ms
Trigger PK_ConstraintTrigger_75314 for constraint demo_pk: time=174.976 calls=99999
Execution time: 663.799 ms

This read more buffers and we can see that an internal trigger (PK_ConstraintTrigger_75314) has been run to re-check the unique constraint at the end of the statement. But only 17% more here for this special case where all rows are updated.

However, a more realistic test case exchanging only two values is much cheaper:


explain (analyze,verbose,costs,buffers) update demo set n=case when n=2 then 2000 when n=2000 then 2 end where n in (2,2000);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Update on public.demo (cost=8.85..16.60 rows=2 width=10) (actual time=0.079..0.079 rows=0 loops=1)
Buffers: shared hit=23
-> Bitmap Heap Scan on public.demo (cost=8.85..16.60 rows=2 width=10) (actual time=0.016..0.055 rows=2 loops=1)
Output: CASE WHEN (n = 2) THEN 2000 WHEN (n = 2000) THEN 2 ELSE NULL::integer END, ctid
Recheck Cond: (demo.n = ANY ('{2,2000}'::integer[]))
Heap Blocks: exact=3
Buffers: shared hit=9
-> Bitmap Index Scan on demo_pk (cost=0.00..8.85 rows=2 width=0) (actual time=0.009..0.009 rows=4 loops=1)
Index Cond: (demo.n = ANY ('{2,2000}'::integer[]))
Buffers: shared hit=6
Planning time: 0.137 ms
Trigger PK_ConstraintTrigger_75322 for constraint demo_pk: time=0.005 calls=1
Execution time: 0.120 ms

In my opinion, the overhead here is totally acceptable, especially given the fact that this re-check displays exactly which value violates the constraint in case there is a duplicate.

But I’m going too fast here. I’ve not even started my blog series about access paths where I’ll explain the cost of the execution plans, starting from the most simple: Seq Scan. Follow my blog or twitter to get informed. There will be nothing about ‘which is better, Oracle or Postgres?’. But I’m convinced that knowing the difference helps to understand how it works, and to design an application that has the correct behavior if ported from one to the other.

 

Cet article Postgres unique constraint est apparu en premier sur Blog dbi services.

Postgres vs. Oracle access paths – intro

Tue, 2017-08-01 00:00

This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading Oracle execution plans and no experience at all on Postgres. This is my way to learn and share what I learn. You will probably be interested if you are in the same situation: an Oracle DBA wanting to learn about Postgres. But you may also be an experienced Postgres DBA who wants to see a different point of view from a different ‘culture’.

I’ll probably use the Oracle terms more often as I’m more familiar with them: blocks for pages, optimizer for query planner, rows for tuples, tables for relations…

Please, don’t hesitate to comment on the blog posts or through twitter (@FranckPachot) if you find some mistakes in my Postgres interpretation. I tend to verify any assumption in the same way I do it with Oracle: the documented behavior and the test result should match. My test should be fully reproducible (using Postgres 9.6.2 here with all defaults). But as I said above, I’ve not the same experience as I have on Oracle when interpreting execution statistics.

Postgres

I’m using the latest versions here. Postgres 9.2.6 (as the one I installed here)
I’ve installed pg_hint_plan to be able to control the execution plan with hints. This is mandatory when doing some research. In order to understand an optimizer (query planner) choice, we need to see the estimated cost for different possibilities. Most of my tests will be done with: EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS)

fpa=# explain (analyze,verbose,costs,buffers) select 1;
 
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
Output: 1
Planning time: 0.060 ms
Execution time: 0.036 ms
(4 rows)

I my go further with unix tools (like strace to see the system calls)

Oracle

I’m using Oracle 12.2 here and the tests are done by running the statement after setting ALTER SESSION SET STATISTICS_LEVEL=ALL and displaying the execution plan with DBMS_XPLAN:
select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');
Note that if you are in lower Oracle versions, you need to call dbms_xplan through the table() function:
select * from table(dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection'));
Example:

SQL> set arraysize 5000 linesize 150 trimspool on pagesize 1000 feedback off termout off
SQL> alter session set statistics_level=all;
SQL> select 1 from dual;
SQL> set termout on
SQL> select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 |
| 1 | FAST DUAL | | 1 | 1 | 2 (0)| 1 |00:00:00.01 |
--------------------------------------------------------------------------------------

I’ll probably never compare the execution time, as this depends on the system and makes no sense on artificial small examples. But I’ll try to compare all other statistics: estimated cost, the actual number of pages/blocks read, etc.

Table of content

I’ll update (or rather insert /*+ append */) the links to the series posts as soon as they are published.

  1. Postgres vs. Oracle access paths I – Seq Scan
  2. Postgres vs. Oracle access paths II – Index Only Scan
 

Cet article Postgres vs. Oracle access paths – intro est apparu en premier sur Blog dbi services.

PostgreSQL on Cygwin

Mon, 2017-07-31 23:00

I run my laptop with Windows 10 for office programs, and VirtualBox machines with Linux for the big stuff (Oracle databases). I have also Cygwin installed on Windows for GNU programs. I wanted to quickly install PosgreSQL and rather than installing it in a Linux VM, or as a Windows program, I installed the Cygwin version of it. Here is how.

Cygwin

Cygwin is easy to install: just run the setup-x86_64.exe from https://www.cygwin.com/ and choose the packages you want to install. Here is what is related to PostgreSQL:
CapturePGCY0001

Note that if you want to install postgres extensions you may need pg_config and you need to install the libpd-devel in addition to postgresql-devel. And gcc and make. Those are not displayed in the screenshot above but you may get something like the following, if you don’t have them, when installing an extension:
pg_config: Command not found

Of course, PostgreSQL is Open Source and you can also compile it yourself.

Cygserver

Cygwin can run daemons through a Windows service (Cygserver) and you need to set it up if not already done. For this step, you will need to run the Cygwin Terminal as Administrator.
fpa@dell-fpa ~
$ /usr/bin/cygserver-config
Overwrite existing /etc/cygserver.conf file? (yes/no) yes
Generating /etc/cygserver.conf file
 
Warning: The following function requires administrator privileges!
 
Do you want to install cygserver as service?
(Say "no" if it's already installed as service) (yes/no) yes
 
The service has been installed under LocalSystem account.
To start it, call `net start cygserver' or `cygrunsrv -S cygserver'.
 
Further configuration options are available by editing the configuration
file /etc/cygserver.conf. Please read the inline information in that
file carefully. The best option for the start is to just leave it alone.
 
Basic Cygserver configuration finished. Have fun!

You start this service as any Windows service:

fpa@dell-fpa ~
$ net start cygserver
The CYGWIN cygserver service is starting.
The CYGWIN cygserver service was started successfully.

You can check from that the service is running:

fpa@dell-fpa ~
$ cygstart services.msc

CapturePGCY0002

PostgreSQL database cluster

Here is the creation of the PostgreSQL database cluster.
fpa@dell-fpa ~
$ /usr/sbin/initdb -D /usr/share/postgresql/data
The files belonging to this database system will be owned by user "fpa".
This user must also own the server process.
 
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
 
Data page checksums are disabled.
 
creating directory /usr/share/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 30
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
 
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
 
Success. You can now start the database server using:
 
/usr/sbin/pg_ctl -D /usr/share/postgresql/data -l log.txt start

Start PostgreSQL database server

I add my network onto the /usr/share/postgresql/data/postgresql.conf

# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.78.0/24 trust

I define the interface and port where the server listen in /usr/share/postgresql/data/postgresql.conf

listen_addresses = 'localhost,192.168.78.1' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 30 # (change requires restart)

Now ready to start the PostgreSQL server:
fpa@dell-fpa ~
$ /usr/sbin/pg_ctl -D /usr/share/postgresql/data -l log.txt start
server starting

Username

My Windows username is ‘FPA’ and so is the Cygwin user which started the database server and I check that I can connect to the maintenance database with this user:

fpa@dell-fpa ~
$ psql -U fpa postgres
psql (9.6.2)
Type "help" for help.
 
postgres=# \du
 
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
fpa | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
postgres=# quit

PgAdmin

As I am on Windows, I install the graphical console PgAdmin and setup the connection to this database:
CapturePGCY0003

SQL Developer

As an Oracle fan, I prefer to connect with SQL Developer. Just download the JDBC driver for PostgreSQL: https://jdbc.postgresql.org/download.html

In SQL Developer you can declare this .jar from Tools -> Preferences -> Third Party JDBC Drivers

CapturePGCY0004

And create the connection with the new ‘PostgreSQL’ tab:

CapturePGCY0005
Then with ‘Choose Database’ you can fill the dropbox and choose the database you want to connect to.

As I have no database with the same name as the username, I have to mention the database name at the end of the hostname, suffixed with ‘?’ to get the proper JDBC url. And what you put in the dropbox will be ignored. I don’t really know the reason, but this is how I got the correct url.

CapturePGCY0006

Extensions

You can install extensions. For example, I’ve installed pg_hint_plan to be able to hint the access path and join methods.

wget https://osdn.net/dl/pghintplan/pg_hint_plan96-1.2.1.tar.gz
tar -zxvf pg_hint_plan96-1.2.1.tar.gz
cd pg_hint_plan96-1.2.1
make
make install

And I’m now able to load it:

$ psql
psql (9.6.2)
Type "help" for help.
 
fpa=# load 'pg_hint_plan';
LOAD

But Why?

You may wonder why I don’t install it directly on Linux. My laptop is on Windows and, of course, I have a lot of VirtualBox VMs. But this doesn’t require to start a VM.
You may wonder why I don’t install the Windows version? I want to investigate the linux behaviour. And I may want to trace the postgres processes. For example, cygwin has a strace.exe which shows similar output as strace on Linux. Here is the I/O calls from a full table scan (Seq Scan):
CaptureStraceCygwinPostgres
I can see that postgres sequential reads are done through one lseek() and sequential 8k read().

This was simple. Just get the pid of the session process:

fpa=# select pg_backend_pid();
pg_backend_pid
----------------
11960

and strace it:

$ strace -p 11960

I’ve done that in about one hour: download, install, setup and write this blog post. Without any virtual machine, you can have a Linux Postgres database server running on Windows.

 

Cet article PostgreSQL on Cygwin est apparu en premier sur Blog dbi services.

Re-assigning all objects from on role to another in PostgreSQL

Mon, 2017-07-31 04:07

From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go.

Obviously we need two users when we want to move objects from one user to another (actually it is not really moving the objects but more about changing the ownership):

postgres=# create role a login password 'a';
CREATE ROLE
postgres=# create role b login password 'b';
CREATE ROLE
postgres=# create schema a authorization a;
CREATE SCHEMA
postgres=# create schema b authorization b;
CREATE SCHEMA
postgres=# alter role a set search_path=a;
ALTER ROLE
postgres=# alter role b set search_path=b;
ALTER ROLE
postgres=# 

Lets create some objects in schema “a” owned by user “a”:

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> \! cat a.sql
create table a ( a int );
create table b ( a int );
create table c ( a int );
create table d ( a int );
create index i1 on a (a);
create index i2 on b (a);
create index i3 on c (a);
create index i4 on d (a);

postgres=> \i a.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX

By joining pg_class and pg_roles we can verify who is actually the owner of the objects:

postgres=> select t.relname, d.rolname 
             from pg_class t, pg_roles d 
            where t.relowner = d.oid and d.rolname = 'a';
 relname | rolname 
---------+---------
 a       | a
 b       | a
 c       | a
 d       | a
 i1      | a
 i2      | a
 i3      | a
 i4      | a
(8 rows)

The easiest way to make these objects owned by another user (call it “c”) would be:

postgres=# alter user a rename to c;
NOTICE:  MD5 password cleared because of role rename
ALTER ROLE
postgres=# select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'c';
 relname | rolname 
---------+---------
 a       | c
 b       | c
 c       | c
 d       | c
 i1      | c
 i2      | c
 i3      | c
 i4      | c
(8 rows)

Not a good idea though as the schema still is named “a” and this at least will create some confusion with the naming. Of course we could rename the schema as well:

postgres=# alter schema a rename to c;
ALTER SCHEMA
postgres=# \c postgres c
You are now connected to database "postgres" as user "c".
postgres=> select count(*) from a;
2017-07-28 15:51:25.499 CEST [3415] ERROR:  relation "a" does not exist at character 22
2017-07-28 15:51:25.499 CEST [3415] STATEMENT:  select count(*) from a;
ERROR:  relation "a" does not exist
LINE 1: select count(*) from a;

… but now we have another mess. Because the search_path is still set to “a” we can not see the objects by default but we will need to use the fully qualified name:

postgres=> select count(*) from c.a;
 count 
-------
     0
(1 row)

Finally we would need to adjust the search_path to get back the previous behavior:

postgres=> set search_path=c;
SET
postgres=> select count(*) from a;
 count 
-------
     0
(1 row)

A lot of steps to follow. Easier is:

postgres=# reassign owned by c to b;
REASSIGN OWNED
postgres=# alter user b set search_path=c,b;
ALTER ROLE
postgres=# \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a;
 count 
-------
     0
(1 row)
postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';
 relname | rolname 
---------+---------
 d       | b
 i4      | b
 c       | b
 i3      | b
 b       | b
 i2      | b
 a       | b
 i1      | b
(8 rows)

Cool :) There is also a command to drop all objects of a user:

postgres=> drop owned by b;
DROP OWNED
postgres=> select t.relname, d.rolname from pg_class t, pg_roles d where t.relowner = d.oid and d.rolname = 'b';
 relname | rolname 
---------+---------
(0 rows)

Nice …

 

Cet article Re-assigning all objects from on role to another in PostgreSQL est apparu en premier sur Blog dbi services.

Documentum – Change password – 8 – FT – Dsearch & xDB

Sat, 2017-07-29 06:19

Here we are, already, at the end of my series of blogs about the “Change Password”. This blog will, as already mentioned in a previous one, talk about the Dsearch and xDB passwords. I could have created a lot more blogs in this series but I already presented the most important and most interesting ones so this blog will be the last one – at least for now ;).

 

Please note that below, I will set the same password for the Dsearch admin account and for the xDB. The reason for that is pretty simple: this is what Documentum is doing by default when you install a PrimaryDsearch. Indeed, when you install a Dsearch, the installer will ask you to enter the Dsearch admin account which is the JBoss instance admin account (this blog explained how to change a JBoss Admin password) and it will use this password for the xDB too.

If you want to use a different password, then you can potentially define three passwords here: xDB Admin password, xDB Superuser password and Dsearch JBoss instance password.

 

The xDB (find more info here about what it is) provides a command line interface to manage it (repair, create, list, aso…) which is the “XHCommand” but you aren’t able to change the different passwords through this CLI ;(. Therefore, before even starting with this blog, you will have to start a X Server or something similar, to be able to open the xDB Admin GUI (“XHAdmin”). For this blog, I installed a new VM and I also installed the complete graphical interface on it (my first time in a very long time) in order to have some screenshots that aren’t too ugly…

 

So first of all, let’s login to the Full Text Server and open the xDB Admin GUI:

[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/dsearch/xhive/admin/
[xplore@full_text_server_01 ~]$ ./XHAdmin

 

Just a small funny note, when you will close the XHAdmin tool, you will see that EMC thanks you for using this tool: “Thank you for using EMC Documentum xDB”. I guess this is your reward for having to open a GUI to change a password, in 2017 ;).

 

At this point, the xDB Admin Client will be opened but not connected. So the first thing to do is to connect to the xDB with the CURRENT Dsearch Admin credentials:

  • Click on the “connect” button
  • Set the “Database name” to: xhivedb
  • Set the “Username” to: Administrator
  • Set the “Password” to: ###CURRENT_DSEARCH_ADMIN_PWD###
  • Click on the “OK” button

Dsearch-xDB_Change_Password_1

 

Then you will be connected to the xDB. Therefore, it is now time to change the Superuser password:

  • Click on the “Federation > Change superuser password” menu
  • Set the “New superuser password” to: ###NEW_DSEARCH_ADMIN_PWD###
  • Set the “Retype password” to: ###NEW_DSEARCH_ADMIN_PWD###
  • Click on the “OK” button
  • A pop-up will be opened with a title “Enter superuser password”. Enter the ###CURRENT_DSEARCH_ADMIN_PWD### in it and click on OK to validate the change.

Dsearch-xDB_Change_Password_2

 

After that, time to change the Admin password itself. Here, you have the use the new Superuser password that has been defined above and then define the new Admin password:

  • Click on the “Database > Reset admin password” menu
  • Set the “Database name” to: xhivedb
  • Set the “Superuser password” to: ###NEW_DSEARCH_ADMIN_PWD###
  • Set the “Administrator password” to: ###NEW_DSEARCH_ADMIN_PWD###
  • Set the “Retype password” to: ###NEW_DSEARCH_ADMIN_PWD###
  • Click on the “OK” button

Dsearch-xDB_Change_Password_3

 

At this point, the xDB Superuser and Admin passwords have been defined so you can close the xDB Admin Client (Database > Exit) and we can go back to the command line. Ok so now let’s stop the xPlore components in order to reflect these changes on the configuration files:

[xplore@full_text_server_01 ~]$ read -s -p "Please enter the NEW Dsearch Admin password: " newpw; echo
Please enter the NEW Dsearch Admin password:
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server
[xplore@full_text_server_01 ~]$ for i in `ls stop*.sh`; do ./${i}; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server/DctmServer_PrimaryDsearch/deployments/dsearch.war/WEB-INF/classes/
[xplore@full_text_server_01 ~]$ cp indexserver-bootstrap.properties indexserver-bootstrap.properties_bck_$(date +"%Y%m%d-%H%M%S")
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ grep "password=" indexserver-bootstrap.properties
superuser-password=AAAAEF1hfFDU0zff6A6Mmd1f6Le8VvxPmLxF11ZdzjVo5KU/
adminuser-password=AAAAEF1hfFDU0zff6A6Mmd1f6Le8VvxPmLxF11ZdzjVo5KU/
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sed -i "s,password=.*,password=${newpw}," indexserver-bootstrap.properties
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ grep "password=" indexserver-bootstrap.properties
superuser-password=New_Dsearch_Admin_Pwd
adminuser-password=New_Dsearch_Admin_Pwd
[xplore@full_text_server_01 ~]$

 

As you can see above, I’m defining the environment variable with the NEW Dsearch Admin Password and then I’m replacing the superuser and adminuser encrypted OLD password inside the file indexserver-bootstrap.properties with the non-encrypted NEW password. It was the same for both before and it is still the same for both after. Don’t worry about putting the non-encrypted NEW password in this properties file, it will be encrypted automatically at the next start of the PrimaryDsearch. So now, let’s start the Dsearch only to verify if the passwords have been encrypted successfully and then we can update the Dsearch JBoss Admin password:

[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server
[xplore@full_text_server_01 ~]$ nohup ./startPrimaryDsearch.sh >> nohup-PrimaryDsearch.out 2>&1 &
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sleep 60
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ grep "password=" ./DctmServer_PrimaryDsearch/deployments/dsearch.war/WEB-INF/classes/indexserver-bootstrap.properties
superuser-password=AAAAFAq2fFwiHf21P98hRch982+1hEDGe824E21eL21e9c57f6A
adminuser-password=AAAAFAq2fFwiHf21P98hRch982+1hEDGe824E21eL21e9c57f6A
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration
[xplore@full_text_server_01 ~]$ cp dctm-users.properties dctm-users.properties_bck_$(date +"%Y%m%d-%H%M%S")
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ encrypted_newpw=`grep "adminuser-password=" ../deployments/dsearch.war/WEB-INF/classes/indexserver-bootstrap.properties | sed 's,adminuser-password=,,'`
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ echo "# users.properties file to use with UsersRolesLoginModule" > dctm-users.properties
[xplore@full_text_server_01 ~]$ echo "admin=${encrypted_newpw}" >> dctm-users.properties
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ cat dctm-users.properties
# users.properties file to use with UsersRolesLoginModule
admin=AAAAFAq2fFwiHf21P98hRch982+1hEDGe824E21eL21e9c57f6A
[xplore@full_text_server_01 ~]$

 

If you are referring to the JBoss Admin password blog I already published and that I linked above, you could think that there is nothing more to be done but actually there is one more file that needs to be updated when it comes to the Dsearch Admin password and this file is the xplore.properties:

[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/dsearch/admin/
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ grep -B2 "password=" xplore.properties
# Specify admin password for xPlore primary instance
# It is highly suggested to input the encrypted password, you can get it from indexserver-bootstrap.properties
password=AAAAEF1hfFDU0zff6A6Mmd1f6Le8VvxPmLxF11ZdzjVo5KU/
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sed -i "s,password=.*,password=${encrypted_newpw}," xplore.properties
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ grep -B2 "password=" xplore.properties
# Specify admin password for xPlore primary instance
# It is highly suggested to input the encrypted password, you can get it from indexserver-bootstrap.properties
password=AAAAFAq2fFwiHf21P98hRch982+1hEDGe824E21eL21e9c57f6A
[xplore@full_text_server_01 ~]$

 

To complete the changes, we can now restart all xPlore components and verify that the PrimaryDsearch has been started properly:

[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server
[xplore@full_text_server_01 ~]$ ./stopPrimaryDsearch.sh
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sleep 30
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ nohup ./startPrimaryDsearch.sh >> nohup-PrimaryDsearch.out 2>&1 &
[xplore@full_text_server_01 ~]$ for i in `ls startIndexag*.sh`; do ia=`echo $i|sed 's,start\(.*\).sh,\1,'`; nohup ./$i >> nohup-$ia.out 2>&1 &; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sleep 30
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ curl -g --user admin -D - http://localhost:9305/management --header "Content-Type: application/json" -d '{"operation":"read-attribute","name":"server-state","json.pretty":1}'
Enter host password for user 'admin':
HTTP/1.1 200 OK
Transfer-encoding: chunked
Content-type: application/json
Date: Sun, 23 Jul 2017 08:14:56 GMT

{
    "outcome" : "success",
    "result" : "running"
}
[xplore@full_text_server_01 ~]$

 

To be sure, you can also open the Dsearch Admin UI (E.g.: https://hostname:9302/dsearchadmin) and then log in with the NEW Dsearch Admin password. If the login is working, you should be good to go! :)

 

 

Cet article Documentum – Change password – 8 – FT – Dsearch & xDB est apparu en premier sur Blog dbi services.

Documentum – Change password – 7 – DB – Schema Owner

Sat, 2017-07-29 04:48

In this serie, I completed the passwords I wanted to talk about on the Content Server. Therefore in this blog, I will talk about the only Database Account that is relevant for Documentum: the Database Schema Owner. Since there are a few steps to be done on the Content Server, I’m just doing everything from there… In this blog, I will assume there is one Global Registry (GR_DOCBASE) and one normal Repository (DocBase1). Each docbase has a different Database Schema Owner of course but both Schemas are on the same Database and therefore the same SID will be used.

 

In High Availability setups, you will have to execute the steps below for all Content Servers. Of course, when it comes to changing the password inside the DB, this needs to be done only once since the Database Schema Owner is shared between the different Content Servers of the HA setup.

 

In this blog, I’m using a CS 7.2. Please note that in CS 7.2, there is a property inside the dfc.properties of the Content Server ($DOCUMENTUM_SHARED/config/dfc.properties) that defines the crypto repository (dfc.crypto.repository). The repository that is used for this property is the one that Documentum will use for encryption/decryption of passwords and therefore I will use this one below to encrypt the password. By default, the Repository used for this property is the last one created… I tend to use the Global Registry instead, but it’s really up to you.

 

As said before, I’m considering two different repositories and therefore two different accounts and two different passwords. So, let’s start with encrypting these two passwords:

[dmadmin@content_server_01 ~]$ read -s -p "Please enter the NEW GR_DOCBASE Schema Owner's password: " new_gr_pw; echo
Please enter the NEW GR_DOCBASE Schema Owner's password:
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ read -s -p "Please enter the NEW DocBase1 Schema Owner's password: " new_doc1_pw; echo
Please enter the NEW DocBase1 Schema Owner's password:
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ iapi `cat $DOCUMENTUM_SHARED/config/dfc.properties | grep crypto | tail -1 | sed 's/.*=//'` -Udmadmin -Pxxx << EOF
> encrypttext,c,${new_gr_pw}
> encrypttext,c,${new_doc1_pw}
> EOF


    EMC Documentum iapi - Interactive API interface
    (c) Copyright EMC Corp., 1992 - 2015
    All rights reserved.
    Client Library Release 7.2.0150.0154


Connecting to Server using docbase GR_DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 010f12345605ae7b started for user dmadmin."


Connected to Documentum Server running Release 7.2.0160.0297  Linux64.Oracle
Session id is s0
API> ...
DM_ENCR_TEXT_V2=AAAAEH7UNwFub2ubf92h+21/rc8HEc3rd1C82hc52c8bz2cFl1cQ721zex2nxWDEegwqgdotwncZVVqgZlDLmfflWK6+f8AGf0dSRzi5rr3h3::GR_DOCBASE
API> ...
DM_ENCR_TEXT_V2=AAAAEGBQ6Zy7FxQ10idQdFj+Gn20nFlif02ieMx+AGBHLz+vQfmGu2GAiv8KeIN2PhPOf1oiF9u2fP98zEFhhuBAmxY+d5AoBCGNf61ZRavpa::GR_DOCBASE
API> Bye
[dmadmin@content_server_01 ~]$

 

If you have more repositories, you will have to encrypt those too, if you want to change them of course. Once the new password has been encrypted, we can change it on the Database. To avoid any issues and error messages, let’s first stop Documentum (the docbases at the very least) and then printing the Database Connection information:

[dmadmin@content_server_01 ~]$ service documentum stop
  ** JMS stopped
  ** DocBase1 stopped
  ** GR_DOCBASE stopped
  ** Docbroker stopped
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
<sid> =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = <database_hostname>)(PORT = <database_port>))
        )
        (CONNECT_DATA =
            (SERVICE_NAME = <service_name>)
        )
    )
[dmadmin@content_server_01 ~]$

 

Once you know what the SID is, you can now login to the database to change the password so I will do that for both repositories. This could also be scripted to retrieve the list of docbases, create new passwords for them, encrypt them all automatically and then connect to each database using different SQL scripts to change the passwords, however I will use here manual steps:

[dmadmin@content_server_01 ~]$ sqlplus GR_DOCBASE@<sid>

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 22 15:05:08 2017

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

Enter password:
    -->> Enter here the OLD GR_DOCBASE Schema Owner's password
Last Successful login time: Sat Jul 22 2017 15:04:18 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> PASSWORD
Changing password for GR_DOCBASE
Old password:
    -->> Enter here the OLD GR_DOCBASE Schema Owner's password
New password:
    -->> Enter here the NEW GR_DOCBASE Schema Owner's password
Retype new password:
    -->> Re-enter here the NEW GR_DOCBASE Schema Owner's password
Password changed

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ sqlplus DocBase1@<sid>

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 22 15:08:20 2017

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

Enter password:
    -->> Enter here the OLD DocBase1 Schema Owner's password
Last Successful login time: Sat Jul 22 2017 15:07:10 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> PASSWORD
Changing password for DocBase1
Old password:
    -->> Enter here the OLD DocBase1 Schema Owner's password
New password:
    -->> Enter here the NEW DocBase1 Schema Owner's password
Retype new password:
    -->> Re-enter here the NEW DocBase1 Schema Owner's password
Password changed

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[dmadmin@content_server_01 ~]$

 

At this point, the passwords have been changed in the database and we encrypted them properly. The next step is therefore to update the password files for each repository with the encrypted password so that the repositories can start again:

[dmadmin@content_server_01 ~]$ cd $DOCUMENTUM/dba/config
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ for i in `ls -d *`; do echo "  ** dbpasswd.txt for ${i} **"; cat ./${i}/dbpasswd.txt; echo; done
  ** dbpasswd.txt for GR_DOCBASE **
DM_ENCR_TEXT_V2=AAAAEH7UNwFgncubfd1C82hc5l1cwqgdotwQ7212c8bz2cFZVVqgZub2zex8bz2cFWK92h+21EelDLmffl2/rc82c8bz2cFf0dSRazi5rr3h3::GR_DOCBASE

  ** dbpasswd.txt for DocBase1 **
DM_ENCR_TEXT_V2=AAAAQ10idQdFj+Gn2EGBPZy7e0niF9uQfAGBHLz+vv8KQ62fP98zE+02iFhhuBAmxY+FFxeMxIN2Phl1od5AoBCGNf61ZRifmGu2GAiOfavpa::GR_DOCBASE

[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ for i in `ls -d *`; do cp ./${i}/dbpasswd.txt ./${i}/dbpasswd.txt_bck_$(date +"%Y%m%d-%H%M%S"); done
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ echo "DM_ENCR_TEXT_V2=AAAAEH7UNwFub2ubf92h+21/rc8HEc3rd1C82hc52c8bz2cFl1cQ721zex2nxWDEegwqgdotwncZVVqgZlDLmfflWK6+f8AGf0dSRzi5rr3h3::GR_DOCBASE" > ./GR_DOCBASE/dbpasswd.txt
[dmadmin@content_server_01 ~]$ echo "DM_ENCR_TEXT_V2=AAAAEGBQ6Zy7FxQ10idQdFj+Gn20nFlif02ieMx+AGBHLz+vQfmGu2GAiv8KeIN2PhPOf1oiF9u2fP98zEFhhuBAmxY+d5AoBCGNf61ZRavpa::GR_DOCBASE" > ./DocBase1/dbpasswd.txt
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ for i in `ls -d *`; do echo "  ** dbpasswd.txt for ${i} **"; cat ./${i}/dbpasswd.txt; echo; done
  ** dbpasswd.txt for GR_DOCBASE **
DM_ENCR_TEXT_V2=AAAAEH7UNwFub2ubf92h+21/rc8HEc3rd1C82hc52c8bz2cFl1cQ721zex2nxWDEegwqgdotwncZVVqgZlDLmfflWK6+f8AGf0dSRzi5rr3h3::GR_DOCBASE

  ** dbpasswd.txt for DocBase1 **
DM_ENCR_TEXT_V2=AAAAEGBQ6Zy7FxQ10idQdFj+Gn20nFlif02ieMx+AGBHLz+vQfmGu2GAiv8KeIN2PhPOf1oiF9u2fP98zEFhhuBAmxY+d5AoBCGNf61ZRavpa::GR_DOCBASE

[dmadmin@content_server_01 ~]$

 

Once the dbpasswd.txt files have been updated with the new encrypted password that has been generated at the beginning of this blog, then we can restart Documentum and verify that the docbases are up&running. If they are, then the password has been changed properly!

[dmadmin@content_server_01 ~]$ service documentum start
  ** Docbroker started
  ** GR_DOCBASE started
  ** DocBase1 started
  ** JMS started
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ ps -ef | grep "documentum.*docbase_name"
...
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ grep -C3 "DM_DOCBROKER_I_PROJECTING" $DOCUMENTUM/dba/log/GR_DOCBASE.log
2017-07-22T15:28:40.657360      9690[9690]      0000000000000000        [DM_SERVER_I_START]info:  "Sending Initial Docbroker check-point "

2017-07-22T15:28:40.671878      9690[9690]      0000000000000000        [DM_MQ_I_DAEMON_START]info:  "Message queue daemon (pid : 9870, session 010f123456000456) is started sucessfully."
2017-07-22T15:28:40.913699      9869[9869]      010f123456000003        [DM_DOCBROKER_I_PROJECTING]info:  "Sending information to Docbroker located on host (content_server_01) with port (1490).  Information: (Config(GR_DOCBASE), Proximity(1), Status(Open), Dormancy Status(Active))."
Tue Jul 22 15:29:38 2017 [INFORMATION] [AGENTEXEC 10309] Detected during program initialization: Version: 7.2.0160.0297  Linux64
Tue Jul 22 15:29:44 2017 [INFORMATION] [AGENTEXEC 10309] Detected during program initialization: Agent Exec connected to server GR_DOCBASE:  [DM_SESSION_I_SESSION_START]info:  "Session 010f123456056d00 started for user dmadmin."

[dmadmin@content_server_01 ~]$

 

When the docbase has been registered to the Docbroker, you are sure that it was able to contact and log in to the database so that the new password is now used properly. To be sure that everything in Documentum is working properly however, I would still check the complete log file…

 

 

Cet article Documentum – Change password – 7 – DB – Schema Owner est apparu en premier sur Blog dbi services.

Documentum – Change password – 6 – CS/FT – JKS

Sat, 2017-07-29 04:29

Just like for the JBoss Admin password (here), this blog will be for both Content Servers and Full Text Servers. I will provide commands below to change the passwords of the Java KeyStore for the Java Method Server (JMS) and Full Text Servers (Dsearch/IndexAgent). Again, JKS aren’t only used in the scope of Documentum so if you are here for the JKS and not for Documentum, that’s fine too ;).

 

The steps are exactly the same for all JKS files, it’s just a matter of integrating that inside Documentum. Therefore, I will continue to use the JMS for single JKS update and I will use the Dsearch/IndexAgent for multiple updates. The steps are pretty simple:

  1. Store the current and new password in variables
  2. Backup the old configuration and JKS files
  3. Update the JKS password
  4. Restart the components
  5. Verify that the components are running over https

 

I. JMS Java KeyStore

For the JMS Java KeyStore, you obviously need to connect to all Content Servers and then perform the steps. Below, I’m using a JKS named “my.keystore” which is placed right next to the standalone.xml file. So let’s do that:

[dmadmin@content_server_01 ~]$ read -s -p "Please enter the CURRENT JKS password: " currentpw; echo
Please enter the CURRENT JKS password:
[dmadmin@content_server_01 ~]$ read -s -p "Please enter the NEW JKS password: " newpw; echo
Please enter the NEW JKS password:
[dmadmin@content_server_01 ~]$ read -p "Please enter the name of the JKS file with extension: " jks_name
Please enter the name of the JKS file with extension: my.keystore
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ cd $DOCUMENTUM_SHARED/jboss7.1.1/server/DctmServer_MethodServer/configuration/
[dmadmin@content_server_01 ~]$ cp ${jks_name} ${jks_name}_bck_$(date +"%Y%m%d-%H%M%S")
[dmadmin@content_server_01 ~]$ cp standalone.xml standalone.xml_bck_$(date +"%Y%m%d-%H%M%S")
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ keytool -storepasswd -keystore ${jks_name} -storepass ${currentpw} -new ${newpw}
[dmadmin@content_server_01 ~]$ keytool -keypasswd -keystore ${jks_name} -storepass ${newpw} -alias jms_alias -keypass ${currentpw} -new ${newpw}
[dmadmin@content_server_01 ~]$

 

These last two commands are the ones updating the Java KeyStore and the key passwords. In case your JKS and its included key do not have the same password, you will have to use the real passwords at the end of the second line. If the last command (the 2nd keytool command) is working, it means that you changed the JKS password properly in the first keytool command because you are now able to change the key password using the new JKS password (-storepass ${newpw}). Still following me?

Once this has been done, the next step is simply to update the password in the JMS configuration file and restart the JMS:

[dmadmin@content_server_01 ~]$ grep "password=" standalone.xml
                <ssl name="https" password="Old_JKS_Password" certificate-key-file="$DOCUMENTUM_SHARED/jboss7.1.1/server/DctmServer_MethodServer/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ sed -i 's,\(<[sk][se][ly].*password="\)[^"]*,\1'${newpw}',' standalone.xml
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ grep "password=" standalone.xml
                <ssl name="https" password="New_JKS_Password" certificate-key-file="$DOCUMENTUM_SHARED/jboss7.1.1/server/DctmServer_MethodServer/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ cd $DOCUMENTUM_SHARED/jboss7.1.1/server
[dmadmin@content_server_01 ~]$ ./stopMethodServer.sh
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ nohup ./startMethodServer.sh >> nohup-JMS.out 2>&1 &
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ sleep 30
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ curl -k -D - https://localhost:9082/DmMethods/servlet/DoMethod; echo
HTTP/1.1 200 OK
Content-Length: 144
Date: Sat, 22 Jul 2017 09:58:41 GMT
Server: MethodServer

<html><title>Documentum Java Method Server</title><body><font face="Verdana, Arial" size="-1"><p>Documentum Java Method Server</p></body></html>
[dmadmin@content_server_01 ~]$

 

If the password of the JKS has been changed properly for the JKS file as well as in the configuration file, then you should get an HTTP 200 OK return code.

 

II. Dsearch/IndexAgent Java KeyStores

For the Dsearch/IndexAgent Java KeyStores, you obviously need to connect to all Full Text Servers and then perform the steps again. Below, I’m using a JKS named “my.keystore”. It doesn’t matter where this file is placed since the commands below will anyway just find them. However, by default this file will be placed right next to the standalone.xml file: this is the default setup if you used the “ConfigSSL.groovy” script to setup the xPlore in SSL (see this blog for information about that and a lot of other of my blogs to see issues related to this script/setup in SSL). These commands are adapted in case you have several IndexAgents installed. Please note that the commands below will set the same JKS password for all JBoss instances (all Dsearch/IndexAgents). Therefore, if that’s not what you want (if you have Subject Alternative Names for example), you will have to execute the commands for each keystore, one by one.

[xplore@full_text_server_01 ~]$ read -s -p "Please enter the CURRENT JKS password: " currentpw; echo
Please enter the CURRENT JKS password:
[xplore@full_text_server_01 ~]$ read -s -p "Please enter the NEW JKS password: " newpw; echo
Please enter the NEW JKS password:
[xplore@full_text_server_01 ~]$ read -p "Please enter the name of the JKS file with extension: " jks_name
Please enter the name of the JKS file with extension: my.keystore
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name ${jks_name}`; do cp ${i} ${i}_bck_$(date +"%Y%m%d-%H%M%S"); done
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name standalone.xml`; do if [[ ${i} != */template/* ]]; then cp ${i} ${i}_bck_$(date +"%Y%m%d-%H%M%S"); fi; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name ${jks_name}`; do keytool -storepasswd -keystore ${i} -storepass ${currentpw} -new ${newpw}; done
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name ${jks_name}`; do keytool -keypasswd -keystore ${i} -storepass ${newpw} -alias ft_alias -keypass ${currentpw} -new ${newpw}; done
[xplore@full_text_server_01 ~]$

 

At this point, all the Java KeyStore have been backed up and updated and the related standalone.xml files have been backed up too. The only remaining step is to replace the passwords in the standalone.xml files, restart the components and test again the availability of the xPlore components:

[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name standalone.xml`; do if [[ ${i} != */template/* ]]; then grep "password=" ${i}; fi; done
                <ssl name="https" password="Old_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="Old_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase1/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="Old_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase2/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="Old_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase3/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name standalone.xml`; do if [[ ${i} != */template/* ]]; then sed -i 's,\(<[sk][se][ly].*password="\)[^"]*,\1'${newpw}',' ${i}; fi; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ for i in `find $XPLORE_HOME -name standalone.xml`; do if [[ ${i} != */template/* ]]; then grep "password=" ${i}; fi; done
                <ssl name="https" password="New_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_PrimaryDsearch/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="New_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase1/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="New_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase2/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
                <ssl name="https" password="New_JKS_Password" certificate-key-file="$XPLORE_HOME/jboss7.1.1/server/DctmServer_Indexagent_DocBase3/configuration/my.keystore" cipher-suite="TLS_RSA_WITH_AES_128_CBC_SHA"/>
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ cd $XPLORE_HOME/jboss7.1.1/server
[xplore@full_text_server_01 ~]$ for i in `ls stop*.sh`; do ./${i}; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ nohup ./startPrimaryDsearch.sh >> nohup-PrimaryDsearch.out 2>&1 &
[xplore@full_text_server_01 ~]$ for i in `ls startIndexag*.sh`; do ia=`echo $i|sed 's,start\(.*\).sh,\1,'`; nohup ./$i >> nohup-$ia.out 2>&1 &; done
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ sleep 30
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ curl -k -D - https://localhost:9302/dsearch/; echo
HTTP/1.1 259
Server: Apache-Coyote/1.1
Pragma: No-cache
Cache-Control: no-cache
Expires: Thu, 01 Jan 1970 00:00:00 UTC
Content-Type: text/html;charset=UTF-8
Content-Length: 65
Date: Sat, 22 Jul 2017 11:33:38 GMT

The xPlore instance PrimaryDsearch [version=1.5.0020.0048] normal
[xplore@full_text_server_01 ~]$
[xplore@full_text_server_01 ~]$ for i in `ls -d DctmServer_Indexag*`; do port=`grep '<socket-binding .*name="https"' ./${i}/configuration/standalone.xml|sed 's,.*port="\([0-9]*\).*,\1,'`; echo; echo "  ** Accessing IndexAgent URL of '${i}' (${port})"; curl -k -D - https://localhost:${port}/IndexAgent; done

  ** Accessing IndexAgent URL of 'DctmServer_Indexagent_DocBase1' (9202)
HTTP/1.1 302 Moved Temporarily
Server: Apache-Coyote/1.1
Location: https://localhost:9202/IndexAgent/
Transfer-Encoding: chunked
Date: Sat, 22 Jul 2017 11:36:57 GMT


  ** Accessing IndexAgent URL of 'DctmServer_Indexagent_DocBase2' (9222)
HTTP/1.1 302 Moved Temporarily
Server: Apache-Coyote/1.1
Location: https://localhost:9222/IndexAgent/
Transfer-Encoding: chunked
Date: Sat, 22 Jul 2017 11:36:57 GMT


  ** Accessing IndexAgent URL of 'DctmServer_Indexagent_DocBase3' (9242)
HTTP/1.1 302 Moved Temporarily
Server: Apache-Coyote/1.1
Location: https://localhost:9242/IndexAgent/
Transfer-Encoding: chunked
Date: Sat, 22 Jul 2017 11:36:58 GMT
[dmadmin@content_server_01 ~]$

 

For the Dsearch, the proper answer is a HTTP 259 return code while for the IndexAgent, I didn’t put an ending “/” in the URL so that we don’t have the full page of the IA loaded but only some header. Therefore HTTP 302 Moved Temporarily is the proper return code there.

 

Please note that for the “sed” commands above, I deliberately used “<[sk][se][ly]” even if for all JBoss 7.1.1 instances, the SSL configuration will always start with “<ssl “. The reason for that is to make the steps compatible with WidlFly 9.0.1 too (xPlore 1.6). There are a few differences between JBoss 7.1.1 and WildFly 9.0.1 and one of them is that the JKS password is now on a line starting with “<keystore ” so that’s why :).

 

 

Cet article Documentum – Change password – 6 – CS/FT – JKS est apparu en premier sur Blog dbi services.

Setting up default parameters for roles in PostgreSQL

Fri, 2017-07-28 08:31

As you might know you can set various parameters on the session level in PostgreSQL by using the “set” command:

postgres=# \h set
Command:     SET
Description: change a run-time parameter
Syntax:
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

This allows a session to adjust parameters at runtime and can be a handy way for on the fly configuration when you need special settings. Wouldn’t it be great if we could have a default set of parameters for a role or user? Maybe there is one user who needs a special setting for work_mem and another one who needs a special setting for search_path. Instead of setting that each time after the connect in the session you can also do that on the server side.

Lets create to users, user a and user b:

postgres=# create user a login password 'a';
CREATE ROLE
postgres=# create user b login password 'b';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 a         |                                                            | {}
 b         |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

When we want a special setting for work_mem every time user a creates a new connection and a special setting for search_path every time user b creates a connection we can do it like this:

postgres=# alter user a set work_mem = '1MB';
ALTER ROLE
postgres=# alter user b set search_path='b';
ALTER ROLE
postgres=# 

When user a connects from now on:

postgres=# \c postgres a
You are now connected to database "postgres" as user "a".
postgres=> show work_mem;
 work_mem 
----------
 1MB
(1 row)

When user b connects from now on:

postgres=> \c postgres b
You are now connected to database "postgres" as user "b".
postgres=> show search_path ;
 search_path 
-------------
 b
(1 row)

Notice that this does not prevent a user from overriding the setting:

postgres=> select current_user;
 current_user 
--------------
 b
(1 row)

postgres=> set search_path=c;
SET
postgres=> show search_path ;
 search_path 
-------------
 c
(1 row)

… this is more meant as setting defaults that differ from the main server configuration where it makes sense. And how can you know then which settings are configured for a specific role? Easy, there is pg_roles:

postgres=> select rolname,rolconfig from pg_roles where rolname in ('a','b');
 rolname |    rolconfig    
---------+-----------------
 a       | {work_mem=1MB}
 b       | {search_path=b}
(2 rows)

Good to know…

 

Cet article Setting up default parameters for roles in PostgreSQL est apparu en premier sur Blog dbi services.

Alfresco 5.2 our DMS of choice!

Thu, 2017-07-27 06:46
Introduction

Nowadays companies have to deal with lots of electronic documents, some of them being mission critical. Insurances, Banks and Pharma industries are good candidates for ECM/DMS solutions since they produce and deal with lots of documentations, contracts and receipts. Usually the largest ECM/DMS infrastructures can be found at those customers which initiate large digitalization processes. However even for smaller businesses, managing e-documents like sales quotations, offers, answers to RFIs and RFPs becomes mission critical. Indeed, while creating such quotations and offers, collaboration is often requested between salesmen and eventually with the technical department too. The ECM/DMS solutions must offer the means to share and work together on the same document. Unfortunately these documents are, most of the time, simply lying around on a basic Windows Share, if the users even took the time to copy the documents on this share. As a result, there is no concurrency management, preventing any data loss and the “locking strategy” is quite simple: “last wrote … won”. It’s even incredible to see how many “larger” companies still work like that. All companies follow the digitalization trends but sometimes in a quite elementary way.

So basically what prevents the usage of a ECM/DMS solution in all companies? From my point of view, most of the time ECM/DMS projects are wrongly sized and approached. Indeed, each customer has lots of good intentions at the begin of the project. Therefore instead of focusing on the essentials, project responsible want to implement almost everything, and may be too much:

  • workflow management
  • complex user/group management and security rules
  • full text indexing
  • infrastructure redundancy
  • full integration in existing solutions
  • complex business processes (mixing up BPM and ECM/DMS)
  • aso … aso …

As a result the proposed ECM/DMS solutions can become quite complex to set up and quite expensive in terms of licenses. That’s exactly where those kinds of projects usually get stuck and die. We want to do too much, it gets too complex, so let’s do nothing! :-)

Is there a way and a technology which allows to start smoothly in the ECM/DMS area?

Why a DMS?

First of all, let’s summarize again which core functionalities we need from a DMS. In other words, what do we want to achieve with such a solution?

As a salesman, and in particular as a Chief Sales Officer, I need to keep a clear track of all changes. Indeed, while editing/changing documents, and in particular quotations, we should keep traces of each modifications. Release management and traceability is a “must have” nowadays. Document validation (workflow processes) would be nice to have in a second step.

Of course in the current context of cyber-attacks, I need a high security level. I also need to protect the documents against unauthorized users: we do not want/need all people in the company to know the sales engineering policy. Furthermore, we do not want viruses encrypting all our documents lying on a simple Windows Share. If the ECM/DMS solutions request identifications to the system to proceed with CheckOut/CheckIn procedures to work on documents the virus has almost no chance to access easily all files.

If this CheckOut/CheckIn procedure is included in the Microsoft Office suite, it won’t even decrease the efficiency of the users or of the salesmen. Users are always afraid when they have to do more that simple double clicks :-)

Why Alfresco?

As explained in the introduction, the costs and the over sizing of ECM/DMS projects may sometimes kill them before they even born.

Alfresco is an Open Source ECM/DMS solution allowing to implement quite quickly and easily the core needed functions without license costs. Of course, the Enterprise version offers some additional features like:

  • content encryption at rest and encrypted configuration files
  • clustering
  • synchronization of content between Cloud and On-Premises installations

At dbi services, since we are ECM/DMS experts we decided to implement Alfresco on our own. However, the setup and documentation of such a solution can be limited to several days, not weeks or years. We do not need bunch of senior technical experts and presales over several months to set it up, like for some un-named ERP solutions :-)

Out of the box, and in particular with the version 5.x, Alfresco really covers 100% of what I do expect from an ECM/DMS, as a salesman:

  • integrated release management
  • protection and management of concurrency between users
  • protection against viruses since some identification is needed and you can always revert a previous version if needed
  • easy drag & drop functionality to copy documents/folders into alfresco

Below, an example of the smooth integration of Alfresco in each Small and Medium Businesses environment using MS Office. With a smooth integration in MS Office it is now possible to directly work on a document and save it into alfresco without having to “CheckOut/CheckIn” it, since this operation is integrated in the Office connector. Below an example of the integration of Alfresco in MS Office once a so called “SharePoint online location” (compatible with alfresco) has been created. you can directly open the documents in Word from the Alfresco repository (checkin/checkout happens in the background) :

alfresco_5.2_open_MS_Office_4

Another example of smooth integration in the MS or Mac world, the drag and drop feature from the file browser directly in the Alfresco browser using any Web browser :

alfresco_5.2_drag_and_drop_4

It is even possible to save a newly created MS Office document directly into Alfresco, the integration has been really maximized in the last Alfresco release (5.x).

Another strong advantage of Alfresco is basically coming from the Open Source culture. Despite the fact that some companies still have the feeling that they have to pay expensive software licenses, it may sometimes be possible to think about the “service only” model. This approach, used by Open Source software, allows the product to improve and growth through contributors offering their services around the product. That’s the case for dbi services providing support around Alfresco which allows a professional usage of the solution. In the same idea, lots of contributors developed some Alfresco extensions allowing to improve the core functionalities and to integrate the product in lots of other solutions or products (i.e. in ERP solutions like Odoo, SAP, Salesforce, aso…). Some of these add-ons that were developed by the community are even integrated directly into the next Alfresco releases to improve the product (HTML5 Previewer, Trashcan Cleaner, aso…).

Providing the complete set of required core features, easy to deploy, manage and administrate, cost efficient and extensible, Alfresco has become a kind of optimum choice for our company development while insuring the quality of our Sales activities.

Conclusion

As for each IT project, we do strongly advice to follow a pragmatic way, ideally proceeding with POCs (Proof Of Concepts), in order to validate, step by step, the solution. Furthermore, it is advised to focus on essential functionalities first, avoiding huge and complex specifications giving the impression that we will never reach the end of the project.

Combining efficiency and security and providing the required features, Alfresco was the most competitive price/feature solution which helped us to growth as we did over the last years. The last version we just migrated to (version 5.2) did even increase the user acceptance since the integration into the usual office tools has been seriously improved.

 

Cet article Alfresco 5.2 our DMS of choice! est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 17 – Identifying a blocking session

Tue, 2017-07-25 13:49

One single blocking session in a database can completely halt your application so identifying which session is blocking other sessions is a task you must be able to perform quickly. In Oracle you can query v$session for getting that information (blocking_session, final_blocking_session). Can you do the same in PostgreSQL? Yes, you definitely can, lets go.

As usual we’ll start by creating a test table:

postgres@pgbox:/home/postgres/ [PG10B] psql -X postgres
psql (10beta2 dbi services build)
Type "help" for help.

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# 

One way to force other sessions to wait is to start a new transaction, modify the table:

postgres=# begin;
BEGIN
postgres=# alter table t1 add column t2 text;
ALTER TABLE
postgres=#  

… and then try to insert data into the same table from another session:

postgres@pgbox:/home/postgres/ [PG10B] psql -X postgres
psql (10beta2 dbi services build)
Type "help" for help.

postgres=# insert into t1 (a) values (1);

The insert statement will hang/wait because the modification of the table is still ongoing (the transaction did neither commit nor rollback, remember that DDLs in PostgreSQL are transactional). Now that we have a blocking session how can we identify the session?

What “v$session” is in Oracle, pg_stat_activity is in PostgreSQL (Note: I am using PostgreSQL 10Beta2 here):

postgres=# \d pg_stat_activity 
                      View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 datid            | oid                      |           |          | 
 datname          | name                     |           |          | 
 pid              | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | name                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | inet                     |           |          | 
 client_hostname  | text                     |           |          | 
 client_port      | integer                  |           |          | 
 backend_start    | timestamp with time zone |           |          | 
 xact_start       | timestamp with time zone |           |          | 
 query_start      | timestamp with time zone |           |          | 
 state_change     | timestamp with time zone |           |          | 
 wait_event_type  | text                     |           |          | 
 wait_event       | text                     |           |          | 
 state            | text                     |           |          | 
 backend_xid      | xid                      |           |          | 
 backend_xmin     | xid                      |           |          | 
 query            | text                     |           |          | 
 backend_type     | text         

There is no column which identifies a blocking session but there are other interesting columns:

postgres=# select datname,pid,usename,wait_event_type,wait_event,state,query from pg_stat_activity where backend_type = 'client backend' and pid != pg_backend_pid();
 datname  | pid  | usename  | wait_event_type | wait_event |        state        |               query                
----------+------+----------+-----------------+------------+---------------------+------------------------------------
 postgres | 2572 | postgres | Client          | ClientRead | idle in transaction | alter table t1 add column t2 text;
 postgres | 2992 | postgres | Lock            | relation   | active              | insert into t1 (a) values (1);
(2 rows)

This shows only client connections (excluding all the backend connections) and does not show the current session. In this case it is easy to identify the session which is blocking because we only have two sessions. When you have hundreds of sessions it becomes more tricky to identify the session which is blocking by looking at pg_stat_activity.

When you want to know which locks are currently being held/granted in PostgreSQL you can query pg_locks:

postgres=# \d pg_locks
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default 
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          | 
 database           | oid      |           |          | 
 relation           | oid      |           |          | 
 page               | integer  |           |          | 
 tuple              | smallint |           |          | 
 virtualxid         | text     |           |          | 
 transactionid      | xid      |           |          | 
 classid            | oid      |           |          | 
 objid              | oid      |           |          | 
 objsubid           | smallint |           |          | 
 virtualtransaction | text     |           |          | 
 pid                | integer  |           |          | 
 mode               | text     |           |          | 
 granted            | boolean  |           |          | 
 fastpath           | boolean  |           |          | 

What can we see here:

postgres=# select locktype,database,relation,pid,mode,granted from pg_locks where pid != pg_backend_pid();
   locktype    | database | relation | pid  |        mode         | granted 
---------------+----------+----------+------+---------------------+---------
 virtualxid    |          |          | 2992 | ExclusiveLock       | t
 virtualxid    |          |          | 2572 | ExclusiveLock       | t
 relation      |    13212 |    24576 | 2992 | RowExclusiveLock    | f
 relation      |    13212 |    24581 | 2572 | AccessExclusiveLock | t
 transactionid |          |          | 2572 | ExclusiveLock       | t
 relation      |    13212 |    24579 | 2572 | ShareLock           | t
 relation      |    13212 |    24576 | 2572 | AccessExclusiveLock | t
(7 rows)

There is one lock for session 2992 which is not granted and that is the session which currently is trying to insert a row in the table (see above). We can get more information by joining pg_locks with pg_database and pg_class taking the pids from above:

select b.locktype,d.datname,c.relname,b.pid,b.mode 
  from pg_locks b 
     , pg_database d
     , pg_class c
 where b.pid in (2572,2992)
   and b.database = d.oid
   and b.relation = c.oid;

 locktype | datname  | relname | pid  |        mode         
----------+----------+---------+------+---------------------
 relation | postgres | t1      | 2992 | RowExclusiveLock
 relation | postgres | t1      | 2572 | AccessExclusiveLock
(2 rows)

Does that help us beside that we now know that both sessions want to do some stuff against the t1 table? Not really. So how can we then identify a blocking session? Easy, use the pg_blocking_pids system information function passing in the session which is blocked:

postgres=# select pg_blocking_pids(2992);
 pg_blocking_pids 
------------------
 {2572}
(1 row)

This gives you a list of sessions which are blocking. Can we kill it? Yes, of course, PostgreSQL comes with a rich set of system administration functions:

postgres=# select pg_terminate_backend(2572);
 pg_terminate_backend 
----------------------
 t

… and the insert succeeds. Hope this helps …

PS: There is a great page on the PostgreSQL Wiki about locks.

 

Cet article Can I do it with PostgreSQL? – 17 – Identifying a blocking session est apparu en premier sur Blog dbi services.

Pages