Sujeet APPS DBA

ORA-01031: insufficient privileges while connecting as sqlplus / as sysdba.

[ora@ora_oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 19 04:10:36 2020

ERROR:

ORA-01031: insufficient privileges

============OR=============

[oracle@ora-dev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 19 04:15:30 2020

ERROR:

ORA-01017: invalid username/password; logon denied

Solution :-

Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

Add below parameter in SQLNET.ora file

SQLNET.AUTHENTICATION_SERVICES = (BEQ,)

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10

Note :- No need to restart DB as well as Listener.

ORA-00704 bootstrap process failure

After Upgrading ORACLE_HOME (from 11.1.0.7.0 to 12.1.0.2.0), I was started my database by using startup command, then got below error.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 2550136832 bytes

Fixed Size 1281816 bytes

Variable Size 536871144 bytes

Database Buffers 1996488704 bytes

Redo Buffers 15495168 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

After I saw my alert log and got ORA-00704 error,

Alert Log :

Thread 1 advanced to log sequence 73 (thread open)

Thread 1 opened at log sequence 73

Current log# 1 seq# 73 mem# 0: /apps/oracle/db/apps_st/data/log01a.dbf

Current log# 1 seq# 73 mem# 1: /apps/oracle/db/apps_st/data/log01b.dbf

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

SMON: enabling cache recovery

Errors in file /apps/oracle/db/tech_st/11.1.0/admin/oracle_sujeet/diag/rdbms/oracle/trace/oracle_ora_16271.trc:

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-01426: numeric overflow

Error 704 happened during db open, shutting down database

USER (ospid: 16271): terminating the instance due to error 704

Instance terminated by USER, pid = 16271

ORA-1092 signalled during: ALTER DATABASE OPEN...

ORA-1092 : opiodr aborting process unknown ospid (16271_140096623843136)

Mon Oct 12 15:52:04 2020

ORA-1092 : opitsk aborting process

Solution:

I missed to run catupgrd.sql script after upgrade, so got above errors while starting the database.

Go to ORACLE_HOME/rdbms/admin

[oracle@dba ~]$ sqlplus / as sysdba

sql>startup upgrade

sql>@catupgrd.sql

sql>@utlrp.sql

sql>shut immediate

sql>startup

============================================================

ORA-08176: consistent read failure; rollback data not available

Concurrent log file detail.

--SQLException

java.sql.SQLException: ORA-08176: consistent read failure; rollback data not available

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

===========================================================

ERROR: ORA-8176 "consistent read failure; rollback data not available" Reference Note (Doc ID 20758.1)

Error:  ORA-08176 consistent read failure; rollback data not available 
---------------------------------------------------------------------------

Cause:  Encountered data changed by an operation that does not generate 
	rollback data : create index, direct load or discrete transaction.

Action: In read/write transactions, retry the intended operation. Read only 
	transactions must be restarted.

Who is locking your accounts in oracle 12c database

First check which schema got locked.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME='SYSTEM';

USERNAME ACCOUNT_STATUS EXPIRY_DA

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

SYSTEM LOCKED

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='SYSTEM';

CAUSE

The situation can happen in case when someone (script or application) is trying to connect to a database with a wrong password

thereby locking an account. By default, the DEFAULT profile allows 10 failed login attempts before an account will be locked.

Because of multiple failed login attempts an account switches its status to ‘LOCKED(TIMED)‘.

An account is locked due to FAILED_LOGIN_ATTEMPTS profile parameter.

=============================================================

Three main view:

DBA_AUDIT_TRAIL – standard auditing only (from AUD$)

DBA_FGA_AUDIT_TRAIL-Fine grained auditing only (from FGA_LOG$)

DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing

FAILED_LOGIN_ATTEMPTS specify the number of consecutive failed attempts to log in to the user account before the account is locked. If you omit this clause, then the default is 10 times.

PASSWORD_LOCK_TIME specify the number of days an account will be locked after the specified number of consecutive failed login attempts. If you omit this clause, then the default is 1 day.

The DBA_AUDIT_SESSION view contains all failed logins attempts made by the script.

The column RETURNCODE in the view corresponds to the ORA- error raised on the failed logins.

It’s enough information to find out who or what caused locking of the account.

==============================================================

Please find below All AUDIT parameter and SQL statement.

show parameter audit;

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/oracle/adump

audit_sys_operations boolean TRUE

audit_syslog_level string

audit_trail string DB

unified_audit_sga_queue_size integer 27535

======================================================

SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;

SELECT username,userhost,returncode FROM dba_audit_session;

SELECT * FROM DBA_STMT_AUDIT_OPTS;

SELECT username,userhost,returncode FROM dba_audit_trail where USERNAME='SYSTEM';

select OS_USERNAME,USERNAME,USERHOST,TERMINAL,TIMESTAMP,OWNER,ACTION_NAME from dba_audit_trail;

SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;

SELECT * FROM dba_audit_session WHERE USERNAME='SYSTEM' ORDER BY sessionid DESC;

SELECT username,userhost,returncode FROM dba_audit_session WHERE RETURNCODE='1017' ORDER BY sessionid DESC;

SELECT username,userhost,returncode FROM dba_audit_session WHERE RETURNCODE='28000' ORDER BY sessionid DESC;

SELECT * FROM DBA_AUDIT_SESSION;

SELECT INSTANCE_NUMBER INUM,OS_USERNAME,USERNAME,USERHOST,TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,

ACTION_NAME,OS_PROCESS,RETURNCODE FROM DBA_AUDIT_SESSION WHERE RETURNCODE IN (1017, 28000) ORDER BY EXTENDED_TIMESTAMP;

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

=========================================================================

AUDIT DELETE ANY TABLE BY ACCESS;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY <schema_name> BY ACCESS;

AUDIT ALL BY <schema_name> BY ACCESS;

==========================================================

Enable auditing for failed logon attempts and then query DBA_AUDIT_SESSION view

AUDIT network BY ACCESS;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

To disable auditing issue the following command

NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

===========================END=====================================

APP-AR-96983 : User Write-off limit does not exist

Oracle Application :- R12.1.3

Database :- 11R1

Action steps:

1. Define user write-off limit

Setup > Transactions > Approval Limit

Enter user limit for each currency the user is allowed to write off

2. Define system write-off limit

Setup > System > System Options >

Miscellaneous (tab)

Enter Maximum Write-off Amount

3. Define receivable activity

Setup > Receipts > Receivable Activities

Enter at least one activity with the type of Receipt Write-off

Reference

184887

ORA-28002: the password will expire within 0 days

ORA-28002: the password will expire within 0 days

Cause: The user’s account is about to expire and the password needs to be changed.

Action: Change the password or contact the database administrator.

Reasons:-

This happen when you have not change password in Password_life_time value of the profile set into

your account and you have entered into PASSWORD_GRACE_TIME value.

[oracle@ora-dev ~]$ sqlplus system/manager@DEV

ERROR:

ORA-28002: the password will expire within 0 days

Connected to:

Oracle Database 12c EE High Perf Release 12.2.0.1.0 - 64bit Production

SQL> select username, account_status, EXPIRY_DATE from dba_users where username='SYSTEM';

USERNAME ACCOUNT_STATUS EXPIRY_DATE

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

SYSTEM OPEN 1986-10-11 09:33:28

SQL> select profile from DBA_USERS where username = 'SYSTEM';

PROFILE

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

DEFAULT

SQL>

SQL> select LIMIT, RESOURCE_NAME from dba_profiles

where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME')

and PROFILE=(select profile from dba_users where username='SYSTEM'); 2 3

LIMIT RESOURCE_NAME

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

180 PASSWORD_LIFE_TIME

UNLIMITED PASSWORD_REUSE_TIME

UNLIMITED PASSWORD_REUSE_MAX

7 PASSWORD_GRACE_TIME

SQL>

SQL> alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;

Profile altered.

SQL> commit;

Commit complete.

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 100 PASSWORD_LOCK_TIME 100;

Profile altered.

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 100;

Profile altered.

SQL> commit;

Commit complete.

SQL>

SQL> select LIMIT, RESOURCE_NAME from dba_profiles

where RESOURCE_NAME in ('PASSWORD_GRACE_TIME','PASSWORD_LIFE_TIME','PASSWORD_REUSE_MAX','PASSWORD_REUSE_TIME')

and PROFILE=(select profile from dba_users where username='SYSTEM');

LIMIT RESOURCE_NAME

UNLIMITED PASSWORD_LIFE_TIME

UNLIMITED PASSWORD_REUSE_TIME

UNLIMITED PASSWORD_REUSE_MAX

100 PASSWORD_GRACE_TIME

SQL>

SELECT * FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 100 NO NO NO

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED NO NO NO

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL NO NO NO

DEFAULT PASSWORD_LOCK_TIME PASSWORD 100 NO NO NO

DEFAULT PASSWORD_GRACE_TIME PASSWORD 100 NO NO NO

DEFAULT INACTIVE_ACCOUNT_TIME PASSWORD UNLIMITED NO NO NO

SQL> alter user system identified by DElhi##2021;

User altered.

SQL> commit;

Commit complete.

SQL>

ORA-01017: invalid username/password; logon denied

ERROR:

ORA-01017: invalid username/password; logon denied

First of all you need to edit your sqlnet.ora adding (or lowering) the parameter

SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12.

But if you try to connect directly after restarting your listener you will receive the

same ORA-1017 again. The secret is mentioned in the above documentation link as well:

you will have to recreate the user’s passwords if you need the logon process to work as

it did work before Oracle Database 12.2.

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):

Oracle Database 12.1.0.2:

SQL> alter user system identified by oracle;

User altered.

SQL> connect system/oracle

Connected.

Oracle Database 12.2.0.1:

SQL> alter user system identified by oracle;

User altered.

SQL> connect system/oracle

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

[oracle@ora admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/product/12.2.0/dbhome_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/product/12.2.0/dbhome_1/admin/DEV/wallet)))

SQLNET.EXPIRE_TIME= 30

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='SYSTEM';

USERNAME PASSWORD_VERSIONS

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

SYSTEM 11G 12C

SQL> show parameter sec_case

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

Solution

There’s no “10G” mentioned. This will prevent the connection.

Solution: You will have to specify the password again respective ALTER the user(s):

SQL> alter user system identified by manager;

SQL> select USERNAME, PASSWORD_VERSIONS from DBA_USERS where USERNAME='SYSTEM';

USERNAME PASSWORD_VERSIONS

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

SYSTEM 10G 11G 12C

Issue resolved.