Tuesday, 31 August 2021

Xclock issue: Error: Can't open display Fixed

Dear DBA-Mates,

Hope you all are Good and Safe, also may have taken Covid-19 Vaccination which is very important.

We would like to share Xclock issue: Error: Can't open display xclock display error which we need to fix for display during Oracle Database/Application software installation as GUI.

Below are some important points and steps which may useful to all. This is very easy and simple steps but sometimes it will be headache to us…

Xclock issue: Error: Can't open display

XCLOCK Fix:

Below are the Pre-checks/pre settings:

1.   We need to start Xming software.

2.   We need to enable X11 in putty as shown below path:

Open putty à SSH à X11 à X11 Forwarding (Tick Mark “Enable X11 forwarding”)

X display location : “:0.0”

3.   Echo $DISPLAY – should give output

4.   Below rpms should be installed:

1. libXaw-1.0.13-4.el7.x86_64.rpm

2. xorg-x11-apps-7.7-7.el7.x86_64.rpm

5. If still not working then we can add xauth list as shown below:

[oracle@linux dbhome_1]$ xclock

Error: Can't open display:

[oracle@linux dbhome_1]$ echo $DISPLAY

 

[oracle@linux dbhome_1]$ export DISPLAY=localhost:11.0

[oracle@linux dbhome_1]$ xclock

PuTTY X11 proxy: Authorisation not recognised

Error: Can't open display: localhost:11.0

[oracle@linux dbhome_1]$ xauth list

linux.oracle.com/unix:16  MIT-MAGIC-COOKIE-1  ea8443005780edfb76f6930b20d4802c

linux.oracle.com/unix:11  MIT-MAGIC-COOKIE-1  80eab308a9f45535b261e1bf999d475e

linux.oracle.com/unix:13  MIT-MAGIC-COOKIE-1  3bbf11afe24f078bae5f55ae9145ca64

linux.oracle.com/unix:14  MIT-MAGIC-COOKIE-1  abc18e54fd548ccec81649f300a6a1af

linux.oracle.com/unix:15  MIT-MAGIC-COOKIE-1  2a03c3a4cc9c52f41b6fd13878194c0e

linux.oracle.com/unix:12  MIT-MAGIC-COOKIE-1  e7b5657526c2481dbfee1e0599496013

linux.oracle.com/unix:10  MIT-MAGIC-COOKIE-1  1ee8aec7489a11e0cb89a93734ccc19c

[oracle@linux dbhome_1]$ pwd

/u01/DB18C/oracle/product/18.0.0/dbhome_1

[oracle@linux dbhome_1]$

[oracle@linux dbhome_1]$ echo $DISPLAY

localhost:11.0

Note: If xclock is working with root but not working with oracle user then also we can take xauth list from root user and export the display as per root user and add the xauth list in oracle user from root user.

Below is the step to add the xauth list:

[oracle@linux dbhome_1]$ xauth add localhost.localdomain/unix:10  MIT-MAGIC-COOKIE-1  15779b706bb1daf925ad02323df4fda3

[oracle@linux dbhome_1]$ xauth add localhost.localdomain/unix:11  MIT-MAGIC-COOKIE-1  542175afd02f0cc7d5f9fe27c43321d4

[oracle@linux dbhome_1]$ xauth add linux.oracle.com/unix:10  MIT-MAGIC-COOKIE-1  94eb4e7f6c702d2e962a3e2cbf727dc8

[oracle@linux dbhome_1]$ xauth add linux.oracle.com/unix:11  MIT-MAGIC-COOKIE-1  21ad871e3931d34b963988874a232d0d

[oracle@linux dbhome_1]$ xclock

Warning: Missing charsets in String to FontSet conversion

^C

[oracle@linux dbhome_1]$

Hope above steps are easy and helpful. Kindly let us know for any concerns and will try to give some more inputs.

How to improve oracle database performance tuning

SQL Tuning Advisor steps and details

What is PDB and CDB in oracle 12c

Stuck Thread in WebLogic Error and solution

Concurrent Manager Performance Tuning Tips in Oracle EBS R12

Saturday, 20 March 2021

How to improve oracle database performance tuning

Dear DBA-Mates,

Hope you all are doing Good and fantastic. We are back with one of the most important question as Oracle Database performance Tuning which will be useful here. And would like to share with you all.

In every interview question is asked as What to do if Oracle Database Performance issue occur?

Below are some points which are very important and useful to check the performance and will help to troubleshoot the Database performance issue.

1. DB and listener status:

Database and listener status is the basic check confirmation.

2. Mount points like archive destination and others:

We have check for the all-mount points and no mount point should be 100%. And specially archive mount point which should be below 70 to 80%.

