How to modify db name in oracle

Modification method: 1. Modify the "db_name" value in the "pfile.ora" parameter file to the specified value and save it; 2. Modify the sid in the original environment variable to the new "db_name" value; 3. After restarting the database, use the resetlogs method to open the data to complete the modification.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to modify the db name in oracle

The overall operation steps are as follows:

• Generate the trace related to the reconstruction space statement, and generate the pfile file of the database instance

• Close the database

• Modify the content of the parameter file

• Execute the statement to create the control file and start the database Go to mount

• Open the database to open state

• View the name of the database

Modify db_name process The implicit content involved

Parameter differences in the process of creating the control file

Methods to restore the database when redolog is damaged

After physically modifying the database file location, How to quickly restore the database

The above content only demonstrates name modification. For other scenarios, please refer to other technical articles.

Modification process

The db_name information of the current database is as follows:

1. Generate parameter file ：

SQL> create pfile='/home/oracle/pfile.ora' from spfile;
File created.

Change the db_name of the generated parameter file (pfile.ora) to testdb

*.db_name='testdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

Generate the modified pfile file into a new spfile

Generate the trace file of the control file

Add a mark to the session to facilitate the search for the trace file

alter session set tracefile_identifier='bak_control';

Put the creation statement of the current controlfile file into the trace file

alter database backup controlfile to trace;

Get the path of the trace file generated in the second step

select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

Close the database

shutdown immediate;

2. Modify the content of the parameter file and operating system environment variables

Change the SID in the original environment variable to the new name testdb

Use the statement to create the control file to start the database

During the actual execution process, you need to replace the above reuse with set and modify NORESETLOGS with RESETLOGS. The actual execution process is as follows:

The specific parameter description is as follows:

| Parameter name | Parameter description | | ------ | ------ | | set | Usually change the database name | | reuse | Re-create the control file | | noresetlogs | No need to resetlogs when opening the database | | resetlogs | resetlogs is required when opening the database |

3. Open the database to the open state

Must use resetlogs to open the data, otherwise an error will be reported.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> 
SQL> 
SQL> alter database open resetlogs;
Database altered.
SQL>

Result verification

Process summary

It is recommended to avoid using this method to modify the database name in the production environment

Use The scenario is that different businesses require different environment names. After completing the cross-machine recovery, modify the corresponding names according to the environment requirements.

The whole process is not difficult, but you need to be careful, especially the modification of parameter files and the regeneration of control files

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to modify db name in oracle. For more information, please follow other related articles on the PHP Chinese website!