DBAQuest.com

How to change the Oracle database name on UNIX
Date Friday, November 16 @ 22:03:48
Topic Oracle Administration

Changing the name of a database is similar to database cloning. You will need the database datafiles, modified init.ora file and modified backup controlfile. Always take a backup of the database. If your database is in Archive log mode, the previous days’ hot backup and archive logs would be sufficient. Here we will change the database called TEST to a new name - PROD.
Pre database name change tasks

Make sure your ORACLE_BASE, ORACLE_HOME and ORACLE_SID environment variables are set properly. In Korn shell, you can find out using the following commands:
```
$ echo $ORACLE_BASE
/u01/app/oracle
$ echo $ORACLE_HOME
/u01/app/oracle/product/8.1.7
$ echo $ORACLE_SID
test
```
If these variables are not set, use the following commands to set them. Replace the sample values with your own.
```
$ export ORACLE_BASE=/u01/app/oracle
$ export ORACLE_HOME=/u01/app/oracle/product/8.1.7
$ export ORACLE_SID=test
```

Login using server manager or SQL*Plus into the database whose name you wish to change.
```
$ svrmgrl
SVRMGR> connect / as sysdba;
```

Doubly verify the name of the database and instance by querying v$instance and v$database.
```
SVRMGR> select name from v$database;
SVRMGR> select instance_name from v$instance;
```

Get a list of control files, redo log files and datafiles for your records. You’ll need only the datafiles during the database name change. New controlfiles and redo log files will be created.
```
SVRMGR> select name from v$datafile;
SVRMGR> select member from v$logfile;
SVRMGR> select name from v$controlfile;
```

Switch logfiles to update header information.
```
SVRMGR> alter system switch logfile;
```

Get the current log sequence number and the redo log member associated with it. You may need this log for media recovery.


SVRMGR> select v$logfile.member, v$log.status from v$log, v$logfile
   2> where v$log.status = 'CURRENT' and 
   3> v$log.group# = v$logfile.group#;

Get a copy of a text version of the database controlfile by issuing the following command. The command creates a text version of the controlfile in the form of an SQL script.
```
SVRMGR>alter database backup controlfile to trace resetlogs;
```
This file is written to the directory designated as your User Dump destination. You can find out which directory that is by the following commands:
```
SVRMGR> show parameter user_dump_dest;
Or	
SVRMGR> select * from v$parameter where name=’user dump dest’;
```

Shutdown the database. I like to do a "shutdown immediate" because it’s quick and clean. Do not do a “shutdown abort” as the datafiles and controlfile headers will not be synchronized.
```
SVRMGR> shutdown immediate;
```

Now go to the user dump directory. Do an ls -lart at the Unix prompt, which will sort the files by time, and then you will find the most recently generated trace file at the bottom of the list. The filename will generally be called orannnn.trc, where nnnn is some number. Edit the file using your favorite editor and remove everything else except the CREATE CONTROLFILE statement.

Modify the CREATE CONTROLFILE statement by changing the keyword REUSE to SET, and the database name TEST to PROD. Also, do a search and replace of all occurrences of TEST to PROD. In vi you can do it by typing :%s/test/prod. Rename the file to something meaningful, like prod_controlfile.sql. Your “create controlfile” script should look like this after you are done.


CREATE CONTROLFILE SET DATABASE “PROD" RESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 254
    MAXINSTANCES 1
    MAXLOGHISTORY 907
LOGFILE
  GROUP 1 '/U01/ORACLE/ORADATA/PROD/REDO01A.LOG'  SIZE 20M,
  GROUP 2 '/U01/ORACLE/ORADATA/PROD/REDO02A.LOG'  SIZE 20M,
  GROUP 3 '/U01/ORACLE/ORADATA/PROD/REDO03A.LOG'  SIZE 20M
DATAFILE
  '/U02/ORACLE/ORADATA/PROD/SYSTEM01.DBF',
  '/U02/ORACLE/ORADATA/PROD/RBS01.DBF',
  '/U02/ORACLE/ORADATA/PROD/USERS01.DBF',
  '/U02/ORACLE/ORADATA/PROD/TEMP01.DBF',
  '/U02/ORACLE/ORADATA/PROD/TOOLS01.DBF',
  '/U02/ORACLE/ORADATA/PROD/INDX01.DBF',
  '/U02/ORACLE/ORADATA/PROD/DR01.DBF',
CHARACTER SET WE8ISO8859P1
;

Now, go to the directory that holds your “init.ora” file. This is usually $ORACLE_HOME/dbs. Make a copy of the “inittest.ora” file and rename it “initprod.ora”. Edit the file. Again, do a search and replace of “test” to “prod”. Note down the location of the dump directories (udump, bdump, cdump, arch). You will need to create these directories under the new location. This is one of the most common things that is forgotten. ) I keep the new “initprod.ora” file and the “prod_controlfile.sql” script in one location for ease of use, usually in $ORACLE_HOME/dbs.

Now, it’s time to move the datafiles to their new location. Here, for simplicity, all the datafiles are shown to reside on one mount point, /u02. Traverse down to the directory containing the subdirectory called “test”, your old database name. Make a new subdirectory called “prod” and move all the datafiles there.
```
$ cd /u02/oracle/oradata
$ mkdir prod
$ mv ./test/*.dbf ./prod/
```
If you have Datafiles on different mount points, repeat the procedure on each one.

Also, make sure you create the appropriate udump, bdump, cdump, arch directories under $ORACLE_BASE/admin/$ORACLE_SID.
```
$ cd /u01/app/oracle/admin
$ mkdir prod
$ cd prod
$ mkdir udump
$ mkdir bdump
$ mkdir pfile
$ mkdir arch 
```

Move the old redo logfiles and controlfiles to a separate location, like $HOME. You don’t need these unless recovery is required.

Changing the database name

Set the ORACLE_SID.
```
$ export ORACLE_SID=prod
```

Connect to server manager and start the instance.


SVRMGR> startup nomount pfile=/u01/app/oracle/product/8.1.7/dbs/initprod.ora

Run the create controlfile script.

SVRMGR> @/u01/app/oracle/product/8.1.7/dbs/prod_controlfile.sql

Recover database using backup controlfile;
```
SVRMGR> recover database using backup controlfile;
```
If recovery is needed, it will ask for a particular log sequence, usually the current. Since we already know which is the current redo logfile, we can give the directory where it’s located. In our case, it’s $HOME.

Now it should say media recovery completed.

Open the database resetlogs.
```
SVRMGR> alter database open resetlogs;
```

Post database name change tasks

Make sure the name change went fine.
```
SVRMGR>select * from v$instance.
```

Switch a logfile and check the archive destination to see if the redo log files are archived properly.
```
SVRMGR>alter system switch logfile;
```

Change entries in tnsnames.ora, listener.ora and /etc/oratab to reflect the new database name. Change any crontab entries that you may have to point to the new database.

This article comes from DBAQuest.com
http://www.dbaquest.com

The URL for this story is:
http://www.dbaquest.com/modules.php?op=modload&name=News&file=article&sid=5