Expdp Backup script with retention for Windows

Oracle Version:- 11g

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file. Schedule it on task scheduler
as per your requirement.Also make sure you have 7zip installed on your machine for the zip purpose.

@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 ORACLE_HOME=C:\app\Oracle11G\product\11.2.0\dbhome_1
set ORACLE_BASE=C:\app\Oracle11G\
set PATH=%ORACLE_HOME%\bin
set BACKUPPATH=C:\app\Oracle11G\Datapump
forfiles /p "C:\app\Oracle11G\Datapump" /m * /d -7 /c "cmd /c rd /s /q @path"
cd %BACKUPPATH%
mkdir "C:\app\Oracle11G\Datapump\%mydate%_%mytime%"


%ORACLE_HOME%\BIN\expdp system/system directory=BACKUP Full=Y dumpfile=backup_%mydate%_%mytime%.dmp logfile=log_%mydate%_%mytime%.log exclude=statistics
cd %BACKUPPATH%
"C:\Program Files\7-Zip\7z.exe" a -mx9 C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\backup_%mydate%_%mytime%.dmp
move C:\app\Oracle11G\Datapump\Backup_%mydate%_%mytime%.7z C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
move C:\app\Oracle11G\Datapump\log_%mydate%_%mytime%.log C:\app\Oracle11G\Datapump\%mydate%_%mytime%\
del backup_%mydate%_%mytime%.dmp

Snapshot database in SQL Server

Database Version : SQL Server 2012

What is a database snapshot ?

Database snapshots are an Enterprise only feature which was introduced  in SQL Server 2005.A database snapshot is a view of what the source database looked like at the time when the
snapshot was created.

How does a snapshot work:-

a. When you create a snapshot a sparse file is created for each data file.
b.When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file.
c.If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot.

How to create a snapshot?

USE master
GO
-- Create a source Database
CREATE DATABASE sourcedb
GO
USE sourcedb
GO
-- Populate sourcedb Database with some Table
CREATE TABLE Employee (ID INT, Value VARCHAR(10))
INSERT INTO Employee VALUES(1, 'First');
INSERT INTO Employee VALUES(2, 'Second');
INSERT INTO Employee VALUES(3, 'Third');
INSERT INTO Employee VALUES(4, 'Fourth');
GO
-- Now Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='sourcedb',
FileName='E:\snapshotdb.ss1')
AS SNAPSHOT OF sourcedb;
GO
-- Select from source and Snapshot Database
SELECT * FROM sourcedb.dbo.Employee;
SELECT * FROM SnapshotDB.dbo.Employee;
GO

ID Value
1 First
2 Second
3 Third
4 Fourth

ID Value
1 First
2 Second
3 Third
4 Fourth



Now lets do some dml activity on source db :-
-- Delete from sourcedb Database
DELETE FROM sourcedb.dbo.employee;
GO

ID Value


ID Value
1 First
2 Second
3 Third
4 Fourth


So we can see the rows have been deleted from the sourcedb but the snapshot db is totally intact as it is.

Now, incase if we want to undo all the changes we did, we can revert back the database from the snapshotdb which was created before doing the activity.

-- Restore Data from Snapshotdb Database
USE master
GO
RESTORE DATABASE sourcedb
FROM DATABASE_SNAPSHOT = 'Snapshotdb';
GO
-- Select from sourcedb and Snapshotdb Database
SELECT * FROM sourcedb.dbo.employee;
SELECT * FROM Snapshotdb.dbo.employee;
GO

ID Value
1 First
2 Second
3 Third
4 Fourth

ID Value
1 First
2 Second
3 Third
4 Fourth


So we see all the datas are restored to the point when the snapshotdb was created.

Benefits of Database Snapshots:-

1.Snapshots can be used for reporting purposes.
2.Maintaining historical data for report generation.
3.Using a mirror database that you are maintaining for availability purposes to offload reporting.
4.In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. For example, before doing
large dml activity, usually create a snapshot of the original db prior to the dml activity being done.

Mysql up & down status check script with email alert

create the shell-script which will monitor the up/down status of mysql service:-

[oracle@server1 ~]$ vi /home/script/alert.sh

#!/bin/bash
servername="server1.example.com"
publicip=192.168.72.5
servicename="mysql"

for i in $servicename
do
/sbin/service $i status
if [ $? -ne 0 ]; then
/sbin/service $i status | mailx -s "Check Status of $i in $servername ($publicip) at `date`"  yourmailid@gmail.com
fi
done

:wq (save & exit)

We schedule the script on crontab which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/script/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

Oracle 11.2.0.1 download link for Windows 7 64bit

Oracle 11.2.0.1 download link for  Windows 7 64bit :-


win64_11gR2_database_1of2 - https://onedrive.live.com/redir?resid=EB956C947D7F25EA!117&authkey=!AC89kV_gQ4Zh6Ug&ithint=file%2czip


win64_11gR2_database_2of2 - https://onedrive.live.com/redir?resid=EB956C947D7F25EA!118&authkey=!AIH8tdcmUzPK9XM&ithint=file%2czip

Monitoring Temporary tablespace usage using shell script

The following script will report if temporary tablespace  hits 80% of its usage.

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

set head off
set wrap off
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
exit;


Now lets create the shell-script which will monitor the space usage of temp tablespaces:-
From oracle user

[oracle@server1 ~]$ vi /home/oracle/alert_temp.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/check_temp_tbs.sql > /home/oracle/temptbs.txt
space=`cat /home/oracle/temptbs.txt`
if [ $space -gt 80 ]; then
mail -s echo "Attention!! Low space in  Temporary 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_temp.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

What is Standby snapshot Database?

What is Standby snapshot Database?
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions.
A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database. A snapshot standby database
receives and archives, but does not apply redo data from a primary database. Redo data received from the primary database is applied when a snapshot standby database is converted back
into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. Local updates to the
snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a
physical standby database at any time, and the redo data received from the primary will then be applied.

A snapshot standby database will allow you to make use of the data available on the physical standby database (which is the same data of the primary database), which allows the users
to test the application on a standby database which has the primary database's data before implementing it into production environment. Whenever a physical standby database is converted
into a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby
database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means the transactions
which were made in standby database while it was open in READ WRITE mode will be flushed out.

The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled.