使用dbms_backup_restore包修改dbname及dbid

4若

   修改dbname以及dbid通常在使用RMAN还原到异机之后需要更改dbname以及dbid的情形。对于这个修改我们可以借助于命令行下的nid工具来完成。同时也可以直接调用API来实现。本文即是通过dbms_backup_restore方式来修改dbname及dbid，供大家参考。
      有关使用nid方式修改dbname及dbid，请参考：使用nid命令修改 db name 及 dbid

1、修改dbid及dbname的步骤
    a、一致性关闭数据库并启动数据库到read only状态(需要调用dbms_backup_restore，因此要open数据库)
    b、调用脚本修改dbname或者dbid(根据提示输入)
    c、修改spfile或pfile中的db_name的值，如果仅改变dbid，此步骤可忽略
    d、以open resetlogs方式打开数据库

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  


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||  -  
     '('||to_char(:old_dbid)||') to '||:new_name|| -  
     '('||to_char(:new_dbid)||')')  

declare  
  v_chgdbid   binary_integer;  
  v_chgdbname binary_integer;  
  v_skipped   binary_integer;  
begin  
  dbms_backup_restore.nidbegin(:new_name,  
       :old_name,:new_dbid,:old_dbid,0,0,10);  
  dbms_backup_restore.nidprocesscf(  
       v_chgdbid,v_chgdbname);  
  dbms_output.put_line('ControlFile: ');  
  dbms_output.put_line('  => Change Name:'  
       ||to_char(v_chgdbname));  
  dbms_output.put_line('  => Change DBID:'  
       ||to_char(v_chgdbid));  
  for i in (select file#,name from v$datafile)  
     loop  
     dbms_backup_restore.nidprocessdf(i.file#,0,  
       v_skipped,v_chgdbid,v_chgdbname);  
     dbms_output.put_line('DataFile: '||i.name);  
     dbms_output.put_line('  => Skipped:'  
       ||to_char(v_skipped));  
     dbms_output.put_line('  => Change Name:'  
       ||to_char(v_chgdbname));  
     dbms_output.put_line('  => Change DBID:'  
       ||to_char(v_chgdbid));  
     end loop;  
  for i in (select file#,name from v$tempfile)  
     loop  
     dbms_backup_restore.nidprocessdf(i.file#,1,  
       v_skipped,v_chgdbid,v_chgdbname);  
     dbms_output.put_line('DataFile: '||i.name);  
     dbms_output.put_line('  => Skipped:'  
       ||to_char(v_skipped));  
     dbms_output.put_line('  => Change Name:'  
       ||to_char(v_chgdbname));  
     dbms_output.put_line('  => Change DBID:'  
       ||to_char(v_chgdbid));  
     end loop;  
  dbms_backup_restore.nidend;  
end;  
/