Database Upgrade/Migration Options

1
Database Upgrade/Migration Options Tips

• Sreekanth Chintala
• Database Technology Strategist

2
Agenda

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

3
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
• Sreekanthchintala_at_gmail.com
• Sreekanth_chintala_at_dell.com

3
4
Database Upgrade
5
Database Upgrade
10gR1
11gR2
6
Database Migrations

• Migrations are typically done as part of
  End-of-Life refresh, usually followed by Database
  Upgrade

7
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
Factors
Factors that influence Migration Path
9
Concerns
10
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
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 lt 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
Export / Import
10gR1
Export Copy
11gR2
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 static
  tables, 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
• Beware of security and password changes

13
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 the amount 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
CTAS

• DB Link

10gR1
11gR2

• Tips
• Review the nature of data. Bring over the static
  tables, 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

15
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
  allocated
• Advantages
• 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 the restore is complete

16
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 and national 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
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
SRDF Copy Option

• 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 source Lun
• 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

19
ASM Rebalance Option

• Leverage ASM rebalance feature to off-load data
  from one storage array 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
ASM Rebalance Option
Storage Arrays
Storage Arrays

1. Add the New LUNs on the OLD system (OS)
2. Add the LUNs to the Disk Group
3. Perform Rebalance
4. Shutdown Old System

1. Create CRS,DB,ASM on new system
2. Copy SPFILEs (DB,ASM)
3. Add the New LUNs on the New system (OS)
4. Bring up the database

21
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
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
8i 9i ? 10g/11g Migration Overview
Production
Create Clone DB via RMAN
Goldengate Capture to Trails
Server Storage Provisioning
Fail back!
Reverse Replication
Real-Time Replication
Goldengate Apply from Trails
XTTS Transport Tablespace
Fail back Strategy Post-Migration
Switchover to RAC Database
DBA Begins Migration
App Testing
24
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
In place Migration Steps

• DBA
• Create and save (elsewhere) pfile from the spfile
• Linux Engineer
• Re-image all cluster nodes to new OS
• Post-build steps an 11g RAC cluster.
• Check and validate if the cluster is healthy and
  operational
• DBA
• Restore the saved pfile configuration files and
  start ASM.
• Mount the expected ASM disk groups and start the
  DB.
• By starting the DB under the 11g binaries, this
  converted the 10gR2/64 DB to 11g. Run DB Upgrade
  script