7 Replies Latest reply on Feb 1, 2013 6:11 PM by User599655-Oracle

    Unique DBID for an Oracle instance

      Hello DB gurus,

      We have requirement with our ETL to uniquely identify where(which instance) the data is getting extracted - we have a placeholder/attribute in our data-mart tables to identify this data for reporting etc.,

      Now, dbid, db_unique_name of the V$DATABASE is not feasible, since if the same db files are copied over to a different host - it continues to be the same - unless & until the end-user/consumer/customer runs the DBNEWID utility on the new cloned instance. We cannot completely rely on this activity.

      So, can we assume "activation#" of the v$database to be unique.

        • 1. Re: Unique DBID for an Oracle instance
          Peter Häusler
          maybe the sys_guid can help you to get a unique id?


          • 2. Re: Unique DBID for an Oracle instance
            Sorry Peter,

            I am not looking out for generating an unique_id for my records.

            We have an ETL (ODI), where consumers/customer can use it to extract data from multiple instance. How can my ETL program identify the different instance;

            The DBID of the v$database will be same if it is cloned.

            For example,
            Customer extracts and loads data from production to a common data-mart. If they want to extract similar data from a test instance and load into a common data-mart. We should be able to identify where the data is extract from either production or the test instance. If the test instance is a clone of the production ( the dbid is the same).

            So my question was to confirm if we can use the activation# from the v$database to identify the uniqueness of the instance.

            Any thoughts of how this activation# gets generated ?

            I did bounce the DB couple of times, the activation# did not change. And i did clone an instance A and named B(same db_name) and the activation# are different -- can we assume that the cloned/different env will have different activation# and it is unique.

            • 3. Re: Unique DBID for an Oracle instance
              Yes you can surely do it because the database is instantiated when it is newly created, but till the time you are not recreating your database you are ok means for example lets say you perform incomplete recovery and open the database with resetlogs then activation# is reset/changed. Same also goes when you recreate the controlfile.

              Edited by: Karan on Feb 1, 2013 3:02 AM
              • 4. Re: Unique DBID for an Oracle instance
                Hemant K Chitale
                You shouldn't be using DBID or Activation#.

                You should be using a name that is meaningful to users. Generate the name as part of the ETL load job.

                Hemant K Chitale
                • 5. Re: Unique DBID for an Oracle instance
                  Surely the customers know which DB they are connecting to? They won't connect to the test database or PROD database without knowing which one they want to connect to, so can't you build in some logic which says "when i'm logging in to the test database using the test connection method, all of my data is from test". Then put some kind of information about it in the data-mart to say where it came from?
                  • 6. Re: Unique DBID for an Oracle instance
                    You are right, the Customer will surely know where they are running the ETL. The concern is, what if the test env is exact cloned/copy of production and the end used un-intentionaly forget to change the obvious parameters. we are trying to evaluate if we can build a extra layer of check in the Oracle Data Integrator ETL tool, to identify what instance the data is extracted from.
                    • 7. Re: Unique DBID for an Oracle instance
                      Yes, exactly. The ETL execution event handler is a concurrent program in the source Oracle Applications system, and we are looking for an attribute which can uniquely identify the source db instance. The concern is, if the test env is a exact cloned/copy of the production - and the end-user un-intentionally forgot to change the obvious parameters and initiates the ETL !!

                      Alternatively, we can start building a repository in the data-mart with composite keys/parameters(dbid, name, controlfile_created, controlfile_sequence#, activation#, db_unique_name and lastly a FND application profile value) of the source instance and generate a unique identified in the DB. But we are evaluating to minimize that effort.