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.

Open ports on Linux

CentOS


How to check if a port is open

netstat -an | grep port

telnet localhost/IP adress/127.0.0.0 port

iptables -L

Opening Ports in a Linux Firewall


The procedure for opening ports in the Linux firewall "iptables" is relatively simple.

First, open a command-line terminal.  In most systems, you can usually find this in your 'Applications' menu under the 'System Tools' section.

Once you have a terminal open, you have to obtain root access to change firewall settings.  Do this by typing:

su -

You will be prompted for your root password.

In the following examples, substitute the port # you want to open for the 12345 in the command.

If you want to open an incoming TCP port, type the following:

iptables -I INPUT -p tcp --dport 12345 --syn -j ACCEPT

If you want to open a UDP port (perhaps for DHT in Tixati), type the following:

iptables -I INPUT -p udp --dport 12345 -j ACCEPT

After you are done opening ports in your firewall, you can save your changes so they will be applied when you restart your computer by typing the following command:

service iptables save

Ubuntu

sudo iptables -A INPUT -p tcp --dport (port number) -j ACCEPT

or

sudo ufw allow 80

Oracle 11G R2 install on CentOS version 57 linux

Oracle install in silent mode

Create Required Group and User

Add the below given groups and user if they do not exists on the system.
#groupadd oinstall
#groupadd dba
#groupadd oper
#useradd –g oinstall –G dba,oper oracle
#passwd oracle

Create software install folders

#mkdir –p /u01/app/oracle/product/11.2.0/db_1
#chown –R oracle:oinstall /u01
#chmod –R 755 /u01

Configure User environment

Add the below given lines in /etc/profile

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=apal3; export ORACLE_SID

ORACLE_UNQNAME=apal3; export ORACLE_UNQNAME

PATH=$ORACLE_HOME/bin:$PATH; export PATH

Add the below given lines in oracle user .bash_profile

umask 022

Configuration of Kernel Parameters

Using any text editor, edit the /etc/sysctl.conf

net.ipv4.ip_forward = 0

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.accept_source_route = 0

kernel.sysrq = 0

kernel.core_uses_pid = 1

net.ipv4.tcp_syncookies = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.shmmax = 2147483648

kernel.shmall = 2097152

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

fs.file-max = 6815744

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 1048576

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

After adding the above lines to the /etc/sysctl.conf file, they persist each time the system reboots. If you would like to make these kernel parameter value changes to the current system without having to reboot, enter the following command:

#sysctl –p

Using any text editor, edit the /etc/security/limits.conf

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  1024

oracle              hard    nofile  65536

Edit the /etc/pam.d/login file and add following line:

# added for Oracle 11.2.0.3

session    required     pam_limits.so

Check current status of SELinux:

#/usr/sbin/getenforce

Enforcing

If output is Enforcing then change mode to Permissive as follows:

#/usr/sbin/setenforce 0

To make the change permanent, modify the /etc/sysconfig/selinux change value of SELINUX variable to disabled:

Add Required RPMs

For this I would recommend to check the oracle web site because different OS versions require different rpm’s. The below given package’s where required for red hat linux5.7 .

compat-libstdc++-33-3.2.3-69.el6.*.rpm

elfutils-devel-0.148-1.el6.x86_64.rpm

elfutils-libelf-devel-0.148-1.el6.x86_64.rpm

gcc-c++-4.4.4-13.el6.x86_64.rpm

glibc-2.12-1.7.el6.i686.rpm

glibc-devel-2.12-1.7.el6.i686.rpm

libaio-devel-0.3.107-10.el6.x86_64.rpm

libaio-0.3.107-10.el6.i686.rpm

libgcc-4.4.4-13.el6.i686.rpm

libstdc++-devel-4.4.4-13.el6.x86_64.rpm

libtool-ltdl-2.2.6-15.5.el6.i686.rpm

nss-softokn-freebl-3.12.7-1.1.el6.i686.rpm

readline-6.0-3.el6.i686.rpm

ncurses-libs-5.7-3.20090208.el6.i686.rpm

libcap-2.16-5.2.el6.i686.rpm

libattr-2.4.44-4.el6.i686.rpm

compat-libcap1-1.10-1.*.rpm

Use below given command to check if the rpm is installed

#rpm –qa | grep package name

Use the below given command to install the required package

#yum –install package name

Creating the oraInst.loc File

Create the /etc/oraInst.loc as below and chage the permission  and ownership.

inventory_loc=/u01/app/oraInventory

inst_group=oinstall

#chown oracle:oinstall oraInst.loc

#chmod 664 oraInst.loc

Prepare the responds file

The sample response file can be found under  “database/response ”.  Here is the example of db_install.rsp.

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0

oracle.install.option=INSTALL_DB_AND_CONFIG

ORACLE_HOSTNAME=linux57

UNIX_GROUP_NAME=oinstall

INVENTORY_LOCATION=/u01/app/oraInventor

SELECTED_LANGUAGES=en

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.isCustomInstall=false

oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,orac
le.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0
,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0

oracle.install.db.DBA_GROUP=dba

oracle.install.db.OPER_GROUP=oper

oracle.install.db.CLUSTER_NODES=

oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

