Use the dbms_backup_restore package to modify dbname and dbid

Modify the dbname and dbid when you use RMAN to restore to a different host, you need to change the dbname and dbid. We can use the nid tool in the command line to complete this modification. At the same time, you can directly call the API for implementation. This article uses dbms_backup_restore to modify dbname and dbid for your reference.

For how to use nid to modify dbname and dbid, see: Use the nid command to modify db name and dbid

1. Steps for modifying dbid and dbname

2. Practical drills

?

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 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 48 49 50 51 52 54 55 57 58 59 60 61 62 63 64 65 66 67 68 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 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,200 6, 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> CRES Ate 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. Script 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 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 48 49 50 51 52 53 54 56 57 58 59 60 61 62 63 64 65 66 67 68

-- This script is compiled from the Internet. -- This script can modify dbname and dbid, or both. -- This script can be used to test OK at 10 GB and round robin @ SZDB at 11 GB :~ /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 struct (: new_name,: old_name,: new_dbid,: old_dbid, 10); struct (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 ;/

More references

For more information about Oracle RAC, see

For more information about the basics and concepts of Oracle network configuration, see:

For more information about user-managed backup and recovery, see

For information on RMAN backup recovery and management, see

For the ORACLE architecture, see