How to connect with Oracle database using Azure Data Studio ?

 

check the Data Source section :

CPU utilization analysis using Oracle report (Statspack/AWR)

How to calculate CPU % used for Instance in Statspack report ?

Sample Statspack report :

Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC

~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---

           11223344 ABCD              1 11-Aug-22 11:34 12.1.0.2.0  NO

Host Name             Platform                CPUs Cores Sockets   Memory (G)

~~~~ ---------------- ---------------------- ----- ----- ------- ------------

     test01      Solaris Operating Syst     4     4       2        320.0

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment

~~~~~~~~    ---------- ------------------ -------- --------- ------------------

Begin Snap:        113 11-Dec-22 02:00:05       44       1.1

  End Snap:        114 11-Dec-22 03:00:10       50       1.1

   Elapsed:      60.08 (mins) Av Act Sess:       1.1

   DB time:      63.19 (mins)      DB CPU:      63.11 (mins)

   

Top 5 Timed Events                                                    Avg %Total

~~~~~~~~~~~~~~~~~~                                                   wait   Call

Event                                            Waits    Time (s)   (ms)   Time

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

LGWR worker group idle                           2,592       7,632   2945   34.4

CPU time                                                     3,698          16.7

heartbeat redo informer                          3,604       3,605   1000   16.3

lreg timer                                       1,261       3,604   2858   16.3

AQPC idle                                          120       3,601  30010   16.2

CPU Time used  3698 s

Total CPU : NUM_cpu * elapsed_time *60 

num_cpu can be found from operating system statistics 

4*60.08*60= 14419.2s   for 3600s DB time 

total % of cpu used by Oracle process : 3698/14419 *100 = 25.64 %

Database only used approx. 26% of cpu 

calculate cpu consumption from OS perspective :

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

OS Statistics  DB/Inst: test/test  Snaps: 113-114

-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name

Statistic                                  Total

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

BUSY_TIME                                411,086

IDLE_TIME                              1,030,890

SYS_TIME                                  30,330

USER_TIME                                380,756

OS_CPU_WAIT_TIME                         784,200

RSRC_MGR_CPU_WAIT_TIME                         0

PHYSICAL_MEMORY_BYTES            343,560,974,336

NUM_CPUS                                       4

NUM_CPU_CORES                                  4

NUM_CPU_SOCKETS                                2

TCP_RECEIVE_SIZE_DEFAULT                 256,000

TCP_RECEIVE_SIZE_MAX                   1,048,576

TCP_SEND_SIZE_DEFAULT                     49,152

TCP_SEND_SIZE_MAX                      1,048,576

Host CPU  (CPUs: 4  Cores: 4  Sockets: 2)

~~~~~~~~              Load Average

                      Begin     End      User  System    Idle     WIO     WCPU

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

                       1.20    1.33     26.41    2.10   71.49    0.00   54.38

= user+ System 

26.41 + 2.10 = 28.51 

Instance CPU

~~~~~~~~~~~~                                       % Time (seconds)

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

                     Host: Total time (s):                 14,419.8

                  Host: Busy CPU time (s):                  4,110.9

                   % of time Host is Busy:      28.5

   

             Instance: Total CPU time (s):                  3,846.6

          % of Busy CPU used for Instance:      93.6

        Instance: Total Database time (s):                  3,889.2

  %DB time waiting for CPU (Resource Mgr):       0.0

16 cpu * 30/100 = 5  cpu 

