Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Quick oracle10g Database Copying/Cloning from Win2003 to Win2008

Posted by Hendry chinnapparaj on September 21, 2012

QUICK DATABASE COPYING / CLONING

Platform: from Windows 2003 to Windows 2008
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 used to quickly migrate a system from one windows 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 Windows 2003  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 “TEST77” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘F:\ORACLE\ORADATA\TEST77\REDO01.LOG’  SIZE 50M,

GROUP 2 ‘F:\ORACLE\ORADATA\TEST77\REDO02.LOG’  SIZE 50M,

GROUP 3 ‘F:\ORACLE\ORADATA\TEST77\REDO03.LOG’  SIZE 50M

— STANDBY LOGFILE

DATAFILE

‘F:\ORACLE\ORADATA\TEST77\SYSTEM01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\UNDOTBS01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\SYSAUX01.DBF’,

‘F:\ORACLE\ORADATA\TEST77\USERS01.DBF’

CHARACTER SET WE8MSWIN1252

;

Customize the create controlfile script as below and save as cc.sql

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “TEST77” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO01.LOG’  SIZE 50M,

GROUP 2 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO02.LOG’  SIZE 50M,

GROUP 3 ‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\REDO03.LOG’  SIZE 50M

— STANDBY LOGFILE

DATAFILE

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

CHARACTER SET WE8MSWIN1252

;

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.

–          Do not copy controlfiles and redolog files

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 “TEST77” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “TEST77” 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 cc.sql.

Old:

DATAFILE
‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

New:

DATAFILE
‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSTEM01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\UNDOTBS01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\SYSAUX01.DBF’,

‘E:\oracle\product\10.2.0\db_1\oradata\TEST77\USERS01.DBF’

STEP 7: Create the bdump, udump and cdump and FRA directories

cd E:\oracle\product\10.2.0\db_1\admin
mkdir TEST77
cd TEST77
mkdir bdump
mkdir udump
mkdir cdump
mkdir adump

cd E:\oracle\product\10.2.0\db_1\oradata\TEST77

mkdir oraflash

cd oraflash

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

INITtest77.ORA

STEP 9: Create new Instance in Windows 2008 Server

E:\oracle\product\10.2.0\db_1\database>oradim -NEW -SID TEST77 -SYSPWD sys -STARTMODE manual -PFILE INITtest77.ORA

Instance created.

This will  also create the password file PWDTEST77.ORA

 

STEP 9:  Create the Controlfile

E:\oracle\product\10.2.0\db_1\database>set oracle_sid=test77

E:\oracle\product\10.2.0\db_1\database>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Sep 21 01:34:17 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @cc.sql

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2163680 bytes

Variable Size             136117280 bytes

Database Buffers          171966464 bytes

Redo Buffers                4325376 bytes

Control file created.

SQL> select open_mode from v$database;

OPEN_MODE

———-

MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      TEST77

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

STEP 10: Create spfile

SQL> create spfile from pfile;

File created.

STEP 11: Shutdown and startup with Spfile

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2163680 bytes

Variable Size             136117280 bytes

Database Buffers          171966464 bytes

Redo Buffers                4325376 bytes

Database mounted.

Database opened.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      TEST77

 

STEP 11: Change the Archive destination

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

SQL> alter system set log_archive_dest_1=’location=E:\oracle\product\10.2.0\db_1\oradata\TEST77\arch’ scope=both;

System altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            E:\oracle\product\10.2.0\db_1\oradata\TEST77\arch

Oldest online log sequence     1

Next log sequence to archive   1

Current log sequence           1

STEP 12: create temp datafile

ALTER TABLESPACE TEMP ADD TEMPFILE E:\ORACLE\PRODUCT\10.2.0\DB_1\ORADATA\TEST77\TEMP01.DBF’

SIZE 2G  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 5G;

Enjoy !

Shalom

Hendry

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: