DBA Day-2 ve

 Operating system 

CPU

mpstat

top 

load average 

pidstat

dstat -vr

strace---> debug if system CPU usage % is high but if you dont know then use strace -tp `grep testdb1 2>&1 | head 100

debug if User CPU usage % is high but if you dont know then use

6. jobs (hit enter)

6.1 perf record -F 99 -a -g --sleep 10 (Profiling )

6.2 perf report -n --stdio

6.3 ./perf-tools/execsnoop

Memory

vmstat

I/O

iostat -xmd 1

iotop

Network

sar -n DEV 1

mtr

netstat -antpl | grep 1521

nmcli device status

ethtool

iperf3 -c 10.1.90.51

https://www.youtube.com/watch?v=eWUeJBAiX80

fs.aio-max-nr--> Async I/O max

fs.file-max --> Max file open

kernal.shmmin --> how many program segment can access(permission) the shared memory

kernal.shmall --> maximum amount of shared memory can accessed by all the process other than OS(set to sum of all SGAs on the server divided by page size – ‘getconf PAGESIZE’)

kernal.shmmax --> maximum amount of shared memory can accessed by individual process (set to the largest SGA on your server plus 1G)

kernal.sem --> RAM locking mechanism to avoid same physical RAM memory area can be accessed by multiple process

net.ipv4.ip_local_port_range --> Only this port range can be accessed from other apps > 9000 that's why oracle is < 8000

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

https://www.youtube.com/watch?v=deNezXr3zJo

oracle soft nofile 1024 --> Maximum no of file handler oracle user can open

oracle hard nofile 65536 --> Resize no of file handler oracle user 

oracle soft nproc 16384 --> Maximum no of process oracle user can open

oracle hard nproc 16384 --> Resize no of process of oracle user 

oracle soft stack 10240 --> Per thread memory size in KB

oracle hard stack 32768 --> Resize thread memory size in KB

oracle hard memlock 134217728 --> how much memory a user or application can lock into RAM (set to slightly less than total RAM on server (in KB))

oracle soft memlock 134217728 --> resize (set to slightly less than total RAM on server (in KB))

RAM = 128GB = 132093152 kB

SGA = 48GB – however, to allow for possible growth and given I have 128GB total, I’m going to use 64G for my numbers

PGA = 16GB

shmmax = 64GB+1GB = 65GB= 69793218560

shmall = 1SGA @ 64GB = 64G/4096 = 16,777,216

oracle soft memlock = slightly less than 132093152 = 130000000

oracle hard memlock = oracle soft memlock = 130000000

https://www.youtube.com/watch?v=BVJcaDIJ-9A

huge pages

load average

tanel poder 16.05

--> https://www.youtube.com/watch?v=ZlNt99JCcGY

CPU 

--

sar -u

sar -P ALL

sar -q --> Server Load Average

sar -r --> Memory check kbcommit and commit ( Commit value will come based on availabe RAM + SWAP memory)

sar -S --> Swap 

sar -B --> Page in and Page Out ( chck majflt is high or not high means prob)

sar -b --> I/O 

sar -d -p --> I/O --> format in block device

sar -q -s 10:00:00 -e 11:00:00

https://www.youtube.com/watch?v=ZlNt99JCcGY (SAR VIDEO)

Pending

https://www.youtube.com/watch?v=dTn3woUHj_M

Mallik

RAC

https://youtu.be/YsEsAnnoI3A?si=fJTJAXQiqgfQMNrQ

https://orskl.com/is-lba-mandatory-for-scan-listener-in-oracle-rac/

https://vishaldesai.wordpress.com/2014/01/22/yet-another-case-of-multiple-child-cursors-bind_mismatch-and-optimizer_mismatch/

hang analyse oracle

Snapshot too old error

python argument

smon --> https://www.youtube.com/watch?v=VQkPhuT2_Bs

Pef 

I/O

CPU

ASH

ADDM

WAIT 

Using DB Services

Tuning Shared pool,Buffer Cache

ROW Chaingin and ROW Migration

RAC

LBA --Youtube

Architecute

Commands

CBD and PDB Architecture 19c and int

*******""""*********

PMON

SMON

RAC Service *** https://www.youtube.com/watch?v=wGHKZhCWEbw

LBA *** Pending

Convert NON CDB to CDB and PDB*** Pending

Add and Remove Node *** Pending

UPgrade *** Pending

Kernal parameter --> https://www.youtube.com/watch?v=deNezXr3zJo

https://www.alex-sanz.com/2022/04/13/how-to-upgrade-oracle-grid-from-12c-to-19c/

Interview TCS

Oracle RAC architecture **

RAC Background process **

GPNP profile **

OCR **

OLR** 

Voting Disk **

GCC Buffer busy wait **

LBA

RAC specific parameters **

https://www.funoracleapps.com/2018/06/how-to-change-various-ips-in-rac-cluster.html

srvctl commands --> https://oracleagent.wordpress.com/2021/01/26/srvctl-command-in-rac-in-19c-database/

https://dbaclass.com/article/useful-srvctl-commands/

crsctl commands --> https://oracleagent.wordpress.com/2021/01/26/oracle-rac-administration-and-monitoring-scripts/

oifcfg COMMANDS --> https://satya-racdba.blogspot.com/2010/01/oifcfg-commands.html

Adding and Removing Nodes

https://dbaclass.com/article/how-to-add-a-node-in-oracle-rac-19c/

https://dbaclass.com/article/how-to-delete-a-node-from-oracle-rac-19c/

Oracle Kernal Parameter --> https://www.youtube.com/watch?v=deNezXr3zJo

Find Master Node -

oclumon manage -get master   

select MASTER_NODE from v$ges_resource;

ocrconfig -manualbackup

 oifcfg 

Adding node --> https://infraxpertzz.com/steps-to-add-a-node-in-oracle-rac-19c/

https://satya-racdba.blogspot.com/2019/01/oracle-rac-master-node.html

ASM -->https://dbaclass.com/article/asm-interview-questions/

Data guard architectire -->https://dbaclass.com/article/oracle-dataguard-interview-questions/

Dataguard DG Brocker configuration and Configure Fast Start Failover -->https://oracledbwr.com/oracle-19c-configure-oracle-data-guard-fast-start-failover/

Dataguard Failover using DG Brocker and DG Broker file and process 

https://www.br8dba.com/failover-with-dg-broker/

https://www.youtube.com/watch?v=CSIxXLYXYwQ

Oracle 19c and 21c new features

Oracle Multitenant like cloaning PDB and etc

DB Upgrade

https://dbaclass.com/article/oracle-rac-interview-questions/ Q- 70,80

https://dbaclass.com/article/oracle-dataguard-interview-questions/

https://support.dbagenesis.com/post/asm-background-process

GRID and Database Patching

Oracle RMAN cloaning  

Oracle RMAN interview questions--> https://dbaclass.com/article/rman-interview-qa/

https://dba12c.wordpress.com/2017/04/24/oracle-local-registry-olr/

http://oracleinaction.com/gpnp-profile/

http://oracleinaction.com/edit-gpnp-profile/

https://airydba.wordpress.com/2016/11/29/gpnp-grid-plug-n-play-profile-in-oracle-11g-r212c-rac/

https://oracledbwr.com/oracle-cluster-registry-in-rac-ocr/

https://oracledbwr.com/voting-disk-in-oracle-rac-database/

https://dba12c.wordpress.com/2017/04/23/voting-disks-in-oracle-11gr2-rac/

https://orahow.com/oracle-rac-clusterware-startup-sequence/

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

OLR

If OCR is on ASM, it can’t be read until ASM (which itself is a resource for the node and this information is stored in OCR) is up. To resolve this problem, information about the resources which need to be started on a node is stored in an operating system file which is called Oracle Local Registry or OLR. Since OLR is a file an operating system file, i

What does OLR contain?

———————-

The OLR stores data about

ORA_CRS_HOME

localhost version

active version

GPnP details

OCR latest backup time and location

information about OCR daily, weekly backup location

 node name etc.

GPNP

 CSSD scans the device headers of all devices specified in the gpnp profile xml file, tag “DiscoveryString” which contains the same value as specified in asm_diskstring parameter for ASM instance

- Cluster name

- Network classifications (Public/Private)

- Storage to be used for CSS

- Storage to be used for ASM : SPFILE location,ASM DiskString etc

Voting Disk

CSSD process on every node makes entries in the voting disk to ascertain the membership of that node.

Voting disks contain static and dynamic data.

Static data : Info about nodes in the cluster

Dynamic data : Disk heartbeat logging

It maintains and consists of important details about the cluster nodes membership, such as

- which node is part of the cluster,

- who (node) is joining the cluster, and

- who (node) is leaving the cluster.

Once the Operating system starts and finish the boot scrap process it reads /etc/init.d file via the initialisation daemon called init or init.d. The init tab file is the one it triggers oracle high availability service daemon.

When a node of an Oracle Clusterware cluster starts, OHASD is started by platform-specific means like init.d in Linux. OHASD is the root for bringing up Oracle Clusterware.

 OHASD has access to the OLR (Oracle Local Registry) stored on the local file system. OLR provides needed data to complete OHASD initialization.

OHASD brings up GPNPD and CSSD ( Cluster synchronization Service Daemon ). CSSD has access to the GPNP Profile stored on the local file system. This profile contains the following vital bootstrap data:

a. ASM Diskgroup Discovery String

b. ASM SPFILE location (Diskgroup name)

c. Name of the ASM Diskgroup containing the Voting Files

The Voting Files locations on ASM Disks are accessed by CSSD with well-known pointers in the ASM Disk headers and CSSD is able to complete initialization and start or join an existing cluster.

OHASD starts an ASM instance and ASM can now operate with CSSD initialized and operating. The ASM instance uses special code to locate the contents of the ASM SPFILE, assuming it is stored in a Diskgroup.

With an ASM instance operating and its Diskgroups mounted, access to Clusterware’s OCR is available to CRSD.

OHASD starts CRSD with access to the OCR in an ASM Diskgroup.

Clusterware completes initialization and brings up other services under its control.

That's all you need:

=> CRS: $ORACLE_BASE/diag/crs/<hostname>/crs/trace/alert.log

=> ASM: $ORACLE_BASE/diag/+asm/<ASM_SID>/trace/alert_<ASM_SID>.log

=> RDBMS-Instances: $ORACLE_BASE/rdbms/<DB_NAME>/<DB_SID>/trace/alert_<DB_SID>.log

=> PDBs: There is not specific log for PDBS. Please see the alert.log of the corresponding RDBMS instances

https://dbaclass.com/article/oracle-performance-tuning-interview-questions-and-answers/

https://www.youtube.com/watch?v=DqWisxZR0UE

---Upgrade RAC and DB Using DBCA --Completed

---Add and Remove Node --Completed

---Patching using opatchauto --Completed

---crsctl,srvctl,oifcfg,ocrconfig,ocrcheck,olsnodes,cluvfg --Completed

---Listener,Scan Listener --Completed

---Unplug and Plug using DBCA --Completed

---Unplug and Plug using Manual ----Completed

---Clone PDB from One container to another container--Completed

---Clone PDB to Same container --Completed

---Clone PDB to different container --Completed (DB Link)

---crsctl query css activeversion,softwareversion,releaseversion

--- RMAN Cloning PDB--Completed

---NTP Issue while configuring RAC --Completed

---Management DB

---Upgrade Single Instance DB --Completed

---Create One Node RAC ---Active and Passive

---Recover OLR --Completed

crsctl stop crs

ocrconfig -local -restore /crsapp/app/oracle/product/grid12c/cdata/prod60-2/backup_20181118_165215.olr

ocrconfig -local

crsctl start crs

---Recover OCR

https://www.youtube.com/watch?v=e0rpZ2aQMfU

---Recover Voting Disk

---Python

---Parameters

---Import OS

---Import File

---Regular expression

---For loop

---If else

---String methods

RMAN

https://dbaclass.com/article/rman-interview-qa/

Cloning,Upgrade and normal restore

AWR

Udemy AWR (Section 9)

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

OLR --Completed

GPNP --Completed 

OCR --Completed

VOTING DISK --Completed 

GLOBAL CACHE FUSION

ADDING NODE --Completed 

REMOVING NODE --Completed 

PATCHING RAC --Completed

Interview Questions

Changing Public IP/Private IP,VIP and Scan Listener --> https://www.funoracleapps.com/2018/06/how-to-change-various-ips-in-rac-cluster.html --Completed

srvctl,crsctl,ocifg,olsnodes,

Scan listener

OCR recovery

Upgrade RAC and DB Using DBCA --Completed

Patching Issues

Python

(Regular expression)

List,tuples,dict,string methods,functions

Oracle Database Arch

PMON,SMON,Buffer cache,CKPT,Controlfile

Rollfwd and rollbackward

Oracle RAC Interview Questions

How to change various IP's

Oracle Patching

Adding Node

Removing Node

DB Upgrade

RMAN 

datapump --Attach

inittrans

row chaining and row migration

pmon,smon,scan listener

vi editor global replace

---Upgrade RAC and DB Using DBCA --Completed

---Add and Remove Node --Completed

---Patching using opatchauto --Completed

---crsctl,srvctl,oifcfg,ocrconfig,ocrcheck,olsnodes,cluvfg --Completed

---Listener,Scan Listener --Completed

---Unplug and Plug using DBCA --Completed --> https://support.dbagenesis.com/post/unplug-pdb-from-12c-and-plug-into-19c-cdb

---Unplug and Plug using Manual ----Completed

---Clone PDB from One container to another container--Completed --> https://support.dbagenesis.com/post/clone-pdb-or-non-cdb-in-oracle-19c

---Clone PDB to Same container --Completed

---Clone PDB to different container --Completed (DB Link)

---crsctl query css activeversion,softwareversion,releaseversion

--- RMAN Cloning PDB--Completed

---NTP Issue while configuring RAC --Completed

---Management DB

---Upgrade Single Instance DB --Completed

---Create One Node RAC ---Active and Passive

---Recover OLR --Completed

Tools

oswatcher,oracle ahf

vmstat

iostat

iotop

mpstat

top

sar commands