Sujeet APPS DBA: October 2020

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