oracle.install.db.config.starterdb.globalDBName=apal3

oracle.install.db.config.starterdb.SID=apal3

oracle.install.db.config.starterdb.characterSet=AL32UTF8

oracle.install.db.config.starterdb.memoryOption=true

oracle.install.db.config.starterdb.memoryLimit=400

oracle.install.db.config.starterdb.installExampleSchemas=false

oracle.install.db.config.starterdb.enableSecuritySettings=true

oracle.install.db.config.starterdb.password.ALL=1Qa2ws3ed4rf

oracle.install.db.config.starterdb.password.SYS=1Qa2ws3ed4rf

oracle.install.db.config.starterdb.password.SYSTEM=oracle

oracle.install.db.config.starterdb.password.SYSMAN=

oracle.install.db.config.starterdb.password.DBSNMP=

oracle.install.db.config.starterdb.control=DB_CONTROL

oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=

oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false

oracle.install.db.config.starterdb.dbcontrol.emailAddress=

oracle.install.db.config.starterdb.dbcontrol.SMTPServer=

oracle.install.db.config.starterdb.automatedBackup.enable=false

oracle.install.db.config.starterdb.automatedBackup.osuid=

oracle.install.db.config.starterdb.automatedBackup.ospwd=

oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE

oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/u01/app/oracle/oradata

oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/u01/app/oracle/flash_recovery_area

oracle.install.db.config.asm.diskGroup=

oracle.install.db.config.asm.ASMSNMPPassword=

MYORACLESUPPORT_USERNAME=

MYORACLESUPPORT_PASSWORD=

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

DECLINE_SECURITY_UPDATES=true

PROXY_HOST=

PROXY_PORT=

PROXY_USER=

PROXY_PWD=

Command to install oracle is given below:-

First login as oracle and go to where the install script is extracted.

$ cd /tmp/database/

$ ./runInstaller -silent -responseFile /tmp/database/response/db_install.rsp

 Wait for the installation step to prompt for executing the scripts. Open terminal and execute the scripts as "root" user.

Auto restart Oracle and related service via dbstart

Create /etc/init.d/oracle

#!/bin/sh

#

# /etc/rc.d/init.d/oracle

# Description: Starts and stops the Oracle database, listeners and Enterprise Manager

# See how we were called.

case "$1" in

start)

echo "Starting Oracle"

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

echo -n "Starting Oracle Databases: "

su - oracle -c dbstart >> /var/log/oracle

echo "Done."

echo -n "Starting Oracle Listeners: "

su - oracle -c "lsnrctl start" >> /var/log/oracle

echo "Done."

echo -n "Starting Oracle Enterprise Manager: "

su - oracle -c "emctl start dbconsole" >> /var/log/oracle

echo "Done."

echo ""

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Finished." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

touch /var/lock/subsys/oracle

;;

stop)

echo "Shutting Down Oracle"

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

echo -n "Shutting Down Oracle Enterprise Manager: "

su - oracle -c "emctl stop dbconsole" >> /var/log/oracle

echo "Done."

echo -n "Shutting Down Oracle Listeners: "

su - oracle -c "lsnrctl stop" >> /var/log/oracle

echo "Done."

rm -f /var/lock/subsys/oracle

echo -n "Shutting Down Oracle Databases: "

su - oracle -c dbshut >> /var/log/oracle

echo "Done."

echo ""

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Finished." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

;;

restart)

echo "Restarting Oracle"

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

echo -n "Restarting Oracle Databases: "

su - oracle -c dbshut >> /var/log/oracle

su - oracle -c dbstart >> /var/log/oracle

echo "Done."

echo -n "Restarting Oracle Listeners: "

su - oracle -c "lsnrctl stop" >> /var/log/oracle

su - oracle -c "lsnrctl start" >> /var/log/oracle

echo "Done."

echo -n "Restarting Oracle Enterprise Manager: "

su - oracle -c "emctl stop dbconsole" >> /var/log/oracle

su - oracle -c "emctl start dbconsole" >> /var/log/oracle

echo "Done."

echo ""

echo "—————————————————" >> /var/log/oracle

date +"! %T %a %D : Finished." >> /var/log/oracle

echo "—————————————————" >> /var/log/oracle

touch /var/lock/subsys/oracle

;;

*)

echo "Usage: oracle {start|stop|restart}"

exit 1

esac


Creating shortcuts for different level to start up and shut down oracle

# chmod +x oracle

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc2.d/S99oracl

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc3.d/S99oracle

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc4.d/S99oracle

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc5.d/S99oracle

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc0.d/K01oracle

# ln -s /etc/rc.d/init.d/oracle /etc/rc.d/rc6.d/K01oracle

Add user to Oracle

$ sqlplus sys as sysdba (it will prompt for the password)

SQL> CREATE USER testuser IDENTIFIED BY "testuser_rw"

SQL> grant connect,resource to testuser;

SQL> grant create table to testuser;

SQL> grant create sequence to testuser;

SQL> grant create tablespace, drop tablespace to testuser;

SQL> grant create procedure to testuser;

SQL> grant create trigger to testuser;

SQL> grant execute on dbms_alert to testuser;

SQL> grant dba to testuser ;