upgrade tips strategies

8/11/2019 Upgrade Tips Strategies

1/25

DatabaseUpgrade/Migration Options& Tips

Sreekanth ChintalaDatabase Technology Strategist


2/25

Global Marketing2 Confidential

Agenda

Very high level overview of Migration/Upgrade options No technical details


3/25


About the Speaker

West Virginia University (M.S)

Supporter of UT Longhorns

11+ Yrs of Oracle, 17+ years in IT

RACSIG Web chair (2008-2010)

Presenter at OIUG, OOW, Local user groups

Technical Editor

[email protected] [email protected]
mailto:[email protected]:[email protected]:[email protected]:[email protected]://www.amazon.com/gp/product/B001HSNI3U/ref=pd_lpo_k2_dp_sr_1?pf_rd_p=486539851&pf_rd_s=lpo-top-stripe-1&pf_rd_t=201&pf_rd_i=B001HVOYPS&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1B8EFM92A2GJ2B359GRV


4/25


Database Upgrade

Moving DB from one version to anotheron the same server

It is NOT:

Upgrading

the Server

Upgrading

the OS

Upgradingthe

Storage


5/25


Database Upgrade

10gR1 11gR2


6/25


Database Migrations

Move to another Server

Move to another Data Center

Move to another (later) Version

Migrations are typically done as part of End-of-Life refresh, usually followed by DatabaseUpgrade


7/25


Database Upgrade Methods

Database Upgrade Assistant GUI Uses GUI to upgrade

The DBUA performs pre-upgrade checks

Manual Upgrade Performed via SQL-Plus Commands

Use pre-upgrade script to validate Modify Cluster Registry entries for RAC Databases .profile changes, environment variables

Tips Use GUI method, especially for RAC Databases

Take a backup before the upgrade Check news groups to be aware of any known issues


8/25


Factors

Source &Target DB

Version

AcceptableDowntime

Size of thedatabase

Source &target

OperatingSystems


9/25


Concerns

Downtime

Performance

Data FieldConversions

UpgradeIssues

Fallback /Backout

Training


10/25


Database Migration Options

Complete Downtime Export and Import using Data Pump (or exp/imp) CTAS Backup & Restore Transportable Tablespaces

Minimal Downtime Logical/Physical Standby EMC SRDF Copy ASM Rebalance

Near Zero Downtime Oracle Streams Golden Gate


11/25


Export / Import

Database needs to be down or open read only Take an export on the source system Copy the export dump to destination system Import into the database

Advantages

Simple to use, proven track record Works well for smaller DB sizes (say < 50GB)

Gotchas

Size of the database dictates the amount of downtime needed

Import time is 3-4 times longer than export time Older Unix system may have 2G file size limitations.


12/25


Export / Import

10gR1

11gR2ExportCopy

Export

Copy

Tips Use NFS mount that can be accessed from both source and destination Data Pump provides many features

Review the nature of data. Bring over the statictables, partitions aheadof time, reduce the total downtime In case of RAC instances, leverage all instances to import different tables Indexes can be re-built on the destination. No need to import them Collect stats after the import Beware of security and password changes


13/25


SQL Plus-CTAS

Database needs to be down or open read only Copy the Tables via DB Link

Gotchas

Size of the database and the network throughput dictates theamount of downtime needed

Dont use it on Live tables

Advantages No intermediate storage needed

Operations can be performed in parallel for non-related tables


14/25


CTAS

10gR1 11gR2

Tips Review the nature of data. Bring over the statictables, partitions ahead

of time, reduce the total downtime

In case of RAC instances, leverage all instances to import different tables Indexes can be re-built on the destination. No need to import them Collect stats after the import

DB Link


15/25


Backup / Restore

RMAN incremental backups can reduce the downtime Copy the backups to destination system Restore into the database

Gotchas Heavy DML operations may prolong the Restore-Sync time Going through Tape can significantly add time to backup and restore

Typically few restore (tape) resources are allocatedAdvantages

Primary Database is up and running

Can use RMAN compression, parallel operations

RMAN only backups the changes and restores the needed files

