Internal error while querying the Host Monitor

Cause:

Graphs are not displayed in the cloudera dashboard and throwing error after starting the cluster

Solution:

Click on the cloudera management service and click restart.

It take sometime for the realtime graphs to reflect on the dashboard!

-bash: locate: command not found (Solved)

Solution:

[root@ip-xxx ~]# which locate
/usr/bin/which: no locate in (/usr/local/sbin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)

Use yum to install mlocate package

[root@ip-xxx ~]# yum install mlocate -y
Loaded plugins: amazon-id, rhui-lb, search-disabled-repos
cloudera-manager                                                                                                                                                      |  951 B  00:00:00
mysql-connectors-community                                                                                                                                            | 2.6 kB  00:00:00
mysql-tools-community                                                                                                                                                 | 2.6 kB  00:00:00
mysql56-community                                                                                                                                                     | 2.6 kB  00:00:00
rhui-REGION-client-config-server-7                                                                                                                                    | 2.1 kB  00:00:00
rhui-REGION-rhel-server-releases                                                                                                                                      | 2.0 kB  00:00:00
rhui-REGION-rhel-server-rh-common                                                                                                                                     | 2.1 kB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mlocate.x86_64 0:0.26-8.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package                               Arch                                 Version                                     Repository                                                      Size
=============================================================================================================================================================================================
Installing:
 mlocate                               x86_64                               0.26-8.el7                                  rhui-REGION-rhel-server-releases                               113 k

Transaction Summary
=============================================================================================================================================================================================
Install  1 Package

Total download size: 113 k
Installed size: 379 k
Downloading packages:
mlocate-0.26-8.el7.x86_64.rpm                                                                                                                                         | 113 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mlocate-0.26-8.el7.x86_64                                                                                                                                                 1/1
  Verifying  : mlocate-0.26-8.el7.x86_64                                                                                                                                                 1/1

Installed:
  mlocate.x86_64 0:0.26-8.el7

Complete!

Update the locate repository

[root@ip-xxx ~]# updatedb

/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.

Cause:

While executing export data from HDFS to RDBMS using sqoop ,because of non existing accumulo home,this error is triggered in hadoop

[root@ip-xxx ~]# [root@ip-172-31-60-27 ~]# sqoop export --connect jdbc:mysql://localhost:3306/tes                                                                                                             t \
> --table t \
> --username root --password 123 \
> --export-dir root/data \
> --fields-terminated-by ',' --lines-terminated-by '\n'

Warning: /opt/cloudera/parcels/CDH-5.15.1-1.cdh5.15.1.p0.4/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
21/01/15 18:48:01 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.15.1
Missing argument for option: connect

Solution:

Make a accumulo home directory in var/lib

Add the environment variable for accumulo home in the /etc/profile file

[root@ip-xxx ~]# mkdir -p /var/lib/accumulo
[root@ip-xxx~]# vi /etc/profile
[root@ip-xxx ~]# cat /etc/profile|grep 'ACCU'
ACCUMULO_HOME=/var/lib/accumulo
export ACCUMULO_HOME
[root@ip-xxx ~]# source /etc/profile

ORA-39123: Data Pump transportable tablespace job aborted;ORA-39185: The transportable tablespace failure list is ORA-29335: tablespace ‘EXAMPLE’ is not read only

Cause:

29335, 00000, "tablespace '%s' is not read only"
// *Cause: The tablespace is not read only.
// *Action: Make the tablespace read only and retry the operation.

Transportable tablespace got failed during export due to read write tablespaces

Solution:

Check the tablespace for self containment without dependent data

SQL> exec dbms_tts.transport_set_check('EXAMPLE',true);

PL/SQL procedure successfully completed.

Make the tablespace readonly to maintain consistency of data

SQL> exec dbms_tts.transport_set_check('EXAMPLE',true);

PL/SQL procedure successfully completed.

Start the export

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** parfile=ttbs.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /tmp/ttbs.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  /app01/EXAMPLE12625.dbf
  /app01/EXAMPLE6345.dbf
  /app01/EXAMPLE7187.dbf
  /app01/oracle/product/base/oradata/orcl12x/example01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Jan 13 16:22:24 2021 elapsed 0 00:00:14

Export is successful !

~~~Exadata health check script~~~

An exclusive script is required sometimes to perform a rapid triage for a critical issue

This script provides a basic health status,resource statistics and other errors of exadata cell nodes and infiniband switches

Test the script in test environment and customize to type of exadata rack before implementing in production

Use it at your own risk!

#!/bin/bash

####################################################################################
#                          EXADATA HEALTH SCRIPT                                   #
#                            AUTHOR~Kishan M                                       #
####################################################################################


#                                 1/8th RACK 

####################################################################################
#                               DESCRIPTION                                        #
# This script will generate the status report from  Exadata cell and switch        #
####################################################################################


#print the title

_title()
{
     echo "                     *** -------------------------------- ***"
     echo "                     ***       EXADATA HEALTH CHECK       ***"
     echo "                     *** -------------------------------- ***"
}

#log

_log()
{
     echo "*** $1"
}

#verify_user
_verify_user()
{
   if [ $(id -u) != 0 ]; then
       _log "Run as root user"
       exit 1
   fi
}
#Set environment
_env()
{
_log "*** Exporting environment variables *** "
          export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/11.2.0.4/dbhome_1/bin:/u01/app/12.2.0.1/grid/bin:/u01/app/12.2.0.1/grid/OPatch:/u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch:/u01/app/oracle/ggs_home
CG=/root/cell_group;SG=/root/switch_group;DG=/root/dbs_group;CELLLOG=/tmp/cell.log;IBLOG=/tmp/iberrors.log;HDD=/tmp/hdd.err;FDD=/tmp/fdd.err;IH=/tmp/IMAGEHISTORY.log
export CG;export SG;export CELLLOG;export IBLOG;export HDD;export FDD;export IH
_log "LOGFILES";echo $CELLLOG;echo $IBLOG;echo $HDD;echo $FDD;echo $IH
}

#check file
_file_check(){
   ls "$CELLLOG" "$IBLOG" "$FDD" "$HDD" >/dev/null 2>&1 && echo '***'
   if [ $? -eq 0 ]
   then
     echo ''
   else
     touch $CELLLOG $IBLOG $FDD $HDD
   fi
}



#Cell
_cell_monitor()
{
echo "#################################################################################"
echo "#                              CELL HEALTH CHECK                                #"
echo "#################################################################################"

_log " Checking the cell status  ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list cell"
echo "---------------------------------------------------------------------------------"
_log " Checking cell processes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root "   hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep '(cellsrvStatus)|(msStatus)|(rsStatus)'"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log " Cumulative CPU utilization on each cell ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CL_CPUT"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log " Top 5 processes consuming CPU in compute nodes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $DG -l root "   hostname;ps -eo pid,ppid,cmd,%cpu --sort=-%cpu|head -6"|awk '{$1="";print}'|column -t|grep -v "ps"
echo "---------------------------------------------------------------------------------"
_log " Top 5 processes consuming CPU in cell nodes ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root "   hostname;ps -eo pid,ppid,cmd,%cpu --sort=-%cpu|head -6"|awk '{$1="";print}'|column -t |grep -v "ps"
echo "---------------------------------------------------------------------------------"
_log " Memory utilization on each cell"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CL_MEMUT"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
#_log " IOPS on each cell"
#/usr/local/bin/dcli -g $CG -l root cellcli -e "list metriccurrent CD_IO_UTIL"
echo "#################################################################################"
_log " Checking abnormal metrics please wait for sometime ***"
echo "                                                       "
echo "#################################################################################"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list metrichistory where alertState!=\'Normal\'"
echo "#################################################################################"
}

