Transcript 2011_338_Sims_ppt

Reducing Downtime on
Upgrades or Migrations to
Oracle Database Server
April C. Sims, DBA
Southern Utah University
Session # 338
Book Author
Executive Editor
Best Practices Booklet
Reducing Downtime
• Planned Downtime is the largest source of
outages
• Narrowing Planned Downtime
• Reducing Risk
• Step-Ordered Approach
• Maximum Availability Architecture
• Lesser Known Processes and Procedures
Upgrade Methods
•
•
•
•
•
•
Transportable Tablespaces
EXPORT/IMPORT , DATA PUMP
DBUA vs. Manual Upgrade
Using RMAN to Upgrade/Downgrade
Standbys – Data Guard, Flashback & GRP
Changing Word Size 32-bit 64-bit – both
OS and Database
• Changing Storage – ASM, OFA
Step-Ordered Approach
• Backwards compatibility automatically built
into Oracle products.
• Database Upgrade – 2 events – binary
and data dictionary
• Outage window smaller
• Incremental fall back position(s) as part of
migration path
Higher Level Components
•
•
•
•
•
•
Oracle Net Services: LISTENER.ORA, SQLNET.ORA
Clients (SQL*Net, JDBC, ODBC )
RMAN Catalog, and Database
Grid Control Repository Database
Grid Control Management Agents
ASM (Automatic Storage Management) and CRS
(Clusterware)
• PL/SQL Toolkit
• Transportable Tablespaces (TTS)
Recommended Migration Order









