This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Sep 23, 2013 10:20 PM by Hemant K Chitale RSS

Unable to change Oracle DBID

af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
Currently Being Moderated

Hi

 

I'm restoring Production Oracle Server database backup to Disaster Recovery database Server using Symantec Backup Exec 2010.

 

Both Production & DR servers (Windows 2008 R2) have identical Oracle (Oracle 11g)  database settings, database file location and database instance names.

 

In order to get successful restore to DR Oracle server, DBID of DR Oracle server should be same as Production server. I have been trying to change DBID of DR Oracle server, but it is not changing

 

I have run following commands on Oracle DR server to change DBID:

 

 

SQL>shutdown immediate;

SQL>startup nomount;

RMAN>set DBID=2701663xxx;

 

 

The Output of set DBID= 2701663xxx, is below:


executing command: SET DBID


when I go back to SQL> prompt and type select dbid from v$database; then it shows me the current ID of Oracle DR Server and doesn't change the DBID to 2701663xxx


Can someone please help ?


Thanks

  • 1. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You cannot manually change the DBID of an existing database.  Oracle sets it automatically.

     

    If you are restoring Production to DR, you need to drop / remove the DR database and then do a restore.

     

    Hemant K Chitale

  • 2. Re: Unable to change Oracle DBID
    JohnWatson Guru
    Currently Being Moderated

    Using the SET command in RMAN doesn't change anything, it merely tells RMAN which database you want to work with. To change the DBID, use DBNEWID utility, nid.exe

     

    c:\users\john>

    c:\users\john>%ORACLE_HOME%\bin\nid

     

    DBNEWID: Release 11.2.0.3.0 - Production on Tue Sep 17 08:14:51 2013

     

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

     

    Keyword     Description                    (Default)

    ----------------------------------------------------

    TARGET      Username/Password              (NONE)

    DBNAME      New database name              (NONE)

    LOGFILE     Output Log                     (NONE)

    REVERT      Revert failed change           NO

    SETNAME     Set a new database name only   NO

    APPEND      Append to output log           NO

    HELP        Displays these messages        NO

     

     

    c:\users\john>

  • 3. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    JohnWatson

     

    Thanks for your reply. DBNEWID can randomly generate a new DBID, but in my case, I would like to change it to customise DBID, same as Production oracle server.

  • 4. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    Hemant

     

    Thanks for your reply. According to following symantec article below, in order to restore original Oracle database ,DBID of target Oracle server has to be changed, otherwise Oracle restore will not be successful:

     

    http://www.symantec.com/business/support/index?page=content&id=TECH60371&actp=search&viewlocale=en_US&searchid=1379030446749

  • 5. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    It doesn't say "DBID of target has to be changed".  It says "SET the DBID".  The DBID to be set must be the same as the source database so that you can restore the source database controlfile and then restore the database.

     

    Hemant K Chitale

  • 6. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    Hemant

     

    Thanks for your quick reply. I am new to Oracle databases and only familiar with general SQL Plus commands.Can you please provide me the proper SQL Plus or RMAN commands to change the DBID of DR Oracle database,so that I can perform the Oracle restore successfully ?

     

    Also note that I am using Symantec Backup  Exec 2010 for backing up and restore Oracle database.

     

    Thanks

  • 7. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Once again : You DON'T CHANGE THE DBID OF THE DR DATABASE.   YOU DROP  / REMOVE THE DR DATABASE AND RESTORE FROM A BACKUP OF THE SOURCE (Production) Database.

     

    In order to be able to restore a controlfile (of the source - production database), you need to use the command "SET DBID" to have the RMAN session ready to restore a controlfile of the same DBID.  Else, it will not restore the controlfile.

     

    Hemant K Chitale


  • 8. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    If I drop the database then all database instance settings will be removed from the Symantec Backup Exec Agent installed on DR Oracle server and I will not be able to perform restore operation. I have done the same thing with DR SQL Server i.e installed the SQL Server with same instance as source database server, created same databases as source server and restore the source database to DR SQL Server without any problem, but now stuck with Oracle restore.

  • 9. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You are confusing the database with configuration and parameter settings.

     

    The database is not the installed rdbms software (binaries, dlls, libraries etc).   The database is not any configuration and parameter files.

    The database is the database files (system, undo, sysaux etc).  Dropping / Removing the database requires removing the datafiles.  If you remove the Windows service for Oracle, you'd need to recreate the Windows service.

     

    An Oracle database is very different from a SQL Server database.

     

    You need to attend an Oracle course in Backup and Recovery  --- and since you are building a DR instance, a course in DataGuard.

     

    See the Oracle instructions on Restoring a database to a new host :

    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmadvre.htm#i1007814

     

    Hemant K Chitale

  • 10. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    >Oracle 12

     

    This is surprising.  Who made the call to go production (and DR) with 12c within less than 3months of its GA ?  Who developed the application, who tested the application against 12c ?

     

    Hemant K Chitale


  • 11. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    Sorry it was my mistake. It is Oracle 11g

  • 12. Re: Unable to change Oracle DBID
    vk82 Explorer
    Currently Being Moderated

    First we need to understand :

    • A duplicate database, which is a copy of the target database (or a subset of the target database) with a unique DBID. Because a duplicate database has a unique DBID, it is entirely independent of the primary database and can be registered in the same recovery catalog as the primary database. Typically, duplicate databases are used for testing.
    • A standby database, which is a special copy of the primary database that is updated by applying archived redo logs from the primary database. A standby database does not get a new DBID.

    After got an idea do it in the manner as provided on below link.

    Creating and Updating Duplicate Databases with RMAN

  • 13. Re: Unable to change Oracle DBID
    af5bbdf1-8a49-4494-865f-4950e3aac85e Newbie
    Currently Being Moderated

    Hi Hemant

     

    As per your advise, I dropped the databse from DR Oracle server.After dropping the database, when I ran shutdown immediate command, then it says ORACLE not available (See below):

     

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

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

    C:\Users\testuser

     

    >sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 24 11:07:56 2013

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

    Connected to an idle instance.

    SQL> shutdown immediate;
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist

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

    I have also used RMAN to change DBID as follows:

    Microsoft Windows [Version 6.1.7601]
    Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

    C:\Users\testuser

     

    >rman

    Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 24 11:23:01 2013

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

    RMAN> connect target /

    connected to target database (not started)

    RMAN> set dbid = 2701663641;

    executing command: SET DBID

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

    After these steps, when I ran restore job from Backup Exec, then the backup job failed with folllowing error message:

    Final error: 0xe000848c - Unable to attach to a resource.  Make sure that all selected resources exist and are online, and then try again.  If the server or resource no longer exists, remove it from the selection list. Edit the selection list properties, click the View Selection Details tab, and then remove the resource.
    Final error category: Resource Errors"

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

     

    Please note that above Backup Exec error message is the same old message, before I dropped the database. Any idea why it is happening ? Is it because ORACLE is not online or something else

     

    Thanks

  • 14. Re: Unable to change Oracle DBID
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Have you done a STARTUP NOMOUNT ?  The message "(not started)" indicates that the instance is not started.

     

    Hemant K Chitale

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points