Log shipping from Sql Server 2008r2 to Sql Server 2008R2
Log shipping from one server to another server is an option that a DBA has, for high arability in Sql Server.
It’s very much like Oracle data guard where from primary to standby archive redo logs goes and are applied automatically. Comparing to Oracle, I would say its lot easy to configure and troubleshoot.
Following are the pre-requisites of log shipping.
Primary database should be running in Full or Bulk recovery mode
Create a shared folder to store backup of transaction logs
We can use either SSMS or using T-Sql, I have discussed SSMS methods following.
Right click on the database and choose property options
From property option select “Transaction Log Shipping”
Select “Enable this as a primary database in a log shipping configuration” check box
Select “backup setting” under “transaction log backup”
In “network path to backup folder” provide path of shared network folder where backup files will be stored.
Now click on “Add” option and it will bring you to new page that asks for connection to your secondary database. Click on “Connect” button and provide logon information of your server hosting secondary database.
In “Initialize Secondary database” tab select first radio button if you want your secondary database to be created from scratch.
In “Copy files” option provide the path of locally created folder.
In “Restore Transaction Log” option choose standby mode and click ok. This will take you back at “Transaction log shipping page”
Optionally you can provide information of monitoring instance.
Now! That’s all you need to set up a Primary/Standby or Primary/Secondary database in SQL server.
This whole setup has created 4 jobs. 1 in primary database and 3 in secondary database. Following I have discussed the jobs.
The “Backup” job created in Primary site, would take backup of transactional log periodically. Sql Server agent put the backup files to designated shared folder.
The “Copy” job created in secondary site, would copy the backup logs from shared folder to local machine.
The “Restore” job would periodically restore the backup on secondary site and keep secondary database in sync with primary database.
The “Alert” job sends alerts at each event.
Any correction,suggestion would be appreciated.
Thanks and regards,
Wednesday, 10 September 2014
Sunday, 24 August 2014
SQL Server 2012 in-built functions
Hi,
In this blog, I have discussed Sql Server in-built functions to be used in T-sql, queries, DML's. I have detailed them and have provided example of each function. Any suggestion,correction would be highly appriciated.
--Math Functions
--Average function
Select avg(Person_id) avg from db_owner.person
--CharIndex function to find index of a particular alphabet. In example
--CHARTINDEX finds 'a' in first_name column of person starting from 1st character of
--string
Select CHARINDEX('a',First_Name,1)a,first_name from db_owner.Person
Use MS2012;
go
declare @avar xml
RETURN @avar.query('Product/Prices')
truncate table db_owner.person
delete * from db_owner.person where middle_name is null
create table t1(id int identity(1,1) default 1)
select * from db_owner.person
select sum(person_id),count(*) ,first_name from db_owner.person
select top(10)
desc sp_settrigger
declare @var1 nvarchar(20);
select @var1 ='My Example';
Select CharIndex ('m',@var1,1) Col1,
CharIndex('P',@var1,1) COl2,
CharIndex ('x',@var1,1) Col3
GO
---Concat Function
Select ConCat ('Mr.',' Jaidev ',' sharma')
Select Concat(First_name,'--',Last_name) from db_owner.Person
---Left function shows number of character from left side of string
-- in example first four character from left would be displayed
Select Left('jaidev sharma',4)
--Len function to get the total length of a string
Select len('jaidev')
Select len(first_name) leth,First_Name from db_owner.person
---Lowever and upper
Select lower('Jaidev'),upper('jaidev')
Select lower(first_name),upper(Last_Name) from db_owner.person
---Ltrim,Rtrim,trim removes blank spaces
select Ltrim(' jaidev')y,Rtrim('Jaidev ')x
Declare @var1 nvarchar(50);
set @var1 = ' My Name is Jaidev Sharma';
Select Ltrim(@var1)
--Substring to get a portion of a string starting from 2nd parameter and upto
--3rd parameter
Select substring('Jaidev',3,3)
Select First_Name,SubString(First_name,1,len(first_name)) nm from db_owner.person
--Find a particular characher left and right of a string
Select PATINDEX(lower('%S%'),'Australia')
Select First_name from db_owner.person
Select PatIndex('%o%',First_Name),First_Name from db_owner.Person
----Replace find a particular character give in 2nd parameter and replaces it
---a parameter give in 3 parameter. In example in 'jai' string 'a' is replaced with 'u'
---
Select Replace('jai','a','u')
Select First_name,Replace(First_name,'a','h') from db_owner.person
Select count(person_id) emp_count from db_owner.person
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---TSQL Date and Time Data Types and Functions
Select @@DATEFIRST
--to get current_Timestamp
Select CURRENT_TIMESTAMP
--to add date in one particular date. In example 1, I have added DAY,41 (no. of days) in to 16-Jun-2014 to see counting 41 from 16-jun what date
--would fall.
---eg 1
Select DATEADD(day,41,'2014-06-16')
--eg 2 in this I have added Month 1 (one month) in todays date
Select DateAdd(Month,1,SysDateTime())
--eg3 In this I have added Year 1 ( one year) in the today's date.
Select DateAdd(Year,1,SysDateTime())
---DateDiff function tells us the difference between to dates.
Select DateDiff(day,'2014-06-16','2014-07-28') --42 days
Select DateDiff(Month,'2014-06-16','2014-07-28') --1 month
Select DateDiff(Year,'2014-06-16','2014-07-28')
---DATEFROMPARTS
Select DateFromparts(1,2,3)
----DATENAME return a specific information (eg. Year,Month,Day, Day of Year or week) of a date. To get just year portion of date use year,
Select DateName(Year,CURRENT_TIMESTAMP) year
Select DateName(Month,CURRENT_TIMESTAMP) Month
Select DateName(day,Current_TimeStamp) Day
Select DateName(DayOfYear,Current_TimeStamp) Day_Of_Year
Select DateName(Week,Current_TimeStamp) WeekNo
----DATEPART Similar to DateName
Select DatePart(Month,Current_TimeStamp) Month
Select DatePart(Year,Current_Timestamp) Year
Select DatePart(Day,Current_TimeStamp) Day
--DATETIMEFROMPARTS converts integers in Data Format
Select DateTimeFromParts(2014,3,22,13,22,13,14) Dat1
--DATETIME2FROMPARTS
---Select DATETIME2FROMPARTS(2014,3,12,12,03,20,12,1)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1
--DAY to know current day of the month
Select day(current_timestamp)
--EOMONTH end of month
Select EoMonth(CURRENT_TIMESTAMP)
-- GETDATE()
Select GetDate()
--GETUTCDATE()
Select GETUTCDATE()
---ISDATE checks the format of date. If format is right than it return 1 otherwise 0
Select IsDate('14-03-02')-- Returns 0 as date format is wrong
Select SYSDATETIME()
Select IsDate(CURRENT_TIMESTAMP) --Returns 1 as date format provided by Current_TImeStamp is right
---- MONTH
Select MONTH(current_timestamp)
Select year(CURRENT_TIMESTAMP)
--SmallDateTimeFromParts
Select SMALLDATETIMEFROMPARTS(2014,07,29,1,04)
--SWITCHOFFSET
--- SYSDATETIME returnes system time stamp
Select SysDateTime()
-- SYSDATETIMEOFFSET
-- SYSUTCDATETIME
-- TIMEFROMPARTS
-- TODATETIMEOFFSET
----TSQL System Functions
---
select @@CONNECTIONS,getdate();
--@@Error to knowif error any came
Use Ms2012;
Go
Select * from db_owner.gender;
Update db_owner.Gender set Gender='UnKnown' where Gender_id=3;
delete from db_owner.gender where Gender_id=4;
if @@ERROR <> 0 Print N'There is no Gender Id 4'
Select * from db_owner.gender;
GO
-- @@IDENTITY to know last value IDENTITY value inserted
-- @@ROWCOUNT to see number of rows effected by a T-Sql Statement
use MS2012;
go
Update db_owner.gender set Gender='Male' where Gender_id=1;
Update Db_owner.Gender set gender='Female' Where Gender_id=2;
Update Db_owner.Gender set gender='Unkown' Where Gender_id=3;
if @@ROWCOUNT<> 0
print Concat('The number of rows affected are ' , @@Rowcount);
if @@ROWCOUNT=0
Print Concat(@@ROWCOUNT, ' no row effected' );
Select * from db_owner.gender;
go
-- COALESCE equal to NVL function of Oracle
Alter Table Db_owner.person
alter column middle_name nvarchar(20) null
Select * from db_owner.person
Update db_owner.Person set Middle_name=null where Person_id=103
Select First_name,Coalesce(Middle_name,null,'UnKown') from db_owner.Person
--ERROR_LINE returns the error at particular line when using with Try and Catch block
USE model;
GO
BEGIN TRY
SELECT 8/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
Select ERROR_MESSAGE () As ErrorMessage
Select ERROR_NUMBER () As ErrorNumber
Select ERROR_PROCEDURE() as Proce
Select ERROR_SEVERITY () as Sever
Select ERROR_STATE() as Stat
END CATCH;
GO
/* ERROR_MESSAGE
ERROR_NUMBER
ERROR_PROCEDURE
ERROR_SEVERITY
ERROR_STATE*/
---Host_Id
Select HOST_ID() as HostId
---HOST_NAME
Select Host_name() HostName
---IsNull
Select ISNULL(NUll,'Jai')
Select * from db_owner.person
select IsNull(Middle_name,'Kumar (replaced)'),Middle_name from db_owner.person
---ISNUMERIC either 1 or 0 if 1 than parameter is number
Select ISNUMERIC('a')
--- NULLIF checks if two expressions are same or not. If same than returns
--null otherwise value of first parameter
Select NullIf('a','a') col1,NullIf('a','b'),NullIf('jai','jai')
----Security Functions
--- CURRENT_USER if connect by windows authentication or sa than its show DBO.
Select Current_user as currentUser
Select ORIGINAL_LOGIN() as OrgLogin
--Session user shows the user of sesison. normally it s same as current user function.
Select SESSION_USER
--Shows the user at OS level connected to sql server
Select SYSTEM_USER
--User_Name tell the name of user at sql server level
Select USER_NAME()
TSQL Metadata Functions
--App_Name() gives the name of the applicaiton.In following example it gives 'Microsoft Sql Server Management Stuido - Query' output
Select APP_NAME ()
--DB_ID returns the number assigned to a database
Select DB_ID()
--DB_Name name of database
Select DB_NAME() DBNAMe
---Object_Definition gets the definition of an object
Select * from sys.objects
where name='PERSON'
select * from ms2012.db_owner.gender
Select OBJECT_DEFINITION(object_ID('ms2012.db_owner.gender'))
--Object_id to know the object id
Select Object_id('ms2012.db_owner.person')
--Ojbect Name to know the object name
Select OBJECT_NAME(437576597),Object_id('ms2012.db_owner.person')
Select OBJECT_SCHEMA_NAME(437576597,8)
Select SCHEMA_ID('db_owner')
Select SCHEMA_NAME(16384)
---TSQL Configuration Functions
Select @@LOCK_TIMEOUT as TimeOutOfLog
Select @@MAX_CONNECTIONS as MaxConn
Select @@SERVERNAME as SrvrNm
Select @@SERVICENAME as SrvcNm
select @@SPID as SPID
In this blog, I have discussed Sql Server in-built functions to be used in T-sql, queries, DML's. I have detailed them and have provided example of each function. Any suggestion,correction would be highly appriciated.
--Math Functions
--Average function
Select avg(Person_id) avg from db_owner.person
--CharIndex function to find index of a particular alphabet. In example
--CHARTINDEX finds 'a' in first_name column of person starting from 1st character of
--string
Select CHARINDEX('a',First_Name,1)a,first_name from db_owner.Person
Use MS2012;
go
declare @avar xml
RETURN @avar.query('Product/Prices')
truncate table db_owner.person
delete * from db_owner.person where middle_name is null
create table t1(id int identity(1,1) default 1)
select * from db_owner.person
select sum(person_id),count(*) ,first_name from db_owner.person
select top(10)
desc sp_settrigger
declare @var1 nvarchar(20);
select @var1 ='My Example';
Select CharIndex ('m',@var1,1) Col1,
CharIndex('P',@var1,1) COl2,
CharIndex ('x',@var1,1) Col3
GO
---Concat Function
Select ConCat ('Mr.',' Jaidev ',' sharma')
Select Concat(First_name,'--',Last_name) from db_owner.Person
---Left function shows number of character from left side of string
-- in example first four character from left would be displayed
Select Left('jaidev sharma',4)
--Len function to get the total length of a string
Select len('jaidev')
Select len(first_name) leth,First_Name from db_owner.person
---Lowever and upper
Select lower('Jaidev'),upper('jaidev')
Select lower(first_name),upper(Last_Name) from db_owner.person
---Ltrim,Rtrim,trim removes blank spaces
select Ltrim(' jaidev')y,Rtrim('Jaidev ')x
Declare @var1 nvarchar(50);
set @var1 = ' My Name is Jaidev Sharma';
Select Ltrim(@var1)
--Substring to get a portion of a string starting from 2nd parameter and upto
--3rd parameter
Select substring('Jaidev',3,3)
Select First_Name,SubString(First_name,1,len(first_name)) nm from db_owner.person
--Find a particular characher left and right of a string
Select PATINDEX(lower('%S%'),'Australia')
Select First_name from db_owner.person
Select PatIndex('%o%',First_Name),First_Name from db_owner.Person
----Replace find a particular character give in 2nd parameter and replaces it
---a parameter give in 3 parameter. In example in 'jai' string 'a' is replaced with 'u'
---
Select Replace('jai','a','u')
Select First_name,Replace(First_name,'a','h') from db_owner.person
Select count(person_id) emp_count from db_owner.person
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---TSQL Date and Time Data Types and Functions
Select @@DATEFIRST
--to get current_Timestamp
Select CURRENT_TIMESTAMP
--to add date in one particular date. In example 1, I have added DAY,41 (no. of days) in to 16-Jun-2014 to see counting 41 from 16-jun what date
--would fall.
---eg 1
Select DATEADD(day,41,'2014-06-16')
--eg 2 in this I have added Month 1 (one month) in todays date
Select DateAdd(Month,1,SysDateTime())
--eg3 In this I have added Year 1 ( one year) in the today's date.
Select DateAdd(Year,1,SysDateTime())
---DateDiff function tells us the difference between to dates.
Select DateDiff(day,'2014-06-16','2014-07-28') --42 days
Select DateDiff(Month,'2014-06-16','2014-07-28') --1 month
Select DateDiff(Year,'2014-06-16','2014-07-28')
---DATEFROMPARTS
Select DateFromparts(1,2,3)
----DATENAME return a specific information (eg. Year,Month,Day, Day of Year or week) of a date. To get just year portion of date use year,
Select DateName(Year,CURRENT_TIMESTAMP) year
Select DateName(Month,CURRENT_TIMESTAMP) Month
Select DateName(day,Current_TimeStamp) Day
Select DateName(DayOfYear,Current_TimeStamp) Day_Of_Year
Select DateName(Week,Current_TimeStamp) WeekNo
----DATEPART Similar to DateName
Select DatePart(Month,Current_TimeStamp) Month
Select DatePart(Year,Current_Timestamp) Year
Select DatePart(Day,Current_TimeStamp) Day
--DATETIMEFROMPARTS converts integers in Data Format
Select DateTimeFromParts(2014,3,22,13,22,13,14) Dat1
--DATETIME2FROMPARTS
---Select DATETIME2FROMPARTS(2014,3,12,12,03,20,12,1)
SELECT DATETIME2FROMPARTS(2014, 2, 22, 8, 53, 39, 7, 1) as Date_1
--DAY to know current day of the month
Select day(current_timestamp)
--EOMONTH end of month
Select EoMonth(CURRENT_TIMESTAMP)
-- GETDATE()
Select GetDate()
--GETUTCDATE()
Select GETUTCDATE()
---ISDATE checks the format of date. If format is right than it return 1 otherwise 0
Select IsDate('14-03-02')-- Returns 0 as date format is wrong
Select SYSDATETIME()
Select IsDate(CURRENT_TIMESTAMP) --Returns 1 as date format provided by Current_TImeStamp is right
---- MONTH
Select MONTH(current_timestamp)
Select year(CURRENT_TIMESTAMP)
--SmallDateTimeFromParts
Select SMALLDATETIMEFROMPARTS(2014,07,29,1,04)
--SWITCHOFFSET
--- SYSDATETIME returnes system time stamp
Select SysDateTime()
-- SYSDATETIMEOFFSET
-- SYSUTCDATETIME
-- TIMEFROMPARTS
-- TODATETIMEOFFSET
----TSQL System Functions
---
select @@CONNECTIONS,getdate();
--@@Error to knowif error any came
Use Ms2012;
Go
Select * from db_owner.gender;
Update db_owner.Gender set Gender='UnKnown' where Gender_id=3;
delete from db_owner.gender where Gender_id=4;
if @@ERROR <> 0 Print N'There is no Gender Id 4'
Select * from db_owner.gender;
GO
-- @@IDENTITY to know last value IDENTITY value inserted
-- @@ROWCOUNT to see number of rows effected by a T-Sql Statement
use MS2012;
go
Update db_owner.gender set Gender='Male' where Gender_id=1;
Update Db_owner.Gender set gender='Female' Where Gender_id=2;
Update Db_owner.Gender set gender='Unkown' Where Gender_id=3;
if @@ROWCOUNT<> 0
print Concat('The number of rows affected are ' , @@Rowcount);
if @@ROWCOUNT=0
Print Concat(@@ROWCOUNT, ' no row effected' );
Select * from db_owner.gender;
go
-- COALESCE equal to NVL function of Oracle
Alter Table Db_owner.person
alter column middle_name nvarchar(20) null
Select * from db_owner.person
Update db_owner.Person set Middle_name=null where Person_id=103
Select First_name,Coalesce(Middle_name,null,'UnKown') from db_owner.Person
--ERROR_LINE returns the error at particular line when using with Try and Catch block
USE model;
GO
BEGIN TRY
SELECT 8/0;
END TRY
BEGIN CATCH
SELECT ERROR_LINE() AS ErrorLine;
Select ERROR_MESSAGE () As ErrorMessage
Select ERROR_NUMBER () As ErrorNumber
Select ERROR_PROCEDURE() as Proce
Select ERROR_SEVERITY () as Sever
Select ERROR_STATE() as Stat
END CATCH;
GO
/* ERROR_MESSAGE
ERROR_NUMBER
ERROR_PROCEDURE
ERROR_SEVERITY
ERROR_STATE*/
---Host_Id
Select HOST_ID() as HostId
---HOST_NAME
Select Host_name() HostName
---IsNull
Select ISNULL(NUll,'Jai')
Select * from db_owner.person
select IsNull(Middle_name,'Kumar (replaced)'),Middle_name from db_owner.person
---ISNUMERIC either 1 or 0 if 1 than parameter is number
Select ISNUMERIC('a')
--- NULLIF checks if two expressions are same or not. If same than returns
--null otherwise value of first parameter
Select NullIf('a','a') col1,NullIf('a','b'),NullIf('jai','jai')
----Security Functions
--- CURRENT_USER if connect by windows authentication or sa than its show DBO.
Select Current_user as currentUser
Select ORIGINAL_LOGIN() as OrgLogin
--Session user shows the user of sesison. normally it s same as current user function.
Select SESSION_USER
--Shows the user at OS level connected to sql server
Select SYSTEM_USER
--User_Name tell the name of user at sql server level
Select USER_NAME()
TSQL Metadata Functions
--App_Name() gives the name of the applicaiton.In following example it gives 'Microsoft Sql Server Management Stuido - Query' output
Select APP_NAME ()
--DB_ID returns the number assigned to a database
Select DB_ID()
--DB_Name name of database
Select DB_NAME() DBNAMe
---Object_Definition gets the definition of an object
Select * from sys.objects
where name='PERSON'
select * from ms2012.db_owner.gender
Select OBJECT_DEFINITION(object_ID('ms2012.db_owner.gender'))
--Object_id to know the object id
Select Object_id('ms2012.db_owner.person')
--Ojbect Name to know the object name
Select OBJECT_NAME(437576597),Object_id('ms2012.db_owner.person')
Select OBJECT_SCHEMA_NAME(437576597,8)
Select SCHEMA_ID('db_owner')
Select SCHEMA_NAME(16384)
---TSQL Configuration Functions
Select @@LOCK_TIMEOUT as TimeOutOfLog
Select @@MAX_CONNECTIONS as MaxConn
Select @@SERVERNAME as SrvrNm
Select @@SERVICENAME as SrvcNm
select @@SPID as SPID
Tuesday, 22 July 2014
Table Recovery
Table recovery in Oracle 10g
Many a times it happens that a developer or sometimes you as
a DBA, accidently drops a table and want to correct things by restoring it
back.
Oracle9i Database introduced the concept of a Flashback
Query option to retrieve data from a point in time in the past, but it can’t
flash back DDL operations such as dropping a table. The only recourse is to use
table space point-in-time recovery in a different database and then recreate
the table in the current database using export/import or some other method.
This procedure demands significant DBA effort as well as precious time, not to
mention the use of a different database for cloning
However Oracle 10g came with excellent feature table
recovery is as simple as that running few very simple commands.
We discuss this
feature in this blog:
SQL> Select banner from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE
10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 – Production
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
TEST_NULL TABLE
SQL> Drop Table T1;
Table dropped.
SQL> select * from tab;
BIN$/sNVxevp5A/gQKjAEQAYwA==$0 TABLE
Notice! We used “Drop Table T1” statement and
expected that table is dropped and spaced is freed. But this is not so. Instead
a new table has come with some bizarre name. This is the name that Oracle gave
to our table T1.
What happened is Oracle tossed a new terminology
called RECYCLEBIN just like the recycle bin that we have in our Windows
machines. Instead of dropping table and freeing space, Oracle kept table in
Recyclebin for just in case DBA wants to flashback it.
Let’s see what else RECYCLEBIN has stored there.
SQL> show recyclebin
ORIGINAL NAME
RECYCLEBIN NAME
OBJECT TYPE DROP TIME
---------------- --------------------------------------------
------------ -----------------------------------------
CURRENCY_TEST
BIN$/mGWpLuT4DLgQKjAEQAWBg==$0 TABLE 2014-07-17:16:46:32
CURRENCY_TEST
BIN$/mGWpLuS4DLgQKjAEQAWBg==$0 TABLE 2014-07-17:16:33:19
T1 BIN$/sNVxevp5A/gQKjAEQAYwA==$0 TABLE 2014-07-22:12:24:08
See! Except others I have my T1 table stored in
there. Now lets see the magic.
SQL> Flashback table t1 to before drop;
Flashback complete.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
See. We got over table back at place. J
Now one important question arises how I can drop
my table completely if at all we want to drop it. In this case use PURGE option
of drop table.
SQL> Drop Table T1 Purge;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME
RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------
-------------------------------------------------- -------------------
CURRENCY_TEST
BIN$/mGWpLuT4DLgQKjAEQAWBg==$0 TABLE 2014-07-17:16:46:32
CURRENCY_TEST
BIN$/mGWpLuS4DLgQKjAEQAWBg==$0 TABLE 2014-07-17:16:33:19
This time, We did not get table listed in
RECYCLEBIN
This particular operation related to PURGE can be done in another way.
Once we drop the Table without PURGE clause we can use PURGE Clause as follows
to remove table from RECYCLEBIN.
SQL> Purge table t1;
Table purged.
Sometimes it happens that you dropped lots of
tables from one particular tablespace. So instead of doing PURGE one by one,
you can use following command simply to remove all entries in one go.
In this example, I had all of my tables in USERTBS
tablespace. So I used following command to remove all tables from RECYCLEBIN.
SQL> Purge Tablespace USERTBS;
Tablespace purged.
Similarly, If you had dropped tables related to
one particular user than you can use variant of this particular command.
SQL> Purge Tablespace UserTBS user test;
Tablespace purged.
OR
Connect to TEST user and run following command.
SQL> Purge Recyclebin;
Recyclebin purged.
You as DBA can use following command to remove
list of tables related to every tablespace.
SQL> Purge DBA_RECYCLEBIN;
This is not all. Using Flashback Table <> to
Drop we can flashback table to particular time or a scn.
FLASHBACK TABLE T1 TO
SCN 285954520;
Renaming is also possible.
FLASHBACK TABLE TEST TO
BEFORE DROP RENAME TO TEST2;
FLASHBACK TABLE TEST TO BEFORE DROP RENAME TO
TEST1;
This feature has definitely saved us from lots of
trouble in situation like this.
Any comment, suggestion would be appreciated.
Sunday, 20 July 2014
Microsoft Sql Server tables and constraints Part 2
This is my third blog about Microsoft Sql Server database administration. In last blog we talked about keys in Sql Server and found that there is no difference between syntax's of defining these keys in Oracle and Sql Server.
In this blog we will discuss auto generation of key values. Just like Synonym of Oracle database. I have demonstrated this by doing T-Sql script. and you can also achive this using Studio as well.
Comments added in between each step would help you to understand concept easily.
First I have created IDNTY_TAB2 table in SAMPLE Database using SAMPLE schema and defined ID as Primary key and set it to a IDENTITY column
USE [Sample]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [sample].[Idnty_Tab2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Nm] [nchar](50) NOT NULL
) ON [PRIMARY]
GO
Now! I have inserted following rows in the table. Notice that I have not provided value of ID primary Key column. As this column is IDENTITY column. So its generating values its own starting from 1.
Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')
Select and confirm that you get to see all the rows.
Select * from idnty_tab2
Now! Delete the row and query again to see the result
Delete from Idnty_tab2 where id=3
Select * from idnty_tab2
Now try to insert new row and query the table and see the value of ID column
Insert into idnty_tab2 values ('Smith')
Select * from idnty_tab2
You will notice that I has not used missing 3 as the value of ID. So to use deleted ID value 3 we need to set
IDENTITY_INSERT on table..Following statement would set this parameter ON.
Set Identity_Insert Idnty_Tab2 On;
After setting this Parameter we need to provide columns name as well. without column name INSERT statement would fail.
Insert into idnty_tab2 values ('Harris'); --Would Fail with following message.
--Msg 545, Level 16, State 1, Line 2
--Explicit value must be specified for identity column in table 'Idnty_Tab2' either when
--IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
Following statement would be successfull.
Insert into idnty_tab2 (id,nm) values (3,'Harris')
Confirm it once again
Select * from idnty_tab2 order by 1
Once you have inserted the missing values you should take this parameter OFF using follwoing parameter.
Set Identity_Insert Idnty_tab2 off;
Now start inserting the values again.
Insert into idnty_tab2 values ('James')
Select * from idnty_tab2 order by 1
Suppose we delete all the rows and again insert it.After insert when we query table and get to know that
instead from 1 ID is started from the higest value we had before we deleted all the rows. Sql Server instead of setting to 1 have started it from that value.
Delete from Idnty_Tab2
Insert into idnty_tab2 values ('James')
Select * from idnty_tab2 order by 1
Now to restart the feed from start we need to reset the seed by using following procedure
dbcc checkident('idnty_tab2',reseed,0)
Checking identity information: current identity value '9', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After using above procedure we insert values again and get to see that this time Sql Server has used 1 as starting value
Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')
Select * from idnty_tab2 order by 1
This is the behavior when we use DELETE statement, However if we use TRUNCATE statement than the counter is automatically set to its starting value 1; Lets use Truncate statement and see what happens
Truncate table Idnty_Tab2;
Select * from idnty_tab2 order by 1
Insert into idnty_tab2 values ('James')
Insert into idnty_tab2 values ('Davis')
Insert into idnty_tab2 values ('Smith')
Insert into idnty_tab2 values ('Samantha')
Insert into idnty_tab2 values ('Nancy')
Insert into idnty_tab2 values ('Deran')
Select * from idnty_tab2 order by 1
Using TRUNCATE command we have no need to reset the counter. It happens with DELETE Statement only
I hope this would clear any doubt.
If have any doubt please write back to me.Any suggestion/comments would be appriciated.
Thanks
Friday, 11 July 2014
Microsoft Sql Server table and constraints Part 1
This is first part of a series of blog discussing tables,constraints in Microsoft Sql Server database. I have
created on database to store person information. For this I have used MSS 2008R2 version.
Drop table sample.gender
Create table sample.gender
(
id int primary key,
gender nvarchar(10) not null
)
Insert into Sample.gender values (1,'Male')
Insert into Sample.gender values (2,'Female')
Insert into sample.gender values (3,'Not Sure')
select * from sample.Gender
Drop table sample.person
Create table sample.person
(
id int primary key,
Name nvarchar(30) Not null,
LastName nvarchar(30) Not null,
Email nvarchar(15) not null,
Nationality nvarchar(20) not null,
gender int not null
)
Alter table sample.person add constraint fk_con foreign key (gender) references sample.gender (id)
truncate table sample.person
Insert into sample.person values (1,'Jai','Sharma','vat@gmail.com','Indian',1)
Insert into sample.person values (2,'Ram','Jain','RJ@gmail.com','Srilankan',1)
Insert into sample.person values (3,'Hariharn','Dev','hd@gmail.com','Singapore',1)
Insert into sample.person values (4,'Anmol','Sethi','AS@gmail.com','Indian',1)
Insert into sample.person values (5,'Rajan','Chawala','RC@gmail.com','Srilankan',1)
Insert into sample.person values (6,'Anamika','Mukharji','AM@gmail.com','Bangladesh',2)
Insert into sample.person values (7,'Rupali','Sen','RS@gmail.com','Aussie',2)
Insert into sample.person(id,name,lastname,email,nationality) values (8,'Radhika','Reddy','RR@gmail.com','Aussie')
select * from sample.person
--Adding a default constraint
Alter table Sample.Person
add constraint dflt_tblPerson_gender default 3 for gender
---Adding an column in table
Alter table sample.person
add city nvarchar(20)
constraint dflt_person_city default 'Melbourne'
---Drop a column from table
Alter table sample.person
drop column city
----Constraint cascade
---Check constraint
Alter table sample.person
add constraint chk_person_aga check (age > 0 and age <100)
Any comment,suggestions or correction is welcome.
created on database to store person information. For this I have used MSS 2008R2 version.
Drop table sample.gender
Create table sample.gender
(
id int primary key,
gender nvarchar(10) not null
)
Insert into Sample.gender values (1,'Male')
Insert into Sample.gender values (2,'Female')
Insert into sample.gender values (3,'Not Sure')
select * from sample.Gender
Drop table sample.person
Create table sample.person
(
id int primary key,
Name nvarchar(30) Not null,
LastName nvarchar(30) Not null,
Email nvarchar(15) not null,
Nationality nvarchar(20) not null,
gender int not null
)
Alter table sample.person add constraint fk_con foreign key (gender) references sample.gender (id)
truncate table sample.person
Insert into sample.person values (1,'Jai','Sharma','vat@gmail.com','Indian',1)
Insert into sample.person values (2,'Ram','Jain','RJ@gmail.com','Srilankan',1)
Insert into sample.person values (3,'Hariharn','Dev','hd@gmail.com','Singapore',1)
Insert into sample.person values (4,'Anmol','Sethi','AS@gmail.com','Indian',1)
Insert into sample.person values (5,'Rajan','Chawala','RC@gmail.com','Srilankan',1)
Insert into sample.person values (6,'Anamika','Mukharji','AM@gmail.com','Bangladesh',2)
Insert into sample.person values (7,'Rupali','Sen','RS@gmail.com','Aussie',2)
Insert into sample.person(id,name,lastname,email,nationality) values (8,'Radhika','Reddy','RR@gmail.com','Aussie')
select * from sample.person
--Adding a default constraint
Alter table Sample.Person
add constraint dflt_tblPerson_gender default 3 for gender
---Adding an column in table
Alter table sample.person
add city nvarchar(20)
constraint dflt_person_city default 'Melbourne'
---Drop a column from table
Alter table sample.person
drop column city
----Constraint cascade
---Check constraint
Alter table sample.person
add constraint chk_person_aga check (age > 0 and age <100)
Any comment,suggestions or correction is welcome.
Some RnD in Microsoft Sql Server 2008R2
Many new Sql Server DBAs would need to do their RnD with the MSS database. In previous blog I have given few options available while creating database.
In this blog we would discuss more operations once the database is created.
1:- Rename a database
To rename the database, we have GUI and command line. First we discuss the GUI method which is really simple.
Using Management Studio (GUI), Select the database you want to rename and in option select
"RENAME"
Another method is run query in query area.
Alter database <nm> modify name = sample3
Third method is to use sp_renameDB procedure to achive same.
Execute sp_renameDB 'oldnm','newnm'
2:- Drop a database
Before we proceed with dropping the database. Following point should be kept in mind otherwise
our DROP statement would fail.
Database should be running in sigle user mode. No other user should be connected to it otherwise
database would not be dropped.
Once database is running in Single user mode. We have following options to drop it.
1:- Using management studio, select the database you want to drop. Right click on it and select delete
option.
2:- On Query window write following command while connected to MASTER or any other database but the database
being dropped.
Drop database '<Name of the Database>';
Note:- If users are connected with database being dropped than run following command to bring it in single user
mode.
Alter Database <Name of the database> Set single_user with rollback immediate;
Drop database <Name of the database>;
We cannot system databases.
Any correction,suggestion are welcome.
In this blog we would discuss more operations once the database is created.
1:- Rename a database
To rename the database, we have GUI and command line. First we discuss the GUI method which is really simple.
Using Management Studio (GUI), Select the database you want to rename and in option select
"RENAME"
Another method is run query in query area.
Alter database <nm> modify name = sample3
Third method is to use sp_renameDB procedure to achive same.
Execute sp_renameDB 'oldnm','newnm'
2:- Drop a database
Before we proceed with dropping the database. Following point should be kept in mind otherwise
our DROP statement would fail.
Database should be running in sigle user mode. No other user should be connected to it otherwise
database would not be dropped.
Once database is running in Single user mode. We have following options to drop it.
1:- Using management studio, select the database you want to drop. Right click on it and select delete
option.
2:- On Query window write following command while connected to MASTER or any other database but the database
being dropped.
Drop database '<Name of the Database>';
Note:- If users are connected with database being dropped than run following command to bring it in single user
mode.
Alter Database <Name of the database> Set single_user with rollback immediate;
Drop database <Name of the database>;
We cannot system databases.
Any correction,suggestion are welcome.
Wednesday, 9 July 2014
Create Database in Micrsoft Sql Server 2008R2
In this blog we will talk about the way we can create database in Microsoft Sql Server. Mostly new Sql server DBA create database by right clicking on database tab and select create database statement. It takes time for them to know different
options available while creating database.
I have complied all the possible options avaible to consider while creating database. Have a look:
DROP DATABASE SALES;
--++++++++++++++++++++++++++++++++++++++++++++++
Create database sales on
(
name=sales_dat,
FILENAME='C:\SqlServerDbFiles\sales\sales.mdf',
size = 10,
maxsize=20,
filegrowth=5
)
Log on
(
name=sales_log,
FILENAME='C:\SqlServerDbFiles\sales\sales.log',
size=10,
maxsize=20,
FILEGROWTH = 5
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++
drop database archive;
Create Database Archive on
Primary
( name = Arch1,
filename='C:\SqlServerDbFiles\sales\archiv1.mdf',
size = 2mb,
maxsize = 5Mb,
filegrowth = 2mb
),
(name = Arch2,
filename='C:\SqlServerDbFiles\sales\archiv2.ndf',
size = 2mb,
maxsize=3mb,
filegrowth=3mb
)
Log On
(name = arch_log1,
filename='C:\SqlServerDbFiles\sales\archiv_log1.ldf',
size = 2mb,
maxsize = 5mb,
filegrowth = 2mb
),
(name=arch_log2,
filename='C:\SqlServerDbFiles\sales\archiv_log2.ldf',
size = 2Mb,
MaxSize = 3Mb,
FileGrowth = 2Mb
)
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Drop Database Sales;
Create Database Sales on
primary
(
name=Spril1_dat,
fileName='C:\SqlServerDbFiles\sales\Spril1dat.mdf',
size = 2MB,
MaxSize=5MB,
FileGrowth = 2%
),
(
Name=Spril2_dat,
FileName='C:\SqlServerDbFiles\sales\Spril2dat.ndf',
size = 3MB,
Maxsize = 6Mb,
FileGrowth=3
),
FileGroup SalesGroup1
(
Name=SgGrpFi1_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrpFi1dat.ndf',
Size = 3MB,
MaxSize = 6MB,
FileGrowth = 2
),
(
Name=SgGrpFi2_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrpFi2dat.ndf',
Size = 3MB,
MaxSize = 6MB,
FileGrowth = 2
),
FileGroup SalesGroup2
(
name = SG2Fil1_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi1dat.ndf',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
),
(
name = SG2Fil2_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi2dat.ndf',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
)
Log ON
(
name = Sales_Log,
FileName='C:\SqlServerDbFiles\sales\Sales_Log.log',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--attaching a database
Create Database Archive
on (Filename='C:\SqlServerDbFiles\archive\Archiv1.mdf')
for Attach
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--SnapShot Database
Create Database ArchiveSnpshot On
(NAME=Spri1_dat,filename='C:\SqlServerDbFiles\archive\Archiv1.mdf'),
(name=Spri2_dat,FileName='C:\SqlServerDbFiles\archive\Archiv2.mdf'),
(Name=spri3_dat,FileName='C:\SqlServerDbFiles\archive\Archiv1.mdf')
As SnapShot Of Sales)
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE DATABASE Sales_Colla
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'Sales_Colla';
GO
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE DATABASE AdventureWorks ON
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf'),
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf'),
(FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+Specifies row file group and FileStream
CREATE DATABASE FileStreamDB
ON PRIMARY
(
NAME = FileStreamDB_data
,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_data.mdf'
,SIZE = 10MB
,MAXSIZE = 50MB
,FILEGROWTH = 15%
),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
(
NAME = FSPhotos
,FILENAME = 'C:\MyFSfolder\Photos'
-- SIZE, MAXSIZE, FILEGROWTH should not be specified here.
-- If they are specified an error will be raised.
),
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
(
NAME = FileStreamResumes
,FILENAME = 'C:\MyFSfolder\Resumes'
)
LOG ON
(
NAME = FileStream_log
,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_log.ldf'
,SIZE = 5MB
,MAXSIZE = 25MB
,FILEGROWTH = 5MB
)
Any information,comment,suggestion would be welcome.
thanks
options available while creating database.
I have complied all the possible options avaible to consider while creating database. Have a look:
DROP DATABASE SALES;
--++++++++++++++++++++++++++++++++++++++++++++++
Create database sales on
(
name=sales_dat,
FILENAME='C:\SqlServerDbFiles\sales\sales.mdf',
size = 10,
maxsize=20,
filegrowth=5
)
Log on
(
name=sales_log,
FILENAME='C:\SqlServerDbFiles\sales\sales.log',
size=10,
maxsize=20,
FILEGROWTH = 5
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++
drop database archive;
Create Database Archive on
Primary
( name = Arch1,
filename='C:\SqlServerDbFiles\sales\archiv1.mdf',
size = 2mb,
maxsize = 5Mb,
filegrowth = 2mb
),
(name = Arch2,
filename='C:\SqlServerDbFiles\sales\archiv2.ndf',
size = 2mb,
maxsize=3mb,
filegrowth=3mb
)
Log On
(name = arch_log1,
filename='C:\SqlServerDbFiles\sales\archiv_log1.ldf',
size = 2mb,
maxsize = 5mb,
filegrowth = 2mb
),
(name=arch_log2,
filename='C:\SqlServerDbFiles\sales\archiv_log2.ldf',
size = 2Mb,
MaxSize = 3Mb,
FileGrowth = 2Mb
)
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Drop Database Sales;
Create Database Sales on
primary
(
name=Spril1_dat,
fileName='C:\SqlServerDbFiles\sales\Spril1dat.mdf',
size = 2MB,
MaxSize=5MB,
FileGrowth = 2%
),
(
Name=Spril2_dat,
FileName='C:\SqlServerDbFiles\sales\Spril2dat.ndf',
size = 3MB,
Maxsize = 6Mb,
FileGrowth=3
),
FileGroup SalesGroup1
(
Name=SgGrpFi1_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrpFi1dat.ndf',
Size = 3MB,
MaxSize = 6MB,
FileGrowth = 2
),
(
Name=SgGrpFi2_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrpFi2dat.ndf',
Size = 3MB,
MaxSize = 6MB,
FileGrowth = 2
),
FileGroup SalesGroup2
(
name = SG2Fil1_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi1dat.ndf',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
),
(
name = SG2Fil2_dat,
FileName='C:\SqlServerDbFiles\sales\SgGrp2Fi2dat.ndf',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
)
Log ON
(
name = Sales_Log,
FileName='C:\SqlServerDbFiles\sales\Sales_Log.log',
Size = 4MB,
MaxSize = 8MB,
FileGrowth = 3%
)
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--attaching a database
Create Database Archive
on (Filename='C:\SqlServerDbFiles\archive\Archiv1.mdf')
for Attach
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--SnapShot Database
Create Database ArchiveSnpshot On
(NAME=Spri1_dat,filename='C:\SqlServerDbFiles\archive\Archiv1.mdf'),
(name=Spri2_dat,FileName='C:\SqlServerDbFiles\archive\Archiv2.mdf'),
(Name=spri3_dat,FileName='C:\SqlServerDbFiles\archive\Archiv1.mdf')
As SnapShot Of Sales)
---+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE DATABASE Sales_Colla
COLLATE French_CI_AI
WITH TRUSTWORTHY ON, DB_CHAINING ON;
GO
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'Sales_Colla';
GO
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE DATABASE AdventureWorks ON
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf'),
(FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks_log.ldf'),
(FILENAME = 'c:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--+Specifies row file group and FileStream
CREATE DATABASE FileStreamDB
ON PRIMARY
(
NAME = FileStreamDB_data
,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_data.mdf'
,SIZE = 10MB
,MAXSIZE = 50MB
,FILEGROWTH = 15%
),
FILEGROUP FileStreamPhotos CONTAINS FILESTREAM DEFAULT
(
NAME = FSPhotos
,FILENAME = 'C:\MyFSfolder\Photos'
-- SIZE, MAXSIZE, FILEGROWTH should not be specified here.
-- If they are specified an error will be raised.
),
FILEGROUP FileStreamResumes CONTAINS FILESTREAM
(
NAME = FileStreamResumes
,FILENAME = 'C:\MyFSfolder\Resumes'
)
LOG ON
(
NAME = FileStream_log
,FILENAME = 'C:\SqlServerDbFiles\archive\FileStreamDB_log.ldf'
,SIZE = 5MB
,MAXSIZE = 25MB
,FILEGROWTH = 5MB
)
Any information,comment,suggestion would be welcome.
thanks
Saturday, 5 July 2014
Char Varchar2 test
A simple Char versus Varchar2 comparison.
Create table test_char(abcb char(1),abcv varchar2(1));
Insert into hr.test_char values('a','b');
Commit;
SQL> Select * from hr.test_char where abcb='a';
A A
- -
a b
SQL> select * from hr.test_char where abcb='a ';
A A
- -
a b
SQL> Select * from hr.test_char where abcv='b';
A A
- -
a b
SQL> Select * from hr.test_char where abcv='b ';
no rows selected
Now! Lets dump the columns and see what we get.
SQL> Select dump(abcb)abcb,dump(abcv)abcv from hr.test_char;
ABCB ABCV
--------------------------------------------------------------------------------
Typ=96 Len=1: 97 Typ=1 Len=1: 98
Oracle Kernal considers CHAR datatype as fixed length. Any extra ' ' space added in
WHERE clause is trimmed.
However with VARCHAR2 this is not true. Varchar2 datatype is a variable length data type.
So when we add space in ABCV column Oracle tries to find equivalent value in table and return "No Rows Selected" message
because it has not found any row matching the values 'b '.
SQL> select * from hr.test_char where abcb='a '; <-- ABCB is fixed length Char datatype. Oracle kernal implicitly does TRIMMING of extra
<-- space added in WHERE clause.
A A
- -
a b
SQL> Select * from hr.test_char where abcv = Trim('b '); <-- Now if we use TRIM function than we get desired result.
A A
- -
a b
SQL> Select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Any comment/suggestion would be appriciated.
Thanks
Create table test_char(abcb char(1),abcv varchar2(1));
Insert into hr.test_char values('a','b');
Commit;
SQL> Select * from hr.test_char where abcb='a';
A A
- -
a b
SQL> select * from hr.test_char where abcb='a ';
A A
- -
a b
SQL> Select * from hr.test_char where abcv='b';
A A
- -
a b
SQL> Select * from hr.test_char where abcv='b ';
no rows selected
Now! Lets dump the columns and see what we get.
SQL> Select dump(abcb)abcb,dump(abcv)abcv from hr.test_char;
ABCB ABCV
--------------------------------------------------------------------------------
Typ=96 Len=1: 97 Typ=1 Len=1: 98
Oracle Kernal considers CHAR datatype as fixed length. Any extra ' ' space added in
WHERE clause is trimmed.
However with VARCHAR2 this is not true. Varchar2 datatype is a variable length data type.
So when we add space in ABCV column Oracle tries to find equivalent value in table and return "No Rows Selected" message
because it has not found any row matching the values 'b '.
SQL> select * from hr.test_char where abcb='a '; <-- ABCB is fixed length Char datatype. Oracle kernal implicitly does TRIMMING of extra
<-- space added in WHERE clause.
A A
- -
a b
SQL> Select * from hr.test_char where abcv = Trim('b '); <-- Now if we use TRIM function than we get desired result.
A A
- -
a b
SQL> Select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Any comment/suggestion would be appriciated.
Thanks
Monday, 23 June 2014
DNS Server setup for SCAN IP using Oracle 11gR2
In this blog, We will discuss how to setup DNS server on Linux for Oracle 11g R2 RAC setup.
With Oracle 11gR2 where we need to provide SCAN IP we need
to configure either DNS or GNS. Giving SCAN
ips on host file does not work.
Instead of any other domain, I will be using LOCALDOMAIN.
Following is our environment to demonstrate DNS server
setup.
Linux
rac1.localdomain 2.6.39-300.26.1.el5uek #1 SMP Thu Jan 3 18:33:10 PST 2013 i686
i686 i386 GNU/Linux
Install following packages on
bind-9.3.6-20.P1.el5_8.5.i386.rpm
bind97-utils-9.7.0-17.P2.el5.i386.rpm
bind-utils-9.3.6-20.P1.el5_8.5.i386.rpm
bind97-chroot-9.7.0-17.P2.el5.i386.rpm
These RPM’s install and create files related to
bind/DNS server that we use.
Go to /etc directory and run following command.
[root@rac1 etc]# ls -tlr named*
lrwxrwxrwx
1 root named 41 Jun 22 10:56 named.rfc1912.zones ->
/var/named/chroot/etc/named.rfc1912.zones
lrwxrwxrwx
1 root named 51 Jun 22 10:56 named.caching-nameserver.conf ->
/var/named/chroot/etc/named.caching-nameserver.conf
and now copy “Named.Caching-nameserver.conf” file
into as /etc/named.conf
[root@rac1 etc]# cp
/var/named/chroot/etc/named.caching-nameserver.conf named.conf
ls -ltr named*
lrwxrwxrwx
1 root named 41 Jun 22 10:56
named.rfc1912.zones -> /var/named/chroot/etc/named.rfc1912.zones
lrwxrwxrwx
1 root named 51 Jun 22 10:56
named.caching-nameserver.conf ->
/var/named/chroot/etc/named.caching-nameserver.conf
-rw-r-----
1 root root 1230 Jun 22 11:09 named.conf
Now! Edit the named.conf file. The final
named.conf file should look like following.
options {
listen-on port 53 { 127.0.0.1; ***.***.**.***; }; ßIP
of your DNS server
listen-on-v6 port 53 { ::1; };
directory "/var/named";
dump-file
"/var/named/data/cache_dump.db";
statistics-file "/var/named/data/named_stats.txt";
memstatistics-file "/var/named/data/named_mem_stats.txt";
//
Those options should be used carefully because they disable port
//
randomization
//
query-source port 53;
//
query-source-v6 port 53;
allow-query { localhost; };
allow-query-cache { localhost; };
};
logging {
channel default_debug {
file "data/named.run";
severity dynamic;
};
};
view localhost_resolver {
match-clients { localhost; };
match-destinations { localhost; };
recursion yes;
include "/etc/named.rfc1912.zones";
};
/*NOTE I
have added following two sections in named.conf file. One for localdomain and one for reverse lookup . You can provide
something like 0.168.192 or any other that matched your network*/
zone "localdomain" IN {
type master;
file "localdomain.zone";
allow-update { none; };
};
zone "***.***.***.in-addr.arpa." IN {
type master;
file "0.168.192.in-addr.arpa.";
allow-update { name; };
};
Now! Edit /var/named/localdomain.zone or if you
are creating it for your own domain zone than better is to copy and rename
localdomain.zone file. Eg zbc.com and make appropriate changes.
Final file should look like following:
[root@rac1 etc]# cat /var/named/localdomain.zone
$TTL
86400
@
IN SOA localdomain root (
42 ; serial (d. adams)
3H ; refresh
15M ; retry
1W ; expiry
1D
) ; minimum
IN NS localdomain
localdomain
IN A 127.0.0.1
rac1-vip IN A **.**.**.221
rac2-vip IN A **.**.**.222
rac-scan IN A **.**.**.11
rac-scan IN A **.**.**.12
rac-scan IN A **.**.**.13
Now create a file for reverse lookup. Change the
name of your file as per your requirement. In our case we will be creating
0.**.**.in-addr.arpa.
The content of file should look like following:
$ORIGIN 0.168.192.in-addr.arpa.
$TTL 1H
@ IN SOA localdomain. root.localdomain. ( 2
3H
1H
1W
1H )
0.168.192.in-addr.arpa. IN NS localdomain.
221 IN PTR rac1-vip.localdomain.
222 IN PTR rac2-vip.localdomain.
11 IN PTR rac-scan.localdomain.
12 IN PTR rac-scan.localdomain.
13 IN PTR rac-scan.localdomain.
Now! Stop and restart named service as follows
[root@rac1 etc]# service named stop
Stopping named:
[ OK ]
[root@rac1 etc]# service named start
Starting named: [
OK ]
[root@rac1 etc]# chkconfig named on
Now! Modify
/etc/resolv.conf file and final file should look like following:
[root@rac1 etc]# cat /etc/resolv.conf
# generated by /sbin/dhclient-script
nameserver 127.0.0.1
search localdomain
Further! Change /etc/nsswitch.conf and following
in file.
hosts: dns files nis
restart NSCD service
using following command.
/sbin/service nscd
restart
Now run following
command to check if DNS working or not. As you can see its working.
[root@rac1 etc]#
nslookup rac-scan.localdomain
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac-scan.localdomain
Address: 192.168.0.13
Name: rac-scan.localdomain
Address: 192.168.0.11
Name: rac-scan.localdomain
Address: 192.168.0.12
[root@rac1 etc]#
nslookup rac1-vip.localdomain
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac1-vip.localdomain
Address: 192.168.0.221
[root@rac1 etc]#
nslookup rac2-vip.localdomain
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac2-vip.localdomain
Address: 192.168.0.222
Subscribe to:
Posts (Atom)