KTEXPERTS – Page 18 – KNOWLEDGE SHARING PLATFORM

Important Oracle v$parameters (SecureCRT)

Hi Dears, In this article, we will see Important Oracle v$parameters (SecureCRT) Tab Name : v$parameter Button Name : SGA

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga%'; 
select &sga/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga%';

select &sga/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_max_size                                       4294967296
pre_page_sga                                       TRUE
lock_sga                                           FALSE
allow_group_access_to_sga                          FALSE
sga_target                                         4294967296
unified_audit_sga_queue_size                       1048576

NAME VALUE

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

sga_max_size 4294967296

pre_page_sga TRUE

lock_sga FALSE

allow_group_access_to_sga FALSE

sga_target 4294967296

unified_audit_sga_queue_size 1048576

select &sga/1024/1024/1024 GB from dual; 
Enter value for sga: 4294967296

        GB
----------
         4

select &sga/1024/1024/1024 GB from dual;

Enter value for sga: 4294967296

----------

Button Name : SGA_TARGET

col name for a50 
set linesize 300 
col value for a40  
select name,value from v$parameter where name like '%sga_target%'; 
select &sga_target/1024/1024/1024 GB from dual;

col name for a50

set linesize 300

col value for a40

select name,value from v$parameter where name like '%sga_target%';

select &sga_target/1024/1024/1024 GB from dual;

Output :

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
sga_target                                         4294967296

select &sga_target/1024/1024/1024 GB from dual; 
Enter value for sga_target: 4294967296

        GB
----------
         4

NAME VALUE

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

sga_target 4294967296

select &sga_target/1024/1024/1024 GB from dual;

Enter value for sga_target: 4294967296

----------

Button Name : PGA_Aggregate_target

SQL>   col name for a50 
SQL> set linesize 300 
SQL> col value for a40  
select name,value from v$parameter where name like '%pga_aggregate_target%'; 
select &pga_aggregate_target/1024/1024/1024 GB from dual;

SQL> col name for a50

SQL> set linesize 300

SQL> col value for a40

select name,value from v$parameter where name like '%pga_aggregate_target%';

select &pga_aggregate_target/1024/1024/1024 GB from dual;

Output :

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; 

NAME                                               VALUE
-------------------------------------------------- ----------------------------------------
pga_aggregate_target                               2147483648

SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual;

NAME VALUE

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

pga_aggregate_target 2147483648

SQL> Enter value for pga_aggregate_target: 2147483648

        GB
----------
         2

SQL> Enter value for pga_aggregate_target: 2147483648

----------

Button Name :… Read More

How to Switch/Recreate Undo Tablespace at PDB Level(12cR2)

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see How to Switch/Recreate Undo Tablespace at PDB Level(12cR2) In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace. In Oracle 12c Release 2 each container in an instance can use its… Read More

Manage SQL Profile in Oracle Database

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Manage SQL Profile in Oracle Database A SQL profile is a collection of auxiliary statistics on a query, including all tables and columns referenced in the query. The profile is stored in the data dictionary. The optimizer… Read More

Resource Manager in Oracle

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Resource Manager in Oracle Resource Manager is an enterprise edition feature which will restrict sessions consuming high resources. We can assign shares to sessions depends on priority of session. We can restrict degree of parallelism for group… Read More

Oracle Sample Unix script to monitor select query during Load Test

3 years ago by Rakesh SahuOracle DB Admin

Hi Dears, In this article, we will see Oracle Sample Unix script to monitor select query during Load Test

#!/bin/bash
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_SID=ORCL

while :
do
sqlplus -s / as sysdba <<EOF
set pagesize 0
set linesize 300
select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|
|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv\$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv\$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;
EOF
date
echo "Hit [CTRL+C] to stop!"
sleep 5
done

#!/bin/bash

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

ORACLE_SID=ORCL

while :

sqlplus -s / as sysdba <<EOF

set pagesize 0

set linesize 300

select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|

|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv\$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv\$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;

EOF

date

echo "Hit [CTRL+C] to stop!"

sleep 5

done

Output :

Wed Mar 24 11:54:39 PDT 2021
Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:44 PDT 2021
Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:39 PDT 2021

Hit [CTRL+C] to stop!

no rows selected

Wed Mar 24 11:54:44 PDT 2021

Hit [CTRL+C] to stop!

no rows selected

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTEXPERTS is always… Read More

Configure Active DataGuard and DG BROKER

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Configure Active DataGuard and DG BROKER. Oracle Active Data Guard allows DBAs and users to run real-time queries on a physical standby system for reporting. Queries and reports can be offloaded from the production system to… Read More

Difference between ROWID and ROWID BATCHED in Oracle

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Difference between ROWID and ROWID BATCHED in Oracle. TABLE ACCESS BY INDEX ROWID BATCHED is new execution plan operation introduced in oracle 12c to improve performace by make efficient and minimal read writes to the disk. It… Read More

Silent Installation of Oracle 19c Database on AWS

3 years ago by Venkata Sai PavanOracle DB Admin

Hi Dears, In this article, we will see Silent Installation of Oracle 19c Database on AWS. Hardware Requirements: Yum Packages to install in server:

