1 / 26

Reducing Downtime on Upgrades or Migrations to Oracle Database Server

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

leanne
Download Presentation

Reducing Downtime on Upgrades or Migrations to Oracle Database Server

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. Reducing Downtime on Upgrades or Migrations to Oracle Database Server April C. Sims, DBA Southern Utah University Session # 338

  2. Book Author Executive Editor Best Practices Booklet

  3. 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

  4. 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

  5. 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

  6. 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)

  7. 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

  8. Compatibility Issues • Export/Import and DataPump • Client compatibility – supported on terminal release • Transportable Tablespaces supported, similar compatibility issues

  9. 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 down-level database.

  10. 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!

  11. Reducing Exp/Imp/Data Pump Downtime • Technical limitations • Cross-platform migrations that can’t use TTS • Converting to characterset that isn’t a superset

  12. 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.

  13. 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

  14. 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

  15. Automatic Tempfile Creation • Tempfiles, removed, corrupted or not available • Cycle database… • Available since 10g+ • Demo

  16. 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

  17. 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)

  18. 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

  19. 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

  20. 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.

  21. 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.

  22. 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

  23. Database States - DataGuard Primary • TRANSPORT-ON • 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 Active Data Guard **license

  24. 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

  25. 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

  26. More Information, Questions? High Availability Blog http://aprilcsims.wordpress.com

More Related