Using the DBMS

Source: Internet
Author: User
Tags oracle database sqlplus

Modifying dbname and dbid typically requires changes to dbname and dbid after restoring to a different machine using Rman. We can do this with the help of the NID tool under the command line. You can also invoke the API directly to implement it. This article is through Dbms_backup_restore way to modify dbname and dbid, for everyone's reference.

For dbname and dbid modifications using the Nid method, refer to: Modify DB name and dbid using the NID command

1, modify the dbid and dbname steps

2, the actual combat exercise

?

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 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 5 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 20:18:28 2014   Copyright (c) 1982, 2006, Or acle.  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.   Controlfile: => change name:1 => change dbid:1 datafile:/u02/database/es0481/oradata/syses0481.dbf => Sk ipped:0 => Change name:1 => change dbid:1 datafile:/u02/database/es0481/undo/undotbses0481.dbf => Skipped:0 T Change Name:1 => The change dbid:1  &nbsp ......... 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_SC Ripts/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= '/u Sers/oracle/orahome10g/dbs/inites0480.ora ';   File created.   idle> startup force;   Idle> Select Name,dbid from V$database;   NAME &NBsp;          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-27--28 29---30 31--32 33 34 35 36 37 38-39 40 41 42 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67-68 --The script is collated from the Web--the script can modify dbname, and dbid, or both--the script is tested under the 10g ok,11g under test robin@szdb:~/dba_scripts/custom/sql> more Chg_dbname_dbid.sql var old_name varchar2 (M) var old_dbid number var new_name varchar2 (a) var new_dbid number   EXE C Select Name, dbid-       into:old_name,:old_dbid-        from v$database   Print old_name   accept new_name Prompt "Enter the new database name:" &nbsp ; 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 ' | |:o ld_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     & NBSP;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: '   &Nbsp;    | | 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     & NBSP;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 Oracle RAC please refer to

For basic and conceptual questions about Oracle Network configuration, please refer to:

For a user-managed backup and backup recovery concept, refer to

For backup recovery and management of Rman please refer to

For Oracle Architecture please refer to

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.