Monitoring Tablespace usage using shell script

The following script will report if any tablespace (except temporary tablespace ) hits 85% of its usage.


From oracle user:
[oracle@server1 ~]$ vi /home/oracle/checktbs.sql

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 15
order by pct_free;
exit;

:wq(save & exit)


Now lets create the shell-script which will monitor the space usage of tablespaces:-
From oracle user
[oracle@server1 ~]$ vi /home/oracle/alert.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/checktbs.sql > /home/oracle/test.txt
file1=cat /home/oracle/test.txt
space=`cat /home/oracle/test.txt | wc -l`
if [ $space -gt 3 ]; then
$file1 | mail -s echo "Attention!! Low space Certain Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert.sh > /dev/null


P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx

How to find out how many times a database has been restared since its creation

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;

STARTUP_TIME
—————————————————————————
29-SEP-13 03.43.07.000 AM
29-SEP-13 03.42.31.000 AM
29-SEP-13 03.12.45.000 AM
29-SEP-13 03.11.28.000 AM
28-SEP-13 11.56.35.000 PM
07-SEP-13 10.04.07.000 PM

RMAN Full Backup script for Windows

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file and schedule it in task scheduler.


@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set RUNDIR=C:\TEMP
set LOGDIR=C:\oracle\log
set ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
rem set NLS_DATE_FORMAT='DD-MM-YY HH24:MI:SS'
set BACKUPDIR=c:\oracle\oradata\backup\%mydate%_%mytime%
set LOGFILE=%LOGDIR%\%ORACLE_SID%_%mydate%_%mytime%.log
set ADDR='job@jobacle.nl'
set TMPLOG=%LOGDIR%\tmplog.$$
mkdir "c:\oracle\oradata\backup\%mydate%_%mytime%""

set CMDFILE=%RUNDIR%\%ORACLE_SID%.rman
echo run { > %CMDFILE%
echo CONFIGURE RETENTION POLICY TO REDUNDANCY 2; >> %CMDFILE%
echo CONFIGURE BACKUP OPTIMIZATION ON;  >> %CMDFILE%
echo CONFIGURE DEFAULT DEVICE TYPE TO DISK; >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP ON;  >> %CMDFILE%
echo CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%BACKUPDIR%\ora_cf%%F'; >> %CMDFILE%
echo CONFIGURE DEVICE TYPE DISK PARALLELISM 2;  >> %CMDFILE%
echo CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;  >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo CONFIGURE MAXSETSIZE TO UNLIMITED;  >> %CMDFILE%
echo CONFIGURE SNAPSHOT CONTROLFILE NAME TO '%BACKUPDIR%\snapcf_%ORACLE_SID%_%mydate%_%mytime%.f'; >> %CMDFILE%
echo CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '%BACKUPDIR%\ora_df%%t_s%%s_s%%p'; >> %CMDFILE%
echo SHOW ALL; >> %CMDFILE%
echo sql "alter system archive log current"; >> %CMDFILE%
echo DELETE FORCE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2'; >> %CMDFILE%
echo BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL=0 DATABASE INCLUDE CURRENT  CONTROLFILE PLUS ARCHIVELOG; >> %CMDFILE%
echo sql "alter database backup controlfile to trace as ''%BACKUPDIR%\ctrlfile_%ORACLE_SID%_%mydate%_%mytime%''"; >> %CMDFILE%
echo CROSSCHECK BACKUP; >> %CMDFILE%
echo REPORT OBSOLETE; >> %CMDFILE%
echo DELETE FORCE NOPROMPT OBSOLETE; >> %CMDFILE%
echo }  >> %CMDFILE%

echo LIST BACKUP OF DATABASE; >> %CMDFILE%
echo LIST BACKUP OF ARCHIVELOG ALL; >> %CMDFILE%
echo LIST BACKUP; >> %CMDFILE%

echo Starting the script > %LOGFILE%
%ORACLE_HOME%\bin\rman target / @%CMDFILE% msglog=%TMPLOG% >>%LOGFILE%

type %TMPLOG% >> %LOGFILE%

copy "%TMPLOG%" "%BACKUPDIR%\%mydate%_%mytime%.log"
@echo on

Transportable tablespace in oracle 11g

Oracle Version : oracle 11g R2

What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.


Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or
the destination tablespace before the transport operation.
5. You cannot transport an encrypted tablespace to a platform with different endianness.

Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,

Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME                      ENDIAN_FORMAT
----------- ---------------------------------- --------------
         13 Linux x86 64-bit                   Little

Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME                                ENDIAN_FORMAT
----------- -------------------------------------------- --------------
         13 Linux x86 64-bit                             Little


Now let me create a tablespace to demonstrate the feature.

Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.

SQL> CREATE USER tts_user IDENTIFIED BY tts_user  DEFAULT TABLESPACE tts  TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;

User created.

SQL> grant connect , resource , create table to tts_user;

Grant succeeded.

SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );

SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM   dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.
SQL> select count(*) from test_tbl;  

  COUNT(*)
----------
     10000

Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);

PL/SQL procedure successfully completed.

P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;

no rows selected

Now , put the tablespace in read only mode  which we are going to move.

SQL> alter tablespace tts read only;

Tablespace altered.

Now , Export the tablespace metadata using expdp utility.

[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
  /u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57


Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf                                                      100%   10MB  10.0MB/s   00:00

[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp                                                            100%   96KB  96.0KB/s   00:00


Now the source tablespace can be switched  back to read write mode.
SQL> alter tablespace tts read write;

Tablespace altered.


At Destination DB:-

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

SQL> CONN / AS SYSDBA

SQL> CREATE USER tts_user IDENTIFIED BY tts_user;

User created.

SQL> GRANT connect , resource, create table TO tts_user;

Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'

Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31


SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TTS                            READ ONLY

SQL> alter tablespace tts read write;

Tablespace altered.


SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
TTS                            YES ONLINE

SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;  

  COUNT(*)
----------
     10000

Script to truncate multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL>BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;

  END LOOP;

END;
/

SQL> select * from xyz;

no rows selected

SQL> select * from t1;

no rows selected

How to take RMAN INCREMENTAL LEVEL 1 Cumulative Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1_cum
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$  vi /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1_cum
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
su - oracle -c "$ORACLE_HOME/bin/rman target / " <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 cumulative database FORMAT '$path1/$date1/inc_level_1_cum_%d_%T_%t_%s_%p'; 
eof



[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

Now schedule the script using crontab from oracle user:-
The  script will run every 6 hours

[oracle@server1 ~]$ crontab -e

0 */6 * * * /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh > /dev/null

Script to delete multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
XYZ                            TABLE

SQL>BEGIN

 FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

 LOOP

      EXECUTE IMMEDIATE 'DROP TABLE ' || i.table_name;

  END LOOP;

END;
/
SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$KPwTny0vY53gUKjACwIKTg==$0 TABLE
BIN$KPwTny0wY53gUKjACwIKTg==$0 TABLE

How to take RMAN INCREMENTAL LEVEL 1 Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/IncLevel_1_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> " 
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 database FORMAT '$path1/$date1/inc_level_1_%d_%T_%t_%s_%p'; 

eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run every 30mins

[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_rman_backup.sh
[oracle@server1 ~]$ crontab -e
15,45 * * * * /u01/backups/scripts/IncLevel_1_rman_backup.sh > /dev/null