May 19, 2017

Oracle WebLogic Server (WLS) 11gR1 (10.3.6) Installation on RHEL 6



Download oracle weblogic server 10.3.6 from oracle site:-
# wget http://download.oracle.com/otn/nt/middleware/11g/wls/1036/wls1036_generic.jar


Also download java development kit(jdk):-

# wget http://download.oracle.com/otn-pub/java/jdk/8u121-b13/e9e7ea248e2c4826b92b3f075a80e441/jdk-8u121-linux-i586.tar.gz


Create the directories in which the Oracle weblogic software will be installed.



Add users and groups
groupadd -g 501 oinstall
groupadd -g 502 dba


useradd -u 502 -g oinstall -G dba oracle
passwd oracle



mkdir -p /u01/app/oracle/product/fmw11g
mkdir -p /u01/app/oracle/config/domains
mkdir -p /u01/app/oracle/config/applications
chown -R oracle:oinstall /u01
chmod -R 775 /u01/


#yum -y install glibc-devel.i686
yum install libaio-devel -y
yum install sysstat -y
yum install openmotif -y
yum install openmotif22 -y



[root@server1 mnt]# alternatives --config java

There are 4 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
   1           /usr/lib/jvm/jre-1.5.0-gcj/bin/java
   2           /usr/lib/jvm/jre-1.7.0-openjdk.x86_64/bin/java
   3           /usr/lib/jvm/jre-1.6.0-openjdk.x86_64/bin/java
*+ 4           /usr/java/jdk1.8.0_121/jre/bin/java

Enter to keep the current selection[+], or type selection number: 4


Now set the enviroments for oracle user:-
[root@server1 mnt]su - oracle
Add the following lines in .bash_profile

[oracle@server1 ]vi .bash_profile

export MW_HOME=/u01/app/oracle/product/fmw11g
export WLS_HOME=$MW_HOME/wlserver_10.3
export WL_HOME=$WLS_HOME
# Set to the appropriate JAVA_HOME.
export JAVA_HOME=/usr/java/jdk1.8.0_121
#export JAVA_HOME=/u01/app/oracle/jrockit-jdk1.6.0_45-R28.2.7-4.1.0
#export JAVA_HOME=/u01/app/oracle/jdk1.7.0_17
export PATH=$JAVA_HOME/bin:$PATH
:wq

Change permission and ownership of web
[root@server1 ]chown -Rf oracle:oinstall wls1036_generic.jar
[root@server1 ]chmod 775 wls1036_generic.jar

From oracle user:-

[oracle@server1 ] cd /u01
[oracle@server1 u01]$ $JAVA_HOME/bin/java -Xmx1024m -jar wls1036_generic.jar

The installation windown will start -> Provide middleware home directory "/u01/app/oracle/product/fmw11g" -> Uncheck the option to get oracle support via
email -> Select "i wish to remain uninformed option" -> continue "Typical installation" -> It should auto select the jdk installed home -> next -> next > finish.

April 1, 2017

How to migrate oracle users from one server to another


1. First take all the information using dbm_metadata.

set head off
set pages 0
set long 9999999

select dbms_metadata.get_ddl('USER', username) || '/' usercreate
from dba_users;


ALTER USER "SYS" IDENTIFIED BY VALUES 'S:1CCB2C8B206B3D3BA9164214F3262051E4C0
D076179455300F8674EDBC85;E100B964899CDDDF'
      TEMPORARY TABLESPACE "TEMP"
 /


   ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:DC73635359324A39D3636017D54F482E7
F3D7CC34D0426EA3C9628FFD120;970BAA5B81930A40'
      TEMPORARY TABLESPACE "TEMP"
 /
   CREATE USER "SOUMYA" IDENTIFIED BY VALUES 'S:3F6E037E8574BC8FB1F0CF44651E0DE9
7C8765DC0924224F19F7F43D583B;6673D7515E467AA4'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
 /

To get all their roles and grants:-

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SYS') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SYS') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SYS') FROM DUAL;



