How To Choose Your Oracle 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.”

I performed this test with Oracle 11.1.0.7 on Linux x86. 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:

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

accept new_dbid prompt "Enter the new Database ID:"
Enter the new Database ID:1

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:

set serveroutput on
exec dbms_output.put_line('Convert '||:old_name||  -
     '('||to_char(:old_dbid)||') to '||:new_name|| -
     '('||to_char(:new_dbid)||')')

Convert BLACK(361377223) to FRANCE(1)

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;
/

ControlFile:
=> Change Name:1
=> Change DBID:1
DataFile: /u01/app/oracle/oradata/BLACK/system01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u01/app/oracle/oradata/BLACK/sysaux01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u01/app/oracle/oradata/BLACK/undotbs01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u01/app/oracle/oradata/BLACK/users01.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u01/app/oracle/oradata/BLACK/streams_tbs.dbf
=> Skipped:0
=> Change Name:1
=> Change DBID:1
DataFile: /u01/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:

create pfile from spfile;

!cat initBLACK.ora | \
   sed "s/db_name='BLACK'/db_name='FRANCE'/" \
   > initFRANCE.ora

shutdown immediate;

startup mount pfile=initFRANCE.ora

alter database open resetlogs;

create spfile from pfile='initFRANCE.ora';

startup force;

Conclusion

I probably have the coolest Database Name and ID in the world now:

select name, dbid from v$database;

NAME		DBID
--------- ----------
FRANCE		   1

But if you think about it, I also changed some information in the datafile, even though the database was opened in read-only mode. Interesting?

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?