Script for checking tablespace growth

with t as ( select ss.run_time,,round(su.tablespace_size*dt.block_size/1024/1024/1024,2) alloc_size_gb, round(su.tablespace_usedsize*dt.block_size/1024/1024/1024,2) used_size_gb from dba_hist_tbspc_space_usage su, (select trunc(BEGIN_INTERVAL_TIME) run_time,max(snap_id) snap_id from dba_hist_snapshot group by trunc(BEGIN_INTERVAL_TIME) ) ss, v$tablespace ts, dba_tablespaces dt where su.snap_id = ss.snap_id and su.tablespace_id = ts.ts# and =upper(‘USERS’) and = dt.tablespace_name ) select e.run_time,,e.alloc_size_gb,e.used_size_gb curr_used_size_gb, b.used_size_gb prev_used_size_gb, case when e.used_size_gb > b.used_size_gb then to_char(e.used_size_gb – b.used_size_gb) when e.used_size_gb = […]

script to get query for reclaim space from auto extensible datafile

set linesize 1000 pagesize 0 feedback off trimspool on with hwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn ), hwmts as ( — join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm […]

Index creation progress in oracle

———-Progress index creation————– set lines 200 col “Index Operation” for a60 select sess.sid as “Session ID”, sql.sql_text as “Index Operation”, longops.totalwork, longops.sofar, longops.elapsed_seconds/60 as “Runtime Mins”, longops.time_remaining/60 as “ETA Mins” from v$session sess, v$sql sql, v$session_longops longops where sess.sid=longops.sid and sess.sql_address = sql.address and sess.sql_address = longops.sql_address and sess.status = ‘ACTIVE’ and longops.totalwork > longops.sofar and sess.sid not in ( […]

PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script

Following topic will discuss how to Backup and Restore PostgreSQL Databases directly in S3. This topic will help you to leverage AWS S3 -IA storage to use as Backup Storage for any number of PostgreSQL databases running under an instance. This topic will cover PostgreSQL Instance running on EC2 or on-premises servers. Step 1:- Create a new mount point or directory […]

Restore Archivelog from particular sequence to another location using tape backup

Error in DR alert log:- FAL[client]: Failed to request gap sequence GAP – thread 2 sequence 9432-9473 DBID 2085418592 branch 924361120 FAL[client]: All defined FAL servers have been attempted. ———————————————————— Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that’s sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. ———————————————————— Now in Primary,do the […]

Clean *.trc ,*.trm and *.aud older than 1 day clean oracle home

#!/bin/bash . /home/oracle/.bash_profile export log=/u01/app/oracle/shells/clean_rdbms_audit.log echo $ORACLE_HOME | tee $log echo “DELETING AUDIT LOGS FROM RDBMS” | tee -a $log date | tee -a $log df -m $ORACLE_HOME | tee -a $log /bin/find /u01/app/oracle/ -type f -name “*.trc” -mtime +1 -exec rm {} \; find $ORACLE_BASE/admin -type f -name “*.aud” -mtime +5 -exec rm {} \; find $ORACLE_BASE/diag/rdbms -type f […]

Tablespace usage considering autoextend on

This script will show  tablespaces from all databases in server which are full by 85 percent.This script will take care autoextend on clause.This means it will show tablespace usage if growth is exceeding autoextended space. 1.The shell script:- for j in ORCL1 ORCL2 do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$i #$ORACLE_HOME/bin/sqlplus -S monitor/m0n1t0r@”$j” << EOF >> tb_check_all_`date +\%d\%m\%Y\%H`.log $ORACLE_HOME/bin/sqlplus -S […]

Script to sync check of applied archive log between 2 RAC databases

1.First create the shell script mentioning all environmental variables adjusted to your environment. vi #!/bin/bash cd /u01/app/oracle/shells for k in ORCL do export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 export ORACLE_SID=$k $ORACLE_HOME/bin/sqlplus -S monitor@”$k”/xxx << EOF >> dataguard_dr_sync_check_orcl_`date +\%d\%m\%Y\%H`.log @dataguard_dr_sync_check_orcl.sql exit EOF done 2.For 2 node RAC,below will be dataguard_dr_sync_check_orcl.sql script .For 4 nodes,just add another 2 sql with thread and union […]