This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 11, 2013 7:12 AM by RK334 Go to original post RSS
  • 15. Re: Oracle 11g : Sequence Issues after impdp
    User286067 Journeyer
    Currently Being Moderated
    RK334 wrote:
    Raj,

    Thanks for the information. Can you please check my original post and let me know if my understanding is correct and the sql's i provided suffice after the import?
    I could but there isn;t sufficient information, do ALL PKs in your db use sequences? You might be better off writing some queries manually and then make decision. If you have 6 hour downtime, you shouldn't encounter this problem since app will be down.

    Rather than taking live expdp for testing, why don't you restore your latest backup to a dummy db, and extract data from there? Live would be ideal but you will have to un-necessarily deal with these pesky issues.

    Raj
  • 16. Re: Oracle 11g : Sequence Issues after impdp
    RK334 Newbie
    Currently Being Moderated
    I could but there isn;t sufficient information, do ALL PKs in your db use sequences? You might be better off writing some queries manually and then make decision. If you have 6 hour downtime, you shouldn't encounter this problem since app will be down.
    All the primary keys doesn't use sequences.. which is why i am using the below query to get the constraint type as Primary key and checking from the data type from the dba_tab_columns to see if it is a Number. This should possibly give all the sequences that are being used a primary keys right?

    select table_name, column_name, utl_raw.cast_to_number(high_value) as highval
    from dba_Tab_columns
    where owner = 'PRODUCTION_OWNER'
    AND DATA_TYPE= 'NUMBER'
    AND (OWNER, TABLE_NAME, COLUMN_NAME) IN
    (SELECT CC.OWNER, CC.TABLE_NAME, CC.COLUMN_NAME
    FROM DBA_CONS_COLUMNS CC
    JOIN DBA_CONSTRAINTS C
    ON CC.OWNER=C.OWNER
    AND CC.CONSTRAINT_NAME=C.CONSTRAINT_NAME
    WHERE C.CONSTRAINT_TYPE ='P'
    )
    ORDER BY 3;
  • 17. Re: Oracle 11g : Sequence Issues after impdp
    User286067 Journeyer
    Currently Being Moderated
    RK334 wrote:
    All the primary keys doesn't use sequences.. which is why i am using the below query to get the constraint type as Primary key and checking from the data type from the dba_tab_columns to see if it is a Number. This should possibly give all the sequences that are being used a primary keys right?
    This should possibly give all the sequences that are being used a primary keys right?
    Right only if,
    1. you are assuming that all PK where PK column is number will use a sequence (possible but not a rule)
    2. Your statistics are accurate as of expdp time (this could be a potential risk)

    So, you can try that, but I dont think it will cover all situations.
  • 18. Re: Oracle 11g : Sequence Issues after impdp
    clementeOTN Explorer
    Currently Being Moderated
    Hi

    I had similar problems with sequences and export and I solved at this way ,I hope it helps

    What we want is a a"+consistent+" export, there was an option in old oracle export utility , "CONSISTENT=Y" .This option dissapeared in the new expdp, but you can do something similar
    expdp has the FLASHBACK_TIME option, if you set yor FLASHBACK_TIME to +"now"+ , you can obtain a consistent export, it would be something like

    exdp user/passwd file=exp.dmp directopry=export_directory FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"

    At this way, you "freeze" your database in a determinate SCN so the sequences and all the other objects must be ok (exactly as they are in the scn of "now")

    P.D be careful with your UNDO_RETENTION_TIME because it must be bigger than the export time ;-)

    P.D2 if you are migrating from 10g to 11g be carefull with the deferred_segment-creation too
  • 19. Re: Oracle 11g : Sequence Issues after impdp
    Paul M. Oracle ACE
    Currently Being Moderated
    expdp has the FLASHBACK_TIME option
    Which is exactly what I said in my first post in this thread...

    That said, do you think this is a good method to migrate a database ? Well, may be, if your database is very small, or you have a very large downtime window for the migration. In my experience, and surely for all my customers, a migration has some MUSTs, at minimum :

    1. No data losses are acceptable

    2. The down time has to be the minimum possible, and anyway not several hours.

    FLASHBACK_TIME (or FLASHBACK_SCN) option is necessary to guarantee consistency when the export is done while users are working, but in this way you'll almost surely lose some transactions.

    If users are not working those parameters are not needed, but the down time increases...

    So ?
  • 20. Re: Oracle 11g : Sequence Issues after impdp
    User286067 Journeyer
    Currently Being Moderated
    Paul M. wrote:
    expdp has the FLASHBACK_TIME option
    Which is exactly what I said in my first post in this thread...
    I am not sure if flashback will help with cached sequences ..

    Raj
  • 21. Re: Oracle 11g : Sequence Issues after impdp
    RK334 Newbie
    Currently Being Moderated
    Guys,Thanks all for your replies. It was really helpful.


    Oracle reads and exports consistent data when the database is offline (application servers are brought down and all users kicked out of their connections :)) --

    This is the suggestion from one of my co-DBA's, posting it here for everyone.

    1) As mentioned earlier, downtime is not an issue for me. -- I have a lenience on that. So I am gonna safely bring down the application and carry out an export. -- This should probably solve all my issues with the sequences.

    2) Any sequences that are cached in my source database will be lost and not re-used again.

    I am going to make few tests and publish the results for the statements mentioned above.
  • 22. Re: Oracle 11g : Sequence Issues after impdp
    Paul M. Oracle ACE
    Currently Being Moderated
    I am not sure if flashback will help with cached sequences ..
    And what's the problem ? that you'll lose some numbers ? If this is a problem, sequences are not what you need, since Oracle never guarantees that sequences are gap free.
  • 23. Re: Oracle 11g : Sequence Issues after impdp
    RK334 Newbie
    Currently Being Moderated
    RK334 wrote:



    *1) Can we ascertain that the export taken when the source database is offline would eliminate the sequences issue.*
    All applications to this database are brought down.

    The last_number in dba_Sequences match with the one in the dump file taken as expdp.
    >
    2) As this is being done in Production, I would like to make few checks to ensure that the sequences are properly imported. ---

    Again reading few websites and oracle Forums, i found the below sql's..

    select table_name, column_name, utl_raw.cast_to_number(high_value) as highval
    from dba_Tab_columns
    where owner = 'PRODUCTION_OWNER'
    AND DATA_TYPE= 'NUMBER'
    AND (OWNER, TABLE_NAME, COLUMN_NAME) IN
    (SELECT CC.OWNER, CC.TABLE_NAME, CC.COLUMN_NAME
    FROM DBA_CONS_COLUMNS CC
    JOIN DBA_CONSTRAINTS C
    ON CC.OWNER=C.OWNER
    AND CC.CONSTRAINT_NAME=C.CONSTRAINT_NAME
    WHERE C.CONSTRAINT_TYPE ='P'
    )
    ORDER BY 3;


    SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, LAST_NUMBER
    FROM DBA_SEQUENCES
    WHERE SEQUENCE_OWNER = 'PRODUCTION_OWNER'
    ORDER BY LAST_NUMBER

    If I relate the last_number with the highval and if they are same, does that mean the sequences are imported properly.

    Note: We have sequence caching done and we are on RAC.
    Do not use DBA_TAB_COLUMNS.HIGH_VAL as this entirely depends on the database and the stats that were collected recently on it.

    Find below the approach that i followed to ensure that all sequences are imported properly.

    1) select sequence_owner, sequence_name, last_number
    from dba_Sequences
    where sequence_owner in ('TESTSCHEMA');

    2) Export the database after bringing down all application connections.

    Take the dumpfile and get the sequences from the dump file. Use the below paramter file.

    cat sequece_only.par
    dumpfile=expdp_allschemas%U.dmp
    logfile=sequece_only.log
    sqlfile=sequences.sql
    directory=dump
    include=sequence

    this gives all the sequences that are present in the dump file.

    3) Select the sequences immediately after the export to ensure that sequences remain unchanged.
    select sequence_owner, sequence_name, last_number
    from dba_Sequences
    where sequence_owner in ('TESTSCHEMA');

    this way we can ensure that sequences doesnt get altered if the application is not writing anything to the database.
1 2 Previous Next

Legend

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