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?
25 Comments. Leave new
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 – https://oracleprof.blogspot.com/ – about that
Marcin, I can hardly more agree with you: use nid!
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)
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!
Thanks, hope this helps me fix my DG configuration.
I am getting errors in Step 4. I am using Oracle 10g and Windows 7 OS. Can anyone help me.
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.
Sagar: You can’t run the above command because it is a unix command.
What the script is trying to do is rename a parm in init.ora
from
db_name=’BLACK’
TO
db_name=’FRANCE
====
You can do the same thing in notepad.
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.
[…] Simple Script:- https://www.pythian.com/news/1491/how-to-choose-your-oracle-database-id-dbid/ […]
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..
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
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;
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.
Should be a bug though, this wasn’t meant for that ;-)
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 ?
If you don’t use rman, how can I find out the old dbid and new dbid from “duplicate”. I would like to select both to find the differences.
Hi Karrolli , that is an intersting question ,
you can find all the dbid’s that your database had in DBA_FEATURE_USAGE_STATISTICS.
Here is how I found that : select owner , table_name from dba_tab_columns where column_name = ‘DBID’ ;-)
Gregory,
This looks Very useful ! I have to set the DBID on my new standby database to the DBID on the Primary database, and your above approach is the only one I’ve found on how to do this, since the NID
utility doesn’t accept a DBID parm value. I’m going to try this out and see how it works for my standby db.
Thanks Very much for posting this !
Dave
Isn’t working on Primary-Standby scenario for me.
Does change the DBID on both sides, but mrp doesn’t go beyond the boundaries of old DBID on standby side because DBID change on Primary does not signal and send the End-of-Redo record to standby.
If anyone is able to get DBID change across Primary – Standby and able to get mrp going without a fresh backup-restore from primary to standby please paste the steps.
Hi Guy,
Were you ever able to get DBID change across Primary – Standby and able to get mrp going without a fresh backup-restore from primary to standby ?
When attempting this with a non-RAC Oracle 12.2.0.1.0 installation, I receive these errors. Any idea how to fix?
dbms_backup_restore.nidbegin(:new_name,:old_name,:new_dbid,:old_dbid,0,0,10);
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00201: identifier ‘DBMS_BACKUP_RESTORE.NIDBEGIN’ must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
ORA-06550: line 7, column 3:
PLS-00201: identifier ‘DBMS_BACKUP_RESTORE.NIDPROCESSCF’ must be declared
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
ORA-06550: line 13, column 6:
PLS-00201: identifier ‘DBMS_BACKUP_RESTORE.NIDPROCESSDF’ must be declared
ORA-06550: line 13, column 6:
PL/SQL: Statement ignored
ORA-06550: line 21, column 6:
PLS-00201: identifier ‘DBMS_BACKUP_RESTORE.NIDPROCESSDF’ must be declared
ORA-06550: line 21, column 6:
PL/SQL: Statement ignored
ORA-06550: line 27, column 3:
PLS-00201: identifier ‘DBMS_BACKUP_RESTORE.NIDEND’ must be declared
ORA-06550: line 27, column 3:
PL/SQL: Statement ignored
Hi Grégory,
Do you know the steps required using this PL/SQL block to change the DBID change across Primary–Standby and able to get mrp going without a fresh backup-restore from primary to standby ?
Hi Steve,
Sorry to leave you hanging, but Grégory is no longer at Pythian.
And I do not know the answer to your question.
Jared