Month: January 2021

Uninstall oracle

· Leave a comment ·

Often,we install oracle database and database software in day to day life.But have you ever completely cleanup the old debris and software from your machine frequently? In oracle we call it as deinstall the software

Here i show a basic deinstall steps to gracefully wipe out all the database files and temporary files

Delete the database using dbca

choose existing database to delete

Confirm to delete the database

After database deletion deconfig the high availability service forcefully using the perl script in grid home 

[root@exdbadm01 apps01]# /apps01/base/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force
Using configuration parameter file: /apps01/base/product/11.2.0/grid/crs/install/crsconfig_params
CRS resources for listeners are still configured
PRKO-2573 : ONS daemon is already stopped.
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'exdbadm01'
CRS-2677: Stop of 'ora.FRA.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm01'
CRS-2677: Stop of 'ora.asm' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'exdbadm01'
CRS-2677: Stop of 'ora.cssd' on 'exdbadm01' succeeded
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.diskmon' on 'exdbadm01'
CRS-2677: Stop of 'ora.diskmon' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'exdbadm01'
CRS-2677: Stop of 'ora.evmd' on 'exdbadm01' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'exdbadm01' has completed
CRS-4133: Oracle High Availability Services has been stopped.
Successfully deconfigured Oracle Restart stack

Deinstall the grid software from grid home

[oracle@exdbadm01 dbhome_1]$ pwd
/data01/base/product/11.2.0/dbhome_1
[oracle@exdbadm01 dbhome_1]$ cd deinstall/
[oracle@exdbadm01 deinstall]$ ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /data01/orainventory/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############


######################### CHECK OPERATION START #########################
## [START] Install check configuration ##


Checking for existence of the Oracle home location /data01/base/product/11.2.0/dbhome_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /data01/base
Checking for existence of central inventory location /data01/orainventory
Checking for sufficient temp space availability on node(s) : 'exdbadm01'

## [END] Install check configuration ##


Network Configuration check config START

Network de-configuration trace file location: /data01/orainventory/logs/netdc_check2021-01-27_08-43-16-PM.log
clsc_ginit: CLSD initialization failed, rc -1, ecode 64

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: /data01/orainventory/logs/databasedc_check2021-01-27_08-43-17-PM.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [exdbx]:

###### For Database 'exdbx' ######

Specify the type of this database (1.Single Instance Database|2.Oracle Restart Enabled Database) [1]: 1
Specify the diagnostic destination location of the database [/data01/base/diag/rdbms/exdbx]:
Specify the storage type used by the Database ASM|FS []: ASM


Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /data01/orainventory/logs/emcadc_check2021-01-27_08-46-28-PM.log

Checking configuration for database exdbx
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /data01/orainventory/logs//ocm_check2031.log
Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################


####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /data01/base/product/11.2.0/dbhome_1
Inventory Location where the Oracle home registered is: /data01/orainventory
The following databases were selected for de-configuration : exdbx
Database unique name : exdbx
Storage used : ASM
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/data01/orainventory/logs/deinstall_deconfig2021-01-27_08-43-09-PM.out'
Any error messages from this session will be written to: '/data01/orainventory/logs/deinstall_deconfig2021-01-27_08-43-09-PM.err'

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: /data01/orainventory/logs/emcadc_clean2021-01-27_08-46-28-PM.log

Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /data01/orainventory/logs/databasedc_clean2021-01-27_10-26-45-PM.log
Database Clean Configuration START exdbx
This operation may take few minutes.
Database Clean Configuration END exdbx

Network Configuration clean config START

Network de-configuration trace file location: /data01/orainventory/logs/netdc_clean2021-01-27_10-26-59-PM.log

De-configuring backup files...
Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START
OCM clean log file location : /data01/orainventory/logs//ocm_clean2031.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START

Detach Oracle home '/data01/base/product/11.2.0/dbhome_1' from the central inventory on the local node : Done

Delete directory '/data01/base/product/11.2.0/dbhome_1' on the local node : Done

Delete directory '/data01/base' on the local node : Done

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END


## [START] Oracle install clean ##

Clean install operation removing temporary directory '/tmp/deinstall2021-01-27_08-41-48PM' on node 'exdbadm01'

## [END] Oracle install clean ##


######################### CLEAN OPERATION END #########################


####################### CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : exdbx
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/data01/base/product/11.2.0/dbhome_1' from the central inventory on the local node.
Successfully deleted directory '/data01/base/product/11.2.0/dbhome_1' on the local node.
Successfully deleted directory '/data01/base' on the local node.
Oracle Universal Installer cleanup was successful.

Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################


############# ORACLE DEINSTALL & DECONFIG TOOL END #############

Clean up the grid directories 

[root@exdbadm01 apps01]# ls
base  orainventory
[root@exdbadm01 apps01]# rm -rf base/

At last the asm disk headers entries should be erased if asm disks are reused

You can refer this post for more info

https://wordpress.com/post/alphaoragroup.com/1109

Now a fresh software can be installed

Could not validate ASMSNMP Password due to following error – “ORA-1031: insufficient privileges”

· Leave a comment ·

Cause:

This error happens when configuring OEM in database installation

The error occur due to missing ASMSNMP user which is used for monitoring ASM health from OEM

Check if user exist

[oracle@exdbadm01 bin]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /apps01/base
[oracle@exdbadm01 bin]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 26 12:38:25 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> alter user asmsnmp identified by password;
alter user asmsnmp identified by password
                                 *
ERROR at line 1:
ORA-01918: user 'ASMSNMP' does not exist

Workaround:

Create the user

SQL> create user ASMSNMP identified by password;
create user ASMSNMP identified by password
                                  *
ERROR at line 1:
ORA-01990: error opening password file
'/apps01/base/product/11.2.0/grid/dbs/orapw'

Check if password file exist

If password file exist,then ignore this step

[oracle@exdbadm01 bin]$ cd /apps01/base/product/11.2.0/grid/dbs/
[oracle@exdbadm01 dbs]$ ls -lrt *orapw*
ls: *orapw*: No such file or directory

Create a password file

[oracle@exdbadm01 dbs]$ orapwd file=orapw entries=30

Enter password for SYS:

Create the user now

[oracle@exdbadm01 dbs]$ sqlplus / as sysasm

SQL> create user ASMSNMP identified by password;

User created.

SQL> grant sysdba,sysoper to asmsnmp;

Grant succeeded.

Give the password of ASMSNMP ,when prompt during installation

You should not see the warning now in installation window

ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute/Describe)

· Leave a comment ·

Cause:

This error occur in ASM when diskmon resource is down

This is exadata test setup and this error is not seen in a real production system

ASMCMD> lsdg
ORA-03114: not connected to ORACLE (DBD ERROR: OCIStmtExecute/Describe)
[oracle@exdbadm01 ~]$ asmcmd
Connected to an idle instance.

There is no detail with error code

[oracle@exdbadm01 ~]$ oerr ora 03114
03114, 00000, "not connected to ORACLE"
// *Cause:
// *Action:

Solution:

When i check the status of asm,it is up and running

[oracle@exdbadm01 log]$ srvctl status asm
ASM is running on exdbadm01

But still i am unable to view the diskgroup details under asmcmd

ASMCMD> lsdg
ASMCMD>

After checking the cluster resources,diskmon resource was down

