Rman backup from production and restore in test machine


We want to make a test database from production database. And our production database is on ASM and our test machine was linux file sysetm.
see the steps how we restored it.

1) Start the test database in nomount mode

sqlplus /nolog

SQL*Plus: Release – Production on Tue Oct 2 08:30:29 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Restore controlfile from backup

[oracle@test rman]$ rmant target / Recovery Manager: Release - Production on Tue Oct 2 08:31:07 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: prod (not…

View original post 1,039 more words

Shell Script to Ping Hostnames and Send SMS if Host is not Available

We use the below procedure to ping hosts and based on availability send SMS to our team. The SMS is send by calling a web service from the BASH Shell script using curl 

The hosts are defined in a file called hosts.txt

— Shell Script —

current_time=$(date “+%Y%m%d%H%M%S%3N”)
#### Above Date Format is in MilliSeconds Accuracy ###
cd /home/online
rm -rf /home/online/SMSHOST.TXT
#### Below Code Will Loop the Hosts.TXT file for the Ping Probes ####
cat /home/online/hosts.txt | while read output
ping -w 30 -c 1 $output > /dev/null
if [ $? -ne 0 ]
echo “Host $output is Down”
curl “$SMSTEXT&destination=9998887190&source=97249&mask=COMPANY”
echo “Host $output is Up”


— hosts.txt file containing list of hostnames —







Shell Script to Add Date to Filenames

Below Shell Script will append Date to all FileNames in Directory with extension .TXT. Also it will create a folder with the Date and move the files to it.

FILE=”$(date +%Y%m%d)”
cd /bi_dataload
mkdir -p /bi_dataload/BI_$FILE
for f in *.TXT
mv -n “$f” /bi_dataload/BI_$FILE/”${f%%.*}”_”$FILE”.TXT

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

Monitoring Oracle with oratop

Rick Miners' Oracle Blog


I recently had a situation where I was troubleshooting a system that had fallen victim to some intermittent and very high load averages. At first, there were no apparent reasons for the high load averages, so that sent me searching for some monitoring tools to help identify the issue. Besides using OSWBB and dstat, I also used the oratop utility which turns out to be a great way to get a quick overview and monitor active sessions in near real time in Oracle 11g and 12c databases. It is also RAC and ASM aware.


Here’s the MOS note for more info and downloads.
oratop – Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Supported Oracle Releases

11gR2 (,


oratop in Action

There is a user guide available for download on the MOS note, but I will show my favorite options.

View original post 240 more words

First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month


select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual;

select trunc((sysdate),’month’) as FirstDay from dual;

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement