Tuesday, April 9, 2013

Change SID of the Oracle database using DBNEWID

In this post I will describe how to change Oracle SID using utility DBNEWID. As I can see DBNEWID is available from 9i version but I’ve never heard about it. Till now, I have used procedure where I’m manually editing and re-creating control file.

But I think it’s much better to use database utility, then manually editing control file script which is task more prone to errors.

I’m aware that I can perform this task using RMAN DUPLICATE feature, but in this case I don’t want to use active database for duplication. As this is 11gR1 database Backup-Based duplication is not available.

So I will restore production database from backup to development server and change SID using DBNEWID.

I’ve successfully restored and recovered database.

Let’s check DBID and instance name. 
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
crmp

SQL> select dbid,open_mode from v$database;

      DBID OPEN_MODE
---------- ----------
1597268947 READ WRITE

CRMP is production database and I want to change SID from CRMP to CRMD.

First step is to shutdown database and start in mount mode. 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
Database mounted.

Now let’s run nid to change database name. 
$ nid target=sys/oracle@test_crmp dbname=crmd setname=YES

DBNEWID: Release 11.1.0.7.0 - Production on Tue Apr 9 11:59:56 2013

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

Connected to database CRMP (DBID=1597268947)

Connected to server version 11.1.0

Control Files in database:
    /u01/app/devcrmd/oradata/control01.ctl
    /u01/app/devcrmd/oradata/control02.ctl

Change database name of database CRMP to CRMD? (Y/[N]) => Y

Proceeding with operation
Changing database name from CRMP to CRMD
    Control File /u01/app/devcrmd/oradata/control01.ctl - modified
    Control File /u01/app/devcrmd/oradata/control02.ctl - modified
    Datafile /u01/app/devcrmd/oradata/datafiles/system01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/sysaux01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/users01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/data01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/indx01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/tools01.db - wrote new name
    Datafile /u01/app/devcrmd/oradata/datafiles/undotbs02.db - wrote new name

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                        :nmchged);    end;
ORA-01116: error in opening database file /u01/app/crmp/oradata/datafiles/tmp01.dbf
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6694
ORA-06512: at line 1



Change of database name failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.

If you are changing DBID or database name of your production database it is very important to backup database prior doing anything. As these are very sensitive operations it is very important to think carefully before performing any action. If you are stuck and not sure what to do I advise you to contact Oracle Support immediately.

You can see that I have received error after running nid because my tempfile was missing. I forgot to recreate it after database restore.

To fix this error I will drop tempfile and run nid again. 
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 15:59:19 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/crmp/oradata/datafiles/tmp01.dbf


SQL> alter database tempfile '/u01/app/crmp/oradata/datafiles/tmp01.dbf' drop including datafiles;

Database altered.

Run nid again. 
$ nid target=sys/oracle@test_crmp dbname=crmd setname=YES

DBNEWID: Release 11.1.0.7.0 - Production on Tue Apr 9 16:09:07 2013

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

Connected to database CRMP (DBID=1597268947)
Operation already in progress, continuing

Connected to server version 11.1.0

Control Files in database:
    /u01/app/devcrmd/oradata/control01.ctl
    /u01/app/devcrmd/oradata/control02.ctl

Change database name of database CRMP to CRMD? (Y/[N]) => Y

Proceeding with operation
Changing database name from CRMP to CRMD
    Control File /u01/app/devcrmd/oradata/control01.ctl - modified
    Control File /u01/app/devcrmd/oradata/control02.ctl - modified
    Datafile /u01/app/devcrmd/oradata/datafiles/system01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/sysaux01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/users01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/data01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/indx01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/tools01.db - already changed
    Datafile /u01/app/devcrmd/oradata/datafiles/undotbs02.db - already changed
    Control File /u01/app/devcrmd/oradata/control01.ctl - wrote new name
    Control File /u01/app/devcrmd/oradata/control02.ctl - wrote new name
    Instance shut down

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

Now operation completed successfully.

Create parameter file for development database and change database name. 
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 16:19:31 2013

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

Connected to an idle instance.

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initcrmp.ora';

File created.


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
SQL>
SQL>
SQL> alter system set db_name=crmd scope=spfile;

System altered.

Startup database in mount mode. 
SQL> startup mount force;
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2163280 bytes
Variable Size             192941488 bytes
Database Buffers          528482304 bytes
Redo Buffers                7127040 bytes
Database mounted.


