Thursday, December 1, 2011

How To Rename Oracle SID, DB Name

How To Rename Oracle SID, DB Name :
=====================================================
All,

Please backup your database before you do any DDL or structural changes.. please take offline and/or online backups, not just one copy but couple of backups in each mode one copy.. ... backup and backup database .. please...

This may not be the great post but one can have a quick look on how to rename oracle sid and db name. As you know all there are two ways i think we can change the oracle SID , db name.

1. using NID, orapwd, oradim utilities - simple way of doing it. works above 9i otherwise correct me.
2. Traditional method ( re-creating the control files etc..)

Please note that the following steps THAT I ONLY tested in oracle 11g test databases and windows environment and it does not mean that they work as exactly as shown here.. it all depends on what OS one use and what database one use and other validations.. please use your own judgement before you do anything on the database.. since it is a database.. one should be very careful no matter what web pages says.. its you who do db changes and responsible but no one else..

windows OS specific:
ORADIM -DELETE -SID ABCD
oradim -new -sid database intpwd password=password -startmode a -pfile sfiledatabase.ora

what should be used in place of those in UNIX:
i don't see any specific replacements .. but
Ensure that the lsnrctl reloaded if db is not in accessible state (valid for all OS's), if not reload
if DB instance can not be started, ensure no process are running with olddb after rename is done, stop all those old db processes before you bounce new db instance

more important after renaming : SET ORACLE_SID, ORACLE_HOME AND OTHER ORACLE SPECIFIC VARIABLES WITHOUT FAIL JUST BEFORE BOUNCING THE NEW DB ONLY AFTER RENAME IS DONE..



Let me start with first way of doing it. Here is the illustration..

1. using NID, orapwd, oradim utilities :
-----------------------------------------
Existing DB/SID: ABCD
New DB/SID: SIRI ( ABCD would be made SIRI)

Please note down existing database spfile name location and this file name has to be renamed just before starting the new db service.

C:\Users\Home>set ORACLE_SID=ABCD

C:\Users\Home>SQLPLUS "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 20:18:56 2011

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> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string F:\APP\HOME2\PRODUCT\11.2.0\DB
HOME_1\DATABASE\SPFILEABCD.ORA
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 188747540 bytes
Database Buffers 444596224 bytes
Redo Buffers 5566464 bytes
Database mounted.
SQL>
SQL>SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Home>nid TARGET=SYS/ORACLE@ABCD DBNAME=SIRI

DBNEWID: Release 11.2.0.1.0 - Production on Thu Dec 1 20:26:30 2011

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

Connected to database ABCD (DBID=51505643)

Connected to server version 11.2.0

Control Files in database:
F:\APP\HOME2\ORADATA\ABCD\CONTROL01.CTL
F:\APP\HOME2\FLASH_RECOVERY_AREA\ABCD\CONTROL02.CTL

Change database ID and database name ABCD to SIRI? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 51505643 to 3654386949
Changing database name from ABCD to SIRI
Control File F:\APP\HOME2\ORADATA\ABCD\CONTROL01.CTL - modified
Control File F:\APP\HOME2\FLASH_RECOVERY_AREA\ABCD\CONTROL02.CTL - modified
Datafile F:\APP\HOME2\ORADATA\ABCD\SYSTEM01.DB - dbid changed, wrote new nam
e
Datafile F:\APP\HOME2\ORADATA\ABCD\SYSAUX01.DB - dbid changed, wrote new nam
e
Datafile F:\APP\HOME2\ORADATA\ABCD\UNDOTBS01.DB - dbid changed, wrote new na
me
Datafile F:\APP\HOME2\ORADATA\ABCD\USERS01.DB - dbid changed, wrote new name

Datafile F:\APP\HOME2\ORADATA\ABCD\TEMP01.DB - dbid changed, wrote new name
Control File F:\APP\HOME2\ORADATA\ABCD\CONTROL01.CTL - dbid changed, wrote n
ew name
Control File F:\APP\HOME2\FLASH_RECOVERY_AREA\ABCD\CONTROL02.CTL - dbid chan
ged, wrote new name
Instance shut down

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



C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 20:28:45 2011

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

Connected to an idle instance.

SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 188747540 bytes
Database Buffers 444596224 bytes
Redo Buffers 5566464 bytes
ORA-01103: database name 'SIRI' in control file is not 'ABCD'


SQL>
SQL> alter system set db_name=SIRI SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> EXIT
C:\Users\Home>orapwd file=F:\app\Home2\product\11.2.0\dbhome_1\database\pwdSIRI.
ora password=oracle entries=50

Before service being created please do the following steps:
Please rename existing old database spfile name i.e. spfileABCD.ora to new db database being created spfileSIRI.ora.
go to the SP file location and rename as requried.

C:\Users\Home>
C:\Users\Home>ORADIM -DELETE -SID ABCD
Instance deleted.

C:\Users\Home>oradim -new -sid SIRI -intpwd password=oracle -startmode a -pfile
F:\app\Home2\product\11.2.0\dbhome_1\database\sfileSIRI.ora

C:\Users\Home>set ORACLE_SID=SIRI

C:\Users\Home>lsnrctl reload
C:\Users\Home>set ORACLE_SID=SIRI

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 20:48:32 2011

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 188747540 bytes
Database Buffers 444596224 bytes
Redo Buffers 5566464 bytes
Database mounted.
SQL> alter database open resetlogs
2 ;

Database altered.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
SIRI READ WRITE

SQL> select instance, status from v$thread;

INSTANCE
--------------------------------------------------------------------------------

STATUS
------
siri
OPEN
SQL> exit;

C:\Users\Home>sqlplus sys@siri as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 1 21:42:56 2011

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

Enter password:

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>


C:\Users\Home>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-DEC-2011 21:41
:28

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Home-PC)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 01-DEC-2011 19:01:17
Uptime 0 days 2 hr. 46 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\Home2\product\11.2.0\dbhome_1\network\admin\lis
tener.ora
Listener Log File f:\app\home2\diag\tnslsnr\Home-PC\listener\alert\log.x
ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Home-PC)(PORT=1521)))
Services Summary...
Service "ABCDXDB" has 1 instance(s).
Instance "siri", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "siri" has 1 instance(s).
Instance "siri", status READY, has 1 handler(s) for this service...

