修改dbname以及dbid通常在使用RMAN还原到异机之后需要更改dbname以及dbid的情形。对于这个修改我们可以借助于命令行下的nid工具来完成。同时也可以直接调用API来实现。本文即是通过dbms_backup_restore方式来修改dbname及dbid，供大家参考。
有关使用nid方式修改dbname及dbid，请参考：使用nid命令修改 db name 及 dbid
1、修改dbid及dbname的步骤
2、实战演习
robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0481 robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 29 20:18:28 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production sys@ES0481> shutdown immediate; sys@ES0481> startup open read only; sys@ES0481> select name,dbid from v$database; NAME DBID --------- ---------- ES0481 123456 sys@ES0481> @chg_dbname_dbid PL/SQL procedure successfully completed. OLD_NAME ------------------------------------------------------ ES0481 Enter the new Database Name:ES0480 Enter the new Database ID:654321 PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Convert ES0481(123456) to ES0480(654321) PL/SQL procedure successfully completed. ControlFile: => Change Name:1 => Change DBID:1 DataFile: /u02/database/ES0481/oradata/sysES0481.dbf => Skipped:0 => Change Name:1 => Change DBID:1 DataFile: /u02/database/ES0481/undo/undotbsES0481.dbf => Skipped:0 => Change Name:1 => Change DBID:1 ................. DataFile: /u02/database/ES0481/temp/ES0481_tempES0481.dbf => Skipped:0 => Change Name:1 => Change DBID:1 PL/SQL procedure successfully completed. sys@ES0481> create pfile from spfile; File created. sys@ES0481> ho cat $ORACLE_HOME/dbs/initES0481.ora |sed "s/db_name='ES0481'/db_name='ES0480'/">$ORACLE_HOME/dbs/initES0480.ora sys@ES0481> shutdown immediate; sys@ES0481> exit Disconnected from Oracle Database 10g Release 10.2.0.3.0 - 64bit Production robin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0480 robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdba idle> startup pfile=/users/oracle/OraHome10g/dbs/initES0480.ora mount; ORACLE instance started. Total System Global Area 599785472 bytes Fixed Size 2074568 bytes Variable Size 167774264 bytes Database Buffers 423624704 bytes Redo Buffers 6311936 bytes Database mounted. idle> alter database open resetlogs; Database altered. -- Author : Leshami -- Blog : http://blog.csdn.net/leshami idle> create spfile from pfile='/users/oracle/OraHome10g/dbs/initES0480.ora'; File created. idle> startup force; idle> select name,dbid from v$database; NAME DBID --------- ---------- ES0480 654321
3、脚本chg_dbname_dbid.sql
--该脚本从网上整理而来
--该脚本可以修改dbname，以及dbid，或者两者同时修改
--该脚本在10g下测试ok，11g下有待测试
robin@SZDB:~/dba_scripts/custom/sql> more chg_dbname_dbid.sql
var old_name varchar2(20)
var old_dbid number
var new_name varchar2(20)
var new_dbid number
exec select name, dbid -
into :old_name,:old_dbid -
from v$database
print old_name
accept new_name prompt "Enter the new Database Name:"
accept new_dbid prompt "Enter the new Database ID:"
exec :new_name:='&&new_name'
exec :new_dbid:=&&new_dbid
set serveroutput on
exec dbms_output.put_line('Convert '||:old_name