To script it in a single command:-
spool '/u01/app/dbusersinfo.sql'
 select dbms_metadata.get_ddl( 'USER', 'HR' ) from dual
 UNION ALL
 select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'HR' ) from dual
 UNION ALL
 select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'HR' ) from dual
 UNION ALL
 select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'HR' ) from dual;
spool off


  GRANT SELECT ON "HR"."D01" TO "SCOTT"                          
                                                                             
  GRANT SELECT ON "HR"."FILOM" TO "SCOTT"                        
                                                                             
  GRANT SELECT ON "HR"."HD01" TO "SCOTT"                        
                                                                             
  GRANT SELECT ON "HR"."HSPRF" TO "SCOTT"                        
                                                                             
  GRANT SELECT ON "HR"."SPRF" TO "SCOTT"                        
                                                                             
  GRANT SELECT ON "HR"."HFILOM" TO "SCOTT"                      
                                                                             
                                                                             
                                                                             
   GRANT "CONNECT" TO "SCOTT"                                          
                                                                             
   GRANT "RESOURCE" TO "SCOTT"                                        


 Now run the above output into database server where you want to migrate the users.

March 15, 2017

How to setup Oracle ASM 12c on Linux

Software used:-
1.VMWARE 10
2.Redhat enterprise linux 6.5(64 bit)
3.Oracle database 12C (64 bit)
4.Oracle Grid infrastructure 12c(64 bit)


What to Setup:-

1. Setup oracle grid infrastructure for standalone server "ASM"
2. Setup oracle Database


Update /etc/sysctl.conf
[root@server1]# vi /etc/sysctl.conf
Scroll to the bottom and add the following:

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
:wq

Run the following command to change the current kernel parameters.
/sbin/sysctl -p

Update /etc/security/limits.conf
[root@server1]# vi /etc/security/limits.conf
Scroll to the bottom and above the “# End of file” line, add:

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

:wq

Amend the "/etc/security/limits.d/90-nproc.conf" file as described below.
# Change this
*          soft    nproc    1024

# To this
* - soft    nproc    16384

**IMPORTANT: Make sure selinux is disabled.

Packages required for oracle database installation:-

[root@server1] yum -y install binutils-2.17.50.0.6
[root@server1] yum -y install compat-libstdc++-33-3.2.3 (*)
[root@server1] yum -y install elfutils-libelf-0.125
[root@server1] yum -y install elfutils-libelf-devel-0.125 (*)
[root@server1] yum -y install gcc-4.1.2
[root@server1] yum -y install gcc-c++-4.1.2 (*)
[root@server1] yum -y install glibc-2.5-24
[root@server1] yum -y install glibc-common-2.5
[root@server1] yum -y install glibc-devel-2.5
[root@server1] yum -y install glibc-headers-2.5
[root@server1] yum -y install ksh-20060214 (*)
[root@server1] yum -y install libaio-0.3.106
[root@server1] yum -y install libaio-devel-0.3.106
[root@server1] yum -y install libgcc-4.1.2
[root@server1] yum -y install libgomp-4.1.2
[root@server1] yum -y install libstdc++-4.1.2
[root@server1] yum -y install libstdc++-devel-4.1.2
[root@server1] yum -y install make-3.81
[root@server1] yum -y install numactl-devel-0.9.8.i386 (*)
[root@server1] yum -y install sysstat-7.0.2 (*)


Check the kernel version:-
[root@server1 var]# uname -r
2.6.32-358.el6.x86_64

Now we would require to install the rpm's required for asm installation
these are the packages required for asm.

--oracleasm
--oracleasm-support
--oracleasmlib
the last 2 packages can be found from the following link
http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html

oracle kmod-oracleasm rpm download link for el6
http://public-yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/getPackage/kmod-oracleasm-2.0.6.rh1-2.el6.x86_64.rpm

