Category: SCRIPTS

  1. Home
  2. SCRIPTS
December 21, 2017

Script for checking tablespace growth

with t as ( select ss.run_time,ts.name,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 ts.name =upper(‘USERS’) and ts.name = dt.tablespace_name ) select e.run_time,e.name,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 = […]

Read More
December 21, 2017

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 […]

Read More
December 4, 2017

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 ( […]

Read More
December 1, 2017

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 […]

Read More
November 27, 2017

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 […]

Read More
November 20, 2017

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 […]

Read More
November 20, 2017

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 […]

Read More
November 20, 2017

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 dataguard_dr_sync_check.sh #!/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 […]

Read More

Count per Day

  • 527038Total reads:
  • 323Reads today:

Top Posts & Pages