ORA - 01017:invalid username/password; logon denied , ORA-28040, ORA-03134

Issue:
When application schema was trying to connect the database from SQL Developer, they are facing intermittent issues with the below. out every 3 attempts only one connection is establishing successfully.

DB version: 12.1.0.2
2 node RAC
Status : Failure -Test failed ORA- 01017:invalid username/password; logon denied

This issue may also arrives when you face below errors.If the client release does not meet or exceed the value defined by this parameter, then authentication fails with one of these two errors:

ORA-28040: No matching authentication protocol error

ORA-03134: Connections to this server version are no longer supported

But in this case we faced below issue.

ORA- 01017:invalid username/password; logon denied

Solution:

In general issue will be with your password, so make sure to ask application team to use the valid credentials.

But here in this situation,

someone has modified the $GI_HOME/network/admin/sqlnet.ora file only node 1, 

which is has having sqlnet.allowed_logon_version_server=8 value but later it modified to sqlnet.allowed_logon_version_server=12 and  due to that a your application proctol is not able to authenticate the sqlnet.allowed_logon_version_server=12 version for your client connections.

So just revert back the changes sqlnet.allowed_logon_version_server of the working node in cluster (or) to the allowed authenicate protocal version.

Note: You can check in test Database and observe the changes.

For UNIX systems,

the sqlnet.ora can be located any of these locations $ORACLE_HOME/network/admin (or) $GI_HOME/network/admin.

By default,  $ORACLE_HOME/network/admin 

The Oracle documentation notes that a setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, and 11g.  

sqlnet.allowed_logon_version_server=8:  A setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, 11g, and 12c.  

sqlnet.allowed_logon_version_server=12a: A sqlnet.allowed_logon_version_server setting of 12a permits only the 12c password version.

A value than 12 means the server is less compatible in terms of the protocol that clients must understand in order to authenticate. the server is also more restrictive in terms of the password version that must exist to authenticate any specific account. the ability for a client to authenticate depends on the dba_users.password_versions value on the server for that account.

The default is sqlnet.allowed_logon_version_server=11, but the following values are supported for sqlnet.allowed_logon_version_server, depending on the version of the authentication protocol.

sqlnet.allowed_logon_version_server=12a:  For Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later.

sqlnet.allowed_logon_version_server=12:  For the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended).

sqlnet.allowed_logon_version_server=11:  For Oracle Database 11g authentication protocols (default).

sqlnet.allowed_logon_version_server=10:  For Oracle Database 10g authentication protocols.

sqlnet.allowed_logon_version_server=8:  For Oracle8i authentication protocol

Hope this is clear..!!!

Generate Automatic Workload Repository (AWR) report:

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

Features of AWR:

• Some of the system and session statistics collected from V$SYSSTAT and V$SESSTAT views
• Object statistics that determine both access and usage statistics of DB segments.
• ASH statistics.
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
• SQL statements that are producing the highest load on the system, based on elapsed time and CPU time.

Types of AWR reports:

@?/rdbms/admin/awrrpt.sql --> Generates an HTML or text report that displays statistics for a range of snapshot Ids.

@?/rdbms/admin/awrrpti.sql -->  Generates an HTML or text report that displays statistics for a range of snapshot
Ids on a specified database and instance.

@?/rdbms/admin/awrsqrpt.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids.
Run this report to inspect or debug the performance of a SQL statement.

@?/rdbms/admin/awrsqrpi.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance.
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance

@?/rdbms/admin/awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and
configuration settings between two selected time periods.

@?/rdbms/admin/awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

In order to run this reports you should have DBA role granted and you need to choose the snap id's interval based on your issue time.

awrrpt.sql is the most key common report and that is very helpful when you start investigating your journey on performance issues.

Access Control List (ACL)

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:

In  11g and beyond, Oracle has introduced the ability to restrict connections to specific hosts (or IP addresses).

The access control lists (ACL) are used to restrict the hosts that are allowed to connect to the Oracle database.

ACL's are created using the dbms_network_acl_admin and dbms_network_acl_utility packages.  Either package can be used to create and manage ACLs.

• acl - The name of the access control list XML file, generated relative to the "/sys/acls" directory in the XML DB Repository.
• description - A description of the ACL.
• principal - The first user account or role being granted or denied permissions. The text is case sensitive.
• is_grant - TRUE to grant, FALSE to deny the privilege.
• privilege - Use 'connect' for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use 'resolve' for UTL_INADDR name/IP resolution. The text is case sensitive.
• start_date - Default value NULL. When specified, the ACL will only be active on or after the specified date.
• end_date - An optional end date for the ACL.

Note:  It is not possible that one server can have multiple services configured. If you try to do so then first configured service will be overwritten.

Creation of ACL Example:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                    description => 'WWW ACL',

                                    principal   => 'SCOTT',

                                    is_grant    => true,

                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',

                                       principal => 'SCOTT',

                                       is_grant  => true,

                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                    host => 'www.us.oracle.com');

END;

/

COMMIT;

Please find below sample scenario.

Error:

we have faced below error on DB: 

ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

ORA-06512: at "XMLUSER.SEND_FLOWMON_MSG_ORDFAIL", line 33

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 816

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 793

The DBA_NETWORK_ACLS view displays information about network and ACL assignments:

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL

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

101.29.77.147                                         /sys/acls/utl_http.xml

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL:

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

col ACL for a40

col PRINCIPAL for a30

col PRIVILEGE for a30

SELECT acl,

       principal,

       privilege,

       is_grant,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges;

ACL                            PRINCIPAL                      PRIVILE IS_GR START_DATE  END_DATE

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

/sys/acls/utl_http.xml         TESTUSER                        connect true

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings:

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM   user_network_acl_privileges;

HOST                           LOWER_PORT UPPER_PORT PRIVILE STATUS

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

101.29.77.147                                         connect GRANTED

101.29.77.147                                         resolve GRANTED

Steps To Fix the above issue:

1. First check the privileges on the user whether connect privilege is granted or not using dba_network_acl_privileges view, in my case privilege is not there.
2. If not granted then follow below step.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/utl_http.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'connect');

end;

/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'resolve');

end;

/

commit;

    3.The final step is to assign the ACL to a specific target.

BEGIN

dbms_network_acl_admin.assign_acl (

acl => 'http_permissions.xml',

host => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

lower_port => 80,

upper_port => 80

);

END;

After above steps performed my issues has been resolved.

Hope this helps !!! Cheers !!!

Thank you 😊

Oracle 12c, 12.1.0.2.0 Basic commands & PDB save state feature

How to check the current container and set container database:

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED

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

         2 PDB$SEED  READ ONLY  NO

         3 TEST1     READ WRITE NO

SQL> alter session set container='&CON_NAME';

SQL> show pdbs

How to create pluggable(PDB) database:

CREATE PLUGGABLE DATABASE  <PDB_NAME> ADMIN USER <Username> IDENTIFIED BY <Password>;

How to drop PDB database:

DROP PLUGGABLE DATABASE <PDB_NAME> INCLUDING DATAFILES;

How to open pluggable instance wise:

ALTER PLUGGABLE DATABASE <PDB_NAME>  OPEN INSTANCES=('instance_name’); 

ALTER PLUGGABLE DATABASE <PDB_NAME>  CLOSE INSTANCES=('instance_name’); 

How to open PDB in all instances:

       ALTER PLUGGABLE DATABASE <PDB_NAME> close instances =all; 

To check status PDB status: 

SQL>  SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;  

    INST_ID NAME          OPEN_MODE  RESTRICTED

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

         2 PDB$SEED      READ ONLY  NO

         2 TEST1         READ WRITE NO

         1 PDB$SEED      READ ONLY  NO

         1 TEST1         READ WRITE NO

        

PDB save state feature: 

In earlier releases of 12c (before 12.1.0.2.0) whenever container database was restarted all the pluggable databases associated with container database remains in MOUNT state.

From 12c release 12.1.0.2.0 this functionality can be achieved with new PDB save state feature.

       

Note: By default all the PDB's would be in MOUNTED state.

Example scenario:  

In this case node 2 has rebooted and observed on second instance went to mounted state and also pluggable database was not register in DBA_PDB_SAVED_STATES View, so after staring PDB on 2nd instance, I have registered the second instance into save state.

set pages 1200 lines 120

col CON_NAME for a7

col STATE for a7

col INSTANCE_NAME for a11

col RESTRICTED for a4

select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   testa1     OPEN    NO

TEST1 Pluggable database was not present in DBA_PDB_SAVED_STATES view , so have added using below commands.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

   INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          MOUNTED          >>>Issue one >>>

SQL> ALTER PLUGGABLE DATABASE TEST1 OPEN INSTANCES=('test1a2');

SQL> alter pluggable database TEST1 save state;

Pluggable database altered.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

  INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          READ WRITE NO

SQL> select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   test1a1     OPEN    NO

         

!! Hope this help you !! Cheers😊

Pre-requisites for expdp and impdp:

• Check the database status details using below query.

SQL>COL DB_UNIQUE_NAME FOR A15

SQL>COL PLATFORM_NAME FOR A20 

SQL>SELECT DBID,NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,

PLATFORM_NAME,FLASHBACK_ON 

FROM gV$DATABASE;

• Based on your requirement you can check the query for entire database or schema size using below queries. 

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;

(OR)

SQL>

select owner,sum(bytes)/1024/1024/1024 schema_size_gb

from dba_segments group by owner;

• Check the database version using below query.

SQL> select banner from v$version;

• Check character set for the current database using below query.

SQL>select * from v$NLS_PARAMETERS;

• Check invalid object count on database prior export using below query.

SQL>select count(*) from dba_objects where status='INVALID';

• Check the space requirement where you're going to take the export at OS level or disk group, wherever depends on your requirement.

• Useful commands if you are checking at OS level: 
• df -kh   in Linux/Sun Solaris
• bdf   in HP- UX

• Directory creation at database and OS level using below command.

SQL>create or replace directory <directory_name> as <directory_path>;

SQL>exit;

$mkdir <direcotory_name>

Note: All above steps are just to check the feasibility before export, i will update how to take export and import the database/schema in my next post.