Forum Stats

  • 3,781,559 Users
  • 2,254,530 Discussions
  • 7,879,753 Comments

Discussions

Schema Refresh- Ideas/Best Practice

Hello-

I am hoping to see if there are some better solutions out there that I could use for my setup. I have a 350GB schema that I would like to refresh from prod to test daily. It contains approx 11K indexes and 17K ref constraints. The DB is 19.11 and on oracle Linux 7.x with 8vcpu/32GB ram.

Using impdp(data pump) and only using the parameter EXCLUDE=STATISTICS this completes in 4 hours with parallel is set to 6. This time is still acceptable for me however this test db is no archive log mode.

The same refresh when test DB in archive log mode takes 8-9 hours. I want this to get down as low as possible; even when using EXCLUDE=INDEX and then getting the DDL for indexes.sql and setting parallel=4 for index create still did not change the behavior so I assume this is because of DB logging mode.


What is the best way/tool that I could use when the test DB is a PRIMARY DB in DataGuard config? I have looked into the TRANSFORM:y option but might not work for my setup. Since my TEST DB here is a PRIMARY DB in Data Guard setup in the test environment.

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 814 Gold Trophy

    Use a Transportable tablespace if you meet the limitations.

    User_I16HP
  • JohnWatson2
    JohnWatson2 Member Posts: 4,361 Silver Crown

    Nowadays I would usually do such things with PDBs. In your test DB, create a refreshable clone of your prod PDB, refreshed on a schedule (every half hour, perhaps). Then once a day clone it to your test PDB. That will likely be well under an hour.

    User_I16HP
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown

    @User_I16HP

    Just to clarify - do you want both the production DB and the test DB running in dataguard standby configurations. Is that why you want to run the refresh in archivelog mode so that the refresh propagates to the test standby?

    Regards

    Jonathan Lewis

    User_I16HP
  • User_I16HP
    User_I16HP Member Posts: 26 Red Ribbon

    Jonathan-


    Yes. I have prod in DG config & test in dg config. And would keep this going like this as a like-like setup requirement.

    I am refreshing from PROD PRIMARY to TEST PRIMARY. I apologize for not being clear on this.


    John-

    Thanks for this info as I just moved from 11g so I will look into this cloned PDB idea & it is helpful. I only want to refresh the TEST PDB from PROD PDB say at 6 AM ( if it does take that little time after initial setups). I will move my 19.11 stand alone to CDB/PDB arch in the coming months.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,848 Gold Crown

    @User_I16HP

    Thank you. I thought that's what you were saying, but it is a little unusual (though commendable if you want to do critical performacne tests properly), so I wanted to check because the fact that you want a secondary to be available as the standby at the same time could make an enormous difference to the best strategy.

    As for what consitutes the best strategy, I'll leave that to others to suggest - it's not a topic I'd claim expertise in.

    Regards

    Jonathan Lewis

    User_I16HP
  • User_I16HP
    User_I16HP Member Posts: 26 Red Ribbon

    Thanks for the helpful responses.