yum install -y oracle-epel-release-el7
yum install -y oracle-database-preinstall-19c
yum install -y bc
yum install -y binutils
yum install -y compat-libcap1
yum install -y compat-libstdc++-33
yum install -y dtrace-modules
yum install -y dtrace-modules-headers
yum install -y dtrace-modules-provider-headers
yum install -y dtrace-utils
yum install -y elfutils-libelf
yum install -y elfutils-libelf-devel
yum install -y fontconfig-devel
yum install -y glibc
yum install -y glibc-devel
yum install -y ksh
yum install -y libaio
yum install -y libaio-devel
yum install -y libdtrace-ctf-devel
yum install -y libXrender
yum install -y libXrender-devel
yum install -y libX11
yum install -y libXau
yum install -y libXi
yum install -y libXtst
yum install -y libgcc
yum install -y librdmacm-devel
yum install -y libstdc++
yum install -y libstdc++-devel
yum install -y libxcb
yum install -y make
yum install -y net-tools # Clusterware
yum install -y nfs-utils # ACFS
yum install -y python # ACFS
yum install -y python-configshell # ACFS
yum install -y python-rtslib # ACFS
yum install -y python-six # ACFS
yum install -y targetcli # ACFS
yum install -y smartmontools
yum install -y sysstat
yum install -y unixODBC
yum install -y chrony

yum install -y oracle-epel-release-el7

yum install -y oracle-database-preinstall-19c

yum install -y bc

yum install -y binutils

yum install -y compat-libcap1

yum install -y compat-libstdc++-33

yum install -y dtrace-modules

yum install -y dtrace-modules-headers

yum install -y dtrace-modules-provider-headers

yum install -y dtrace-utils

yum install -y elfutils-libelf

yum install -y elfutils-libelf-devel

yum install -y fontconfig-devel

yum install -y glibc

yum install -y glibc-devel

yum install -y ksh

yum install -y libaio

yum install -y libaio-devel

yum install -y libdtrace-ctf-devel

yum install -y libXrender

yum install -y libXrender-devel

yum install -y libX11

yum install -y libXau

yum install -y libXi

yum install -y libXtst

yum install -y libgcc

yum install -y librdmacm-devel

yum install -y libstdc++

yum install -y libstdc++-devel

yum install -y libxcb

yum install -y make

yum install -y net-tools # Clusterware

yum install -y nfs-utils # ACFS

yum install -y python # ACFS

yum install -y python-configshell # ACFS

yum install -y python-rtslib # ACFS

yum install -y python-six # ACFS

yum install -y targetcli # ACFS

yum install -y smartmontools

yum install -y sysstat

yum install -y unixODBC

yum install -y chrony

User/group Creation:

groupadd -g 650 oinstall
groupadd -g 651 dbaoper
groupadd -g 652 dba
groupadd -g 653 asmadmin
groupadd -g 654 asmoper
groupadd -g 655 asmdba
groupadd -g  656 backupdba
groupadd -g 54325 dgdba
groupadd -g 658 kmdba
groupadd -g 659 racdba
useradd oracle -u 1330 -d /home/oracle -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmdba,backupdba,dgdba,kmdba,racdba,ssh_in
useradd grid  -u 1331 -d /home/grid   -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmadmin,asmoper,asmdba,ssh_in

groupadd -g 650 oinstall

groupadd -g 651 dbaoper

groupadd -g 652 dba

groupadd -g 653 asmadmin

groupadd -g 654 asmoper

groupadd -g 655 asmdba

groupadd -g 656 backupdba

groupadd -g 54325 dgdba

groupadd -g 658 kmdba

groupadd -g 659 racdba

useradd oracle -u 1330 -d /home/oracle -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmdba,backupdba,dgdba,kmdba,racdba,ssh_in

useradd grid -u 1331 -d /home/grid -m -p $(echo “password” | openssl passwd -1 -stdin) -g oinstall -G dbaoper,dba,asmadmin,asmoper,asmdba,ssh_in

Setup Filesystem:

mkdir /u01/app/stage
mkdir -p /u01/app/oraInventory
mkdir -p /arch/oradata
mkdir -p /data/control
mkdir -p /arch/control
mkdir -p /data/oradata
mkdir -p /arch/archive
mkdir -p /u01/app/oracle/tde/testpdb1
mkdir -p /arch/redolog
mkdir -p /arch/flash
mkdir -p /arch/archive
chown -R oracle:oinstall /data /arch
cd /u01/app/
chown -R oracle:oinstall  *

mkdir /u01/app/stage

mkdir -p /u01/app/oraInventory

mkdir -p /arch/oradata

mkdir -p /data/control

mkdir -p /arch/control

mkdir -p /data/oradata

mkdir -p /arch/archive

mkdir -p /u01/app/oracle/tde/testpdb1

mkdir -p /arch/redolog

mkdir -p /arch/flash

mkdir -p /arch/archive

chown -R oracle:oinstall /data /arch

cd /u01/app/

chown -R oracle:oinstall *

Download Software from Oracle Site: Software installation by using attached response file: a) Unzip… Read More

Backup Details of SQL

3 years ago by KTEXPERTS TEAMSQL Server DBA

Dear Readers, In this article, we will see the following Backup Details of SQL. SQL Server offers many options for creating backups. As we discussed what types of backups can be performed based on the recovery model of the database. Here different types of backups… Read More

How to Install Ubuntu 20.04.1 LTS On Windows Using VirtualBox

3 years ago by Ramesh ReddyAWS

Hi Dears, In this article, we will see Install Ubuntu 20.04.1 LTS On Windows.. Steps to Follow Download and Install Oracle VM VirtualBox Download Ubuntu 20.04.1 LTS Create New Virtual Machine Install Ubuntu 20.04.1 LTS in Virtual Machine Install Guest Additions 1. Download and Install… Read More