Here’s how:
sql> shutdown immediate sql> startup mount sql> exit
&> nid TARGET=SYS/@ / as sysdba -- nid TARGET = / AS SYSDBA DBNAME=NEW_DBNAME DBNEWID: Release 10.1.0.5.0 - 64bit Production Copyright (c) 2001, 2004, Oracle. All rights reserved. Password: Connected to database (DBID=3672849994) Connected to server version 10.1.0 Control Files in database: /m001/oradata//cntrl1.ctl /m002/oradata//cntrl2.ctl Change database ID of database ? (Y/[N]) => Y Proceeding with operation Changing database ID from 3672849994 to 3366639646 Control File /m001/oradata//cntrl1.ctl - modified Control File /m002/oradata//cntrl2.ctl - modified Datafile /m001/oradata//system01.dbf - dbid changed Datafile /m002/oradata//undo01.dbf - dbid changed Datafile /m001/oradata//users01.dbf - dbid changed Datafile /m001/oradata//tools01.dbf - dbid changed Datafile /m003/oradata//tablespace.dbf - dbid changed Datafile /m003/oradata//tablespace.dbf - dbid changed Datafile /m004/oradata//tablespace.dbf - dbid changed Datafile /m001/oradata//sysaux01.dbf - dbid changed Datafile /m002/oradata//temp01.dbf - dbid changed Control File /m001/oradata//cntrl1.ctl - dbid changed Control File /m002/oradata//cntrl2.ctl - dbid changed Instance shut down Database ID for database changed to 3366639646. 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.
Of Course, this doesn’t work with 8.1.7:
Remember, if you brick it, you’re on your own !
The following procedure can be used to zero out the dbid:
— Get a clean shutdown and do a startup mount
shutdown immediate;
startup mount;
— Check the old dbid:
select dbid , name from v$database ;
— Generate the create controlfile statement in a trace file
alter database backup controlfile to trace;
— This will causes a new dbid to be generated
execute dbms_backup_restore.zeroDbid(0)
— Shutdown the database
shutdown normal
>>> delete existing controlfiles
— Change the controlfile, modify the sql statement from the trace file and run it
create controlfile SET database resetlogs ...
— Do a startup nomount to recreate the controlfile with SET option
startup nomount @ccf.sql
— Open the database
alter database open resetlogs;
— Check the new dbid:
select dbid , name from v$database ;