Transcript RTS Inc.

Doug Cackett
RTS Incorporated
Remote DBA Services
RTS Inc.
Topics of Discussion
• Installing Oracle10g
• Upgrading to Oracle10g
• New Features of Oracle10g
RTS Inc.
Installing Oracle 10g
RTS Inc.
Startup the OUI
RTS Inc.
File Locations
RTS Inc.
Loading product list …….
RTS Inc.
Installation Type
RTS Inc.
Select Database
RTS Inc.
Database Options
RTS Inc.
Enterprise Manager Option
RTS Inc.
Datafile Storage
RTS Inc.
Backup and Recovery Option
RTS Inc.
Passwords
RTS Inc.
Installation Summary
RTS Inc.
Installing …..
RTS Inc.
That’s it…..
RTS Inc.
End … Links and info
RTS Inc.
Upgrading to Oracle
Database 10g
RTS Inc.
Database 10g Upgrade Enhancements
• Analysis of database before upgrade to
increase likelihood of successful upgrade
• Pre upgrade information tool
• Upgrade Performance Enhancement
• Parallel recompilation of invalid pl/sql database
objects on multiprocessor CPUs
• Verification of database upgrade
• Post upgrade status tool
RTS Inc.
Reduced Complexity
• Automaticly determines what components
(e.g. Java, Spatial…) need to be upgraded
and then performs all necessary upgrades in
correct dependency order
• All components in DBMS_REGISTRY
• Prior to Oracle Database 10g, much of the
component script invocation was manual
RTS Inc.
Reduced Complexity
• One measure of simplicity: Database Upgrade
Guide documentation size is decreasing
dramatically:
• 8i
512 pages
• 9i
484 pages – 111 steps total for RDBMS
with all 9 components!
• 9iR2 344 pages
• 10g 230 pages - 6 steps!
RTS Inc.
Prepare to Upgrade
• Become familiar with the features of the new
Oracle Database 10g release
• Determine the Upgrade Path to the new
release
• Choose an upgrade Method
• Choose an Oracle Home Directory
• Prepare a Backup Strategy
• Develop a test plan
RTS Inc.
10g Upgrade Paths
• Direct upgrade from 9iR2 (9.2.0), 9i (9.0.1), 8i
(8.1.7), 8.0.6
• If you are not at one of these versions you
need to perform a “double-hop” Upgrade
• E.g. 8.1.5 -> 8.1.7 -> 10.1.0
• 7.3.4 -> 9.2.0 -> 10.1.0
RTS Inc.
Choose an Upgrade Method
• Database Upgrade Assistant (DBUA)
• The Database Upgrade Assistant is a graphical
user interface (GUI) tool that interactively steps
the user through the upgrade process and
configures the database to run with the new
Oracle Database 10g release.
• Manual Upgrade
• A manual upgrade consists of running SQL
scripts and utilities from the command line
RTS Inc.
Advantages of DBUA
• Performs all necessary steps for the upgrade
• Interacts with the DBA if decisions need to be
made
• Provides complete HTML report of the
upgrade process
• Significantly reduces database downtime
RTS Inc.
Manual Upgrade
Advantages & Disadvantages
• Advantage:
• Gives the DBA finer control over the upgrade
process
• Disadvantage:
• More work
• More error prone
RTS Inc.
Pre Upgrade Analysis (Auto)
script: utlu101i.sql
 Checks for certain known database, init.ora, etc
settings that may cause upgrade to fail, and
generates warnings
i.e. shared_pool_size init.ora parameter is too low
• Utility runs in “old server” & “old database” context
• Based on database knowledge & needs of Oracle
Database 10g
• DBUA automatically performs any required actions
RTS Inc.
Pre Upgrade Analysis
•
•
•
•
•
•
•
Database version and compatibility
Redo logs <4MB
Updated Parameters e.g. shared_pool_size
Deprecated (renamed) parameters
Obsolete parameters
Cluster check
Account Check
RTS Inc.
Pre Upgrade Analysis
• Names of Components in database e.g. JVM,
Spatial, etc
• Tablespaces
• Increase in size recommendations
• New SYSAUX tablespace
• Character set incompatibilities
• Installed options
• Upgrade time estimate
RTS Inc.
Key DBUA Features
• The Assistant automates the upgrade process by
performing all the tasks you normally accomplish
manually
• Invokes the Pre Upgrade Information Utility and
automatically makes appropriate adjustments
(including tablespace, redo logs, etc)
• Checks for adequate resources (disk space, rollback
segments)
• Creates mandatory tablespace SYSAUX
• Optionally, backs up all necessary files.
• Creates new configuration files (init.ora, spfile,
listener) in the new Oracle home
RTS Inc.
Key DBUA Features
• During the upgrade process, the DBUA:
• Runs all necessary scripts (pre-upgrade
information utility, “u” script, post upgrade status
utility)
• Shows upgrade progress
• Write detailed trace and logging files
RTS Inc.
Key DBUA Features
• Settings during upgrade
• DBUA disables archiving during upgrade phase
• Security features
• DBUA locks new users in the upgraded database
• RAC-DBUA
• RAC-DBUA facilitates upgrade of 8.1.7, 9.0.1, 9.2 to
10.1.0
• Upgrade done on the cluster. All database and
configuration files are upgraded on all the nodes in
the cluster.
RTS Inc.
Key DBUA Features
• Silent mode for single command upgrade
i.e. dbua –silent –sid ora9idb [-backup <location>]
RTS Inc.
Welcome Screen
RTS Inc.
Select the database to upgrade
RTS Inc.
Tablespace
RTS Inc.
Log Files
RTS Inc.
Create SYSAUX Tablespace
RTS Inc.
Parallel Recompilation
RTS Inc.
Database Backup
RTS Inc.
Pre-Upgrade Summary Page
RTS Inc.
Upgrade Progress
RTS Inc.
Upgrade Results
RTS Inc.
Manual Upgrade
• Install Oracle Database 10g
• Analyze the existing database using preupgrade information tool
• @?/rdbms/admin/utlu101i.sql
• Make necessary adjustments based on feedback




