Error: ORA-65096: invalid common user or role name in oracle 19c database

Dear DBA-Mates, Happy New Year to you all!!!

Hope you all doing good.

Here, we would like to share user creation error ORA-65096: invalid common user or role name in 19c database.

How to create database user in 19c oracle database because when you create database user in oracle 19c database you may get an ORA error like ORA-65096 as shown below in details.

So, to avoid that user creation error either we can set one hidden parameter to true or else put pre-fix word as c##.

For kind information without alter of the hidden parameter or pre-fix word c## you can’t create the user in oracle 19c database like previous version of the oracle database.

Please find the below details:

Error:

SQL> show user

USER is "SYS"

SQL> create user ogg identified by ogg account unlock;

create user ogg identified by ogg account unlock

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL>

 

Solutions:

1.   Here, we have one hidden parameter which needs to be set as True as shown in below command:

SQL> alter session set "_ORACLE_SCRIPT"=true;

 Session altered.

 SQL> show user

USER is "SYS"

SQL>

 SQL> create user ogg identified by ogg account unlock;

 User created.

 SQL>

 

SQL> GRANT CONNECT, RESOURCE, DBA TO ogg;

 Grant succeeded.

 SQL>

SQL> conn ogg/ogg

Connected.

SQL> show user

USER is "OGG"

SQL>

2.   As discussed, there is another method also which can be used here but it will be different like pre-fix word as shown below:

 

SQL> create user C##OGGS identified by oggs;

 User created.

 SQL> create user c##OGGS identified by oggs container=all;

 User created.

SQL>

19c DB aler log path and details:

[oracle@linux trace]$ view alert_orcl.log

[oracle@linux trace]$ pwd

/u01/19cDB/diag/rdbms/orcl/orcl/trace

[oracle@linux trace]$

 

SQL> select name,open_mode from v$database;

 

NAME      OPEN_MODE

--------- --------------------

ORCL      READ WRITE

 

SQL>

SQL> select BANNER_FULL from v$version;

 

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL>

Some more important links:

Oracle Database 19c Features

Cloud Service Models in cloud computing

What is PDB and CDB in oracle 12c

How to improve oracle database performance tuning

How to change sysadmin password FNDCPASS in R12.1.3

Oracle Database 19c Features

Dear DBA-Mates,

We would like to share one of most important information as all the customer are upgrading to 19c.

Hope his will be useful as reason why we need to upgrade 19c database from previous version of database specially 12c which I am seeing/doing. So be Ready!!!

Reasons to upgrade 19c:

1.Oracle lifetime support policy only follows their latest version

2. OS compatibilities as per DB versions

3. Security purpose as compared to old version. 

Some Older version History:

12c (2014): Oracle designed to work on the Cloud, the most notable features are the pluggable databases and multitenant architecture.

 

18c (2018): autonomous database, this self-driving database is self-securing and self-repairing, available for the Oracle cloud or on-site premises.

 

19c (Jan 2019): will be supported through 2023, extended till 2026.

latest version-19.3

 

Oracle Version 19C

The latest Oracle version, the 19C, was released in early January 2019. It's been noted as the long- term release for the 12.2 product family of Oracle databases. This particular version will be supported through 2023, with extended support available until 2026.

 

Some of the newest features in the 19C release include:

Query Quarantine -

Unlike with previous Oracle versions, 19C solves the problem of overloading system resources with query requests. This version provides automatic query quarantine to ensure that specific queries aren't running over and over.

 

JSON Support -

This version has simplified the syntax for JSON functions and allows users the capability to do partial updates of JSON. In addition, it provides SODA APIs for Node.js, C, Python, and Java.

 

Automatic Indexing -

19C offers a machine learning algorithm feature that provides constant index adjustment. This algorithm leads to enhanced system performance and more cost savings. After just a short amount of use, this database version will build indexes that allow for more efficient plans for each dataset.

 

Hybrid Partitioned Tables -

This 19C feature permits database administrators to manage tables both inside and outside the database with ease. The partitions inside the database can easily be managed alongside partitions that are in read-only data stores outside of the database on the premises or on the Cloud.

 

Active Data Guard -

