Duplicating Databases / Restoring Database to New Host using RMAN.

The duplicating  of the database has gotten lot more easier with RMAN.

Assume we have copied the backup sets / backup control file / spfile  into "C:\FlashRecoveryArea" in the target server Or access to the backup sets / backup control file / spfile in the network share , the process is straight forward.   

Of course , there is little bit of leg work to be done before we issue RMAN's  duplicate database . 

Let us look at the steps below.


Step1 : Create a PFILE


In the pfile , we just the db name to "DUP2" ( this is going to be the name of the duplicated database".

C:>echo db_name='DUP2' > C:\app\mohideen\product\11.2.0\dbhome_1\database\INITDUP2.ORA

Step2:  Create Oracle Instance using ORADIM

Being the duplication is done in Windows environment , we would need to create the service using oradim utility.

C:>oradim -new -sid dup2 -intpwd Orapwd$dup2
Instance created.

Step3: Create the directories to hold the datafiles , logfiles , archived redo logs , ....

C:>mkdir  C:\app\mohideen\oradata\dup2\CONTROLFILE\
C:>mkdir C:\app\mohideen\oradata\DUP2\DATAFILE\
C:>mkdir C:\app\mohideen\oradata\DUP2\LOGFILE\
C:>mkdir C:\app\mohideen\oradata\dup2\ARCHIVELOG1\
C:>mkdir C:\app\mohideen\oradata\dup2\ARCHIVELOG2\
C:>mkdir C:\app\mohideen\FRA\dup2

Step4 : Set the SID to the destination database. 
 
C:\>set ORACLE_SID=dup2
 

 Step 5: Connect to the instance . 

As there is no database associated with this instance . it should say " Connected to an idle instance" as below.

C:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 25 10:36:03 201
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.


Step 6: Start the database in nomount state

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  313196544 bytes
Fixed Size                  2254704 bytes
Variable Size             255854736 bytes
Database Buffers           50331648 bytes
Redo Buffers                4755456 bytes
SQL>


Step 7:  Determine the location of the data files / log files from the source database.

SQL> select name   FROM v$datafile;

NAME
----------------------------------------
E:\DATA\DBPRD\SYSTEM01.DBF
E:\DATA\DBPRD\SYSAUX01.DBF
E:\DATA\DBPRD\UNDOTBS01.DBF
E:\DATA\DBPRD\USERS01.DBF
E:\DATA\DBPRD\SYSAUX02.DBF
E:\DATA\DBPRD\TBSDW.DBF

6 rows selected.

Get the log file's location .

SQL> select MEMBER  FROM v$logfile;

MEMBER
------------------------------------------------
E:\DATA\DBPRD\REDO03.LOG
E:\DATA\DBPRD\REDO02.LOG
E:\DATA\DBPRD\REDO01.LOG

These locations ( e:\data\dbprd)  need to be mentioned in the RCV File created in  the next step.

Step 8: Prepare the RCV file for RMAN to duplicate. 

Prepare the RCV that contains  the location for the  Control files  , flash recovery area , listener name , ADR location . Also , the file should contain the new locations for the data files / log files .

Finally , we need to provide the location where backups are stored ( c:\fastrecoveryarea ) in this case.


C:\dup_db>type dup.rcv
run
{
duplicate database to dup2
spfile
set control_files='c:\app\mohideen\oradata\dup2\CONTROLFILE\CONTROL1.CTL','c:\app\mohideen\oradata\dup2\CONTROLFILE\CONTROL2.CTL'
set db_file_name_convert='E:\DATA\DBPRD','c:\app\mohideen\oradata\DUP2\DATAFILE'
set log_file_name_convert='E:\DATA\DBPRD','c:\app\mohideen\oradata\DUP2\LOGFILE'
set db_recovery_file_dest='c:\app\mohideen\FRA\dup2\'
set LOG_ARCHIVE_DEST_2='location=c:\app\mohideen\oradata\dup2\ARCHIVELOG1\'
set LOG_ARCHIVE_DEST_3='location=c:\app\mohideen\oradata\dup2\ARCHIVELOG2\'
set diagnostic_dest='C:\app\mohideen'
set local_listener=''
backup location 'c:\FastRecoveryArea';


Step 9 : Issue RMAN  Duplicate Database

Here is where the rubber meets the road ; all of your leg work pays off . RMAN duplicates the database based on the RCV file . 

C:\dup_db>rman auxiliary  /  cmdfile dup.rcv  log  dup.log
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15>
C:\dup_db>


Look for any errors in the log file ( dup.log ) . You may want to look at the dup.log to see what are the steps RMAN did to perform the duplication process. 

Now you have duplicated the database .  

Hope this was clear . 

Please email me for your feedback.

Equivalent of Oracle's rownum in SQL Server.

In oracle , we use rownum to display the running number for the result set . There is no direct equivalent in SQL Server . From SQL Server 2005 , it is easy to simulate this functionality with the help of Window Functions ( SQL Server ) / Analytical Functions ( Oracle) .

ORACLE
=======

SQL> set pagesize 90
SQL> select ename , rownum from scott.emp ;

ENAME          ROWNUM
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.


SQL Server
=========

C:\Users\zahir>sqlcmd -W
1> use EXSQL2008R2
2> go
Changed database context to 'ExSQL2008R2'.
1>
2> Select top 10 name into t from master.sys.tables;
3> go

1> drop table t;
2> go

1> Select top 10 name into t from master.sys.tables;
2> go

(6 rows affected)
1> Select * from t;
2> go
name
----
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 rows affected)





We use ROW_NUMBER function to get the desired result .

1> Select name ,
2> ROW_NUMBER() over ( partition by 1 order by (Select 1)  asc) as rownum
3> from t;
4> go
name rownum
---- ------
spt_fallback_db 1
spt_fallback_dev 2
spt_fallback_usg 3
spt_monitor 4
spt_values 5
MSreplication_options 6

(6 rows affected)

Comments Welcome.

In SQL Server , Primary Key needs to be Clustered Index ?

After browsing thru variuous SQL Server discussion forums ,
I found that there is a misunderstanding in the SQL Server community , that stands out.

"Primary Key needs to be a clustered index" .

In most of the cases , it makes sense. But it need not be .

Here are the steps to create a Primary key that is not a clustered index.

a) Create a Heap table .
b) Create clustred index on non-key columns
c) Create a Primary Key on  the key columns

