RMAN - Clone an Oracle database using a cold backup (same server)

Clone an Oracle database using a cold backup (same server)

This procedure will clone a database using a cold copy of the source database files.

 If a cold backup of the database is available, restore it to the new location and jump to step 2.

In this example OS – window , Source DB – DBA1 and Clone DB – COLDBA1

Identify and copy the database files With the source database started, identify all of the database's files.

The following query will display all datafiles, tempfiles and redo logs:

 Conn sys/password@DBA1 as sysdba

 set lines 100 pages 999
 col name format a50
select name, bytes from (select name, bytes from v$datafile union all select name, bytes from v$tempfile union all
select lf.member "name", l.bytes from v$logfile lf , v$log l where lf.group# = l.group#) used , (select sum(bytes) as poo from dba_free_space) free
/

Stop the source database with:

shutdown immediate;

Copy, scp or ftp the files from the source database/machine to the target.

Do not copy the control files across.

Make sure that the files have the correct permissions and ownership.

Start the source database up again

Startup

Produce a pfile for the new database

This step assumes that you are using a spfile.

If you are not, just copy the existing pfile.

create pfile='initCOLDBA1.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/database directory.

Once created, the new pfile will need to be edited.

If the cloned database is to have a new name, this will need to be changed, as will any paths.

Note.

Pay particular attention to the control locations.

Create the clone controlfile



alter database backup controlfile to trace as 'E:\Oracle11G\admin\COLDBA1\cr_COLDBA1.sql';

The file will require extensive editing before it can be used.

Using your favourite editor make the following alterations:

Remove all lines from the top of the file up to
but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

Remove any lines that start with --

Remove any lines that start with a #

Remove any blank lines in the 'CREATE CONTROLFILE' section.

Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

Move to the top of the file to the 'CREATE CONTROLFILE' line.

The word 'REUSE' needs to be changed to 'SET'.

The database name needs setting to the new database name (if it is being changed).

Decide whether the database will be put into archivelog mode or not.

If the file paths are being changed, alter the file to reflect the changes.

Here is an example :

STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "COLDBA1" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE GROUP 1 'E:\ORACLE11G\ORADATA\COLDBA1\REDO01.LOG' SIZE 50M,
GROUP 2 'E:\ORACLE11G\ORADATA\COLDBA1\REDO02.LOG' SIZE 50M,
GROUP 3 'E:\ORACLE11G\ORADATA\COLDBA1\REDO03.LOG' SIZE 50M
DATAFILE 'E:\ORACLE11G\ORADATA\COLDBA1\SYSTEM01.DBF', 'E:\ORACLE11G\ORADATA\COLDBA1\SYSAUX01.DBF', 'E:\ORACLE11G\ORADATA\COLDBA1\UNDOTBS01.DBF', 'E:\ORACLE11G\ORADATA\COLDBA1\USERS01.DBF',
'E:\ORACLE11G\ORADATA\COLDBA1\EXAMPLE01.DBF' CHARACTER SET WE8MSWIN1252 ;



ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\ORACLE11G\ORADATA\COLDBA1\TEMP01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;


Create the instance (On unix not applicable)

oradim -NEW -SID COLDBA1 -STARTMODE MANUAL -PFILE E:\Oracle11G\product\11.1.0\db_1\database\initCOLDBA1.ora 5.

Create the a password file orapwd FILE=E:\Oracle11G\product\11.1.0\db_1\database\orapwCOLDBA1.ora PASSWORD=password ENTRIES=30

6

Create the new control file(s)

Set ORACLE_SID= COLDBA1

 sqlplus /nolog SQL> conn sys/password@COLDBA1 as sysdba

Connected to an idle instance.

SQL> @E:\Oracle11G\admin\COLDBA1\cr_COLDBA1.sql

ORACLE instance started.

Total System Global Area 418484224 bytes
Fixed Size 1333592 bytes
Variable Size 306185896 bytes
Database Buffers 104857600 bytes
Redo Buffers 6107136 bytes

Control file created.

Database altered.

Tablespace altered.




It is quite common to run into problems at this stage.

Here are a couple of common errors and solutions:

ORA-01113: file 1 needs media recovery You probably forgot to stop the source database before copying the files.

Go back to step 1 and recopy the files.

ORA-01503: CREATE CONTROLFILE failed ORA-00200: controlfile could not be created ORA-00202: controlfile: '/u03/oradata/dg9a/control01.ctl' ORA-27038: skgfrcre: file exists Double check the pfile created in step 2.

Make sure the control_files setting is pointing at the correct location.

If the control_file setting is ok, make sure that the control files were not copied with the rest of the database files. If they were, delete or rename them.

7. Perform a few checks



Check that the database has opened with:

SQL> select status from v$instance;

STATUS ------------ OPEN

Make sure that the datafiles are all ok:

SQL> select distinct status from v$datafile;

STATUS ------- ONLINE SYSTEM

Take a quick look at the alert log too.

8 . Set the databases global name




alter database rename global_name to COLDBA1;


9. Create a spfile



create spfile from pfile;


10. Change the database ID

If RMAN is going to be used to back-up the database, the database ID must be changed.

If RMAN isn't going to be used, there is no harm in changing the ID anyway -

and it's a good practice to do so. From sqlplus: shutdown immediate startup mount exit

From OS command prompt:

C: >set ORACLE_SID=COLDBA1

C: >nid target=/ DBNEWID: Release 11.1.0.6.0 - Production on Wed Jul 14 22:20:46 2010 Copyright (c) 1982, 2007, Oracle. All rights reserved.
 Connected to database COLDBA1 (DBID=2034668687)
Connected to server version 11.1.0

Control Files in database: E:\ORACLE11G\ORADATA\COLDBA1\CONTROL01.CTL E:\ORACLE11G\ORADATA\COLDBA1\CONTROL02.CTL E:\ORACLE11G\ORADATA\COLDBA1\CONTROL03.CTL

Change database ID of database COLDBA1? (Y/[N]) => Y

Proceeding with operation Changing database ID from 2034668687 to 436604927 Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL01.CTL - modified Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL02.CTL - modified Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL03.CTL - modified Datafile
E:\ORACLE11G\ORADATA\COLDBA1\SYSTEM01.DBF - dbid changed Datafile
E:\ORACLE11G\ORADATA\COLDBA1\SYSAUX01.DBF - dbid changed Datafile E:\ORACLE11G\ORADATA\COLDBA1\UNDOTBS01.DBF - dbid changed Datafile
E:\ORACLE11G\ORADATA\COLDBA1\USERS01.DBF - dbid changed Datafile
E:\ORACLE11G\ORADATA\COLDBA1\EXAMPLE01.DBF - dbid changed Datafile
E:\ORACLE11G\ORADATA\COLDBA1\TEMP01.DBF - dbid changed Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL01.CTL - dbid changed Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL02.CTL - dbid changed Control File
E:\ORACLE11G\ORADATA\COLDBA1\CONTROL03.CTL - dbid changed Instance shut down
Database ID for database COLDBA1 changed to 436604927.

All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID. DBNEWID - Completed succesfully.
 Once it has finished, start the database up again in sqlplus:
SQL>

conn sys/password@COLDBA1 as sysdba

shutdown immediate;

startup mount;

alter database open resetlogs;

11. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora as necessary.