Tips Use a shared disk (NFS) between the target server

If you have to go to tape, request dedicated backup channels until therestore is complete


16/25


Transportable Tablespaces

Create the empty database Create the Metadata of the source database in the new database Database files are moved from existing database to the other system

Gotchas Source and target systems should have the same character set andnational character set Limitations on indexes ( Function based/domain ) Limitations on snapshot/replication Tables and indexes to be part of the self contained set All the partitions of a partitioned table needs to be moved as a set

Advantages Use this option to move the databases across different platforms Faster because it bypasses usual data extract and load processes Efficient than export/import


17/25


Logical/Physical Standby Option

Available for 10.1.0.3 and above Very minimal downtime( time to switchover ) Must setup a Data Guard environment

Gotchas Need to modify Primary to setup Logical/Physical Standby OS must be the same ( family) between primary and standby

Primary needs to be shut down for DB Upgrade on the target SQL Apply reapplies the transactions. Heavy OLTP systems are not a

good candidate for Logical Standby Doesnt work for cross platform

Advantages Proven technology Changes to the production are copied over to the standby Newer versions, you can use it to test the performance using snapshot

standby Protection from Physical Corruption


18/25


Only available for EMC Storage - Symmetrix Remote Data Facility Manages real time copies of data volumes ( at the storage level)

Gotchas EMC solution for Symmetrix storage Physical corruption will get carried over to the other side

The target (individual) Lun size should be equal to or larger than sourceLun

Advantages Most simple solution to copy multi-terra byte DBs Most of the work is on the storage team to setup SRDF

SRDF can copy the incremental block changes after initial setup

SRDF Copy Option


19/25


ASM Rebalance Option

Leverage ASM rebalance feature to off-load data from one storagearray to another

Gotchas Will require downtime to bring up the new system

Will require downtime to perform DB upgrades

Advantages No disruption to primary database during rebalance No monitoring or governance during the operation


20/25


ASM Rebalance Option

Storage ArraysStorage Arrays

1. Add the New LUNs on the OLDsystem (OS)

2. Add the LUNs to the Disk Group

3. Perform Rebalance

4. Shutdown Old System

1. Create CRS,DB,ASM on new system2. Copy SPFILEs (DB,ASM)

3. Add the New LUNs on the New system(OS)

4. Bring up the database


21/25


Oracle Streams

Little or no downtime Source and Targets can be any version to any platform

Gotchas Limitations on some data types ( CLOB/BLOB) Extremely complex to implement & maintain Longer development cycles Need highly skilled DBAs

Advantages Can be used to migrate between different platforms and versions


22/25


Golden Gate Option

Little or no downtime Source and Targets can be any version to any platform

Gotchas Requires Golden Gate License investment of intermediate platform when moving from one OS Family

to another

Advantages Moderately complex to implement

Provides Fall back options

You can leverage the infrastructure many times

Best near Zero Downtime Option


23/25

Global Marketing

8i & 9i10g/11g Migration Overview

Goldengate Capture to Trails

Create Clone DB via RMAN

8i/9i

ASM Storage

Real-Time ReplicationFail back!Reverse ReplicationServer & Storage

Provisioning

DBA Begins

Migration

Production

App Testing

Switchover to

RAC Database

Fail back Strategy

Post-Migration


24/25


In place Migrations of OS

Complete Downtime to the server being upgraded Data Guard can be up and running while the migration is taking place Operating System Upgraded wipes out internal drives

Newer Cluster Version wipes out the Clusterware drives

Data Luns inside ASM are intact.


25/25


In place Migration Steps

DBA

1. Create and save (elsewhere) pfile from the spfile

Linux Engineer

1. Re-image all cluster nodes to new OS2. Post-build steps an 11g RAC cluster.

3. Check and validate if the cluster is healthy and operational

DBA

1. Restore the saved pfile configuration files and start ASM.2. Mount the expected ASM disk groups and start the DB.3. By starting the DB under the 11g binaries, this converted the

10gR2/64 DB to 11g. Run DB Upgrade script

upgrade tips strategies

Documents