[root@server3 ~]# rpm -Uvh oracleasm-support-2.1.8-1.el6.x86_64.rpm
[root@server3 ~]# rpm -Uvh kmod-oracleasm-2.0.6.rh1-2.el6.x86_64.rpm
[root@server3 ~]# rpm -Uvh oracleasmlib-2.0.4-1.el6.x86_64.rpm


Create groups:-
[root@server3 tmp]# groupadd -g 1000 oinstall
[root@server3 tmp]# groupadd -g 1200 dba
[root@server3 tmp]# useradd -g oinstall -G dba -d /home/oracle oracle


Create directory structures:-
[root@server3 u01]# mkdir -p /u01/app/oracle/product/12.1.0/grid
[root@server3 u01]# mkdir -p /u01/app/oracle/product/12.1.0/db_1

Assigning proper permission:-
[root@server3 u01]# chown -Rf oracle:oinstall /u01/
[root@server3 u01]# chmod -Rf 775 /u01/

Set up the oracle user environment
For oracle user:-
[root@server1 var]# su - oracle

[oracle@server1]#vi .bash_profile
#export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=server3.soumya.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
GRID_HOME=/u01/app/oracle/product/12.1.0/grid; export GRID_HOME
DB_HOME=$ORACLE_BASE/product/12.1.0/db_1; export DB_HOME
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
BASE_PATH=/usr/sbin:$PATH; export BASE_PATH
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATHH
if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

alias grid_env='. /home/oracle/grid_env'
alias db_env='. /home/oracle/db_env'

:wq(save & exit)

[oracle@server1 ~]$ . .bash_profile


Create a file called "/home/oracle/db_env" with the following contents :-

[oracle@server1 ~]$vi /home/oracle/db_env

ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq(save & exit)


Create a file called "/home/oracle/grid_env" with the following contents:-

[oracle@server1 ~]$vi /home/oracle/gid_env
ORACLE_SID=+ASM; export ORACLE_SID
ORACLE_HOME=$GRID_HOME; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

:wq(save & exit)



[oracle@server1 ~]$ chmod 775 /home/oracle/db_env
[oracle@server1 ~]$ chmod 775 /home/oracle/grid_env

