Doug Cackett
This presentation is the property of its rightful owner.
Sponsored Links
1 / 67

Doug Cackett RTS Incorporated Remote DBA Services PowerPoint PPT Presentation


  • 152 Views
  • Uploaded on
  • Presentation posted in: General

Doug Cackett RTS Incorporated Remote DBA Services. Topics of Discussion. Installing Oracle10g Upgrading to Oracle10g New Features of Oracle10g. Installing Oracle 10g. Startup the OUI. File Locations. Loading product list ……. Installation Type. Select Database. Database Options.

Download Presentation

Doug Cackett RTS Incorporated Remote DBA Services

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Doug cackett rts incorporated remote dba services

Doug Cackett

RTS Incorporated

Remote DBA Services


Topics of discussion

Topics of Discussion

  • Installing Oracle10g

  • Upgrading to Oracle10g

  • New Features of Oracle10g


Doug cackett rts incorporated remote dba services

Installing Oracle 10g


Startup the oui

Startup the OUI


File locations

File Locations


Loading product list

Loading product list …….


Installation type

Installation Type


Select database

Select Database


Database options

Database Options


Enterprise manager option

Enterprise Manager Option


Datafile storage

Datafile Storage


Backup and recovery option

Backup and Recovery Option


Passwords

Passwords


Installation summary

Installation Summary


Installing

Installing …..


That s it

That’s it…..


End links and info

End … Links and info


Doug cackett rts incorporated remote dba services

Upgrading to Oracle Database 10g


Doug cackett rts incorporated remote dba services

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


Reduced complexity

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


Reduced complexity1

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!


Prepare to upgrade

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


10g upgrade paths

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


Choose an upgrade method

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


Advantages of dbua

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


Manual upgrade advantages disadvantages

Manual Upgrade Advantages & Disadvantages

  • Advantage:

    • Gives the DBA finer control over the upgrade process

  • Disadvantage:

    • More work

    • More error prone


Pre upgrade analysis auto script utlu101i sql

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


Pre upgrade analysis

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


Pre upgrade analysis1

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


Key dbua features

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


Key dbua features1

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


Key dbua features2

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.


Key dbua features3

Key DBUA Features

  • Silent mode for single command upgrade

    i.e. dbua –silent –sid ora9idb [-backup <location>]


Welcome screen

Welcome Screen


Select the database to upgrade

Select the database to upgrade


Tablespace

Tablespace


Log files

Log Files


Create sysaux tablespace

Create SYSAUX Tablespace


Parallel recompilation

Parallel Recompilation


Database backup

Database Backup


Pre upgrade summary page

Pre-Upgrade Summary Page


Upgrade progress

Upgrade Progress


Upgrade results

Upgrade Results


Doug cackett rts incorporated remote dba services

Manual Upgrade

  • Install Oracle Database 10g

  • Analyze the existing database using pre-upgrade 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


Doug cackett rts incorporated remote dba services

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


Upgrade validation

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


Upgrade validation1

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


10g new features sampling

10g New Features - Sampling


Online segment shrink

Online Segment Shrink

  • Can be recommended by Segment Advisor

  • alter table enable row movement;

  • alter table t1 shrink space [compact] cascade;

TABLE HEADER

10111101

11001101

10101111

11101101

00001101

11001101

00001101

10111101

0101010111011010

High Water Mark

Free Space

TABLE HEADER

11001101

10101111

11101101

00001101

11001101

10101111

11101101

00001101

11001101

00001101

High Water Mark

Free Space


Rename tablespace

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


Bigfile tablespaces

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

  • CREATE BIGFILE TABLESPACE bigtbsDATAFILE '/u02/oracle/data/bigtbs01.dbf‘SIZE 50G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

8 000 000 000 000 000 000 bytes


Temporary tablespace groups

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 '';


Data pump

Data Pump

  • A server-managed data transportation tool

  • Direct load/extract capabilities

  • Very high performance/efficient with large data sets

  • Replacement for exp/imp

    • old exp/imp remain supported

DataPumpClient

DataPumpClient

DataWarehouse

PROD

Data Pump

Data Pump


Data pump1

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


Intelligent management

Intelligent Management

  • Built-in Intelligent Management

    • Tune (Automatic Workload Repository)

    • Fix (Automatic Maintenance Tasks)

    • Alert (Server-Generated Alerts)

    • Advise (Advisory Infrastructure)

Application & SQL

Management

Storage

Management

System Resource

Management

Space

Management

Backup & Recovery

Management

Database Management

Intelligent Infrastructure


Automatic workload repository awr

Advisory Infrastructure

Server-generated Alert Infrastructure

Automatic Maintenance Task Infrastructure

Automatic Workload Repository

Automatic Workload Repository(AWR)

  • 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


Automatic workload repository

Automatic Workload Repository

ADDM finds top problems

MMON

SYSAUX

WR Schema

7:00am

Snapshot 1

7:30am

In memorystatistics

Snapshot 2

8:00am

Snapshot 3

8:30am

Snapshot 4

SGA

8:30am


Oracle10 g system management automatic sga management

Buffer Cache

sort

sort

Oracle10G System Management – Automatic SGA Management

  • Only 2 memory parameters

  • Advisor helpsset parameters

  • Automatically resizes with changes in workload

  • Maximize usage of available memory

Buffer Cache

Large Batch Jobs

Online

Users

Large Pool

Large Pool

SQL Cache

SQL Cache

SGA Pool

Java Pool

Java Pool

PGA Pool


Flashback error correction

Flashback Error Correction

Database

  • Recovery at all levels

  • Database Level

    • Flashback Database restores the whole database to time

      • Uses Flashback Logs

  • Table Level

    • 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

Customer

Order


Flashback database

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

ServerProcess

ServerProcess

ServerProcess

DB

Flashback Log


Flashback database1

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;


Flashback options

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';


Flashback row history

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 ANDMAXVALUE

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


Flashback transaction history

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”

= ‘655’

3 rows selected


Table recovery using flashback

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$1TO BEFORE DROP RENAME TO scott.emp;

  • PURGE RECYCLEBIN;

  • DROP TABLE X PURGE;


Asm architecture

ASM Architecture

ASM Instance

ASM Instance

ASM Instance

ASM Instance

RAC or Non–RAC

Databases

Oracle

DB Instance

Oracle

DB Instance

Oracle

DB Instance

Oracle

DB Instance

Oracle

DB Instance

Clustered

Servers

Clustered

Pool of Storage


Doug cackett rts incorporated remote dba services

Q

&

Q U E S T I O N S

A N S W E R S

A


  • Login