C:\Users\Home>tnsping siri

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 01-DEC-2
011 21:22:39

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

Used parameter files:
F:\app\Home2\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Home-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SIRI)))
OK (364160 msec)

=================================================================

2. Traditional method...
----------------------------------
How to rename oracle database SID, DB name by re-creating the control file..

Please take complete backup of your database in both offline and online. This is must to do ... please take db complete backup , backup.....

Old SID/DBID/db name: Blue
New SID/DBID/DBName being created: RED (Blue would be made RED)

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string F:\APP\HOME2\PRODUCT\11.2.0\DB
HOME_1\DATABASE\SPFILEBLUE.O
RA
SQL> alter system checkpoint;

System altered.

SQL> alter session set tracefile_identifier="renamedb"
2 ;

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> shutdown immediate;

SQL> startup mount;

SQL> CREATE PFILE='F:\app\Home2\product\11.2.0\dbhome_1\database\initRED.ora' from spfile;

File created.

SQL> shutdown immediate;
----------------------------------
1. Change all occurences of olddb name to newdb name in newly created initRED.ora file.

see my initRED.ora

red.__db_cache_size=419430400
red.__java_pool_size=4194304
red.__large_pool_size=4194304
red.__oracle_base='F:\app\Home2'#ORACLE_BASE set from environment
red.__pga_aggregate_target=427819008
red.__sga_target=641728512
red.__shared_io_pool_size=0
red.__shared_pool_size=205520896
red.__streams_pool_size=0
*.audit_file_dest='F:\app\Home2\admin\red\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='F:\app\Home2\oradata\red\control01.ctl','F:\app\Home2\flash_recovery_area\red\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='red'
*.db_recovery_file_dest='F:\app\Home2\flash_recovery_area'
*.db_recovery_file_dest_size=1941962752
*.diagnostic_dest='F:\app\Home2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=redXDB)'
*.memory_target=1068498944
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

