How to change the DBID,DBNAME Using NID utility in version 11gr2

How to change the DBID,DBNAME Using NID utility in version 11gr2

How to change the DBID,DBNAME Using NID utility in version 11gr2

OS win 2003

Ora: 11gr2

OLD DBNAME/ID = DAY/3579108225 ( you can check the dbid with the following SQL query)

NEW DBNAME/ID = NIGHT/

SQL> select dbid from v$database;

      DBID

----------

3579108225

Step:

1. cold backup of database

2. Bring database in mount stage

3. set oracle_home

4. check tnsping / lsrnctl status ( make sure tnsnames.ora and listener.ora file configured with OLD_DB).

5. drop dbconsole (if configured)

6. issue NID utility

7. change the db_name in pfile/spfile

8. bring database in mount stage

9. open database in resetlogs 

10. recreate password

11. configure tnsnames.ora file with the new db name.

12. rename GLOBAL DATABASE NAMES (if configured)

13. recreate database service via ORADIM utility (windows specific steops)

14. full backup of database (after rename dbname or dbid)

Details

1. Cold backup

C:\>set oracle_sid=day

C:\>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 11:29:14 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host xcopy D:\app\Administrator\oradata\day\*.* f:\tmp\

D:\app\Administrator\oradata\day\CONTROL01.CTL

D:\app\Administrator\oradata\day\CONTROL02.CTL

D:\app\Administrator\oradata\day\REDO01.LOG

D:\app\Administrator\oradata\day\REDO02.LOG

D:\app\Administrator\oradata\day\REDO03.LOG

D:\app\Administrator\oradata\day\SYSAUX01.DBF

D:\app\Administrator\oradata\day\SYSTEM01.DBF

D:\app\Administrator\oradata\day\TEMP01.DBF

D:\app\Administrator\oradata\day\UNDOTBS01.DBF

D:\app\Administrator\oradata\day\USERS01.DBF

10 File(s) copied

SQL> --bring database in mount stage

SQL> startup  mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1384752 bytes

Variable Size             293605072 bytes

Database Buffers          234881024 bytes

Redo Buffers                5791744 bytes

Database mounted.

SQL> --set ORACLE_HOME

SQL> host set ORACLE_HOME=D:\app\Administrator\11.2.0\db

SQL> --configure listner and tnsnames.ora for OLD DB name so we can connect with

SQL> --tnsentry

SQL> --you can check the tns configure via connecting to db with tns entry

SQL> -- for drop dbconsole we need to open the database.

SQL> alter database open;

Database altered.

SQL> --drop dbconsole

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Pr

oduction

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>cd d:\app\Administrator\11.2.0\db\BIN

C:\>set ORACLE_HOME=d:\app\Administrator\11.2.0\db\

C:\>set oracle_sid=DAY

C:\>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Sep 1, 2011 12:16:32 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:

Database SID: DAY

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Sep 1, 2011 12:16:44 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at D:\app\Administrator\cfgtoollogs\emca\da

y\emca_2011_09_01_12_16_32.log.

Sep 1, 2011 12:16:44 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be p

erformed. Some of the possible reasons may be:

 1) EM is configured with different hostname then physical host. Set environment

 variable ORACLE_HOSTNAME= and re-run EMCA script

 2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script

Sep 1, 2011 12:16:44 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Sep 1, 2011 12:23:52 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Enterprise Manager configuration completed successfully

FINISHED EMCA at Sep 1, 2011 12:23:53 PM

SQL> --after dropping dbconsole close the database

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1384752 bytes

Variable Size             301993680 bytes

Database Buffers          226492416 bytes

Redo Buffers                5791744 bytes

Database mounted.

SQL> host NID target=sys/oracle@day DBNAME=NIGHT

DBNEWID: Release 11.2.0.2.0 - Production on Thu Sep 1 12:36:01 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database DAY (DBID=3579108225)

Connected to server version 11.2.0

Control Files in database:

    D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL01.CTL

    D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL02.CTL

Change database ID and database name DAY to NIGHT? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3579108225 to 2106096690

Changing database name from DAY to NIGHT

    Control File D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL01.CTL - modified

    Control File D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL02.CTL - modified

    Datafile D:\APP\ADMINISTRATOR\ORADATA\DAY\SYSTEM01.DB - dbid changed, wrote

new name

    Datafile D:\APP\ADMINISTRATOR\ORADATA\DAY\SYSAUX01.DB - dbid changed, wrote

new name

    Datafile D:\APP\ADMINISTRATOR\ORADATA\DAY\UNDOTBS01.DB - dbid changed, wrote

 new name

    Datafile D:\APP\ADMINISTRATOR\ORADATA\DAY\USERS01.DB - dbid changed, wrote n

ew name

    Datafile D:\APP\ADMINISTRATOR\ORADATA\DAY\TEMP01.DB - dbid changed, wrote ne

w name

    Control File D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL01.CTL - dbid changed,

wrote new name

    Control File D:\APP\ADMINISTRATOR\ORADATA\DAY\CONTROL02.CTL - dbid changed,

wrote new name

    Instance shut down

Database name changed to NIGHT.

Modify parameter file and generate a new password file before restarting.

Database ID for database NIGHT changed to 2106096690.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

C:\>sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 12:38:18 2011

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

Connected to an idle instance.

SQL> --change the new db name in the pfile and create spfile from pfile;

SQL> create pfile from spfile;

File created.

SQL> --change name in pfile

SQL> create spfile from pfile;

File created.

SQL> --now startup db in mount stage

SQL> startup mount

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1384752 bytes

Variable Size             301993680 bytes

Database Buffers          226492416 bytes

Redo Buffers                5791744 bytes

Database mounted.

SQL> --open database in resetlogs

SQL> alter database open resetlogs;

Database altered.

SQL> --recreate password file via ORAPWD utility

SQL> host orapwd file=D:\app\Administrator\11.2.0\db\database\orapwdnight.ora pa

ssword=oracle entries=5

SQL> --configure TNSNAMES.ORA file for new db name

SQL> --$ORACLE_HOME/network/admin/TNSNAMES.ORA

SQL> --rename GLOBAL DATABASE NAME

SQL> --recreate oracle service via ORADIM utility (on windows only)

SQL> host oradim -new -sid NIGHT -startmode manual

Instance created.

SQL> --check the new dbid and db name

SQL> select dbid,name from v$database;

      DBID NAME

---------- ---------

2106096690 NIGHT

Change ORACLE_SID

Oracle Database : 10.1.0.2.0
OS : Windows XP sp2
Archivelog : Enable
OEM : DBCONSOLE configured

HGC@TAJ>select name from v$database;

NAME
---------
ORCLDB

HGC@TAJ>select instance_name from v$instance;

INSTANCE_NAME
----------------
sidorcl

STEP 1

shutdown database

HGC@TAJ>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

STEP 2

STOP SERVICES

C:\>set ORACLE_SID=sidorcl

C:\>net stop oracleservicesidorcl
The following services are dependent on the OracleServiceSIDORCL service.
Stopping the OracleServiceSIDORCL service will also stop these services.

OracleDBConsolesidorcl

Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsolesidorcl service is stopping...
The OracleDBConsolesidorcl service was stopped successfully.

The OracleServiceSIDORCL service is stopping.
The OracleServiceSIDORCL service was stopped successfully.

STEP 3

DROP OLD ORACLE_SID service

C:\>ORADIM -DELETE -SID sidorcl
Instance deleted.

STEP 4

set new oracle_sid

C:\>set ORACLE_SID=orclsid

STEP 5

Recreate Password File

C:\>ORAPWD FILE=c:\oracle\product\10.1.0\db_1\database\pwdorclsid.ora PASSWORD=o
racle ENTRIES=5 FORCE=y

STEP 6

Create New Oracle Services with new ORACLE_SID name

C:\>ORADIM -new -sid ORCLSID -startmode manual -spfile
Instance created.

C:\>oradim -edit -sid ORCLSID -startmode auto

STEP 7

