Oracle How to Change SID of data

Concept 
clone db to new sid 
- trace controlfile and edit to new path except database name and add "SET" to database name
    alter database backup controlfile to trace as '/some/arbitrary/path' reuse;
- alter database backup controlfile to trace;
- copy all datafile to new path
- copy init file to new name and edit to new path but 
  db_name and instance_name is original
- make core dir,bdump and .., to new path
- export oracle_sid to new new 
- create orapasswd to new name
- start up nomount mode and run create new controlfile
- alter database open resetlogs 
# if error in  Unable to create logfile file name to set 
alter system set db_create_online_log_dest_1='/data1/oradata/newdb2/' scope=both; 
# oracle will not the same sid 
# difference dbid but same sid is no
# same sid difference instance(rac)
# same sid is start in one instance other to exclusive mode 
Step-by-Step 
Cloning Procedures 
1. Make a full backup of the source database 
# stop the listener 
$ lsnrctl stop 
# login the source database as sysdba 
# shutdown the database for a full backup 
$ export ORACLE_SID=PROD
$ sqlplus internal
SQL> shutdown immediate 
# backup the config files 
$ cd /demo/oracle/db/8.1.7/admin 
$ tar -czvf PROD_config.tar.gz PROD 
# backup the datafiles 
$ cd /demo/oracle/db/8.1.7/oradata 
$ tar -czvf PROD_data.tar.gz PROD 
2. Create the control files script 
# startup the source database 
$ sqlplus internal 
SQL> startup 
# create the control files script for the target database 
# the file can be found at /demo/oracle/db/8.1.7/admin/PROD/udump 
# for example, ora_17832.trc 
SQL> alter database backup controlfile to trace resetlogs; 
# shutdown the database for cloning 
SQL> shutdown immediate 
# copy the control file script for later use 
$ cd /demo/oracle/db/8.1.7/admin/PROD/udump
$ cp ora_17832.trc /demo/oracle/db/TEST_ctl.sql 
3. Copy the source database to the target database 
# copy the config files 
$ cd /demo/oracle/db/8.1.7/admin
$ cp -r PROD TEST # create the initTEST.ora 
$ cd /demo/oracle/db/8.1.7/admin/TEST/pfile 
$ mv initPROD.ora initTEST.ora 
# copy the datafiles 
$ cd /demo/oracle/db/8.1.7/oradata 
$ cp -r PROD TEST 
# remove the original control files 
# we will re-create them later 
$ cd /demo/oracle/db/8.1.7/oradata/TEST 
$ rm control01.ctl control02.ctl control03.ctl 
4. Prepare the initTEST.ora 
# edit the initTEST.ora 
# 1. change all PROD to TEST 
# 2. update the file locations 
# 3. set remote_login_passwordfile = none 
$ cd /demo/oracle/db/8.1.7/admin/TEST/pfile 
$ vi initTEST.ora 
# create a link for initTEST.ora at /demo/oracle/db/8.1.7/dbs 
# oracle will use this file to start the instance 
$ cd /demo/oracle/db/8.1.7/dbs
$ ln -s /demo/oracle/db/8.1.7/admin/TEST/pfile/initTEST.ora initTEST.ora 
5. Prepare the control file script 
# edit the TEST_ctl.sql 
$ cd /demo/oracle/db
$ vi TEST_ctl.ora 
# 1. remove comment lines 
# 2. change all PROD to TEST 
# 3. update the file locations 
# 4. change the following line 
#    CREATE CONTROLFILE REUSE DATABASE "PROD" RESETLOGS NOARCHIVELOG 
#    to 
#    CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG 
# 5. remove the following line 
#    RECOVER DATABASE USING BACKUP CONTROLFILE 

# after edit, the TEST_ctl.sql should look like this 
  STARTUP NOMOUNT
  CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 2
  MAXDATAFILES 30
  MAXINSTANCES 1
  MAXLOGHISTORY 7260
  LOGFILE
  GROUP 1 '/demo/oracle/db/8.1.7/oradata/TEST/redo01.log' SIZE 500K,
  GROUP 2 '/demo/oracle/db/8.1.7/oradata/TEST/redo02.log' SIZE 500K,
  GROUP 3 '/demo/oracle/db/8.1.7/oradata/TEST/redo03.log' SIZE 500K
  DATAFILE
    '/demo/oracle/db/8.1.7/oradata/TEST/system01.dbf',
    '/demo/oracle/db/8.1.7/oradata/TEST/tools01.dbf',
    '/demo/oracle/db/8.1.7/oradata/TEST/rbs01.dbf',
    '/demo/oracle/db/8.1.7/oradata/TEST/users01.dbf',
    '/demo/oracle/db/8.1.7/oradata/TEST/indx01.dbf',
    '/demo/oracle/db/8.1.7/oradata/TEST/drsys01.dbf',
  CHARACTER SET US7ASCII
  ;
  ALTER DATABASE OPEN RESETLOGS;
  ALTER TABLESPACE TEMP ADD TEMPFILE 
    '/demo/oracle/db/8.1.7/oradata/TEST/temp01.dbf' REUSE; 
6. Re-create the control files 
# start the instance for the target database 
$ export ORACLE_SID=TEST 
$ sqlplus internal 
# re-create the control files for the target database 
SQL> @/demo/oracle/db/TEST_ctl.sql 
# change the global name of the target database 
SQL> alter database rename global_name to TEST; 
7. Edit the network config files 
# edit the listener.ora 
# add the following lines in the SID_LIST_LISTENER section 
(SID_DESC =
    (GLOBAL_DBNAME = TEST)
    (ORACLE_HOME = /demo/oracle/db/8.1.7)
    (SID_NAME = TEST)
  ) 
# edit the tnsnames.ora 
# add the following lines at the buttom 
TEST = (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.1)(PORT=1521))
    (CONNECT_DATA=(SID=TEST))
  ) 
# start the listener 
$ lsnrctl start 
8. Edit the oratab file 
# add a entry for the new database 
# dbstart will use this file to bring the database up/down 
# during system boot time 
$ cd /etc 
$ vi oratab 
# after edit, the oratab file should look like this 
  PROD:/demo/oracle/db/8.1.7:N
  TEST:/demo/oracle/db/8.1.7:N

Credit: Knowledge Management IT for you: Oracle How to Change SID of database and Clone database 
Under Creative Commons License: Attribution

原文：http://kmit4u.blogspot.hk/2010/07/oracle-how-to-change-sid-of-database.html