Total Pageviews

Rename Database name with NID utility

High Level steps:


1.     Create pfile from spfile and shutdown database
2.     Startup database in mount state using PFILE
3.     Run NID command at server level (at the end of this DB will be shut down by this command)
4.     Change DB_NAME parameter in create pfile to new name want to set.
5.     Startup database in mount state using pfile and create new spfile from pfile.
6.     Open database in resetlogs
7.     Restart the database



1. Create pfile from spfile and shutdown database
C:\Users\neersi>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 8 10:23:08 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

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

SQL> create pfile='E:\ODSPD\pfile_n.ora' from spfile;
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
If database is in clustered mode, shutdown all instances and follow next steps on one instance.


2. Startup database in mount state.

C:\Users\neersi>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 8 10:27:30 2013

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

Connected to an idle instance.

SQL> startup mount pfile='E:\ODSPD\pfile_n.ora';
ORACLE instance started.

Total System Global Area  803500032 bytes
Fixed Size                  1377588 bytes
Variable Size             222300876 bytes
Database Buffers          574619648 bytes
Redo Buffers                5201920 bytes
Database mounted.


3. Run NID command at server level

C:\Users\neersi>nid TARGET=sys/neeraj DBNAME=NEERPRD

DBNEWID: Release 11.2.0.1.0 - Production on Sun Sep 8 10:55:12 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to database NEWODSPD (DBID=1349846919)
Connected to server version 11.2.0

Control Files in database:
    E:\ODSPD\ORADATA\ODSPD\CONTROLFILE\O1_MF_929CD5D8_.CTL
Change database ID and database name NEWODSPD to NEERPRD? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1349846919 to 3279177235
Changing database name from NEWODSPD to NEERPRD
    Control File E:\ODSPD\ORADATA\ODSPD\CONTROLFILE\O1_MF_929CD5D8_.CTL - modified
    Datafile E:\ODSPD\ORADATA\ODSPD\DATAFILE\O1_MF_SYSTEM_929CDCPO_.DB - dbid changed, wrote new name
    Datafile E:\ODSPD\ORADATA\ODSPD\DATAFILE\O1_MF_SYSAUX_929CF2GP_.DB - dbid changed, wrote new name
    Datafile E:\ODSPD\ORADATA\ODSPD\DATAFILE\O1_MF_UNDOTBS1_929CFMFD_.DB - dbid changed, wrote new name
    Datafile E:\ODSPD\ORADATA\ODSPD\DATAFILE\O1_MF_USERS_929CGV9X_.DB - dbid changed, wrote new name
    Datafile E:\ODSPD\ORADATA\ODSPD\DATAFILE\O1_MF_TEMP_929CFV09_.TM - dbid changed, wrote new name
    Control File E:\ODSPD\ORADATA\ODSPD\CONTROLFILE\O1_MF_929CD5D8_.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to NEERPRD.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEERPRD changed to 3279177235.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

4. Change DB_NAME parameter in pfile.  (e.g. db_name=NEERPRD)
5. Startup database in mount state using pfile and create new spfile from pfile.
6. Open database in resetlogs
7. Restart the database


C:\Users\neersi>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Sep 8 10:55:59 2013

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

Connected to an idle instance.

SQL> startup mount pfile='E:\ODSPD\pfile_n.ora';
ORACLE instance started.

Total System Global Area  803500032 bytes
Fixed Size                  1377588 bytes
Variable Size             222300876 bytes
Database Buffers          574619648 bytes
Redo Buffers                5201920 bytes
Database mounted.
SQL> create spfile from pfile='E:\ODSPD\pfile_n.ora';

File created.

SQL> alter database open resetlogs;

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  803500032 bytes
Fixed Size                  1377588 bytes
Variable Size             222300876 bytes
Database Buffers          574619648 bytes
Redo Buffers                5201920 bytes
Database mounted.
Database opened.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 odspd
L-156020001
11.2.0.1.0        08-SEP-13 OPEN         NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO


SQL> select name from v$database;

NAME
---------
NEERPRD


No comments:

Post a Comment