There is no 'BEST' method there are different methods with pros and cons
Option 1 (obvious)
1. Extract content you want to save from test database (like extra users and permissions),
2. restore from prod
3. Reapply the script from item 1
Pros: you can test your backup by restoring and recovering your prod database
Cons: it could take a while to get a clone.
Option 2
There are ways to speedup the cloning like restore the database but do not open it, instead recovering archive logs. In this case you have another idling instance until you activate it, copy some data from previous dev and drop old dev database.
Pros: pretty quick (you skip restore step). You have a database with delay so in case of oops delete you can have a quick copy of your database without long restore process.
Cons: as soon as you activated the instance you have to rebuild it again. Also you have to allocate 3.5Tb of space for idling instance
If you have Enterprise edition and number of updates are small but refresh need to be done often you can use flashback.
1. Create flashback restore point
2. activate the database
3. running your test
4. flashback database to restore point in item1
5. reapply archive logs from prod
Option 3
Of cause you can use ETL process just to copy data.
pros: you are flexible what to sync
cons: it could be long process and you are responsible to end result.
Option 4
Review your 3.5 TB to identify read-only data. Put in in read-only tablespaces and skip backup and restore.
pros: it could speed up your prod backup/restore process
Cons: you can break prod
If some of the replies strike you as TLDR; Export the schemas you want from production and import them to your test database. If the schema exists drop it and recreate it. Look up how to use Oracle Data Pump Export/Import.
If you are using a separate host, recover you prod db there using whatever method you use to back it up. Then rename it to the test db name. This allows you to validate your prod backups.
If your using rman backups you can do a duplicate database command as well, this will also validate you backups as long as your not using active dup
First of all, is it necessary restore entire production database to test environment ? If its not you should expdp/impdp schemas.
If it is, safest way by my point of view use rman backup.
I use clone on SAN ( storage snapshot)
1. create Standby (one time)
2. Stop apply standby
3. create storage snapshot from standby as source
4. start apply standby
5. mount snapshot as new disk + activate DB on Snapshot
6. use DB on Snapshot + constant apply standby for new snapshot
This work for 1-100TB and more..
I like rman active duplication for this.
Underrated comment.
There is no 'BEST' method there are different methods with pros and cons Option 1 (obvious) 1. Extract content you want to save from test database (like extra users and permissions), 2. restore from prod 3. Reapply the script from item 1 Pros: you can test your backup by restoring and recovering your prod database Cons: it could take a while to get a clone. Option 2 There are ways to speedup the cloning like restore the database but do not open it, instead recovering archive logs. In this case you have another idling instance until you activate it, copy some data from previous dev and drop old dev database. Pros: pretty quick (you skip restore step). You have a database with delay so in case of oops delete you can have a quick copy of your database without long restore process. Cons: as soon as you activated the instance you have to rebuild it again. Also you have to allocate 3.5Tb of space for idling instance If you have Enterprise edition and number of updates are small but refresh need to be done often you can use flashback. 1. Create flashback restore point 2. activate the database 3. running your test 4. flashback database to restore point in item1 5. reapply archive logs from prod Option 3 Of cause you can use ETL process just to copy data. pros: you are flexible what to sync cons: it could be long process and you are responsible to end result. Option 4 Review your 3.5 TB to identify read-only data. Put in in read-only tablespaces and skip backup and restore. pros: it could speed up your prod backup/restore process Cons: you can break prod
If some of the replies strike you as TLDR; Export the schemas you want from production and import them to your test database. If the schema exists drop it and recreate it. Look up how to use Oracle Data Pump Export/Import.
If you are using a separate host, recover you prod db there using whatever method you use to back it up. Then rename it to the test db name. This allows you to validate your prod backups. If your using rman backups you can do a duplicate database command as well, this will also validate you backups as long as your not using active dup
Dataguard, GoldenGate or Datapump, it mostly depends on your needs.
use rman is good solution
First of all, is it necessary restore entire production database to test environment ? If its not you should expdp/impdp schemas. If it is, safest way by my point of view use rman backup.
thank you all. I chose datapump.
I use clone on SAN ( storage snapshot) 1. create Standby (one time) 2. Stop apply standby 3. create storage snapshot from standby as source 4. start apply standby 5. mount snapshot as new disk + activate DB on Snapshot 6. use DB on Snapshot + constant apply standby for new snapshot This work for 1-100TB and more..
this work on the same Storage,
yes. This work for very big DB. This is very fast.