How to shrink LDF file in SQL server 2008 R2

  In  Server Management Studio Right click on the database in question and go to Properties:

• Go to "Options" on the left
• Change the line "Recovery Model:" from Full to "Simple" and hit OK
• Right click on the database again, go to Tasks - Shrink - Files
• Under File Type: select Log and click OK

This will temporarily free up space for you, make sure that your database is set to back up and that its configured to empty the log after back up. Once you have resolved the space issue and made sure your log file is set to flush after backups, I recommend you put the recovery model back on Full. If it was already set to flush the log after backup, check to see why the backups aren't being done.

Oracle datafile recovery without RMAN

Scenario : In this scenario we will delete a datafile without taking a backup.

TABLESPACE : NON PERMANENT ( To find query select property_name,property_value from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE’ )

Note: PERMANENT TABLESPACE (for e.g – USERS which is created by default when we create a database using DBCA) cannot be recovered without a backup. Deleting a permanent tablespace datafile cannot be recreated unless an older backup is restored and archives are applied.

a) Create a tablespace
b) Create users
c) create a table and insert values.

CREATE tablespace TEST datafile '/oraDevBin/oradata/test01.dbf' SIZE 100M;
CREATE USER dbasupp IDENTIFIED BY abc DEFAULT tablespace TEST;
GRANT CREATE SESSION TO dbasupp;
GRANT CONNECT,resource TO dbasupp;
CONNECT dbasupp

Enter password:

CREATE TABLE emp (2 eno NUMBER(10),3 ename varchar2(20),4 dept varchar2(10));
INSERT INTO emp 2 VALUES (1,'Nazim','IT');
commit;

DROP THE DATAFILE

$ rm -f test01.dbf

FROM SQL PROMPT TRY TO BRING THE DATAFILE IN OFFLINE STATE

ALTER DATABASE datafile 6 offline; <<< 6 IS the file_id FOR TEST datafile

ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

TRY WITH OFFLINE DROP OPTION

ALTER DATABASE datafile 6 offline DROP;

— CREATE THE DATAFILE

ALTER DATABASE CREATE datafile 6;

RECOVER DATAFILE

recover datafile 6;

BRING THE DATAFILE IN ONLINE STATE

ALTER DATABASE datafile 6 online;

GO TO THE FILE LOCATION ON THE SERVER AND CHECK IF THE DATAFILE EXISTS
CHECK FOR ANY DATA LOSS

FTP Server Setup Steps on Ubuntu

• Create folders /FTPSites/WarehouseOptimizationFTP/ for FTP users home directories.
• mkdir -p /FTPSites/WarehouseOptimizationFTP/
• Install package vsftpd
• apt-get install vsftpd
• add-apt-repository ppa:thefrontiergroup/vsftpd () (I was facing problems when trying to lock ftp users to their home directory, found this solution for it in a blog)
• Edit /etc/vsftpd.conf (If required uncomment the fields or change it from YES to NO or vice versa)
• listen=YES
• anonymous_enable=NO
• local_enable=YES
• write_enable=YES
• local_umask=022
• dirmessage_enable=YES
• use_localtime=YES
• xferlog_enable=YES
• connect_from_port_20=YES
• data_connection_timeout=1200
• ftpd_banner=Welcome to TWO FTP service.
• chroot_local_user=NO
• chroot_list_enable=YES
• chroot_list_file=/etc/vsftpd.chroot_list
• secure_chroot_dir=/var/run/vsftpd/empty
• pam_service_name=vsftpd
• rsa_cert_file=/etc/ssl/private/vsftpd.pem
• # Passive mode
• pasv_enable=YES
• pasv_min_port=1024
• pasv_max_port=2048
• # Keep non-chroot listed users jailed
• allow_writeable_chroot=YES
• Edit /etc/vsftpd.chroot_list and add the users which will use the FTP server, the ftp users that were in the vsftpd.chroot_list are listed below.
• ftpusertest
• nestleewm
• woftptop
• ftpwoindia
• ftpwonetperception
• ftpwonz
• ftpwoohio
• ftpwoorbit
• ftpwotenn
• ftpbkpconagraapal
• ftpbkpdemo4coke
• ftpbkpdemo6apal
• ftpbkpdemoRMT
• ftpbkpdreyers
• ftpbkpibmtwo
• ftpbkpkraft
• ftpbkpkrftibm
• ftpbkpnestle
• ftpbkppepsiapal
• ftpbkpschenck
• ftpbkpschenckbkp
• ftpbkptest6apal
• ftpbkpTWOWEB
• ftpwoalert
• ftpwopgaps
• ftpwowebadmin
• ftpwowoolumsr
• ftpwostarzynski
• ftpwopanwar
• ftpuatbkp
• SacKapi
• ftpchris
• ftpwosumin
• ccj007
• ftptwowebsitebackup
• service vsftpd restart (this will put all the changes to the conf file to affect)
• Test using a ftp client (i.e FileZila). Make sure that the ftp users are unable to go above their home directory, here is is defined as /FTPSites/WarehouseOptimizationFTP/.

