XscalibaL

Uncategorized

INSTALL ORACLE DATABASE 12C

We use dbca to install oracle database

[oracle@orcldg ~]$ cd $ORACLE_HOME/bin/
[oracle@orcldg bin]$ ./dbca

Click create database

Click advanced mode

Choose general purpose or transaction processing

Give the SID name

Tick OEM checkbox

Click on same admin password and give your password

Click on create new listener and give a listener name and port as 1521 if you dont have any existing listener running

In my case, i have ASM so i choose DATA diskgroup for datafiles and FRA for backups and archivelogs. If you dont have ASM ,then choose filesystem itself. Perform either of the steps below for database files

Skip this step

Choose Automatic memory management(AMM)

Choose AL32UTF8 character set

Click next

You will get summary like this

Installation progress looks like this

Install is complete

kishan

Jun, Fri, 2021

Uncategorized

No package ansible available.Error: Nothing to do

Update yum at the first place

yum update -y

Use wget to send a pull request from the epel package site to download the rpm

[root@tibero tibero]# wget https://archives.fedoraproject.org/pub/archive/epel/5                                      /x86_64/epel-release-5-4.noarch.rpm
--2021-06-04 00:41:51--  https://archives.fedoraproject.org/pub/archive/epel/5/x                                      86_64/epel-release-5-4.noarch.rpm
Resolving archives.fedoraproject.org... 38.145.60.22, 38.145.60.23, 38.145.60.24
Connecting to archives.fedoraproject.org|38.145.60.22|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 12232 (12K) [application/x-rpm]
Saving to: “epel-release-5-4.noarch.rpm”

100%[======================================>] 12,232      --.-K/s   in 0.007s

2021-06-04 00:41:53 (1.63 MB/s) - “epel-release-5-4.noarch.rpm” saved [12232/122                                      32]

Install epel rpm which was downloaded

[root@tibero tibero]# rpm -Uvh epel-release-5-4.noarch.rpm
warning: epel-release-5-4.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 217521f6: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]

List all the repositories. Now you should see epel 5 enabled which is bold

[root@tibero tibero]# yum repolist all
Loaded plugins: security, ulninfo
epel                                                                                           | 3.6 kB     00:00
epel/primary_db                                                                                | 2.8 MB     00:02
repo id                       repo name                                                                status
epel                          Extra Packages for Enterprise Linux 5 - x86_64                           enabled:  6,428
epel-debuginfo                Extra Packages for Enterprise Linux 5 - x86_64 - Debug                   disabled
epel-source                   Extra Packages for Enterprise Linux 5 - x86_64 - Source                  disabled
epel-testing                  Extra Packages for Enterprise Linux 5 - Testing - x86_64                 disabled
epel-testing-debuginfo        Extra Packages for Enterprise Linux 5 - Testing - x86_64 - Debug         disabled
epel-testing-source           Extra Packages for Enterprise Linux 5 - Testing - x86_64 - Source        disabled
public_ol6_MySQL              MySQL 5.5 for Oracle Linux 6 (x86_64)                                    disabled
public_ol6_MySQL56            MySQL 5.6 for Oracle Linux 6 (x86_64)                                    disabled
public_ol6_UEKR3_latest       Unbreakable Enterprise Kernel Release 3 for Oracle Linux 6Server (x86_64 enabled:  1,270
public_ol6_UEK_base           Unbreakable Enterprise Kernel for Oracle Linux 6Server (x86_64)          disabled
public_ol6_UEK_latest         Latest Unbreakable Enterprise Kernel for Oracle Linux 6Server (x86_64)   disabled
public_ol6_addons             Oracle Linux 6Server Add ons (x86_64)                                    disabled
public_ol6_ga_base            Oracle Linux 6Server GA installation media copy (x86_64)                 disabled
public_ol6_gdm_multiseat      Oracle Linux 6 GDM Multiseat (x86_64)                                    disabled
public_ol6_latest             Oracle Linux 6Server Latest (x86_64)                                     enabled: 12,932
public_ol6_ofed_UEK           OFED supporting tool packages for Unbreakable Enterprise Kernel on Oracl disabled
public_ol6_playground_latest  Latest mainline stable kernel for Oracle Linux 6 (x86_64) - Unsupported  disabled
public_ol6_spacewalk20_client Spacewalk Client 2.0 for Oracle Linux 6 (x86_64)                         disabled
public_ol6_spacewalk20_server Spacewalk Server 2.0 for Oracle Linux 6 (x86_64)                         disabled
public_ol6_u1_base            Oracle Linux 6Server Update 1 installation media copy (x86_64)           disabled
public_ol6_u2_base            Oracle Linux 6Server Update 2 installation media copy (x86_64)           disabled
public_ol6_u3_base            Oracle Linux 6Server Update 3 installation media copy (x86_64)           disabled
public_ol6_u4_base            Oracle Linux 6Server Update 4 installation media copy (x86_64)           disabled
public_ol6_u5_base            Oracle Linux 6Server Update 5 installation media copy (x86_64)           disabled
public_ol6_u6_base            Oracle Linux 6Server Update 6 installation media copy (x86_64)           disabled
public_ol6_u7_base            Oracle Linux 6Server Update 7 installation media copy (x86_64)           disabled
repolist: 20,630

Create a repo file called ansible.repo

[root@tibero yum.repos.d]# touch ansible.repo
[root@tibero yum.repos.d]# vi ansible.repo
[root@tibero yum.repos.d]#

Paste the configuration inside the file

[root@tibero yum.repos.d]# cat ansible.repo

[Ansible]
name = ansible
baseurl = https://releases.ansible.com/ansible/rpm/release/epel-7-x86_64/
enabled = 1
gpgcheck = 0

Now try to install ansible

[root@tibero yum.repos.d]# yum install ansible -y --skip-broken
Loaded plugins: security, ulninfo
Setting up Install Process
Ansible                                                                                        | 2.9 kB     00:00
Ansible/primary_db                                                                             |  88 kB     00:00
Resolving Dependencies
--> Running transaction check
---> Package ansible.noarch 0:2.9.22-1.el7.ans will be installed
--> Processing Dependency: python(abi) = 2.7 for package: ansible-2.9.22-1.el7.ans.noarch
--> Processing Dependency: sshpass for package: ansible-2.9.22-1.el7.ans.noarch

kishan

Jun, Fri, 2021

Uncategorized

ORA-00845: MEMORY_TARGET not supported on this system

While installation of oracle database, due to high memory target getting into shared memory of low size

Here we can see than memory size is allocated as 1440 MB , and shared memory size is also set as 1500MB.

[root@orcldg u01]# df -h /dev/shm
Filesystem      Size  Used Avail Use% Mounted on
shmfs           1.5G     0  1.5G   0% /dev/shm

Workaround:

Soln 1: Increase the shared memory segments to resolve the issue. This issue happens because there are no sufficient memory available for other running programs,OS and databases etc..

[root@orcldg grid]# umount tmpfs
[root@orcldg grid]# mount -t tmpfs shmfs -o size=3000m /dev/shm

Before allocation of shm

[oracle@orcl19x oradata]$ ipcs

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 312901632  oracle     640        4096       0
0x00000000 312934401  oracle     640        4096       0
0x149f5fc8 312967170  oracle     640        4096       0

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0xde2547a4 2457600    oracle     640        154

After allocation of shm

[oracle@orcl19x ~]$ ipcs

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status
0x00000000 313425920  oracle     640        4096       0
0x00000000 313458689  oracle     640        4096       0
0x149f5fc8 313491458  oracle     640        4096       0
0x00000000 314179587  oracle     640        2928640    42
0x00000000 314212356  oracle     640        1560281088 21
0x00000000 314245125  oracle     640        13848576   21
0xc7fedcfc 314277894  oracle     640        12288      21

------ Semaphore Arrays --------
key        semid      owner      perms      nsems
0xde2547a4 2981888    oracle     640        154
0x4761b218 3112961    oracle     640        152
0x4761b219 3145730    oracle     640        152
0x4761b21a 3178499    oracle     640        152

Soln 2: During instance startup, this error triggers sometime

kish@STDBY>startup nomount pfile='/apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora';
ORA-00845: MEMORY_TARGET not supported on this system

You have to adjust the SGA or PGA size parameters to startup the database instance using pfile

Reduce the memory_target temporarily to start the instance and after adding more memory to the server, the parameter can be dynamically increased

I reduce the memory_target parameter

Post reduction, the instance startup successfully

kish@STDBY>startup nomount pfile='/apps01/product/12.1.0/dbhome_1/dbs/initdbx01.ora';
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  2924832 bytes
Variable Size            1073745632 bytes
Database Buffers          486539264 bytes
Redo Buffers               13848576 bytes

kishan

Jun, Fri, 2021

Uncategorized

umount: /dev/shm: device is busy.(In some cases useful info about processes that usethe device is found by lsof(8) or fuser(1))

Cause:

When try to unmount tmpfs from system, some processes which use the /dev/shm are not allowing to release the mount

[root@orcldg CVU_12.1.0.2.0_oracle]# umount tmpfs
umount: /dev/shm: device is busy.
        (In some cases useful info about processes that use
         the device is found by lsof(8) or fuser(1))

Workaround:

Check the process which access the tmpfs mount

[root@orcldg CVU_12.1.0.2.0_oracle]# lsof|grep /dev/shm|head
gnome-pow 5753       gdm  mem       REG               0,16  67108904                74930 /dev/shm/pulse-shm-1912185855
pulseaudi 5765       gdm  mem       REG               0,16  67108904                19555 /dev/shm/pulse-shm-4123376116
asm_pmon_ 5883      grid  mem       REG               0,16   4194304                30599 /dev/shm/ora_+ASM_105840646_0
asm_pmon_ 5883      grid  mem       REG               0,16         0                30603 /dev/shm/ora_+ASM_105873415_0
asm_pmon_ 5883      grid  mem       REG               0,16         0                30604 /dev/shm/ora_+ASM_105873415_1
asm_pmon_ 5883      grid  mem       REG               0,16         0                30605 /dev/shm/ora_+ASM_105873415_2
asm_pmon_ 5883      grid  mem       REG               0,16         0                30606 /dev/shm/ora_+ASM_105873415_3
asm_pmon_ 5883      grid  mem       REG               0,16         0                30607 /dev/shm/ora_+ASM_105873415_4
asm_pmon_ 5883      grid  mem       REG               0,16         0                30608 /dev/shm/ora_+ASM_105873415_5
asm_pmon_ 5883      grid  mem       REG               0,16         0                30609 /dev/shm/ora_+ASM_105873415_6

In my case, ASM was up and running. Warning: If you are in production database,then this step is not recommended

[grid@orcl ~]$ srvctl stop asm -f
[grid@orcl ~]$

Check again the processes accessing the shm

[root@orcl oracle]# lsof|grep /dev/shm
gnome-pow 5891       gdm  mem       REG               0,16  67108904      59571 /dev/shm/pulse-shm-2901322705
pulseaudi 5903       gdm  mem       REG               0,16  67108904      18199 /dev/shm/pulse-shm-1163139171

kill the process pid forcefully

[root@orcl oracle]# kill -9 5891 5903

Now there are no process accessing the database. We are good to unmount!

[root@orcl oracle]# lsof|grep /dev/shm
[root@orcl oracle]# umount tmpfs

kishan

Jun, Fri, 2021

Uncategorized

PRCR-1065 : Failed to stop resource ora.asm – CRS-2529: Unable to act on ‘ora.asm’ because that would require stopping or relocating ‘ora.CRS.dg’, but the force option was not specified

If you are unable to stop ASM in oracle restart, then go to next step

[grid@orcl ~]$ srvctl stop asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.CRS.dg', but the force option was not specified

Use -f force option to stop ASM

[grid@orcl ~]$ srvctl stop asm -f
[grid@orcl ~]$

kishan

Jun, Fri, 2021

Uncategorized

Existing lock /var/run/yum.pid: another copy is running as pid 6969.Another app is currently holding the yum lock; waiting for it to exit…

Cause:

There is already an old yum process running on server which doesnot allow any further installs

[root@tibero tibero]# yum install ansible
Loaded plugins: security, ulninfo
Existing lock /var/run/yum.pid: another copy is running as pid 6969.
Another app is currently holding the yum lock; waiting for it to exit...
  The other application is: yum
    Memory :  23 M RSS (327 MB VSZ)
    Started: Thu Jun  3 23:13:02 2021 - 00:07 ago
    State  : Traced/Stopped, pid: 6969

Workaround:

Identify the process pid from the error message which runs in background

[root@tibero tibero]# ps -ef|grep 6969|grep -v 'grep'
root      6969  6960  1 23:13 pts/0    00:00:01 /usr/bin/python /usr/bin/yum install ansible

Kill the old yum session

[root@tibero tibero]# kill -9 6969

[root@tibero tibero]# ps -ef|grep 6969|grep -v 'grep'

[1]-  Killed                  yum install ansible

kishan

Jun, Thu, 2021

Uncategorized

FIND COUNT OF ORACLE PROCESS FROM LINUX

Use below simple command to find an approximate count of processes in oracle database

[oracle@orcl ~]$ ps -e|grep 'ora'|wc -l
61

We usually login to database using sqlplus tool to find the total processes

SQL> select count(*) from v$process;

  COUNT(*)
----------
        52

I got a situation where concurrent sessions connect to database and exceed the process limit. Due to the overflow of concurrent spawn of process , as a DBA even i couldnot login to the database itself and got kicked out with below error.

C:\Windows\system32>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 14 14:31:27 2021
Version 19.3.0.0.0

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

ORA-03113: end-of-file on communication channel
Process ID: 6215
Session ID: 1 Serial number: 46504

In that case, i dont have any options to login to the sqlplus session. So i identified the total oracle process using the above linux command. It will be close to the sqlplus count but more than database process count

Process in OS level

[oracle@orcl ~]$ ps -ef|grep ora|awk -F '_' '{print $2}'|grep -v '^$'
asmb
o000
pmon
psp0
vktm
gen0
mman
diag
dbrm
vkrm
dia0
dbw0
lgwr
ckpt
smon
reco
lreg
pxmn
rbal
asmb
mmon
mmnl
asmb
d000
s000
mark
dmon
ocf0
o000
ocf0
o000
o001
o001
rvwr
insv
tmon
arc0
arc1
arc2
arc3
tt00
o002
tt01
o002
tt00
smco
w000
w001
aqpc
p000
p001
p002
p003
cjq0
qm02
q002
q003
q004

Process in database level

SQL> select pname from v$process where pname is not NULL;

PMON
PSP0
VKTM
GEN0
MMAN
RVWR
DIAG
DBRM
VKRM
DIA0
DBW0
LGWR
CKPT

SMON
RECO
LREG
PXMN
RBAL
ASMB
MMON
MMNL
D000
S000
MARK
DMON
Q002

O000
INSV
TMON
ARC0
ARC1
ARC2
ARC3
TT00
TT01
SMCO
W000
W001
AQPC

CJQ0
P000
P001
P002
P003
QM02
Q003

46 rows selected.

kishan

Jun, Thu, 2021

Uncategorized

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

Below error is due to stopped sql server agent

Right click on SQL server agent –> Click on start

Now right click —> Properties

kishan

Jun, Thu, 2021

Uncategorized

MAX MEMORY SETTINGS SQL SERVER

In sql server , while configuring memory for databases, always leave reserve 20% of memory for spare use of OS.

sql server uses 80% of memory from OS. Depending on your application workload and overhead , you have to calculate and allocate memory to your database accordingly

kishan

Jun, Thu, 2021

Uncategorized

FIND TOTAL PROCESS COUNT IN LINUX

Use the below command to find the total number of processes running in linux

[oracle@orcldg ~]$ ps -ef|wc -l
710

kishan

Jun, Thu, 2021