Oracle Database Scripts

Tuesday, May 16, 2023

RMAN BACKUP FORMAT and Commands

always do pre-measures:-
cat /etc/oratab or cat /var/opt/oracle/oratab
export ORACLE_HOME=
export ORACLE_SID=
export PATH=$ORACLE_HOME/bin:$PATH

echo $ORACLE_SID

rman target /

RMAN> backup database format '/path/to/backup/location/db_backup_%U';
RMAN> backup database format '/path/to/backup/location/%d_%t_%U';
Note:-
"backup database format '/path/to/backup/location/%d_%t_%U';" will take backup of the entire database including data files, control files, and archived logs. The command is called Backup database.

if you want to speed up the backup you can mention "parallel n"
example :-
RMAN> backup database format '/path/to/backup/location/%d_%t_%U' parallel 4;
this will make four files

some flags used in rman :-
%d :- database name
%t :- backup set
%s :- backup set number
%p :- piece number
%U :- unique backup piece number
%F :- filename with full name
%T :- backup piece tag

for example :- %d_%t means $ORACLE_SID_$(DATE '+%Y%m%d')
pune_20230516 ---> this will come after using %d_%t

RMAN > backup archivelog all format '/path/to/backup/location/arc_%d_%t_%U';

To delete archivelog older :-
----------------------------------
RMAN> list archivelog until time 'sysdate - n';
RMAN > delete noprompt archivelog until time 'sysdate - n';
RMAN > crosscheck archivelog all;

sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount;

RMAN > catalog start with '/path/to/backup/location';
RMAN > restore database;
RMAN > recover database;

sqlplus / as sysdba
SQL> alter database open resetlogs;

Restore and Recovery :-

backup current controlfile format '/path/to/bkp_controlfile.bkp';
alter database nomount;
restore controlfile from '/path/to/bkp_controlfile.bkp';
or
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open resetlogs;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++ RMAN FILES DETAILS ++++++++++++++++++++++++++++++
select fname,to_char(completion_time,'DD-Mon-YYYY HH:MI:SS') "completion_time" from v$backup_files where completion_time is not null and fname like '%dbf%' order by completion_time desc;

select * from v$rman_configuration;
select * from v$rman_backup_job_details;
select * from v$rman_backup_type;

to_char(date,'DD-MONTH-YYYY HH12:MI:SS') :- 08-September-2023 10:02:32
to_char(date,'DD-MONTH-YYYY HH24:MI:SS') :- 08-September-2023 21:02:32

-----------------------------------------------------------------------------------------------
rman target /
RMAN > run {
delete noprompt archivelog until time 'sysdate-2';
crosscheck archivelog all;
}

Incremental backups :-
Level 0 : full backup
Level 1 : incremental backup

To perform a level 0 backup (full backup), you can use:
backup incremental level 0 database;
To perform a level 1 backup ( incremental backup), you can use:
backup incremental level 1 database;
To perform cumulative incremental backup, you can use:
backup incremental level 1 cumulative database;
Specify the backup location:
backup incremental level 1 database tag 'level1' format '/path/to/backup/%U';

The increment backup in RMAN is done if you specify a LEVEL <n> option, where n can by either 0 or 1
A LEVEL 0 incremental backup means complete backup just like Full backup
A LEVEL 1 incremental backup copies all blocks changed since last LEVEL 0 or LEVEL 1 backup.
A LEVEL 1 COMULATIVE backup copies all blocks changed since last LEVEL 0 backup.
In incremental backup strategy you will first need to take complete backup i.e. LEVEL 0 backup
++++++++++++++++++++++++++++++++++++++++++++++++++++++
Point in time recovery in Oracle using RMAN (recovery manager) allows you to recover your database to a specific point in time, i.e. any time you would like to make the database available present in database backup.
step 1:- Connect to the RMAN
echo $ORACLE_SID
rman target /
or
rman target sys/passwd@database_netservice
step 2:- restore the control file to the location desired

RMAN >
run {
allocate channel ch1 type disk;
restore controlfile to '/path/to/new/controlfile.ctl';
}

step 3:- Mount the database since backup/restore can only perform in mount mode

run {
allocate channel ch1 type disk;
sql 'alter database mount';
}

step 4:- Specify the point in time i.e. mention the time

run {
set until time 'to_date('yyyy-mm-dd hh24:mi:ss','yyyy-mm-dd hh24:mi:ss')';
}

step 5:- Do Incomplete Recovery to open database with reset logs option since we don't want to use old redo logs.

run {
allocate channel ch1 type disk;
recover database;
}

step 6:- Open the database with reset logs option.

run {
sql 'alter database open resetlogs';
}

step 7:- Complete the recovery now

run {
allocate channel ch1 type disk;
backup database format '/path/to/backup/%d_%t.dbf';
}

+++++++++++RMAN DETAIL SCRIPT ++++++++++++++++++++
RMAN BACKUP STATUS DETAILS SCRIPTS

######## RMAN Backup logs details will show which are files completed #############
cd logs/voyager/backups
voyager:/home/oracle/ $ cd logs/voyager/backups
voyager:logs/voyager/backups/ $ ls -lrth
total 3218
-rw-r----- 1 oracle dba 54K Feb 21 08:17 DB_Full_Backup_202202210158.log
-rw-r----- 1 oracle dba 54K Feb 21 08:18 DB_Full_Backup_202202210155.log
-rw-r----- 1 oracle dba 103K Feb 21 23:27 DB_Full_Backup_202202212300.log
-rw-r----- 1 oracle dba 155K Feb 23 01:09 DB_Full_Backup_202202222300.log
-rw-r----- 1 oracle dba 132K Feb 24 01:13 DB_Full_Backup_202202232300.log
-rw-r----- 1 oracle dba 29K Feb 25 00:25 DB_Full_Backup_202202242300.log
voyager:logs/voyager/backups/ $ more DB_Full_Backup_202202242300.log