_cell_error_check()
{
_log "   Checking the critical alerts in the cell ***"
echo "#################################################################################"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list alerthistory attributes name,alertMessage,beginTime,endTime where severity = 'critical' detail;"|tail -10 >$CELLLOG && cat $CELLLOG
grep 'alertMessage' $CELLLOG >/dev/null 2>&1
if [ $? -ne 0 ]
then
echo "           *** No cell alerts for now ***"
fi
echo "#################################################################################"
}
#Physical Disk
_hard_disk_check()
{
_log " Checking the abnormal physical disk ***"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list physicaldisk attributes name,id,slotnumber where disktype=\"harddisk\" and status!=\'normal\'" >$HDD && cat $HDD
egrep -i 'poor|predictive' $HDD >/dev/null 2>&1
if [ $? -ne 0 ];then
echo "            *** No hard disk issue ***"
fi
echo "#################################################################################"
}
#Flash Disk
_flash_disk_check()
{
echo "              ***   Flashcache Mode  *** "
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root "    hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep 'flashCacheMode'"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo "              *** Flashcache used in cell nodes *** "
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g /root/cell_group -l root "    hostname;echo "-----------------------";cellcli -e "list metriccurrent where name = 'FC_BY_USED'""|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo "               Checking abnormal flash disk ***"
echo "---------------------------------------------------------------------------------"
/usr/local/bin/dcli -g $CG -l root cellcli -e "list physicaldisk attributes name,id,slotnumber where disktype=\"flashdisk\" and status!=\'normal\'" >$FDD && cat $FDD
egrep -i 'poor|predictive' $FDD >/dev/null 2>&1
if [ $? -ne 0 ];then
echo "           *** No flash disk issue ***"
fi
echo "---------------------------------------------------------------------------------"
}

#Switch
_ib_port_check()
{
echo "#################################################################################"
echo "#                           EXADATA SWITCH PORT HEALTH                          #"
echo "#################################################################################"

echo "       ***   Checking the ports status on the switches ***"
/usr/local/bin/dcli -g $SG -l root "listlinkup"|grep -v '13A\|14A\|15A\|8B\|12B\|15B'|grep 'down' >$IBLOG && cat $IBLOG
grep 'down' $IBLOG >/dev/null 2>&1
if [ $? -ne 0 ];then
echo "---------------------------------------------------------------------------------"
echo "#                            Ports are up!"                                     #
fi
echo "---------------------------------------------------------------------------------"
}
_ib_status(){
echo "#################################################################################"
ibstatus
}
echo "#################################################################################"
echo "                                                                                 "
echo "                                                                                 "
echo "---------------------------------------------------------------------------------"
#Diagnostic Reports
_sundiag_report()
{
while true
  do
  read -p "Do you want to run sundiag report on all cellnodes: Y/N?" I
    case $I in
  [Yy]* ) _log "Running sundiag report! please wait ***"; /usr/local/bin/dcli -g $CG -l root "/opt/oracle.SupportTools/sundiag.sh"; break;;
  [Nn]* ) _log "=====>Exiting Report";  break;;
  * ) echo "Please choose either Yes(Y) or No(N)";;
    esac
  done
}
echo "                                                                                 "

#exacheck version
_exa_chk_version(){
_log "Please verify if you have the latest exachk utility version from MOS"
echo "#################################################################################"
echo "                                                                                 "
/opt/oracle.ahf/exachk -v
echo "                                                                                 "
echo "#################################################################################"
}
_exa_chk()
{
echo "Do want to run exa_chk report? Choose 1 or 2"
select I in "Yes" "No"
  do
    case $I in
  Yes ) _log "Running exachk report! please wait ***";/opt/oracle.ahf/exachk; break;;
  No ) _log "=====>Exiting Report";  break;;
    esac
  done
}
echo "                                                                                 "
echo "                                                                                 "

#if you face error you can login to any of the nodes
_host_logon()
{
echo '***Do you login to any host? Choose a valid option!***'
hosts=("exacel01" "exacel02" "exacel03" "exacel04" "exasw-ibb01" "exasw-iba01" "Exit")
select h in "${hosts[@]}";do
 case $h in
 "exacel01")
  ssh exacel01
  ;;
 "exacel02")
  ssh exacel02
  ;;
 "exacel03")
  ssh exacel03
  ;;
 "exacel04")
  ssh exacel04
  ;;
 "exasw-ibb01")
  ssh exasw-ibb01
  ;;
 "exasw-iba01")
  ssh exasw-iba01
  ;;
 "Exit")
  _log "=====>End of script"; break
  ;;
*) echo "Enter correct hostname";;
 esac
done
}
echo "                                                                                 "
echo "                                                                                 "
_other_stats()
{
_log "Fan status"
echo "---------------------------------------------------------------------------------"
echo "                                                                                 "
/usr/local/bin/dcli -g $CG -l root "    hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep fan"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log "Power status"
echo "---------------------------------------------------------------------------------"
echo "                                                                                 "
/usr/local/bin/dcli -g $CG -l root "    hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep power"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
_log "makeModel of cell"
echo "                                                                                 "
echo "---------------------------------------------------------------------------------"

/usr/local/bin/dcli -g $CG -l root "    hostname;echo "-----------------------";cellcli -e "list cell detail"|egrep makeModel"|awk '{$1="";print}'
echo "---------------------------------------------------------------------------------"
echo "                                                                                 "
}

echo "                                                                                 "
echo "---------------------------------------------------------------------------------"

_imagehistory_info()
{
_log "Imagehistory details will be dumped to $IH file"
/usr/local/bin/dcli -g $DG -l root "hostname;echo "-----------------------";imagehistory -all"|awk '{$1="";print}'
/usr/local/bin/dcli -g $CG -l root "hostname;echo "-----------------------";imagehistory -all"|awk '{$1="";print}'
}

_title
_env
_verify_user
_file_check
_cell_monitor
_cell_error_check
_hard_disk_check
_flash_disk_check
_ib_port_check
_ib_status
_other_stats
_imagehistory_info > $IH
_sundiag_report
_exa_chk
_host_logon

Hope you like this script !!

PRCR-1079 : Failed to start resource ora.asm;CRS-5017: The resource action “ora.asm start” encountered the following error:;ORA-56865: Invalid IP address in CELLINIT.ORA;. For details refer to “(:CLSN00107:)” in “/apps01/base/product/11.2.0/grid/log/ exdbadm01/agent/ohasd/oraagent_oracle/oraagent_oracle.log”. (EXADATA)

Cause:

ASM cant start because of miscommunication between compute and cell nodes

[oracle@exdbadm01 ~]$ oerr ora 56865
56865, 00000, "Invalid IP address in CELLINIT.ORA"
// *Document: YES
// *Cause:   One or more of the specified IP addresses in CELLINIT.ORA is not
//           valid.
// *Action:  Check that all IP addresses in CELLINIT.ORA are valid.

Solution:

Verify the ipaddress in cellinit.ora file on all cell nodes

[root@exceladm00 celladmin]# cat /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/cellsrv/deploy/config/cellinit.ora
#CELL Initialization Parameters
version=0.0
HTTP_PORT=8888
bbuChargeThreshold=800
SSL_PORT=23943
RMI_PORT=23791
ipaddress1=10.10.1.11/24
bbuTempThreshold=60
DEPLOYED=TRUE
JMS_PORT=9127
BMC_SNMP_PORT=162

Check if the rds protocols are enabled on the cellnodes

[root@exceladm00 celladmin]# lsmod|grep rds
rds_rdma              106561  0
rds_tcp                48097  0
rds                   155561  260 rds_rdma,rds_tcp
rdma_cm                73429  2 rds_rdma,ib_iser
ib_core               108097  8 rds_rdma,ib_iser,rdma_cm,ib_cm,iw_cm,ib_sa,ib_mad,iw_cxgb3

If not enabled,then start the protocols

[root@exceladm00 celladmin]# /sbin/modprobe rds-tcp
[root@exceladm00 celladmin]# /sbin/modprobe rds_rdma
[root@exceladm00 celladmin]#  modprobe rds

