Skip to content

Posts from the ‘Scripts’ Category

Dec 10

4 Comments

Simple cronjob for datapump

Today morning I just written a small script for taking full database using datapump and after I scheduled on every one week with cron.

#!/bin/bash
#Script to perform a full database  export backup using by datapump on every wednesday
# Script Written by
# Mohamed Azar http://mohamedazar.com
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
ORACLE_SID=devcrm; export ORACLE_SID
expdp system/Admin123 directory=exportdb dumpfile=exportdb-$(date +%Y-%m-%d).dmp logfile=exportdb-$(date +%Y-%m-%d).log full=y exclude=statistics
# Send log to mail
tail -4 /u01/datapump/dumps/exportdb-$(date +%Y-%m-%d).log | mailx -s “Cronjob Oracle Export Output:SIEBELDEV” mazar@ace-ins.com

#Remove old previous dumpfile
rm -rf /u01/datapump/dumps/previous/*.*

#Find out last week dumpfiles and move to previous directory
cd /u01/datapump/dumps
chown -Rf oracle:oinstall *.dmp *.log
chmod -Rf 775 *.dmp *.log
find -mtime +6 -exec mv {} /u01/datapump/dumps/previous \;
gzip /u01/datapump/previous/*.*
gzip *.dmp *.log

Cronjob schedule :

I scheduled here , every week on wednesday at 04:00pm

[oracle@siebelpoc cronjob]$ vi cron.exportdb
0 16 * * 3 /u01/datapump/dumps/scripts/exportdb.sh

[oracle@siebelpoc cronjob]$crontab cron.exportdb

Nov 26

9 Comments

How do i check my oracle database is either 32 bit or 64bit

Have a lot of method…Here one method below

[oracle@netbackuptest bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Nov 26 07:57:52 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select length(addr)*4 || '-bits' word_length from v$process where rownum=1;

WORD_LENGTH
---------------------------------------------
64-bits

SQL>

Nov 20

7 Comments

Script : Tablespace free space

How do we identify tablespace free space in database ?

SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
2 df.bytes / (1024 * 1024) "Size (MB)",
3 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
4 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
5 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
6 FROM dba_free_space fs,
7 (SELECT tablespace_name,SUM(bytes) bytes
8 FROM dba_data_files
9 GROUP BY tablespace_name) df
10 WHERE fs.tablespace_name (+) = df.tablespace_name
11 GROUP BY df.tablespace_name,df.bytes
12 UNION ALL
13 SELECT /* + RULE */ df.tablespace_name tspace,
14 fs.bytes / (1024 * 1024),
15 SUM(df.bytes_free) / (1024 * 1024),
16 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
17 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
18 FROM dba_temp_files fs,
19 (SELECT tablespace_name,bytes_free,bytes_used
20 FROM v$temp_space_header
21 GROUP BY tablespace_name,bytes_free,bytes_used) df
22 WHERE fs.tablespace_name (+) = df.tablespace_name
23 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
24 ORDER BY 4 DESC;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1 130 102.5 79 21
USERS 5 3.625 73 28
VPX 1024 650.625 64 36
VUM 1000 601.75 60 40
SYSAUX 880 80.5625 9 91
SYSTEM 750 42.25 6 94
TEMP 119 0 0 100

7 rows selected.
SQL>