欢迎来到山村网

使用dbms_backup_restore包修改dbname及dbid

2019-03-02 11:33:34浏览:815 来源:山村网   
核心摘要:  修改dbname以及dbid通常在使用RMAN还原到异机之后需要更改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、实战演习

?

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

(责任编辑:豆豆)
下一篇:

显示ADO.NET分页填充技术

上一篇:

将批处理文件注册成服务在系统启动的时候自动调用

  • 信息二维码

    手机看新闻

  • 分享到
打赏
免责声明
• 
本文仅代表作者个人观点,本站未对其内容进行核实,请读者仅做参考,如若文中涉及有违公德、触犯法律的内容,一经发现,立即删除,作者需自行承担相应责任。涉及到版权或其他问题,请及时联系我们 xfptx@outlook.com