Sujeet APPS DBA: 2020

Enable & Disable ADDM reports in Pluggable Database Oracle

you can configure ADDM reports for Pluggable database(PDBs).

By default it is disabled. You need to enable it manually by executing following commands:

Note:

Default value of AWR_PDB_AUTOFLUSH_ENABLED is false

In Oracle 12c, By default ADDM report is enabled at Container level.

It work same in Oracle 19c as its work in Oracle 12c.

Enable ADDM reports in PDB databases

1. Login in the PDB database.

[oracle@ora admin]$ sqlplus system/manager@PDB

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 15 11:41:32 2020

Last Successful login time: Tue Dec 15 2020 11:36:12 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

2. Set the value of the following parameter.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED

NAME TYPE VALUE

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

awr_pdb_autoflush_enabled boolean TRUE

3. Set the AWR snapshot interval greater than 0 in the PDB.

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>120);

Disable the ADDM reports in PDB

1. Login in the PDB database.

2. Set the value of the following parameter.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=FALSE;

SQL> show parameter AWR_PDB_AUTOFLUSH_ENABLED

NAME TYPE VALUE

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

awr_pdb_autoflush_enabled boolean FALSE

3. Set the AWR snapshot interval greater than 0 in the PDB.

EXEC dbms_workload_repository.modify_snapshot_settings(interval=>0);

How to Run ADDM report on PDB

ADDM must be run from the Root Container database ( CDB )

Running ADDM from CDB will analyze the data of the PDB also and a sample ADDM looks like below.

[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 15 11:58:12 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name;

CON_NAME

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

CDB$ROOT

SQL>

SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/addmrpt.sql

*****************************END****************************************

How to check what current IDLE_TIME is set at in 11g ORACLE DATABASE?

Description

The Idle Time Resource Usage setting limits the maximum idle time allowed in a session.

Idle time is a continuous inactive period during a session, expressed in minutes.

Long-running queries and other operations are not subject to this limit.

Setting an Idle Time Resource Usage limit helps prevent users from leaving

applications open when they are away from their desks.

On my system:

SQL > select * from DBA_PROFILES where resource_name = 'IDLE_TIME';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT COMMON

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

DEFAULT IDLE_TIME KERNEL UNLIMITED NO

ORA_STIG_PROFILE IDLE_TIME KERNEL 15 NO

2 rows selected.

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

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');

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

select * from user_resource_limits where resource_name='IDLE_TIME';

Modify profiles to meet the idle time requirement.

alter profile default limit idle_time 15;

Question:

How the idle_time parameter works. I want to disconnect sessions after the idle_time has expired.

Answer:

The idle_time parameter is used within a SQL*Plus profile. Oracle has several ways to disconnect idle sessions, both from within SQL*Plus via resources profiles (connect_time, idle_time), and with the SQL*net expire time parameter.

IMPORTANT NOTE:

You must set resource_limit=TRUE to automatically terminate Oracle connections.

SQL > alter system set resource_limit=true scope=both;

You can use profiles to set the connect time and idle time with "alter profile" statements. Note that idle_time is expressed in minutes and you can express idle_time for 15 minutes by setting idle_time to 15:

SQL > alter profile senior_claim_analyst limit connect_time 100 sessions_per_user 20 idle_time 15;

ORA-03114: not connected to ORACLE

The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,'00:

Shutting down Concurrent Manager : 03-OCT-2016 09:11:42

ORA-03114: not connected to ORACLE

ORACLE error 3114 in AFPRSR-Resubmit_Time

Cause:

AFPRSR-Resubmit_Time failed due to ORA-03114: not connected to ORACLE

.The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,'00:

List of errors encountered:

_ 1 _

Routine AFPCMT encountered an ORACLE error. ORA-03114: not connected

to ORACLE

.Review your error messages for the cause of the error. (=<POINTER>)

_ 2 _

Routine AFPPRD has encountered a fatal error. ORA-03114: not connected

to ORACLE

Solutions:

There are multiple scenarios that cause this error. Check the following list of solutions:

Lets devide it into two scenarios where we get this error.

1.DB Connection issue :-

2. Verify there is no firewall or router setting which terminates connections which are active for longer than x minutes.

3. Verify there is no firewall or router setting which terminates connections which are idle for longer than x minutes.

3. Verify the Oracle configuration. Let the database send a packet every x minutes, so that the firewall,

router, etc does not close the connection due to being 'idle'.

odaadmcli hardware monitoring commands

Use the odaadmcli hardware monitoring commands to display hardware configurations.

odaadmcli show cooling

Use the odaadmcli show cooling command to show cooling details.

odaadmcli show env_hw

Use the odaadmcli show env_hw command to display information about the environment and hardware.

odaadmcli show fs

Use the odaadmcli show fs command to display filesystem details.

odaadmcli show memory

Use the odaadmcli show memory command to display memory details.

odaadmcli show network

Use the odaadmcli show network command to show network details.

odaadmcli show power

Use the odaadmcli show power command to display power supply details.

odaadmcli show processor

Use the odaadmcli show processor command to display processor details.

odaadmcli show server

Use the odaadmcli show server command to display server details.

ORA-39087: directory name EXPDP is invalid

This error may be returned if you are trying to export or import using oracle data pump,

but invalid directory name has been provided during this process.

[oracle@ora-dev EXPDP]$ expdp system/*****@TEST schemas=test1 directory=EXPDP dumpfile=test1.dmp log=test1.log

Export: Release 12.2.0.1.0 - Production on Thu Oct 29 16:40:44 2020

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

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-39087: directory name EXPDP is invalid

Solution :-

In case you are sure that you have specified a valid directory name, same error would be returned if “oracle” user doing the export or import does not have read/write rights on the directory at OS or oracle level.

You must make sure following for a successful expdp or impdp without ORA-39087

Directory exists at OS level.
Oracle software owner has read/write privileges on the OS level directory.
The user doing export/import has been granted rights on directory created at oracle level, as follows

You need to grant read, write on directory MY_DATA_PUMP_DIRECTORY to system and your myschema.

Here are the two steps you must take to prepare the directory:

Grant "create any directory" privilege to the export/import administrator.

Login as sysdba

SQL> grant create any directory to system;

Grant succeeded.

Create a directory for dump file set and log file.

SQL> create directory EXPDP as '/u01/oracle/EXPDP';

Directory created.

Retest issue.

ASM Command-Line Utility (ASMCMD)

What is ASMCMD Utility?

ASMCMD is a command-line utility that you can use to easily view and manipulate files and directories within Automatic Storage Management (ASM) disk groups. It can list the contents of disk groups, perform searches, create and remove directories and aliases, display space utilization, and more.

ASMCMD is a command line utility for managing files and directories in ASM (Automatic Storage Management ) disk-groups. It offers Unix-style filesystem commands for managing ASM directories and files. Prior to Oracle 10gR2, ASM had to be administered via OEM or SQL statements while connected to a special ASM instance.

Note that ASMCMD cannot be used to create or drop diskgroups, or to add or drop disks in a disk group. These ASM administrative tasks must be accomplished via SQL commands.

ASMCMD offers two modes of operation: interactive and non-interactive. Interactive mode provides a shell-like environment for executing ASMCMD commands. It resembles how SQL*Plus interactive mode works and looks like this:

Note:

You cannot use ASMCMD to create or drop disk groups or to add or drop disks in a disk group. For these operations, you must use SQL commands.

Summary of ASM Commands

Command

Description

Changes the current directory to the specified directory.

Displays the total disk space occupied by ASM files in the specified ASM directory and all its subdirectories, recursively.

exit

Exits ASMCMD.

find

Lists the paths of all occurrences of the specified name (with wildcards) under the specified directory.

help

Displays the syntax and description of ASMCMD commands.

Lists the contents of an ASM directory, the attributes of the specified file, or the names and attributes of all disk groups.

lsct

Lists information about current ASM clients.

lsdg

Lists all disk groups and their attributes.

mkalias

Creates an alias for a system-generated filename.

mkdir

Creates ASM directories.

pwd

Displays the path of the current ASM directory.

Deletes the specified ASM files or directories.

rmalias

Deletes the specified alias, retaining the file that the alias points to.

Summary of ASMCMD Disk group management commands

Command	Description
chdg	Changes a disk group (add, drop, or rebalance).
chkdg	Checks or repairs a disk group.
dropdg	Drops a disk group.
iostat	Displays I/O statistics for disks.
lsattr	Lists the attributes of a disk group.
lsdg	Lists disk groups and their information.
lsdsk	Lists disks Oracle ASM disks.
lsod	Lists open devices.
md_backup	Creates a backup of the metadata of mounted disk groups.
md_restore	Restores disk groups from a backup of the metadata.
mkdg	Creates a disk group.
mount	Mounts a disk group.
offline	Offlines a disk or a failure group.
online	Onlines a disk or a failure group.
rebal	Rebalances a disk group.
remap	Relocates data in a range of physical blocks on a disk.
setattr	Sets attributes in a disk group.
stamp	Stamps the disk, site, and failure group labels in disk headers.
stamplist	Displays the disk, site, and failure group labels in disk headers.
umount	Dismounts a disk group.

Preparing to Run ASMCMD

Before running ASMCMD, check the following:

To use most of the ASMCMD commands, ensure that the ASM instance is started and the ASM disk groups are mounted.

If the ASM instance is not running or if the ORACLE_SID is set incorrectly, ASMCMD will still run,

but only those commands that do not require an ASM instance will run. The commands are lsdsk, help, and exit.

If you attempt to run other ASMCMD commands, an error message displays.

Running ASMCMD in Interactive Mode

The interactive mode of the ASMCMD utility provides a shell-like environment where you are prompted to enter ASMCMD commands.

To run ASMCMD in interactive mode:

[grid@ora-prod]$ asmcmd

ASMCMD> ls -l

State Type Rebal Name

MOUNTED EXTERN N DATA/

MOUNTED EXTERN N RECO/

ASMCMD>

ASMCMD> pwd

+DATA/ora_data/19744798347947/DATAFILE

ASMCMD> ls -l

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.348.1009978273

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.354.1013346191

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.363.1022606279

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.366.1017318075

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.370.1019732645

DATAFILE UNPROT COARSE OCT 26 21:00:00 Y SYSTEM.374.1024834305

To list the diskgroups we have.

ASMCMD> lsdg

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 1048576 376496 0 376496 0 Y DATA/

MOUNTED EXTERN N 512 512 4096 4194304 262144 258856 0 258856 0 N RECO/

ASMCMD> lsct

DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group

+APX CONNECTED 12.2.0.1.0 12.2.0.1.0 +APX1 DATA

_OCR CONNECTED - - ora-prod DATA

prod CONNECTED 12.2.0.1.0 12.2.0.1.0 prod DATA

prod CONNECTED 12.2.0.1.0 12.2.0.1.0 prod RECO

ASMCMD>

ASMCMD> du DATA/

Used_MB Mirror_used_MB

666788 666788

ASMCMD> du RECO/

Used_MB Mirror_used_MB

3160 3160

To check details of all the diskgroups, i.e. Mounted and Dismounted, We would use lsdg command with ‘ – – discovery’ argument.

ASMCMD> lsdg --discovery

State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 512 4096 4194304 1048576 376496 0 376496 0 Y DATA/

MOUNTED EXTERN N 512 512 4096 4194304 262144 258856 0 258856 0 N RECO/

ASMCMD>

To list Mounted ASM disks.

ASMCMD> lsdsk

Path

/dev/DATADISK5

/dev/DATADISK6

/dev/DATADISK7

/dev/DATADISK8

/dev/RECODISK1

/dev/RECODISK2

/dev/RECODISK3

/dev/RECODISK4

ASMCMD>

To check details of ASM disks.

ASMCMD> lsdsk -k

Total_MB Free_MB OS_MB Name Failgroup Site_Name Site_GUID Site_Status Failgroup_Type Library Label Failgroup_Label Site_Label UDID Product Redund Path

262144 94092 262144 DATA_0004 DATA_0004 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/DATADISK5

262144 94156 262144 DATA_0005 DATA_0005 00000000000000000000000000000000 REGULAR System UNKNOWN /dev/DATADISK6

Let’s check ASMCMD’s version.

[oracle@ora ~]$ asmcmd -V

asmcmd version 11.2.0.0.0

To list a particular diskgroup.

ASMCMD> lsdg DATA

To list Mounted ASM disks.

ASMCMD> lsdsk

To check details of ASM disks.

ASMCMD> lsdsk -k

To get Input/Output State details of disks.

ASMCMD> iostat

To check above details for a particular Disk.

ASMCMD> iostat -G FRA

To retrieve information of Reads/Writes Error.

ASMCMD> iostat -e

To get Time Statistics, Read Time & Write Time.

ASMCMD> iostat -e -t

To get information about OracleASM ServerParameter file.

ASMCMD> spget

To List the current directory.

ASMCMD> ls -l

To run ASMCMD in noninteractive mode:

At the command prompt, enter:

asmcmd command arguments

where:

command is any valid ASMCMD command. arguments is a list of command flags and arguments.

The following example demonstrates running ASMCMD in the noninteractive mode.

[grid@ora-prod ~]$ asmcmd ls -l

State Type Rebal Unbal Name

MOUNTED NORMAL N N DGROUP1/

MOUNTED NORMAL N N DGROUP2/

Rebalance from asmcmd :

Now we can do rebalance of diskgroup from asmcmd prompt also.

To rebalance a diskgroup with power limit of 100

SYNTAX –

rebal –power < POWER_LIMIT> < DISKGROUP_NAME>

ASMCMD> rebal --power 100 B2BWMDB

Rebal on progress.

To monitor the rebalance operation:

ASMCMD> lsop

Group_Name Pass State Power EST_WORK EST_RATE EST_TIME

B2BWMDB COMPACT WAIT 1 0 0 0

B2BWMDB REBALANCE RUN 1 12072 4400 2

Miscellaneous asmcmd commands:

Check the patches installed in grid home:

ASMCMD> showpatches

Check asm instance version:

ASMCMD> showversion

Check the cluster mode:

ASMCMD> showclustermode

ASM cluster : Flex mode disabled

Check the cluster state:

ASMCMD> showclusterstate

Normal

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

SQL> set lines 150

column path format a30

column DiskGroup format a15

column DiskName format a30

col free_mb for 999,999,999

compute sum of total_mb on DiskGroup

compute sum of free_mb on DiskGroup

break on DiskGroup skip 1 on report

set pages 255

SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB", free_mb/total_mb*100 "%FREE" FROM v$asm_diskgroup;

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

select a.name DiskGroup,b.disk_number Disk#,b.name DiskName,b.total_mb,

b.free_mb,

b.path,

b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

and b.header_status != 'FOREIGN'

order by b.group_number, b.disk_number, b.name;

ASM related SQL scripts 12c

[oracle@ora-prod cleandb]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 27 11:22:10 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

scripts-1

SQL> SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB", free_mb/total_mb*100 "%FREE" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB USED_MB %FREE

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

DATA 376496 1048576 672080 35.9054565

RECO 258856 262144 3288 98.7457275

scripts-2

SQL> SELECT name, free_mb, total_mb, total_mb-free_mb "USED_MB" FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB USED_MB

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

DATA 376496 1048576 672080

RECO 258856 262144 3288

[grid@ora-prod ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 27 11:19:43 2020

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set lines 150

SQL> column path format a40

SQL> column name format a30

SQL> select name,path,mount_status,header_status,mode_status,state,total_mb,free_mb from v$asm_disk order by 2;

NAME PATH MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB FREE_MB

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

DATA_0004 /dev/DATADISK5 CACHED MEMBER ONLINE NORMAL 262144 94092

DATA_0005 /dev/DATADISK6 CACHED MEMBER ONLINE NORMAL 262144 94156

DATA_0006 /dev/DATADISK7 CACHED MEMBER ONLINE NORMAL 262144 94132

DATA_0007 /dev/DATADISK8 CACHED MEMBER ONLINE NORMAL 262144 94116

RECODISK1 /dev/RECODISK1 CACHED MEMBER ONLINE NORMAL 65536 64720

RECODISK2 /dev/RECODISK2 CACHED MEMBER ONLINE NORMAL 65536 64704

RECODISK3 /dev/RECODISK3 CACHED MEMBER ONLINE NORMAL 65536 64716

RECODISK4 /dev/RECODISK4 CACHED MEMBER ONLINE NORMAL 65536 64716

8 rows selected.

SQL> select name,path, group_number group_#, disk_number disk_#, mount_status,header_status, state, total_mb, free_mb from v$asm_disk order by group_number;

NAME PATH GROUP_# DISK_# MOUNT_S HEADER_STATU STATE TOTAL_MB FREE_MB

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

DATA_0005 /dev/DATADISK6 1 5 CACHED MEMBER NORMAL 262144 94156

DATA_0006 /dev/DATADISK7 1 6 CACHED MEMBER NORMAL 262144 94132

DATA_0004 /dev/DATADISK5 1 4 CACHED MEMBER NORMAL 262144 94092

DATA_0007 /dev/DATADISK8 1 7 CACHED MEMBER NORMAL 262144 94116

RECODISK1 /dev/RECODISK1 2 0 CACHED MEMBER NORMAL 65536 64720

RECODISK3 /dev/RECODISK3 2 2 CACHED MEMBER NORMAL 65536 64716

RECODISK2 /dev/RECODISK2 2 1 CACHED MEMBER NORMAL 65536 64704

RECODISK4 /dev/RECODISK4 2 3 CACHED MEMBER NORMAL 65536 64716

8 rows selected.

SQL>

ASM diskgroup number and file number for your redo logs.

SQL> select member from v$logfile;

MEMBER

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

+RECO/prod_ora/ONLINELOG/group_3.259.968631061

+RECO/prod_ora/ONLINELOG/group_2.258.968631059

+RECO/prod_ora/ONLINELOG/group_1.257.968631059

SQL>

SQL> select name, path from v$asm_disk;

NAME PATH

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

DATA_0005 /dev/DATADISK6

RECODISK4 /dev/RECODISK4

DATA_0007 /dev/DATADISK8

DATA_0006 /dev/DATADISK7

DATA_0004 /dev/DATADISK5

RECODISK2 /dev/RECODISK2

RECODISK1 /dev/RECODISK1

RECODISK3 /dev/RECODISK3

8 rows selected.

SQL>

Reference:-

https://vidyanathdba.wordpress.com/2017/07/28/asm-queries/

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.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error is raised when the following statement is executed against the Oracle Database.

SQL> truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Cause:

The statement requires an exclusive lock on the table.If the table is active and has DML or DDL transactions against it the transaction will not be able to acquire the lock quickly. This will cause the transaction to fail and return the resource busy error to the session.

Solution:

Using DDL_LOCK_TIMEOUT

You can increased the timeout by modifying the DDL_LOCK_TIMEOUT parameter in your session. Once this is set Oracle will wait for the new TIMEOUT before returning the “ORA-00054: resource busy and acquire with NOWAIT specified” error.

[ora@sonu ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET ddl_lock_timeout=900;

Session altered.

ORA-28365: wallet is not open encountered below issue while Starting the Database

SQL> startup
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size 12170960 bytes
Variable Size 2818574640 bytes
Database Buffers 1.8589E+10 bytes
Redo Buffers 54935552 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-28365: wallet is not open
Process ID: 12599
Session ID: 260 Serial number: 21611

Solution:
Start the instance in mount mode

[oracle@ora]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 10 17:02:32 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size 12170960 bytes
Variable Size 2818574640 bytes
Database Buffers 1.8589E+10 bytes
Redo Buffers 54935552 bytes
Database mounted.

Check Wallet status
SQL> select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

STATUS
------------------------------
WALLET_DIR
--------------------------------------------------------------------------------
WALLET_TYPE
--------------------
CLOSED
/apps/DEV/product/12.2.0/dbhome_1/admin/DEV/wallet/
UNKNOWN

SQL> administer key management set keystore open identified by "tde1986";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR ULLY_BAC
CON_ID FILE
/apps/DEV/product/12.2.0/dbhome_1/admin/DEV/wallet/ OPEN PASSWORD SINGLE NO 0

SQL> alter database open;

Database altered.