https://blog.pythian.com/how-to-choose-your-oracle-database-id-dbid

 

How To Choose Your Oracle Database ID (DBID)

by Grégory Guillou

 

February 18, 2009

Posted in: Technical Track

Tags: Oracle

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_restoreinstead 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?