Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
comp.databases.oracle.server
+ new post
About this group
Subscribe to this group
This is a Usenet group - learn more
Message from discussion Changing DBID
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Tim Arnold  
View profile  
 More options Jul 8 2008, 11:40 am
Newsgroups: comp.databases.oracle.server
From: "Tim Arnold" <timkarn...@comcast.net>
Date: Tue, 8 Jul 2008 11:40:19 -0400
Local: Tues, Jul 8 2008 11:40 am
Subject: Re: Changing DBID
Print | View thread | Show original | Report this message | Find messages by this author

"Chuck" <chuckh1958_nos...@gmail.com> wrote in message

news:RKLck.1120$bn3.583@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@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:
========

1. 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@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.

10. 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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy
©2012 Google