Showing posts with label Automation. Show all posts
Showing posts with label Automation. Show all posts

Thursday, August 18, 2016

How to invoke a shell script from a windows batch file

From  last few days I was  searching for a script that can invoke a shell script from windows batch file.After a prolonged search on google I made it. So thought of sharing here -

Create a text file with some name , for example Check_Service and save it as .bat. The content of my Check_Servic.bat is,

C:\Users\mahesh\Desktop\PT\putty.exe -ssh oracle@10.81.155.9 -pw Myoracle123 -m "C:\Users\mahesh\Desktop\PT\work3.txt" -t

And the content of  C:\Users\mahesh\Desktop\PT\work3.txt  is here ,


sh /home/oracle/mp/srvctl_status_db_DI.sh
/bin/bash 


And the content of my srvctl_status_db_DI.sh


for database in DIOES DIIAM DIHIM
do
export ORACLE_SID=${database}1
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
srvctl status database -d $database
echo ------------------------------------------------------
done


Its a simple script to check the status of your cluster database . 

Double click the batch file -



Thursday, June 23, 2016

How to configure a batch file to make use of winscp to get files from remote server

I have a perl script scheduled in each databases box that will do the health-checks of my databases. Every morning it will generate a html file and I would like to fetch all those files in a single click into  my local machine  . How to do that ? As ftp is disabled in my database box , I have to rely on scp . I could able to fetch all the files by creating a batch file .

Note:-   Winscp is  required for this set up and it should be installed in your local machine. I have installed my WinScp software in "C:\Program Files (x86)\WinSCP" .

Create a batch file say for example
Get_files_using_winscp.bat  with following contents

@echo off

del "C:\Users\mahesh\Desktop\DB_HealthCheck\Report\*.html"
cd "C:\Program Files (x86)\WinSCP"
Winscp.com /script=C:\Users\mahesh\Desktop\DB_HealthCheck\get_my_files.txt
Exit 

And the contents of  get_my_files.txt is given below 

option echo off

option batch on

option confirm off

open sftp://oracle:Welcome123@10.18.211.8

lcd "C:\Users\mahesh\Desktop\DB_HealthCheck\Report"

cd  /home/oracle/HealthChecks/html/

get -nopermissions -nopreservetime DBHealthChecks_*


open sftp://oracle:Welcome123@10.18.214.9
lcd "C:\Users\mahesh\Desktop\DB_HealthCheck\Report"

cd  /home/oracle/HealthChecks/html/

get -nopermissions -nopreservetime DBHealthChecks_*

Double click the batch file you created and you will get your files in your local PC.

Enjoy :)

Thursday, April 21, 2016

Shell scritp to check space utilization for normal/high redundancy disk group

The space allocation, utilization as well as the space availability in a ASM diskgroup is completely dependent on the type of redundancy defined for a ASM diskgroup.
The free_mb from v$asm_diskgroup is valid only when we are using a external redundancy disk group . why it differ for disk group having redundancy NORMAL/HIGH ?  Its pretty clear from the following facts 

Normal Redundancy: In Normal redundancy, the disks are two way mirrored, therefore we need at least two disks of same size to create a diskgroup with normal redundancy.
High Redundancy: In High redundancy, the disks are three way mirrored, therefore we need at least three disks of same size to create a diskgroup with high redundancy.
External Redundancy: In External redundancy, the disks are not mirrored by ASM. In this case, we may want the disk mirroring to be maintained external to ASM by means of storage array or we may decide not to mirror the disks at all

So in a normal redundancy diskgroup, we should divide the TOTAL_MB/FREE_MB by two to get the actual space allocation and for a high redundancy diskgroup we have to devide by three.

However, this is a lengthy approach; as we need to first identify the diskgroup redundancy level and then adjust our query to get the actual result. There must be some simple method available to identify the actual diskgroup space availability. Yes, it is there……
We can query the USABLE_FILE_MB from V$ASM_DISKGROUP to know the actual free space available within a diskgroup.

I created a shell script to check the space utilization for normal/high redundancy disk group - as I am using exadata in my firm , by default  I can't find any external redundancy  disk group .

 #! /bin/bash
clear
export TERM=xterm
ccc='\033[0;33m'
NC='\033[0m'
green='\033[0;32m'

under='\033[4m\033[1;36m'
green2='\033[30;42m'

for database in $(ps -ef |grep pmon|grep +ASM|awk '{print$8}'| cut -d"_" -f3)
do
export ORACLE_SID=$database
#export ORACLE_SID=${database}1
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
echo -e "${under}  ASM Disk Group Usage for - `hostname -f` (`hostname -i`) ${NC}"
echo -e " "
echo -e "Connected ASM instance is ${green2}$ORACLE_SID${NC}"
sqlplus -s " / as sysdba" <<\EOF
SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name         FORMAT a20           HEAD 'Disk Group|Name'
COLUMN state                    FORMAT a11           HEAD 'State'
COLUMN type                     FORMAT a6            HEAD 'Type'
COLUMN total_gb               FORMAT 999,999,999   HEAD 'Total Size (GB)'
COLUMN used_gb                FORMAT 999,999,999   HEAD 'Used Size (GB)'
COLUMN free_gb                FORMAT 999,999,999   HEAD 'Free Size (GB)'
COLUMN pct_free               FORMAT 999.99        HEAD '%Free'
SELECT
    name                                         group_name
  , state                                        state
  , type                                         type
  , Round((TOTAL_MB/1024),2)                     total_gb
  , Round(((TOTAL_MB - USABLE_FILE_MB)/1024),2)  used_gb
  , Round((USABLE_FILE_MB/1024),2)               free_gb
  , Round((USABLE_FILE_MB/TOTAL_MB)*100,2)       pct_free
FROM
    v$asm_diskgroup
ORDER BY pct_free desc;
exit;
EOF
done


Sample output :- 



 Enjoy :)


Wednesday, December 12, 2012

Rman backup through batchfile how to?

we can use rman along with batch file to take the database backup. 

First you have to create a rcv with following contents . I name it as bkp.rcv  and kept in d: drive.

The contents of my bkp.rcv is given below

backup datafile 1 format 'c:\rman\datafile_1_%D%M%Y%U';
backup datafile 2 format 'c:\rman\datafile_2_%D%M%Y%U';

backup datafile 3 format 'c:\rman\datafile_3_%D%M%Y%U';
backup datafile 4 format 'c:\rman\datafile_4_%D%M%Y%U';
backup datafile 5 format 'c:\rman\datafile_5_%D%M%Y%U';
backup datafile 6 format 'd:\rman\datafile_6_%D%M%Y%U';
backup datafile 7 format 'c:\rman\datafile_7_%D%M%Y%U';
exit;

Next create a file named rman_backup.txt , and insert the following contents ,set the PATH and ORACLE_SID accordingly .

set PATH=C:\oracle\product\10.2.0\Asm_1\bin;%PATH%
set ORACLE_SID=oracle1
C:\oracle\product\10.2.0\Asm_1\bin\rman target sys/sys cmdfile d:\bkp.rcv log d:\rmanbkp.log
pause

After creating the file rename it as rman_backup.bat and execute the batch file.

Hopes it will help for anybody :)

Friday, November 9, 2012

Batch file for taking cold backup and expdp

In many case we need to take the cold backup , am happy to share this batch file that will  take the full database backup and finally it will automatically start the database after cold backup.

1. First create a file name backup.txt and save the following contents,
REM
echo off
echo coldbackup started
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set dayofweek=%%i
set month=%%j
set day=%%k
set year=%%l
set curtime=%time%
set fname=BOOST-%%i-%%j-%%k-%%l
)
md e:\dbbackup\%fname%
set oracle_sid=boost  --> here change your database name
sqlplus -s "sys/maku as sysdba" @e:\coldbackup.sql
echo cold backup completed succesffully.
pause

After saving , rename it as backup.bat .

2.Now create coldbackup.sql as follows ,
set lines 1000
set head off
set term off
set feedback off
spool e:\coldbackup.bat
select 'copy '||name||' e:\dbbackup\%fname%' from v$datafile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$controlfile;
select 'copy '||name||' e:\dbbackup\%fname%' from v$tempfile;
select 'copy '||member||' e:\dbbackup\%fname%' from v$logfile;
spool off
shutdown IMMEDIATE
host e:\coldbackup.bat
startup
host del e:\coldbackup.bat
set term on
set head on
set feedback on
set verify on
EXIT

And keep this file in e:\ folder .

3. Our script is ready , now run the batchfile "backup.bat" . The script will first create a spool file named "coldbackup.bat" and it contain the necessary command to copy the files from  the command line , and  will execute this spooled batch file by host e:\coldbackup.bat after  successful execution we will delete this spooled batch file. We can see the cold backup got generated in e:\dbbackup folder.

A simple batch file for taking date-wise expdp of a schema 

@ECHO OFF
: Sets the proper date and time stamp with 24Hr Time for log file naming
: convention

SET HOUR=%time:~0,2%
SET dtStamp9=%date:~-4%%date:~4,2%%date:~7,2%_0%time:~1,1%%time:~3,2%%time:~6,2% 
SET dtStamp24=%date:~-4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%
if "%HOUR:~0,1%" == " " (SET dtStamp=%dtStamp9%) else (SET dtStamp=%dtStamp24%)

set PATH=D:\oracle\product\10.2.0\db_1\BIN;%PATH% --> set your own path 
D:\oracle\product\10.2.0\db_1\BIN\expdp aiwa/aiwa@proddb dumpfile=prod_aiwa_%dtStamp%.dmp logfile=prod_aiwa_%dtStamp%.log directory=DATA_PUMP_DIR schemas=aiwa
pause