Modify the name of an Oracle Database

I decided to modify the name of the database when I was idle recently. I remember I learned how to modify the name of the database by recreating the control file, you can use the nid of Oracle to modify the database name. However, this method is troublesome and the name of the database cannot contain "_".

1. Modify the database name by recreating the control file.

To facilitate trace file search, we can mark the trace file when backing up the control file:

Alter session set tracefile_identifier = 'control _ Bak'

Below we back up our control file:

Alter database backup controlfile to trace;

10 Gb is a file containing "control_bak" under $ ORACLE_BASE/admin \ orcl \ bdump.

11g is the "control_bak" file included in our $ ORACLE_BASE/diag/rdbms/orcl/trace.

Regardless of the version of the database, we can query the path of the trace file.

Select * from v $ diag_info;

Close the database and delete control files (if we want to copy the database, we can copy all the files to the corresponding location, including data files and archive files, online log files and network-related files; no need to copy control files)

Simply modifying the database name can be omitted. We need to modify the pfile file: mainly to modify the database file path and database name, start the new database to the nomount stage with the modified data file.

Open the trace file you just found and copy a section of the control file (generally starting with create controlfile)

We need to modify the following two points:

1. Change "REUSE" to "set", change the original database name to the database name, and change the path of other files as needed

· 2. Remove the recover database using backup controlfile statement.

Follow our steps to replicate

Open our database in resetlog Mode

Alter database open resetlogs;

Enable temporary files

Alter tablespace temp add tempfile '/.../temp01.dbf' reuse;

This step can be omitted if you only modify the database name.

If it can be opened normally, the database name is successfully modified.

2. Use nid to modify the Database Name

Start the database to mount and modify the Database Name

Startup mount;

Host nid target = sys/orcl dbname = new_name

Disable restart to mount

Alter system set db_name = new_name scope = spfile;

Shutdown immediate;

Recreate Control File

Host orapwd file =/../pwdorcl. ora password = orcl entries = 5 (in fact, this parameter is of little use, and many such users set it according to the system)

Open in resetlogs Mode

Startup mount;

Alter database open resetlogs;

View Database Name

Select dbid, name from v $ database;

Change the instance_name of the database and stop all services under windows.

Rebuilding an instance

Oradim-delete-sid old_name;

Oradim-new-sid new_name-intpwd pwd-startmode a-pfile c: \ .. \ initonew_name.ora (modify the original parameter file)

Go to the database and create a spfile file.

Set oracle_sid = new_name

Sqlplus sys/orcl as sysdba

Create spfile from pfile = 'C: \ .. \ initnew_name.ora ';

Reload the listening File

Lsnrctl reload;

If the logstore cannot be started, run resetlogs.

Alter database open resetlogs;

When using method 2 to modify the database name, do not change the name without "_". Otherwise, the following error message is displayed: DIM-00003: the parameter is missing.

An oracle bug was found online, which was described in Bug No. 6000490.

The solution is to remove "_" and say that the bug has been submitted for development, but it cannot be solved until version 12.

Currently, only the underline can be removed.