Transcript Cleaning up your Oracle Environment

Migrating to 11g – Step Ordered
Approach
April Sims OCP 8i 9i 10g
Southern Utah University
April Sims, Editor
Are you a Dinosaur?
•
•
•
•
•
•
•
Do you still do weekly cold backups?
Do you stay in your office, cubicle, work area?
Rarely go to meetings with other human beings?
Do people wonder what do you do all day?
Did you bring any business cards to pass around?
Do you only “linger” on technical email discussion lists?
Do you have a migration to the next patchset, version,
hardware replacement in progress?
Step Ordered Approach
•
•
•
•
•
Narrows the amount/time of outage needed
Interim steps
Oracle is backwards compatible – generally
What is compatible?
Patching, upgrades and migrations – Tips and
Techniques.
Compatible Components –
Higher version than database
• Oracle Net Services
• Clients
• RMAN binary, Virtual/Catalog and Database
• Grid Control Repository Database
• Grid Control Intelligent Agents
• ASM
**EXPORT/IMPORT and DATAPUMP will have
issues
Oracle Application Server Cross
Compatibility
• Deploy Ear files from 10.1.3.x  10.1.2.x
• Utilize latest OC4J enhancements without starting
another HTTP Server.
• Using static ports – Note 184826.1
Deploy the ear file, default-web-site.html to find the port
number, add lines to 10.1.2 mod_oc4j.conf
Oc4jMount /instance ajp13://localhost:12507
Oc4jMount /instance/* ajp13://localhost:12507
Recommended Order of
Implementation
•
•
•
•
•
•
•
Listener
Rman
Rman Catalog
GC database
Clients
ASM
Database and Optimizer
Planning for Change – Keeping
things clean!
•
•
•
•
Maximum Availability Architecture – OTN
Multiple Oracle Homes
Multiple Operating System Accounts
Environmental Variables – SID.cnf for
variables not in .profile which is executed
using oraenv
• Scripting
Why Clean Things Up?
• It is enough work to install, maintain and upgrade
multiple instances with multiple ORACLE_HOMES
(OH)….
• Less need to modify scripts after an upgrade
• Less errors and/or problems when the environment
becomes “cloudy” with more than one OH.
• Less core dumps.
• Less off-hours work due to
installs/patches/upgrades/testing
Multiple $ORACLE_HOMES
Core dumps, fatal tns errors or hanging if exp, imp, sqlplus
from one OH to a database in a different OH
Different OH’s different versions or patches.
Recommended by Oracle Support & other DBA’s.
Multiple listeners running in different OH’s can also cause
problems as well as multiple OMS Intelligent Agents
It is normal to have several OH’s at any point in time. Now
what is the best way to do this safely?
It can be done by the same “oracle” unix account or by
using two different “oracle” unix accounts (multiple is
safer)…..who does this? Reality check.
Multiple OraInventory’s
Contents of /etc/oraInst.loc
inst_group=oinstall
#inventory_loc=/u01/10.2/oraInventory
inventory_loc=/u01/11.1/oraInventory
*This is for those who survived an oraInventory
corruption and lived to tell about it. Comment or
uncomment as needed for each install. Not
recommended by Oracle.
I don’t need no stinking
patches……
Patch Set
8.1.7.4
9.0.1.5
9.2.0.7
10.1.0.5
10.2.0.2
10.2.0.3
10.2.0.4
Number of bugs
1757
1000
2000
2500
1173
2007
4326
Upgrades and Patches
Have at least 2 $ORACLE_HOMES one for production,
one for testing patches – ALWAYS!!!!
Find the most stable, what is the terminal release? Put in
the latest CPU patch.
Bad version that is x.0.0.0 (unpatched) because it is rare
that you can truly simulate production load….Bugs
come out under pressure.
Cancel out of the install when it starts to configure Net
Manager and the Intelligent Agent (configure later).
Switch to new listener during off peak hours.
PSU – Patch Set Update
1st Digit - Major release number
2nd Digit - Maintenance release
3rd Digit - Application server release
4th Digit - Release component specific
5th Digit - Platform specific release
First PSU – 10.2.0.4.1
Second PSU – 10.2.0.4.2
MetaLink Doc ID 850471.1
**Includes latest CPU at release time
Oracle Recommended Patches
• Common issues in targeted configurations.
• Stabilize production environments.
• Save time and cost with known issues.
• Tested as a single combined unit, reducing risk.
• Easier to identify applicable patches.
https://metalink2.oracle.com/metalink/plsql/docs/10g_
Upgrade_Companion.htm
• Released between quarterly PSU’s
Oracle Target Configurations
•
•
•
•
•
Generic
Real Application Clusters and CRS
DataGuard (and/or Streams)
Exadata
Ebusiness Suite Certification
PSUs vs. CPUs Information
• Apply PSUs from now on (since DB Version
10.2.0.4)
• Both CPU & PSU Released Quarterly
• PSUs include CPUs
• PSUs are a Superset of CPUs
• Might need merge patch if migrating from a
CPU
• Don’t revert back to CPU once on PSU
http://blogs.oracle.com/gridautomation/
Issues with Conflicts and Rollbacks
Without Uploaded Configs
• Download patch then…
• Install….if conflict then
• Check if any key patches
rolled back
• Merge request…..then
• Wait….might be too late for
this CPU, narrow
window….maybe next
quarter!
With Uploaded Configs
• Patch Plan
• Auto checks prerequisites
• Checks Conflicts before
downloading
• Request Merge before
downloading
• Check Recommendations
• Deployment Plan Available
Net Services – Listener Pre-Spin
• Give each database a different listener port,
use naming convention
• I don’t use port 1521 for any listener
(because of autoregister feature)
• Put ORACLE_HOME of database if using
Listener in a different ORACLE_HOME
• Define local_listener in init.ora
• TCP, BEQ
Listener.ora
LISTENER_TEST = (ADDRESS = (PROTOCOL =
TCP)(HOST = FQ.NODENAME)(PORT = 1540))
SID_LIST_LISTENER_TEST =(SID_LIST =
(SID_DESC = (SID_NAME = TEST)
(ORACLE_HOME = /u01/app/oracle/product/10gR2) ) )
ADMIN_RESTRICTIONS_LISTENER_TEST=ON
LOG_FILE_LISTENER_TEST=listener_test.log
Rotate Listener Log
export dat="`date '+%y%m%d'`"
lsnrctl << EOF
set current_listener listener_test
set log_file listener_tmp.log
exit
EOF
mv listener_test.log listener_test.${dat}
cat listener_temp.log >> listener_test.${dat}
Clients
•
•
•
•
•
•
•
•
ODBC,SQLPLUS, Instant Client ****
Database Links ****
32/bit to 64/bit ****
JDBC, JDK **
Precompilers **
Features Availability **
Exp/Imp or Datapump *
BEQUEATH not supported b/t diff releases
****Highly recommended, usually minor issues
** Needs testing, maybe application specific
* Definitely has issues
RMAN
• Migrating the catalog database
• Multiple schemas- One for each release
and/or database
• Upgrading the rman catalog
• Differences in 11g
RMAN Duplicate – Upgrading
• RMAN is configured so that a higher release is
able to restore a lower release
SQL> alter database open resetlogs upgrade;
SQL> alter database open resetlogs downgrade;
Then run CATUPGRD.SQL or CATDWGRD.SQL
• Can’t use duplicate command
• Different OS levels
• Switch between word sizes (32 vs 64)
• Use Transportable Tablespace Conversion
between different OS’s
Grid Control
• Migrating Repository Database to 11g – if
uncoupled install – Use GC 10.2.0.5+
• Co-locate RMAN Catalog (s) -much easier to
move, migrate or drag along with GC.
ASM/CRS/RDBMS
• CRS must have its own $ORACLE_HOME
• ASM can be separate as of 10gR2
• Push to move ASM, CRS and RDBMS to
three different operating accounts
• One-off patches - separate ASM and RDBMS
• Rolling Upgrades
11g Diagnosability Framework
•
•
•
•
Environmental Variable – ORACLE_BASE
adrci command-line utility
Scripting changes
Rotating, purging logs, trace files, core
dumps and incidents
• Can disable ADR, especially for
troubleshooting
Surprises
• SYSTEM password expires with default
profile – 11gR1
• Case sensitivity issue between primary and
physical standby – new security feature,
11gR1
• MOS NoteID:579523.1 dangling (orphaned)
rows in dictionary table SYS.KOTTD$ affects all upgrades
Characterset Selection – UTF8
Any implications for migration? options are
usually some sort of csalter and/or exp/imp,
datapump.
Transitional Steps – convert to a superset to
remove some types of lossy data. See blog
also NLS by Gary Gordhammer articles in
IOUG “SELECT”
Grid Control and RMAN catalog can be UTF8
Database Upgrade Methods
• EXP/IMP or DataPump • Transportable Tablespaces - same or
different node
• DBUA
• Manual Upgrade
• Transient Logical Standby
• Physical Standby
• Snapshot Standby
Upgrade Testing Environment
•
•
•
•
•
•
•
Data Guard copy of production
Break DG Apply process to standby
Enable flashback, guaranteed restore point
Upgrade to 11g for testing….TEST….TEST
Downgrade to 10g
???? Exact steps will post to blog.
Restore to guaranteed restore point…repeat
• Data Guard site is also fallback position during golive, break connection leave at 10g.
Optimizer Upgrade
• SQL Plan
Management=Stored
Outlines in 10g
• Bulk Load
• SQL Tuning Set (STS)
– Top 50, 100, etc.
• Stored Outlines
• Cursor Cache
• Use a staging table
http://optimizermagic.blogspot.com
PreUpgrade Optimizer
• STS – Top 50…etc.
• Source database – instance-wide stats at
peak load, AWR - 7 days
• Statspack – Level 7 – generates plans
• OS stats - CPU, memory and IO (such as sar,
vmstat, iostat)
Order of SQL Plan Migration
In the target 11g database……
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_FEATURES_ENABLE=11g
Turn off Statistics Gathering until stable – 1 week
DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);
TEST…TEST…TEST, Correct Regression
Turn off Pending Statistics
Gather 11g Statistics – SPM in effect
Backpedal to 10g Stats
In the target 11g database……
OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE
OPTIMIZER_FEATURES_ENABLE=10g
Turn off Statistics Gathering
Re-import 10g Stats from Bulk Load
DBMS_STATS.SET_GLOBAL_PREFS(‘PENDING’,’TRUE’);
TEST…TEST…TEST, Correct Regression
Turn off Pending Statistics
Gather 11g Statistics – SPM in effect
Migration – Step Ordered
Approach
•
•
•
•
•
Narrows the amount/time of outage needed
Interim steps
Oracle is backwards compatible – generally
What is compatible?
Patching, upgrades and migrations
Questions?
Leave Business Card – put question or interest on back.
http://www.twitter.com/aprilcsims
http://www.twibes.com/novicedba
http://www.twibes.com/lone_dba
Twitter hash tags # IOUG_SELECT # NOVICEDBA
#LONE_DBA
@aprilcsims
BLOG http://aprilcsims.wordpress.com
38