Escolhendo meu DBID

Para que a alteração de DBID seria útil?

Bom, pode ser pelo mero prazer de ter uma base com o DBID do seu gosto (1,2,3..24..666) ou pelo fato da licença da sua aplicação estar vinculada ao DBID do banco de modo que se ele for alterado a aplicação para de funcionar!

Antes de começar lembre-se que é de suma importância executar um backup FULL do database!

Verificando o dbid atual da base:

SQL> select dbid from v$database;
 
      DBID
----------
3464458105

Efetuando um shutdown limpo da base e na sequencia abrindo como READ ONLY:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.
 
Total System Global Area 1138368512 bytes
Fixed Size                  1364088 bytes
Variable Size             318771080 bytes
Database Buffers          788529152 bytes
Redo Buffers               29704192 bytes
Database mounted.
Database opened.

Agora basta executar o script abaixo que ele irá pedir o NOVO DBID da base e após você inserir ele irá fazer todo o trabalho. Baixe o script clicando AQUI.

SQL> set serveroutput on
SQL> declare
   v_chgdbid   binary_integer;
   v_chgdbname binary_integer;
   v_skipped   binary_integer;
   v_new_db_name varchar2(9);
   v_old_db_name varchar2(9);
   v_new_dbid    number;
   v_old_dbid    number;
   w_action      varchar2(255);


 begin
      w_action:='Recuperando DBID Atual.' ;
      select dbid, name, name into v_old_dbid, v_new_db_name, v_old_db_name  from v$database;

      select TO_NUMBER('&NOVO_DBID') into v_new_dbid from dual;

      w_action:='Executando a Procedure (dbms_backup_restore.nidbegin).';
      dbms_output.put_line('New NAME='||V_NEW_DB_NAME);
      dbms_output.put_line('Old NAME='||V_OLD_DB_NAME);
      dbms_output.put_line('New DBID='||V_NEW_DBID);
      dbms_output.put_line('Old DBID='||V_OLD_DBID);

      dbms_backup_restore.nidbegin(V_NEW_DB_NAME,V_OLD_DB_NAME,V_NEW_DBID,V_OLD_DBID,0,0,10);

      w_action:='Executando a Procedure (dbms_backup_restore.nidprocesscf).';
      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));

      w_action := 'Alterando os Datafiles, procedure (dbms_backup_restore.nidprocessdf).';
      for i in (select file#,name from v$datafile)
      loop
         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));
         dbms_backup_restore.nidprocessdf(i.file#,0, v_skipped,v_chgdbid,v_chgdbname);
      end loop;

      w_action := 'Alterando os Tempfiles, procedure (dbms_backup_restore.nidprocessdf).';
      for i in (select file#,name from v$tempfile)
      loop
         dbms_output.put_line('TempFile..........: '  ||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));
         dbms_backup_restore.nidprocessdf(i.file#,1,v_skipped,v_chgdbid,v_chgdbname);
      end loop;
   dbms_backup_restore.nidend;
 end;
 /
Enter value for novo_dbid: 1
old  16:      select TO_NUMBER('&NOVO_DBID') into v_new_dbid from dual;
new  16:      select TO_NUMBER('1') into v_new_dbid from dual;
New NAME=DBTRN
Old NAME=DBTRN
New DBID=1
Old DBID=3464458105
ControlFile.......:
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/system01.dbf
=> Skipped......:
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/sysaux01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/users01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/veys01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/FAROL01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/teste01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd02/oradata/dbtrn/teste02.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd03/oradata/dbtrn/system02.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
DataFile..........: /oraprd03/oradata/dbtrn/undotbs01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
TempFile..........: /oraprd02/oradata/dbtrn/temp01.dbf
=> Skipped......: 0
=> Change Name..: 0
=> Change DBID..: 1
 
PL/SQL procedure successfully completed.

Terminado a execução do script podemos ver que todos os arquivos tiveram o DBID alterado para 1 e que nenhum foi pulado (SKIPPED). Efetuando novamente um shutdown e abrindo a base com OPEN RESETLOGS pois alteramos o controlfile!

SQL> startup mount
ORACLE instance started.
 
Total System Global Area 1138368512 bytes
Fixed Size                  1364088 bytes
Variable Size             318771080 bytes
Database Buffers          788529152 bytes
Redo Buffers               29704192 bytes
Database mounted.
SQL> alter database open resetlogs;
 
Database altered.

Verificando o novo DBID do banco de dados:

SQL> select dbid from v$database;
 
      DBID
----------
         1

No script também existe a variavel v_new_db_name que lhe mertite alterar junto com o DBID o DBNAME da base, basta ajustá-lo e voilá! tudo de uma só vez.

Referências:

 How To Choose Your Oracle Database ID (DBID)