Oracle Database Discussions

I'm in the process to test out the following

1. clone a database using a cold backup of datafiles
2. restore only a selective set of datafiles

When I'm testing #1, I get the error when running the create controlfile command.

CREATE CONTROLFILE SET DATABASE "BRESMME" RESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01159: file is not from same database as previous files - wrong database id
ORA-01110: data file 5: 'E:\ORADATA\BRESMME\MME_TBS01.ORA'

Where is the database id stored? What am I missing?


This are the steps that I've took
==========================================================
Server 1 -- source
Server 2 -- target

1. stop Oracle on source server
2. copy all user datafiles from source to target
3. create PFILE from SPFILE on source

SQL>
SQL> CREATE PFILE FROM SPFILE;

File created.

File INITbresmme.ORA created!

4. copy PFILE from source to target
5. tweak PFILE to the dir locations on the target and rename it to PFILE_bresmme.ORA
6. backup the SPFILE on target by renaming it.
7. create SPFILE from on target --> may need to shutdown immediate the instance first

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

clo@pluris-dev1 ~
$ set ORACLE_SID=BRESMME

clo@pluris-dev1 ~
$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jan 17 11:19:51 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

USER is ""
SQL> connect sys/XXX as sysdba
Connected to an idle instance.
USER is "SYS"
SQL> CREATE SPFILE FROM PFILE = 'C:\oracle\product\10.1.0\db_1\database\PFILE_bresmme.ORA';

File created.

8. Obtain a trace file script to recreate the controlfile. On the source database issue the following command:
USER is "SYS"
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

9. Copy the control trace file from source to target. The trace should be located in Oracle (in $ORACLE_BASE/admin/$ORACLE_SID>/udump) dir.

10. Edit the controlfile trace and remove the lines until you get to the 'CREATE CONTROLFILE ..'
statement. Edit this stement to read:

CREATE CONTROLFILE SET DATABASE "new_db_name" RESETLOGS
ARCHIVELOG

11. Edit this script to update the directories for the datafiles and redo log files.
Also comment out the lines for "RECOVER ...", ALTER DATABASE OPEN ..." and "ALTER TABLESPACE TEMP ..."

12. Remove all the lines starting with 'Set #2. RESETLOGS case ...'

13. Make sure the target instance is shutdown, then bring it back up nomoun and also use the SPFILE

$ set ORACLE_SID=BRESMME

clo@pluris-dev1 ~
$ sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jan 17 11:33:33 2007

Copyright (c) 1982, 2004, Oracle. All rights reserved.

USER is ""
SQL> connect sys/XXX as sysdba
Connected to an idle instance.
USER is "SYS"
SQL> startup nomount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790096 bytes
Variable Size 174322096 bytes
Database Buffers 436207616 bytes
Redo Buffers 1048576 bytes
SQL>

14. Run the prepared create control file stmt.