Clone a Oracle XE database and change the SID

Assumption

• Windows platform
• Source database and target database are installed under the same directory name.
• The ARCHIVELOG mode is off
• The source SID is XE, and the target SID is XE2

Source Database
Open a command prompt:

1. sqlplus / as sysdba
2. shutdown immediate
3. startup mount
4. alter database backup controlfile to trace as 'c:\traceXE.sql' resetlogs
5. create pfile='c:\initXE.ora' from spfile
6. shutdown immediate

Zip up all the datafiles under (10g) C:\oraclexe\oradata\XE or (11g) C:\oraclexe\app\oracle\oradata\XE directory except CONTROL.DBF and TEMP.DBF.

Transfer the trace file, pfile, and data file archive to the target machine.

Target Database

Install Oracle XE as usual.  Then shutdown the database

Delete all datafiles in (10g) C:\oraclexe\oradata\XE or (11g) C:\oraclexe\app\oracle\oradata\XE.  Unzip the data file archive to this directory.

Remove the service OracleServiceXE and OracleXETNSListener.  Remove the online log files.  Delete the Oracle instance. Recreate the new instance.  Run the command as follows:
Open a command prompt

1. sc delete OracleServiceXE2
2. sc delete OracleXETNSListener
3. delete C:\oraclexe\app\oracle\fast_recovery_area\XE\*.*
4. oradim -DELETE -SID XE
5. oradim -NEW -SID XE2
6. oradim -EDIT -SID XE2 -STARTMODE auto

A new Oracle instance service is created by step 6.

Edit the line in initXE.ora for new SID name

*.db_name='XE2'

Rename the filename to initXE2.ora and put this file to 

(10g) C:\oraclexe\app\oracle\product\10.2.0\server\database

(11g) C:\oraclexe\app\oracle\product\11.2.0\server\database

Change the registry value of ORACLE_SID under 

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE

from XE to XE2

Open the trace file, and copy the lines from

CREATE CONTROLFILE REUSE DATABASE "XE" RESETLOGS  NOARCHIVELOG

...

CHARACTER SET AL32UTF8

Change the words "REUSE" to "SET" and "XE" to "XE2", i.e.
CREATE CONTROLFILE USE DATABASE "XE2" RESETLOGS  NOARCHIVELOG

Take out the extra empty line if needed

Change ONLINELOG filenames if you want

Open a command prompt

1. sqlplus / as sysdba
2. create spfile from pfile
3. startup nomount
4. run the code copied above to create control file
5. ALTER DATABASE OPEN RESETLOGS
6. Add temp file

ALTER TABLESPACE TEMP ADD TEMPFILE ' (10g) c:\oraclexe\oradata\xe\TEMP.DBF or (11g) C:\oraclexe\app\oracle\oradata\XE\TEMP.DBF' 

SIZE 50M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 500M;

Edit the tnsnames.ora and listener.ora under
(10g) C:\oraclexe\app\oracle\product\10.2.0\server\network\ADMIN
(11g) C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
Change appearance of XE to XE2

Run the command lsnrctl start and a new listener service will be created automatically.

Test whether everything is okay.

Done !

You can change the directories of data file, online log or others dump file in the target database.  But make sure you have changed these directories in the trace file and init file first before running any command.