Create new password file. 
$ orapwd file=$ORACLE_HOME/dbs/orapwcrmd password=oracle entries=10

Open database. 
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Apr 9 16:23:58 2013

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

Let’s check DBID and instance name. 
SQL> select dbid,open_mode from v$database;

      DBID OPEN_MODE
---------- ----------
1597268947 READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
crmd

Notice that DBID is kept unchanged as I changed only database name. This is the reason why RESETLOGS operation wasn’t needed.

Post operations...

Add missing tempfile for temporary tablespace: 
SQL> alter tablespace TMP add tempfile '/u01/app/devcrmd/oradata/datafiles/tmp01.dbf' size 2048M;
Change oratab entry: 
$ vi /etc/oratab
...
#### set
...
crmd:/u01/app/devcrmd/product/11.1.0/db_1:N

Change listener.ora and tnsnames.ora files using new SID.


REFERENCES:
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dbnewid.htm


Written April 09, 2013 by Marko Sutic

   10 comments

10 comments:

  1. Damir VadasApril 10, 2013 at 8:29 AM

    windows databases are not so lucky...
    :-)

    ReplyDelete
  2. Mladen GogalaApril 10, 2013 at 4:59 PM

    Generally speaking, there isn't much need for changing the database name, ever since the advent of the parameter DB_UNIQUE_NAME. That parameter is also used by RMAN, in conjunction with the DBID. If you have to chane the DB name, I would still prefer re-creating the control file to nid. This utility is an anachronism from the times of 9i, at least in my opinion.

    ReplyDelete
  3. Marko SuticApril 10, 2013 at 7:30 PM

    Hello Damir,

    thanks for comment ;)
    I've tested nid only on Linux machine. But as I can see on Google some DBA's tried to run nid on Windows machine so it probably works there also.

    For 10g version : http://tinyurl.com/c3vquva

    Regards,
    Marko

    ReplyDelete
  4. Marko SuticApril 10, 2013 at 7:50 PM

    Hi Mladen,

    also thanks for comment.
    You are correct, there isn't much need for changing database name when you have DB_UNIQUE_NAME. But I wanted to test nid utility as I've never tried to use it.

    Why you prefer re-creating control file over nid?
    Have you experienced some odd problems with nid? I'm just curious :)

    I don't think that re-creating control files is the wrong way (as I said, it was the only way for me till now). But using nid utility means less writing/editing, less chance for stupid typos, so why not use it... yes, I know, I'm lazy :D

    Regards,
    Marko

    ReplyDelete
  5. night_dreamerJune 30, 2015 at 4:10 PM

    I did exactly what you did in your blog without any errors, but when I grep for pmon or smon, the name is still the old one. Also "select instance_name from v$instance;" return old name.
    Now I have 2 folders in diag/rdbms/ with "new" name and the old name. Alert.log file is in the directory with the "new" name, but the file itself is named still alert_oldname.log
    (Linux x64, 11.2.0.4.0)

    It seems your guide is incomplete ;)

    ReplyDelete
  6. Marko SuticJuly 1, 2015 at 9:44 AM

    Hello night_dreamer,

    have you changed DB_NAME parameter with new name in parameter file?

    Regards,
    Marko






    ReplyDelete
  7. night_dreamerJuly 2, 2015 at 2:36 PM

    Hello.
    Thanks for your reply!

    Yes, I've changed this parameter. But here is another parameter and I don't know whether to change it or not:

    *.dispatchers='(PROTOCOL=TCP) (SERVICE=atlantisXDB)'

    ReplyDelete
  8. Marko SuticJuly 3, 2015 at 8:48 AM

    This parameter is not important for changing name of the database.
    You can probably ignore it. Parameter is important for dispatcher configuration.

    Regards,
    Marko

    ReplyDelete
  9. night_dreamerJuly 3, 2015 at 10:40 AM

    Hello
    I found what causing this half renamed db.
    I forgot in.bash_profile exports of ORACLE_SID and ORACLE_UNQNAME pointed to the old name.
    I corrected them, restart db, and now everything is as expected!

    Thanks for your guide!

    ReplyDelete
  10. Johannes AhrendsAugust 10, 2020 at 1:08 PM

    Sorry, but the title of the document is misleading: to change the instance_name it's sufficient to change the name of the spfile, orapwd and variable ORACLE_SID. You only need NID if you want to change the database name

    ReplyDelete

Subscribe to: Post Comments (Atom)