3. Alert log and Trace file: which helps in getting some issue related information

4. top activity: 

OEM monitoring tool (memory uses, CPU uses)

5. Blocking session and deadlock: 

We can check blocking sessions and deadlock if occur any. Please click on blocking sessions and deadlock for more details.

6. User session (active, inactive, ideal etc):

We can check the number of Inactive sessions and if there is more number from long time then we can check details and kill them like form sessions related sessions and other accordingly.

7. Increase memory, CPU, resources if number of users increased, check DB growth – We can check and compare the details from previous to current like DB growth and user growth, so in case of DB and number of users have increased then of course we need to increase the memory and number of CPU to increase the performance.

8. Optimize the query:

We can optimize the query also to improve query.

9. DB Backup should run in non-business hours:

As we all know DB backup process uses resources which may also decrease the performance of database. So, always should run in non-hours business.

10. AWR, ash and addm reports:

We can also take awr, ash and addm reports which can help in analysis of the resource, sql etc... uses and can help in to improve the database performance.

11. Wait events and their types:

Here, we can check the what are events occurred in AWR report which can be analysis and take action accordingly.

12. Oracle bug: 

After above checks if still now helpful then we can check for if there is any oracle bug. For this we need to raise the Oracle Support and may need to database apply patches like quarterly or security patches etc. as per suggested by oracle support.

13. Table Scan check:

We can check if the query is scanning full table or not. Also, can check for GSS, last analyzed table/schema.

Hope this may useful, there are some more details which will backup with some more details.

Thank you all!!!

Ora-data Team

Friday, 15 January 2021

SQL Tuning Advisor steps and details

 Dear DBA-Mates,

Hope you all are Safe and doing well!!! As we all know about corona virus, so be-careful and safe.

Yes… Long time haven’t written anything but if you ask me I was also lost in this world and lots of things came up and down and that’s called life for all…

It’s ok… Let’s start the topic now which is very important and DBA’s should know it. You all may heard about SQL Tuning but very few people only do it.

It is very important as whenever developer/functional raise a concerns about the request or program is taking time to complete then we can do the SQLT which helps a lots and resolve the issue…

Please find the below steps:

1. Create Tuning Task:

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '8b98n39ax64uj',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 300,

                          task_name   => '1q7d0p8qnpv51_tuning_task11',

                          description => 'Tuning task1 for statement 1q7d0p8qnpv51');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

2. Execute Tuning task:

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '1q7d0p8qnpv51_tuning_task11');

 

3. Get the Tuning advisor report:

set longchunksize 65536

SET LONG 100000;

SET PAGESIZE 50000

SET LINESIZE 200

select dbms_sqltune.report_tuning_task('1q7d0p8qnpv51_tuning_task11') from dual;

This output can be spool also, which we can give to developer team to verify and then we can execute it.

This output will give advice and sql tune script by which running it will run fast and get it complete.

Some useful links:

How to change database DB name using NID utility

Stuck Thread in WebLogic Error and solution

What is PDB and CDB in oracle 12c

ORA-01110 data file 1: system01.dbf recovery

Friday, 12 June 2020

DemoIdentity.jks file not found

Dear DBA-Mates, Topic: demoidentity.jks does not exist for Nodemanager
Hope you all are doing Good in Covid-19. Stay safe, be safe!!!

This post is related to Node Manager Issue which throwing error while starting as DemoIdentity.jks is not found under below path.
.../wlserver_12.2.1/oracle_common/common/nodemanager/security/DemoIdentity.jks

When we start Node Manager, it will not start and will come out of nohup. So, we need to check the log file where we can see this alert.
Error/Action:

ora-data.blogspot.com$ ls -ltr .../wlserver_12.2.1/oracle_common/common/nodemanager/security/DemoIdentity.jks

ls: cannot access .../wlserver_12.2.1/oracle_common/common/nodemanager/security/DemoIdentity.jks: No such file or directory

As above we can see file is not found.

So, we can create it manually without any issue.

1. We need to set the wls env file as shown below:

ora-data.blogspot.com$ pwd
.../wlserver/server/bin
ora-data.blogspot.com$

ora-data.blogspot.com$ . setWLSEnv.sh

2. Once above env setted. Go to security directory where you want to create that DemoIdentity.jks file.

ora-data.blogspot.com$ cd .../wlserver_12.2.1/oracle_common/common/nodemanager/security

ora-data.blogspot.com$ java utils.CertGen -keyfilepass DemoIdentityPassPhrase -certfile democert -keyfile demokey -strength 1024 -noskid

