Search This Blog

Thursday, September 1, 2011

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

2 comments:

Anonymous said...

Wow... Nicely explained

Anonymous said...

Have you changed the DBID and DB_NAME with ASM file structure?

Thanks,

Su