Transcript Using the Dell PowerPoint Template

Database
Upgrade/Migration Options
& Tips
Sreekanth Chintala
Database Technology Strategist
Agenda
• Very high level overview of Migration/Upgrade options
• No technical details
2
Confidential
Global Marketing
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]
3
Confidential
Global Marketing
Database Upgrade
Moving DB from one version to another
on the same server
It is NOT:
Upgrading
the Server
4
Confidential
Upgrading
the OS
Upgrading
the
Storage
Global Marketing
Database Upgrade
10gR1
5
Confidential
11gR2
Global Marketing
Database Migrations
Move to another Server
Move to another Data Center
Move to another (later) Version
• Migrations are typically done as part of End-ofLife refresh, usually followed by Database
Upgrade
6
Confidential
Global Marketing
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
7
Confidential
Global Marketing
Factors
Source &
Target DB
Version
8
Confidential
Acceptable
Downtime
Size of the
database
Source &
target
Operating
Systems
Global Marketing
Concerns
Downtime
Training
Performance
Fallback /
Backout
Data Field
Conversions
Upgrade
Issues
9
Confidential
Global Marketing
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
10
Confidential
Global Marketing
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)
Gotcha’s
• 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.
11
Confidential
Global Marketing
Export / Import
10gR1
Export
Copy
Export
Copy
11gR2
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
12
Confidential
Global Marketing
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
• Don’t use it on Live tables
Advantages
• No intermediate storage needed
• Operations can be performed in parallel for non-related tables
13
Confidential
Global Marketing
CTAS
10gR1
• DB Link
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
14
Confidential
Global Marketing
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
15
Confidential
Global Marketing
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
16
Confidential
Global Marketing
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
• Doesn’t 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
17
Confidential
Global Marketing
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
18
Confidential
Global Marketing
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
19
Confidential
Global Marketing
ASM Rebalance Option
Storage Arrays
Storage Arrays
1.
Add the New LUNs on the OLD
system (OS)
Add the LUNs to the Disk Group
Perform Rebalance
Shutdown Old System
2.
3.
4.
20
Confidential
1.
2.
3.
4.
Create CRS,DB,ASM on new system
Copy SPFILEs (DB,ASM)
Add the New LUNs on the New system
(OS)
Bring up the database
Global Marketing
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
21
Confidential
Global Marketing
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
•
•
•
•
22
Moderately complex to implement
Provides Fall back options
You can leverage the infrastructure many times
Best near Zero Downtime Option
Confidential
Global Marketing
8i & 9i  10g/11g Migration Overview
Production
Create Clone DB via RMAN
8i/9i
Goldengate Capture to Trails
Server & Storage
Provisioning
Real-Time
Replication
Reverse
Fail back!
Replication
Fail back Strategy
DBA
Begins
Post-Migration
Migration
ASM Storage
Switchover to
RAC Database
App Testing
Global Marketing
In place Migrations of OS
Complete Downtime to the server being upgraded
•
•
•
•
24
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.
Confidential
Global Marketing
In place Migration Steps
DBA
1. Create and save (elsewhere) pfile from the spfile
Linux Engineer
1. Re-image all cluster nodes to new OS
2. 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
25
Confidential
Global Marketing