Re: Changing DBID

From: Tim Arnold <timkarnold_at_comcast.net>
Date: Tue, 8 Jul 2008 11:40:19 -0400
Message-ID: <iPGdnUApa5P-F-7VnZ2dnUVZ_jadnZ2d@comcast.com>

"Chuck" <chuckh1958_nospam_at_gmail.com> wrote in message news:RKLck.1120$bn3.583_at_trnddc07...
> Is there a way to reset the DBID of a database to a specific value? For
> example in the case if a duplicated database, I want to reset the DBID of
> the auxiliary DB to it's original DBID after the duplication is complete
> to make maintenance on the old backups easier. The auxiliary database in
> question is a test db, is not in archivelog mode, and all it's backups are
> cold backups.
>
> TIA
see Metalink note Note:224266.1
Change Only the DBID


  1. Backup the database
  2. SHUTDOWN IMMEDIATE of the database
  3. STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges % nid TARGET=SYS/password_at_test_db
  5. Shutdown IMMEDIATE of the database
  6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
  7. Create a new password file
  8. Startup of the database with open resetlogsExample:
  9. C:\>set ORACLE_SID=TEST1BY C:\>sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 24 11:16:52 2002 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance.

   SQL> startup pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora    ORACLE instance started.

   Total System Global Area 135338868 bytes

   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
   Database opened.

2. check the DBID before change

   SQL> select dbid,name,open_mode,activation#,created from v$database;

         DBID NAME OPEN_MODE ACTIVATION# CREATED

  • --------- ---------- ----------- --------- 1395399949 TEST1BY READ WRITE 1395404134 10-SEP-02 3. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 4. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora ORACLE instance started.

   Total System Global Area 135338868 bytes

   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.
   SQL>exit

5. execute NID

   C:\>nid target=sys/oracle_at_TEST1BY

   DBNEWID: Release 9.2.0.1.0 - Production    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

   Connected to database TEST1BY (DBID=1395399949)

   Control Files in database:
     D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL    Change database ID of database TEST1BY? (Y/[N]) => y

   Proceeding with operation
   Changing database ID from 1395399949 to 1397190693    Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - modified

   Datafile D:\ORACLE\BASE_TEST\TEST1BY\SYSTEM01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\UNDOTBS01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\DRSYS01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\EXAMPLE01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\INDX01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\TOOLS01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\USERS01.DBF - dbid changed
   Datafile D:\ORACLE\BASE_TEST\TEST1BY\XDB01.DBF - dbid changed
   Control File D:\ORACLE\BASE_TEST\TEST1BYCONTROL01.CTL - dbid changed

   Database ID for database TEST1BY changed to 1397190693.    All previous backups and archived redo logs for this database are unusable.

   Shut down database and open with RESETLOGS option.    Succesfully changed database ID.
   DBNEWID - Completed succesfully.

6. SQL> shutdown immediate;

   ORA-01109: database not open
   Database dismounted.
   ORACLE instance shut down.

7. create the new passwordfile

8. SQL> startup mount pfile=D:\oracle\admin\TEST1BY\pfile\initTEST1BY.ora

   ORACLE instance started.

   Total System Global Area 135338868 bytes

   Fixed Size                   453492 bytes
   Variable Size             109051904 bytes
   Database Buffers           25165824 bytes
   Redo Buffers                 667648 bytes
   Database mounted.

9. SQL> alter database open resetlogs;

   Database altered.

  1. check the new DBID

    SQL> select dbid,name,open_mode,activation#,created from v$database;

          DBID NAME OPEN_MODE ACTIVATION# CREATED

  • --------- ---------- ----------- --------- 1397190693 TEST1BY READ WRITE 1397188261 10-SEP-02
NOTE: The NID change the OLD DBID 1395399949 to the NEW DBID 1397190693   Received on Tue Jul 08 2008 - 10:40:19 CDT

Original text of this message