How to recover deleted file under Linux

Jephe Wu - http://linuxtechres.blogspot.com

Objective: undelete httpd access log file while httpd process is still running
Environment:  CentOS 6.5 64bit

Cases Study:
Sometime, you deleted a big httpd log file while httpd is still running. As long as httpd process doesn't exit, you still can recover removed deleted httpd log file.

Apply to: Recover deleted file, data recovery under Linux,  restore deleted file,  undo deletion

Steps:


1. For testing purpose, list access_log file content first for confirmation

[root@www httpd]# pwd
/var/log/httpd
[root@www httpd]# more access_log
192.168.1.15 - - [24/Jul/2014:09:02:25 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.15 - - [25/Jul/2014:02:29:59 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:29 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:29 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:58 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:55:26 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:56:11 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"

2. purposely delete /var/log/httpd/access_log, then check lsof

[root@www httpd]# cp access_log /tmp
[root@www httpd]# rm -f access_log
[root@www httpd]# lsof | grep -i deleted
httpd     1146    root   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1177  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1178  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1179  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1180  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1181  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1182  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1183  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)
httpd     1184  apache   11w      REG              253,0     1282     261208 /var/log/httpd/access_log (deleted)

Note: above httpd process 1146 is running under root which is parent process of httpd.

Or use command below

[root@server1 nbpxyhelper]# ls -l /proc/*/fd 2>/dev/null | grep '(deleted)'

lr-x------ 1 root root 64 Feb 13 19:20 6 -> /var/lib/sss/mc/passwd (deleted)

lr-x------ 1 root root 64 Jan 23 15:28 8 -> /var/lib/sss/mc/initgroups (deleted)

l-wx------ 1 root root 64 Jan 23 15:28 4 -> /usr/openv/logs/nbpxyhelper/51216-486-842667692-240930-0000000000.log (deleted)

l-wx------ 1 root root 64 Jan 23 15:28 4 -> /usr/openv/logs/nbpxyhelper/51216-486-842667692-240930-0000000000.log (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 6 -> /dev/shm/pym-19449-qwpenwat (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 7 -> /dev/shm/pym-19449-qwpenwat (deleted)

l-wx------ 1 root root 64 Feb 13 19:20 8 -> /usr/local/ncpa/var/log/ncpa_listener.log.5 (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 6 -> /dev/shm/pym-19449-qwpenwat (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 7 -> /dev/shm/pym-19449-qwpenwat (deleted)

l-wx------ 1 root root 64 Feb 13 19:20 8 -> /usr/local/ncpa/var/log/ncpa_listener.log.5 (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 6 -> /dev/shm/pym-19449-qwpenwat (deleted)

lrwx------ 1 root root 64 Feb 13 19:20 7 -> /dev/shm/pym-19449-qwpenwat (deleted)

lr-x------ 1 root root 64 Jan 30 16:29 15 -> /var/lib/sss/mc/initgroups (deleted)

lr-x------ 1 root root 64 Jan 30 16:29 24 -> /var/lib/sss/mc/initgroups (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 13 -> /var/lib/sss/mc/passwd (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 14 -> /var/lib/sss/mc/group (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 15 -> /var/lib/sss/mc/initgroups (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 16 -> /var/lib/sss/mc/passwd (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 17 -> /var/lib/sss/mc/initgroups (deleted)

lr-x------ 1 root root 64 Feb 13 19:20 4 -> /var/lib/sss/mc/initgroups (deleted)

3. find out the FD of parent process of httpd then retrieve deleted access log file

[root@www fd]# cd /proc/1146/fd
[root@www fd]# ll
total 0
lr-x------ 1 root root 64 Dec  4 06:40 0 -> /dev/null
l-wx------ 1 root root 64 Dec  4 06:40 1 -> /dev/null
l-wx------ 1 root root 64 Dec  4 06:40 10 -> /var/log/httpd/ssl_error_log
l-wx------ 1 root root 64 Dec  4 06:40 11 -> /var/log/httpd/access_log (deleted)
l-wx------ 1 root root 64 Dec  4 06:40 12 -> /var/log/httpd/ssl_access_log
l-wx------ 1 root root 64 Dec  4 06:40 13 -> /var/log/httpd/ssl_request_log
l-wx------ 1 root root 64 Dec  4 06:40 2 -> /var/log/httpd/error_log
lr-x------ 1 root root 64 Dec  4 06:40 3 -> /dev/urandom
lrwx------ 1 root root 64 Dec  4 06:40 4 -> socket:[9492]
lrwx------ 1 root root 64 Dec  4 06:40 5 -> socket:[9493]
lrwx------ 1 root root 64 Dec  4 06:40 6 -> socket:[9496]
lrwx------ 1 root root 64 Dec  4 06:40 7 -> socket:[9497]
lr-x------ 1 root root 64 Dec  4 06:40 8 -> pipe:[9509]
l-wx------ 1 root root 64 Dec  4 06:40 9 -> pipe:[9509]

[root@www fd]# cp 11 /var/log/httpd/access_log.deleted

[root@www fd]# cat /var/log/httpd/access_log.deleted
192.168.1.15 - - [24/Jul/2014:09:02:25 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.15 - - [25/Jul/2014:02:29:59 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:29 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:29 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:54:58 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:55:26 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"
192.168.1.5 - - [04/Aug/2014:19:56:11 +1000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.125 Safari/537.36"


4. compare both files by diff and md5sum

[root@www fd]# diff /tmp/access_log /var/log/httpd/access_log.deleted

[root@www fd]# md5sum /tmp/access_log /var/log/httpd/access_log.deleted
9aa2e8c58ecfb689d3ada71bb6ec5815  /tmp/access_log
9aa2e8c58ecfb689d3ada71bb6ec5815  /var/log/httpd/access_log.deleted

Note: in my test environment, there's no active web access traffic so both log files are the same.

5. free up disk space
When httpd is running and access log file is deleted, the disk space won't be freed up, df -h and du reported disk free space do not match, you can use lsof -i deleted to find out if there's a big file was deleted to decide which process you need to restart to retrieve back the free space, in this case, I need to restart httpd process to free up space after restore back access log file.

How to Perform SQL Server 2012 Logshipping DR Test

Jephe Wu - http://linuxtechres.blogspot.com

Objective: showing step by step instruction for doing non-disruptive DR test and real world DR on existing logshipping SQL server 2012 production environment

Environment: SQL server 2012 prod and DR servers replicating through logshipping, both are running on Windows 2008 R2 SP1 64bit OS, production database name is LINUXTECHRES_LIVE, application server use LINUXTECHRES to connect to database.



High Level Concept 

1.  disable restore job and alert job on DR SQL server
2.  run command 'RESTORE DATABASE LINUXTECHRES_LIVE WITH RECOVERY' on DR SQL server new query window to bring database to online mode (refresh it to change status to remove 'restoring' after run command)
3.  find out which username is being used by application server to connect to production database
4. create same username with same sid on DR sql server 
5. try to login with application username and password on DR sql server with sql authentication.
6. rebuild DR database to enable logshipping again after done DR test

Steps

1. Preparation work before the actual day of DR test

After management decided to do non-disruptive DR Test, we should check the logshipping status to make sure it's working fine so that DR will have up-to-date data after we break logshipping for DR test

Also need to make sure daily full backup on production side are working correctly so that we will have working full database backup for restoring on DR side after finishing this DR test.

2. check again the version of SQL server on both prod and DR side - optional

run sql statement select @@version to decide which version you are running for SQL server.

refer to How to determine the version and edition of SQL Server and its components at http://support.microsoft.com/kb/321185 , in our case, it's SQL Server 2012 11.00.2100.60.  Also, please refer to http://sqlserverbuilds.blogspot.com.au/ for more detail versions for all SQL server versions.

3. Create application server username with password on DR SQL server so that DR application server is able to connect to DR database
sql server login on prod sql server has unique sid associated with it,  you have to create username with same sid on DR side to enable the database user to be able to connect to replicated database on DR instance.  each sid is associated with an SQL server instance which is unique.

run command below on prod sql server to find out sid of application user.

SELECT name, [sid] 
FROM sys.server_principals
WHERE [type] = 's'; 

e.g. app user LINUXTECHRES sid is 0x5B56330D7270CE4FB396226DA772ABCD

on prod sql server, expand prod database which is being logshipping to DR site, check database users to find out which user is used for application server to connect.

run command below on DR sql server query window to create application username:

CREATE LOGIN LINUXTECHRES WITH PASSWORD = 'strong password in plaintext', sid = 0x5B56330D7270CE4FB396226DA772ABCD, DEFAULT_DATABASE=[LINUXTECHRES_LIVE], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;
GO

4. try to login to DR sql server as app user linuxtechres with sql authentication

You should encounter error message like 'cannot open user default database, login failed', because the DR database is still in restoring state which cannot be accessed. Once we bring it online, you should be able to login to it.

5. Make sure the last backup transaction logfile on production server has already been copied and restored on DR site  - optional

run this on prod to check last_backup file name - 
select last_backup_file from msdb.dbo.log_shipping_monitor_primary

run this on DR to check last_copied_file and last_restored_file name - 
select last_copied_file, last_restored_file from msdb.dbo.log_shipping_monitor_secondary

If the latest backup file has not yet been copied or restored on DR site, manually run file copy and restore job on DR server to bring all 3 filenames are same


6.  break logshipping and bring DR database online

Disable restore and alert job on DR server [optional, to avoid job failure after bring DR database online ]

Run command below to bring DR database online 
RESTORE DATABASE LINUXTECHRES_LIVE WITH RECOVERY

do not run 'backup log with norecovery' on primary prod db, which will be doing tail log backup, as it will change prod database status to 'restoring',  We are doing non-disruptive DR test only, everything is still running independently on prod side.

7. You should be able to connect to database now
8. rebuild logshipping after DR test.

copy the latest full backup of database from prod to DR server, then run command below in SSMS query window.

RESTORE DATABASE LINUXTECHRES_LIVE FROM DISK = 'D:/backup/prod_full_backup_linuxtechres.bak' WITH NORECOVERY, REPLACE;

or you can use SSMS, right clicking database LINUXTECHRES_LIVE,  choose 'task', 'restore database' in SQL server 2012, if the logshipping DR has been running long time, the screen might need long time to appear. Then, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it. then it will do restore headonly to give brief detail, if it says timeout, logout SSMS , login and try again.

Go to options, choose 'overwrite the existing database', for 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery, you can right click 'restore job' , choose history to see the progress.

8. If Real DR is happening

Before above step 6, you should make sure you have restored the latest backup file on prod site, so manually run copy/restore job on DR side to bring all these 3 filename are same.

Then you should also do tail log backup on prod side if possible to reduce data lost. run this:

USE master 
GO 
BACKUP LOG LINUXTECRES_LIVE
TO DISK = 'C:\backups\prod_tail_log.bak' 
WITH NORECOVERY

then copy above prod_tail_log.bak to DR sql server then restore it manually like this:

RESTORE LOG LINUXTECHRES_LIVE
FROM DISK = 'path_to_prod_tail_log.bak'
WITH RECOVERY

References

1. http://www.sqlskills.com/blogs/glenn/how-to-avoid-orphaned-database-users-with-sql-server-authentication/