https://blog.pythian.com/how-to-choose-your-oracle-database-id-dbid
How To Choose Your Oracle Database ID (DBID)
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?

本文介绍如何使用dbms_backup_restore而非nid来更改Oracle数据库名称和ID(DBID),包括打开数据库为只读模式、设置新的数据库名称和ID、进行更改以及更新数据库参数等步骤。
845

被折叠的 条评论
为什么被折叠?



