So, you renamed your database using the
nid utility as outlined here but now you need (or want) to change all the file system names to suit. Read on.
In the following example, we have two mount points for the database. Files on these mounts are spread all over a pile of separate discs making up the LUN – so it’s not as bad as it looks!
The two mounts are wrongly named at the moment since we changed the database name using
nid and we would like to tidy things up. The current names are:
What we would like to see after the renaming is the following:
First of all, you need a current pfile. If you don’t have one already (from the rename exercise) then create one as follows:
create pfile='/home/oracle/initnew_sid.ora' from spfile;
We also need a control file trace taking, so we do this next:
alter database backup controlfile
to trace as '/home/oracle/new_sid_controlfile.sql';
It should be obvious that you wouldn’t want the pfile and control file trace to be located on the file system that is about to be moved. If they are, you will have to wait for the Sys Admins to complete the remounting exercise before you can do your own editing in preparation for restarting the database.
The next thing to do is backup the database. Shut it down and take a cold backup in your preferred manner –
RMAN or some other utility. Better safe than sorry after all.
In a shell session, as the oracle user, remove (or move) the
spfilenew_sid.ora files, if present, from $ORACLE_HOME/dbs:
Edit the newly generated parameter file as required and move it from
$ORACLE_HOME/dbs/initnew_sid.ora. Things to change will include some or all of the following:
Anything that has been moved (or will be moved) by renaming the mount points will require changing.
Next, the control file trace that was created needs to be edited. There is much waffle to be removed and a choice of script to generate. Edit the file
- Find the section of the trace file that is applicable to your wish to start the database with
RESETLOGSor not. For
NORESETLOGSit is the first section, near the top of the trace, otherwise it is the second section near the bottom.
For my own databases, I have chosen not to reset the logs, so I use the top section of the trace file.
- Delete all the comment lines above the section you want. You need to start the script from the
STARTUP NOMOUNTcommand in the appropriate section.
- Scroll down, but retain all the lines from (and including)
STARTUP NOMOUNTuntil you find a comment line that looks something like
-- End of tempfile additions. Delete that line, and everything else to the end of file.
You should now be in possession of a script that begins with
STARTUP NOMOUNT and ends with a few lines adding your
TEMPFILEs back into your temporary tablespace9s).
- Run a global search and replace to ensure that all filenames match with the new locations.
- If the database is in ARCHIVELOG mode, pay particular attention to the commented out commands to
ALTER DATABASE REGISTER LOGFILEand make sure you enter the full path to an existing log file. You can ignore this part if your database is running in NOARCHIVELOG mode.
- Save the file.
When the Sys Admins have given you back the file systems mounted on their new locations, it’s a simple matter to:
- Connect to the database
When that has finished, everything should now be in order. You may create a new spfile in the usual manner:
create spfile='/home/oracle/spfilenew_sid.ora' from pfile;
host cp /home/oracle/spfilenew_sid.ora $ORACLE_HOME/dbs/
Nothing to it!