How to Reset SA Password in Sql Server 2012

I had a scenario where i forgot SA password or windows authentication was not working.
So i used the below method to reset the SA password to login into SQL Server.

Step 1:-
Change SQL SA password from a command prompt

Go to the command prompt of the server and type in command prompt osql –L
C:\Users\Administrator>osql -L

Servers:
localhost

Step 2:-
Copy full name of SQL Server and type: OSQL -S <insert_servername_here> -E
C:\Users\Administrator>osql -S localhost -E

Step 3:-
Execute the following query: sp_password NULL, '<insert_new_password_here>', 'sa'
1> sp_password NULL, 'soumya@123#','sa'
2> GO

Done, the sa password has been reset.

Last Login Time for nonsys user in oracle 12c

Sqlplus shows Last Login Time for non system users in oracle12c.

In oracle 12c a new security feature has been added which allows us to check when did  a non system user logged in.

[oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:35:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 15 2016 08:35:14 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The feature can be disabled by doing the following:-
[oracle@server3 ~]$ sqlplus -nologintime soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:45:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


The actual information is basically stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.

[oracle@server3 ~]$ sqlplus / as sysdba
SYS@ORA12C> col username FOR a15
SYS@ORA12C> col last_login FOR a25
SYS@ORA12C> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

Session altered.

SYS@ORA12C> SELECT username,last_login FROM dba_users WHERE username='SOUMYA';

USERNAME        LAST_LOGIN
--------------- -------------------------
SOUMYA          15.02.2016 08:45:09

SYS@ORA12C> col name FOR a15
SYS@ORA12C> ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';
col spare6 for a40
Session altered.

SYS@ORA12C> SELECT name,spare6 FROM USER$ WHERE name='SOUMYA';

NAME            SPARE6
--------------- -------------------
SOUMYA          15.02.2016 03:15:09


oracle@server3 ~]$ sqlplus soumya/soumya

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 08:52:32 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 15 2016 08:45:09 +05:30

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