Now you will be able to switch environments between oracle and asm instance as follows.
[oracle@server3 ~]$ db_env
[oracle@server3 ~]$ echo $ORACLE_SID
orcl
[oracle@server3 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
[oracle@server3 ~]$ grid_env
[oracle@server3 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/grid
[oracle@server3 ~]$ echo $ORACLE_SID
+ASM


Now  we will add 3 disks using vmware.
So open vmware workstation and go to settings and add hard disk from there,add 3 different disks size of atleast 10GB each.

[root@server1]# echo "- - -"> /sys/class/scsi_host/host0/scan

******
P.S. if the above command doesnt show the newly added disk try this
[root@server1]#grep mpt /sys/class/scsi_host/host?/proc_name
/sys/class/scsi_host/host2/proc_name:mptspi


then run this
[root@server1]# echo "- - -"> /sys/class/scsi_host/host2/scan
******

using the above command we can avoid rebooting the machine to  mount the hard disks.

[root@server1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa4bd7fb9.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-261, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-261, default 261):
Using default value 261

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.



[root@server1 ~]# fdisk /dev/sdc
[root@server1 ~]# fdisk /dev/sdd

[root@server1 dev]# fdisk -l
Disk /dev/sda: 64.4 GB, 64424509440 bytes
255 heads, 63 sectors/track, 7832 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x0006f980

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1               1        5737    46080000   83  Linux
/dev/sda2   *        5737        6885     9216000   83  Linux
/dev/sda3            6885        7458     4608000   83  Linux
/dev/sda4            7458        7833     3009536    5  Extended
/dev/sda5            7459        7731     2188288   82  Linux swap / Solaris
/dev/sda6            7731        7833      818176   83  Linux

Disk /dev/sdb: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x44ac96a0

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1         261     2096451   83  Linux

Disk /dev/sdc: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x004b1011

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1         261     2096451   83  Linux

Disk /dev/sdd: 2147 MB, 2147483648 bytes
255 heads, 63 sectors/track, 261 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xf5898159

   Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1         261     2096451   83  Linux

Give proper ownership and permissions to the new partition:-
chown -Rf oracle:oinstall /dev/sdb1
chown -Rf oracle:oinstall /dev/sdc1
chown -Rf oracle:oinstall /dev/sdd1

chmod -Rf 664 /dev/sdb1
chmod -Rf 664 /dev/sdc1
chmod -Rf 664 /dev/sdd1

Now configure ASM and create ASM disks:-

[root@server1 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [  OK  ]
Scanning the system for Oracle ASMLib disks: [  OK  ]
   

To create ASM disks:-
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
[root@server1 ~]#/etc/init.d/oracleasm createdisk VOL3 /dev/sdd1

Now we will install grid infrastructure software.
Give proper permission to the software folder.

[root@server1 ] chown -Rf oracle:oinstall /u01/linuxamd64_12102_grid_1of2.zip
root@server1 ] chown -Rf oracle:oinstall /u01/linuxamd64_12102_grid_2of2.zip

[root@server1 u01]# unzip linuxamd64_12102_grid_1of2.zip
[root@server1 u01]# unzip linuxamd64_12102_grid_2of2.zip
[root@server1 u01]#su - oracle
[oracle@server1 u01]$ cd grid/
[oracle@server1 grid]$ sh runInstaller


Select "install and configure grid infrastructure for a standalone server" -> Next -> select 3 Disks from candidate disk option rest option will be unchanged  ->
select "use same password for these accounts " and provide password -> specify os groups OSDBA-oinstall,
OSOPER-oinstall, OSASM-oinstall -> Select Install location "oracle base- /u01/app/oracle " , "software location- /u01/app/oracle/product/12.1.0/grid" -> Next and
start the installation.> execute "/u01/app/oracle/product/12.1.0/grid/root.sh" script from root user from another terminal.


I got this error while installing grid infrastructure.To fix this steps are below:-
**INFO: Read: ORA-00845: MEMORY_TARGET not supported on this system

To increase the size
# mount -o remount,size=3G /dev/shm
Verify the size
# df -h
To make permanent changes to your file system update your fstab
# vi /etc/fstab
tmpfs  /dev/shm  tmpfs  defaults,size=3G  0 0

[root@server1 u01]#

[grid@server3 app]$ sqlplus  / as sysasm
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+ASM


Now we will setup oracle database .

[oracle@server1 u01]$ cd /u01/database/
[oracle@server3 u01]$ sh runInstaller

select "Create & configure a database" -> Server Class -> Single instance database installation -> Advanced Installa -> Next ->Enterprise edition ->
Select "oracle base- /u01/app/oracle " , "software location- /u01/app/oracle/product/12.1.0/db_1" -> select "general purpose" -> Global database
name- orcl , SID name - orcl -> Next -> select " Oracle automatic storage management"-> Next ->Next ->Next-> select " use same password for all accounts"->
Next -> Next -> Install

Done!!!...

February 6, 2017

How to take SQL server database backup without data?

How to take SQL server database backup without data?

Version: Sql server 2012

There are different method available to achieve this goal. Such as..Script out the source database and then run the script against an empty target database to create all database objects that are in the source database

Right click on the database -> select "tasks" -> "Generate scripts"-> Next -> Select script entire database and all database objects -> Save the sql file in location ->Next-> Next-> Finish.
Now If you want to restore the database just execute  the content of sql file and this will create a new database with only data structure .

This is the content of sql file.

USE [master]
GO
/****** Object:  Database [newdb]    Script Date: 2/3/2017 10:20:17 AM ******/
CREATE DATABASE [newdb]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'sourcedb', FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\newdb.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'sourcedb_log', FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\newdb_log.ldf' , SIZE = 768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [newdb] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [newdb].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [newdb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [newdb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [newdb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [newdb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [newdb] SET ARITHABORT OFF
GO
ALTER DATABASE [newdb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [newdb] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [newdb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [newdb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [newdb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [newdb] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [newdb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [newdb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [newdb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [newdb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [newdb] SET  DISABLE_BROKER
GO
ALTER DATABASE [newdb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [newdb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [newdb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [newdb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [newdb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [newdb] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [newdb] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [newdb] SET RECOVERY FULL
GO
ALTER DATABASE [newdb] SET  MULTI_USER
GO
ALTER DATABASE [newdb] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [newdb] SET DB_CHAINING OFF
GO
ALTER DATABASE [newdb] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [newdb] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
EXEC sys.sp_db_vardecimal_storage_format N'newdb', N'ON'
GO
USE [newdb]
GO
/****** Object:  User [test1]    Script Date: 2/3/2017 10:20:17 AM ******/
CREATE USER [test1] FOR LOGIN [test1] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [som]    Script Date: 2/3/2017 10:20:17 AM ******/
CREATE USER [som] FOR LOGIN [som] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  User [readonly]    Script Date: 2/3/2017 10:20:17 AM ******/
CREATE USER [readonly] FOR LOGIN [readonly] WITH DEFAULT_SCHEMA=[db_datareader]
GO
ALTER ROLE [db_owner] ADD MEMBER [test1]
GO
ALTER ROLE [db_owner] ADD MEMBER [som]
GO
ALTER ROLE [db_datareader] ADD MEMBER [readonly]
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/3/2017 10:20:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
            [ID] [int] NULL,
            [Value] [varchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[t1]    Script Date: 2/3/2017 10:20:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t1](
            [id] [varchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[t2]    Script Date: 2/3/2017 10:20:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t2](
            [id] [varchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER DATABASE [newdb] SET  READ_WRITE
GO



Incase if you want to restore the database with different name then we would require to modify the sql file with the new database name .

 Another method is: Backup the source database and restore to the destination database and then delete all table data.

Now we will show you another method which also serve our purpose.

Backup the database without data.

In the SSMS Object Explorer Window, right click on the "newdb" database and choose "Tasks" > "Extract Data-tier Application..."




The [Extract Data-tier Application] wizard will start. 





Provide the DAC package file location 





click "Next"






click "Next"




Now we have newdb.dacpac file generated.


Restore a SQL Server Database from a DAC package

The DAC package can be restored to a target SQL Server instance whose version is equal to or higher than that of the source SQL Server instance.
 SSMS Window, right click [Databases] , and choose "Deploy Data-tier Application...", as shown below

The [Deploy Data-tier Application] wizard will start, Click next in the first [Introduction] screen, and in the [Select Package] screen, click the Browse button to find the DAC package file location


The [Deploy Data-tier Application] wizard will start, Click next and Browse button to find the DAC package file location.





Click Next, and in the [Update Configuration] screen, input the required destination database name or leave it as if you don’t want to change the db name.




That’s it. We have successfully restored the database with only data structure.










February 2, 2017

How to create linked server in SQL Server using TSQL ?

What is linked server?
Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers.
It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers.
The remote servers can be SQL Server, Oracle, Mysql etc. which means those databases that support OLE DB can be used for linking servers.

First create a user in mysql which will have permission for atleast select command.

In mysql Server:-
mysql> grant select ON `koopkrachtdb `.* TO 'koopreport'@'192.168.2.100' identified by 'Rghdwf4324Fvxg';
Query OK, 0 rows affected (0.00 sec)



In SQL Server:-
Change the fields accordingly to your server settings.



/****** Object:  LinkedServer [KOOPKRATCHMYSQL]    Script Date: 11/04/2016 08:13:54 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'KOOPKRATCHMYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.3 ANSI Driver}; SERVER=52.71.55.125;DATABASE=koopkrachtdb; USER=koopreport; PASSWORD=Rghdwf4324Fvxg;option=3'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'KOOPKRATCHMYSQL',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'KOOPKRATCHMYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO


Related Posts Plugin for WordPress, Blogger...