使用dbms

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