Verify that all the griddisks in cell node are visible from compute node

[root@exdbadm01 oracle]#  /grid/stage/ext/bin/kfod op=disks disk=all
--------------------------------------------------------------------------------
 Disk          Size Path                                     User     Group
================================================================================
   1:        976 Mb o/192.168.56.69/DATA_CD_cell01_exceladm00 <unknown> <unknown>
   2:        976 Mb o/192.168.56.69/DATA_CD_cell02_exceladm00 <unknown> <unknown>
   3:        976 Mb o/192.168.56.69/DATA_CD_cell03_exceladm00 <unknown> <unknown>
   4:        976 Mb o/192.168.56.69/DATA_CD_cell04_exceladm00 <unknown> <unknown>
   5:        976 Mb o/192.168.56.69/DATA_CD_cell05_exceladm00 <unknown> <unknown>
   6:        976 Mb o/192.168.56.69/DATA_CD_cell06_exceladm00 <unknown> <unknown>
   7:        976 Mb o/192.168.56.69/FRA_CD_cell07_exceladm00 <unknown> <unknown>
   8:        976 Mb o/192.168.56.69/FRA_CD_cell08_exceladm00 <unknown> <unknown>
   9:        976 Mb o/192.168.56.69/FRA_CD_cell09_exceladm00 <unknown> <unknown>
  10:        976 Mb o/192.168.56.69/FRA_CD_cell10_exceladm00 <unknown> <unknown>
  11:        976 Mb o/192.168.56.69/MGMT_CD_cell11_exceladm00 <unknown> <unknown>
  12:        976 Mb o/192.168.56.69/MGMT_CD_cell12_exceladm00 <unknown> <unknown>

Start the asm

[oracle@exdbadm01 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM
The Oracle base has been set to /apps01/base
[oracle@exdbadm01 ~]$ srvctl start asm

Check status of asm

[root@exdbadm01 oracle]# srvctl status asm
ASM is running on exdbadm01

ORA-02096: specified initialization parameter is not modifiable with this option

Cause:

You try to modify a parameter online without saving the changes to spfile

02096, 00000, "specified initialization parameter is not modifiable with this option"
// *Cause: Though the initialization parameter is modifiable, it cannot be
//         modified using the specified command.
// *Action: Check the DBA guide for information about under what scope
//          the parameter may be modified

Solution:

There are two types of parameters

one is static and other is dynamic

Dynamic parameters can be modified,when the instance is up and running without a database bounce and can be modified at memory level

Static parameters can be modified,when the instance is up and running but require a reboot for the changes to get reflected on database and it can be modified only at spfile level not in memory

For example,you can see the parameters which can and cant be modified in ISINSTANCE_MODIFIABLE column of v$parameter view

SQL> set lines 200
SQL> set pages 1000
SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter;

NAME                                                                             ISINS <------
-------------------------------------------------------------------------------- -----
lock_name_space                                                                  FALSE(static)
processes                                                                        FALSE(static)
sessions                                                                         TRUE(dynamic)

If i try to modify the static parameter,i get the error

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     200


SQL> alter system set processes=300;
alter system set processes=300
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified <---------


SQL> alter system set processes=300 scope=spfile; <-------

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1572861600 bytes
Fixed Size                  8910496 bytes
Variable Size            1132462080 bytes
Database Buffers          423624704 bytes
Redo Buffers                7864320 bytes
Database mounted.
Database opened.

In alertlog, you can monitor the number of process gets modified with the value which was set earlier

[oracle@orcl19x ~]$ tail -f /data01/base/diag/rdbms/orcl19x1/orcl19x1/trace/alert_orcl19x1.log|grep processes
Shutting down archive processes
  processes                = 300 <-----

I can modify the dynamic parameter in fact without a reboot!

SQL> select name,ISINSTANCE_MODIFIABLE from v$parameter where name='optimizer_use_invisible_indexes';

NAME                                                                             ISINS
-------------------------------------------------------------------------------- -----
optimizer_use_invisible_indexes                                                  TRUE <---(Dynamic)


SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
SQL> alter system set optimizer_use_invisible_indexes=FALSE;

System altered.

SQL> show parameter optimizer_use_invisible_indexes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

PLS-00201: identifier ‘DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION’ must be declared

Cause:

Appropriate permissions are not in place for the procedure,function or package for user!

SQL> exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=                         >TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=>TRU                         E); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL>  exec dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE);
BEGIN dbms_system.set_sql_trace_in_session(sid=>123,serial#=>1234,sql_trace=TRUE); END;

                                                                  *
ERROR at line 1:
ORA-06550: line 1, column 67:
PLS-00201: identifier 'SQL_TRACE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution:

Even though the user has dba privilege,still the user couldnot execute the packages of SYS and you can also create a public synonym for this package

Grant the privilege using sysdba account

SQL> conn / as sysdba
Connected.

SQL> create public synonym dbms_system for dbms_system;

Synonym created.

SQL> grant execute on dbms_system to test;

Grant succeeded.

SQL>  grant all on dbms_system to test;

Grant succeeded.

Check the privilege given

SQL> select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE from DBA_TAB_PRIVS where grantee='TEST';

'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE
--------------------------------------------------------------------------------
GRANT EXECUTE ON SYS.DBMS_SYSTEM TO TEST
GRANT DEBUG ON SYS.DBMS_SYSTEM TO TEST

It works fine now

SQL>  exec dbms_system.set_sql_trace_in_session(45,14530,TRUE);

PL/SQL procedure successfully completed.

SQL> show user
USER is "TEST"

ORA-01743: only pure functions can be indexed

Cause:

While creating function based index,you should give deterministic keyword for oracle to understand the function you create

You should not give sysdate,user etc.. inside a function

01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS).  SQL
//          expressions must not use SYSDATE, USER, USERENV(), or anything
//          else dependent on the session state.  NLS-dependent functions
//          are OK.

Solution:

The function you give to functional based index should return the same result for same parameters

eg:

Don’t

SQL> create index fn_order_date on sales(trunc(sysdate));
create index fn_order_date on sales(trunc(sysdate))
                                          *
ERROR at line 1:
ORA-01743: only pure functions can be indexed

Do

SQL> create index fn_order_date on sales(trunc(order_date));

Index created.

ORA-10631: SHRINK clause should not be specified for this object

Cause:

The solution for this error by oerr utility is not detailed

However,this issue is due to function based indexes existing on the table and we try to shrink the space in table to reduce fragmentation

SQL> !oerr ora 10631
10631, 00000, “SHRINK clause should not be specified for this object”
// *Cause: It is incorrect to issue shrink on the object
// *Action: Verify the object name and type and reissue the command

SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

Solution:

Only way to overcome this error is to drop the index and recreate it

Identify the function index and drop it

SQL> select index_name from dba_indexes where owner='TEST1' and table_name='SALES';

INDEX_NAME
--------------------------------------------------------------------------------
PK_SALES_ID
FN_ORDER_DATE

SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('INDEX','FN_ORDER_DATE') from dual;

DBMS_METADATA.GET_DDL('INDEX','FN_ORDER_DATE')
--------------------------------------------------------------------------------

  CREATE INDEX "TEST1"."FN_ORDER_DATE" ON "TEST1"."SALES" (TRUNC("ORDER_DATE"))

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

Drop the index

SQL> alter index test1.FN_ORDER_DATE invisible;

Index altered.

SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> alter index test1.FN_ORDER_DATE unusable;

Index altered.

SQL> alter table test1.sales shrink space;
alter table test1.sales shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

SQL> drop index test1.FN_ORDER_DATE;

Index dropped.

SQL> alter table test1.sales shrink space;

Table altered.

After space shrink,rebuild the index

SQL> create index fn_order_date on sales(trunc(order_date));

Index created.