How to change DBNAME and DBID in Oracle database Using Normal & RMAN Method

(i)Changing the DBID in normal method:

    If u clone the database , the new DBID same as like the source DB. If  u want to change the DBID following below methods useful.

This is my new clone database DBID. Same as source DBID, now i want to change new DBID for clone database.

Step 1:

Step 3:

This script checks user for the new NAME and DBID.

(i)DBNAME change

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:" 

Step 4:

(ii) DBID Change

    If u run the script , it will ask DBID enter the new DBID.

      accept new_dbid prompt "Enter the new Database ID:"

Step 5:

Make the changes of new DBNAME and new DBID.

exec :new_name:='&&new_name'

exec :new_dbid:=&&new_dbid

Step 6:

  The following below script run- new DBID changes in contolfile level all database files.

set serveroutput on

exec dbms_output.put_line('Convert '||:old_name||  -

     '('||to_char(:old_dbid)||') to '||:new_name|| -

     '('||to_char(:new_dbid)||')')

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;

/

O/P:-

Step 7:

Change db_name and open the database.

Bef u can open the database, you have to change db_name parameter in the spfile. Everything is done open the database in open resetlogs mode.

shutdown immediate;

startup mount;

alter database open resetlogs;

startup force;

create spfile from pfile=’e:\initcorporate.ora’

startup force; 

========================================================================

(ii) Changing the DBID in RMAN method:

Step 1. Identify the DBID of the database

SQL> select dbid from v$database;

DBID

———-

1320285073

Step 2. Shutdown the database in normal mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 3.Start the database to mount phase

SQL> startup mount

ORACLE instance started.

Total System Global Area    250667625 bytes

Fixed Size                    2365080 bytes

Variable Size                73274908 bytes

Database Buffers             56721608 bytes

Redo Buffers                   5876423 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step 4. Execute the nid command

[oracle@orcl ~]$ which nid

/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid

[oracle@orcl ~]$ nid target=/

DBNEWID: Release 11.2.0.3.0 – Production on Mon Jan 23 11:45:22 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database RC (DBID=1320285073)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/control01.ctl

/u01/app/oracle/oradata/control02.ctl

Change database ID of database RC? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1320285073 to 1323314655

Control File /u01/app/oracle/oradata/control01.ctl – modified

Control File /u01/app/oracle/oradata/control02.ctl – modified

Datafile /u01/app/oracle/oradata/system01.dbf– dbid changed

Datafile /u01/app/oracle/oradata/sysaux01.dbf – dbid changed

Datafile /u01/app/oracle/oradata/undotbs01.dbf– dbid changed

Datafile /u01/app/oracle/oradata/temp01.dbf – dbid changed

Instance shut down

Database ID for database RC changed to 1323314655

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID – Completed succesfully.

Step 5. Open the DB in mount stage

[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 23 12:10:45 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area     250667625 bytes

Fixed Size                    2365080 bytes

Variable Size                73274908 bytes

Database Buffers             56721608 bytes

Redo Buffers                   5876423 bytes

Database mounted.

Step 6. Open the database with resetlog option

SQL> alter database open resetlogs ;

Database altered.

SQL>

Step 7. Identify the new changed DBID

SQL> select dbid from v$database;

DBID

———-

1323314655

=============================================================================================