使用dbms_backup_restore包修改dbname及dbid

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

修改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=ES0481robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdbaSQL*Plus: Release 10.2.0.3.0 - Production on Sat Mar 29 20:18:28 2014Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Release 10.2.0.3.0 - 64bit Productionsys@ES0481> shutdown immediate;sys@ES0481> startup open read only;sys@ES0481> select name,dbid from v$database;NAME   <em>本文来源[email protected]搞@^&代*@码2网</em>         DBID--------- ----------ES0481        123456sys@ES0481> @chg_dbname_dbidPL/SQL procedure successfully completed.OLD_NAME------------------------------------------------------ES0481Enter the new Database Name:ES0480Enter the new Database ID:654321PL/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:1DataFile: /u02/database/ES0481/oradata/sysES0481.dbf=> Skipped:0=> Change Name:1=> Change DBID:1DataFile: /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:1PL/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.orasys@ES0481> shutdown immediate;sys@ES0481> exitDisconnected from Oracle Database 10g Release 10.2.0.3.0 - 64bit Productionrobin@SZDB:~/dba_scripts/custom/sql> export ORACLE_SID=ES0480robin@SZDB:~/dba_scripts/custom/sql> sqlplus / as sysdbaidle> startup pfile=/users/oracle/OraHome10g/dbs/initES0480.ora mount;ORACLE instance started.Total System Global Area  599785472 bytesFixed Size                  2074568 bytesVariable Size             167774264 bytesDatabase Buffers          423624704 bytesRedo Buffers                6311936 bytesDatabase mounted.idle> alter database open resetlogs;Database altered.-- Author : Leshami-- Blog   : http://blog.gaodaima.com/leshamiidle> 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 numbervar new_name varchar2(20)var new_dbid numberexec select name, dbid -       into :old_name,:old_dbid -       from v$databaseprint old_nameaccept 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_dbidset serveroutput onexec 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;/        

更多参考

有关Oracle RAC请参考

有关Oracle 网络配置相关基础以及概念性的问题请参考：

有关基于用户管理的备份和备份恢复的概念请参考

有关RMAN的备份恢复与管理请参考

有关ORACLE体系结构请参考

喜欢 (0)赏

[搞代码]

分享 (0)