Transcript Overview of First4DB Monitoring

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
 All non-DBAs should know how to
properly request them

First4 Database Partners Inc.
Typical Environment
Test
Development
First4 Database Partners Inc.
Quality
Assurance
Production
Non-Typical Environment
Development
Linux 32-bit
11.2.0.3
PSU Jan 2012
First4 Database Partners Inc.
Quality
Assurance
Linux 64-bit
10.2.0.5
PSU April 2011
Test
Production
Linux 64-bit
Linux 64-bit
10.2.0.5
PSU April 2011 10.2.05
PSU April 2011
What’s a refresh request?
Specific Database Object(s)
 Specifict Schema(s)
 Entire Database
 Refresh plus upgrade/downgrade

First4 Database Partners Inc.
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.
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.
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 – recreation of control file(s) required on
target
First4 Database Partners Inc.
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.
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.
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.
Security Considerations




Production credentials copied to nonProduction
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.
External Environment
References
Database Links
 URLs within tables
 Encryption keys

First4 Database Partners Inc.
Repeatable
Build an infrastructure that makes
the refresh process easily
repeatable
 Script everything
 Have good documentation

First4 Database Partners Inc.
Demonstration
Data Pump schema refresh
 Oracle Home Clone
 RMAN Duplicate

First4 Database Partners Inc.
The End
[email protected]
First4 Database Partners Inc.