ORA-01503, ORA-01161, ORA-01110

Problem Scenario:

You are trying to (clone) copy one database to another and you receive the following errors during create controlfile step:

ORA-01503: create controlfile

ORA-01161: database name in file header does not match given name of

 ORA-01110: ………………………………………..

Reason:

For CREATE CONTROLFILE commands you have to ensure that all the datafiles are from the same database. So in this case the ‘given name’ in the error message is the DB NAME from the first file header. Verify that all the dbf files were copied to the correct directories. Also double check to see if all of them are from the same database.

If everything seems to be perfect but you are still receiving ora-1161 then you might have attempted to create controlfile before copying all the files and then alter/added that file.  During Create Controlfile command oracle allocates a db_id and after this if we add any new files then oracle considers them to be from a different database and gives the errors that you received. So you should copy all the files before creating the controlfile.

This error can also occur if you copy the datafiles as a Unix user other than Oracle. If the owner read/write permissions are not the same as Oracle user, then when the create controlfile command is executed, the datafile header will not be updated and the errors occur.

Solution:

a. Copy all the datafiles again and re-execute the Create Controlfile command

b. Check read/write permissions and ownership of the copied datafiles

Quick Database Cloning

One of the common tasks of the DBAs is to clone a database. Very often there is a need to create a test or dev database which is a replica of Production database. In such cases Database cloning can be of great help. This process is quick and has simple steps.
Database cloning procedure can be used to clone/copy a database from one UNIX server to another server. This is one of the fastest ways to copy a database to another server.
Before you clone the database it is important to know that this procedure is Operating System dependent. You cannot clone a database from HP-UX to Windows server or Red Hat Linux server. The simple reason being the difference in Endianness. So before starting the cloning process, please make sure that the target and source OS are same or atleast follow same Endianness.

1. Create the script from Source database

Connect to the source database using SQL*PLUS (as SYSDBA) and execute the following command:

SQL> alter database backup controlfile to trace;

This command will generate a script containing a create database syntax in the trace file directory identified by USER_DUMP_DEST parameter in init.ora file.
The file looks like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 20
MAXLOGMEMBERS 3
MAXDATAFILES 300
MAXINSTANCES 1
MAXLOGHISTORY 115
LOGFILE
GROUP 1 (’/app1/oradata/src_db/log1a.dbf’,
‘/app1/oradata/src_db/log1b.dbf’) SIZE 50M,
GROUP 2 (’/app2/oradata/src_db/log2a.dbf’,
‘/app2/oradata/src_db/log2b.dbf’) SIZE 50M
DATAFILE
‘/appdata/oradata/src_db/system01.dbf’,
‘/appdata/oradata/src_db/srcdatabase.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;

2. Shutdown the source database

SQL> Shutdown immediate

3. Copy datafiles to destination server

Copy all the datafiles on the destination server. You may change the filenames if you wish, in this case you will also have to edit the controlfile to reflect the new datafile names of the destination server.

rcp /app1/oradata/src_db/*    newhost:/app1/oradata/dest_db
rcp /app2/oradata/src_db/*    newhost:/app2/oradata/dest_db
rcp /appdata/oradata/src_db/* newhost:/appdata/oradata/dest_db

4. Edit the Create database command

Change the Create controlfile clause in the Create database command generated in STEP1:

Old:

CREATE CONTROLFILE REUSE DATABASE “SRC_DB” NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE “DEST_DB” RESETLOGS

5. Remove extra clauses

Remove the “recover database” and “alter database open” clause from the script generated in STEP1:

# 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;

6. Change the path of datafiles

Change the path of the datafiles in the script generate in STPE1 to reflect the path on the destination server

Old:

DATAFILE
‘/appdata/oradata/src_db/system01.dbf’,
‘/appdata/oradata/src_db/mydatabase.dbf’

New:

DATAFILE
‘/appdata/oradata/dest_db/system01.dbf’,
‘/appdata/oradata/dest_db/mydatabase.dbf’

7. Create the dump directories

Create the dump directories: bdump, cdump, udump

>cd /app/admin
>mkdir bdump cdump udump pfile

8. Copy the Initialization parameter file

Copy the old init.ora file to the destination server

rcp /app/admin/src_db/pfile/*.ora newhost:/app/oracle/admin/dest_db/pfile

9. Create the new database

SQL> @db_create_controlfile.sql

 

Finally, your cloned database is ready to use.