HOW TO CHANGE DATABASE ID

HOW TO CHANGE DATABASE ID (DBID)

    You can choose a DBID when you rename your Oracle database. This is probably a bad, unsupported, and useless idea. I assume this hidden feature can help you to mess up all your backups. So my advice would be: “don’t use it.” But as DBA we should aware of this.

I performed this test with Oracle 11.1.0.6 on Windows Server 2008. It consists in using dbms_backup_restore instead of nid to rename the database. You’ll find below the few steps require to get to it.

Step 1. Open the database in read-only mode

First, stop the instance with an immediate shutdown. If we were to use nid, we would mount the instance, but with dbms_backup_restore, we need to access the package. For this reason, we have to open the database in read-only mode. Here are the commands I ran:

sqlplus / as sysdba

shutdown immediate;

startup open read only;

Step 2. Get the old values; set the new ones…

Once we can access the database, we can check its NAME and DBID. The script below does these checks and prompts the user for the new NAME and DBID. To that result, it queries V$DATABASE:

Step 2.1

Selection 1

-----------------------------------------------------------------------------

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

Enter the new Database Name:CORPORATE

Step 2.2

Execute below step:-

Selection 2
----------------------------------------------------------------------------------------

accept new_dbid prompt "Enter the new Database ID:"

------------------------------------------------------------------------------

I will ask New DBID:-

Enter the new Database ID:1255844286

Step 2.3

Selection 3
-------------------------------------------------------------------------------

exec :new_name:='&&new_name'

exec :new_dbid:=&&new_dbid
-------------------------------------------------------------------------------

Step 3. Make the changes

I won’t go into all the details of the package. Instead, here is PL/SQL block you can run to make the change:

Selection 4

------------------------------------------------------------------------------

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;

/

-----------------------------------------------------------------------------

It Will give output as below,

ControlFile:

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\system01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\sysaux01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\undotbs01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\users01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\streams_tbs.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\temp01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

Step 4. Change db_name and open the database

Before you can open the database, you have to change the db_name parameter in the spfile. Once you’ve done so, you should be able to open it with resetlogs. That’s the script I ran to get to that result:

shutdown immediate;

startup mount;

alter database open resetlogs;

startup force;

create spfile from pfile=’e:\initcorporate.ora’

startup force; 

Note: Dear Friends above method  try to execute  on Test Environment.