2. Open tracefile which has create controlfile statement ( the trace file we generated in above step)

change all the directories named with old db name to new db being created.
change tracefile content similar to the below.
Ensure no control files to be used... rename all the old control files something else in file system. no existing control files should be used....

my cotrol file script info is:

--STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set DATABASE "RED" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\APP\HOME2\ORADATA\red\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'F:\APP\HOME2\ORADATA\red\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'F:\APP\HOME2\ORADATA\red\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'F:\APP\HOME2\ORADATA\red\SYSTEM01.DBF',
'F:\APP\HOME2\ORADATA\red\SYSAUX01.DBF',
'F:\APP\HOME2\ORADATA\red\UNDOTBS01.DBF',
'F:\APP\HOME2\ORADATA\red\USERS01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\APP\HOME2\ORADATA\red\TEMP01.DBF'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

SQL> startup nomount pfile=f:\initRED.ora
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 188747540 bytes
Database Buffers 444596224 bytes
Redo Buffers 5566464 bytes

SQL> @f:\red.sql

Control file created.


Database altered.


Tablespace altered.

SQL> select name from v$database;

NAME
---------
RED

SQL> EXIT;

CHANGE ALL the OCCURENCES OF OLD DBNAME name TO NEW DB NAME IN ALL THE NETWORK CONFIGURATION FILES.
please check network config files, all the three files, tnsnames.ora, listener.ora, sqlnet.ora. just to be sure everything is okay.


C:\Users\Home>sqlplus "/as sysdba"
SQL> select name from v$database;

NAME
---------
RED

SQL> select instance, status from v$thread;

INSTANCE
--------------------------------------------------------------------------------

STATUS
------
BLUE
OPEN

SQL> shut immediate;
SQL> exit;

oradim -delete -sid BLUE
oradim -new -sid RED -intpwd oracle -startmode a -pfile f:\spfileRED.ora



C:\Users\Home>oradim -delete -sid BLUE
Unable to stop service, OS Error = 1062
Instance deleted.

C:\Users\Home>oradim -new -sid RED -intpwd oracle -startmode a -pfile f:\spfile
ED.ora
Instance created.

C:\Users\Home>set ORACLE_SID=RED

C:\Users\Home>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 2 05:47:28 2011

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

Connected to an idle instance.

SQL> startup pfile='f:\initRED.ora"
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 213913364 bytes
Database Buffers 419430400 bytes
Redo Buffers 5566464 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> show parameter pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> select name from v$database;

NAME
---------
RED

SQL> select instance, status from v$thread;

INSTANCE
-------------------------------------------------------------------------------

STATUS
------
red
OPEN


SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:\APP\HOME2\ORADATA\RED\CONTR
OL01.CTL, F:\APP\HOME2\FLASH_R
ECOVERY_AREA\RED\CONTROL02.CTL
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> create spfile from pfile='initRED.ora';

File created.

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

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 213913364 bytes
Database Buffers 419430400 bytes
Redo Buffers 5566464 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string F:\APP\HOME2\PRODUCT\11.2.0\DB
HOME_1\DATABASE\SPFILERED.ORA
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Home>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 02-DEC-2011 06:11
:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Home-PC)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 02-DEC-2011 05:47:13
Uptime 0 days 0 hr. 24 min. 11 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\app\Home2\product\11.2.0\dbhome_1\network\admin\lis
tener.ora
Listener Log File f:\app\home2\diag\tnslsnr\Home-PC\listener\alert\log.x
ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Home-PC)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "red" has 1 instance(s).
Instance "red", status READY, has 1 handler(s) for this service...
Service "redXDB" has 1 instance(s).
Instance "red", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\Users\Home>sqlplus sys@red as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 2 06:12:19 2011

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

Enter password:

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>



----- Have a fun in working in oracle technologies --- Cheers-------

3 comments:

  1. Awesome!!! Works perfectly!!!

    ReplyDelete
  2. It is glad to hear that it worked, Keep exploring the oracle technology..

    ReplyDelete