Move your mouse over this icon to show all screenshots.
You can also move your mouse over each individual icon to see only the screenshot
associated with it.
In this lesson you will learn how to change the name of your
database.
If you are using Oracle9i
Database Release 2 or a later release of Oracle Database, you can
use the DBNEWID database utility to change the database name (DBNAME) and the
internal database identifier (DBID) for an operational database.
If you are using a release prior to Oracle9i
Database Release 2, you can change the database name (DBNAME) by
re-creating the control file.
Refer to the appropriate section below depending on the release
of Oracle you are using:
You can use the DBNEWID database utility to change the internal
database identifier (DBID) and the database name (DBNAME) for an operational
database. The DBNEWID database utility is available with Oracle9i
Database Release 2.
Prior to the introduction of the DBNEWID utility, you could
manually create a copy of a database and give it a new database name (DBNAME)
by re-creating the control file. However, you could not give the database a
new identifier (DBID). The DBID is an internal, unique identifier for a database.
Because Recovery Manager (RMAN) distinguishes databases by DBID, you could not
register a seed database and a manually copied database together in the same
RMAN repository. The DBNEWID utility solves this problem by allowing you to
change any of the following:
Only the DBID of a database
Only the DBNAME of a database
Both the DBNAME and DBID of a database
When the DBID of a database is changed, all previous backups
and archived logs of the database become unusable. After you change the DBID,
you must open the database with the RESETLOGS option. This re-creates the online
redo log files and resets their sequence to 1. Consequently, you should make
a backup of the whole database immediately after changing the DBID.
Changing the DBNAME without changing the DBID does not require
you to open with the RESETLOGS option, so database backups and archived logs
are not invalidated. However, changing the DBNAME does have consequences. You
must change the DB_NAME initialization parameter after a database name change
to reflect the new name. Also, you may have to re-create the Oracle password
file. If you restore an old backup of the control file (before the name change),
then you should use the initialization parameter file and password file from
before the database name change.
You can change the DBID by completing the following steps:
1.
Make a whole database backup.
2.
Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3.
Issue the following query to determine the current DBID:
SELECT dbid, name FROM v$database;
4.
Shut down the instance using the NORMAL,
IMMEDIATE, or TRANSACTIONAL
options:
SHUTDOWN IMMEDIATE
5.
Start the instance and mount the database, specifying
the parameter file if you are not using a server parameter file or the
text initialization parameter file is not in the default location:
STARTUP MOUNT
6.
Invoke the DBNEWID utility on the command line, specifying
a valid user with the SYSDBA privilege. The DBNEWID utility performs validations
of the headers of the data files and control files before attempting I/O
to the files. If validation is successful, then DBNEWID prompts you to
confirm the operation unless you specify a log file, changes the DBID
for each data file (including offline normal and read-only data files),
and then exits. The database is left mounted but is not yet usable.
nid TARGET=SYS/secure@<service_name>
7.
After DBNEWID successfully changes the DBID, shut down
the instance:
SHUTDOWN IMMEDIATE
8.
Create a new password file using the ORAPWD utility:
Verify the change to the DBID by issuing the following
query:
SELECT dbid, name FROM v$database;
12.
Make a new database backup. Because you reset the online
redo logs, the old backups and archived logs are no longer usable in the
current incarnation of the database.
You can change the DBNAME by completing the following steps:
1.
Make a whole database backup.
2.
Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3.
Issue the following query to determine the current database
name:
SELECT name FROM v$database;
4.
Shut down the instance using the NORMAL,
IMMEDIATE, or TRANSACTIONAL
options:
SHUTDOWN IMMEDIATE
5.
Start the instance and mount the database, specifying
the parameter file if you are not using a server parameter file or the
text initialization parameter file is not in the default location:
STARTUP MOUNT
6.
Invoke the DBNEWID utility on the command line, specifying
a valid user with the SYSDBA privilege. You must specify the DBNAME parameter
and supply your new database name. You must also specify the YES
value for the SETNAME parameter
to indicate that only the DBNAME is to be changed. DBNEWID performs validations
of the headers of the control files, but not the data files, before attempting
I/O to the files. If validation is successful, then DBNEWID prompts for
confirmation, changes the database name in the control files, and exits.
After DBNEWID completes successfully, the database is left mounted but
is not yet usable.
nid TARGET=<username>/<password>@<servicename> DBNAME=<newname> SETNAME=YES
7.
After DBNEWID successfully changes the database name,
shut down the instance:
SHUTDOWN IMMEDIATE
8.
Change the DB_NAME initialization parameter to your
new database name.
9.
Create a new password file using the ORAPWD utility:
You can change the DBNAME by completing the following steps:
1.
Make a whole database backup.
2.
Invoke SQL*Plus and connect as a user with SYSDBA privileges.
3.
Issue the following query to determine the current database
identifier and database name:
SELECT dbid, name FROM v$database;
4.
Shut down the instance using the NORMAL,
IMMEDIATE, or TRANSACTIONAL
options:
SHUTDOWN IMMEDIATE
5.
Start the instance and mount the database, specifying
the parameter file if you are not using a server parameter file or the
text initialization parameter file is not in the default location:
STARTUP MOUNT
6.
Invoke the DBNEWID utility on the command line, specifying
a valid user with the SYSDBA privilege. You must specify the DBNAME parameter
and supply your new database name. DBNEWID performs validations of the
headers of the control files and the data files before attempting I/O
to the files. If validation is successful, then DBNEWID prompts you to
confirm the operation unless you specify a log file, changes the DBID
for each data file (including offline normal and read only files), changes
the database name in the control file, and exits. After DBNEWID completes
successfully, the database is left mounted but is not yet usable.
nid TARGET=<username>/<password>@<service name> DBNAME=<new name>
7.
After DBNEWID successfully changes the database identifier
and database name, shut down the instance:
SHUTDOWN IMMEDIATE
8.
Change the DB_NAME initialization parameter to your
new database name.
9.
Create a new password file using the ORAPWD utility:
Verify the change to the database name and database
identifier by issuing the following query:
SELECT name, dbid FROM v$database;
12.
Make a new database backup. Because you reset the online
redo logs, the old backups and archived logs are no longer usable in the
current incarnation of the database.
Oracle9i
Release 1 and Earlier: Changing the Database Name
You can change the Database Name by completing the following
steps:
1.
Invoke SQL*Plus and connect as a user with SYSDBA privilege.
2.
Issue the following query to determine the current database
name:
SELECT name FROM v$database;
3.
Issue the following command to force a checkpoint:
ALTER SYSTEM CHECKPOINT;
4.
Issue the following command to create a trace file containing
the statements to re-create the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
5.
Locate the trace file created in step 4. The trace file
will be in the location specified by the USER_DUMP_DEST
initialization parameter. The file will be named ora_NNNN.trc.
You can find the value of USER_DUMP_DEST
by issuing the following command:
SHOW PARAMETER USER_DUMP_DEST
6.
Change the value of the DB_NAME
initialization parameter to your new database name.
7.
Shut down the instance using the NORMAL,
IMMEDIATE, or TRANSACTIONAL
options:
SHUTDOWN IMMEDIATE
8.
Copy the contents of the trace file beginning with the
STARTUP NOMOUNT command
to a new file. For the purposes of this example, assume you have named
the new file ccf.sql.
9.
Edit your ccf.sql
file and make the following changes:
Remove all of the comment lines. Your script should
begin with the CREATE CONTROLFILE
statement.
Change:
CREATE CONTROLFILE REUSE DATABASE
"olddbname" RESETLOGS
To:
CREATE CONTROLFILE SET
DATABASE "newdbname" RESETLOGS
Remove the RECOVER
DATABASE USING BACKUP CONTROLFILE statement:
10.
Rename your existing control files to provide a backup.
11.
Invoke SQL*Plus and connect as a user with SYSDBA privileges.
12.
Execute your ccf.sql
script to re-create the control file. The script starts the instance in
NOMOUNT mode and re-creates the control file. The script opens the database.
@ccf
13.
Change your global database name if required by issuing
the following command.
ALTER DATABASE RENAME GLOBAL_NAME to <newdbname>.<domain>;
14.
Verify your changes by issuing the following query to
find the current value of DB_NAME:
SELECT name FROM v$database;
15.
Make a whole consistent (full or complete) database
backup using Recovery Manager (RMAN) or operating system commands.
Move your mouse over this icon to hide all screenshots
