Transcript 3_10_Morgan

Migrating an 11i Database to
Linux – Tips, Tricks & Gotchas
Mark Morgan
DBA Consultant
siMMian systems, inc.
415-585-4242
[email protected]
January 21, 2009
Migrating an 11i Database to
Linux
•
•
•
•
Overview
Project Definition
Process Overview and Detail
Post Migration Tips and Tricks
Mark Morgan siMMian systems, inc.
January 21, 2009
Project Definition: Why Linux?
• Advantages
•
•
•
•
•
Lower Cost of Hardware
Performance
Lower Cost of Hardware
RAC
Lower Cost of Hardware
• Disadvantages
• Stability
• Hidden Administration Costs
• RAC
Mark Morgan siMMian systems, inc.
January 21, 2009
Project Definition: Goals
• Replace HPUX hardware
• Prepare for R12 upgrade
• Improve upgrade performance
• Long-term EBS compatibility
• Database cleanup
• Coalesce data
• Purge metadata junk
• Characterset conversion to AL32UTF8
• postponed to R12 upgrade
• 10.2.0.4 minimum
• 11i does not support AL32UTF8
• Minimum downtime
Mark Morgan siMMian systems, inc.
January 21, 2009
Project Definition: Infrastructure
• 11.5.10.2 EBS
•
•
•
•
Installed as 11.0 / RDBMS 8.0
Financials, OM, Manufacturing, HR, CRM
OATM
Recent ATG & TXK
• RDBMS 10.2.0.3 64-bit
• HPUX 64 bit
• Linux Redhat 4 64 bit
Mark Morgan siMMian systems, inc.
January 21, 2009
Project Definition: Datapump
• 362205.1 Export/Import Process for Oracle Applications
Release 11i Database Instances Using Oracle
Database 10g Release 2
• 454616.1 Export/Import Process for Oracle E-Business
Suite Release 12 using 10gR2
• 402785.1 iSetup dependency with Deinstall and Reinstall
of XMLDB
• Transportable tablespaces not supported for EBS
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview
•
•
•
•
•
•
•
Build Linux environment
Stage target database
Prepare source database
Export the source database
Import to the target database
Update the target database
Post migration tasks
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Build Linux
Environment
• 416305.1 Oracle Applications Installation and Upgrade
Notes Release 12 (12.0) for Linux (64-bit)
• 169706.1 Oracle® Database on AIX®,HPUX®,
Linux®,Mac OS® X,Solaris®,Tru64 Unix®
Operating Systems Installation and
Configuration Requirements
• 339510.1 Requirements for Installing Oracle 10gR2
RDBMS on RHEL 4 on AMD64/EM64T
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Create
Target Database
• Create 10g ORACLE_HOME
• R12.0.4 installation media
• 10g installation media
• RDBMS patches
• 10.2.0.3 / 10.2.0.4
• DST
• CPUs
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Create
Target Database
Datapump-specific patches
• RDBMS
• 5874989 (characterset)
• 4352110
• 6855589 (corrects ORA-39125 if v8 database)
• EBS Patches (unzip only)
•
•
•
•
4872830 (should already be applied)
7225862 (replaces 5873146)
6723741 (R12)
6342289 (R12)
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Create
Target Database
• Create init.ora
• Create the target database
•
•
•
•
auclondb.sql from R12 patch 6924477
edit and run aucrdb.sql
autoextend datafiles
disable archive logging
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Create
Target Database
• Create database objects (patch 7225862)
•
•
•
•
SYS: addb1020.sql
SYSTEM: adsy1020.sql
JVM: adjv1020.sql
Other: admsc1020.sql
• Post-creation Tasks
•
•
•
•
•
Complete patch 6855589 readme
Verify SYSTEM grants
Verify XDB grants
Compile objects
Backup the new database
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Prepare
the Source Database
• System downtime
• Apply database preparation patch 7225862 to apps
tier
• Apply 6855589 to source RDBMS (including
README steps)
• update SYSTEM_PRIVILEGE_MAP (2 SQL
statements)
• Run preclones on both the source database and
apps tiers
• Record Advanced Queue settings
• $AU_TOP/patch/115/sql/auque1.sql
• (generates auque2.sql)
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Prepare
the Source Database
• Create parameter file for tables with long
columns
•
aulong.sql (6723741)
•
(aulongexp.dat is generated)
• Remove rebuild index parameter in spatial
indexes
• select * from dba_indexes where
index_type='DOMAIN' and upper(parameters) like
'%REBUILD%';
• alter index <index name> rebuild parameters
<parameters>
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Export
the Source Database
• Create export directory filesystem
• Copy parameter file from
$AU_TOP/patch/115/import/auexpdp.dat
• Create directory in database
• create directory dmpdir as
'<DUMPDIR>';
• Prepare XMLSchema objects for
migration (doc 402785.1)
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Export
the Source Database
• Export the instance with expdp, using the
above parameter file
• Export tables with long columns using
aulongexp.dat
• Required SYS connection
• Used 10.1 ORACLE_HOME (bug 4154125)
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Import the
Target Database
• Create directory in target database
• create directory dmpdir as '<DUMPDIR>';
• Import the users
• impdp parfile=auimpusr.dat
• verify tablespace quotas
• verify user grants and privileges to sys/system objects
• Import tables with long columns
• Modify aufullimp.dat as aulongimp.dat
• file=longexp
• full=y
• imp parfile=aulongimp.dat
• Ignore errors for triggers
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Import the
Target Database
• Import the Applications database instance
• impdb parfile=auimpdp.dat (6723741)
• expect ORA-31684, ORA-39111, compilation
warnings, trigger errors and BISM constraint
errors
• Import triggers into the target database using
standard import
•
•
•
•
create autrigimp.dat from aufullimp.dat (6723741)
connect as SYS
ignore=y
rows=n
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Update the
Target Database
• Reset Advanced Queues
• run auque2.sql generated above
• Start the database listener
• Run adgrants.sql
• Grant create procedure privilege on CTXSYS
• $AD_TOP/patch/115/sql/adctxprv.sql
• Implement and run AutoConfig on database
tier (165195.1)
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Update
the Target Database
• Postclone applications tier(s) to point to new
database
• Gather statistics for SYS schema using adstats.sql
• Re-create custom database links
• Create ConText and Spatial objects
• create custom dpost_imp.pl driver from 11i 4872830 and
R12 6723741
• apply with adpatch
• Rebuild XMLSchema Objects per doc 402785.1
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Post
Migration Tips
• Re-create missing CTXSYS objects
• (427418.1, 372263.1, 141131.1, 459857.1)
• adadmin
• Compile flexfield data in AOL tables
• Recreate grants and synonyms for APPS
schema
• Recreate HRV_PER_PEOPLE_V
• Clear nodes and run autoconfig per doc
260887.1
Mark Morgan siMMian systems, inc.
January 21, 2009
Process Overview: Post
Migration Tips
• Verify BISM Constraints
• ALTER TABLE ADD CONSTRAINT
•
•
•
•
•
•
Compile invalid objects
Rebuild nptification queues using wfntfqup.sql
Gather statistics (optional)
Start applications tier
Syncronize Workflow
Create DQM indexes for Trading Community
Mark Morgan siMMian systems, inc.
January 21, 2009
Q&A
Mark Morgan
DBA Consultant
siMMian systems, inc.
415-585-4242
[email protected]
Mark Morgan siMMian systems, inc.
January 21, 2009