Oracle DB Admin – Page 5

Oracle Sample Unix script to monitor select query during Load Test

4 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

4 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

4 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

4 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

How to add Container database to Existing RMAN Recovery catalog

4 years ago by Binnary Ajay KumarOracle DB Admin

Hello Readers, In this article ,we will see how to a container database to the existing RMAN recovery catalog. Please check below article before adding container database to RMAN Catalog. How to Create an RMAN Recovery Catalog Target Database : … Read More

Rebuild Stand By database when PDB restored from restore point/Issue with the PDB

4 years ago by Venkata Sai PavanOracle DB Admin

Hello Readers, In this article, we will see Recovery models in SQL Server. 1. Take backup of the primary database

run {
allocate channel ch1 device type disk  maxpiecesize 2G;
allocate channel ch2 device type disk  maxpiecesize 2G;
backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';
release channel ch1;
release channel ch2;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;

run {

allocate channel ch1 device type disk maxpiecesize 2G;

allocate channel ch2 device type disk maxpiecesize 2G;

backup incremental level 0 cumulative database format ='/rman/dbbackups/DB_BKP/full_%d_%T_df_%f_%s' tag 'INCREMENTAL LEVEL 0';

release channel ch1;

release channel ch2;

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

2. Create standby control file from primary database.

RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

1	RMAN> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/ForStandbyCTRL.bck';

3. Transfer backup pieces along with control file from primary server to standby server.… Read More

How to Create an RMAN Recovery Catalog

4 years ago by Binnary Ajay KumarOracle DB Admin

Hello Readers, In this article, we will see Creation of RMAN Recovery Catalog. Target Database : Catalog Database: Name : AVPROD … Read More

Oracle : Important Commands for Dataguard (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Commands for Dataguard (SecureCRT) Menubar Name : Dataguard Tab Name : Instance_info

set linesize 300 
col machine for a60 
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; 

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

set linesize 300

col machine for a60

select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance;

NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE

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

ORCLTEST - ORCLTEST1@SERVER1234.sephoraus.com - MSTORCLTEST - 12.1.0.2.0 - READ ONLY WITH APPLY - 04-FEB-2021 00:59:59 - PHYSICAL STANDBY

Tab Name : DG_GAP

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" 
FROM 
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, 
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)  
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL 
WHERE 
ARCH.THREAD# = APPL.THREAD# 
ORDER BY 1; 

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied; 

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  44791                 44791          0
         2                  42354                 42354          0
         3                  40223                 40223          0

         

SQL> 
   THREAD# APPLIED   MAX(SEQUENCE#)
---------- --------- --------------
         1 IN-MEMORY          44791
         1 NO                 39800
         1 YES                44790
         2 NO                 38782
         2 YES                42354
         3 NO                 38673
         3 YES                40223

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",

(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME)

FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

select thread#,applied,max(sequence#) from gv$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#,applied order by thread#,applied;

Thread Last Sequence Received Last Sequence Applied Difference

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

1 44791 44791 0

2 42354 42354 0

3 40223 40223 0

SQL>

THREAD# APPLIED MAX(SEQUENCE#)

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

1 IN-MEMORY 44791

1 NO 39800

1 YES 44790

2 NO 38782

2 YES 42354

3 NO 38673

3 YES 40223

Tab Name : ERR_DEST

set linesize 300 
col dest_name for a30 
col error for a70 
select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4); 
select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1; 

   DEST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID
         2 LOG_ARCHIVE_DEST_2             INACTIVE
         3 LOG_ARCHIVE_DEST_3             INACTIVE
         4 LOG_ARCHIVE_DEST_4             DEFERRED

SQL> 
   INST_ID DEST_NAME                      STATUS    ERROR
---------- ------------------------------ --------- --------------
         1 LOG_ARCHIVE_DEST_1             VALID

set linesize 300

col dest_name for a30

col error for a70

select dest_id,dest_name,status,error from v$archive_dest where dest_id in (1,2,3,4);

select inst_id,dest_name,status,error from gv$archive_dest where dest_id in (1,2,3,4) and status='VALID' order by 1;

DEST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

2 LOG_ARCHIVE_DEST_2 INACTIVE

3 LOG_ARCHIVE_DEST_3 INACTIVE

4 LOG_ARCHIVE_DEST_4 DEFERRED

SQL>

INST_ID DEST_NAME STATUS ERROR

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

1 LOG_ARCHIVE_DEST_1 VALID

Tab Name : Date Format

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

1	alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Tab Name :… Read More

Autonomous Health Framework

4 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the Autonomous Health Framework. AHF includes number of diagnostic tools in a single bundle and helps us to gather various types og information like upgrading info. , oracheck….. AHF combines Trace file analyzer(TFA), orachk etc.., which oracle… Read More

Oracle : Important Queries for Parallel Feature (SecureCRT)

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT). Menubar Name : Parallel Tab Name : Create table show parameter parallel_max_servers

create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ;

1	create table owner.tablename_bkp as select /+ parallel(tablename,64) / * from owner.tablename ;

create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP;

1	create table vinod.tablename_bkp as select /+ parallel(EMP,4) / * from SCOTT.EMP;

Tab Name : Finding_Parallel_Queries

set linesize 300  
col username for a19  
col terminal for a15  
col osuser for a15  
col program for a45 
col time for a25

set linesize 300

col username for a19

col terminal for a15

col osuser for a15

col program for a45

col time for a25

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER   
from gv$session where program like '%P0%' and sql_id is not null;   

   INST_ID        SID SQL_ID        PROGRAM                                       USERNAME            TIME                      TERMINAL        OSUSER
---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- ---------------
         1         58 29d991q9rq5kn oracle@awsserver003 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         1         59 29d991q9rq5kn oracle@awsserver003 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2         37 29d991q9rq5kn oracle@awsserver004 (P001)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle
         2        207 29d991q9rq5kn oracle@awsserver004 (P000)                    SYS                 27-FEB-2021 21:08:38      pts/1           oracle

select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER

from gv$session where program like '%P0%' and sql_id is not null;

INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER

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

1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle

2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle

Tab Name : Select Query with Parallel Option… Read More