---------------------------------------------------------------------
SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- -------------- -------------- -------
29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- -------------- -------------- -------
29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS
----------- ------------- --------- -------------- -------------- -------
29 DB FULL COMPLETED 01/07/14 10:28 01/07/14 10:29 .03
============================================================
next script
---------------------------------------------------------------------------
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

SID SERIAL# CONTEXT SOFAR TOTALWORK %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
18 29 1 9115569 19258880 47.33
===========================================================================
next script
----------------------------------------------------------------------------------
You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

SESSION_KEY INPUT_TYPE STATUS RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME HOURS
----------- ------------- ---------- ------------------- ------------------- --------
137764 DB FULL COMPLETED 06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
137770 ARCHIVELOG COMPLETED 06-06-2017 04:00:29 06-06-2017 04:01:05 .01
137778 ARCHIVELOG COMPLETED 06-06-2017 06:00:27 06-06-2017 06:00:35 .00
137782 ARCHIVELOG COMPLETED 06-06-2017 08:00:32 06-06-2017 08:03:36 .05
137786 ARCHIVELOG COMPLETED 06-06-2017 10:00:30 06-06-2017 10:02:03 .03
137790 ARCHIVELOG COMPLETED 06-06-2017 12:00:30 06-06-2017 12:02:34 .03
137794 ARCHIVELOG COMPLETED 06-06-2017 14:00:30 06-06-2017 14:02:58 .04

=======================================================
next script
-----------------------------------------------------------
select OUTPUT from V$RMAN_OUTPUT;
=======================================================

RMAN COMMANDS:---
=======================

To show all configuration details :- RMAN > show all;
To show backups details:- RMAN > list backup;
To show archivelog details:- RMAN > list archivelog until time 'sysdate - 12';
To show backup summary:- RMAN > list backup summary;
To delete expired backups:- RMAN > delete noprompt expired backup;
To crosscheck backups:- RMAN > crosscheck backup;
To delete obsolute backups:- RMAN > delete noprompt obsolute;
To list backups for sysdate:- RMAN > list backup completed before 'sysdate-7';
To delete backup after time:- RMAN > list backup completed after 'sysdate-8';
To delete backup between time:-
RMAN > list backup completed between 'sysdate-7' and 'sysdate-3';
To delete backup before time:- RMAN > delete backup completed before 'sysdate-7';
To delete backup after time:- RMAN > delete backup completed after 'sysdate-8';
To delete backup between time:-
RMAN > delete backup completed between 'sysdate-7' and 'sysdate-3';

+++++++ RMAN BKUP Location command ++++++++++++++++++
echo $(echo "select fname from v\$backup_files where file_type='PIECE' order by completion_time desc fetch first 1 rows only;" | sqlplus \/ as sysdba | dirname $(grep -e "^/[[:graph:]]*"))

another best script that will work on standalone DB :-
cd $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")
another best script that will work on RAC DB :-
echo $(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -e "^+")
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@pm latestFolder]$ cat example.sh
#!/bin/sh
# below command will extract timing of latest rman backup
timing=$(echo "select to_char(newest_backup_time,'YYYY-MM-DD HH:MI:SS') "latest_backup_time" from v\$backup_set_summary order by newest_backup_time desc;" | sqlplus \/ as sysdba | grep -v "[[:alpha:]]" | grep -E "^[[:digit:]]{4}+-+[[:digit:]]{0,2}+-[[:digit:]]{0,2}+[[:space:]]+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}+:+[[:digit:]]{0,2}")
# below command will extract backup location
backup_location=$(echo "select name from v\$recovery_file_dest;" | sqlplus \/ as sysdba | grep -E "^/[[:graph:]]*")
# below command will fine files
find ${backup_location}-maxdepth 1 -type f -not -newermt "${timing}"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
or we can find backups older than today

find . -type f -not -newermt "$(date -d '0 days ago' +'%Y-%m-%d %H:%M:%S')"
--------------------
du -b *2023_12_30_dbf_* | awk '{x+=$1};END{print x/(1024*1024*1024),"GB"}'
--------------------

How to kill ORACLE RMAN backup job:-
---------------------------------------------------------------------------------------------------------------------
SQL>
select b.sid, b.serial#, a.spid, b.client_info from gv$process a, gv$session b
where a.addr=b.paddr and client_info like 'rman%';

SID SERIAL# SPID CLIENT_INFO
---------------------------------------------------------------------
590 452 888 rman channel=full_chanel

SQL >
alter system kill session '&sid, &serial' ;

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

-- 1. Full Level 0 Backup with KEEP FOREVER and TAG

BACKUP INCREMENTAL LEVEL 0

DATABASE

TAG 'PREUPGRADE_LEVEL0_BKP'

KEEP FOREVER;

-- 2. Full Backup + Control File + Archivelogs + SPFILE

BACKUP INCREMENTAL LEVEL 0

DATABASE

INCLUDE CURRENT CONTROLFILE

PLUS ARCHIVELOG

TAG 'PREUPGRADE_FULL_BKP'

KEEP FOREVER;

-- 3. Separate Backup for SPFILE and Control File

BACKUP SPFILE TAG 'PREUPGRADE_SPFILE_BKP';

BACKUP CURRENT CONTROLFILE TAG 'PREUPGRADE_CONTROLFILE_BKP';

-- 4. Mark Existing Backup as KEEP FOREVER

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' KEEP FOREVER;

-- 5. Remove KEEP FOREVER Status

CHANGE BACKUP TAG 'PREUPGRADE_FULL_BKP' NOKEEP;

-- 6. Force Delete Backup (Even with KEEP FOREVER)

DELETE FORCE BACKUP TAG 'PREUPGRADE_FULL_BKP';

-- 7. Crosscheck and Validate Backups

CROSSCHECK BACKUP;

CROSSCHECK ARCHIVELOG ALL;

VALIDATE BACKUPSET TAG 'PREUPGRADE_FULL_BKP';

-- 8. Backup All Datafiles into a Single File

RUN {

ALLOCATE CHANNEL ch1 DEVICE TYPE DISK;

BACKUP AS BACKUPSET

DATABASE

FORMAT '/u01/backup/full_db_single_file.bkp';

RELEASE CHANNEL ch1;

}

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

How to restore & recover database using tag

STARTUP NOMOUNT;

-- Restore control file from the tagged backup

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- Mount the database using restored controlfile

ALTER DATABASE MOUNT;

-- Restore all datafiles from the backup with the given tag

RESTORE DATABASE FROM TAG 'FULL_BKP_11Jul2025';

-- Recover database using available archive logs and redo

RECOVER DATABASE;

-- Open the database

ALTER DATABASE OPEN;

If needs to recover control file also

Set Unitl uses in Oracle RMAN command:

Incremental backup on standby db:

-- Step 1: Start the standby instance without mounting (control file is missing)

STARTUP NOMOUNT;

-- 🔎 Reason: Required before restoring control file. DB can't mount without it.

-- Step 2: Restore control file from a known TAG or path (taken from primary)

RESTORE CONTROLFILE FROM TAG 'FULL_BKP_11Jul2025';

-- 🔎 Reason: Replaces lost/corrupted standby control file with a consistent one from backup.

-- Step 3: Mount the database to allow access to datafile metadata

ALTER DATABASE MOUNT;

-- 🔎 Reason: Required before restoring or recovering datafiles. Now RMAN can read file structure.

-- Step 4: Catalog the incremental backup if standby doesn't know about it

CATALOG START WITH '/u01/backup/';

-- 🔎 Reason: Tells RMAN about external backup files if they weren’t already registered in the control file.

-- Step 5: Restore the database (optional - only if full restore needed)

RESTORE DATABASE FROM TAG 'INCR_STANDBY_BKP_11Jul2025';

-- 🔎 Reason: Reapplies full or incremental image copies of the datafiles if the standby is outdated.

-- Step 6: Recover the database using archived redo or incremental changes

RECOVER DATABASE;

-- 🔎 Reason: Applies redo to bring the datafiles to a consistent state.

-- Step 7: Restart standby recovery (if part of Data Guard)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- 🔎 Reason: Starts real-time redo apply in standby mode, reconnects to primary for log shipping.

Summary:

Rolling forward Standby db:

-- STEP 1: Get SCN from Standby

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

SELECT CURRENT_SCN FROM V$DATABASE;

-- Assume output SCN is: 123456789

-- STEP 2: Take Incremental Backup on PRIMARY

-- Run on PRIMARY in RMAN

rman target /

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL FROM SCN 123456789 DATABASE FORMAT '/backup/inc_for_stby_%U.bkp' TAG 'INC_FOR_STANDBY';

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/standby_ctrl.bkp';

RELEASE CHANNEL c1;

}

-- STEP 3: Transfer Backup Files to STANDBY

-- Run on OS shell (PRIMARY)

scp /backup/inc_for_stby_*.bkp oracle@standby_host:/backup/

scp /backup/standby_ctrl.bkp oracle@standby_host:/backup/

-- STEP 4: Restore and Apply on STANDBY

-- Run on STANDBY in RMAN

rman target /

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

RESTORE CONTROLFILE FROM '/backup/standby_ctrl.bkp';

ALTER DATABASE MOUNT;

-- STEP 5: Catalog and Recover

CATALOG START WITH '/backup/';

RECOVER DATABASE;

-- STEP 6: Start Standby Recovery (MRP)

-- Run on STANDBY SQL*Plus

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Recover spfile from backup:

-- STEP 1: Connect to RMAN

rman target /

-- STEP 2: (Optional) Set DBID if database is down

SET DBID = 123456789;

-- STEP 3: Startup NOMOUNT (use PFILE if needed)

-- If no spfile or pfile exists, create a minimal init file manually:

echo "db_name='ORCL'" > $ORACLE_HOME/dbs/initORCL.ora

-- Start database with temporary PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='$ORACLE_HOME/dbs/initORCL.ora';

-- STEP 4: Restore SPFILE from autobackup

-- Run in RMAN

RESTORE SPFILE FROM AUTOBACKUP;

-- or restore to temporary PFILE location (if you want to edit first)

RESTORE SPFILE TO PFILE '/tmp/init_temp.ora' FROM AUTOBACKUP;

-- STEP 5: (Optional) Edit temp PFILE if needed

vi /tmp/init_temp.ora

-- STEP 6: Create SPFILE from edited PFILE

sqlplus / as sysdba

STARTUP NOMOUNT PFILE='/tmp/init_temp.ora';

CREATE SPFILE FROM PFILE='/tmp/init_temp.ora';

-- STEP 7: Restart Database with SPFILE

SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT;

-- ✅ SPFILE is now restored and database is using it

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

Saturday, May 13, 2023

User Profile Manipulation In Oracle

--- We'll Find Profile for the user from below query
select username,profile from dba_users where username=upper('&username');

--- Now we'll find profile resource limit for the profile
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name');

--- We'll create new profile and then assign to scott user

CREATE PROFILE c##scott_profile LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED;

--- We'll change profile resoure limits
ALTER PROFILE c##scott_profile LIMIT
password_lock_time 2;

--- We'll verify profile resource limit changed or not
select profile,resource_name,limit from dba_profiles where profile=upper('&profile_name')
and resource_name=upper('&resource_name');

--- We'll change user profile to new one
ALTER USER scott PROFILE c##scott_profile;

--- We'll revert back profile
alter user scott profile default;

--- We can drop the profile
drop profile profile_name;

++++++++++++++++++++++++ User Details +++++++++++++++++++

SELECT username
	,account_status
	,to_char(created, 'DD/MM/YYYY HH:MI:SS AM') created
	,to_char(lock_date, 'DD/MM/YYYY HH:MI:SS AM') lock_date
	,to_char(expiry_date, 'DD/MM/YYYY HH:MI:SS AM') expiry_date
	,to_char(last_login, 'DD/MM/YYYY HH:MI:SS AM') last_login
	,to_char(password_change_date, 'DD/MM/YYYY HH:MI:SS AM') password_change_date
	,PROFILE
