This content has been marked as final. Show 7 replies
maybe the sys_guid can help you to get a unique id?
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.
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.
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
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
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?
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.
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.