1 / 16

Oracle Database Refreshes

Oracle Database Refreshes. Steve Recsky Complete DBA Inc. A Partner of First4 Database Partners Inc. First4 Database Partners Inc. Why Talk About Refreshes?. They are a necessary evil They are a repeated request They can be dissimilar in nature All DBAs should know how to do them

guinevere-mckinney
Download Presentation

Oracle Database Refreshes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Database Refreshes Steve Recsky Complete DBA Inc. A Partner of First4 Database Partners Inc. First4 Database Partners Inc.

  2. Why Talk About Refreshes? • They are a necessary evil • They are a repeated request • They can be dissimilar in nature • All DBAs should know how to do them • All non-DBAs should know how to properly request them First4 Database Partners Inc.

  3. Typical Environment Test Development Quality Assurance Production First4 Database Partners Inc.

  4. Non-Typical Environment Development Linux 32-bit 11.2.0.3 PSU Jan 2012 Test Linux 64-bit 10.2.0.5 PSU April 2011 Quality Assurance Linux 64-bit 10.2.0.5 PSU April 2011 Production Linux 64-bit 10.2.05 PSU April 2011 First4 Database Partners Inc.

  5. What’s a refresh request? • Specific Database Object(s) • Specifict Schema(s) • Entire Database • Refresh plus upgrade/downgrade First4 Database Partners Inc.

  6. Terminology • Requestors need to be educated on the difference between the following: • Instance • Database • Schema • User • Many applications refer a schema as a database so the request comes across as a database refresh First4 Database Partners Inc.

  7. Refresh Methods • Data Pump Export/Import (assumes target instance already built) • Schemas • Tables • Transportable Tablespaces (Require source tablespaces to be in READ ONLY mode while export of Metadata takes place) • Export/Import (traditional) • RMAN Duplicate First4 Database Partners Inc.

  8. Refresh Methods (Cont…) • Disk copy (SnapShot, SnapMirror, Shadow image, BCV, etc.) with database quiesce • Be aware of Oracle Database ID (Use DBNEWID Utility) • Manual copy of database and related files with source database down – re-creation of control file(s) required on target First4 Database Partners Inc.

  9. Data Pump (DP) vs. Traditional (Trad) • DP faster • Trad not totaly dependency aware • DP has params for • Exclusion - Exclude specific object types, e.g. EXCLUDE=TABLE:EMP • Version - Version of objects to export • Mapping – Remap objects from source to target, e.g. REMAP_SCHEMA=hr:scott • Trad must be cautious with NLS_LANG • DP only available in 10g+ First4 Database Partners Inc.

  10. Instance Parameter Considerations • QA (Quality Assurance) environments typically need to match Production exactly so that performance related issues can be readily duplicated (like-for-like) • Development and Test environments typically have a scaled-down resource footprint so more of them can be fit onto a server and/or cheaper/smaller servers can be used • Queues – consider initially setting queues to zero so no jobs run when the copied database is first started First4 Database Partners Inc.

  11. Oracle Home Cloning • Use Oracle Home cloning when you are copying a database to a target where there is no Oracle Home • Cloning guarantees the installation and patch levels are exactly the same between the two environments • tar or zip the entire Oracle Home then copy it to the destination and extract • Use the runInstaller or setup to clone runInstaller –silent –clone ORACLE_HOME=… ORACLE_HOME_NAME=… ORACLE_BASE=… First4 Database Partners Inc.

  12. Security Considerations • Production credentials copied to non-Production • Passwords • Database Links • Data Masking (obscuring sensitive data) • Manual data masking • Oracle Data Masking Pack • Comprehensive and Extensible Mask Library • Sensitive Data Discovery and Application Integrity • Sophisticated Masking Techniques • Secure High Performance Mask Execution First4 Database Partners Inc.

  13. External Environment References • Database Links • URLs within tables • Encryption keys First4 Database Partners Inc.

  14. Repeatable • Build an infrastructure that makes the refresh process easily repeatable • Script everything • Have good documentation First4 Database Partners Inc.

  15. Demonstration • Data Pump schema refresh • Oracle Home Clone • RMAN Duplicate First4 Database Partners Inc.

  16. The EndSteve.Recsky@first4db.com First4 Database Partners Inc.

More Related