FROM dba_users
WHERE username = '&username';

Friday, May 12, 2023

FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database.

`FLASHBACK_TRANSACTION_QUERY` displays information about all flashback transaction queries in the database.

The database must have at least minimal supplemental logging enabled to avoid unpredictable behavior.

select

xid,

start_scn,

start_timestamp,

commit_scn,

commit_timestamp,

logon_user,

undo_change#,

operation,

table_name,

table_owner,

row_id,

undo_sql

from

flashback_transaction_query;

if you want to know column_name,data_type for a table,view, etc then execute below query:-

select column_name,data_type from all_tab_columns where table_name='&table';

Resolve In-Doubt-Transaction (IDT)

select LOCAL_TRAN_ID from dba_2pc_pending;
retruns idt Value
rollback force 'IDT Value' ;
commit;
commit;
exec dbms_transaction.purge_lost_db_entry('IDT Value');
commit;

ORACLE SESSIONS MONITORING SQL QUERIES

SQL>
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

TO find out sql_id for the above sid:
SQL> select sql_id from gv$session where sid='&SID';
To find sql text for the above sql_id:
SQL> select sql_fulltext from gv$sql where sql_id='&sql_id';
To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from gv$session where sql_id='&sql_id'; ---------------------------> best query for getting os user

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
if object/table locked ora error :
alter session set ddl_lock_timeout=30;
alter system kill session 'sid, serial#';

=============================================
select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b --v$sqltext b
where a.SQL_ID = b.SQL_ID and sql_id='&sql_id'
order by a.SQL_EXEC_START asc;
=============================
SELECT SESSION_ID, program, module , event,sql_id ,TIME_WAITED,SESSION_STATE
FROM gv$active_session_history where sql_id='&sqlid';
=======================================================
select distinct inst_id,sample_time,time_waited, session_id,session_serial#,sql_id from gv$active_session_history where sql_id='&sqlid' order by 1 desc;
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;
1b3mkpnwf6c9c
5968nvz4bcn7n
========================
SELECT distinct sql_text FROM gv$sql WHERE sql_id='&sql_id';
===================================================
oracle_sql_text_spid.sql
----------------------------------
select s.sql_text,s.sql_fulltext from v$sql s
where sql_id in (SELECT A.SQL_ID FROM V$SESSION A,V$PROCESS B
WHERE A.PADDR=B.ADDR AND B.SPID='6495');
=================================================================
select t.sql_id,
t.sql_text,
s.executions_total,
s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where t.sql_id=s.sql_id and
s.snap_id between 9349 and 9351;
or t.sql_id='&sql_id';
====================================================================
col username for a15
col machine for a20
col event for a20
col program for a20
col osuser for a20
set line 3000
set pagesize 2000
SELECT
Inst_id,
s.username,
s.blocking_session blk_sess,
s.blocking_instance blk_inst,
s.sid,
s.serial#,
s.sql_id,
s.status,
s.seconds_in_wait waittime,
s.event,
s.machine
FROM
gv$session s
WHERE
blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

****************** getting inactive sessions ***********************************
select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE';

***************** details of session ***************************
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;

select inst_id, status,first_change_time,last_change_time,sql_exec_start,plan_operation,
plan_object_owner,plan_object_name,plan_object_type,plan_time, plan_temp_space, starts,output_rows from gv$sql_plan_monitor
where sql_id='&sql_id' and rownum<=5;
*************** osusername,machine,termial and program lockwait details ************
select sid,serial#,username,lockwait,status,osuser,machine,terminal,program,
server,final_blocking_session from gv$session where username='NIMBUS';
desc all_objects;
****************ddl details for objects ********************************************
select owner,object_name,object_type,last_ddl_time,status,(select host_name from v$instance) "host" from all_objects where object_name='SUBSCRIPTION';
SELECT ORACLE_USERNAME, OS_USER_NAME,LOCKED_MODE,OBJECT_NAME,OBJECT_TYPE
FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
WHERE A.OBJECT_ID=B.OBJECT_ID;

select * from dba_ddl_locks;

select column_name,data_type,(select host_name from v$instance) as host from all_tab_columns where table_name='UDAS_DTVREQUEST' ;
********************** kill multiple sessions ****************************
begin
for x in (select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE')
loop
dbms_output.put_line('alter system kill session '||''''||x.sid||', '||x.serial#||''''||';');
end loop;
end;
/

LOCKED OBJECTS SQL QUERIES IN ORACLE

Locks on tables by sessions :

solution:

1) commit;

2) rollback;

3) kill the session;

4) wait to complete;

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

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name

FROM V$Locked_Object A, All_Objects B

WHERE A.Object_ID = B.Object_ID;

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

select

c.owner,

c.object_name,

c.object_type,

b.sid,

b.serial#,

b.status,

b.osuser,

b.machine

from

v$locked_object a ,

v$session b,

dba_objects c

where

b.sid = a.session_id

and

a.object_id = c.object_id;

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

column oracle_username format a15;

column os_user_name format a15;

column object_name format a37;

column object_type format a37;

select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from

(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,

(select object_id, owner, object_name,object_type from dba_objects) b

where a.object_id=b.object_id;

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

Thursday, May 11, 2023

TABLESPACE, DATAFILES, ASM DISKGROUP IN ORACLE SQL QUERIES

For checking asm disk group :-

su - oragrid

sqlplus / as sysasm

tablespace,datafiles,asm,etc
gv$filespace_usage RAC view;
v$filespace_usage view;

dba_tablespace_usage_metrics view:-
dba_hist_tbspc_space_usage AWR tablespace :-

SELECT name ,round(bytes/power(1024,3),2) size_gb
FROM v$datafile where name like '+INSTANCE_ORADATA%' order by size_gb desc;
desc v$datafile;

select count(round(bytes/power(1024,3),2)) count,sum(round(bytes/power(1024,3),2)) size_gb
from v$datafile
where
round(bytes/power(1024,3),2) >=10 and round(bytes/power(1024,3),2) < 32 and name like '+INSTANCE_ORADATA%';

select sum(round(bytes/power(1024,3),2)) size_gb from v$datafile where name like '+INSTANCE_ORADATA%';

select
tablespace_name,
round(used_percent,2) used_percent
from
dba_tablespace_usage_metrics;

desc gv$asm_diskgroup;

select name,round(total_mb/power(1023,2),2) from gv$asm_diskgroup;

select distinct name,round(total_mb/power(1024,1)) total_size_GB,round(free_mb/power(1024,1)) free_size_GB,
round(round(free_mb/power(1024,1),2)/round(total_mb/power(1024,1),2)*100) percent_free,state from gv$asm_diskgroup;
--where name like '%INSTANCE_ORADATA%';

--- free space in asm diskgroup ---
select distinct name,round(total_mb/power(1024,1)) total_size_GB,round(free_mb/power(1024,1)) free_size_GB,
round(round(free_mb/power(1024,1),2)/round(total_mb/power(1024,1),2)*100) percent_free,
(round((total_mb - free_mb)/power(1024,1),2)) USED_GB
,state from gv$asm_diskgroup;

select ts.tablespace_name,df.name from dba_tablespace_usage_metrics ts inner join v$datafile df on df.name is not null where df.name like
'+INSTANCE_ORADATA%';

select distinct
a.name Name,
round(a.bytes/power(1024,3),2) size_GB,
b.phyrds,
b.phywrts
from gv$datafile a, gv$filestat b
where a.file# = b.file#
order by size_GB desc;

Wednesday, May 10, 2023

Install OEM Oracle Enterprise Manager

1) install oracle db sid/service_name "emcdb" and pdb "emrep"
2) check /tmp space should be >15gb
3) use normal listener without sid_listener_list
4) sqlplus / as sysdba
5) alter pluggable database emrep open read write; alter pluggable database emrep save state;
6) execute following as sysdba on cdb
7)
-- Recommended minimum settings.
alter system set "_allow_insert_with_update_check"=true scope=both;
alter system set session_cached_cursors=200 scope=spfile;
alter system set processes=600 scope=spfile;
alter system set pga_aggregate_target=450M scope=spfile;
alter system set sga_target=800M scope=spfile;
alter system set shared_pool_size=600M scope=spfile;
-- Unset any adaptive optimizer settings that were set for the previous installation. They are not needed now.
alter system reset "_optimizer_nlj_hj_adaptive_join" scope=both sid='*';
alter system reset "_optimizer_strans_adaptive_pruning" scope=both sid='*';
alter system reset "_px_adaptive_dist_method" scope=both sid='*';
alter system reset "_sql_plan_directive_mgmt_control" scope=both sid='*';
alter system reset "_optimizer_dsdir_usage_control" scope=both sid='*';
alter system reset "_optimizer_use_feedback" scope=both sid='*';
alter system reset "_optimizer_gather_feedback" scope=both sid='*';
alter system reset "_optimizer_performance_feedback" scope=both sid='*';
8) Restart the repository database.
9) check connecting remotely using sql developer/toad
10) connectivity for cdb "emcdb" --> host:kayyum port:1521 sid:emcdb
11) connectivity for pdb "emrep" --> host:kayyum port:1521 service:emrep
12) copy all oem files *.zip and .bin to /u02
13) chmog u+x *.bin
14) ./em13500_linux64.bin
15) wait for universal installer screen to popup
vm installation:-
/ ---> 150gb
/tmp-->30gb
/home-->10gb
/var -->5gb
/biosboot ---> 1gb
network:-
bridged network automatic dhcp
NAT network assign 192.168.56.71

Sunday, May 7, 2023

File System Free Space Automatically Shell Script Linux

touch freethespace.sh

chmod 777 freethespace.sh

vim freethespace.sh

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

#!/bin/bash

echo "Enter year:"

read year

echo "Enter month:"

read month

echo "Enter day:"

read day

rm -rf ${year}_${month}_${day}_*

#rm -rf $(date --date='1 days ago' '+%Y_%m_%d')*

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

To check file exists in current directory :

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

#!/bin/bash

echo "Enter your file to be search:"

read file

if test -f $file

then

echo "file does exists !"

else

echo "no such file found !"

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

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Oct 18" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}'

redo_551326936_2_657440.arc

redo_551326936_2_657441.arc

redo_551326936_1_758270.arc

redo_551326936_1_758271.arc

redo_551326936_1_758272.arc

redo_551326936_2_657442.arc

redo_551326936_1_758273.arc

redo_551326936_1_758274.arc

redo_551326936_1_758275.arc

redo_551326936_2_657443.arc

redo_551326936_1_758276.arc

redo_551326936_1_758277.arc

redo_551326936_2_657444.arc

redo_551326936_1_758278.arc

redo_551326936_1_758279.arc

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 22" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}' | while read i;

do rm -rf $i ; done

ls -lrt *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 23" | awk '{print $4}' | while read i; do rm -rf $i; done

ads00

ls -lrt | head -n 500 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}' | while read n; do rm -rf $n; done

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

for host and others to free up *.arc files

freeup.sh

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

#!/bin/bash

target_dir=/pac/host/bkup/orabkup01/host/archivelog/

target_dir_no_of_files=$(ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | wc -l)

if [ "${target_dir_no_of_files}" -gt "3000" ]

then

echo "you can remove files"

ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | head -n 2000 | awk '{print $9}' | while read n; do rm -rf $n; done

else

echo "files less than 3000"

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

host:/pac/host/arch/host/archivelog/ $ echo `date --date='1 days ago' +'%Y_%m_%d'`

2022_12_08

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

