Re: Changing DBID

On Jul 8, 11:30 am, Chuck <chuckh1958_nos..._at_gmail.com> wrote:
> 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
I wonder if a simple change in the TNSNAMES.ORA is sufficient to accomplish what needs to be done? Assume that TNSNAMES.ORA contains the following to allow a client to connect to the production database instance:
PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PROD-SERVER)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

Also, assume that you need a TNSNAMES.ORA entry to connect to the test database instance on the test server that looks like this: TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TEST-SERVER)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

On the client computer that should only connect to the test database instance when using the same connection string as would be used on another client when connecting to the production server, the TNSNAMES.ORA entry might look like this: PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = TEST-SERVER)(PORT = 1521))     )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

In the above, when the client requests to connect to "PROD", it actually connects to a database instance named "TEST" on a different server. I recently used the above approach to test a newer version of an ERP package, and a number of custom developed applications with hard coded connection strings targeting the data of the ERP system. A similar approach may work for your needs.

I believe that DBID has a special meaning, which is causing some concern from people responding in this thread, when you state you want two databases to have the same DBID.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Jul 12 2008 - 12:06:44 CDT