In this article, we
will discuss using the nid utility to change database sid or database name
without dropping and recreating the database, before starting this activity
make sure you have a good backup of the database as a rollback strategy.
new database name: oracledb
Step 1: Create pfile from
spfile or take backup of pfile or password file
SQL> show
parameter pfile
-------------------- --------------------------------------
spfile
C:\APP\ORACLE\PRODUCT\11.2.0.4\DBHOME_1\
DATABASE\SPFILETECHNODB.ORA
SQL> create pfile from spfile;
File created.
SQL>
Step 2: shutdown the database and again open the database using the startup mount option
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3156725760
bytes
Fixed Size
2179896 bytes
Variable Size
1728056520 bytes
Database Buffers
1409286144 bytes
Redo Buffers
17203200 bytes
Database mounted.
Step 3: run nid utility with
syntax
nid
target=sys/<password>@technodb dbname=oracledb
C:\app\oracle>nid target=sys/<password>@technodb
dbname=oracledb
DBNEWID: Release 11.2.0.1.0 -
Production on Sat Jul 2 23:10:18 2022
Copyright (c) 1982, 2009, Oracle
and/or its affiliates. All rights reserved.
Connected to database TECHNODB (DBID=2727431841)
Connected to server version 11.2.0
Control Files in database:
C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL
C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL
Change database ID and database name TECHNODB to ORACLEDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2727431841
to 3726858970
Changing database name from TECHNODB
to ORACLEDB
Control File
C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - modified
Control File
C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - modified
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\SYSTEM01.DB - dbid changed, wrote new name
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\SYSAUX01.DB - dbid changed, wrote new name
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\UNDOTBS01.DB - dbid changed, wrote new name
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\USERS01.DB - dbid changed, wrote new name
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\EXAMPLE01.DB - dbid changed, wrote new name
Datafile
C:\APP\ORACLE\ORADATA\TECHNODB\TEMP01.DB - dbid changed, wrote new name
Control File
C:\APP\ORACLE\ORADATA\TECHNODB\CONTROL01.CTL - dbid changed, wrote new name
Control File
C:\APP\ORACLE\FLASH_RECOVERY_AREA\TECHNODB\CONTROL02.CTL - dbid changed, wrote
new name
Instance shut down
Database name changed to ORACLEDB.
Modify parameter file and generate a
new password file before restarting.
Database ID for database ORACLEDB
changed to 3726858970.
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.
Successfully changed database name
and ID.
DBNEWID - Completed successfully.
C:\app\oracle>
Step 4: As in above, we can see the database name is changed, and the database is shutdown now we need to change it in spfile as well. Open database in nomount mode and change database name using alter system command
C:\app\oracle >sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jul 2 23:12:19 2022
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3156725760
bytes
Fixed Size
2179896 bytes
Variable Size
1728056520 bytes
Database Buffers
1409286144 bytes
Redo Buffers
17203200 bytes
SQL>
SQL> alter system set
db_name=oracledb scope=spfile;
System altered.
SQL>
Step 5: if you are using
database on Linux/Unix- change the entry in oratab and if you are using the
database on windows, then create a new sid using oradim utility, I have my
database on windows, so I have created a new sid in windows services using
oradim and delete old sid
Instance created.
C:\windows\system32>oradim -delete
-sid technodb
Instance deleted.
C:\windows\system32>
Step 6: Now change spfile
name to with oracle database name from spfiletechnodb.ora
to spfileoracledb.ora and open the database step-wise as we need to open the database
using resetlogs options
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3156725760
bytes
Fixed Size
2179896 bytes
Variable Size
1728056520 bytes
Database Buffers
1409286144 bytes
Redo Buffers
17203200 bytes
SQL>
SQL> alter database mount;
Database altered.
SQL> show parameter db_name
NAME TYPE VALUE
----------------- -----------
------------------------------
db_name
string ORACLEDB
SQL>
SQL> alter database open resetlogs;
Step 7: Change entry in the
listener.ora and tnsnames.ora and just reload the listener using command
lsnrctl reload LISTENER
In this way, you can
database name has been changed, remember, only the database name can be changed
0 comments:
Post a Comment