Monday, September 17, 2012

Steps to Change database name and DBID


$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.7 (Tikanga)

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 – Production

Here I’m renaming TESTDB database name to DEMODB database including DBID


STEP 1 ) Shut down the database and open database in mount

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
----------------            ------------
TESTDB                        OPEN


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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233336 bytes
Variable Size             683674632 bytes
Database Buffers          377487360 bytes
Redo Buffers                5541888 bytes
Database mounted.
SQL>


Step 2) Invoke the DBNEWID utility (nid) specifying the new database name in DBNAME

$ nid target=/  dbname=DEMODB

DBNEWID: Release 11.2.0.2.0 - Production on Mon Sep 17 14:45:30 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database TESTDB (DBID=2577169847)

Connected to server version 11.2.0

Control Files in database:
    /opt/oracle/oradata/TESTDB/control01.ctl
    /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl

Change database ID and database name TESTDB to DEMODB? (Y/[N]) => y

Proceeding with operation
Changing database ID from 2577169847 to 3790527754
Changing database name from TESTDB to DEMODB
    Control File /opt/oracle/oradata/TESTDB/control01.ctl - modified
    Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - modified
    Datafile /opt/oracle/oradata/TESTDB/system01.db - dbid changed, wrote new name
    Datafile /opt/oracle/oradata/TESTDB/sysaux01.db - dbid changed, wrote new name
    Datafile /opt/oracle/oradata/TESTDB/users01.db - dbid changed, wrote new name
    Datafile /opt/oracle/oradata/TESTDB/undotbs01.db - dbid changed, wrote new name
    Control File /opt/oracle/oradata/TESTDB/control01.ctl - dbid changed, wrote new name
    Control File /opt/oracle/app/fast_recovery_area/TESTDB/control02.ctl - dbid changed, wrote new name
    Instance shut down

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

Step 3) Make changes to pfile and password file and opened the database with resetlogs option

$ cd $ORACLE_HOME/dbs

Edit pfile   ---->  initDEMODB.ora

Vi   initDEMODB.ora

Change ‘dbname’

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 17 19:28:23 2012

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

Connected to an idle instance.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2233336 bytes
Variable Size             616565768 bytes
Database Buffers          444596224 bytes
Redo Buffers                5541888 bytes
Database mounted.

SQL> alter database open resetlogs;
Database altered.

SQL> select name from v$database;

NAME
---------
DEMODB

Also see:  

How to Change the DBID, DBNAME Using NID Utility in version 10gR2 onwards (Doc ID 863800.1)


0 comments:

Post a Comment

Auto Scroll Stop Scroll