RENAME PFILE (INIT[OLDSID].ORA TO INIT[NEW].ORA

C:\>rename c:\oracle\product\10.1.0\db_1\database\initSIDORCL.ORA initORCLSID.ORA

STEP 8

Recreate SPFILE from PFILE

C:\>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Sun Jun 3 12:07:42 2007

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

HGC@TAJ>conn sys as sysdba
Enter password:
Connected to an idle instance.
HGC@TAJ>create spfile from pfile;

File created.

STEP 9

STARTUP database and check !!!

HGC@TAJ>startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
HGC@TAJ>select name from v$database;

NAME
---------
ORCLDB

HGC@TAJ>select instance_name from v$instance;

INSTANCE_NAME
----------------
orclsid

STEP 10

Drop EM repository

HGC@TAJ>ed
Wrote file afiedt.buf

1 DECLARE
2 CURSOR c1 IS
3 SELECT owner, synonym_name name
4 FROM dba_synonyms
5 WHERE table_owner = 'SYSMAN';
6 BEGIN
7 FOR r1 IN c1
8 LOOP
9 IF r1.owner = 'PUBLIC' THEN
10 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
11 ELSE
12 EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
13 END IF;
14 END LOOP;
15* END;
16 /

PL/SQL procedure successfully completed.

HGC@TAJ>drop role mgmt_user;

Role dropped.

HGC@TAJ>drop user mgmt_view cascade;

User dropped.

HGC@TAJ>drop user sysman cascade;

User dropped.

STEP 11

Create New EM repository

C:\>emca

STARTED EMCA at Sun Jun 03 13:09:14 GST 2007
Enter the following information about the database to be configured
Listener port number: 1521
Database SID: orclsid
Service name: orclsid
Email address for notification:
Email gateway for notification:
Password for dbsnmp:
Password for sysman:
Password for sys: :

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1
Enterprise Manager ORACLE_HOME ................ C:\oracle\product\10.1.0\Db_1

Database host name ................ taj.domainname.local
Listener port number ................ 1521
Database SID ................ orclsid
Service name ................ orclsid
Email address for notification ...............
Email gateway for notification ...............

-----------------------------------------------------------------
Do you wish to continue? [yes/no]:

Jun 3, 2007 1:10:06 PM oracle.sysman.emcp.EMConfig updateReposVars
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\emdrep\config\repositor
y.variables ...
Jun 3, 2007 1:16:19 PM oracle.sysman.emcp.EMConfig createRepository
INFO: Creating repository ...
Jun 3, 2007 1:16:19 PM oracle.sysman.emcp.EMConfig perform
INFO: Repository was created successfully
Jun 3, 2007 1:16:29 PM oracle.sysman.emcp.util.PortQuery findUsedPorts
INFO: Searching services file for used port
Jun 3, 2007 1:16:30 PM oracle.sysman.emcp.EMConfig addPortEntries
INFO: Updating file C:\oracle\product\10.1.0\Db_1\install\portlist.ini ...
Jun 3, 2007 1:16:30 PM oracle.sysman.emcp.EMConfig updateEmdProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emd.properties .
..
Jun 3, 2007 1:16:31 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: targets.xml file is updated successfully
Jun 3, 2007 1:16:31 PM oracle.sysman.emcp.EMConfig updateEmomsProps
INFO: Updating file C:\oracle\product\10.1.0\Db_1\sysman\config\emoms.properties
...
Jun 3, 2007 1:16:31 PM oracle.sysman.emcp.EMConfig updateConfigFiles
INFO: emoms.properties file is updated successfully
Jun 3, 2007 1:16:34 PM oracle.sysman.emcp.EMConfig startOMS
INFO: Starting the DBConsole ...
Jun 3, 2007 1:18:14 PM oracle.sysman.emcp.EMConfig perform
INFO: DBConsole is started successfully
Jun 3, 2007 1:18:14 PM oracle.sysman.emcp.EMConfig perform
INFO: >>>>>>>>>>> The Enterprise Manager URL is http://taj.domainname.lo
cal:5502/em <<<<<<<<<<<
Enterprise Manager configuration is completed successfully
FINISHED EMCA at Sun Jun 03 13:18:14 GST 2007

C:\>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation. All rights reserved.
http://taj.domainname.local:5502/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory C:\oracle\product\10.1.0\Db_1/taj.domainname.local_orclsid/sysman/log

Changing Only the Database Name

Database : 10gr1
OS PLATFORM : Windows
Archive Log : Enable
Non Distributed Database System.


SQL> select NAME from v$database;

NAME
---------
ORA101



STEP
1.Ensure that you have a recoverable whole database backup.

2.Ensure that the target database is mounted but not open.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

3.Invoke the utility on the command line, specifying a valid user with the SYSDBA privilege. You must specify both the DBNAME and SETNAME parameters. This example changes the name to ORA10G.

C:\>nid TARGET=SYS/oracle@ORA101 DBNAME=ORA10G SETNAME=YES
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORA101 (DBID=13875087)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL

Change database name of database ORA101 to ORA10G? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORA101 to ORA10G
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - modifie
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF - wrote new na
me
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF - wrote new n
ame
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF - wrote new na
me
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF - wrote new nam
e
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF - wrote new n
ame
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\TEMP01.DBF - wrote new name

Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - wrote n
ew name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - wrote n
ew name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - wrote n
ew name
Instance shut down

Database name changed to ORA10G.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

If validation is not successful, then DBNEWID terminates and leaves the target database intact.

4.Set the DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new database name.

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 17:57:39 2007

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

Enter password:
Connected to an idle instance.

SQL> -- if PFILE not created file create and edit DB_NAME parameter.
SQL> create PFILE from SPFILE;

File created.

SQL> --After edit db_name in PFILE create new SPFILE.
SQL> create SPFILE from PFILE;

File created.

5.Create a new password file.

C:\>orapwd file=c:\oracle\product\10.1.0\db_1\database\PWDora101.ora password=or
acle entries=5 force=y

6.startup database for normal use.

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 18:09:07 2007

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

Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
SQL> select NAME from v$database;

NAME
---------
ORA10G

Instance Name or Database Name is different. in above example we changed just DATABASE NAME not INSTANCE_NAME.

SQL> select NAME from v$database;

NAME
---------
ORA10G

SQL> select INSTANCE_NAME from v$instance;

INSTANCE_NAME
----------------
ora101

Changing the DBID and Database Name

Database : 10gr1
OS PLATFORM : Windows
Archive Log : Enable
Non Distributed Database System.


SQL> select DBID,NAME from v$database;

DBID NAME
---------- ---------
13875087 ORA10G


1.Ensure that you have a recoverable whole database backup.


2.shutdown database and startup in mount stage.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege.

C:\>nid TARGET=SYS/oracle DBNAME=ORA101
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORA10G (DBID=13875087)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL

Change database ID and database name ORA10G to ORA101? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 13875087 to 13925003
Changing database name from ORA10G to ORA101
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - modifie
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF - dbid changed
, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF - dbid changed
, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF - dbid changed,
wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF - dbid change
d, wrote new name
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\TEMP01.DBF - dbid changed,
wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - dbid ch
anged, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - dbid ch
anged, wrote new name
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - dbid ch
anged, wrote new name
Instance shut down

Database name changed to ORA101.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORA101 changed to 13925003.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

4.Open PFILE and edit DB_NAME parameter and create new SPFILE.
and Create New Password file.

C:\>orapwd file=c:\oracle\product\10.1.0\db_1\database\PWDora101.ora PASSWORD=or
acle entries=5 force=y
C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 18:54:20 2007

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

Enter password:
Connected to an idle instance.

SQL> -- Edit DB_NAME parameter in PFILE and CREATE NEW SPFILE.
SQL> create SPFILE from PFILE;

File created.

Note:password file name like "PWD.ORA" and sid is equal to INSTANCE_NAME not DATABASE_NAME.


5.Startup Database in MOUNT MODE and open with RESETLOGS.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select DBID,NAME from v$database;

DBID NAME
---------- ---------
13925003 ORA101



Note : Take complete database backup. previous backup no longer usable.

Only the DBID of a database

Database : 10gr1
OS PLATFORM : Windows
Archive Log : Enable
Non Distributed Database System.


DBNEWID is a database utility that can change the internal, unique database identifier (DBID) and the database name (DBNAME) for an operational database.

Note:
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1.

STEPS

SQL> select DBID from v$database;

DBID
----------
13855521

1. Take Complete Recoverable Database BACKUP.

C:\>rman target=sys@ORA101

Recovery Manager: Release 10.1.0.2.0 - Production

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

target database Password:
connected to target database: ORA101 (DBID=13855521)

RMAN> configure controlfile autobackup on;

using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }

Starting backup at 02-APR-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.D
BF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.D
BF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.
DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.
DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 02-APR-07
channel ORA_DISK_1: finished piece 1 at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2007_
04_02\O1_MF_NNNDF_TAG20070402T125943_311KJ15X_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
Finished backup at 02-APR-07

Starting backup at 02-APR-07
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=618839783
input archive log thread=1 sequence=8 recid=2 stamp=618842813
input archive log thread=1 sequence=9 recid=3 stamp=618843692
channel ORA_DISK_1: starting piece 1 at 02-APR-07
channel ORA_DISK_1: finished piece 1 at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2007_
04_02\O1_MF_ANNNN_TAG20070402T130132_311KMG29_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 02-APR-07

Starting Control File and SPFILE Autobackup at 02-APR-07
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2007
_04_02\O1_MF_S_618843697_311KML4F_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 02-APR-07

RMAN>

2.Shutdown database and startup in mount mode.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\>set oracle_sid=ora101

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 16:12:42 2007

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database mount;

Database altered.

3.Invoke the DBNEWID utility on the command line, specifying a valid user with the SYSDBA privilege

C:\>nid TARGET=SYS/oracle@ORA101
DBNEWID: Release 10.1.0.2.0 - Production
Copyright (c) 2001, 2004, Oracle. All rights reserved.

Connected to database ORA101 (DBID=13855521)

Connected to server version 10.1.0

Control Files in database:
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL

Change database ID of database ORA101? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 13855521 to 13868607
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - modifie
d
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - modifie
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF - dbid changed

Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF - dbid change
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF - dbid changed

Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF - dbid changed
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF - dbid change
d
Datafile C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\TEMP01.DBF - dbid changed
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL01.CTL - dbid ch
anged
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL02.CTL - dbid ch
anged
Control File C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\CONTROL03.CTL - dbid ch
anged
Instance shut down

Database ID for database ORA101 changed to 13868607.
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.

4.startup database in mound mode.

C:\>set oracle_sid=ora101

C:\>sqlplus sys as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Apr 2 16:16:42 2007

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

Enter password:
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.

5.open database with RESETLOGS.

SQL> alter database open resetlogs;

Database altered.

SQL> select DBID from v$database;

DBID
----------
13868607

NOTE: Take New DATABASE BACKUP previous backup is no longer usable.