Changing Oracle Database ID and Name (nid)

 Getting back into Oracle as a DBA I've been reading up and watching tutorials to try to get the old skills back and update to the new tools, last time I DBAed in anger we were still on Oracle 7 and 8i, 9i had been out a while and 10g was in early versions, but because much of our software was so old we weren't using it much.  I came across a tutorial on how to update the name and ID of a database.  This is useful if you have copied a database and want to rename the copy to stop it being confused with the old, or if you want to have it on the same machine as the old.  The database ID is an internal number used by Oracle to identify the database, it looks like the main reason you'd want to change the ID is if you use RMAN and want to have both in the same repository.

PLEASE NOTE: The below is based on a tutorial I watched and has not been tested, errors and omissions are expected.  Before using in production do a few practice runs on sandbox databases.  Proceed at your own risk.

Change Just Database ID

Shutdown the database in a consistent state:

sqlplus / as sysdba

shutdown immediate

startup restrict

shutdown

 Then startup in MOUNT mode:

startup mount

 At the Linux prompt enter:

nid TARGET=SYS/<password>@[Oracle SID]

You will then be prompted to confirm that you want to change the database ID.  Respond Y.  The process will then update the database files and notify you of the new database ID, then shut the database down.

Recreate the password file with orapwd.

Startup the database in MOUNT mode then open with the RESETLOGS option:

sqlplus / as sysdba

startup mount

alter database open resetlogs

Change Just Database Name

If you use an SPFILE (Oracle's binary parameters file) then create an editable text versions using:

sqlplus / as sysdba

create pfile from spfile

(It's a good idea to do this periodically anyway as a backup)

Shutdown the database in a consistent state:

sqlplus / as sysdba

shutdown immediate

startup restrict

shutdown

 Then startup in MOUNT mode:

startup mount

 At the Linux prompt enter:

nid TARGET=SYS/<password>@[Oracle SID] DBNAME=[New Database Name] SETNAME=YES

You will be prompted to confirm that you want to rename the database.  Respond Y.

The process will then update the database files.  Note that only the actual database files will be updated, most of the ancillary settings and parameter files will not be updated.

Recreate the password file using orapwd.

Edit the listener settings file with the new database name and the tnsnames.ora files on the server and each client with the new database name.

Edit the PFILE you created with the new database and save with the new filename.

Edit /etc/oratab to point to the new database name.

start the database explicitly pointing  to the PFILE you just edited

sqlplus / as sysdba

startup pfile="[path and filename of changed PFILE]"

If you usually use an SPFILE recreate it using:

create spfile from pfile


Change Database ID and name in one action

 If you use an SPFILE (Oracle's binary parameters file) then create an editable text versions using:

sqlplus / as sysdba

create pfile from spfile

(It's a good idea to do this periodically anyway as a backup)

Shutdown the database in a consistent state:

sqlplus / as sysdba

shutdown immediate

startup restrict

shutdown

 Then startup in MOUNT mode:

startup mount

 At the Linux prompt enter:

nid TARGET=SYS/<password>@[Oracle SID] DBNAME=[New Database Name]

 You will then be prompted to confirm that you want to change the database ID and name.  Respond Y.  The process will then update the database files and notify you of the new database ID, then shut the database down.  Note that, again, only the actual database files will be updated, most of the ancillary settings and parameter files will not be updated.

Recreate the password file with orapwd.

Edit the listener settings file with the new database name and the tnsnames.ora files on the server and each client with the new database name.

Edit the PFILE you created with the new database and save with the new filename.

Edit /etc/oratab to point to the new database name.

Startup the database in MOUNT mode explicitly pointing  to the PFILE you just edited then open with the RESETLOGS option:

sqlplus / as sysdba pfile="[path and filename of changed PFILE]"

startup mount

alter database open resetlogs

If you usually use an SPFILE recreate it using:

create spfile from pfile

 

Comments

Post a Comment

Popular posts from this blog

SQL*Server Import Export Wizard 2017 vs Office 64 bit

Not Oracle, but since we're having to multi-platform these days, it might be relevant to some. When you install SQL*Server Management Studio on 64-bit Windows, the Management Studio (SSMS, equivalent of SQL*Plus) is 64-bit, but it installs the 32-bit version of Import Export Wizard (SSIS). This is fine if you have the 32-bit version of Office installed or only import and export CSV or other non-Microsoft file types. If you have 64-bit Office and try importing an Excel spreadsheet, you will get an error message: "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)" Googling for this error message brings back a range of solutions (many dating back to SQL*Server 2008 and Office 2010), from installing the 64-bit Access Runtime redistributable to setting up a VM in Oracle Virtual Box and putting the SSMS/SSIS software on that, but not any form of Office. When I ran into this error today, we found, after some trial and err...
Read more

Preventing record deletion

This entry is partly an aide memoire for me, partly to try to get something that has been keeping me awake for the past hour or so out of my brain so I can sleep and partly in the hope that someone can suggest a way forward. A quick bit of background. Until April 06 most of our major systems were looked after by an external Faccilities Management company. In April 06 IT was kind of outsourced to a joint venture company, support of the systems transferred to that company and a couple of the DBAs transferred in as well under TUPE. A reccurring problem on one of the systems is that the users keep deleting records which, by law, they must not delete so we (actually one of the DBAs who transferred in who is responsible for that system) has to restore from backup to another machine and copy the deleted records over (she's tried using LogMiner but finds it too unwieldy). The core problem is that the application is faulty and lets the users delete the records when it shouldn't. The...
Read more

Oracle aims to secure future of Sparc, Solaris and Sun hardware

Interesting article on ComputerWeekly.com about how Oracle are looking to secure the future of Sparc, Solaris and Sun Hardware . They don't mention it but I wouldn't be surprised if we see 'Database as an Appliance' coming soon with Oracle Database running on Sun Hardware with management taken out of the hands of the local DBA and automated or made accessible only to Oracle themselves. This isn't a 'Death of the DBA' thing, the sort of sites that could go for something like that are probably the ones that don't have a DBA, just a support tech who has a couple of books, the sort of sites that probably run M$ SQLServer now. One thing they mention, as an aside, is that Oracle plan to OpenOffice.org to position it as a competitor to Microsoft Office. If they want to do that then I think the thing they really need to introduce to the suite is a desktop RDBMS to beat Access. Ideally something that can also act as a front end to Oracle with a tool that will...
Read more