Super fast Database Copying/Cloning

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another.  It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 2

MAXDATAFILES 240

MAXINSTANCES 1

MAXLOGHISTORY 113

LOGFILE

GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',

'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,

GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',

'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M

DATAFILE

'/u01/oradata/oldlsq/system01.dbf',

'/u01/oradata/oldlsq/mydatabase.dbf'

;

# Recovery is required if any of the datafiles are restored

# backups, or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN; 

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq

rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq

rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq 

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored

# backups, or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# Database can now be opened normally.

ALTER DATABASE OPEN; 

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE

'/u01/oradata/oldlsq/system01.dbf',

'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE

'/u01/oradata/newlsq/system01.dbf',

'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin

mkdir newlsq

cd newlsq

mkdir bdump

mkdir udump

mkdir cdump

mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

Generate Data Dictionary from SQL Server

Introduction  

This tip shows how to find out table description and dependency with other tables.

Using the code

In any SQL Server database (any version of SQL Server) just create a Stored Procedure [spGenerateDBDictionary] that is given as attachment, and exec [spGenerateDBDictionary]. Then the procedure will return tables information with table name, data, attributes, data types, IsNullable info, primary key, foreign key constraints, any reference with another table, and the details description, but remember that the description will come from the description field where you give any description at the time of table creation.

CREATE proc [dbo].[spGenerateDBDictionary]
AS
BEGIN
select a.name [Table],b.name [Attribute],c.name [DataType],b.isnullable [Allow Nulls?],CASE WHEN
d.name is null THEN 0 ELSE 1 END [PKey?],
CASE WHEN e.parent_object_id is null THEN 0 ELSE 1 END [FKey?],CASE WHEN e.parent_object_id
is null THEN '-' ELSE g.name  END [Ref Table],
CASE WHEN h.value is null THEN '-' ELSE h.value END [Description]
from sysobjects as a
join syscolumns as b on a.id = b.id
join systypes as c on b.xtype = c.xtype
left join (SELECT  so.id,sc.colid,sc.name
     FROM    syscolumns sc
     JOIN sysobjects so ON so.id = sc.id
     JOIN sysindexkeys si ON so.id = si.id
                   and sc.colid = si.colid
     WHERE si.indid = 1) d on a.id = d.id and b.colid = d.colid
left join sys.foreign_key_columns as e on a.id = e.parent_object_id and b.colid = e.parent_column_id    
left join sys.objects as g on e.referenced_object_id = g.object_id  
left join sys.extended_properties as h on a.id = h.major_id and b.colid = h.minor_id
where a.type = 'U' order by a.name
END

How to format datetime & date in Sql Server

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
                                      – Oct  2 2008 11:01AM          
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008                  
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                      – Oct  2 2008 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
                                      – 02 Oct 2008 11:02:07:577     
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
                                      – 2008-10-02T10:52:47.513
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 – yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          – mon yyyy