Listener
RMAN version as part of a Catalog Repository
RMAN Catalog Repository Database
Grid Control Database
Grid Control Agents
Clients—SQL*Plus, Instant client, ODBC, JDBC, among others
ASM and/or CRS
Database
Optimizer
Compatibility Issues
• Export/Import and DataPump
• Client compatibility – supported on
terminal release
• Transportable Tablespaces supported,
similar compatibility issues
Client Compatibility
• ODBC *
• SQL*Plus, Instant Client, SQL Developer *
• JDBC, JDK—Application specific
• Precompilers—Application specific
• Export/import or data pump—MOS article, very strict guidelines
• Database links*
• 32/bit to 64/bit **—SQL*Plus, C, Cobol, database link
• PL/SQL features compatibility— features @ lowest version client
• Features availability—New release features @ lowest version client
• BEQUEATH connections are not supported b/t releases
* Few issues found with this component when connecting to a downlevel database.
Reducing Upgrade Downtime
• DBUA vs. Manual Methods – which one takes longer?
• What is an Oracle DB upgrade? Upgrading the Data Dictionary
• What tablespaces are required to be online for an upgrade?
SYSTEM, SYSAUX, UNDO and/or ROLLBACK SEGMENTS
Either offline normal or READ ONLY for application tablespaces
Transportable Tablespaces can be used for an Oracle upgrade just for
this reason!
Reducing Exp/Imp/Data Pump
Downtime
• Technical limitations
• Cross-platform migrations that can’t use TTS
• Converting to characterset that isn’t a superset
Data Pump Access Methods
1. Data File Copying – Fastest, TTS, converts metadata
2. Direct Path – 2nd fastest, default
3. External Table – Parallel, external table, SQL engine,
NETWORK_LINK for EXPORT
4. Network Link Import – slowest, INSERT SELECT
statement over a database link.
Improving Import Performance
• Influence Access Method – Parallelize Data Pump
• Adjust initialization parameters – increase sort_area_size,
shared_pool_size, sga_max_size, etc..
• Turn off flashback, archivelog, auditing, db_block_checking,
recycle bin, job queues, etc…
• Grant exempt access policy to “userdoingexportimport”
• MAX_DUMP_FILE_SIZE = unlimited
• CURSOR_SHARING
• Turn on autoextend for UNDO/TEMP, increase
UNDO_RETENTION
• Split indexes, constraints, ref_constraints, statistics separate
step.
• ****Turning off archivelog ramifications – Streams, DG
RMAN – Upgrade/Downgrade
• One-off OS upgrades
• Changing word sizes
• No additional ORACLE_HOME needed
• One-offs, patchsets, version changes
• Trial restores
• Downgrade different than original
• Clone a user-managed backup
• Move ASM- AUXILIARY DESTINATION
Same as Manual Method – any pre/post tasks
Automatic Tempfile Creation
• Tempfiles, removed, corrupted or not
available
• Cycle database…
• Available since 10g+
• Demo
Transportable Tablespaces
• TTS compartmentalizes the physical database objects ( tables &
index segments) into a moveable entity.
• Different utilities carry out subtasks: Can be a combination of
Export/import, data pump, DBMS_FILE_TRANSFER, and RMAN
• Changes the datafile headers associated with a tablespace.
• Source and target 8i+, Compatible charactersets
• Not all database objects can be transferred.
• TTS in DataGuard – metadata propogated, datafiles need to be
manually copied. Can be from primary or standby.
• As of 10.2+ can create TTS from RMAN backupsets – uses an
auxiliary instance like a TSPITR.
• Avoid Initial Long Refresh for Materialized Views
Why use TTS?
• Exports only the metadata of the objects, not the physical data
(rows)
• Indexes don’t have to be recreated
• Does require advance work to identify/isolate/move both
transportable and non-transportable objects.
• Excellent DBA training project!
• 10g+ across OS platform versions
• Standard Edition can only import TTS
• Both must be the same characterset
• Ok to change word-size
• Data Pump Compatible Parameter
• NLS Conversions
• Block Size (Older than 10g require same block size)
TTS Project List
• Restoring an unrecoverable database (dictionary
corruption or can’t be recreated with exp/imp)
• Upgrading a database (fastest way if datafiles stay in
original location, can be on SAN, NAS device)
• Migrating to a different operating system
• Migrating or consolidating ASM datafiles
• Exp/Imp Data Warehouse partitions
• Archiving Historical Data
• TSPITR
• Sharing a read-only tablespace between databases
Cloning $ORACLE_HOME (s)
•
•
•
•
•
•
Patch Sets 11.2.0.2 and higher are supplied as full releases
Out-of-place Upgrades
Multiple $ORACLE_HOMES
OUI-based command-line utility
Exact copy including one-off patches – binary
Clone to other Hosts, different ports, not just Database
DataGuard + Flashback
•
•
•
•
Enable Flashback
Guaranteed Restore Points (GRP)
Lost Write Detection/Corruption Prevention
Additional license may be needed for
standby hardware **
**most often failover limited to twice/year
unless unlimited license.
GRP without Flashback
• Create Guaranteed Restore Point WITHOUT Enabling Flashback
•
SQL> CREATE RESTORE POINT <rpname> GUARANTEE FLASHBACK DATABASE;
• Still creates flashback logs, so other initialization parameters related to
FRA must be configured.
• Saves flashback log space for workloads where the same blocks are
repeatedly updated, nightly batch loads
• This process generates both UNDO and REDO resulting in more area
used
• Drop guaranteed restore point immediately reclaims all space vs.
more steps to disable Flashback Database.
Not just for Failing Over
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Preventing or fixing physical corruption
Fixing logical corruption
Reversing an application vendor upgrade
Batch job reversal
Untested hot fix
Untested Oracle patch
Stress testing
Testing Oracle upgrades
Testing ASM, OMF, SAME, or OFA changes
Testing hardware updates or changes
Testing OS upgrades, patches, or changes
Testing Network or SQL*Net parameter changes
Real application testing **additional license
SQL performance analyzing **additional license
Database States - DataGuard
Primary
Active Data
• TRANSPORT-ON
Guard **license
• TRANSPORT-OFF
Physical standby (REDO APPLY)
• APPLY-ON
• APPLY-OFF
Snapshot standby (REDO APPLY)
• APPLY-OFF
****NO APPLY-ON (no longer a snapshot in time)
Logical standby (SQL APPLY)
• APPLY-ON
• APPLY-OFF
32-bit to 64-bit/64-bit to 32-bit
Convert between 32-bit and 64-bit word sizes as well as 32-bit and 64-bit
Operating Systems
•
•
•
•
•
EXPORT/IMPORT
Transportable Tablespaces can convert as well, beginning with 10g can
always be done with the same or higher compatibility setting.
RMAN – same OS platform.
Oracle Upgrades – catpatch.sql, catalog.sql , catproc.sql If you are
changing word-size during a migration, upgrade, or downgrade operation
running the appropriate script changes the word-size.
SQLPLUS – for changing word-size in between releases
Changing IP/Hostname
• Oracle Database Server isn’t affected by this change
• How to propagate the new hostname/IP address? –OID, LDAP,
Oracle Connection Manager
• LISTENER_NETWORKS (11.2.x)allows you to resolve/change the
listener name alias for other listeners through a local tnsnames.ora
This doesn’t work with Transparent Application Failover (TAF).
• Affects SQLNET-related changes – initialization parameters,
listener.ora, sqlnet.ora
• Side NOTE: Oracle Wallet – just copy over to new server, upgrade
by opening in new $ORACLE_HOME/owm and save as…version
10g to 11g
More Information, Questions?
High Availability Blog
http://aprilcsims.wordpress.com