tag:blogger.com,1999:blog-58291915506010270732024-03-13T12:35:09.411-04:00Guidewire, SAP, Oracle, UNIX, Genesys Technology BlogGuidewire, SAP, Genesys, hacking, Oracle Application Server, Oracle database, UNIXSChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.comBlogger238125tag:blogger.com,1999:blog-5829191550601027073.post-52237848148440307242024-02-05T13:01:00.006-05:002024-02-05T13:01:50.248-05:00Oracle sqlplus Login Failure Handling in Shell Script<p> Product: Oracle RDBS</p><p>Command: sqlplus, shell script</p><h2>Overview</h2><p>I would like to document how to create a shell script that will report whether the username credential that stores in a file can successfully login to Oracle DB, or filed.</p><p>Without specifying any sqlplus parameter, sqlplus will always return exit code of 0 to the shell script. I have seen people uses text processing utility to search for the login failure command to detect login failure, but they should use parameter "/l" to do that.</p><p>Moreover, in a shell script, I will suggest to turn off the login banner to make the output less verbose by using parameter "/s"</p><h2>Instruction</h2><p>Suggested command is "sqlplus /s /l username@TNS"</p><p>To automate the password, my suggested command is "echo [password] | sqlplus /s /l username@TNS"</p><p>Later, the shell script can uses the OS return code's build-in parameter $? with following meaning:</p><p></p><ul><li>0 - login successful</li><li>1 - login failed</li></ul><p></p><p><br /></p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0Canada56.130366 -106.34677127.820132163821157 -141.503021 84.440599836178848 -71.190521tag:blogger.com,1999:blog-5829191550601027073.post-62123752757116501882023-10-20T12:51:00.005-04:002023-10-23T10:55:56.301-04:00SSL Setup - Java https, smtp, MS SQL Server<p>Technology: Java using SSL Certificate, or TLS1.2 - 1.3 encryption</p><p>Product: web server (https), SMTP email, force encryption MS SQL Server</p><p>There are several steps involve in setup SSL for various servers besides following standard</p><p>1. Create new SSL certificate for the virtual hostname used by the applications</p><p>2. Import the SSL certificate to the application servers</p><p>2.1. For Java, you need to import into Java keystore. For OpenJDK or Oracle Java (JRE), it is located in [java_jre_home]\lib\security, or [java_<b>jdk</b>_home\jre\lib\security.</p><p>2.2. For Linux or Windows binary programs, you need to import into OS. For RHEL, the SSL certificate file will be /etc/ssl/certs</p><p>3. Configures the application server to enable SSL, and indirectly specified the SSL certificate to use</p><p>4. Configures the application client to enable SSL, and optionally</p><p>4.1. Java - optionally specifies SSL keystore if not using default</p><p>Now, let's pick an application that leverages multiple technologies, and gives real picture what needs to be configured to enable SSL</p><h2 style="text-align: left;">Cognos Analytics report server</h2><p>This reporting server has following functionality (typical, but could be more) that will has SSL encryption:</p><p>1. Web server - it is severing the report in html, so it is a web server</p><p>2. Web client - it is able to forward its traffic to a customer facing web server (IIS, Apache httpd, IBM httpd), so for this communication, it is a web client</p><p>3. SMTP email client - its interactive report and scheduled report can be sent out as email attachment (pdf, html)</p><p>4. DB client - it needs a "Content Store" DB to stores user credential and report template. It can has additional audit DB to store additional application data for audit functionality. Any DB that it is querying to display the reports are all act as a DB client</p><p>5. Authentication client - OOTB LDAP (Windows only) server, custom LDAP, Okta, IBM OpenConnect, etc authentication that it supports, it is acting as client</p><p>Therefore, for above 5 typical server & client functionality, SSL can be enabled and encrypts the traffic between remote servers and clients.</p><p>For all the above functionality, you need to understand that the product is a pure Java J2EE application. Therefore, all the SSL configuration will follow Java (standard) and IBM documentation (application specific).</p><p>Several and multiple SSL certificates involve in this encryption setup:</p><p>1. Java keystore located in [cognos_home]\configuration\certs\CAMKeystore (default password NoPassWordSet)</p><p>1.1. CAMKeystore keeps the SSL cert for web/<b>https </b>access for Cognos Analytics server. If there are multiple virtual hostname (such as internal access, intranet access, internet access), then there are multiple SSL certs (unless the SSL cert contains multiple virtual hostname)<br />1.2. CAMKeystore keeps the SSL cert for Cognos Analytics server's databases access, e.g. Content DB, audit DB, data store DB. Example is MS SQL Server force encryption. Each remote DB server uses different SSL cert, and CA root<br />1.3. CAMKeystore keeps the SSL cert for SMTP server, which used for email of report, or email notification functionality. Example is smtp protocol with STARTTLS that enforce SSL, or smtps protocol. Typically only 1 SMTP server, so only 1 SSL cert<br />1.4. CAMKeystore keeps the SSL cert for client side https with remote customer users facing web server<br />1.5. CAMKeystore keeps the SSL cert for remote authentication server, e.g. LDAP , ActiveDirectory, and SSO. Each authentication server has its own SSL cert</p><p>Procedure:</p><p>1. Determine the root certificate for all 5 components above. Each server will have its own SSL cert, and might not use the same CA root certificate</p><p>2. Verify in [cognos_home]\configuration\certs\CAMKeystore that the CA root certificate is already there, and will be recognize</p><p>2.1. Follows Cognos Analytics doc: <span style="background-color: white; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px;">DLS_SSL_CertImportTool.bat (Windows), or sh (Linux) - </span>https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=server-enabling-secure-tls-connection-your-email</p><p>3. Even if it is in CAMKeystore, verify that it is not expired</p><p>4. If not there, or expired, then export the CA root cert from remote server</p><p>5. Import into CAMKeystore, which is the IBM Java keystore</p><p>6. For DB used in data store, import the SSL cert into [cognos_home]\ibm-jre\jre\lib\security\<b>cacerts</b></p><p>6. Follows various IBM Cognos Analytics document to enable SSL encryption</p><p>6.1. For Cognos Analytics 12.0.0 SMTP - https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=server-enabling-secure-tls-connection-your-email</p><p>6.2. For Cognos Analytics 11.2.0 Content DB, audit DB, logging DB as database client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases</p><p>6.3. For data store as DB client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases</p><h2 style="text-align: left;">Tools</h2><p>There are various DOS utilities that can troubleshoot SSL communication, but none for Java. Some sample Java programs are available, but you have to compile, as well as familiar with SSL logging parameters that you have to manually specify.</p><p>Tools for</p><p>1. Windows: openssl</p><p>2. UNIX, Linux - openssl, curl</p><p>3. Java SMTP - Oracle javamail-samples.zip that based on Oracle javax.mail.jar library that you need to download. It contains sample SMTP with STARTTLS sample code that you need to modify the Java program to specify SMTP port (default port 25)</p><p>4. Java MS SQL Server client - Microsoft provided sample https://learn.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?view=sql-server-ver16</p><h3 style="text-align: left;">Tool - opemnssl for SMTP</h3><p>For SMTP that uses STARTTLS to enable encryption, uses following command to troubleshoot SSL issue</p><p>openssl s_client -starttls smtp -crlf -connect [remote email server hostname]:[smtp port]</p><p>This relies on OS SSL certificate store, so not applicable for Java SMTP troubleshooting</p><h3>Tool - opemnssl for SMTPS</h3><div>Same as above, but uses SMTPS SSL encryption, not SMTP + STARTTLS</div><div><p>openssl s_client smtps -crlf -connect [remote email server hostname]:[smtp port]</p></div><h3 style="text-align: left;">Tool - curl for SMTP</h3><p>curl is preinstalled in Linux. It can login to SMTP email server, and send out email. Therefore, it is useful to ensure the remote SMTP server is properly configured to allow your application server to send out email. This uses SMTP + STARTTLS protocol</p><p>curl -v smtp://email-smtp.us-east-1.amazonaws.com:587 --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email" -T emailcontent.txt --ssl</p><div><h3>Tool - curl for SMTPS</h3><p>Same as above, but uses SMTPS instead of SMTP + STARTTLS</p><p>curl -v smtps://email-smtp.us-east-1.amazonaws.com:587 --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email" -T emailcontent.txt</p><h3 style="text-align: left;">Tool - Test-NetConnection</h3></div><p>PowerShell Test-NetConnection can be used to verify the application server machine can reach remote SMTP/DB/LDAP/Okta/SSO/web server. This will give you a quick indication that the firewall, router, DNS, AWS Security Group, etc are configured to allow them to talk before you perform application side of testing, such as openssl, or curl above</p><p>test-netconnection [remote server hostname] -port [remote server port]</p><p>Example for MS SQL Server: test-netconnection [remote MS SQL Server virtual hostname] -port 1433</p><p>Example for web server: test-netconnection [remote web server virtual hostname] -port 443</p><p>Example for Oracle DB: test-netconnection [remote Oracle virtual hostname] -port 1521</p><h3 style="text-align: left;">Tool - Java</h3><p>Enable following SSL debug parameter when running sample MS SQL Server, Oracle sample JavaMail program, etc</p><p>Example for using Oracle JavaMail sample called smtpsend.java: java -cp .;..\javax.mail.jar;..\javax.activation-api-1.2.0.jar <b>-Djavax.net.debug=ssl:handshake</b> smtpsend</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-55088720270223790522023-05-26T17:07:00.003-04:002023-05-26T17:07:52.964-04:00SAP IPS - setup.exe doesn't recognize Product Key<p>Product: SAP IPS<br />Version: 4.1 - 4.2</p><h2 style="text-align: left;">Symptom</h2><p>Run setup.exe, and enter a valid product key. Installer returns following error</p><p>Error<br />An error has occurred.</p><p>The product key is not valid. INS00140</p><p>Checked installer log in /tmp/yyyy.mm.dd.hh24.mi.ss/setupengine.log for key validation failure. It is showing following</p><p>20:58:18.743 RawArgFormatter - property not set yet: shared.library.keycode.props-4.0-core$ProductKey</p><p>20:58:25.692 [validate_ProductKey]argument: -keycode ******** -version 142 -property BOE.EnableBOE</p><p>20:58:26.056 [run_external_executable] cmdLine: cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"; /opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions//isKeycodeValid -keycode ******** -version 142 -property BOE.EnableBOE</p><p>20:58:26.056 [validate_ProductKey]keyTypes [BOE.EnableBOE:142] is bad</p><h2 style="text-align: left;">Solution</h2><div>For for full path of program "isKeycodeValid" shown in above log, then run following</div><div>chmod u+x <full path from log above>/isKeycodeValid</div><div><br /></div><div>You should be able to manually test this is success</div><div>cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"</div><div>isKeycodeValid -keycode <non case sensitive product key> -version 142 -property BOE.EnableBOE</div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-65197605745289684432023-05-16T14:26:00.002-04:002023-05-16T14:26:13.368-04:00Cognos Analytics 11.1.x - 11.2.x - Installer failed with JRE libraries missing or not compatible<p>Product: IBM Cognos Analytics<br />Version: 11.1.0 - 11.2.2<br />OS: Linux, AIX, PowerPC, all UNIX distributions<br />Related IBM KB: https://www.ibm.com/support/pages/jre-libraries-are-missing-or-not-compatible</p><h2 style="text-align: left;">Problem</h2><div style="background-color: white; border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">When executing the Cognos installer on a Linux,Aix server the installer fails to run the following error:</div><div style="background-color: white; border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><br /></div><div style="background-color: white; border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><p style="border: 0px; box-sizing: border-box; color: #323232; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 1rem; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; letter-spacing: 0px; line-height: 1.625rem; margin: 0px; padding: 0px 0px 15px; vertical-align: baseline;">Preparing to install...<br style="box-sizing: border-box;" />Extracting the JRE from the installer archive...<br style="box-sizing: border-box;" />Unpacking the JRE...<br style="box-sizing: border-box;" />Extracting the installation resources from the installer archive...<br style="box-sizing: border-box;" />Configuring the installer for this system's environment...</p><p style="border: 0px; box-sizing: border-box; color: #323232; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 1rem; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; letter-spacing: 0px; line-height: 1.625rem; margin: 0px; padding: 0px 0px 15px; vertical-align: baseline;">Launching installer...</p><p style="border: 0px; box-sizing: border-box; color: #323232; font-family: inherit; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 1rem; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-variation-settings: inherit; letter-spacing: 0px; line-height: 1.625rem; margin: 0px; padding: 0px 0px 15px; vertical-align: baseline;">JRE libraries are missing or not compatible....<br style="box-sizing: border-box;" />Exiting...."</p><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><h2 class="ibm-h4 ibm-bold ibm-northstart-documentation-information-label" style="border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 1.25rem; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0px; line-height: 1.5625rem; margin: 0px; padding: 0px 0px 15px; vertical-align: baseline;">Symptom</h2><div style="border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">The installer consistently fail to run with below false alarm:</div></div><div style="border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><ul style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px; text-align: left;"><li>JRE libraries are missing - not applicable as the installer is downloaded many times from IBM as a valid zip file, as well as across many different version of Cognos from 11.1.0 - 11.2.2</li><li>JRE not compatible - not possible, as the installer bundled IBM Java in /tmp/install.dir.[nnnnn]/Linux/resource/jre/bin</li></ul><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;">(login as UNIX cognos user that created to own the application)</span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;">$ echo $TMP</span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;">[empty]</span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;">$ grep noexec /etc/fstab</span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">UUID=c7aac85c-4dfd-4e40-bd6c-bf57c8284c0c / xfs defaults,noexec</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">$ echo "echo 012345" > /tmp/test_exec.sh</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">$ sh /tmp/tst_exec.sh</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">/tmp/tst_exec.sh: Permission denied</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">$ </span></span><span style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">/tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java -version</span></div><div><span style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">/tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java: </span><span style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; letter-spacing: 0.16px;">: Permission denied</span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;"><br /></span></div></div><h2 style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; text-align: left; vertical-align: baseline;">Cause #1</h2><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">/tmp mount point is mounted without execute permission for security reason (checks /etc/fstab with value noexec).</div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">It is confirmed by tst_exec.sh that created above that it failed to run</div><h2 style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; text-align: left; vertical-align: baseline;"><span style="letter-spacing: 0.16px;"><br /></span></h2><h2 style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; text-align: left; vertical-align: baseline;"><span style="letter-spacing: 0.16px;">Cause #2</span></h2><div style="border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">A wrong installer for other OS is downloaded. For Intel Linux, you should expect following output</div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><br /></div><div style="border: 0px; box-sizing: border-box; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">$ file analytics-installer-3.6.46-linuxx86.bin</div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">analytics-installer-3.6.46-linuxx86.bin: data</div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><br /></div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Following is expected for Intel x86 64-bit Linux</div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="font-size: medium; letter-spacing: 0.16px;">$ </span><span style="font-size: medium; letter-spacing: 0.16px;">file /tmp/install.dir.*/Linux/resource/jre/bin/java</span></div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="font-size: medium; letter-spacing: 0.16px;">/tmp/install.dir.15939/Linux/resource/jre/bin/java</span><span style="letter-spacing: 0.16px;">: ELF 64-bit LSB shared object, <b>x86-64</b>, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped</span></div><div><span style="letter-spacing: 0.16px;"><br /></span></div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="font-size: medium; letter-spacing: 0.16px;"><br /></span></div><div style="border: 0px; box-sizing: border-box; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-feature-settings: inherit; font-kerning: inherit; font-optical-sizing: inherit; font-size: 16px; font-stretch: inherit; font-variant-alternates: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; font-variation-settings: inherit; letter-spacing: 0.16px; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Following is for PowerPC Linux, not Intel x86</div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">$ file /tmp/install.dir.*/Linux/resource/jre/bin/java</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;">/tmp/install.dir.15939/Linux/resource/jre/bin/java: ELF 64-bit LSB executable, 64-bit <b>PowerPC </b>or cisco 7500, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=c07e00748dd7b5ca30b6feb0e2d7c911cec024d7, not stripped</span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;"><br /></span></span></div><div><span style="color: #323232; font-family: IBM Plex Sans, IBM Plex Mono;"><span style="letter-spacing: 0.16px;"><br /></span></span></div><h2 style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px; text-align: left;">Resolving the Problem</h2><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">Option #1: Identify a directory that user is allow to run the installer program, i.e. java. Specify Cognos installer OS environment variable IATEMPDIR to that location before execute the installer</div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><br /></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">$ export IATEMPDIR=~/tmp</div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">$ analytics-installer-3.6.46-linuxx86.bin</div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><br /></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;">Option #2: Coordinate with Linux SA to remove "noexec" from /etc/fstab, or provides another mount point that doesn't contain "noexec" security option. Assumes the mount point is called /tmp2, then set <span style="letter-spacing: 0.16px;">Cognos installer OS environment variable IATEMPDIR to that mount point before execute the installer</span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><span style="letter-spacing: 0.16px;"><br /></span></div><div style="color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; letter-spacing: 0.16px;"><div style="letter-spacing: 0.16px;">$ export IATEMPDIR=/tmp2</div><div style="letter-spacing: 0.16px;">$ analytics-installer-3.6.46-linuxx86.bin</div><div style="letter-spacing: 0.16px;"><br style="letter-spacing: 0.16px;" /></div></div></div></div></div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-56854588713312296512023-02-27T09:18:00.007-05:002023-02-27T10:07:07.408-05:00Oracle Timezone: Clarification of 2 Timezone Setting<p>Product: Oracle Database<br />Version: All<br />OS: All</p><h2 style="text-align: left;">History</h2><p>Many years ago, Oracle database relies on OS clock, as well as no date column type that can store timezone setting (similar to MS SQL Server today).</p><p>Timezone aware column type, TIMESTAMP WITH TIME ZONE, introduced much later, but many DBA and developers don't have a good knowledge about OS timezone.</p><h2 style="text-align: left;">Date DB Function</h2><div>In Oracle 12c to 21c, DB has following 4 date functions, which has different behavior on timezone</div><div><ul style="text-align: left;"><li>sysdate - Uses OS timezone</li><ul><li>E.g. select sysdate from dual;</li><li>E.g. insert into [user table] values (sysdate);</li></ul><li>systimestamp - Uses OS timezone</li><ul><li>E.g. select systimestamp from dual;</li><li>E.g. insert into [user table] values (systimestamp);</li></ul><li>current_date - uses DB session timezone, similar to sysdate</li><ul><li>E.g. select sessiontimezone from dual;</li><li>E.g. insert into [user table] values (sessiontimezone);</li></ul><li>LOCALTIMESTAMP</li><li> - uses DB session timezone, similar to systimestamp</li><ul><li>E.g. select sessiontimezone from dual;</li><li>E.g. insert into [user table] values (sessiontimezone);</li></ul></ul><h2 style="text-align: left;">Timezone Configuration At DB Level</h2><div>I won't discuss OS timezone configuration, as this post is mainly clarifying DB level timezone setting that confused many DBA or developers.</div><div><br /></div>In above DB functions, the DB session's timezone depends on sessiontimezone. Default is DB timezone, which set by following:<br /><ul style="text-align: left;"><li>ALTER DATABASE SET TIME_ZONE = 'US/New_York';</li><li>CREATE DATABASE ..... TIME_ZONE = 'US/New_York';</li></ul><div>At user level, you can configure it using</div><ul style="text-align: left;"><li></li><li>ALTER SESSION SET TIME_ZONE='US/Eastern';</li></ul>You can check its current value by using:<br /><ul style="text-align: left;"><li>sessiontimezone - uses DB session timezone, similar to sysdate</li><ul><li>E.g. select sessiontimezone from dual;</li></ul></ul><h2 style="text-align: left;">Troubleshooting Highlight</h2><div>When verifying it, always display the sessiontimezone together with 1 systimestamp, and 1 current_date, e.g.</div></div><div>select sessiontimezone , sysdate, current_date from dual;</div><div><br /></div><div>This will ensure that you cover both OS and DB timezone, and displaying DB timezone value</div><div><br /></div><div>Note: systimestamp will display OS timezone value</div><div><br /></div><div>Ref: Oracle 19c DB Time Functions: https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html</div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-63144457719110546042023-01-27T12:00:00.005-05:002023-01-27T12:00:51.683-05:00Oracle No more data to read from socket<p>Product: Oracle Database<br />Version: All</p><p>I often found from StackOverflow.com that people are posting following error from their application:</p><p><span style="background-color: white; color: #1d1c1d; font-family: Slack-Lato, Slack-Fractions, appleLogo, sans-serif; font-size: 15px; font-variant-ligatures: common-ligatures;">No more data to read from socket</span><br style="background-color: white; box-sizing: inherit; color: #1d1c1d; font-family: Slack-Lato, Slack-Fractions, appleLogo, sans-serif; font-size: 15px; font-variant-ligatures: common-ligatures;" /></p><p>The application could be Java, Springframework, etc, and they often blame the application problem, as the error is captures by the application, e.g. Java application/daemon.</p><p>Often people will suggest possible cause, or possible resolution. Some people will even suggest not related resolution which confused the entire IT community.</p><p>The full Oracle error is below:</p><p>ORA-17410 No more data to read from socket</p><p>There are several possible causes, but many people don't put when each is applicable. The issue is 100% not due to the application, but problem with the infrastructure (network) or Oracle database:</p><h2 style="text-align: left;">Cause #1: Intermittent Network issue</h2><p>Behavior #1: Application will fail with this error intermittently</p><p>Behavior #2: Point of failure will be random, and won't be in the same table/index/SELECT SQL, as network failure is intermittent</p><p>Behavior #3: Program should failed when pull data into the application, such as SELECT, but should not fail with CREATE TABLE, CREATE INDEX, DELETE, DROP, ALTER</p><p>Behavior #4: Any application will be affected by intermittent network issue, instead of just 1 application</p><p>Behavior #5: Perform a few GB size file transfer between the application and remote DB server to simulate intermittent network issue. The duration must be longer than the SQL execution time. E.g. if it is running a SELECT that take 5 min (then failed), then the file transfer time must be more than 5 min, such as 10 min. So the test is not actually based on file size, but file x'fer time that last as long as the failed SQL</p><p>Problem Simulation: Simulate a 5 - 30 min long file transfer to confirm the issue is network related. The source and target server used to simulate file transfer must include the application server with the remote DB server</p><p>Workaround: Once confirmed, you can move the DB and application into the same machine to eliminate network issue. The network admin can take longer time to provide a permanent workaround</p><h2 style="text-align: left;">Cause #2: Intermittent DB Failure</h2><p>Behavior #1: Application could fail with this error randomly or consistently</p><p>Behavior #2: Oracle alert log will contain more detail error message</p><p>Behavior #3: Oracle alert log could throw ORA-00600 that DBA needs to raise ticket with Oracle Support due to DB patch/bug</p><p>Behavior #4: Even putting both application and Oracle database into the same machine, this error will continue to exists intermittently</p><p>Problem Simulation: Capture the failed SQL, and run it manually using SQL Developer or sqlplus. You might need to create a shell script (PowerShell for Windows) to run it few 1000s time and last for 1 hr to simulate the error</p><p>Workaround #1: If it is ORA-00600 Oracle error, then try it with higher or lower Oracle database version as temporary workaround. There are potential Oracle bug that affect specific version and patch.</p><p>Workaround #2: There could be other OS issue that affected the Oracle database. Move Oracle database to another different machine (OS)</p><p>Workaround #3: Check RAM and storage to ensure no low storage, or extremely high RAM usage when the application hitting the error</p><h2 style="text-align: left;">Cause #3: Wrong JDBC driver</h2><p>Behavior #1: Java application consistently failed and not intermittent</p><p>Problem Simulation: Create a simple Java program and run the same SQL by using the exact JDBC driver. This should consistently failed</p><p>Workaround: Replace the Oracle JDBC driver with Oracle's</p><h2 style="text-align: left;">Cause #4: Special data or table structure that is not supported</h2><p>Behavior #1: Application is creating table, or inserting data that is more advance, e.g. Spatial. The program will consistently failed for the same SQL</p><p>Behavior #2: This is not intermittent. If you think it is intermittent, then capture all the of failed SQL, and look for the pattern. They must use some special data type that is <b>not </b>varchar2, number, date, long, clob, blob, function, partition</p><p>Problem Simulation: Capture the SQL, and create a simple Java program to run the same SQL. The Java program will consistently fail</p><p>Workaround #1: Rewrite the Java program on this SQL. It could be wrong Java object that is not compatible</p><p>Workaround #2: Very old JDBC driver that doesn't support that new feature can be another cause. Replace with newer Oracle JDBC driver</p><h2 style="text-align: left;">Cause #5: Firewall Timeout - idle connection clearing</h2><div>Behavior #1: Some enterprise firewall can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value</div><div>Behavior #2: Find out the time between connection dropped and SQL is fired. The time is constant, such as 30 sec</div><div><br /></div><div>Workaround #1: Network admin to review the firewall rule, and exclude TCP port 1521 connection to & from Oracle database server</div><div><p>Workaround: Replace the Oracle JDBC driver with Oracle's</p><h2>Cause #6: Router Timeout - idle connection clearing</h2><div>Behavior #1: Some enterprise router can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value</div><div>Behavior #2: Find out the time between connection dropped and SQL is fired. The time is constant, such as 30 sec</div><div><br /></div><div>Workaround #1: Network admin to review the router rule, and exclude TCP port 1521 connection to & from Oracle database server</div></div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-6306682675780919702022-12-13T10:09:00.003-05:002022-12-13T10:09:44.955-05:00Oracle: ORA-01555: snapshot too old Tuning<p>Product: Oracle Databse<br />Version: 9.0.0 - 18.0</p><p>Since Oracle Database 9i, many DBA have been keeping UNDO_MANAGEMENT=AUTO setting, and developers/support often encountered following UNDO related error on long running SQL</p><pre class="c-mrkdwn__pre" data-stringify-type="pre" style="--saf-0: rgba(var(--sk_foreground_low,29,28,29),0.13); border-radius: 4px; border: 1px solid var(--saf-0); box-sizing: inherit; color: #1d1c1d; counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; font-family: Monaco, Menlo, Consolas, "Courier New", monospace !important; font-size: 12px; font-variant-ligatures: none; line-height: 1.50001; margin-bottom: 4px; margin-top: 4px; overflow-wrap: break-word; padding: 8px; tab-size: 4; white-space: pre-wrap; word-break: normal;">Oracle <ORCL> error message for operation <OCIStmtExecute>: <ORA-12801: error signaled in parallel query server P07G
ORA-01555: snapshot too old: rollback segment number 271 with name "_SYSSMU271_3009956035$" too small>.</pre><p>If DBA don't study Oracle documentation on "Automatic Tuning of Undo Retention" but simply search Google/Internet for answer, then they often comes to following incorrect workaround:</p><p></p><ol style="text-align: left;"><li>Increase UNDO_RENTENTION time - no change in the long running SQL</li><li>Modify code to split the long running SQL to have several shorter running SQL, e.g. store into intermedia table, then select from the intermedia table</li><li>Asks developer to tune the long running SQL to run faster, e.g. create index, don't select unnecessary columns, filter out unrequired data (more WHERE criteria)</li><li>Don't insert/update/delete on the table that the long running SQL is SELECTing</li></ol><p></p><p>So what does DBA missed in above analysis is he/she only focus in ORA-01555 error without considering the rest of the message. Following are the things that DBA missed:</p><p></p><ol style="text-align: left;"><li>Parallel query server is enabled</li><li>Above PQ number is P07G with starting number as P000</li><li>Number of concurrent running SQL that that point of time, without considering Parallel query server (excludes parallel query coordination and slave)</li><li>Size of the UNDO tablespace, which could be few TB in size, and bigger than entire DB size</li><li>UNDO tablespace's dbf files are not in auto extend mode</li><li>Even if UNDO dbf files are in auto extend mode, it hit max dbf file size</li></ol><div>As the result, tuning UNDO_RETENTION or even increase the size of UNDO dbf to 300GB larger doesn't resolve the issue.</div><div><br /></div><div>The resolution is in fact totally not related to modify UNDO_RETENTION.</div><div><br /></div><div>Post your comment if you want to know the answer, as I want to know how many visitors are actually reading my blog post</div><p></p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-81431425146341444152022-05-30T16:44:00.006-04:002022-05-30T16:44:57.032-04:00SAP Data Services Job Server - SSL Configuration<p>Product: SAP Data Services<br />Component: JobServer<br />Version: 4.2SP0 - 4.2SP14<br />OS: Windows</p><p>SAP Doc #1: https://help.sap.com/docs/SAP_DATA_SERVICES/2d2abbb0fab34071a4c53b7de873241b/571a69626d6d1014b3fc9283b0e91070.html?locale=en-US</p><p>SAP Doc #2: https://help.sap.com/docs/SAP_DATA_SERVICES/e54136ab6a4a43e6a370265bf0a2d744/571dc02c6d6d1014b3fc9283b0e91070.html?locale=en-US&version=4.2.8</p><p>SAP Doc #3: https://help.sap.com/docs/SAP_DATA_SERVICES/e54136ab6a4a43e6a370265bf0a2d744/571db2eb6d6d1014b3fc9283b0e91070.html?locale=en-US&version=4.2.8</p><p>SAP has a relatively poor documentation about custom SSL certificate setup for BODS Data Services, which is Job Server. They break it into several different chapters and failed to leverage "Related information" section to help customer to bridge the information together.</p><p>There are 2 main directories, and 1 tool (Data Services Server Manager in Windows, and svrcfg in Linux) to configure the SSL certificate:</p><p></p><ul style="text-align: left;"><li>%LINK_DIR%\ssl\server</li><li>%LINK_DIR%\ssl\trusted_certs</li></ul><p></p><p>The most common mistaken about SSL certificate is that administrator failed to read SAP's doc that their program only recognize PEM format SSL certificate (there are DER, PKCS12 that are common).</p><p>Let me list down a list of files and directory of each, which is applicable to both self-signed, or paid SSL cert:</p><p></p><ol style="text-align: left;"><li>Custom root CA SSL cert > %LINK_DIR%\ssl\trusted_certs</li><li>Custom root CA SSL cert private key, password file > not required</li><li>Custom intermediate #1 CA SSL cert > %LINK_DIR%\ssl\trusted_certs</li><li>Custom intermediate #1 CA SSL cert private key, password file</li><li>Custom intermediate #2 CA SSL cert > %LINK_DIR%\ssl\trusted_certs</li><li>Custom intermediate #2 CA SSL cert private key, password file</li><li>Custom intermediate #3 CA SSL cert > %LINK_DIR%\ssl\trusted_certs</li><li>Custom intermediate #3 CA SSL cert private key, password file</li><li>Custom BODS server SSL cert > %LINK_DIR%\ssl\server</li><li>Custom BODS server SSL cert private key > %LINK_DIR%\ssl\server</li><li>Custom BODS server SSL cert password file > %LINK_DIR%\ssl\server</li><li>There is no SSL certificate keystore for Job Server configuration</li></ol><p></p><p>There are several catch that SAP document doesn't cover, as well as misleading info in SAP KB:</p><p></p><ol style="text-align: left;"><li>%LINK_DIR%\ssl\trusted_certs can contains additional SSL certs files</li><li>%LINK_DIR%\ssl\trusted_certs cannot contains other files that are not SSL cert files, or it will complain error "Unable to load verify locations"</li><li>If %LINK_DIR%\ssl\trusted_certs contains private key PEM file, Server Manager will complain error "Unable to load verify locations" as the same reason explained in #2 above</li><li>%LINK_DIR%\ssl\trusted_certs can contains unused SSL certs, such as put new self-signed or paid cert files together with OOTB self-signed cert files</li><li>SSL cert that is V1 or V3 are both accepted</li><li>SSL cert that is RSA 1024-bit or 4096-bit are all accepted</li><li>The program is not able to produce meaningful log, and no way to enable debugging log</li><li>The openssl command in SAP's doc implies to generate PEM format certificate, but that command is actually generates "certificate request" file, and not the final certificate</li><li>SAP document wrote to put all the signed SSL certs into "%LINK_DIR%\ssl\trusted_certs" but you just need to put the root, intermediate, and parent SSL certs into it (as PEM format)</li><li>You can have SSL file extension other than crt: crt, cer, pem</li><li>The SSL cert that issue to BODS server should put under %LINK_DIR%\ssl\server directory, which SAP doc failed to mention. This is for consistency of their design, and won't break application functionality. I recommend to keep existing architecture instead of follow their doc</li><li>In Data Services Server Manager > SSL tab, configures</li><ol><li>Server certificate file: Location of the PEM SSL cert file that issued to this BODS server. It should be in %LINK_DIR%\ssl\server directory</li><li>Server private key file: Part 2/3 file for above SSL cert. It should be PEM format, and located in same dir as above</li><li>Use Server private key password file: Recommends to has a password file for above SSL cert. Recommends to keep in same dir as above</li><li>Trusted certificates folder: No need to change, but ensures copy all the root and intermediate cert files into dir %LINK_DIR%\ssl\trusted_certs (default). You do not need to change this location</li></ol></ol><div>You can use both Windows, or bundled openssl utility to convert SSL certificate to PEM format that recognized by BODS. PEM format file is ASCII file, which you can open it up in text editor to confirm that it is not binary file.</div><div><br /></div><div>Following is openssl command to convert DER format SSL cert file to PEM format:</div><div>%LINK_DIR%\bin\<b>openssl </b>x509 -<b>inform </b>DER -<b>in </b>custom.cert1.crt -<b>out </b>custom.cert1.pem.crt</div><div><br /></div><div><br /></div><span><a name='more'></a></span><div>Error: Not able to read the specified certificate file error BODI-320039</div><p>SAP Note 2287520 - Not able to read the specified certificate file error BODI-320039<br />https://launchpad.support.sap.com/#/notes/2287520<br />Cause: cer file generated by Microsoft CA was not in the format Data Services expects<br />Resolution: Regenerate the cer file in correct format as per Administrator Guide. Internet Explorer may be able to open the cer file properly but check from application like notepad the correct format expected by DataServices contains "BEGIN CERTIFICATE"</p><div>My comment: Data Services only recognize PEM format SSL cert, but not DER or PKCS12 format even the file has cer, crt, or PEM extension.</div><div><br /></div><span><!--more--></span><div>Error: Unable to load verify locations</div><div><br /></div><div>SAP Note 2409684 - Error: Unable to load verify locations is prompted when making changes in Data Service Server Manager</div><p>URL: https://launchpad.support.sap.com/#/notes/2409684<br />Cause: You have unnecessary certificates under trusted_certs which are 3rd party products(Coupa and GoDaddy). They were saved into the Data Server SSL folder<br />Resolution: Move the 3rd party SSL certificates to a different location</p><div>My comment: %LINK_DIR%\ssl\trusted_certs can contains any 3rd party SSL certificates, as well as unused, or inactive, or extra, or duplicate, or expired SSL certificates. It can contains non-PEM format SSL as well. Above error is not due to additional SSL certificate.</div><div>My resolution: Ensures the directory only contains SSL certificate, but no other files, such as private key, or password file</div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-50489347730121736942022-02-03T12:52:00.017-05:002023-05-17T12:41:47.999-04:00Powershell: List AD Group for Specific User and Domain<p>Product: Windows<br />Utility: Powershell, ActiveDirectory</p><p>Windwos AD domain users who are not administrator of ActiveDirectory users are often getting confusing push back from AD admin when the business users are requesting for access that controlled by AD.</p><p>This article is going to show various commands that business users (end users) who are not AD admin, and allows them to find out which AD group they belongs to directly, or indirectly (Windows term if group nesting).</p><h2 style="text-align: left;">CMD</h2><p>Following command will display all the nesting group that a user belongs to</p><p>1. net user <username> /domain</p><p>Limitation #1: You cannot change the domain, if the computer you login to belongs to multiple domain</p><p>Limitation #2: Doesn't show child groups. It will always display direct groups the user belongs to</p><p>Limitation #3: Display "Description" name of the group, not the actual group name</p><p>Limitation #4: Group name is truncated after 21 characters</p><p>2. gpresult /v</p><p>Limitation #1: can't specify which domain if you belongs to multiple AD domain</p><p>Limitation #2: Cannot show direct group. It will always display nesting child groups</p><p>3. whoami /groups</p><p>Limitation #1: can't specify which domain if you belongs to multiple AD domain</p><p>Limitation #2: Cannot show direct group. It will always display nesting child groups</p><h2 style="text-align: left;">PowerShell</h2><div>Note: Windows 10 - 11 required to use Settings > Optional features to add module "RSAT: Active Directory Domain Services and Lightweight Directory Services Tools"</div><p>1. Show AD group the current login user directly belongs to:</p><p>(Get-ADuser $env:username -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name</p><p>Limitation #1: It cannot show nesting group</p><p>1.1. Show AD group of username tester1</p><p>(Get-ADuser <b>tester1</b> -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name</p><p>1.2. Similar to above, but sorted</p><p>(Get-ADuser tester1 -server $env:userdomain -Properties memberof).memberof | get-adgroup -server $env:userdomain | select-object -ExpandProperty name | <b>sort</b></p><p>2. Show AD group user in specific domain, e.g. domain GAME-ADM</p><p>(Get-ADuser $env:username -server <i>GAME-ADM</i> -Properties memberof).memberof | get-adgroup -server GAME-ADM | select-object name</p><p>3. Show AD group user GAMER1 in domain GAME-ADM</p><p>(Get-ADuser <i>GAMER1 </i>-server <i>GAME-ADM</i> -Properties memberof).memberof | get-adgroup -server <i>GAME-ADM</i> | select-object -ExpandProperty name</p><p>4. Show AD group is <u>managed by who</u></p><p>General syntax: Get-ADGroup [group name] -server [domain name] -properties ManagedBy</p><p>Example: group name GAMEVIPUSR, domain name GAME-ADM, the command will be</p><p>Get-ADGroup <i>GAMEVIPUSR </i>-server <i>GAME-ADM </i>-properties <b>ManagedBy</b></p><p>Get-ADGroup <i>GAMEVIPUSR </i>-server $env:userdomain -properties <b>ManagedBy</b></p><div>5. Show members belong to AD group SAP-ADMIN</div><p>Get-ADGroupMember <b>SAP-Admins </b> -server $env:userdomain | select-object -ExpandProperty name</p><p>6. Similar to #5, but don't display user. Only display child AD group that belongs to it</p><p>Get-ADGroupMember <b>SAP-Admins </b> -server $env:userdomain | where-object objectClass -ne "user" | select-object -ExpandProperty name</p><p>7. Show a child group TESTER-GRP belongs to which parent group</p><p>Get-ADPrincipalGroupMembership GWRE-CASE-DataManagement -server $env:userdomain | select-object -ExpandProperty name</p><p>8. Show a child group TESTER-GRP belongs to which parent group like "PROD*" and not case-sensitive</p><p>Get-ADPrincipalGroupMembership GWRE-CASE-DataManagement -server $env:userdomain | | where name -match "PROD*" | select-object -ExpandProperty name </p><p><br /></p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-25713180484087447912022-01-28T11:35:00.013-05:002022-01-31T09:37:34.197-05:00IBM Informix Startup Problem: ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment<p>Product: IBM Informix<br />Version: 10.x - 12.x<br />Related Product: Cognos Analytics 11.x</p><p>Cognos Analytics server bundled with Informix DB server 12.x, if you choose to use build-in DB server as Cognos' Content Database's daemon.</p><p>This post contains troubleshooting in startup this Informix DB daemon in Windows, by illustrating a problem after a hostname change.<br /></p><p>I will use the Informix 12.10.FC5 setting by using Cognos Analytics 11.0.5 Easy Install option that created following variables/setting:</p><p></p><ul style="text-align: left;"><li>INFORMIXSERVER <b>= </b>cognoscm</li><li>Local Windows user to start Informix = informix</li><li>OS = Windows</li></ul><p></p><p>I will summarize the steps to change hostname, which if you missed (or don't aware of it), then you will get following error when manually starts up Informix (as Windows Services won't give you much detail, including Windows Event Viewer):</p><p>1. Run cmd<br />2. cd cognos_home\informix<br />3. Run: ol_cognoscm.bat (to set required Informix env variables)<br />4. cd cognos_home\informix\bin<br />5. Run: onstat (to show DB running status)</p><p><b>ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment</b></p><p>6. echo %informixserver% (to confirm its value set to cognoscm, default)<br />7. Since informixserver variable is set, it become clueless why it can't startup</p><p>The root cause that it can't starts up will be 2:</p><p>1. Local machine account "informix" password expired. Checks with Windows lusrmgr.msc (Local Users and Groups Manager) GUI to reset its password, and allow login<br />2. Windows Service name "informix IDS - ol_cognoscm" password mismatch, or removed. Enters in tab "Log On" with account ".\informix" and password set in #1 above<br />3. Registry below points to wrong hostname<br />3.1. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\Users\Usernames\[hostname]\Administrator<br />3.2. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\Users\Usernames\[hostname]\informix<br />3.3. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\Users\Usernames\[hostname]\UID6 -> [hostname]\informix<br />3.4. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\Users\Usernames\[hostname]\UID6 -> [hostname]\Administrator<br />3.5. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\IXDBSA Group\Domain -> [hostname]<br />3.6. HKLM\SOFTWARE\Informix\Online\ol_cognoscm (name base on %<b>INFORMIXSERVER%</b> above)\Security\</p><p>When you change Windows hostname, you need to change Windows Registry under HKLM\SOFTWARE\Informix\Online\ol_cognoscm</p><p>If you changed <i>INFORMIXSERVER </i>value in environment variable, then corresponding registry needs to create in HKLM\SOFTWARE\Informix\Online\[new INFORMIXSERVER value]</p><p>You will need to run cognos_home\informix\bin\ntchname -m -o [old hostname] -n [new hostname] as per IBM KB https://www.ibm.com/support/pages/after-renaming-windowstm-server-bundled-informix-database-server-will-not-start-easy-installation with Windows hostname change (separate KB for UNIX)</p><p>"ntchname" utility will change most hostname in the registry. Verify it is changed by checking following registry value</p><p></p><ul style="text-align: left;"><li>HKLM\SOFTWARE\Informix\SQLHOSTS\ol_cognoscm_drda (name base on %<b>INFORMIXSERVER%</b> above) -> HOST. The hostname will contains a "*" as the first character followed by the hostname (this section was not changed by "ntchname" utility)</li><li>File [cognos_home]\informix\etc\sqlhosts (file name is referred in Windows registry under HKLM\SOFTWARE\Informix\Online\ol_cognoscm\Environment > INFORMIXSQLHOSTS), value of old hostname should be replaced with new hostname. One line for port 9080 (default), and one line for port 9110 (default)</li></ul><div>Informix log file (installed/setup by Cognos Analytics 11.0.5): [cognos home]\informix\ol_cognoscm.log</div><div>On hostname change troubleshooting, following will log if it is using old hostname:</div><p></p><div>08:46:56 Dataskip is now OFF for all dbspaces</div><div><div>08:46:57 Init operation complete - Mode Online</div><div>08:46:57 Checkpoint Completed: duration was 0 seconds.</div><div>08:46:57 Fri Jan 28 - loguniq 66, logpos 0xfa40c0, timestamp: 0xf607d4 Interval: 607</div><div><br /></div><div>08:46:57 Maximum server connections 0 </div><div>08:46:57 Checkpoint Statistics - Avg. Txn Block Time 0.008, # Txns blocked 0, Plog used 24, Llog used 1</div><div><br /></div><div>08:46:57 On-Line Mode</div><div>08:47:00 Assert Warning: Unable to bind to the port (<b>9110</b>) on the host (*[old hostname]) for the server (<b>ol_cognoscm</b>).</div><div>08:47:00 IBM Informix Dynamic Server Version 12.10.FC5</div><div>08:47:00 Who: Session(4, informix@, 0, 0000000000000000)</div></div><div><br /></div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-49060608075321146682021-12-22T15:29:00.003-05:002021-12-22T15:29:28.090-05:00ssh: Setup Passwordless Login<p>Product: ssh daemon<br />Version: All</p><p>As SELinux become standard about 10 yr ago, but many ssh passwordless setup is still not updated, and causing many confusion. This post is going to show the complete setup procedure.</p><h2 style="text-align: left;">Preparation</h2><p>Enable SSH Daemon log level to DEBUG1, as this is critical to troubleshoot ssh login, as well as sharing sufficient information in the UNIX community forum, when you need to get help:</p><p>1. Login as root (or sudo su)<br />2. Modify /etc/ssh/sshd_config file: vi /etc/ssh/sshd_config<br />3. Modify</p><p>From: LogLevel INFO<br />To: LogLevel DEBUG1</p><p>4. Other acceptable level are DEBUG1, DEBUG2, DEBUG3<br />5. Restart ssh daemon: systemctl restart sshd<br />6. View the log while simulating ssh password-less login: tail -f /var/log/secure</p><h2 style="text-align: left;">Setup</h2><p>Following illustration will use OS username "oracle" as this is a common example for Oracle database</p><p>1. Login as oracle user<br />2. Create ".ssh" directory, if doesn't exists: mkdir ~/.ssh<br />3. Change user permission that other user/group can't access: chmod go= ~/.ssh<br />4. Create authorized_keys and paste the entry from remote machine (such as PuTTY) into it: vi ~/.ssh/authorized_keys<br />5. Change SELinux permission for above file: restorecon -Fvv ~/.ssh/authorized_keys<br />6. Setup the ssh client to auto login as oracle, with local file, while another ssh session viewing /var/log/secure in real time to troubleshoot the problem, if applicable</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-62862857823711237682021-12-10T15:07:00.006-05:002021-12-10T15:25:34.874-05:00Apache Tomcat with log4j: Workaround to address CVE-2021-44228 in Tomcat Application Server<p>Product: Tomcat Application Server<br />Version: All version<br />URL: https://www.bleepingcomputer.com/news/security/new-zero-day-exploit-for-log4j-java-library-is-an-enterprise-nightmare/</p><p>Apache log4j has a new CVE-2021-44228 vulnerability announced yesterday, which allow remove code executing using LDAP messaging lookup.</p><p>BleepingComputer.com shown that existing Apache Log4j can use Java startup parameter "<i>-Dlog4j2.formatMsgNoLookups=true</i>" so this post is to show how to configure Windows Service to configure Tomcat Application Server service to startup with this additional Java startup parmaeter</p><h2 style="text-align: left;">Procedure</h2><p>1. Run "cmd" to launch Command Prompt</p><p>2. For each of the Tomcat in the machine, cd to its bin directory, e.g. <br />cd "D:\SAP\SAP BusinessObjects\tomcat\bin"</p><p>3. Type "services.msc" and look for the Tomcat service name from "Services" screen. You need to know its instance name to modify it later</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRCsDd663HBireDgddhB8n530XT_ErwZXOhpetzRW4jRz6GLn6EgCnULgpP-1JbQWa_w7hbRm66Cn0I8eEpu2SsG5UTs2mGiD_M467omTKbQ9UmeJRyYB8gM85GJXHGmky97aJHOgjsPbP/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="468" data-original-width="406" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRCsDd663HBireDgddhB8n530XT_ErwZXOhpetzRW4jRz6GLn6EgCnULgpP-1JbQWa_w7hbRm66Cn0I8eEpu2SsG5UTs2mGiD_M467omTKbQ9UmeJRyYB8gM85GJXHGmky97aJHOgjsPbP/s16000/image.png" /></a></div>4. The Tomcat instance name is <b>BOEXI40Tomcat </b>in my env<p></p><p>5. Open up Tomcat Windows Service editor screen to modify this Windows Service configuration</p><p>tomcat9w //ES/<b>BOEXI40Tomcat</b></p><p>6. You will see following editor screen. If you don't, go back Step 3 to ensure you are specifying the correct Tomcat instance name</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD_213G2_mCwASloOlmwSdCmO7NfTn9sFb66UC4VPc7rMx529I2hCB-QxGNA1Da29DfhPclqtBrgJ-iqKiq17IkLvpePPqjVyvOTGRGzQMnJ6a-GnPHE25DSGq8d6cXpd31skWhJgyjDQd/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="491" data-original-width="412" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD_213G2_mCwASloOlmwSdCmO7NfTn9sFb66UC4VPc7rMx529I2hCB-QxGNA1Da29DfhPclqtBrgJ-iqKiq17IkLvpePPqjVyvOTGRGzQMnJ6a-GnPHE25DSGq8d6cXpd31skWhJgyjDQd/s16000/image.png" /></a></div><br />7. Click on tab "Java" and you should see a text box labeled "Java Options"<p></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGkA-lvj5C1l5f42-ntNc4tz52DobcyubrVoTeFGrspmo_U7J00p9cCLgqfdzLMn2_j8pL-UwemlRBWXPTNEkEzDW79zb8I5rV18rw2XgX7-4c_8KKoP9nkvMPagNy9oCiWdXtN0VdKQpV/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="491" data-original-width="412" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGkA-lvj5C1l5f42-ntNc4tz52DobcyubrVoTeFGrspmo_U7J00p9cCLgqfdzLMn2_j8pL-UwemlRBWXPTNEkEzDW79zb8I5rV18rw2XgX7-4c_8KKoP9nkvMPagNy9oCiWdXtN0VdKQpV/s16000/image.png" /></a></div><br />8. Scroll to the bottom and add <i>-Dlog4j2.formatMsgNoLookups=true</i><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-PGHrNfduMGmnKdrGaonalQugJAjcyl05hdIhbqPXeft1PUFhlB2JYX075vTNZvnj4QSBcaeZqysV7jccZ0xOmEV9kTjefwwKaXXRAlho3XLGMR35vC8hxZciXvieXOUxxRS1vQx_QrIt/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="491" data-original-width="412" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-PGHrNfduMGmnKdrGaonalQugJAjcyl05hdIhbqPXeft1PUFhlB2JYX075vTNZvnj4QSBcaeZqysV7jccZ0xOmEV9kTjefwwKaXXRAlho3XLGMR35vC8hxZciXvieXOUxxRS1vQx_QrIt/s16000/image.png" /></a></div><br />9. Click on OK button to save and close it<p></p><p>10. Restart Tomcat application server<br />10.1. Stop Tomcat: D:\SAP\SAP BusinessObjects\tomcat\bin><b>runas /user:administrator "net stop BOEXI40Tomcat"</b></p><p>Enter the password for administrator:</p><p>Attempting to start net stop BOEXI40Tomcat as user "win2019-tester1\administrator" ...</p><p>10.2. Start Tomcat: D:\SAP\SAP BusinessObjects\tomcat\bin><b>runas /user:administrator "net start BOEXI40Tomcat"</b></p><p>Enter the password for administrator:</p><p>Attempting to start net start BOEXI40Tomcat as user "win2019-tester1\administrator" ...</p><p>11. Verify the Java parameter is there by checking existing running tomcat9.exe. For this execution, the process ID (PID) is 10312 (Task Manager)</p><p>D:\SAP\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\sapjvm\bin><b>runas /user:administrator "jinfo 10312"</b><br />Attaching to process ID 10312, please wait...<br />Debugger attached successfully.<br />Server compiler detected.<br />JVM version is 8.1.061 10.0.2+000<br />Java System Properties:</p><p>java.vendor = SAP AG<br />org.apache.xml.security.ignoreLineBreaks = true<br />catalina.base = D:\SAP\SAP BusinessObjects\tomcat<br />sun.management.compiler = HotSpot 64-Bit Tiered Compilers<br />catalina.useNaming = true<br />........<br />...cut...<br />........<br /></p><p>VM Flags:<br />Non-default VM flags: -XX:CICompilerCount=2 -XX:CoreDumpType=null -XX:+EnableDebuggingOnDemand -XX:+HeapDumpOnOutOfMemoryError -XX:InitialHeapSize=268435456 -XX:MaxHeapSize=1073741824 -XX:MaxMetaspaceSize=402653184 -XX:MaxNewSize=357564416 -XX:MaxVMs=1 -XX:MinHeapDeltaBytes=524288 -XX:NewSize=89128960 -XX:NonNMethodCodeHeapSize=5828300 -XX:NonProfiledCodeHeapSize=131303578 -XX:OldSize=179306496 -XX:+PrintGCDetails -XX:-PrintGCID -XX:+PrintOldStyleGC -XX:+PrintOldStyleGCDetails -XX:ProfiledCodeHeapSize=131303578 -XX:+ReduceSignalUsage -XX:+SegmentedCodeCache -XX:ThreadStackSize=1024 -XX:-UseAOT -XX:+UseCompressedClassPointers -XX:+UseCompressedOops -XX:-UseLargePagesIndividualAllocation -XX:+UseParallelOldGC -XX:-UseSharedSessionStore</p><p>Command line: -Dcatalina.home=D:\SAP\SAP BusinessObjects\tomcat -Dcatalina.base=D:\SAP\SAP BusinessObjects\tomcat -Xrs -XX:MaxMetaspaceSize=384M -Djava.awt.headless=true -XX:+HeapDumpOnOutOfMemoryError -Xloggc:D:\SAP\SAP BusinessObjects\tomcat\logs\tomcat.gc.log -XX:+PrintGCDetails -XX:+UseParallelOldGC -Xloggc:d:\SAP\SAP BusinessObjects\tomcat\logs\tomcat.gc.log -Dfile.encoding=UTF-8 <b>-Dlog4j2.formatMsgNoLookups=true</b> exit abort -Xms256m -Xmx1024m -Xss1024k</p><p>12. Done. The new setting is activated. Test with vulnerability scanner to confirm, as well as fully tested the application functionality before deploy to PROD env</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-39466363670022223132021-12-07T16:07:00.003-05:002021-12-07T16:07:32.350-05:00Oracle: How to drop tablespace with missing file with ORA-29857<p>Product: Oracle DB</p><p>Version: 19.2 - 21c</p><p>This procedure illustrate how to drop a tablespace which has dbf files that missing, or corrupted.</p><p>Assumption:</p><p></p><ul style="text-align: left;"><li>Recycle bin is active</li><li>Several users has table and index within the affected tablespace</li><li>Entire tablespace will drop, even if one of the multiple dbf file missing</li><li>Tablespace name is ABC</li></ul><p></p><p>Problems simulation:</p><p>SQL> drop tablespace <b>ABC </b>including contents and datafiles;</p><p>ERROR at line 1:<br />ORA-29857: domain indexes and/or secondary objects exist in the tablespace</p><div>Workaround Procedure:</div><p>1. Find out all the dbf file ID of the tablespace. Note down the FILE_ID to use later</p><p>select file_id from dba_data_files where tablespace_name = '<b>ABC</b>';</p><p>2. Disable all the above dbf file. If multiple files, execute these once per file</p><p>alter database datafile [file_id] offline drop;</p><p>3. Disable recycle bin</p><p>alter system set recyclebin=off deferred;</p><p>4. Drop tablespace with all its dbf file</p><p>drop tablespace ABC including contents and datafiles;</p><p>5. Verify dbf file dropped. There should be no output</p><p>select file_id from dba_data_files where tablespace_name = '<b>ABC</b>';</p><p>6. Re-enable recycle bin</p><p>alter system set recyclebin=on deferred;</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-22269367503338464642021-10-12T10:18:00.003-04:002021-10-12T10:55:25.710-04:00Oracle/Tomcat - Monitoring ulimit for nofile (max open files) to Resolve max file open<p>Product: Any product that open files, such as Oracle DB, Tomcat<br />Version: Any version since using floppy disk</p><p>In all software that runs in Linux (including MacOS), a good vendors will include Linux kernel tuning parameters that their software used, or tested. In this post, I would like to discuss maximum number of concurrent files open parameter, which call following:</p><p></p><ul style="text-align: left;"><li>Linux (RedHat, Fedora, Ubuntu, CentOS): <b>nofile</b></li><li>MacOS: <b>maxfiles </b>(won't be discussed in this post)</li></ul><p></p><p>This configuration falls under responsibility of OS administrator & application administrator. For application administrators, they could be DBA, programmer, web admin, LDAP admin, SAP BODS admin, ETL developer, data migration consultant, Apache Parque, etc. The application team needs to tell OS admin how many max files the program will access (read or write) per process concurrently, while OS admin should monitor its usage, and alert application team when congested.</p><p>Linux has a 3 places to configure this per process parameter:</p><p></p><ul style="text-align: left;"><li>Global limit for entire OS/machine/VM - File <span style="background-color: white; border: 0px; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; font-weight: 600; margin: 0px; padding: 0px; vertical-align: baseline;">/etc/sysctl.conf</span><span style="background-color: white; border: 0px; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; margin: 0px; padding: 0px; vertical-align: baseline;">, parameter <b>fs.file-max</b>, verify current value </span>/proc/sys/fs/file-max, command to change it <span style="background-color: white; border: 0px; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; font-weight: 600; margin: 0px; padding: 0px; vertical-align: baseline;">sysctl -w fs.file-max=[new value]</span><span style="background-color: white; border: 0px; color: #323232; font-family: "IBM Plex Sans", "IBM Plex Mono"; font-size: 16px; margin: 0px; padding: 0px; vertical-align: baseline;">, monitor current usage using <b>cat /proc/sys/fs/file-nr</b></span></li><li>Per user, per process soft limit, the effective limit - File /etc/security/limits.conf, parameter "soft nofile," verify current value ulimit -Sn, verify current usage <b>cat /proc/<PID>/fd</b></li><li>Per user, per process hard limit, the value user can change to without asking SA - File /etc/security/limits.conf, parameter "hard nofile," verify current value ulimit -Hn, verify current usage <b>is not appliable</b> as process depends on soft limit</li></ul><div>Here are some clarification to prevent confusion:</div><div><ul style="text-align: left;"><li>"ulimit" command is per OS user, so it will display different value when login as different OS account</li><li>OS user can only manually increase (or automate using .bash_profile) the soft limit of nofile, but the hard limit (configured in /etc/security/limits.conf) must be changed by SA (whoever can sudo or modify file owned by root)</li><li>Network socket connection count towards nofile usage, in addition of file</li><li>Pipe file count towards nofile usage, and minimum there are 3 per process, i.e. standard input, standard error, standard output</li><li>File open/write by spawn thread counts toward the nofile usage of the process. If 2 threads are spawn by the process, each read/write 100 files concurrently, then total per process usage is 200 files access concurrently</li><li>The parameter is concurrent file open or write operation, and doesn't count files which are closed</li><li>DB always need to write to all dbf files (such as Oracle), plus additional log files, so total number of dbf file will be indirectly limited by OS nofile soft limit (required DB bounce to activate new value)</li><li>Vendors' nofile configuration is just for reference (unless its value is unlimited). Admin must adjust according to their concurrent file access usage</li><li>Direct raw storage usage, such as raw device used by Oracle RAC is counted, so the more raw device, the higher the concurrent nofile usage</li><li>Per process monitoring is most accurately by checking /proc/<PID/fd. ls command can display which file it is accessing (concurrently), netstat -anp| grep <PID> can display the network port it used, lsof -p <PID> can show pipe + file + network port</li><li>Extremely low value, or high usage could cause application unable to display "reached max file open" error, and causes it near impossible to troubleshoot, as OS doesn't capture historical value (when process crash/hang/misbehave)</li></ul><div>Example output of lsof for PID 25661 by showing entries related to nofile:</div></div><div><div>$ lsof -P -p 25661 | | awk '$4 ~ /[[:digit:]]/ {print}'</div><div><span style="font-family: courier;">COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME</span></div><div><div><span style="font-family: courier;">al_engine 25661 sap 0r FIFO 0,9 0t0 2188223 pipe</span></div><div><span style="font-family: courier;">al_engine 25661 sap 1w FIFO 0,9 0t0 2188224 pipe</span></div><div><span style="font-family: courier;">al_engine 25661 sap 2w FIFO 0,9 0t0 2188225 pipe</span></div><div><span style="font-family: courier;">al_engine 25661 sap 3r REG 259,1 637448 156135934 /opt/sap/dataservices/bin/BEError_message_en.bin</span></div><div><span style="font-family: courier;">al_engine 25661 sap 4r REG 259,1 5492 156135932 /opt/sap/dataservices/bin/broker_message_en.bin</span></div><div><span style="font-family: courier;">al_engine 25661 sap 5r REG 259,1 91966 156135931 /opt/sap/dataservices/bin/BETrace_message_en.bin</span></div><div><span style="font-family: courier;">al_engine 25661 sap 6u IPv4 2188262 0t0 TCP localhost:37482->localhost:4012 (ESTABLISHED)</span></div></div></div><div><br /></div><div>FD (nofile entry, which means File Descriptor) 0, 1 and 3 are standard input/output/error.</div><div>There is 1 TCP socket open</div><div>lsof -P is not to translate port number to application name, in this case port 4012 and port 37482</div><div>File type FIFO means pipe file</div><div>File type REG means regular file</div><div>File type IPv$ means TCP/IP ver 4</div><div>Command is the program that read/write/talk to these file/tcp/pipe/device</div><div><br /></div><div>Summary of useful command, or files<br /></div><p>
</p><table border="1">
<tbody><tr><td>Command</td><td>Purpose</td><td>Per Process</td>
</tr><tr><td>ulimit -Sn<span> </span></td><td>Shows default soft limit (effective value) of nofile for new process/command that is going to use</td><td>Y</td></tr>
<tr><td>ulimit -n</td><td>Same as "ulimit -Sn"</td><td>Y</td></tr>
<tr><td>grep file-max /etc/sysctl.conf</td><td>Checks current global max concurrent file for entire OS/VM. If not configured, that it won't return any value</td><td>N</td></tr>
<tr><td>sysctl fs.file-max</td><td>Same as /etc/sysctl.conf, but it will always return a value, even if not defined in /etc/sysctl.conf</td><td>N</td></tr>
<tr><td>sudo sysctl -w fs.file-max <new max value></td><td>Modify the global concurrent max file open</td><td>N</td></tr>
<tr><td>ls -l /proc/<PID>/fd</td><td>Display all file accessed by specific process</td><td>Y</td></tr>
<tr><td>grep "open files" /proc/<PID>/limits</td><td>Display effective max file open soft & hard limit by specific process. This override the ulimit command output, and cannot be changed unless restart the process (after changed ulimit)</td><td>Y</td></tr>
<tr><td>lsof -P -p <pid> | awk '$4 ~ /[[:digit:]]/ {print}'</td><td>More verbose output than /proc/<PID>/fd. It will display the file open for read/write, TCP port, block device name, pipe open for read/write</td><td>Y</td></tr>
<tr><td>wc -l /proc/<PID>/fd</td><td>Count nofile usage by specific process</td><td>Y</td></tr>
<tr><td>lsof -p <pid> | awk '$4 ~ /[[:digit:]]/ | wc -l</td><td>Same as wc -l above but based on lsof output</td><td>Y</td></tr>
</tbody></table>
<p></p>
SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-13350282949431262842021-09-10T16:58:00.000-04:002021-09-10T16:58:06.048-04:00Apache Tomcat: Connection Pool Manager - Confusion<p>Product: Apache Tomcat<br />Version: 7.0 or higher</p><p>Tomcat connection pool manager information in the internet is so confusing, even from Apache Tomcat documentation page itself.</p><p>This post is to highlight common miscommunication and understanding in the community.</p><p>Let's clarify about Apache Tomcat's build-in JNDI/JDBC DB connection pool manager.</p><p>Tomcat has build-in 2 connection pool manager:</p><p>1. org.apache.tomcat.jdbc.pool.DataSource - Tomcat preferred light-weight connection pool manager</p><p>2. org.apache.tomcat.dbcp.dbcp2.BasicDataSource</p><p>The configuration parameters </p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-70804472003984331602021-09-07T17:03:00.004-04:002021-09-08T17:40:55.722-04:00Apache Tomcat 7-9: JNDI JDBC Connection Pool Configuration<p>Product: Apache Tomcat<br />Version: 7.0 - 9.x</p><p>Since Apache Tomcat 7, the build-in JNDI/JDBC connection pool manager has 2 options:</p><p>1. Apache Common DBCP - class name is org.apache.tomcat.dbcp.dbcp2.BasicDataSource<br />2. Tomcat's light weight DBCP - recommended setting, and I will refer to it as non-DBCP. Class name is org.apache.tomcat.jdbc.pool.DataSource</p><p>Tomcat's jdbc-pool.html page documented various benefits to use Tomcat's light-weight DBCP connection pool manager in their homepage: https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html. In summary, you need to configure extra <Resource> parameter <span style="background-color: lightcyan; font-family: monospace;">org.apache.tomcat.jdbc.pool.DataSourceFactory</span> to switch to the light-weight driver, else it will be using DBCP.</p><p>In Tomcat's jndi-datasource-examples-howto.html homepage, it failed to document this additional factor=org.apache.tomcat.jdbc.pool.DataSourceFactory parameter, and many people are using Common DBCP connection pool manager.</p><p>This post going to give clear example how to configure both using MS SQL Server</p><h2 style="text-align: left;">Using Apache Common DBCP</h2><p>File: [Tomcat]/conf/<i>context.xml</i> (for global), [Tomcat]/webapps/[webapp]/META-INF/<i>context.xml</i> (for specific webapp)</p><p>Example: [Tomcat]/conf/context.xml</p><p><?xml version="1.0" encoding="UTF-8"?><br /><!-- The contents of this file will be loaded for each web application --></p><p><Context></p><p> <!-- Default set of monitored resources. If one of these changes, the --><br /> <!-- web application will be reloaded. --><br /> <WatchedResource>WEB-INF/web.xml</WatchedResource><br /> <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource><br /> <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource></p><p> <!-- Uncomment this to disable session persistence across Tomcat restarts --><br /> <Manager pathname="" /><span style="white-space: pre;"> </span><br /><span style="white-space: pre;"> </span><Resources cacheMaxSize="512000" /><br /> <Resource name="jdbc/pc1001DataSource" auth="Container"<br /> type="javax.sql.DataSource"<br /><span style="white-space: pre;"> </span> username = "[DB username]"<br /> password = "[DB user password"<br /> driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" <br /> url="jdbc:sqlserver://[MS SQL Server hostname]:1433;DatabaseName=pc1001"<br /> maxTotal="100"<br /><span style="white-space: pre;"> </span> validationQuery="select 1004"<br /><span style="white-space: pre;"> </span> connectionProperties="sendStringParametersAsUnicode=false"<br /> /></p><p></Context></p><p>Note: If using tomcat/conf/context.xml, then msjdbc.jar must resides in tomcat/lib directory, else Tomcat will complain ClassNotFound</p><h2 style="text-align: left;">Using Tomcat Non-DBCP Connection Pool</h2><p>File: [Tomcat]/conf/<i>context.xml</i> (for global), [Tomcat]/webapps/[webapp]/META-INF/<i>context.xml</i> (for specific webapp)</p><p>Example: [Tomcat]/conf/context.xml</p><p><?xml version="1.0" encoding="UTF-8"?><br /><!-- The contents of this file will be loaded for each web application --></p><p><Context></p><p> <!-- Default set of monitored resources. If one of these changes, the --><br /> <!-- web application will be reloaded. --><br /> <WatchedResource>WEB-INF/web.xml</WatchedResource><br /> <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource><br /> <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource></p><p> <!-- Uncomment this to disable session persistence across Tomcat restarts --><br /> <Manager pathname="" /><span style="white-space: pre;"> </span><br /><span style="white-space: pre;"> </span><Resources cacheMaxSize="512000" /><br /> <Resource name="jdbc/pc1001DataSource" auth="Container"<br /> type="javax.sql.DataSource"<br /><span style="white-space: pre;"> </span> <b>factory = "org.apache.tomcat.jdbc.pool.DataSourceFactory"<br /></b><span style="white-space: pre;"> </span> username = "[DB username]"<br /> password = "[DB user password"<br /> driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" <br /> url="jdbc:sqlserver://[MS SQL Server hostname]:1433;DatabaseName=pc1001"<br /> maxTotal="100"<br /><span style="white-space: pre;"> </span> validationQuery="select 1004"<br /><span style="white-space: pre;"> </span> connectionProperties="sendStringParametersAsUnicode=false"<br /> /></p><p></Context></p><p>Note: If using tomcat/conf/context.xml, then msjdbc.jar must resides in tomcat/lib directory, else Tomcat will complain ClassNotFound</p><h2 style="text-align: left;">Monitoring</h2><p>I compared the monitoring for both DBCP and non-DBCP connection pool manager using Tomcat 9.0 using Oracle Java 1.8.0_111 on Windows OS. JConsole.exe (Java Monitoring & Management Console) is capable of monitoring both connection pool manager through its "MBeans" tab.</p><p>When comparing how each to monitor both of them, I found that DBCP is updating "MBeans" faster and more accurate than non-DBCP. Non-DBCP sometimes won't show the detail of the connected connections, i.e. PooledConnect[array], for hours, in other word, it never update it.</p><p>The slow, delay, and inconsistency for non-DBCP connection pool make JConsole can't be reliabiliy used to monitor it via this JMX utility. I didn't write a program to directly grab the JDBC connection pool JMX metrics, which I suspect there could be a delay as well</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-91507090387260137362021-09-03T16:04:00.007-04:002021-09-08T10:42:27.592-04:00Apache Tomcat: Connection Pool Manager - Quick Config to Self Recover After DB Restart<p>Product: Apache Tomcat<br />Version: 7, 8, 9</p><p>According to following Apache Tomcat documentation, testOnBorrow is off by default:</p><p>* Tomcat 7: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html</p><p>* Tomcat 8.5: https://tomcat.apache.org/tomcat-8.5-doc/jdbc-pool.html</p><p>* Tomcat 9.0: https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html</p><p>However, this document mentioned following that is confusing on how many DB connection pool managers it provides, quote:</p><h4 style="text-align: left;">Section: Introduction</h4>
<p><span face=""Open Sans", sans-serif" style="font-size: 14px;">The </span><strong style="font-family: "Open Sans", sans-serif; font-size: 14px;">JDBC Connection Pool <code style="background-color: lightcyan; font-family: Consolas, monospace;">org.apache.tomcat.jdbc.pool</code></strong><span face=""Open Sans", sans-serif" style="font-size: 14px;"> is a replacement or an alternative to the </span><a href="https://commons.apache.org/dbcp/" style="font-family: "Open Sans", sans-serif; font-size: 14px;">Apache Commons DBCP</a><span face=""Open Sans", sans-serif" style="font-size: 14px;"> connection pool</span></p>
<h4 style="text-align: left;">Section: How to Use</h4>
<p><span face=""Open Sans", sans-serif" style="font-size: 14px;">Usage of the Tomcat connection pool has been made to be as simple as possible, for those of you that are familiar with commons-dbcp, the transition will be very simple. Moving from other connection pools is also fairly straight forward</span></p><h4 style="text-align: left;">Section: Inside The Apache Tomcat Container</h4>
<p><span face=""Open Sans", sans-serif" style="font-size: 14px;">The Tomcat Connection pool is configured as a resource described in </span><a href="http://tomcat.apache.org/tomcat-9.0-doc/jndi-datasource-examples-howto.html" style="font-family: "Open Sans", sans-serif; font-size: 14px;" target="_blank">The Tomcat JDBC documentation</a><span face=""Open Sans", sans-serif" style="font-size: 14px;"> With the only difference being that you have to specify the </span><code style="background-color: lightcyan; font-family: Consolas, monospace; font-size: 14px;">factory</code><span face=""Open Sans", sans-serif" style="font-size: 14px;"> attribute and set the value to </span><code style="background-color: lightcyan; font-family: Consolas, monospace; font-size: 14px;">org.apache.tomcat.jdbc.pool.DataSourceFactory</code></p>
<p>What does this means? Tomcat 7 onward offers 2 types of DB connection pool manager that you can choose:</p><p>1. <b>Apache Common DBCP</b> - full feature and heavy duty connection pool manager. For its default configuration values' documentation, refers to https://commons.apache.org/proper/commons-dbcp/configuration.html, but not Tomcat's documentation. Example of [Tomcat]\conf\context.xml"</p><pre style="background-color: #eff8ff; border-radius: 5px; border: 1px solid rgb(153, 153, 153); display: table; font-family: Consolas, monospace; font-size: 14px; margin-bottom: 0px; margin-top: 0px; padding: 4px;"><code style="background-color: transparent; font-family: Consolas, monospace;"><Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
username="root"
password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mysql"/></code></pre><p>2. <strong style="font-family: "Open Sans", sans-serif; font-size: 14px;"><code style="background-color: lightcyan; font-family: Consolas, monospace;">org.apache.tomcat.jdbc.pool</code></strong> - Lightweight DB connection pool manager. context.xml will has additional <i>factory=org.apache.tomcat.jdbc.pool.DataSourceFactory</i> parameter. Example of [Tomcat]\conf\context.xml:</p><pre style="background-color: #eff8ff; border-radius: 5px; border: 1px solid rgb(153, 153, 153); display: table; font-family: Consolas, monospace; font-size: 14px; margin-bottom: 0px; margin-top: 0px; padding: 4px;"><code style="background-color: transparent; font-family: Consolas, monospace;"><Resource name="jdbc/TestDB"
auth="Container"
type="javax.sql.DataSource"
<b>factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"</b>
username="root"
password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mysql"/></code></pre><p>A big different between them is that the default value for <b>testOnBorrow </b>is different, so DBCP will drop broken DB connections, while org.apache.tomcat.jdbc.pool.DataSourceFactory won't:</p><p>1. Apache Common DBCP - testOnBorrow=true<br />2. Tomcat org.apache.tomcat.jdbc.pool.DataSourceFactory - testOnBorrow=false</p><p>Out of curiosity, I spin up Tomcat 9.0.21 without specifying "factory" parameter, and used jconsole.exe (Java Console) to check folder "Catalina - DataSource" detail in MBeans tab as shown below:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZJTVJN_YA9boga1ySudbcsE1t0eOIfYQdnHzNNdrrmBJXgD5yESPicCr-aZpXnDiQN0T2Sf37Yt2mzyZ7N77WlOh1Kum4KdM1RjrHrY7uEq0FdeoS8IX9IMqAX6JZhZ7hSIcWxcD0td80/" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="361" data-original-width="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZJTVJN_YA9boga1ySudbcsE1t0eOIfYQdnHzNNdrrmBJXgD5yESPicCr-aZpXnDiQN0T2Sf37Yt2mzyZ7N77WlOh1Kum4KdM1RjrHrY7uEq0FdeoS8IX9IMqAX6JZhZ7hSIcWxcD0td80/s16000/image.png" /></a></div><div class="separator" style="clear: both; text-align: left;">Clicking on "Attributes" menu on the left, and I can see the actual value of the JNDI/JDBC data source configuration showing testOnBorrow=true by default:</div><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQK0r95yT-2tyL10J_KU5Fo8fUHNGW_LvJnV_qRhTuvAx-P-3LBVTlZlytKw4DGoTdH1xf5Nmg6IaukCa30uYZITUCUdpUbQx-Dc5vY8dOdJgruNYy-TOfRvdz8CJqccSDpCS-emuJp0dk/" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="354" data-original-width="616" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQK0r95yT-2tyL10J_KU5Fo8fUHNGW_LvJnV_qRhTuvAx-P-3LBVTlZlytKw4DGoTdH1xf5Nmg6IaukCa30uYZITUCUdpUbQx-Dc5vY8dOdJgruNYy-TOfRvdz8CJqccSDpCS-emuJp0dk/s16000/image.png" /></a></div></div><br />In other word, to configure testOnBorrow in Apache Tomcat using its build-in DBCP JNDI/JDBC connection pool data source, you just need to configure parameter validationQuery into [Tomcat]/conf/context.xml or [Tomcat]/webapp/[app]/META-INF/context.xml <Resource> xml tag.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">If you used org.apache.tomcat.jdbc.pool.DataSourceFactory, then jconsole.exe will display additional folder "tomcat.jdbc" at the bottom of MBeans tab as shown below:</div><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGlexh0-97Yu0Ruc5rX3wVwqo3BpMljVwIcgZ4mkADib81iCM6CYuy8jd9GsTHG51RcIgKqBc6lY7BYbtwsEXp-2XlauQNKporvOgR9raNFavmzTrdhLyiSYx-3d7VJtyXubjaP8p7P7ZQ/s534/Image+534.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGlexh0-97Yu0Ruc5rX3wVwqo3BpMljVwIcgZ4mkADib81iCM6CYuy8jd9GsTHG51RcIgKqBc6lY7BYbtwsEXp-2XlauQNKporvOgR9raNFavmzTrdhLyiSYx-3d7VJtyXubjaP8p7P7ZQ/s16000/Image+534.png" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div></div><p></p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-23377607558659888302021-09-01T18:17:00.009-04:002021-09-08T17:23:23.677-04:00Apache Tomcat: Connection Pool Manager - Self Recover After DB Restart<p>Product: Apache Tomcat<br />Version: 6.x - 9.x<br />Ref: https://commons.apache.org/proper/commons-dbcp/configuration.html</p><p>Apache Tomcat 6 and earlier bundled with DBCP 1.3 connection pool manager for many years, which is readily to use by J2EE application as JNDI/DBCP connection pool manager. Tomcat 7.x onward start using its own lighter <strong style="background-color: white; border-collapse: separate;"><code style="background-color: lightcyan; border-collapse: separate; padding: 0px 0.1em;">org.apache.tomcat.jdbc.pool</code></strong> connection pool manager (DBCP2 JAR is still bundled), but continue to use the same configuration parameters as DBCP.</p><p>This post going to discuss about non-DBCP connection pool manager, which is <strong style="background-color: white; border-collapse: separate;"><code style="background-color: lightcyan; border-collapse: separate; padding: 0px 0.1em;">org.apache.tomcat.jdbc.pool</code></strong>.</p><p>One of the big challenge for many J2EE developers who use this connection pool manager assumes that their application will always get a good working DB connection from the connection pool manager when the DB is bounced. In reality, they see that their Tomcat application will need to restart after DB maintenance (restart). Many developers or administrators assume that, by default, the connection pool will auto close and open new DB connection when DB restarted, but it is not. Some think that Tomcat will close them if let it idle for some time, but it won't close the broken connections.</p><p>This post is going to discuss several configurations that can configure connection pool manager to close existing broken DB connections and create new DB connection if DBA/anyone bounce the DB.</p><p>Among Apache Tomcat version in last 20+ years, it has connection pool parameters that can be configured <u>without </u>bouncing Tomcat (whenever DB bounced, e.g. Windows OS Update/patch). Let's start with the simplified state diagram that I drawn by lookup Apache 9's connection pool manager. It is more involving to create the UML state machine diagram which will be best, as I don't have any tool handy. I tried to add as many Tomcat parameter as much into the diagram, yet to keep its size small, so it certainly won't contain the entire lifecycle of a connections in the pool</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxqM_-SOo5jhg_U-PyHqcdxAeh1seXAB79OnD-2p7-IIR2Yc02rEvFRue5yr5kYrfzWysLTKltF9MPAq9JPAgXOjdQovVrzOz8SmsYUoPxR7dAME7iB8VNBQs8DAeI_kj93IyGGpm8BHDt/s685/Tomcat.ConnectionPool.State.Diagram.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="414" data-original-width="685" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxqM_-SOo5jhg_U-PyHqcdxAeh1seXAB79OnD-2p7-IIR2Yc02rEvFRue5yr5kYrfzWysLTKltF9MPAq9JPAgXOjdQovVrzOz8SmsYUoPxR7dAME7iB8VNBQs8DAeI_kj93IyGGpm8BHDt/s16000/Tomcat.ConnectionPool.State.Diagram.png" /></a></div><br /><p>Note: Above diagram doesn't show states involving opening new connections from DB, which could experience long login, invalid password, account locked, remote DB down. On failure, it will not auto login to DB, if you are interested to know</p><p>Following are the parameters related to testing and dropping DB connections in the pool</p><p>1. <i>testOnBorrow </i>- Immediately re-connect all DB connections. Application will not see broken DB connections, and DB bounce is most likely transparent to the application. Default off</p><p>2. <i>testWhileIdle </i>+ <i>minEvictableIdleTimeMillis</i> - When connections in the pool meet long idle condition as defined by <i>minEvictableIdleTimeMillis</i> , it will drop all (Tomcat 7+) existing broken long idle DB connections. Default off</p><p>3. <i>testOnReturn </i>- Continue to borrow broken DB connection to application, but immediately drop it if it is a broken DB connection. Application end will complain DB connection broken in its log, and not able to perform any DB operation. Application will not retry automatically. Default off</p><p>Following table illustrate more detail about its behavior:</p><p>
</p><table border="1">
<tbody><tr>
<td>#
</td>
<td>Method
</td>
<td>Application Will See Broken DB Conn Error?
</td>
<td>How Quickly Close</td>
<td>Close One-by-One
</td>
<td>Close in Chunk
</td>
<td>CPU Usage
</td>
</tr>
<tr>
<td>1
</td>
<td>testOnBorrow</td>
<td>No, if DB is up</td>
<td>Immediate, and open new DB connections</td>
<td>Yes</td>
<td>No</td>
<td>High</td>
</tr>
<tr>
<td>2</td>
<td>testWhileIdle</td>
<td>Yes</td>
<td>Controlled by:<br />timeBetweenEvictionRunsMillis (5 sec)<br />minEvictableIdleTimeMillis (60 sec)<br />maxIdle (same as maxActive, i.e. 100)<br />minIdle (same as initialSize, i.e. 10)</td>
<td>No</td>
<td>Yes. Number of connection will be closed equals to value of numTestsPerEvictionRun
</td>
<td>Low</td>
</tr>
<tr>
<td>3
</td>
<td>testOnReturn</td>
<td>Yes</td>
<td>Immediate
</td>
<td>Yes</td>
<td>No</td>
<td>Medium</td>
</tr>
</tbody></table>
<br /><p></p><p>Note: for <i>testWhileIdle </i>setting, parameter <i>numTestsPerEvictionRun </i>is not used by Tomcat 7 onward (non-DBCP), so it will always closed <b>all </b>broken idle connections</p><p>For any of the above self recovered connection pool configuration, parameter "<i>validationQuery</i>" must specify a SELECT statement that is quick, and depends on DB type. For example:</p><p>1. Oracle - select 1 from dual<br />2. MS SQL Server - select 1</p><p>I have encountered DB that could be slow to execute above SQL, and causing no available connections to borrow to the application, and stalled. So developer/administrator must test running above SQL in series + 1000 integration or more to measure the time taken as precaution.</p><p>If no SELECT statement is specified, DBCP won't auto close existing broken DB connections, and open a new one.</p><p><i>testOnBorrow </i>and <i>testWhileIdle </i>are the most commonly configuration as there are generally 2 groups of people who favor high or low CPU resource.</p><p>For those who configure <i>testWhileIdle</i>, there are additional configuration to adjust it to the taste of the administrator to reduce CPU usage:</p><p>1. <i>minIdle </i>- default 10, which follows <span style="background-color: lightcyan; font-family: Consolas, monospace; font-size: 14px;">initialSize</span><br />2. <i>maxIdle </i>- default 100, which follows <span style="background-color: lightcyan; font-family: Consolas, monospace; font-size: 14px;">maxActive</span><br />3. <i>numTestsPerEvictionRun </i>- used by Tomcat 6 or older. Tomcat 7+ doesn't has this setting<br />4. <i>minEvictableIdleTimeMillis</i> - default 60 sec. The criteria for the DB connections in the connection pool to be considered "idle too long" and will be dropped, regardless they are broken or valid. If connection not broken, then the connection could remains in the pool by keeping as many as 100 (minIdle)<br />5. <i>timeBetweenEvictionRunsMillis </i>- default 5 sec. Eviction frequency (cycle or interval) that will drop broken DB connections in the connection pool</p><p>Tomcat 6 and older uses DBCP 1.3, which has additional <i>numTestsPerEvictionRun </i>to slowly drops long idle connections (to reduces CPU usage), so following are additional considerations for various versions:</p><p>1. How many broken connections in the pool that would like to close each time: Tomcat 7 will close <b>all</b> broken idle connections. <i>numTestsPerEvictionRun </i>(default 3 by DBCP 1.3 doc) is not used by Tomcat 7+, while Tomcat 6 or older will close 3 connections during each eviction cycle.<br />1.1. If application opened 1,000 DB connections when DB bounced, then there will be 1,000 broken DB connections in the connection pool. This Tomcat 6 parameter means each round it will close 3 connections (default), and will take ~ 1,000 / 3 = 333 rounds to close all broken connections in the pool. For Tomcat 7+, it will close all 1000 broken connections (not closed 9990 and keeps 10 per minIdle)<br />1.2. A lower value will use less CPU as it close less broken connection each eviction round of eviction cycle<br />1.3. A higher value will use more CPU, so Tomcat 7+ will always use more CPU to close all broken connections<br />1.4. Broken connections get close one-by-one, even though it is in a batch as this is done by 1 Java eviction thread</p><p>2. How long a connection will be considered as "long idle" in order to close them: <i>minEvictableIdleTimeMillis</i>, default is 60 sec<br />2.1. Broken connection must be sitting in the connection pool without borrow to the application, and no user should use the application, or schedule any job, incoming web service call, background process, that will borrow those broken connection<br />2.2. If the broken connection not used for more or equals to 30 min, then it will be consider idle, and will pick up (per <i>numTestsPerEvictionRun </i>limit for Tomcat 6 or older, which is 3 idle connections only) by eviction thread to close the broken + idle connection<br />2.3. Even if a connection is idle more than 30 min (default), if in between the eviction cycle, the broken idle connection borrowed to application (due to batch process, background, or user trigger), then it will needs to wait for 30 min to be considered "idle." Sometimes, it will never idle as the broken connections keep borrow to the application due to user activity<br />2.4. A lower value will make broken connection to meet "idle" status sooner, but will use higher CPU<br />2.5. If a connection is idle for 29 min, borrowed to application for 1 sec (error out due to broken DB connection), idle for 29 min, borrow to app for 1 sec, and repeat 24 hr, then that specific connection will not consider "idle." This could be due to schedule batch process that fired every 15 min, or multiple that scheduled every 30 min (overlapping, so effectively < 30 min)</p><p>3. How frequent the eviction cycle will run and start closing idle (broken in this case) connections: <i>timeBetweenEvictionRunsMillis</i>, default 5 sec<br />3.1. A lower value will use more CPU, but will close broken connections that meet "long idle" status sooner (limit to 3 connections each round - numTestsPerEvictionRun). If numTestsPerEvictionRun is 3, the CPU usage will be lower, but if numTestsPerEvictionRun is very high, and this cycle interval is high, then CPU usage will be very high<br />3.2. A higher cycle interval will close broken idle connections slower, even after they are in "idle" mode<br />3.3. If eviction cycle frequency (<i>timeBetweenEvictionRunsMillis</i>) is every 1 min (not applicable for Tomcat 7+) with default to close 3 connections (<i>numTestsPerEvictionRun</i>), then for 1000 broken DB connections, it will take 1000/3 * 1 min = 333 min (5 hr 33 sec) to close all<br />3.4. If eviction cycle is every 1 min with numTestsPerEvictionRun=100, to close 1000 broken DB connection, it will take 1000/100 * 1min = 10 min<br />3.5. If eviction cycle is every 5 sec with numTestsPerEvictionRun=10, to close 1000 broken DB connection, it will take 1000/10 * 5 sec = 500 sec (8 min 20 sec)<br />3.6. If eviction cycle is every 1 sec with numTestsPerEvictionRun=20, to close 1000 broken DB connection, it will take 1000/20 * 1 sec = 50 sec<br /></p><p>Last warning is that even Tomcat DBCP2 or non-DBCP connection pool manager can self recovered by closing all the broken DB connections (either immediate, or with delay), this doesn't mean the J2EE application functionality will self recovered without human intervention. For example application functionality that won't self recovered:</p><p>1. Schedule job that failed to run earlier will not run again<br />2. Actively running schedule job will not self recovered after it abort when DB bounced<br />3. Background messaging/JMS might not self brought up<br />5. Incoming web services that failed due to loosing of DB connection will need to trigger again from remote server<br />6. End user might needs to login again and lost the information they entered into the screen</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0Toronto, ON, Canada43.653226 -79.383184315.342992163821151 -114.5394343 71.963459836178842 -44.226934299999996tag:blogger.com,1999:blog-5829191550601027073.post-33400213249838779612021-08-24T16:14:00.004-04:002021-08-24T16:14:22.474-04:00Oracle: Advance Shrink UNDO Tablespace<p>Product: Oracle RDBMS<br />Version: 9.0.x - 19.3 (could be higher)<br />OS: Windows, Linux</p><p>Locally managed UNDO tablespace has been introduced about 20 yr ago in Oracle 9i. Oracle's documentation (KB) about shrinking it is never actually shrinking it, but replacing it with another tablespace with following steps:</p><p>1. Create 2nd new undo tablespace, e.g. UNDOTBS2<br />2. Set init parameter undo_tablespace=UNDOTBS2, the 2nd new undo tablespace above<br />3. Bounce DB<br />4. Drop old UNDOTBS1 tablespace, including its dbf files</p><p>This post going to go into more advance technique to drop existing automatically created rollback segments, and shrink the original UNDOTBS1 tablespace. This step will drop all rollback segments that automatically got created, followed by shrinking the files. As UNDOTBS1 tablespace is clean, we can shrink the dbf file. This procedure will be using hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS" as without it, we can't drop offline rollback segments that auto created.</p><p>Preparation:</p><p></p><ol style="text-align: left;"><li>Ensure DB can be safely brought down for maintenance, as this will require to bounce the DB few times</li><li>Make a backup of spfile[SID].ora in $ORACLE_HOME/dbs/ directory. The maintenance will modify it, and backup is the fastest way to revert back to original setting</li></ol><div>Steps:</div><div>1. List down all rollback segments in tablespace UNDOTBS1:</div><div>SELECT segment_name FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';</div><div><br /></div><div>2. Creates a statement to set hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS" like below with maximum of 255 characters long:</div><div><br /></div><div>alter system set "_OFFLINE_ROLLBACK_SEGMENTS" = '_SYSSMU1_2270612333$,_SYSSMU2_1117599206$,_SYSSMU3_2866369812$' <b>scope=spfile</b>;</div><div><br /></div><div>3. Shutdown immediate</div><p></p><p></p><div>4. Startup</div><div><br /></div><div>5. Generates DROP ROLLBACK SEGMENT statement dynamically:</div><div>SELECT 'drop rollback segment "'||segment_name|| '";' FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';</div><p></p><div>6. Run above generate DROP ROLLBACK SEGMENT to drop all segments that listed in Step 2 above</div><div><br /></div><div>7. There will be only some UNDO segments that we configured in Step 2 above that we can drop. In order to drop more, repeat step #1 (to get updated list) to 6</div><div><br /></div><div>8. Double check only 1 rollback segments in UNTOTBS tablespace left, which is auto created</div><p>SELECT segment_name FROM dba_rollback_segs where tablespace_name = 'UNDOTBS1';</p><div>9. If there is more, then repeat Step 1 - 6</div><div><br /></div><div>10. Shrink UNTOTBS1 dbf file, in this example, it is file ID 3, and shrinks it to 100MB</div><div><br /></div><div><div>select tablespace_name, file_id, file_name, bytes/1024/1024 mb, maxbytes/1024/1024 mb_max from dba_data_files where tablespace_name = 'UNDOTBS1';</div><div>alter database datafile 3 resize 100m;</div></div><div><br /></div><div>11. Optionally set its maxsize to 1000 MB</div><div><br /></div><div>alter database datafile 3 autoextend on maxsize 1000m;</div><div><br /></div><div>12. Shutdown, and restore original spfile.ora. This will indirectly remove the hidden parameter "_OFFLINE_ROLLBACK_SEGMENTS"</div><div><br /></div><div>shutdown immediate</div><div>cp -p $ORACLE_HOME/dbs/spfile[SID].ora.org </div><div><br /></div><div>13. Startup</div><div>14. Now DB will auto creates 1-2 new undo rollback segments in UNDOTBS1 tablespace</div><div><br /></div><div><div>SELECT segment_id, segment_name, tablespace_name, status, next_extent, max_extents</div><div>FROM dba_rollback_segs;</div></div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-66714406531316909142021-06-17T17:35:00.004-04:002021-06-17T18:15:52.010-04:00MS SQL Server - Create OLE DB Connection File<p>Product: MS SQL Server<br />Version: All</p><p>I am surprise to find that there is not much information in the Internet to create an OLE DB connection file in Windows. Most of the information are creating OLE DB connection through software such as SSIS, Excel, DB utilities.</p><p>Following is the process to create OLE DB connection file, which can be used to specify the desire MS SQL Server OLE DB version:</p><p></p><ol style="text-align: left;"><li>Open Windows Explorer and navigate to the desire directory to create the OLE DB connection file</li><li>In Windows Explorer, right click on the empty area, and pick New > Text Document</li><li>Enter desire connection file name, such as "test1.udl"</li><li>Now you will see the icon for "test1.udl" as shown below<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgv9JW4dZaRccEPhvMUM_WDHI6ShaTqj6hB-lSJsv9VTJN0mWX9068bz8FCI1dAJ-ltcl41-P9-EfE62hqO9xj13VDoBozXBfKXyoYmMUfVQQsT1wouQbdcV65T5sG5dWIN4xj5tU-bLYuY/s129/test1-udl.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="129" data-original-width="107" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgv9JW4dZaRccEPhvMUM_WDHI6ShaTqj6hB-lSJsv9VTJN0mWX9068bz8FCI1dAJ-ltcl41-P9-EfE62hqO9xj13VDoBozXBfKXyoYmMUfVQQsT1wouQbdcV65T5sG5dWIN4xj5tU-bLYuY/s0/test1-udl.png" /></a></div><br /></li><li>Open up the properties screen by doing Alt-Double-Click to see the pop up window below<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2R4nb9zgpHBQ3QhO-dtyCJy6ivfRhI156I3ohdM4_f0xZLN5XUBuwhJC2ShT63WOa4wouJQOH2JMx4LBsHCQlAeDyyctowe0Fqori-VpzU7L4IeeF9rD60kJPlqpfkvqRFa1Cn7AJoLXp/s527/test1-udl-prop.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="527" data-original-width="363" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2R4nb9zgpHBQ3QhO-dtyCJy6ivfRhI156I3ohdM4_f0xZLN5XUBuwhJC2ShT63WOa4wouJQOH2JMx4LBsHCQlAeDyyctowe0Fqori-VpzU7L4IeeF9rD60kJPlqpfkvqRFa1Cn7AJoLXp/s320/test1-udl-prop.png" /></a></div><br /></li><li>Click on tab "Connection"<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL-pmEkElE2ytJ-Lqo0EYjSU3rYk1P31acnBgttbu6r57tAnEmIMmaHJ0EHyQtPWTwZH8Shmf-a5WpZhzzfY_ICHau7s6O1lE2R1F7a99hf56KFEg8uuDAf0y9fiVdKvkoEU0xuaS9OCq7/s527/test1-udl-conn.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="527" data-original-width="363" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL-pmEkElE2ytJ-Lqo0EYjSU3rYk1P31acnBgttbu6r57tAnEmIMmaHJ0EHyQtPWTwZH8Shmf-a5WpZhzzfY_ICHau7s6O1lE2R1F7a99hf56KFEg8uuDAf0y9fiVdKvkoEU0xuaS9OCq7/s320/test1-udl-conn.png" /></a></div><br /></li><li>Enter the DB configuration values</li><ol><li>Server name and port, separate by comma</li><li>Choose Windows integrated authentication or DB</li><li>For DB authentication, fill in username and password</li><li>Choose whether want to save password, if Windows Group Policy allow by your company's ActiveDirectory server</li><li>Select the database name to use after login</li></ol><li>After configured, click on button "Test Connection" to confirm that the machine can login to remote MS SQL Server</li><li>You can further pick your desire OLE DB driver from the "Provider" tab<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKcvVIx1w_DikD2STIN1yNmiRloETrtoNqOV8Avb5W4o7EqV4xhbqdjKbbVTtdRq17f6Ee5UegfyOnfNmhKPyOC0H2X57e_RTWiXhz2t4hXqct-fgTNQy9Hx14l1lMIQ95ntgaGcxD87OB/s534/test1-udl-provider.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="376" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKcvVIx1w_DikD2STIN1yNmiRloETrtoNqOV8Avb5W4o7EqV4xhbqdjKbbVTtdRq17f6Ee5UegfyOnfNmhKPyOC0H2X57e_RTWiXhz2t4hXqct-fgTNQy9Hx14l1lMIQ95ntgaGcxD87OB/s320/test1-udl-provider.png" /></a></div><br /></li></ol><p></p><p><br /></p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-79156993933231135452021-04-09T12:20:00.006-04:002021-04-09T12:41:34.385-04:00Oracle - Startup Failure<p>Product: Oracle RDBMS<br />Version: 10.2 - 19.2<br />OS: Linux</p><p>While helping others to troubleshoot Linux systemd in auto startup Oracle RDBMS, encountering following failure when trying to login as SYSTEM (or any application DB user) in the Oracle server OS itself:</p><div class="line number1 index0 alt2" style="background: none white; border-radius: 0px; border: 0px; box-shadow: none; box-sizing: content-box; direction: ltr; float: none; height: auto; inset: auto; line-height: 1.1em; margin: 0px; outline: 0px; overflow: visible; padding: 0px 1em; position: static; vertical-align: baseline; width: auto;"><span style="font-family: Monaco, Consolas, Bitstream Vera Sans Mono, Courier New, Courier, monospace;"><span style="font-size: 12px; white-space: pre;">[oracle]$ sqlplus system/non_default_password
SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 9 13:59:13 2021
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 500 (oracle), current egid =
501 (dba)</span></span><span style="font-family: Monaco, Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace; font-size: 12px; white-space: pre;">
</span></div><div><br /></div><p>However, if shutdown, then startup Oracle daemon manually, SYSTEM and other application DB user can login without issue.</p><p>Diagnostic:</p><p>1. Checks the OS username and group name for $ORACLE_HOME/bin/oracle</p><p>$ ls -ld $ORACLE_HOME/bin/oracle</p><p>-rwxr-x---. 1 <b>oracle dba </b>323762270 Mar 24 19:34 /dbf/ora12.1/bin/oracle</p><div>2. Note down the username, and group name, which is oracle and dba</div><div>3. Checks the current running Oracle PMON daemon (it can be any other Oracle daemon) by displaying the OS user and OS group:</div><div><div>$ ps -eo euser,egroup,cmd | grep -i ora_pmon_$ORACLE_SID</div><div><b>oracle</b> <b>oinstall</b> ora_pmon_SUPP121</div></div><div>4. Compare the output of ls and ps to ensure that both <u>user </u>& <u>group </u>are identical</div><div>5. Typically, the custom auto startup script has wrong OS user group name</div><p>6. For example, systemd script could have following wrong entry:</p><p>Group=oracle</p><p>7. Changes above to the OS user group as shown in #1, which is "dba"</p><h2 style="text-align: left;">Advance Diagnostic</h2><p>Oracle daemons allocate "shared memory segment," and "semaphore arrays" in Linux (UNIX in general), which has OS user group which leads to this login issue:</p><p>1. Checks "shared memory segment" and "semaphore arrays" allocated to specific ORACLE_SID</p><div>$ sysresv -l RMANCAT<br />IPC Resources for ORACLE_SID "RMANCAT" :<br />Maximum shared memory segment size (shmmax): 18446744073692774399 bytes<br />Total system shared memory (shmall): 18446744004990070784 bytes<br />Total system shared memory count (shmmni): 4096<br />*********************** Dumping ipcs output ********************<br />------ Message Queues --------<br />key msqid owner perms used-bytes messages<br />------ Shared Memory Segments --------<br />key shmid owner perms bytes nattch status<br />0x00000000 655360 oracle 640 4096 0<br />0x00000000 688129 oracle 640 4096 0<br />0x00000000 720898 oracle 640 4096 0<br />0xd787ced8 753667 oracle 640 16384 70<br />------ Semaphore Arrays --------<br />key semid owner perms nsems<br />0x720acc58 <b>98304</b> oracle 640 125<br />0x720acc59 131073 oracle 640 125<br />0x720acc5a 163842 oracle 640 125<br />0x720acc5b 196611 oracle 640 125<br />0x720acc5c 229380 oracle 640 125<br />0x720acc5d 262149 oracle 640 125<br />0x720acc5e 294918 oracle 640 125<br />0x720acc5f 327687 oracle 640 125<br />0x720acc60 360456 oracle 640 125<br />*********************** End of ipcs command dump **************<br /><br />***************** Dumping Resource Limits(s/h) *****************<br />core file size 0 KB/0 KB<br />data seg size UNLIMITED/UNLIMITED<br />scheduling priority 0 KB/0 KB<br />file size UNLIMITED/UNLIMITED<br />pending signals 30 KB/30 KB<br />max locked memory UNLIMITED/UNLIMITED<br />max memory size UNLIMITED/UNLIMITED<br />open files 64 KB/64 KB<br />POSIX message queues 800 KB/800 KB<br />real-time priority 0 KB/0 KB<br />stack size 10 MB/10 MB<br />cpu time UNLIMITED/UNLIMITED<br />max user processes 16 KB/16 KB<br />virtual memory UNLIMITED/UNLIMITED<br />file locks UNLIMITED/UNLIMITED<br />***************** End of Resource Limits Dump ******************<br />Total /dev/shm size: 14524801024 bytes, used: 2097008640 bytes<br />Shared Memory:<br />ID KEY<br />688129 0x00000000<br />720898 0x00000000<br />655360 0x00000000<br />753667 0xd787ced8<br /><b>Semaphores</b>:<br />ID KEY<br /><b>98304</b> 0x720acc58<br />131073 0x720acc59<br />163842 0x720acc5a<br />196611 0x720acc5b<br />229380 0x720acc5c<br />262149 0x720acc5d<br />294918 0x720acc5e<br />327687 0x720acc5f<br />360456 0x720acc60<br />Oracle Instance alive for sid "RMANCAT"</div><div style="text-align: left;"><br /></div><div style="text-align: left;">2. Let's pick the first semaphore array ID of 98304 to find out the OS user group using ipcs command:</div><div style="text-align: left;"><br /></div><div style="text-align: left;"><div><div>$ ipcs -si 98304 | head -20</div><div><br /></div><div>Semaphore Array semid=98304</div><div>uid=500 <b>gid=501</b> cuid=500 cgid=501</div><div>mode=0640, access_perms=0640</div><div>nsems = 125</div><div>otime = Fri Apr 9 16:34:50 2021</div><div>ctime = Fri Apr 9 16:34:50 2021</div><div>semnum value ncount zcount pid</div><div>0 1 0 0 3252</div><div>1 25427 0 0 3252</div><div>2 4619 0 0 3252</div><div>3 32764 0 0 3252</div><div>4 0 0 0 0</div><div>5 0 0 0 0</div><div>6 0 1 0 3307</div><div>7 0 1 0 3309</div><div>8 0 0 0 0</div><div>9 0 1 0 3318</div><div>10 0 1 0 3320</div><div>11 0 1 0 3376</div></div><div><br /></div><div>3. The "gid" of the semaphore array must match the ORACLE_HOME/bin/oracle program name. Uses following command to show the file owner in gid. Second column is the gid, which is 501:</div><div><br /></div><div><div>[oracle]$ ls -ldn $ORACLE_HOME/bin/oracle</div></div><div>-rwxr-x---. 1 500 <b>501</b> 323762270 Mar 24 19:34 /dbf/ora12.1/bin/oracle</div><div><br /></div><div>4. This value is configured in /etc/passwd and /etc/group file in UNIX</div></div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-53899455391266174442021-03-31T04:44:00.003-04:002021-03-31T04:44:25.149-04:00iOS 14.2.2 Upgrade Failed - Failed to get location ID from booted OS device<p> iOS 14.0.0 Upgrade Failure to 14.2.2</p><p>Used iTunes 12.11.0.26 to upgrade iPhone 12 Pro Max with 14.0.0, and password enabled, but failed with following error:</p><div style="text-align: left;">2021-03-30 11:32:01.322 [14164:3774]: restore library built Sep 24 2020 at 20:03:35<br />2021-03-30 11:32:01.322 [14164:3774]: iTunes: iTunes 12.11.0.26<br />2021-03-30 11:32:01.327 [14164:3774]: iTunes: Current software version: 18D52<br />2021-03-30 11:32:01.328 [14164:3774]: iTunes: Apple Mobile Device version: 1253.12.2.100.3<br />2021-03-30 11:32:01.328 [14164:3774]: iTunes: Software payload version: 18D70<br />2021-03-30 11:32:01.328 [14164:3774]: iTunes: Using MobileRestore state machine<br />[15:33:43.0726] Failed to get location ID from booted OS device<br />[15:33:43.0726] Proxy returned empty location ID<br />[15:33:43.0786] =====================================================<br />[15:33:43.0786] Device info:<br />[15:33:43.0786] <span style="white-space: pre;"> </span>board configuration: D54PAP<br />[15:33:43.0786] <span style="white-space: pre;"> </span>Board ID: 0x8<br />[15:33:43.0786] <span style="white-space: pre;"> </span>Chip ID: 0x8101</div><div style="text-align: left;">...</div><div style="text-align: left;">...cut...</div><div style="text-align: left;">...</div><div style="text-align: left;"><div>[15:33:52.0262] amai: BbfwWriterAddFile: Added bbfw file : bbcfg.mbn</div><div>[15:33:52.0262] amai: BbfwWriterAddFile: Added bbfw file : b15ce16628a3f5b94d31d9a91794cdba9ea6c5f98fe03a2d6ff4b652f624be8e/update/main/profile.bin</div><div>[15:33:52.0319] amai: _AMAuthInstallBundleInstallPersonalizedEntry: entry "OS" does not require personalization; skipping it</div><div>[15:33:52.0750] AMDeviceCopyFDRPreflightOptions: Skipping FDR preflight</div><div>[15:33:52.0833] Stashbag commit failed: failed to set manifest: -536870212</div><div>[15:33:52.0838] Failed to commit stashbag</div><div>[15:33:52.0838] Finished BootedOS Restore Phase: Failed</div><div>[15:33:52.0838] State Machine Dump, status:ERROR - [state:BootedOS remaining-cycles:0] -> [state:Recovery remaining-cycles:2 (current state)] -> [state:RestoreOS remaining-cycles:1]</div><div>[15:33:52.0838] Restore completed, status:4000</div><div>[15:33:52.0838] Failure Description:</div><div>[15:33:52.0838] Depth:0 Code:4037 Error:Failed to commit stashbag</div><div>[15:33:52.0838] Depth:1 Code:4000 Error:Stashbag commit failed: failed to set manifest: -536870212</div><div>[15:33:52.0838] Changing state from 'Restoring' to 'Error'</div><div>[15:33:52.0838] State is now set to error: Failed to commit stashbag</div></div><p>Searched Apple forum but nobody encountered the exact error.</p><p>Tested following step, and it resolved the problem. iOS was able to upgrade to 14.2.2 without any more isssue:</p><p>1. Disable passcode protection</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-32209672817990553342020-11-27T16:27:00.008-05:002020-11-30T10:22:33.855-05:00Oracle: Oracle Enterprise Manager Express 12.2 Setup<p>Product: Oracle Enterprise Manager Express<br />Version: 12.2.0.1<br />Oracle Document URL: https://docs.oracle.com/database/121/ADMQS/GUID-BA75AD46-D22E-4914-A31E-C395CD6A2BBA.htm</p><p>Followed above Oracle Enterprise Manager Express 12.1 documentation to check my Oracle 12.2 EM Express login issue, and found the workaround.</p><p>Problem #1: Chrome blocks Adobe Flash content, so Oracle EM Express login screen won't appear<br />Solution: Uses IE 11 in Windows 10</p><p>Problem #2: Accessing http://hostname:8080 consistently getting prompt with <i>That server also reports: "XDB"</i> and cannot login as SYSTEM as SYS users</p><p>Solution: Change http://hostname:8080/<b>em</b>, which contains the extra "/em"</p><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx3epa5tH2QR5maFENVIUjK_p-e7nCV-2Ki_C2YajGdtMaRoySMv-lREHYMkF61oLWbXw7zwb90mcASifAGQfHI2OppmEKIuKEfpkxIGypIB5JjqhNcE0nLvNI-LyL9OdpoCbHGd_tGKDw/s456/Image+121.png" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" data-original-height="381" data-original-width="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx3epa5tH2QR5maFENVIUjK_p-e7nCV-2Ki_C2YajGdtMaRoySMv-lREHYMkF61oLWbXw7zwb90mcASifAGQfHI2OppmEKIuKEfpkxIGypIB5JjqhNcE0nLvNI-LyL9OdpoCbHGd_tGKDw/s320/Image+121.png" width="320" /></a></p><p><br /></p><p>Problem #3: HTTP port is not configured, but only HTTPS. I prefer HTTP for ease of troubleshooting, and not to maintain self-signed SSL cert<br />Solution:</p><p>1. Login to sqlplus as system, and run following:</p><pre dir="ltr" style="background-color: #f9f9fb; border-radius: 3px; border: 1px solid rgb(217, 217, 226); box-sizing: border-box; font-family: menlo, monaco, "Courier New", Courier, mono, serif; font-size: 13px; line-height: 1.615; margin-bottom: 1em; margin-top: 0px; overflow-wrap: normal; overflow: auto; padding: 5px;">exec DBMS_XDB_CONFIG.SETHTTPSPORT(8080);</pre><p>2. Stop Listener</p><p>lsnrctl stop</p><p>3. Start Listener</p><p>lsnrctl start</p><p>4. Checks listener is showing extra entry for port 8080</p><p>(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<i>hostname</i>)(PORT=8080))(Presentation=HTTP)(Session=RAW))</p><div>Problem 3: EM Express keeps prompting me with a dialog box with credential to XDB<br />Solution:</div><div>1. Login to sqlplus as <b>system</b>, and run following</div><p>exec dbms_xdb_config.setglobalportenabled(TRUE);</p><p>2. Restart Oracle database</p><p>3. Access http://<i>hostname</i>:8080/em and unable to login with following XDB login screen and able to see the login screen</p><p>Issue resolved</p>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-75660590506894982072020-11-24T16:22:00.009-05:002020-11-24T16:23:43.655-05:00PowerShell: Download Video in Parallel<p>Product: PowerShell</p><p>PowerShell script which shown an example to download video from web, which contains multi-part TS video.</p><p>Assumptions:</p><p></p><ul style="text-align: left;"><li>The TS video starts with number 1</li><li>Last part of the video is number 600</li><li>The file name is a running number that increment by 1</li><li>All videos have exact same URL, but different by running number</li><li>The filename has 3 character digit, e.g. 001, 002, 003</li><li>The output file is call S01E08-[part].ts</li><li>The video extension is .ts</li><li>5 parallel download</li></ul>
<table border="1">
<tbody><tr>
<td><pre>Start-Job -Scriptblock {
1..100 | foreach {
$url_main="https://www.szhysws.com:65/20190422/RuBaJpqw/2000kb/hls/1svhk1647"
$i="{0:000}" -f $_
$url="$url_main$i.ts"
$file="S01E08-$i.ts"
Write-Output $file
Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $url -OutFile $file
}
}
Start-Job -Scriptblock {
101..200 | foreach {
$url_main="https://www.szhysws.com:65/20190422/RuBaJpqw/2000kb/hls/1svhk1647"
$i="{0:000}" -f $_
$url="$url_main$i.ts"
$file="S01E08-$i.ts"
Write-Output $file
Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $url -OutFile $file
}
}
Start-Job -Scriptblock {
201..300 | foreach {
$url_main="https://www.szhysws.com:65/20190422/RuBaJpqw/2000kb/hls/1svhk1647"
$i="{0:000}" -f $_
$url="$url_main$i.ts"
$file="S01E08-$i.ts"
Write-Output $file
Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $url -OutFile $file
}
}
Start-Job -Scriptblock {
301..400 | foreach {
$url_main="https://www.szhysws.com:65/20190422/RuBaJpqw/2000kb/hls/1svhk1647"
$i="{0:000}" -f $_
$url="$url_main$i.ts"
$file="S01E08-$i.ts"
Write-Output $file
Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $url -OutFile $file
}
}
Start-Job -Scriptblock {
401..600 | foreach {
$url_main="https://www.szhysws.com:65/20190422/RuBaJpqw/2000kb/hls/1svhk1647"
$i="{0:000}" -f $_
$url="$url_main$i.ts"
$file="S01E08-$i.ts"
Write-Output $file
Invoke-WebRequest -UserAgent "Mozilla/5.0 (Windows NT 6.1; Win64; x64)" -Uri $url -OutFile $file
}
}
<br /></pre></td>
</tr>
</tbody></table>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0tag:blogger.com,1999:blog-5829191550601027073.post-44144239271653950072020-11-24T16:11:00.005-05:002020-11-24T16:11:42.353-05:00PowerShell: Merge TS Video as MP4 Video<p>Product: Windows Command Prompt, FFMPEG</p><p>This post is to show using ffmpeg.exe utility (free) that bundled with Stream, or freely download from Internet to combine multi-part TS video as 1 mp4 video</p><p>The steps:</p><p>1. Create a text file which contains list of TS video file<br />2. Feed above file into ffmpeg as input file<br />3. Specify "copy" in ffmpeg to copy the video <br />4. Specify audio format conversion from MPEG-2 or MPEG-4 AAC ADTS to MPEG-4 LATM<br />5. Assumes that I have a TS video for a video for Season 1, Episode 8, and their filename format is S01E[nnn] where nnn is the part number<br />6. Set the output filename as "Expense" plus episode number, in this case, 08</p><p>Command:</p><p>1. Start CMD as regular user</p><p>2. Create following as create_video.cmd in location where the TS video files are located</p><p>set n=08</p><p>(for %%i in (S01E*.ts) do @echo file '%%i') > S01E%n%_List.txt</p><p>REM ..\ffmpeg -f concat -safe 0 -i S01E%n%_List.txt -c copy Expense_S01E%n%.ts</p><p>..\ffmpeg -f concat -i S01E%n%_List.txt -c copy -bsf:a aac_adtstoasc Expense_S01E%n%.mp4</p><div>3. Run "create_video.cmd"</div>SChttp://www.blogger.com/profile/07381143682989190248noreply@blogger.com0