The Dbnewid of Oracle Tools

Dbnewid is a tool that Oracle provides to modify database dbid and dbname.

Before we introduce this tool, we must rebuild the control file if we want to change the database name of the database. However, it is not possible to modify the dbid of the database. As is known to all, dbid is a unique identifier for a database. Rman is based on the dbid of the database to distinguish between different databases.

Dbnewid solves this problem by providing the following three options:

1> only Modify dbid

2> only Modify dbname

3> simultaneous modification of dbid and dbname

Note: when the dbid of a database is modified, its previous backup and archive logs will not be available. And when you reopen the database, add the Resetlogs option, which re-creates the online logs and resets their serial numbers to 1. It is recommended that the library be fully prepared after modifying the dbid of the database.

If you only modify the dbname of the database, the previous backup and archive logs are not affected. However, the db_name of the parameter file must be modified, otherwise the following error will be reported during the reopening of the database: ora-01103:database name ' ORCL ' in control file was not ' SZ '. Indicates that the dbname of the control file and the parameter file do not match, you need to modify the parameter file. Also, you need to change the password file.

the syntax for using DBNEWID is as follows :

Nid TARGET = [username]/[password] [@service_name]
[REVERT = {YES | NO}
| DBNAME = New_db_name [SETNAME = {YES | NO}]
[LOGFILE = LOGFILE [APPEND = {YES | NO}] [help = {YES | NO}]

which

Target enter the user name and password, and note that the user must have SYSDBA permissions. For example Target=sys/oracle, of course, if we operate locally on the operating system, we can use a backslash connection, that is target=/

Revert indicates whether a failed dbid operation requires fallback. The default is No. Note that if the dbid modification of the database succeeds, the operation cannot be rolled back. The fallback operation is only for failed dbid modification operations.

DBNAME Specifies the new database name.

SetName default is no, the database will modify both dbid and dbname. If yes is specified, the database will only modify the dbname

LogFile log files, logging operation information, the default is to overwrite the last log file. If append is set to Yes, the operation information will be appended to the previous log file. Append default is No.

Help is used to prompt dbnewid grammar rules. Default is No

Below, we'll show you how to modify dbid and dbname

First, close the database and reboot to Mount state

sql> shutdown Immediate--must be a consistent close

Sql> Startup Mount

Ii. modification of dbid and dbname

[email protected] ~]$ nid target=sys/oracle Dbname=victor-the original dbname is ORCL.

Let's take a look at the output

Dbnewid:release11.2.0.1.0 -Production onThu Sep -  to:xx: the  theCopyright (c)1982, the, Oracleand/orits affiliates. Allrights reserved. Connected to DatabaseORCL (DBID=1386938297) Connected toServer version11.2.0Control Filesinch Database:    /U01/App/Oracle/Oradata/Sz/Control01.ctl/U01/App/Oracle/Flash_recovery_area/Sz/Control02.ctlchangeDatabaseId and DatabaseName ORCL toVICTOR? (Y/[N])=yproceeding withoperationchangingDatabaseId from 1386938297  to 431720306changingDatabaseName fromORCL toVICTOR ControlFile /U01/App/Oracle/Oradata/Sz/Control01.ctl-Modified ControlFile /U01/App/Oracle/Flash_recovery_area/Sz/Control02.ctl-modified DataFile/U01/App/Oracle/Oradata/Sz/System01.db-dbid changed, wrote new name DataFile/U01/App/Oracle/Oradata/Sz/Sysaux01.db-dbid changed, wrote new name DataFile/U01/App/Oracle/Oradata/Sz/Undotbs01.db-dbid changed, wrote new name DataFile/U01/App/Oracle/Oradata/Sz/Users01.db-dbid changed, wrote new name DataFile/U01/App/Oracle/Oradata/Sz/Temp01.db-dbid changed, wrote new name ControlFile /U01/App/Oracle/Oradata/Sz/Control01.ctl-dbid changed, wrote new name ControlFile /U01/App/Oracle/Flash_recovery_area/Sz/Control02.ctl-dbid changed, wrote new name Instance shut downDatabaseName changed toVICTOR. Modify parameterfile  andGenerate a new passwordfilebefore restarting.DatabaseId for DatabaseVICTOR changed to 431720306. AllPrevious backups andArchived Redo Logs forThisDatabaseis unusable.Database  is  notAware ofPrevious backups andArchived logsinchRecovery area.Databasehas beenshutdown,Open Database  withResetlogsoption. succesfully changedDatabaseName andID. Dbnewid-Completed succesfully.

The output finally indicates that the modification was successful.

Note: after the NID command is issued, the Dbnewid first verifies the data file and controls the validity of the file. If the checksum is passed, Dbnewid will prompt to continue with the operation, such as the change database ID in the above output and the database name ORCL to VICTOR? (Y/[n]). This prompt is not available if the log file is indicated. Next, Dbnewid modifies the dbid of each data file, including offline data files and read-only data files. Then close the database and exit.

In the 10.1 and later versions, the operation is also recorded in the alarm log. Such as

***Dbnewid Utility started***DBID 'll be changed from 1386938297  toNew DBID of 431720306  for DatabaseOrcldbname 'll be changed fromORCL toNew DBNAME ofvictorstarting datafile conversiondatafile conversion completeDatabaseName changed toVICTOR. Modify parameterfile  andGenerate a new passwordfilebefore restarting.DatabaseId for DatabaseVICTOR changed to 431720306. AllPrevious backups andArchived Redo Logs forThisDatabaseis unusable.Databasehas beenshutdown,Open  withResetlogsoption. succesfully changedDatabaseName andID.***Dbnewid Utility finished succesfully***

Third, modify the parameter file

sql> create pfile= '/home/oracle/victor.pfile ' from SPFile;

[Email protected] trace]$ Vim/home/oracle/victor.pfile

Db_name= ' ORCL ' modified to db_name= ' Victor '

Iv. restarting the database

Sql> create SPFile from pfile= '/home/oracle/victor.pfile ';

Sql> Startup Mount

sql> ALTER DATABASE open resetlogs;

Sql> Show Parameter name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Db_file_name_convert string
Db_name string Victor
Db_unique_name string Victor
Global_names Boolean FALSE
instance_name string ORCL
Lock_name_space string
Log_file_name_convert string
Service_names string Victor

Visible, dbname modified successfully. Of course, the instance name is still the original ORCL.

If you also need to modify the instance name to Victor, the command is as follows:

Sql> alter system set Instance_name= ' Victor ' Scope=spfile;

Summary: This example demonstrates the simultaneous modification of dbid and dbname.

1> If you just modify dbid, the second step above does not specify the dbname parameter, which is the NID target=sys/oracle. However, when you reopen the database, you also need to specify the RESETLOGS clause.

2> How to just modify dbname, the second step above requires SetName to be specified as Yes, which is the Nid target=sys/oracle Dbname=victor setname=yes. Direct startup opens the library without specifying the RESETLOGS clause.

3> Dbnewid does not modify the global database name. You can modify it with the following command:

sql> ALTER DATABASE rename Global_name to victor.being.com. Where being.com is the domain name.

The Dbnewid of Oracle Tools