Shutdown database
Take a backup
Switch to New Oracle Home
Startup Database
 SQL> startup upgrade
RTS Inc.
Manual Upgrade
• Create SYSAUX tablespace
• Invoke the proper upgrade “u” script
• SQL> @rdbms/admin/u0801070.sql
 Shutdown database
 Startup database
 Run utlrp
 SQL>@rdbms/admin/utlrp
 Check upgrade status using post upgrade status
tool
 SQL>@?/rdbms/admin/utlu101s.sql
RTS Inc.
Upgrade Validation
 “STARTUP UPGRADE” mode will suppress certain
spurious and unnecessary errors
• Catch and handle ORA-00942 on DROP TABLE
• Automatically handles setting parameters that can
cause problems during upgrade
• DBMS_REGISTRY package and dictionary view
used to record and manage information about all
the component upgrades, for example:
SQL> select ... from dba_registry;
COMP_ID COMP_NAME
VERSION
STATUS
------- ---------------------- -----------CATALOG Oracle10g Catalog Views 10.1.0.1.0 VALID
RTS Inc.
Upgrade Validation
• New “Post Upgrade Status Utility” script utlu101s.sql
• Queries component REGISTRY to determine
upgrade status
• Provides information about invalid/incorrect
component upgrades
• Provides script name to re-run if necessary
• DBUA calls this script to display status and then
takes corrective action
RTS Inc.
10g New Features - Sampling
RTS Inc.
Online Segment Shrink
• Can be recommended by Segment Advisor
TABLE
HEADER
10111101
11001101
10101111
11101101
00001101
11001101
00001101
10111101
01010101
11011010
High Water Mark
Free Space
• alter table enable row movement;
• alter table t1 shrink space [compact] cascade;
TABLE
HEADER
11001101
10101111
11101101
00001101
11001101
10101111
11101101
00001101
11001101
00001101
High Water Mark
Free Space
RTS Inc.
Rename Tablespace
• Useful in Transportable Tablespace scenarios
• ALTER TABLESPACE prod RENAME to arc1;
• Can’t rename SYSTEM or SYSAUX
• Tablespace and all datafiles must be online
• Can also rename READ ONLY tablespaces
PROD
ARC1
RTS Inc.
Bigfile Tablespaces
• Support for sizes up to 8 Exabytes!
• 8 000 000 Terabytes
• Max 65535 files in database
• SYSTEM & SYSAUX can’t be bigfile tablespaces
8 000 000 000 000 000 000 bytes
• CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u02/oracle/data/bigtbs01.dbf‘
SIZE 50G EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
RTS Inc.
Temporary Tablespace Groups
• Can create several temporary tablespaces and
assign them to a tablespace group
• Can assign the tablespace group as users
temporary tablespace
• Parallel Query and Parallel execution slaves can
spread temporary segments on different
tablespaces
• alter tablespace temp1 tablespace group t;
• create temporary tablespace temp2 tempfile
'temp2_01.dbf' size 100m tablespace group t;
• alter tablespace temp1 tablespace group '';
RTS Inc.
Data Pump
• A server-managed data transportation tool
• Direct load/extract capabilities
• Very high performance/efficient with large data
Data
sets
Pump
Client
Data
• Replacement for exp/imp
Pump
• old exp/imp remain supported
Data
Warehouse
Data Pump
Client
Data Pump
PROD
RTS Inc.
Data Pump
•
•
•
•
Commands expdp/impdp
Dynamic configuration, resumable operations
Client can detach and reconnect
Can be parallelized using PARALLEL
• Even loads to/from external text files
• Parallelization level can be changed on the fly for
long running jobs
• Monitored through DBA_DATAPUMP_JOBS
RTS Inc.
Intelligent Management
• Built-in Intelligent
Management
Application & SQL
Management
Storage
Management
Backup & Recovery
Database
Management
Management
System Resource
Management
Space
Management
• Tune (Automatic
Workload Repository)
• Fix (Automatic
Maintenance Tasks)
• Alert (Server-Generated
Alerts)
• Advise (Advisory
Infrastructure)
Intelligent Infrastructure
RTS Inc.
Automatic Workload Repository
(AWR)
•
Advisory
Infrastructure
Server-generated Alert
Infrastructure
Automatic Maintenance Task
Infrastructure
Automatic Workload Repository
•
•
•
•
Automatically collect and
compute important statistics,
SQL workload, feature usage
Direct memory access
Periodically flush snapshots
to disk
Base-lining capability
Primarily build for
automating internal
components
STATISTICS_LEVEL=TYPICAL
RTS Inc.
Automatic Workload Repository
ADDM finds
top problems
MMON
SYSAUX
WR Schema
7:00am
In memory
statistics
Snapshot 1
7:30am
Snapshot 2
8:00am
Snapshot 3
8:30am
Snapshot 4
SGA
8:30am
RTS Inc.
Oracle10G System Management –
Automatic SGA Management
Online
Users
Buffer Cache
Large Batch
Jobs
Buffer Cache
Large Pool
Large Pool
SQL Cache
SQL Cache
Java Pool
sort
SGA Pool
PGA Pool
•Only 2 memory
parameters
•Advisor helps
set parameters
•Automatically resizes with
changes in workload
•Maximize usage of
available memory
Java Pool
sort
RTS Inc.
Flashback Error Correction
Database
Customer
• Recovery at all levels
• Database Level
• Flashback Database restores the
whole database to time
• Uses Flashback Logs
• Table Level
Order
• Flashback Table restores rows in
a set of tables to time
• Uses UNDO in database
• Flashback Drop restores a
dropped table or a index
• Recycle bin for DROPs
• Row Level
• Restore individual rows
• Uses Flashback Query
RTS Inc.
FlashBack Database
• Flash Recovery Area must be configured
• Flashback logs are stored there
• Consisting of old database block images
• Fast “rollback” of database, no redologs required
Server
Process
Server
Process
DB
Server
Process
Flashback
Log
RTS Inc.
FlashBack Database
Configuration parameters:
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE
• DB_FLASHBACK_RETENTION_TARGET
Commands:
• ALTER DATABASE FLASHBACK ON;
• ALTER DATABASE FLASHBACK OFF;
• ALTER TABLESPACE test1 FLASHBACK OFF;
• ALTER TABLESPACE test1 FLASHBACK ON;
RTS Inc.
FlashBack Options
Flashback Query:
• exec dbms_flashback.enable_at_time(…);
• select * from table;
• exec dbms_flashback.disable;
• select * from table AS OF timestamp ‘…’;
Flashback Table:
• FLASHBACK TABLE t1 TO SCN 12345;
• FLASHBACK TABLE t1 TO TIMESTAMP '2003-03-03
12:05:00';
RTS Inc.
Flashback Row History
SELECT versions_xid XID, versions_startscn
START_SCN,
versions_endscn END_SCN, versions_operation
OPERATION,
empname, salary FROM hr.employees_demo
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
--- --------- ------- --------- ------- -----0004000700000058 113855
I Tom 927
000200030000002D 113564
D Mike 555
000200030000002E 112670 113564 I Mike 555
3 rows selected
• Useful for auditing
RTS Inc.
Flashback Transaction History
select xid, start_scn, commit_scn, operation,
undo_sql, table_name
from dba_transaction_query where xid =
’000200030000002D’;
XID START_SCN COMMIT_SCN OPERATION UNDO_SQL
--- --------- ---------- --------- -----------------------000200030000002D 112670 113565 D insert into
"SCOTT"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655')
000200030000002D 112670 113565 I delete from
"SCOTT"."DEPT"
where "DEPTNO" = '20' and "DEPTNAME" = 'Finance'
000200030000002D 112670 113565 D update “SCOTT”.”EMP”
set “SALARY” = ‘555’ where “EMPNO” = ‘111’ and ”EMPNAME” =
‘Mike’ and “SALARY”
RTS Inc.
Table Recovery using
Flashback
• DROP TABLE X;
• Table is renamed internally, not dropped
• Indexes & other structures remain
• Table is purged when out of free space or quota
• SELECT * FROM RECYCLEBIN;
• Systemwide recyclebin DBA_RECYCLEBIN
• Or show recyclebin command in sqlplus
• FLASHBACK TABLE RB$$3560$TABLE$1
TO BEFORE DROP RENAME TO scott.emp;
• PURGE RECYCLEBIN;
• DROP TABLE X PURGE;
RTS Inc.
ASM Architecture
RAC or
Non–RAC
Databases
Clustered
Servers
ASM Instance
ASM Instance
ASM Instance
Oracle
DB Instance
Oracle
DB Instance
Oracle
DB Instance
ASM Instance
Oracle
DB Instance
Oracle
DB Instance
Clustered
Pool of Storage
RTS Inc.
QUESTIONS
ANSWERS
RTS Inc.