Home > tips > NID: renaming your database in a minute

NID: renaming your database in a minute


NID Utility, “DBNEWID is a database utility that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database”.         – excerpt from Oracle documentation.

When I first learnt from a guy, fortunately not newbie dba, it is a real shame for me to hear about nid very late in my dba career.

The day before I heard about nid, we were discussing with another 16 year dba, why Oracle could not rename db in year 2012, even preparing for version 12. But it was a real disaster that we discovered that there was a tool since year 2001, version 9. (Oracle documentation about nid utiliy in 9i) This gave real pain that I could not write about “nid” for two weeks, and rethink about continueing my career in dba position 🙂

Before nid, we were producing controlfile by backup controlfile to trace, changing its contents, etc. No need to these disgusting and a bit lengthy process.

Here’s what I did, while changing the name of database which I had restored for making a test and development environment. Changing database name from MYDB to MYDBTEST.

[oracle@myhostt01 ~]$ nid TARGET=SYS DBNAME=MYDBTEST logfile=’/tmp/mydb_nid.log’
Password:
[oracle@myhostt01 ~]$

— log file details
[oracle@myhostt01 ~]$ tail -f /tmp/mydb_nid.log
Instance shut down

Database name changed to MYDBTEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database MYDBTEST changed to 1233471344.
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.

— change dbname in init.ora

[oracle@myhostt01 ~]$ vi /tmp/init2.ora

MYDBTEST.__db_cache_size=4378853376
MYDBTEST.__java_pool_size=16777216
MYDBTEST.__large_pool_size=16777216
MYDBTEST.__pga_aggregate_target=1073741824
MYDBTEST.__sga_target=5368709120
MYDBTEST.__shared_io_pool_size=0
MYDBTEST.__shared_pool_size=922746880
MYDBTEST.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/MYDBTEST/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/mydb/controlfile/current.270.782998169’#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_domain=”
*.db_name=’MYDBTEST’
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBTESTXDB)’
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=5368709120
*.undo_tablespace=’UNDOTBS1′

[oracle@myhostt01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 11 14:11:52 2012

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

Connected to an idle instance.

SQL> exit
Disconnected
[oracle@myhostt01 ~]$ ps -ef|grep pmon
grid     15792     1  0 11:11 ?        00:00:00 asm_pmon_+ASM
oracle   29856 28284  0 14:12 pts/7    00:00:00 grep pmon

Advertisements
Categories: tips Tags: ,
  1. Taner Akbulut
    May 22, 2012 at 8:35 am

    Hi Derya

    You know, this is the way how Oracle works. They generate a new command and then put it into some “a123xxxnnn.pdf” reference document between thousands of pages and expect the users to find it. Before version 9, even changing column names in tables was not possible.

    In the nid explanation it says that this tool specifically designed to address rman repository logic problem. If you generate a copy of the database then rman can not understand the difference since DBID value is same in the original and seeded database. Just as I expected. Instead of simplifying and fixing the problem in rman, just generate another tool/command and expect the users to read all documents and learn again. How dbas can earn money if they really would do “zero administration” logic?

    So this is not your shame, it is shame for Oracle but they don’t feel shame since their face is made from do..ey skin.

  2. deryaoktay
    May 22, 2012 at 2:06 pm

    Taner, you maybe right, because I do remember that,I have not seen this topic in advanced backup recovery course.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: