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.

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

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.

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.

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

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