ls -lrt 2022_12_10/*.arc | head -n 10000 | awk '{print $9}' | while read n; do rm -rf $n; done

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

62G 2022_12_10

463G 2022_12_11

host:/pac/host/arch/host/archivelog/ $ ls -lrth `date --date='1 days ago' +'%Y_%m_%d'`/*.arc | wc -l

1453

host:/pac/host/arch/host/archivelog/ $ ls -lrt 2022_12_10/*.arc | wc -l

1453

host:/pac/host/arch/host/archivelog/ $ ls -lrth `date --date='0 days ago' +'%Y_%m_%d'`/*.arc | wc -l

10695

host:/pac/host/arch/host/archivelog/ $ echo `date --date='0 days ago' +'%Y_%m_%d'`

2022_12_11

pwd

ls -lrth /pac/host/arch/host/archivelog/`date --date='0 days ago' +'%Y_%m_%d'`/*.arc 2>/dev/null | wc -l

ls -lrth /pac/host/arch/host/archivelog/`date --date='1 days ago' +'%Y_%m_%d'`/*.arc 2>/dev/null | wc -l

/pac/host/arch/host/archivelog/`date --date='0 days ago' +'%Y_%m_%d'`/*.arc

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

#!/bin/bash

target_dir=/pac/host/bkup/orabkup01/host/archivelog/

target_dir_no_of_files=$(ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | wc -l)

if [ "$(ps -ef | grep ora_pmon_ | wc -l)" -gt "1" ]

then

if [ "${target_dir_no_of_files}" -gt "3000" ]

then

echo "you can remove files"

ls -lrt /pac/host/bkup/orabkup01/host/archivelog/*.arc | head -n 2000 | awk '{print $9}' | while read n; do rm -rf $n; done

df -h /pac | mail -s "Space has been free now on host " example@gmail.com

else

echo "files less than 3000"

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

#!/bin/bash

target_dir_yesterday=/pac/host/arch/host/archivelog/$(date --date='1 days ago' +'%Y_%m_%d')

target_dir_today_no_of_files=$(ls -lrt /pac/host/arch/host/archivelog/$(date --date='0 days ago' +'%Y_%m_%d')/*.arc | wc -l)

#echo $target_dir_yesterday

#echo $target_dir_today_no_of_files

#echo $(ps -ef | grep ora_pmon_ | wc -l)

if [ "$(ps -ef | grep ora_pmon_ | wc -l)" -gt "1" ]

then

if [ "${target_dir_today_no_of_files}" -gt "10000" ]

then

echo "you can remove files"

#to delete the archivelogs

ls -lrt $target_dir_yesterday/*.arc 2>/dev/null | awk '{print $9}' | while read m; do rm -rf $m; done

ls -lrt /pac/host/arch/host/archivelog/$(date --date='0 days ago' +'%Y_%m_%d')/*.arc | head -n 8000 | awk '{print $9}' | while read n;do rm -rf $n; done

# mail to respect groups

df -h /pac | mail -s "Space has been free now on host " example@gmail.com

else

echo "files less than 3000"

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

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}'

Use% Mounted

0% /dev

0% /dev/shm

11% /run

0% /sys/fs/cgroup

24% /

26% /boot

77% /usr/local/opt/oracle

1% /opt/app/ggs

72% /pac

13% /var

1% /tmp

78% /pac/prod_backup2

48% /pac/prod_backup

0% /run/user/0

0% /run/user/4264524

0% /run/user/3095009

0% /run/user/1563708

65% /nas/osd

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}' | awk '{print $2}' | grep /usr/local/opt/oracle

/usr/local/opt/oracle

host:/pac/host/arch/host/archivelog/ $ df -h | awk '{print $5,$6}' | awk '{print $2}' | grep /usr/local/opt/oracle | read a

host:/pac/host/arch/host/archivelog/ $ echo $a

/usr/local/opt/oracle

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

df -h /pac | awk '{print $5,$6}' | grep "60" | awk '{print $1}'

60%

host:/pac/host/arch/host/archivelog/ $ cat ch.sh

#!/bin/bash

chk=$(df -h /pac | awk '{print $5,$6}' | grep "44" | awk '{print $1}' | grep -o "\w[[:digit:]]" )

if [[ "${chk}" -eq "44" ]]

then

echo "/pac is 44% full"

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

for i in 24 25; do ls -lrth *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec $i" | awk '{print $4}' | while read n; do echo $n; done; done

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

#!/bin/sh

check_trails_count=`ls -lrt /opt/app/ggs/trails/lt* | wc -l`

check_space=`df -h /opt/app/ggs | awk '{print $5}' | grep "[[:digit:]]" | cut -b1-2`

check_gg=`ps -ef | grep -i PARAM | grep -i -e "\.prm" | wc -l`

if [[ "${check_trails_count}" -gt "100" ]] && [[ "${check_space}" -gt "90" ]] && [[ "${check_gg}" -gt "0" ]]

then

echo "yes"

ls -lrt /opt/app/ggs/trails/lt* | head -n 50 | awk '{print $9}' | while read n; do rm -rf $n; done

df -h /opt/app/ggs | mail -s "Free the Space on /opt/app/ggs on host" example@gmail.com

location: /opt/app/ggs/trails/trails_free_up.sh

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

#!/bin/sh

count_arch=`ls -lrt /pac/lci8t1/arch/oraarch01/*.arc | wc -l`

check_oracle=`ps -ef | grep -i ora_pmon_${ORACLE_SID} | grep -v grep | grep -i pmon | wc -l` # best command to find oracle is running or not #

if [[ "${count_arch}" -gt "500" ]] && [[ "${check_oracle}" -gt "0" ]]

then

echo "${count_arch}"

ls -lrt /pac/lci8t1/arch/oraarch01/*.arc | head -n 200 | awk '{print $9}' | while read n; do rm -rf $n; done;

df -h /pac | mail -s "Space has been free ${hostname} " example@gmail.com

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

Shell Script For Auto Backup old trail files and free the space in the mount point then sent mail

$ cat freeuptrails.sh

#!/bin/sh

PATH=/usr/bin:/sbin: ; export PATH # exporting path for using TAR in this shell script otherwise tar won't work

count_files=$(ls -lrth /opt/app/ggs/trails/hostname/lt* | wc -l)

nfiles=`echo "scale=1; 2/5" | bc`

files_count_delete=`echo "scale=0; $nfiles*$count_files" | bc`

result=`printf "%.0f\n" $files_count_delete`

echo $result

echo $count_files

files=$(ls -lrth /opt/app/ggs/trails/hostname/lt* | head -n $result )

disk_usage=$(df -h /opt/app/ggs/trails/hostname | awk '{print $5}' | grep -e "[[:digit:]]" | awk -F% '{print $1,$2}')

if [[ "${disk_usage}" -gt "90" ]]

then

if test ${result} -lt ${count_files}

then

# check if old backup.tar.gz exists if yes then delete it

bkp_file=/opt/app/ggs/trails/hostname/backup.tar.gz

if test -f ${bkp_file}

then

rm -f /opt/app/ggs/trails/hostname/backup.tar.gz

# make backup directory for storing old trails files

mkdir /opt/app/ggs/trails/hostname/backup

# for loop to move old trails files to backup folder

for i in ${files}

mv $i /opt/app/ggs/trails/hostname/backup

done

# tar the backup folder

tar -czf /opt/app/ggs/trails/hostname/backup.tar.gz -C /opt/app/ggs/trails/hostname backup --remove-files

wait

echo "work is done"

send=$(df -h /opt/app/ggs)

sendone=$(echo "current trail files count:"; ls -lrth /opt/app/ggs/trails/hostname/lt* | wc -l)

sendtwo=$(ls -lrth /opt/app/ggs/trails/hostname/backup.tar.gz)

echo -e "$send\n$sendone\n$sendtwo" | mailx -s "Space free now on hostname /opt/app/ggs" exampel@gmail.com

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

createuser.sh

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

#!/bin/bash

echo "enter username:"

read user;

echo "enter default tablespace:"

read default_tablespace;

echo "password:"

read password;

sqlplus / as sysdba << EOF > log.txt

alter session set "_oracle_script"=true ;

create user ${user} identified by ${password} default tablespace ${default_tablespace} temporary tablespace temp quota unlimited on users;

grant dba to ${user};

exit;

EOF

sqlplus ${user}/${password} << EOF > ${user}.txt

show user;

exit;

EOF

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

userdrop.sh

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

#!/bin/bash

echo "Enter username:"

read user

echo "password:"

read password

sqlplus / as sysdba << EOF > ${user}drop.txt

alter session set "_oracle_script"=true;

drop user ${user} cascade;

exit;

EOF

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

createtablespace.sh

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

#!/bin/bash

echo "Enter tablespace_name:"

read tablespace_name

echo "path:"

read path

echo "Size:"

read size

echo "autoextend on/off"

read autoextend_on_off

echo "tablespace_file_no:"

read tablespace_file_no

sqlplus / as sysdba << EOF > ${tablespace_name}created.txt

create tablespace ${tablespace_name}

datafile '${path}${tablespace_name}_${tablespace_file_no}.dbf'

size ${size}

autoextend ${autoextend_on_off};

exit

EOF

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

droptablespace.sh

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

#!/bin/bash

echo "Enter tablespace_name:"

read tablespace_name

sqlplus / as sysdba << EOF > ${tablespace_name}drop.txt

drop tablespace ${tablespace_name} including contents and datafiles;

exit;

EOF

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

mkscript.sh

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

#!/bin/bash

echo "Enter script name:"

read script_name

echo "Enter permission"

read permission

touch ${script_name}

chmod ${permission} ${script_name}

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

createcontrolfile.sql

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

create controlfile <-------------------------

set database db_name |

logfile group 1 ('/u02/app/oracle/db_name/redo01_01.log', | #only change: /u02/app/oracle/db_name remaining all remains as it is

'/u02/app/oracle/db_name/redo01_02.log'), | # alter database backup controlfile to trace;

group 2 ('/u02/app/oracle/db_name/redo02_01.log', | # adrci > show home then $ cd /rdbms/trace/ $ ls -lrt alert_${db_name}.trc - copy the content to c.sql

'/u02/app/oracle/db_name/redo02_02.log'), | |

group 3 ('/u02/app/oracle/db_name/redo03_01.log', | |

'/u02/app/oracle/db_name/redo03_02.log') |-------------------------------------------------------------------------------------------------

resetlogs |

datafile '/u02/app/oracle/db_name/system01.dbf' size 30M, |

'/u02/app/oracle/db_name/sysaux01.dbf' size 50M, |

'/u02/app/oracle/db_name/users01.dbf' size 50M, |

'/u02/app/oracle/db_name/temp01.dbf' size 50M |

maxlogfiles 50 |

maxlogmembers 3 |

maxloghistory 400 |

maxdatafiles 200 |

maxinstances 6 |

archivelog; <-------------------------------

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

freespace.sh

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

echo "path"

read path

echo "year:"

read year

echo "month:"

read month

echo "day:"

read day

echo "find type option f or d"

read option

echo "days_of_files_to_kept"

read days_of_files_to_kept

rm -rf ${year}_${month}_${day}_*

# or can be used as below

find ${path} -type ${option} -mtime +${days_of_files_to_kept} -exec rm {} +

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

extract.sh --> scripts to get query results without going inside database

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

#!/bin/bash

echo "Enter username:"

read user

echo "Enter password:"

read password

echo "Enter statement or query:"

read query

sqlplus ${user}/${password} << EOF > logon.txt

$query

exit;

EOF

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

switch.sh ---> scripts for listing of all directories and files

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

#!/bin/bash

echo "Enter path:"

read path

for i in $path

echo "list of files or folders"

ls -lrth ${i}

done

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

search.sh ----> scripts for

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

#!/bin/bash

count=$(wc -m file | tr -cd "[:digit:]")

i=1

while [[ $i -le $count ]]

cut -b $i file

(( i++ ))

done

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

bkup script is running or not :

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

#!/bin/bash

bkp_running=$(ps -ef | grep rman)

if [[ "$bkp_running" = "TRUE" ]]

then

echo "bkp is running"

else

echo "bkp is not running"

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

Linux Mail send GUIDE

$ mail -s "$(echo -e "$Subject Tablespace Usage\nContent-Type: text/html;\n\n")" example@gmail.com < index.html

echo index.html | mail -s "$(echo -e "Tablespace Usage \nContent-Type: text/html")" example@gmail.com

$(echo -e "$Subject\nContent-Type: text/html;\n\n)"

Below is for SQLPLUS :-

$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/usr/local/opt/oracle/oraadmin/ipag/monitor/long_run_session.sql > ${MAILMSGFILE}
pr -e -t ${MAILMSGFILE} | mailx -s "IDIS Long Running Queries on $HOST: $ORACLE_SID" ${MAILLIST}

$ mail -s "$(echo -e "$Subject Tablespace Usage \n Content-Type: text/html \n charset=utf-8")" example@gmail.com

$ echo "attachment" | mailx -s "Tablespace usage" -a index.html example@gmail.com

oracle@hostame : path/ $ cat try.sh #!/bin/sh command=`df -h `

echo "${command}" > index.html

oracle@hostname: path/$ sh try.sh 
oracle@hostname: path/ $ cat testmail.sh
#!/bin/sh
echo "email_to:"
read email_to
echo "email_from:"
read email_from
echo "Subject:"
read subject
echo "file_path"
read filepath
(
 echo "To: ${email_to}"
 echo "From: ${email_from}"
 echo "Subject: ${subject}"
 echo "Mime-Version: 1.0"
 echo "Content-Type: text/html; charset='utf-8'"
 echo
 cat ${filepath}
) | sendmail -t
oracle@hostname: path/ $ sh testmail.sh
email_to:
user@sample.com
email_from:
oracle@hostname
Subject:
Space free
file_path
index.htm
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
Sample Shell Script To send HTML report From SQL Query 
host:log/ $ cat test.sh
#!/bin/sh


echo "Enter SQL query:"
read query

sqlplus / as sysdba << EOF
set markup HTML on;
spool index.html;
${query};
spool off;
set markup HTML off;
exit;
EOF

echo "email_to:"
read email_to
echo "email_from:"
read email_from
echo "Subject:"
read subject
echo "file_path"
read filepath
(
 echo "To: ${email_to}"
 echo "From: ${email_from}"
 echo "Subject: ${subject}"
 echo "Mime-Version: 1.0"
 echo "Content-Type: text/html; charset='utf-8'"
 echo
 echo "<body><style> th{background-color:gold;} table{border:2px solid blue;}</style></body>"
 echo
 cat ${filepath}
) | sendmail -t

ACTIVE SESSION & Inactive session AND BLOCKING SESSION IN ORACLE DB

Active :- waiters

Inactive:- holders

dba_blockers:- non-waiting sessions

dba_ddl_locks:- ddl locks

dba_dml_locks:- dml locks

dba_lock_interval:- 1 row for every lock username

dba_locks:- show all locks/latches

dba_waiters:- waiting session

dba_objects, all_objects, user_objects, dba_segments, gv$loced_objects, gv$session_longops

Real-time monitoring of sql query:- gv$sql_plan_monitor

col inst_id for a13
col sid for a12
col serial# for a12
col username for a14
col status for a12
col server for a14
col schemaname for a12
col osuser for a14
col machine for a13
col program for a13
select inst_id,sid,serial#,username,status,server,schemaname,osuser,machine,program,sql_id from gv$session
where status = 'ACTIVE';
#################### Working status and seconds v$session ###############################
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from gv$session
where username = 'schema';
###################################################################################
select SID, osuser, machine, terminal, service_name,
logon_time, last_call_et
from gv$session
where username = 'schema';
###################################################################################
Session wait for each machine
-------------------------------------------
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from gv$session
where machine = 'host_name';

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

Blocking sessions occur when a session issues an "insert, update or delete" command that changes a row.

When the change occurs,"the row is locked until the session either commits the change, rolls the change

back or the user logs off the system."

select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

from

v$session

where

blocking_session is not NULL

order by

blocking_session;

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

How to check Locking session/Blocking session

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

col username for a15

col machine for a20

col event for a20

col program for a20

col osuser for a20

set line 3000

set pagesize 2000

SELECT

Inst_id,

s.username,

s.blocking_session blk_sess,

s.blocking_instance blk_inst,

s.sid,

s.serial#,

s.sql_id,

s.status,

s.seconds_in_wait waittime,

s.event,

s.machine

FROM

gv$session s

WHERE

blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

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

################################### How to search users on database##################

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

s.sid,

s.serial#,

--s.sql_id,

p.spid,

s.username,

s.program

FROM gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE s.type != 'BACKGROUND';

INST_ID SID SERIAL# SPID USERNAME PROGRAM

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

1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)

1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)

############################ To get spid and then kill them#########################

$ ps -ef | grep ora

$ kill -9 spid

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; -------------------------> single instance db

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id'; ------------------> RAC instance db

Oracle Database Scripts

Tuesday, May 16, 2023

RMAN BACKUP FORMAT and Commands

How to restore & recover database using tag

Saturday, May 13, 2023

User Profile Manipulation In Oracle

Friday, May 12, 2023

FLASHBACK_TRANSACTION_QUERY displays information about all flashback transaction queries in the database.

`FLASHBACK_TRANSACTION_QUERY` displays information about all flashback transaction queries in the database.

Resolve In-Doubt-Transaction (IDT)

ORACLE SESSIONS MONITORING SQL QUERIES

LOCKED OBJECTS SQL QUERIES IN ORACLE

Thursday, May 11, 2023

TABLESPACE, DATAFILES, ASM DISKGROUP IN ORACLE SQL QUERIES

Wednesday, May 10, 2023

Install OEM Oracle Enterprise Manager

Sunday, May 7, 2023

File System Free Space Automatically Shell Script Linux

Linux Mail send GUIDE

ACTIVE SESSION & Inactive session AND BLOCKING SESSION IN ORACLE DB

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

Enable OpenSSH on Windows 11

Translate