Move or migrate user accounts from old Linux server to a new Linux server

Q. How do I Move or migrate user accounts to from old Linux server a new Cent OS Linux server including mails? This new system a fresh installation.

A. You can migrate users from old Linux server to new Linux sever with standard commands such as tar, awk, scp and others. This is also useful if you are using old Linux distribution such as Redhat 9 or Debian 2.x.

Following files/dirs are required for traditional Linux user management:
* /etc/passwd - contains various pieces of information for each user account
* /etc/shadow - contains the encrypted password information for user's accounts and optional the password aging information.

* /etc/group - defines the groups to which users belong
* /etc/gshadow - group shadow file (contains the encrypted password for group)
* /var/spool/mail - Generally user emails are stored here.
* /home - All Users data is stored here.

You need to backup all of the above files and directories from old server to new Linux server.
Commands to type on old Linux system

First create a tar ball of old uses (old Linux system). Create a directory:
# mkdir /root/move/

Setup UID filter limit:
# export UGIDLIMIT=500

Now copy /etc/passwd accounts to /root/move/passwd.mig using awk to filter out system account (i.e. only copy user accounts)
# awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534)' /etc/passwd > /root/move/passwd.mig

Copy /etc/group file:
# awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534)' /etc/group > /root/move/group.mig

Copy /etc/shadow file:
# awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534) {print $1}' /etc/passwd | tee - |egrep -f - /etc/shadow > /root/move/shadow.mig

Copy /etc/gshadow (rarely used):
# cp /etc/gshadow /root/move/gshadow.mig

Make a backup of /home and /var/spool/mail dirs:
# tar -zcvpf /root/move/home.tar.gz /home
# tar -zcvpf /root/move/mail.tar.gz /var/spool/mail

Where,
Users that are added to the Linux system always start with UID and GID values of as specified by Linux distribution or set by admin. Limits according to different Linux distro:

RHEL/CentOS/Fedora Core : Default is 500 and upper limit is 65534 (/etc/libuser.conf).

Debian and Ubuntu Linux : Default is 1000 and upper limit is 29999 (/etc/adduser.conf).

You should never ever create any new system user accounts on the newly installed Cent OS Linux. So above awk command filter out UID according to Linux distro.
export UGIDLIMIT=500 - setup UID start limit for normal user account. Set this value as per your Linux distro.

awk -v LIMIT=$UGIDLIMIT -F: '($3>=LIMIT) && ($3!=65534)' /etc/passwd > /root/move/passwd.mig - You need to pass UGIDLIMIT variable to awk using -v option (it assigns value of shell variable UGIDLIMIT to awk program variable LIMIT). Option -F: sets the field separator to : . Finally awk read each line from /etc/passwd, filter out system accounts and generates new file /root/move/passwd.mig. Same logic is applies to rest of awk command.
tar -zcvpf /root/move/home.tar.gz /home - Make a backup of users /home dir
tar -zcvpf /root/move/mail.tar.gz /var/spool/mail - Make a backup of users mail dir

Use scp or usb pen or tape to copy /root/move to a new Linux system.
# scp -r /root/move/* user@new.linuxserver.com:/path/to/location
Commands to type on new Linux system

First, make a backup of current users and passwords:
# mkdir /root/newsusers.bak
# cp /etc/passwd /etc/shadow /etc/group /etc/gshadow /root/newsusers.bak

Now restore passwd and other files in /etc/
# cd /path/to/location
# cat passwd.mig >> /etc/passwd
# cat group.mig >> /etc/group
# cat shadow.mig >> /etc/shadow
# /bin/cp gshadow.mig /etc/gshadow

Please note that you must use >> (append) and not > (create) shell redirection.

Now copy and extract home.tar.gz to new server /home
# cd /
# tar -zxvf /path/to/location/home.tar.gz

Now copy and extract mail.tar.gz (Mails) to new server /var/spool/mail
# cd /
# tar -zxvf /path/to/location/mail.tar.gz

Now reboot system; when the Linux comes back, your user accounts will work as they did before on old system:
# reboot

Please note that if you are new to Linux perform above commands in a sandbox environment. Above technique can be used to UNIX to UNIX OR UNIX to Linux account migration. You need to make couple of changes but overall the concept remains the same.