%Busy_time = { busy_time/(busy_time+Idele_time)*100 

  411086  

+1030890 

==========

 1441976

           = (411086/1441976) *100 = 28.50 

Busy_time= SYS_time + USER_TIME

   30330

+ 380756

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

  411086

% SYS time =  (sys_time/busy_time ) *100 = (30330/411086) *100 = 7.3 out of %Busy_time : 28.51 

% User time=  (user_time/busy_time) * 100 = 92.62  out of %Busy_time : 28.51 

How to install, configure, and use Statspack in Oracle database step by step ?

High Level steps for Statspack install and configure :

1. How to check Statspack installed or not ?

    select * from stats$level_description;

    *If no output and error "table doesn't exists" 

2. Create sperate tablespace for keep Statspack  objects 

create tablespace stat_pack datafile '/u01/app/oracle/oradata/INDIA/stat_pack01.dbf' size 500M autoextend on maxsize 30G;

#####################################################################

For Statspack installation, need to run below command using sysdba login 

#####################################################################

@/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spcreate.sql

SQL>

SQL> @/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spcreate.sql

Session altered.

Choose the PERFSTAT user's password

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

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: perfstat

perfstat

Choose the Default tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store user data.  Specifying the SYSTEM tablespace for the user's

default tablespace will result in the installation FAILING, as

using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS

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

STATSPACK DEFAULT TABLESPACE

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

STAT_PACK                      PERMANENT

SYSAUX                         PERMANENT

*

USERS                          PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace: STAT_PACK

Using tablespace STAT_PACK as PERFSTAT default tablespace.

Choose the Temporary tablespace for the PERFSTAT user

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

Below is the list of online tablespaces in this database which can

store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

tablespace for the user's temporary tablespace will result in the

installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE

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

TEMP                           TEMPORARY             *

Pressing <return> will result in the database's default Temporary

tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as PERFSTAT temporary tablespace.

... Creating PERFSTAT user

... Installing required packages

... Creating views

... Granting privileges

.

.

.

…………will take 2 min to complete , need to check there is no error 

############################

Below parameter should true 

#############################

SQL> show parameter timed_statistic

NAME                                 TYPE        VALUE

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

timed_statistics                     boolean     TRUE

SQL>

##########################################

To check Statspack Level for take snapshot

############################################

SQL> l

  1* select * from stats$level_description

SQL> /

SNAP_LEVEL DESCRIPTION

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

         0 This level captures general statistics, including rollback s

           egment, row cache, SGA, system events, background events, se

           ssion events, system statistics, wait statistics, lock stati

           stics, and Latch information

         5 This level includes capturing high resource usage SQL Statem

           ents, along with all data captured by lower levels

         6 This level includes capturing SQL plan and SQL plan usage in

           formation for high resource usage SQL Statements, along with

            all data captured by lower levels

         7 This level captures segment level statistics, including logi

           cal and physical reads, row lock, itl and buffer busy waits,

            along with all data captured by lower levels

        10 This level includes capturing Child Latch statistics, along

           with all data captured by lower levels

############################################

Login with perfstat user and take snapshot 

##############################################

[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat

SQL>

SQL> show user

USER is "PERFSTAT"

SQL>

SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')

     "Date/Time" from stats$snapshot,v$database;

SQL> execute statspack.snap(i_snap_level=>10);

PL/SQL procedure successfully completed.

########################

To run report 

######################

[oracle@10.22.3333.2222 admin]$

[oracle@10.22.3333.2222 admin]$ sqlplus perfstat/perfstat

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Dec 6 07:45:00 2022

Version 19.3.0.0.0

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

Last Successful login time: Tue Dec 06 2022 07:41:01 +00:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL> @/u01/app/oracle/product/19.0/dbhome1/rdbms/admin/spreport.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

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

 3280227920 INDIA               1 india

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id    Inst Num DB Name      Instance     Host

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

 3280227920        1 INDIA        india        ip-172-31-19

                                               -40.ec2.inte

                                               rnal

Using 3280227920 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                                                       Snap

Instance     DB Name        Snap Id   Snap Started    Level Comment

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

india        INDIA                1 06 Dec 2022 07:38    10

                                  2 06 Dec 2022 07:41    10

                                  3 06 Dec 2022 07:41    10

                                  4 06 Dec 2022 07:41    10

                                  5 06 Dec 2022 07:41    10

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 5

End   Snapshot Id specified: 5

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sp_1_5.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: sp_1_5_report_india

Using the report name sp_1_5_report_india

Checking swap space: 0 MB available, 150 MB required. Failed

Checking swap space: 0 MB available, 150 MB required.    Failed 

[oracle@oracle12c database]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 1991 MB    Passed

Checking swap space: 0 MB available, 150 MB required.    Failed 

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

User Selected: No

Solution :

Need to add swap space 

https://anuragkumarjoy.blogspot.com/2020/04/adding-swap-space-in-linux.html

[root@oracle12c ~]# free -g

              total        used        free      shared  buff/cache   available

Mem:              7           0           2           0           4           6

Swap:             1           0           1

[root@oracle12c ~]#

How to check the valid components loaded into the database (JVM,XML,XDK,Oracle Label Security......) ?

How to check the valid components loaded into the database ?

Login as privilege user and run below commands : 

SQL> select * from all_registry_banners;

BANNER                                                                           BANNER_FULL

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

Oracle Database Catalog Views Release 19.0.0.0.0 - Production                    Oracle Database Catalog Views Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Database Packages and Types Release 19.0.0.0.0 - Production               Oracle Database Packages and Types Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle XML Database Release 19.0.0.0.0 - Production                              Oracle XML Database Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Workspace Manager Release 19.0.0.0.0 - Production                         Oracle Workspace Manager Release 19.0.0.0.0 - Production Version 19.3.0.0.0

JServer JAVA Virtual Machine Release 19.0.0.0.0 - Production                     JServer JAVA Virtual Machine Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle XDK Release 19.0.0.0.0 - Production                                       Oracle XDK Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Database Java Packages Release 19.0.0.0.0 - Production                    Oracle Database Java Packages Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Text Release 19.0.0.0.0 - Production                                      Oracle Text Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Multimedia Release 19.0.0.0.0 - Production                                Oracle Multimedia Release 19.0.0.0.0 - Production Version 19.3.0.0.0

OLAP Analytic Workspace Release 19.0.0.0.0 - Production                          OLAP Analytic Workspace Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle OLAP API Release 19.0.0.0.0 - Production                                  Oracle OLAP API Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Spatial Release 19.0.0.0.0 - Production                                          Spatial Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Label Security Release 19.0.0.0.0 - Production                            Oracle Label Security Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Oracle Database Vault Release 19.0.0.0.0 - Production                            Oracle Database Vault Release 19.0.0.0.0 - Production Version 19.3.0.0.0

14 rows selected.

SQL>

Login using SSH into a new EC2 instance created from an custom AMI (image)

 Not able to login (SSH) to EC2 running Linux(Custom AMI)

Solution 

Login as ec2-user 

sudo su - root 

first use source *.pem key to login ec2 instance launch from custom AMI using ec2-user 

enable PasswordAuthentication to yes

vi /etc/ssh/sshd_config

PasswordAuthentication yes

systemctl restart sshd

then login with root user with same password as source. 

How to check Linux distribution and version

 

check Linux  distribution and version

[root@localhost ~]# cat /etc/os-release

NAME="Oracle Linux Server"

VERSION="7.8"

ID="ol"

ID_LIKE="fedora"

VARIANT="Server"

VARIANT_ID="server"

VERSION_ID="7.8"

PRETTY_NAME="Oracle Linux Server 7.8"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:oracle:linux:7:8:server"

HOME_URL="https://linux.oracle.com/"

BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"

ORACLE_BUGZILLA_PRODUCT_VERSION=7.8

ORACLE_SUPPORT_PRODUCT="Oracle Linux"

ORACLE_SUPPORT_PRODUCT_VERSION=7.8

[root@localhost ~]#

[root@localhost ~]# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.8 (Maipo)

[root@localhost ~]#

[root@localhost ~]# cat /etc/oracle-release

Oracle Linux Server release 7.8

[root@localhost ~]#

check kernel version 

[root@localhost ~]# uname -r

4.14.35-1902.300.11.el7uek.x86_64

[root@localhost ~]#

To check Package is installed or not 

rpm -q package_name

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep package_name

[root@localhost ~]#

[root@localhost ~]# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep ssh 

openssh-server-7.4p1-21.0.1.el7 (x86_64)

openssh-7.4p1-21.0.1.el7 (x86_64)

openssh-clients-7.4p1-21.0.1.el7 (x86_64)

libssh2-1.8.0-3.el7 (x86_64)

[root@localhost ~]#

check multiple packages, and review the output for the validations

rpm -q binutils compat-libstdc++ gcc glibc libaio libgcc libstdc++ \

make sysstat unixodbc

[root@localhost ~]# rpm -q binutils compat-libstdc++ gcc glibc libaio libgcc libstdc++ \

> make sysstat unixodbc

binutils-2.27-43.base.0.1.el7.x86_64

package compat-libstdc++ is not installed

gcc-4.8.5-39.0.3.el7.x86_64

glibc-2.17-307.0.1.el7.1.x86_64

libaio-0.3.109-13.el7.x86_64

libgcc-4.8.5-39.0.3.el7.x86_64

libstdc++-4.8.5-39.0.3.el7.x86_64

make-3.82-24.el7.x86_64

sysstat-10.1.5-19.el7.x86_64

package unixodbc is not installed

[root@localhost ~]#

PostgreSQL Administration Certificate

 

Download Microsoft Windows Server 2019

 Download Windows Server 2019

https://www.microsoft.com/en-in/evalcenter/download-windows-server-2019

Microsoft SQL Server Edition

 

SQL Server Edition:

• Enterprise  

• Standard

• Developer

• Web 

• Express

Download SQL Server (MSSQL) 2019 database

 Download MSSQL 2019 database for Windows

Download Link  : 

https://www.microsoft.com/en-in/evalcenter/download-sql-server-2019

Download AdventureWorks sample databases for MSSQL

 AdventureWorks sample databases

https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms

How to Pass AWS Certified Database Specialty : AWS DBS-C01 ?

 AWS DBS-C01 Exam

                                                         (AWS Certified Database Specialty)

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

Please let me know questions in comments  if any.