Generating a certificate with common name vmohsautr068 and key strength 1024
issued by CA with certificate from /.../wlserver_12.2.1/wlserver/server/lib/CertGenCA.der file and key from /.../fmw/bea/wlserver_12.2.1/wlserver/server/lib/CertGenCAKey.der file

ora-data.blogspot.com$ ls -ltr
total 8
-rw-r-----. 1 ora-data ora-data   64 Jun 2 01:03 SerializedSystemIni.dat
-rw-r--r--. 1 ora-data ora-data  676 Jun 2 01:22 demokey.der
-rw-r--r--. 1 ora-data ora-data  993 Jun 2 01:22 demokey.pem
-rw-r--r--. 1 ora-data ora-data  800 Jun 2 01:22 democert.der
-rw-r--r--. 1 ora-data ora-data 1139 Jun 2 01:22 democert.pem

ora-data.blogspot.com$ java utils.ImportPrivateKey -keystore DemoIdentity.jks -storepass DemoIdentityKeyStorePassPhrase -keyfile demokey.pem -keyfilepass DemoIdentityPassPhrase -certfile democert.pem -alias demoidentity

No password was specified for the key entry
Key file password will be used
<Jun 2, 2020 1:23:07 AM EDT> <Info> <Security> <BEA-090905> <Disabling the CryptoJ JCE Provider self-integrity check for better startup performance. To enable this check, specify -Dweblogic.security.allowCryptoJDefaultJCEVerification=true.>
<Jun 2, 2020 1:23:07 AM EDT> <Info> <Security> <BEA-090906> <Changing the default Random Number Generator in RSA CryptoJ from ECDRBG28 to HMACDRBG. To disable this change, specify -Dweblogic.security.allowCryptoJDefaultPRNG=true.>

Imported private key demokey.pem and certificate democert.pem
into a new keystore DemoIdentity.jks of type jks under alias demoidentity

ora-data.blogspot.com$ ls -ltr
total 10
-rw-r-----. 1 ora-data ora-data   64 Jun 2 01:03 SerializedSystemIni.dat
-rw-r--r--. 1 ora-data ora-data  676 Jun 2 01:22 demokey.der
-rw-r--r--. 1 ora-data ora-data  993 Jun 2 01:22 demokey.pem
-rw-r--r--. 1 ora-data ora-data  800 Jun 2 01:22 democert.der
-rw-r--r--. 1 ora-data ora-data 1139 Jun 2 01:22 democert.pem
-rw-r--r--. 1 ora-data ora-data 1577 Jun 2 01:24 DemoIdentity.jks
ora-data.blogspot.com$

Then start the Node Manager, it will work fine.
Some useful links:



Ref.: 1392455.1

Monday, 4 May 2020

How to get sql query output in xml format from putty session

Dear DBA-Mates, How to get sql query output in xml format.
Hope you all are doing well and Safe at Home!!!

We would like to share one of very important and useful query for How to get SQL Query output in xml/xls readable format.

This is very headache if you are running in Putty Session.

Even we also use this same format for such queries. So, we thought to share to you all and it will be easy for us also for references in future.

This type of request will be send by the developer or functional team which they may need for some analysis or monthly reports generation.

Whatever if they asked then as a DBA we need to provide them, even though angry inside Hahahah.

And the worst things those queries will be running 2 to 3 hrs and rows may be 2 to 3 lakhs, so can imagine it.

$sqlplus / as sysdba
SQL> SET LINESIZE 10000
SQL> SET VERIFY OFF
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 999

SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SQL> SPOOL <Output_Filename>.xls
SQL> set time on   ## To Capture query start and end time
SQL> @<Filename>.sql
SQL> spool off;

Optional:
SQL>SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON

SQL>SET LINESIZE 2000 VERIFY ON FEEDBACK ON

OR
SQL> set feed off markup html on spool on
SQL> SET PAGESIZE 50000
SQL> SPOOL <output_filename>.xls
SQL> <your_query like select * from dual;>
SQL> SPOOL OFF;

OR
SQL> set pagesize 50000
SQL> set heading on
SQL> set echo off
SQL> set colsep ','
SQL> set heading on
SQL> SET LINESIZE 999 VERIFY OFF FEEDBACK OFF
SQL> SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SQL> spool workflow.xls
SQL> <Your query>
SQL> Spool off;

OR
Save Query output as HTML file from SQLPLUS

As alternative you may use below block of code from the sqlplus only to save the output as HTML file.

SQL> set linesize 4000
SQL> set verify off
SQL> set feedback off
SQL> set pagesize 9999
SQL> set markup html on entmap on spool on preformat off
SQL> spool /tmp/query_output.html
SQL> --Your query--
SQL> set markup html off
SQL> spool off;

Hope this ma useful and helpful for everyone here.

Some more useful link:
Regards,