Hello everyone, today we are going to share the way that exists in Oracle to rename a database. To perform this action, you use a command-line application found in the installation binaries, which is the NID command.
The NID command changes the header of the datafiles, controlfiles, etc. and the parameter db_name, although manually you have to generate the init.ora file, change the paths of the files, and recreate the spfile if it is used in the database.
Steps to rename an Oracle database by changing the SID
1. Stop the instance in an orderly manner
It is not necessary to make a stop with the ABORT option to rename an Oracle database, but it is advisable to make a checkpoint and the logfile switches necessary for the database to be cleaned before carrying out the process.
[oracle@server1 admin] echo $ORACLE_SID
[oracle@server1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 18.104.22.168.0 Production on Mon Feb 10 13:37:36 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> alter system checkpoint;
SQL> alter system switch logfile;
SQL> alter system switch logfile
SQL> shutdown immediate;
ORACLE instance shut down.
2. Set up the database
The renaming process is done with the database in MOUNT mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1152450560 bytes
Fixed Size 2252584 bytes
Variable Size 352321752 bytes
Database Buffers 788529152 bytes
Redo Buffers 9347072 bytes
3. Create the PFILE parameter file
You can use the SPFILE of the current database to generate the parameter file, changing the value of the parameter db_name with the new name that the database will have after the change.
SQL> create pfile='/tmp/init_nuevo.ora' from spfile;
$ cp /tmp/init_nuevo.ora $ORACLE_HOME/dbs/initnewsid.ora
4. Run the NID command
The NID command is an application that is located in the Oracle software installation directory, which is given by the environment variable ORACLE_HOME, and in the BIN folder.
[oracle@server1 admin]$ export ORACLE_SID=oldsid
[oracle@server1 admin]$ nid target=/ dbname=newsid
DBNEWID: Release 22.214.171.124.0 - Production on Mon Feb 10 13:40:47 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database OLDSID (DBID=2695375495)
Connected to server version 11.2.0
Control Files in database:
Change database ID and database name OLDSID to NEWSID? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2695375495 to 4057109727
Changing database name from OLDSID to NEWSID
Control File +REDO1/oldsid/controlfile/current.256.838662091 - modified
Control File +REDO2/oldsid/controlfile/current.256.838662091 - modified
Datafile +DATA/oldsid/datafile/system.259.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/sysaux.258.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/undotbs1.257.83866210 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/users.260.83866211 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/app1tab.267.83873288 - dbid changed, wrote new name
Datafile +DATA/oldsid/datafile/app2.266.83873290 - dbid changed, wrote new name
Datafile +DATA/oldsid/tempfile/temp.256.83866210 - dbid changed, wrote new name
Control File +REDO1/oldsid/controlfile/current.256.838662091 - dbid changed, wrote new name
Control File +REDO2/oldsid/controlfile/current.256.838662091 - dbid changed, wrote new name
Instance shut down
Database name changed to NEWSID.
Modify parameter file and generate a new password file before restarting.
Database ID for database NEWSID changed to 4057109727.
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.
5. Open the database with the new name
Once the name change is done, you have to open the database with the RESETLOGS option. Once the database is opened, the SPFILE can be regenerated with the previously created init.ora file.
[oracle@server1 ~]$ export ORACLE_SID=newsid
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> select name from v$database;
You can create the spfile with the following statement:
SQL> CREATE SPFILE='+DATA' from pfile;
You can consult the information of this tool in the official documentation of the manufacturer, in the following link:
We hope you found it useful. See you in a new installment about PostgreSQL.
If you don’t want to miss tricks like this and stay up to date with this world, subscribe to our newsletter. We will keep you informed with only one email per month.