[oracle@exdbadm01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  OFFLINE      exdbadm01                STARTING
ora.FRA.dg
               ONLINE  OFFLINE      exdbadm01                STARTING
ora.LISTENER.lsnr
               ONLINE  ONLINE       exdbadm01
ora.asm
               ONLINE  ONLINE       exdbadm01                Started
ora.ons
               OFFLINE OFFLINE      exdbadm01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       exdbadm01
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       exdbadm01

When i checked the alertlog , i can see the error messages of diskmon not being loaded

01/23/21 19:34:27: Master Diskmon starting
skgzib_load_ib_symbols: libibmad lib file (/usr/lib64/libibmad.so) does not exists.

skgzib_ini: Fail to load infiniband symbols.

dskm_skgzib_ini: required inifiniband software is not found (libibmad, libibumad). SM Query will be disabled.

01/24/21 05:34:11: Master Diskmon starting
skgzib_load_ib_symbols: libibmad lib file (/usr/lib64/libibmad.so) does not exists.

skgzib_ini: Fail to load infiniband symbols.

dskm_skgzib_ini: required inifiniband software is not found (libibmad, libibumad). SM Query will be disabled.

01/25/21 18:38:08: Master Diskmon starting

Then i started the diskmon resource manually

[oracle@exdbadm01 ~]$ crsctl start res ora.diskmon
CRS-2672: Attempting to start 'ora.diskmon' on 'exdbadm01'
CRS-2676: Start of 'ora.diskmon' on 'exdbadm01' succeeded

Now i can able to view the diskgroups in asmcmd

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304      6144     5976                0            5976              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  4194304      6144     5976                0            5976              0             N  FRA/

In alertlog ,there are heartbeat messages between ASM and storage cell using diskmon

2021-01-25 18:38:12.426: [ DISKMON][7776:552761664] dskm_process_msg5: received msg type KGZM_PING (0x0011)
2021-01-25 18:38:15.430: [ DISKMON][7776:552761664] dskm_process_msg5: received msg type KGZM_PING (0x0011)
2021-01-25 18:38:18.435: [ DISKMON][7776:552761664] dskm_process_msg5: received msg type KGZM_PING (0x0011)
2021-01-25 18:38:21.439: [ DISKMON][7776:552761664] dskm_process_msg5: received msg type KGZM_PING (0x0011)

PRCR-1001 : Resource ora.asm does not exist

· Leave a comment ·

Cause:

After installing grid,i cant start asm resource 

[oracle@exdbadm01 ~]$ srvctl start asm 
PRCR-1001 : Resource ora.asm does not exist

Check the resource status

[oracle@exdbadm01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       exdbadm01
ora.ons
               OFFLINE OFFLINE      exdbadm01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       exdbadm01

ASM resource doesnot reflect in the list of resources

After investing the logfiles, i can see that SPFILE was not accessible because css 

*** 2021-01-23 19:29:47.210
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
KGGPNP_SIHA: resource 'ora.asm' is not available [200]
KGGPNP_SIHA: attribute 'SPFILE' get failed sts[200] lsts[0]
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
2021-01-23 19:29:47.182536*:kgflk.c@160: kgflkLockInit2: Unable to get CSS context(2)
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
2021-01-23 19:29:47.245344*:kgflk.c@160: kgflkLockInit2: Unable to get CSS context(2)
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
kjidpins: unable to connect to NM

After scrutinizing the ASM configuration,i found out that asm was not added to cluster confoguration

[oracle@exdbadm01 ~]$ srvctl config asm
PRCR-1001 : Resource ora.asm does not exist

Solution:

Add the asm resource to the cluster configuration

[oracle@exdbadm01 ~]$ srvctl add asm

Check if ora.asm resource reflect 

[oracle@exdbadm01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       exdbadm01
ora.asm
               OFFLINE OFFLINE      exdbadm01
ora.ons
               OFFLINE OFFLINE      exdbadm01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       exdbadm01

Start the ASM

[oracle@exdbadm01 ~]$ srvctl start asm

Check the ASM status

[oracle@exdbadm01 ~]$ srvctl status asm
ASM is running on exdbadm01

Hope this worked for you!

[INS-40404] The installer has detected a configured instance of Oracle grid infrastructure software on the server.

· Leave a comment ·

Cause:

When installing oracle grid for a server,this error pops up because of improper cleanups of the previous grid software directories and files.I hope i can save your time,by giving you this solution

,
Cause - You selected to install and configure a new Oracle grid infrastructure installation (Oracle Clusterware and Oracle ASM). However, only one installation of Oracle Clusterware and Oracle ASM can be configured on a server at the same time.  

Action - Deconfigure the existing Oracle Grid Infrastructure software before installing and configuring a new instance of Oracle Grid Infrastructure software.

Solution:

These culprit registry files though it acts as pointer for ASM disks cause this error and disallow you to go forward to the next step!

Take a backup of these files

[root@exdbadm01 oracle]# cp ocr.loc ocr.loc.bkp
[root@exdbadm01 oracle]# cp olr.loc olr.loc.bkp

Then remove ocr.loc and olr.loc files and continue your installation 

[root@exdbadm01 etc]# cd /etc/oracle
[root@exdbadm01 etc]# rm -rf ocr.loc
[root@exdbadm01 oracle]# ls
cell      ocr.loc.orig  olr.loc.orig  scls_scr
lastgasp  olr.loc       oprocd        setasmgid
[root@exdbadm01 oracle]# rm -rf olr.loc

Hope it works and save your time 🙂

-bash: locate: command not found (Solved)

· Leave a comment ·

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.

· Leave a comment ·

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

· Leave a comment ·

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 !