Friday, April 15, 2011

Changing database name and DBID…looks exciting!!

What is DBID and how to change it?

DBID is called the internal database identifier. From oracle 9i DBNEWID utility is introduced to perform this task very easily. Prior to this you have to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, you could not give the database a new identifier (DBID).

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 

Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. 
Change DBID and DBNAME:

First take the full backup of your database:

C:\>sqlplus sys/sys@test as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 15 12:36:11 2011

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

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

Shutdown your database

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

Mount the database now!

SQL> startup mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             327157332 bytes
Database Buffers           96468992 bytes
Redo Buffers                6078464 bytes
Database mounted.

Type the below command…

SQL> host nid target=sys/sys@test dbname=prod

DBNEWID: Release 11.1.0.6.0 - Production on Fri Apr 15 12:38:29 2011

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

Connected to database TEST (DBID=93940391)

Connected to server version 11.1.0

Control Files in database:
    D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL03.CTL

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

Proceeding with operation
Changing database ID from 93940391 to 160375751
Changing database name from TEST to PROD
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
    Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - dbid changed, wrote new name

    Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - dbid changed, wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - dbid changed, wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - dbid changed, wrote new
name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - dbid changed, wrote new
name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - dbid changed, wrote new
name
    Instance shut down

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


SQL> conn / as sysdba

Connected to an idle instance.

SQL> create pfile='C:\test\testinit.ora' from spfile;

File created.

Edit this initialization file:

test.__db_cache_size=96468992
test.__java_pool_size=12582912
test.__large_pool_size=4194304
test.__oracle_base='D:\oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=113246208
test.__sga_target=318767104
test.__shared_io_pool_size=0
test.__shared_pool_size=192937984
test.__streams_pool_size=8388608
*.audit_file_dest='D:\oracle\admin\test\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\oracle\oradata\test\control01.ctl','D:\oracle\oradata\test\control02.ctl','D:\oracle\oradata\test\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest='D:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='D:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.log_archive_dest_1='location=c:\archive\'
*.memory_target=428867584
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'




SQL> create spfile from pfile='C:\test\testinit.ora';

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             327157332 bytes
Database Buffers           96468992 bytes
Redo Buffers                6078464 bytes
Database mounted.

Create the password file …

SQL> host orapwd file=D:\oracle\product\11.1.0\db_1\dbs\pwdprod.ora password=prod entries=10

If you are using Windows you must recreate the service so the correct name and parameter file are used:

SQL> host oradim -delete -sid test
Instance deleted.

SQL> host oradim -new -sid prod -intpwd prod -startmode a
Instance created.


Configure listener for new DBNAME

SQL> lsnrctl
SP2-0042: unknown command "lsnrctl" - rest of line ignored.
SQL> host lsnrctl

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 15-APR-2011 13:03
:34

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-117)(PORT=1521))
)
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-117)(PORT=1521))
)
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Produ
ction
Start Date                15-APR-2011 10:04:39
Uptime                    0 days 2 hr. 59 min. 6 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\11.1.0\db_1\network\admin\listener.o
ra
Listener Log File         d:\oracle\diag\tnslsnr\ramtech-117\listener\alert\log.
xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-117)(PORT=1521)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
Service "refer" has 2 instance(s).
  Instance "refer", status UNKNOWN, has 1 handler(s) for this service...
  Instance "refer", status READY, has 1 handler(s) for this service...
Service "referXDB" has 1 instance(s).
  Instance "refer", status READY, has 1 handler(s) for this service...
Service "refer_XPT" has 1 instance(s).
  Instance "refer", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
  Instance "test", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> exit

C:\>sqlplus sys/prod as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 15 13:09:01 2011

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

Connected to an idle instance.

SQL> create spfile from pfile='C:\test\testinit.ora' ;

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             255854164 bytes
Database Buffers          167772160 bytes
Redo Buffers                6078464 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
PROD      READ WRITE

See DBID & DBNAME has been changed.

Changing DBNAME only:

Backup the database.
Shutdown and mount the database..

C:\>sqlplus sys/sys@prod as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             260048468 bytes
Database Buffers          163577856 bytes
Redo Buffers                6078464 bytes
Database mounted.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>nid target=sys/sys dbname=stand setname=yes

DBNEWID: Release 11.1.0.6.0 - Production on Fri Apr 15 15:34:27 2011

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

Connected to database PROD (DBID=160375751)

Connected to server version 11.1.0

Control Files in database:
    D:\ORACLE\ORADATA\TEST\CONTROL01.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL02.CTL
    D:\ORACLE\ORADATA\TEST\CONTROL03.CTL

Change database name of database PROD to STAND? (Y/[N]) => y

Proceeding with operation
Changing database name from PROD to STAND
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - modified
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - modified
    Datafile D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\USERS01.DBF - wrote new name
    Datafile D:\ORACLE\ORADATA\TEST\TEMP01.DBF - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL01.CTL - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL02.CTL - wrote new name
    Control File D:\ORACLE\ORADATA\TEST\CONTROL03.CTL - wrote new name
    Instance shut down

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

SQL> create spfile from pfile='C:\test\testinit.ora';

File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1333676 bytes
Variable Size             255854164 bytes
Database Buffers          167772160 bytes
Redo Buffers                6078464 bytes
Database mounted.
SQL> alter database open;

Database altered.

As u see in case of changing DBNAME there is no need to open the database with reset logs option

Changing DBID Only:

Please follow the same steps as for Changing DBNAME and type the below command as below:

C:\>nid target=sys/sys@prod

This time you need to open the database with reset logs option.