STEPS TO CHANGE DATABASE NAME USING NID UTILITY

1- Shutdown immediate;

2- Startup mount;

3- on command prompt

C:\Users\Cmd>nid TARGET=SYS/oracle@orcl DBNAME=testdb SETNAME=YES

4- Change folder permission if there is an issue.

5- Create new password file.

C:\Users\Cmd>orapwd file=E:\app\db11g\product\11.2.0\dbhome_1\database\

PWDtestdb.ora password=oracle force=y entries=2 ignorecase=y

SQL> startup pfile='E:\app\db11g\admin\orcl\pfile\init.ora';

SQL> select name from v$database;

NAME

---------
TESTDB

SQL> create spfile from pfile='E:\app\db11g\admin\orcl\pfile\init.ora';

File created.

Changing the DBID and Database Name

The following steps describe how to change the DBID of a database. Optionally, you can change the database name as well.

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

2. Ensure that the target database is mounted but not open, and that it was shut down consistently prior to mounting. For example:

3.  SHUTDOWN IMMEDIATE

4.  STARTUP MOUNT

5.   

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

4.  % nid TARGET=SYS/oracle@test_db

5.   

To change the database name in addition to the DBID, specify the DBNAME parameter. This example changes the name to test_db2:

% nid TARGET=SYS/oracle@test DBNAME=test_db2

The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID for each datafile (including offline normal and read-only datafiles), and then exits. The database is left mounted but is not yet usable. For example:

DBNEWID: Release 9.2.0.1.0

(c) Copyright 2002 Oracle Corporation.  All rights reserved.

Connected to database TEST_DB (DBID=3942195360)

Control Files in database:

    /oracle/dbs/cf1.f

    /oracle/dbs/cf2.f

Change database id of database SOLARIS? (Y/[N]) => y

Proceeding with operation

    Datafile /oracle/dbs/tbs_01.f - changed

    Datafile /oracle/dbs/tbs_02.f - changed

    Datafile /oracle/dbs/tbs_11.f - changed

    Datafile /oracle/dbs/tbs_12.f - changed

    Datafile /oracle/dbs/tbs_21.f - changed

New DBID for database TEST_DB is 3942196782.

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

Proceed to shutdown database and open with RESETLOGS option.

DBNEWID - Database changed.

If validation is not successful, then DBNEWID terminates and leaves the target database intact. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.

4. After DBNEWID successfully changes the DBID, shut down the database:

5.  SHUTDOWN IMMEDIATE

6.   

5. Mount the database. For example:

6.  STARTUP MOUNT

7.   

6. Open the database in RESETLOGS mode and resume normal use. For example:

7.  ALTER DATABASE OPEN RESETLOGS;

8.   

Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.