How To Choose Your Oracle Database ID (DBID)

Feb 18, 2009 / By Grégory Guillou

Tags:

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?

16 Responses to “How To Choose Your Oracle Database ID (DBID)”

  • Surachart says:

    Good Article! Anyway I have no idea why? I should to change DBID.
    Perhaps that make easy to remember or support.

    My idea.. I don’t interest yet…

    If it can help when I mess up all my backups. I think that’s very good.

  • Hi Grégory,

    Good stuff, but why not use just nid ?
    Did you have any problem with that ?
    I had been using dbms_backup_recovery from time to time when I had problems with RMAN and due to lack of catalog command in 8i and 9i.

    Surachart,
    why to change ID of database ? it is always a good when you manually duplicate your database into other one. why ? There is some problem without that with RMAN – see my blog entry – http://oracleprof.blogspot.com/ – about that

  • Marcin, I can hardly more agree with you: use nid!

  • Rudi Demeulenaere says:

    As far as I can see, the above approach is the only way to set the new DBID to a value you want. This is not possible with “nid” (a new value might be generated, but not *your* value)

  • Harsha says:

    Excellent script! Was asked to restore a DB from some backupset files – no controlfile backups, no documentation. I had to create a dummy database and set dbid just to catalog the backupsets – so your script came in very handy.

    Thanks a ton!

  • Bill says:

    Thanks, hope this helps me fix my DG configuration.

  • Sagar says:

    I am getting errors in Step 4. I am using Oracle 10g and Windows 7 OS. Can anyone help me.

  • Sagar says:

    Can’t able to run the script

    !cat initBLACK.ora | \
    sed “s/db_name=’BLACK’/db_name=’FRANCE’/” \
    > initFRANCE.ora

    i am using Oracle 10g and Windows 7 OS and running the script command prompt.
    Please help me out on this.
    Thanks in advance.

  • Gregory says:

    Hello Sagar,

    What this step does basically is create a file name initFRANCE.ora from initBLACK.ora by
    – copying all the parameters except for db_name
    – replacing db_name value from BLACK to FRANCE

    You can probably automate that on Windows with some kind of VBScript/Powertools but I have no clue how.

  • Meenu says:

    SQL> startup mount pfile=’/u01/app/oracle/product/11.1.0/db_5/dbs/initCORP2.ora';
    ORA-01081: cannot start already-running ORACLE – shut it down first
    SQL> alter database mount;
    alter database mount
    *
    ERROR at line 1:
    ORA-00205: error in identifying control file, check alert log for more info
    what to do..

  • kamil.piorekk@gmail.com says:

    Very Usefull ! ;D i Just set physical standby by rman duplicate with SKIP TABLESPACE .. then i’ve change the DBID to identical as primary .. an then .. it works :D physical standby with different structure :D

    • kamil.piorekk@gmail.com says:

      ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/dhdev/dg_stdby.ctl';
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      RMAN
      ALTER DATABASE ADD STANDBY LOGFILE (‘/oradb1/oradata/dg_stdby/slog1.rdo’) SIZE 10M;
      ALTER DATABASE ADD STANDBY LOGFILE (‘/oradb1/oradata/dg_stdby/slog2.rdo’) SIZE 10M;
      ALTER DATABASE ADD STANDBY LOGFILE (‘/oradb1/oradata/dg_stdby/slog3.rdo’) SIZE 10M;
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      SELECT database_role FROM v$database;
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      select open_mode from v$database ;
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      SHUT IMMEDIATE
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      declare v_chgdbid binary_integer;
      v_chgdbname binary_integer;
      v_skipped binary_integer;
      begin
      dbms_backup_restore.nidbegin(‘DG_PRMY’, ‘DG_PRMY’,1006803094,1006896744,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;
      -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
      ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

  • sakthivel Govindasamy says:

    This is an excellent script, we have 45 TB of DB and took FRA save point for flash back for multiple time,but DBID is changed for a reason using NID, then realized we need to flashback the DB ,after changing the DBID, we can’t flash back since all FRA logs not useful.
    using this script we changed back to original DBID and able to flashback the database.

  • Gregory says:

    Should be a bug though, this wasn’t meant for that ;-)

  • Kumar says:

    Hi,

    I have a situation to clone the primary and its associated logical standby from HP to AIX machines. We have cloned the primary -primary and logical – logical using rman duplicate. While trying to turn on the SQL apply ,noticed DBID is pointing to the original primary DBID on HP-UX.

    Using this script tried to change the DBID, i was successful in changing the dbib to the new primary, however the dbib in v$logstdby_state has not updated to a new value. Any reason behind this ?

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>