I have posted the following example in one of the forums.

Let me clarify with an example .

Step1 : Create a ( heap ) table called "emp"
============================================

1> create table emp 
2> ( 
3> empno integer not null , 
4> empfanme varchar(50) not null , 
5> emplanme varchar(50) not null 
6> ) ; 
7> go 

Step 2 : Verify the contents in DB Dictionary
=============================================

1> select name , object_id from sys.tables ; 
2> go 
name object_id 
---- --------- 
emp 341576255 

(1 rows affected) 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
NULL 0 HEAP 

(1 rows affected) 

The above result confirms that we just have a heap.


Step 3: Create Clustered Index on the non key columns
=====================================================
1> 
2> 
3> CREATE UNIQUE CLUSTERED INDEX [CIDX_EMP] ON [dbo].[emp] 
4> ( 
5> [emplanme] ASC 
6> ); 
7> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 

(1 rows affected) 


The above result verifies that we indeed have a clustered index.


Step 4: Create a Primary Key
============================


1> alter table emp add constraint pk_emp primary key (empno); 
2> go 

1> Select name , index_id , type_desc from sys.indexes where object_id = OBJECT_ID('emp') ; 
2> go 

name index_id type_desc 
---- -------- --------- 
CIDX_EMP 1 CLUSTERED 
pk_emp 2 NONCLUSTERED 

(2 rows affected)

Let us re-run the sql and see the contents .
Now , we see that SQL Server created non clustered index on the Primary Key , as there can be only one clustered index in a table.


Comments welcome.