OraclePoint - Oracle Database Administration

9.2.0.8 Patchset Guide for Oracle database

Mon, 16 Apr 2007 05:00:52 +0000

Please refer to this oracle official guide for applying oracle datbase 9.2.0.8 on solaris.

The Top 20 Features for Oracle 10G DBA

Mon, 16 Apr 2007 23:18:26 +0000

Top Features offered Oracle 10g onwards

See complete article at http://www.oracle.com/technology/pub/articles/10gdba/index.html

Oracle 9i Onwards-SGA_MAX_SIZE and Granules

Tue, 17 Apr 2007 19:07:24 +0000

Oracle 9i introduced many new features to make the Database Administrator’s life a little easier. One of these new features includes the capability to dynamically change initialization parameters that make up the System Global Area (SGA) without having to restart the database. Previously, changes to parameters that affected the SGA necessitated a restart of the database contributing to downtime. While much information has been written detailing how to change SGA parameters, there is not much information about the SGA_MAX_SIZE parameter, how it affects dynamic resizing of the SGA and how “granules” affect the process. This paper attempts to shed some light on this subject.

See complete article at http://www.peasland.net/SGA_MAX_SIZE%20and%20Granules.zip

Downgrade Oracle From 10.2.0 To Lower Version

Tue, 17 Apr 2007 20:00:10 +0000

To rollback the upgradation of Database to 10.2.0 from lower releases Metalink: [table][tr][td]Doc ID: [/td][td]Note:398372.1[/td][/tr][/table]Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.1.0 to 10.2.0.3
Information in this document applies to any platform.
GoalRequirement :
~~~~~~~~~~~~~
To rollback the upgradation of Database to 10.2.0 from lower releases.

Supportability :
~~~~~~~~~~~~~
Release 9.2.0 : Downgrade possible to 9.2.0.6.0 and above
Release 10.1.0 : Downgrade possible to 10.1.0.4.0 and above

Note : It is not mandatory for you to have initially upgraded from those patchsets that are supported for downgrade.
Example : You can upgrade to 10.2.0 from 9.2.0.4.0 without issues, and in case of downgrading this database back to 9.2.0
you must now, however, ensure that you downgrade the Database to 9.2.0.6.0 or higher.

Oracle Default Ports List

Tue, 17 Apr 2007 22:34:35 +0000

Please refer to this Oracle Default Ports List.

See complete article at http://www.red-database-security.com/whitepaper/oracle_default_ports.html

Upgrade to Oracle Database 10g

Wed, 18 Apr 2007 23:20:35 +0000

Upgrading to Oracle Database 10g
This article provides a brief overview of the areas involved in upgrading existing databases to Oracle 10g Release 1. The article provides the minumum information needed when preparing for the 10g Database Administation OCP upgrade exam, including:

Supported Upgrade Paths
Pre-Upgrade Validation Checks
Database Upgrade Assistant (DBCA)
STARTUP UPGRADE
The whole migration process is beyond the scope of this article so please refer to the Upgrading a Database to the New Oracle Database 10g Release document for further information.

See complete article at: http://www.oracle-base.com/articles/10g/UpgradingTo10g.php

Controlling Database Access

Thu, 19 Apr 2007 18:23:17 +0000

Controlling Database Access

The default listener configuration places no limits at all on who can connect to the

database server: all security must be managed within the database or the application.

There will be many cases where it is desirable to limit access to users connecting from

certain machines or subnets. This can of course be done by a well-configured firewall,

but such restrictions can also be enforced by the listener itself.

TIP If your firewall is not capable of proxying Oracle Net traffic securely, then

the Oracle Net Connection Manager can be used as a proxy server instead.

The Connection Manager is not installed by default; you have to select it in a

“custom” installation.

Controlling access to a listener is done through configuring Oracle Net profiles,

which are in fact a set of directives in the sqlnet.ora file on the server machine.

Remember from Chapter 12 that the sqlnet.ora file is optional on both the client

side and the server side; listener security is a case where it becomes necessary. The

directives are as follows:

• TCP.VALIDNODE_CHECKING

• TCP.EXCLUDED_NODES

• TCP.INVITED_NODES

Once TCP.VALIDNODE_CHECKING is changed to YES from its default of NO, the

other directives are enabled. In practice, it is necessary to set only one of them. If any

node is listed in TCP.INVITED_NODES, then all other nodes are implicitly excluded;

if any node is listed in TCP.EXCLUDED_NODES, then all other nodes are implicitly

invited. If both directives are set and there is a conflict, then the TCP.INVITED_NODES

list takes precedence.

The invited and excluded nodes can be listed by name or by IP address, but the

syntax does not permit wildcards; each node must be listed individually. For this

reason, access control by the listener is most useful in a three-tier environment, where

all the sessions are spawned from a small number of application servers with fixed IP

addresses. Listing these in the TCP.INVITED_NODES list will ensure that no users can

bypass the application servers and make client/server connections to the database.

A large client base of PC terminals using dynamically acquired IP addresses cannot

easily be controlled in this manner. Note that this mechanism applies only to TCP.

Connections from user processes running on the server machine using IPC will always

be accepted. The profile can be created either with the Net Manager GUI or by editing the sqlnet.ora file directly.

The directives in sqlnet.ora will be

TCP.VALIDNODE_CHECKING = YES

TCP.INVITED_NODES = (as1.haunting.com, as2.haunting.com)

A Comparison of Oracle and MySQL

Fri, 20 Apr 2007 18:50:01 +0000

A Comparison of Oracle and MySQL

screen.width-461) window.open('http://www.dba-oracle.com/images/star_four.jpg');" onload='if(this.width>screen.width-460) { this.width=screen.width-460; this.style.cursor="pointer";}' onmousewheel="return bbimg(this)" alt='' />[font="Times]

March 25, 2005

Article Review

A Comparison of Oracle and MySQL
By Gregg Petri
IOUG SELECT Journal, First quarter 2005 (www.selectonline.org)

This is an excellent overview for the Oracle DBA who wants to understand how the ubiquitous MySQL database compares to Oracle databases. Petri outlines the basic differences between Oracle’s and the MySQL implementation of the relational model, including table, index, data types, tables, triggers, and many, many more. In this comprehensive comparison we see many interesting conclusions:

Data Types

– MySQL only supports primitive data types, DATE, NUMBER and STRING and TIME.

Tables

– MySQL supports more types of tables than Oracle, including MyISAM, MERGE, ISAM, innoDB, BDB and HEAP.

Indexes

– MySQL only supports one index type, but implements different access paths using the various table types.

View and Inline Views

– MySQL is planning to offer these features soon in a new release of MySQL.

Sequences

– MySQL uses an AUTO-INCREMENT attribute assigned to a column when creating a table. . . The ability to start and increment sequence numbers values at the initial value within a group is a useful feature provided by MySQL that is not available in Oracle 9i database.

SQL Inserts

– Unlike Oracle 9i database, MySQL allows insert statements with a VALUES clause to specify multiple sets of data to insert.

Procedural code

– MySQL does not have a language such as PL/SQL, but instead allows C++ to be used for functions, stored procedures and triggers. Overall, this is a good high-level comparison of MySQL and Oracle.

How to resize temporary tablespace

Tue, 05 Jun 2007 22:25:23 +0000

In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error!

Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '' drop including datafiles; command. Each method is explained below.

See complete article at http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml

Oracle Primary Events Reference

Wed, 06 Jun 2007 18:06:30 +0000

Oracle offers hundreds of events to help DBA and developers to diagnose specific problems they are facing.

Some DBAs are very familiar with event 10046 and 10053. There are also some other primary events available.

See this reference list for primary events.

See complete article at http://www.juliandyke.com/Diagnostics/Events/EventReference.html