This new feature works to prevent downtime and data loss during database repairs and upgrades. It allows for incidental updates to be run while a synchronized physical replica or standby is maintained.

Some more useful link:

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

Cloud Service Models in cloud computing

Dear DBA-Mates,

As per last commitment/promises, we are back with next topic Cloud Service Models.

Here, we would like to explain it in simple and short notes for quick references. This is one of the basic and important which should be clear for any cloud concepts. So, tried to clear in simple ways, hope this will be useful for all.

Cloud Service Models:  Means the Services provided by the Cloud companies.

Please find the below main Cloud Service Models:

1.   IaaS: Infrastructure as a service

In this Service model, we will get infrastructure template by cloud provider company and remaining management, configuration part will be taken care by the clients.

Eg. in aws:  EC2 -- virtual machine (only this will be provided by Cloud provider with resources as per demand)

We Manage: Data, Apps, Runtime, Middleware, O/S.  (Total is 5)

Provider Manage: Virtualization, Server, Storage, Networking.  (Total is 4)

 

2.   PaaS: Platform as a Service

In this Service model, Cloud provider company will provide configured platform and client only need to deploy their apps, codes etc…

We Manage: Data, Apps (total is 2)

Provider Manage: Runtime, Middleware, O/S, Virtualization, Server, Storage, Networking. (Total is 7)

 

3.   SaaS:  Software as a Service

In this Service model, Cloud service provider company will provide direct software to use. Client/user will use direct software through internet from anywhere in the world.

In Shorts:

Ready to use software. -- We will only login on internet and will use apps.

Example: Gmail, Facebook etc... web software, web services in software form.

We Manage: Data (total is 1)

Provider Manage: Apps, Runtime, Middleware, O/S, Virtualization, Server, Storage, Networking. (Total is 8) 

Some other Services model:

4. Database model:

In this model service provider will install database and provide services

5. Storage Models etc...

6. Data Models

Some more useful link:

How to improve oracle database performance tuning

R12.2 Adop Phases in Oracle EBS

How to change sysadmin password FNDCPASS in R12.1.3

ADADMIN, ADCONFIG and ADMRGPCH

AD Utilities for Oracle Apps DBA

AWS Cloud Computing Concepts

Dear DBA-Mates,

As we all are knowing that cloud is hot in market in which AWS is one of the most important. So, I would like to share some important knowledge in short and easy way which can be learn in short time and will help in career.

So, let’s start and grow together!!!

Cloud Computing:

Suppose, we have to start any business then we have to setup an infrastructure and then we need Human power to manage that infrastructure.

Also, here infrastructure like servers where you can develop your software and test your software which was very costly and everyone was not able to effort it.

Earlier disadvantages:

1. More expenses for setup and resources

2. Less time to focus on business.

 

For example: If you have to open a shop then you have to buy land then build room/house which is very costly and everyone can't effort.

So, people take shop on rent and they run their business.

Same way cloud also works here...

 

Here, Computation means calculation.

Cloud computing example like Electric Supply -- You pay what you use.

We can use scale-up and scale-down means resources can be increase or decrease as per requirements.

Cloud Computating:

It enables on-demand services like compute, storage, networking etc... which needs to develop and deploy a software and can be access through internet in the world anywhere.

Features:

Scalable, Pay as You use, Flexible, Cost Effective, smooth services,

Secure and Disaster Recovery. (It depends how you want manage with permissions and access.

It's up to you how much access/permissions you want and how much you to give to providers).

Cloud Service Models <<click here

Some more useful links:

What is PDB and CDB in oracle 12c

SQL Tuning Advisor steps and details

How to change database DB name using NID utility

TKPROF Concepts with Syntax and Real time example

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

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 questions 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

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

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:

https://ora-data.blogspot.com/2020/05/how-to-get-sql-query-output-in-xml.html

https://ora-data.blogspot.com/2020/02/how-to-change-database-db-name-using-nid-utility.html

https://ora-data.blogspot.com/2019/12/stuck-thread-in-weblogic-error-and-solution.html

https://ora-data.blogspot.com/2019/11/oracle-apps-dba-interview-questions-and-answers.html

Ref.: 1392455.1