How to modify an Oracle database name

In the actual production environment we may need to quickly replicate the existing database, also hope that the new library and the original library is not the same name, or we need to replicate the production library to do research and development testing, if we adopt DBCA way, the disadvantage is slow and no data, we need to new data, time-consuming and troublesome. Below I will detail how to modify the database name to complete the mission, using 2 methods (1. Rebuild the control file to modify the database name 2. Modify the database name using the NID tool)

I. Reconstruction control file Modify database name

Steps overview

Generate Controlfile Create script and write to trace file

Execute lookup trace file statement, get current trace file path

Shut down the database

Copy only data files, redo logs (no copy control files required!) ）

Generate a new Initleo2.orac plain text parameter file

Edit a newly generated Initleo2.ora plain text parameter file

Create spfileleo2.ora binary parameter file with Initleo2.ora Plain text parameter file,

Database boot to Nomount state, load parameter file

Delete the original library control file (if the control file is included in the copy)

Modify the contents of a/U01/APP/ORACLE/ADMIN/LEO/UDUMP/LEO_ORA_25621.TRC file

Objective: We replicate the new library LEO2 with the original library, Leo.

1. Generate controlfile Create script and write to trace file

sys@leo> ALTER DATABASE backup Controlfile to trace;

2. Execute lookup trace file statement, get current trace file path

Sys@leo> Select C.value | | '/' || D.instance_name | | ' _ora_ ' | | To_char (a.spid, ' fm99999 ') | | '. TRC ' from V$process A, v$session B, V$parameter C, v$instance d where a.addr = b.paddr and B.audsid = Userenv (' SessionID ') and c.name = ' user_dump_dest ';

Trace file path

c.value| | ' /'|| d.instance_name| | ' _ora_ ' | | To_char (a.spid, ' FM99999 ') | | TRC '

--------------------------------------------------------------------------------

/u01/app/oracle/admin/leo/udump/le O_o RA_25621.TRC

3. Shut down the database

sys@leo> shutdown Immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

4. Only copy data files, redo log (no need to copy the control file Oh!) ）

Copy from the original/u01/app/oracle/oradata/leo/disk1 to

/u01/app/oracle/oradata/leo2/disk1

5. Generate a new Initleo2.orac plain text parameter file

Export Oracle_sid=leo

Sqlplus/as SYSDBA into the original library execute command

sys@leo> create pfile= '/u01/app/oracle/product/10.2.0/db_1/dbs/initleo2.ora ' from SPFile;

6. Edit the newly generated Initleo2.ora plain text parameter file

Vim Initleo2.ora

What needs to be modified:

(1) path

*.audit_file_dest= '/u01/app/oracle/admin/leo2/adump '

*.background_dump_dest= '/u01/app/oracle/admin/leo2/bdump '

*.control_files= '/u01/app/oracle/oradata/leo2/disk1/control01.ctl ', '/u01/app/oracle/oradata/leo2/disk2/ Control02.ctl ', '/u01/app/oracle/oradata/leo2/disk3/control03.ctl '

*.core_dump_dest= '/u01/app/oracle/admin/leo2/cdump '

*.user_dump_dest= '/u01/app/oracle/admin/leo2/udump '

(2) Database name

*.db_name= ' LEO2 '

7. Create spfileleo2.ora binary parameter file with Initleo2.ora plain text parameter file

Export Oracle_sid=leo2

Sqlplus/as SYSDBA

Sys@leo2> create SPFile from Pfile; Create SPFile

8. Database boot to Nomount state, load parameter file

Sys@leo2> Startup Nomount

ORACLE instance started.

Total System Global area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 96470416 bytes

Database buffers 213909504 bytes

Redo buffers 2973696 bytes

Sys@leo2> Show Parameter name

NAME TYPE VALUE