Oracle - How to rename an Oracle database ORACLE_SID

How to rename an Oracle database ORACLE_SID

We can change database name using DBNEWID utility nid.

Steps Outlined:

               

1.       Start the database in mount mode

2.       Invoke nid command using user having sysdba privilege. Substitute DBNAME with new database name.

3.       Shutdown database.

4.       Change db_name=CATDB in initDUPDB1.ora

5.       Start database in mount mode. Ignore the error.

6.       Shutdown database.

7.       Create new password file for new database name.

8.       Set ORACLE_SID to new value.

9.       Rename the SPFILE or pfile whichever you are using

10.   Alter the listener.ora and tnsnames.ora with new database SID

11.   Reload listener to affect the change.

12.   Start the database in mount mode.

13.   Open database in resetlogs mode.

14.   Verify whether dbname and instance name has changed.

Start the database in mount mode

[oracle@localhost dbs]$ . oraenv

ORACLE_SID = [ORADB1] ? DUPDB1

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 27 13:42:35 2017

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size               2253824 bytes

Variable Size          314575872 bytes

Database Buffers        96468992 bytes

Redo Buffers             4247552 bytes

Database mounted.

Invoke nid command using user having sysdba privilege. Substitute DBNAME with new database name.

[oracle@localhost dbs]$ nid TARGET=sys/oracle@DUPDB1 DBNAME=CATDB

DBNEWID: Release 11.2.0.4.0 - Production on Wed Sep 27 13:45:27 2017

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

Connected to database DUPDB1 (DBID=800698880)

Connected to server version 11.2.0

Control Files in database:

    /u01/app/oracle/oradata/DUPDB1/control01.ctl

    /u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl

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

Proceeding with operation

Changing database ID from 800698880 to 2501525751

Changing database name from DUPDB1 to CATDB

    Control File /u01/app/oracle/oradata/DUPDB1/control01.ctl - modified

    Control File /u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl - modified

    Datafile /u01/app/oracle/oradata/DUPDB1/system01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/sysaux01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/undotbs01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/users01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/example01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/tbs_restore01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/DUPDB1/temp01.db - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/DUPDB1/control01.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/fast_recovery_area/DUPDB1/control02.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to CATDB.

Modify parameter file and generate a new password file before restarting.

Database ID for database CATDB changed to 2501525751.

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.

Shutdown database.

[oracle@localhost dbs]$

shutdown immediate

Change db_name=CATDB in initDUPDB1.ora

Start database in mount mode. Ignore the error.

SQL> startup mount pfile=initDUPDB1.ora;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size               2253824 bytes

Variable Size          314575872 bytes

Database Buffers        96468992 bytes

Redo Buffers             4247552 bytes

ORA-01103: database name 'CATDB' in control file is not 'DUPDB1'

Shutdown database.

shutdown immediate

Create new password file for new database name.

[oracle@localhost dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwCATDB.ora password=oracle entries=10

Set ORACLE_SID to new value.

ORACLE_SID=CATDB; export ORACLE_SID

Rename the SPFILE or pfile whichever you are using

[oracle@localhost dbs]$ mv initDUPDB1.ora initCATDB.ora

Alter the listener.ora and tnsnames.ora with new database SID

Reload listener to affect the change.

lsnrctl reload

Start the database in mount mode.

[oracle@localhost dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 27 14:44:49 2017

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

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  417546240 bytes

Fixed Size               2253824 bytes

Variable Size          331353088 bytes

Database Buffers        79691776 bytes

Redo Buffers             4247552 bytes

Database mounted.

Open database in resetlogs mode.

SQL> alter database open resetlogs;

Database altered.

Verify whether dbname and instance name has changed.

SQL> select name,open_mode from v$database;

NAME     OPEN_MODE

--------- --------------------

CATDB    READ WRITE

SQL> select instance_name from v$instance;

INSTANCE_NAME

----------------

CATDB

Our database rename is complete. For complete rename, you need to change all references to old database name.

You can see still datafiles and logfiles are refering to a directory with old dbname.

You can change it by method of renaming datafiles and log files. I am not explaining it here.

SQL>

SQL> select file_name , tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files;

FILE_NAME                                TABLESPACE_NAME    AUT STATUS

------------------------------------------------ ------------------ --- ---------

/u01/app/oracle/oradata/DUPDB1/system01.dbf     SYSTEM          YES AVAILABLE

/u01/app/oracle/oradata/DUPDB1/sysaux01.dbf     SYSAUX          YES AVAILABLE

/u01/app/oracle/oradata/DUPDB1/undotbs01.dbf    UNDOTBS1        YES AVAILABLE

/u01/app/oracle/oradata/DUPDB1/users01.dbf      USERS           YES AVAILABLE

/u01/app/oracle/oradata/DUPDB1/example01.dbf    EXAMPLE         YES AVAILABLE

SQL> select member from v$logfile;

MEMBER

-----------------------------------------------

/u01/app/oracle/oradata/DUPDB1/redo03.log

/u01/app/oracle/oradata/DUPDB1/redo02.log

/u01/app/oracle/oradata/DUPDB1/redo01.log

Finally when everything is done, take a good backup of database as all old backups are obsolete now.

Backup the database.