使用dbms_backup_restore包修改dbname及dbid

　　修改dbname以及dbid通常在使用RMAN還原到異機之後需要更改dbname以及dbid的情形。對於這個修改我們可以借助於命令行下的nid工具來完成。同時也可以直接調用API來實現。本文即是通過dbms_backup_restore方式來修改dbname及dbid，供大家參考。

　　有關使用nid方式修改dbname及dbid，請參考：使用nid命令修改 db name 及 dbid

　　1、修改dbid及dbname的步驟

　　2、實戰演習

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 --該腳本從網上整理而來 --該腳本可以修改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; /       

　　更多參考

　　有關Oracle RAC請參考

　　有關Oracle 網絡配置相關基礎以及概念性的問題請參考：

　　有關基於用戶管理的備份和備份恢復的概念請參考

　　有關RMAN的備份恢復與管理請參考

　　有關ORACLE體系結構請參考