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

Clone Oracle9i Database on same Windows Server

Posted by Hendry chinnapparaj on May 10, 2011

Summary :-

                      
The following steps must be on Source -Production instance/Server

Step 1. Shutdown database in normal mode and start it up in restricted mode.

                        Step 2. Take the backup of control file

                        Step 3. Shutdown database again  in normal mode.                    

Step 4. Copy init parameter file ,control file script  and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.

                        The following step must be on destination – Clone instance/server

                        Step 5. Edit init parameter file and control file script.

                        Step 6.  New Environment setup .

                        Step 7. Connect with sqlplus and recreate control file

                        Step 8. Open the database in resetlogs.

                        Step 9. Shutdown the database in normal mode

                        Step 10. Take the cold backup and start the database in archive/non archive mode.

Details :-


Step 1 – 3 on the Source Server ( Server A)

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> startup restrict

ORACLE instance started.

Total System Global Area                         57124108 bytes

Fixed Size                                          70924 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Database mounted.

Database opened.

SVRMGR> alter database backup controlfile to trace;

Statement processed.

SVRMGR>

SVRMGR> show parameter user_dump_dest

NAME                                TYPE    VALUE

———————————– ——- ————————-

user_dump_dest                      string E:\ora816\admin\ora816\udump

In Windows

D:\oracle\admin\ora816\udump

Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora<xxxxx>.trc .  Rename this file to Ctrl.sql

 

SVRMGR> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 4: Copy all the parameter files (initsid.ora, configsid.ora ),  Control files,  Ctrlprod.sql ( created as above) and all data files  to clone server . It is good to follow the OFA. If you are  cloning a  database on the same machine then  create a different directory structure ( as OFA) for cloning instance and copied all the required files of primary instance here.

 

    Step 4a : On Unix  ( Let us say your cloning instance name is clone)

If you had followed OFA , your initsid.ora  parameters files would be in  $ORACLE_BASE/admin/clone/pfile/initclone.ora

and configsid.ora would be in  $ORACLE_BASE/admin/clone/pfile/configclone.ora.

Create a symbolic link

Change the working directory to $ORACLE_HOME/dbs and create a symbolic link

$ ln –s $ORACLE_BASE/admin/clone/pfile/initclone.ora  initclone.ora

 

Step 5 : On destination server

Change the following parameter in the initsid.ora of the cloning instance/server

 

InitSid.ora (initora816.ora) of the Source/production server.
InitSid.ora (initclone.ora) of the

destination/clone server.

db_name = “ora816”instance_name = ora816

service_names = ora816

control_files = (“e:\ora816\oradata\ora816\control01.ctl”, “f:\ora816\oradata\ora816\control02.ctl”, “g:\ora816\oradata\ora816\control03.ctl”)

Db_name= “clone”Instance_name= clone

Service_name = clone

Control_file = (“C:\clone\control01.ctl”,”D:\clone\control02.ctl”)

The other parameter which is required to change is user_dump_dest, background_dump_dest,log_archive_dest

 

 

Oraxxxx.trc is copied from source/production instance to destination/clone instance and renamed it as ctrlclone.sql .

 

 Edit ctrlclone.sql as follows.

 

Oraxxxx.trc in user dump dest of Source/Production instance Ctrlclone.sql
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “ORA816” NORESETLOGS ARCHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘E:\ORA816\ORADATA\ORA816\REDO03.LOG’  SIZE 1M,

GROUP 2 ‘E:\ORA816\ORADATA\ORA816\REDO02.LOG’  SIZE 1M,

GROUP 3 ‘E:\ORA816\ORADATA\ORA816\REDO01.LOG’  SIZE 1M

DATAFILE

‘E:\ORA816\ORADATA\ORA816\SYSTEM01.DBF’,

‘E:\ORA816\ORADATA\ORA816\RBS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TEMP01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TOOLS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\INDX01.DBF’,

‘E:\ORA816\ORADATA\ORA816\DR01.DBF’,

‘E:\ORA816\DATABASE\NGAR5ORA81601.DBF’,

‘E:\ORA816\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

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

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

ALTER DATABASE OPEN;

/* Delete everything upto startup nomount */STARTUP NOMOUNT pfile=f:\clone\initclone.ora

CREATE CONTROLFILE set DATABASE “CLONE” resetlogs noarchivelog

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘F:\CLONE\ORA816\REDO03.LOG’

SIZE 1M,

GROUP 2 ‘F:\CLONE\ORA816\REDO02.LOG’

SIZE 1M,

GROUP 3 ‘F:\CLONE\ORA816\REDO01.LOG’

SIZE 1M

DATAFILE

‘F:\CLONE\ORA816\SYSTEM01.DBF’,

‘F:\CLONE\ORA816\RBS01.DBF’,

‘F:\CLONE\ORA816\TEMP01.DBF’,

‘F:\CLONE\ORA816\TOOLS01.DBF’,

‘F:\CLONE\ORA816\INDX01.DBF’,

‘F:\CLONE\ORA816\DR01.DBF’,    ‘F:\CLONE\DATABASE\NGAR5ORA81601.DBF’,

‘F:\CLONE\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

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

#RECOVER DATABASE

# All logs need archiving and a log switch is needed.

#ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

#ALTER DATABASE OPEN;

STEP 6 :-

 

ON NT :- You have to create a windows NT services as follows.

 

C:\>oradim -new -sid clone -srvc oracleserviceClone -intpwd oracle -startmode auto -pfile f:\clone\initclone.ora

 

ON Unix :-

         

(A)   (A)    Change the working directory to /etc and edit the oratab file  to put the entry for CLOBE instance.

 (B)   Setup login profile for the Oracle user having dba group.

                        ORACLE_SID=CLONE

                         Export ORACLE_SID

         Or  .oraenv

               

           

 

 

STEP 7 :- Create controlfile as follows :-
 
Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to
   exclusive or shared.  If this is set, then a valid passwordfile

   should exist in ORACLE_HOME/dbs or created using orapwd  as

 

orapwd file=d:\oracle\ora92\database\pwdclone.ora password=oracle entries=1 
 

  sqlplus /nolog

connect sys/** as sysdba

or

SVRMGR> @f:\clone\ctrlclone.sql

ORACLE instance started.

Total System Global Area                         57123804 bytes

Fixed Size                                          70620 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Statement processed.

D:\ORACLE\ORA92\DATABASE

STEP 8 :- Open the database in resetlog mode as follows.

SVRMGR> alter database open resetlogs;

Statement processed.

STEP 9 :- Shutdown the database in Normal mode

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP 10 :- Take the cold backup and start the database in archive/non archive mode.

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: