使用dbms_backup_restore包修改dbname及dbid_编程语言综合--HTML5中文学习网

　　修改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

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.

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|| -

'('||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体系结构请参考