connect sys as sysdba

How to remove Junk Characters from Oracle Data Files..

2008-09-14T00:30:00.000-07:00

Recently I came across this scenario. We were working on one activity of datafile movement from one mount point to another mount point for space management at OS level.

Database Size = 2 TB Approx
Total Number of Datafiles = 1570
OS = HP-UX
DB Version = 8.1.7.4

We identified 35 datafiles, which should be moved from one mount point to another mount point, because of their faster growth rate. While performing this activity we landed up in problem because some datafiles was containing junk characters.

You can read the complete case study on www.metalink.oracle.com under note id 736533.1

Nologging - By Example

2008-05-19T22:55:00.000-07:00

I have created one empty table.

SQL> create table nolog_test as select * from dba_objects where 1=2;

Table created.

SQL> set autot on
SQL> insert into nolog_test select * from dba_objects;

50448 rows created.
Statistics
----------------------------------------------------------
1343 recursive calls
4135 db block gets
7154 consistent gets
3 physical reads
5685740 redo size
671 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50448 rows processed

Here it is normal insert. Check the amount of redo generated.

SQL> alter table nolog_test nologging;

Table altered.

Now I place the table in nologging mode.

SQL> insert into nolog_test select * from dba_objects;

50448 rows created.

Statistics
----------------------------------------------------------
337 recursive calls
3888 db block gets
6803 consistent gets
0 physical reads
5655284 redo size
674 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
50448 rows processed

The redo size will not change (almost same) But if i will use append clause it will change.

SQL> insert /*+append*/ into nolog_test select * from dba_objects;

50448 rows created.
Statistics
----------------------------------------------------------
200 recursive calls
771 db block gets
5402 consistent gets
0 physical reads
11740 redo size
660 bytes sent via SQL*Net to client
592 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50448 rows processed

At last I will place my database in force nologging mode. Here even append will generate the redo.

SQL> alter database force logging;

Database altered.
SQL> insert /*+append*/ into nolog_test select * from dba_objects;

50448 rows created.

Statistics
----------------------------------------------------------
160 recursive calls
761 db block gets
5390 consistent gets
0 physical reads
5711072 redo size
660 bytes sent via SQL*Net to client
592 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50448 rows processed

Conclusion : Nologging mode will not stop generating redo. It will not generate redo only in case of append, direct path loading (sql* Loader) and if my database is in noarchive log mode. The exception for append is "Force Logging".

Sometimes something goes unnoticed…

2007-12-20T23:24:00.001-08:00

ORA-01194: File 5 needs media recovery to be consistent

When you get the above error if you decide that you have to recover the datafile (or database), think twice. Always you may not have to recover the file. I had faced this scenario in one of the client places. Though the initial plan was to restore the datafile from the backup and recover, I had to change my mind.

Now the scenario…I was informed that a guy at the client site had restarted the database (on windows) and they could not open the database because of the datafile inconsistency. After some searching I could find that the problem was with the backup process!!

It happened so that when the hot backup (user-managed) of the database was being taken, that guy had shutdown the database. Now, while restarting the database a particular datafile was asking for media recovery. When checked I found that the datafile was still in backup mode. i.e. the database was shutdown when the backup was going on. Now, the solution is simple. Mount the database and use alter database datafile 5 end backup command and open the database. There is absolutely no need to recover the datafile!! J

So what exactly happened here?!? Well, nothing strange happened. It is a normal oracle behavior, nothing else. When the instance was restarted, the datafile which was in the backup mode will look old because the datafile header is freezed with older SCN. So it will (and it shouldJ) ask for recovery. That’s it!

Generally on unix servers if you issue normal shutdown commands (except abort!!) on the database when the hot backup is going on, shutdown wont happen. It will throw an error saying that the datafile is in backup mode. So there is very less chance of facing this scenario on unix servers.

But in windows, it is general practice to shutdown the database using the OracleService on the services window. In this case even if the backup is happening database goes down with shutdown abort command internally. You won’t come to know that shutdown abort has happened. You can even face this scenario when the instance crashes during backup (hot backup).You try to restart the instance without knowing that backup was active during the instance crash and end up with the same error.

So, don’t panic… Take it easy!!!

What is SSTIOMAX ????

2007-12-20T23:23:00.000-08:00

SSTIOMAX is an internal parameter/constant used by oracle, which limits the maximum amount of data transfer in a single IO of a read or write operation.

This parameter is fixed and cannot be tuned/changed.

Relationship between SSTIOMAX and db_file_multiblock_read_count (MBRC)

More often than not, DBAs try to increase the db_file_multiblock_read_count parameter (which can be set in the init.ora), in an attempt to optimize the IO performance of the read and write operations.

Normally, with a higher value of MBRC, the IO performance is expected to be better. So, users tend to increase this parameter to a higher value, in case they find it beneficial. But, there is a limitation on this.

The limitation is, the product of db_block_size and MBRC cannot exceed the SSTIOMAX. For example:

db_block_size * db_file_multiblock_read_count <= SSTIOMAX
(which is predefined for a particular version of oracle)

If the value of the product exceeds this, then the value of db_file_multiblock_read_count set in the init.ora is ignored and it is set as follows:

db_file_multiblock_read_count = SSTIOMAX/db_block_size (rounded)

For example:

If the SSTIOMAX is 128K, and the db_block_size is 8k ,then the maximum value to which the MBRC can be raised is 16. If it is set to a higher value (say 20), then the value set in init.ora (20 in this case) is ignored and MBRC is
assigned a value of 16.

You can see the actual value of MBRC by querying the V$PARAMETER view or by issuing 'show parameter db_file_multiblock_read_count' in the svrmgrl.
If the limitation is violated, then the value that is displayed might be different from the value that is set in the init.ora

Value of SSTIOMAX in oracle 7 and oracle 8

The value of SSTIOMAX has been raised in newer versions of oracle.

In Oracle 7.3 --> 128K

(Patch exists for 7.3.2.2 and 7.3.2.3 and 7.3.3.4 in which the limit has been raised to 1 MB)

From Oracle 8.0.5 --> 1 MB

Examples of possible combinations of SSTIOMAX and MBRC
For a db_block_size of 2K:

In oracle 7.3, maximum value for MBRC = 128/2 = 64

In oracle 8.0.5, maximum value for MBRC = 1024/2 = 512

11g MEMORY_TARGET Parameter Dependency

2007-12-20T23:16:00.000-08:00

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1 to 11.1
Information in this document applies to any platform.

Purpose

Automatic memory management can be configured using a target memory size initialization parameter MEMORY_TARGET and a maximum memory size initialization parameter MEMORY_MAX_TARGET. Oracle Database then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database.

This note describes the relationships between the various memory sizing parameters.

Scope and Application

DBA's with prior knowledge of 9i and 10g memory management.

11g MEMORY_TARGET Parameter Dependency

If MEMORY_TARGET is set to a non-zero value:

If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_SIZE.
If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)) and will auto-tune subcomps.
If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total server memory in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% for sga and 40% for PGA at startup.

If MEMORY_TARGET is not set or set to set to 0 explicitly (default value is 0 for 11g):

If SGA_TARGET is set we will only auto-tune the sizes of the sub-components of the SGA. PGA will be autotuned independent of whether it is explicitly set or not. Though the whole SGA(SGA_TARGET) and the PGA(PGA_AGGREGATE_TARGET) will not be auto-tuned, i.e., will not grow or shrink automatically.
If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow the same policy as we have today; PGA will be auto-tuned and the SGA will not be auto-tuned and parameters for some of the sub-components will have to be set explicitly (for SGA_TARGET).
If only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we will not auto tune sga and pga. It will default to 10gR2 behavior within sga and pga.
If sga_max_size is not user set, we will internally set it to MEMORY_MAX_TARGET.

In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.

If you wish to monitor the decisions made by Automatic Memory Management following views can be useful

V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memory components
V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests

Features introduced in the various Oracle Server Releases

2007-12-04T21:32:00.000-08:00

This document summarizes the differences between Oracle Server releases.

Most DBA’s and developers work with multiple versions of Oracle at any particular time. This document describes the high level features introduced with each new version of the Oracle database. It is intended to be used as a quick reference as to whether a feature can be implemented, or if a upgrade is required.

Oracle 10g Release 2 (10.2.0) - September 2005

Transparent Data Encryption
Async commits
CONNECT ROLE can not only connect
Passwords for DB Links are encrypted
New asmcmd utility for managing ASM storage

Oracle 10g Release 1 (10.1.0)

Grid computing - an extension of the clustering feature (Real Application Clusters)
Manageability improvements (self-tuning features)
Performance and scalability improvements
Automated Storage Management (ASM)
Automatic Workload Repository (AWR)
Automatic Database Diagnostic Monitor (ADDM)
Flashback operations available on row, transaction, table or database level
Ability to UNDROP a table from a recycle bin
Ability to rename tablespaces
Ability to transport tablespaces across machine types (E.g Windows to Unix)
New ‘drop database’ statement
New database scheduler - DBMS_SCHEDULER
DBMS_FILE_TRANSFER Package
Support for bigfile tablespaces that is up to 8 Exabytes in size
Data Pump - faster data movement with expdp and impdp.

Oracle 9i Release 2 (9.2.0)

Locally Managed SYSTEM tablespaces
Oracle Streams - new data sharing/replication feature (can potentially replace Oracle Advance Replication and Standby Databases)
XML DB (Oracle is now a standards compliant XML database)
Data segment compression (compress keys in tables - only when loading data)
Cluster file system for Windows and Linux (raw devices are no longer required).
Create logical standby databases with Data Guard
Java JDK 1.3 used inside the database (JVM)
Oracle Data Guard Enhancements (SQL Apply mode - logical copy of primary database, automatic failover
Security Improvements - Default Install Accounts locked, VPD on synonyms, AES, Migrate Users to Directory

Oracle 9i Release 1 (9.0.1) - June 2001

Traditional rollback segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it’s own “Rollback Segments” and size them automatically without any DBA involvement.
Flashback query (dbms_flashback.enable) - one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
Use Oracle Ultra Search for searching databases, file systems, etc. The UltraSearch crawler fetch data and hand it to Oracle Text to be indexed.
Oracle Nameserver is still available, but deprecate in favour of LDAP Naming (using the Oracle Internet Directory Server). A nameserver proxy is provided for backwards compatibility as pre-8i client cannot resolve names from an LDAP server.
Oracle Parallel Server’s (OPS) scalability was improved - now called Real Application Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications doesn’t need to be cluster aware anymore.
The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read write operations. The Data Guard Broker allows single step fail-over when disaster strikes.
Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
Dynamic Memory Management - Buffer Pools and shared pool can be resized on-the-fly. This eliminates the need to restart the database each time parameter changes were made.
On-line table and index reorganization.
VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI’s, etc. XML integrated with AQ.
Cost Based Optimizer now also consider memory and CPU, not only disk access cost as before.
PL/SQL programs can be natively compiled to binaries.
Deep data protection - fine grained security and auditing. Put security on DB level. SQL access do not mean unrestricted access.
Resumable backups and statements - suspend statement instead of rolling back immediately.
List Partitioning - partitioning on a list of values.
ETL (eXtract, transformation, load) Operations - with external tables and pipelining.
OLAP - Express functionality included in the DB.
Data Mining - Oracle Darwin’s features included in the DB.

Oracle 8i (8.1.7)

Static HTTP server included (Apache)
JVM Accelerator to improve performance of Java code
Java Server Pages (JSP) engine
MemStat - A new utility for analyzing Java Memory footprints
OIS - Oracle Integration Server introduced.
PLSQL Gateway introduced for deploying PL/SQL based solutions on the
Web
Enterprise Manager Enhancements - including new HTML based reporting and Advanced Replication functionality included.
New Database Character Set Migration utility included.

Oracle 8i (8.1.6)

PL/SQL Server Pages (PSP’s)
DBA Studio Introduced
Statspack
New SQL Functions (rank, moving average)
ALTER FREELISTS command (previously done by DROP/CREATE TABLE)
Checksums always on for SYSTEM tablespace allowing many possible
corruptions to be fixed before writing to disk
XML Parser for Java
New PLSQL encrypt/decrypt package introduced
User and Schemas separated
Numerous Performance Enhancements

Oracle 8i (8.1.5)

Fast Start recovery - Checkpoint rate auto-adjusted to meet roll
forward criteria
Reorganize indexes/index only tables which users accessing data -
Online index rebuilds
Log Miner introduced - Allows on-line or archived redo logs to be
viewed via SQL
OPS Cache Fusion introduced avoiding disk I/O during cross-node
communication
Advanced Queueing improvements (security, performance, OO4O support
User Security Improvements - more centralisation, single enterprise
user, users/roles across multiple databases.
Virtual private database
JAVA stored procedures (Oracle Java VM)
Oracle iFS
Resource Management using priorities - resource classes
Hash and Composite partitioned table types
SQL*Loader direct load API
Copy optimizer statistics across databases to ensure same access
paths across different environments.
Standby Database - Auto shipping and application of redo logs. Read
Only queries on standby database allowed.
Enterprise Manager v2 delivered
NLS - Euro Symbol supported
Analyze tables in parallel
Temporary tables supported.
Net8 support for SSL, HTTP, HOP protocols
Transportable tablespaces between databases
Locally managed tablespaces - automatic sizing of extents,
elimination of tablespace fragmentation, tablespace information managed in tablespace (i.e moved from data dictionary) improving tablespace reliability
Drop Column on table (Finally !!!!!)
DBMS_DEBUG PL/SQL package, DBMS_SQL replaced by new EXECUTE
IMMEDIATE statement
Progress Monitor to track long running DML, DDL
Functional Indexes - NLS, case insensitive, descending

Oracle 8.0 - June 1997

Object Relational database
Object Types (not just date, character, number as in v7
SQL3 standard
Call external procedures
LOB >1 per table
Partitioned Tables and Indexes
export/import individual partitions
partitions in multiple tablespaces
Online/offline, backup/recover individual partitions
merge/balance partitions
Advanced Queuing for message handling
Many performance improvements to SQL/PLSQL/OCI making more efficient use of CPU/Memory. V7 limits extended (e.g. 1000 columns/table, 4000 bytes VARCHAR2)
Parallel DML statements
Connection Pooling ( uses the physical connection for idle users and transparently re-establishes the connection when needed) to support more concurrent users.
Improved “STAR” Query optimizer
Integrated Distributed Lock Manager in Oracle PS (as opposed to Operating system DLM in v7).
Performance improvements in OPS - global V$ views introduced across all instances, transparent failover to a new node
Data Cartridges introduced on database (e.g. image, video, context, time, spatial)
Backup/Recovery improvements - Tablespace point in time recovery, incremental backups, parallel backup/recovery. Recovery manager introduced
Security Server introduced for central user administration. User password expiry, password profiles, allow custom password scheme. Privileged database links (no need for password to be stored)
Fast Refresh for complex snapshots, parallel replication, PL/SQL replication code moved in to Oracle kernel. Replication manager introduced.
Index Organized tables
Deferred integrity constraint checking (deferred until end of transaction instead of end of statement).
SQL*Net replaced by Net8
Reverse Key indexes
Any VIEW updateable
New ROWID format

Oracle 7.3

Partitioned Views
Bitmapped Indexes
Asynchronous read ahead for table scans
Standby Database
Deferred transaction recovery on instance startup
Updatable Join Views (with restrictions)
SQLDBA no longer shipped.
Index rebuilds
db_verify introduced
Context Option
Spatial Data Option
Tablespaces changes - Coalesce, Temporary Permanent,
Trigger compilation, debug
Unlimited extents on STORAGE clause.
Some init.ora parameters modifiable - TIMED_STATISTICS
HASH Joins, Antijoins
Histograms
Dependencies
Oracle Trace
Advanced Replication Object Groups
PL/SQL - UTL_FILE

Oracle 7.2

Resizable, autoextend data files
Shrink Rollback Segments manually
Create table, index UNRECOVERABLE
Subquery in FROM clause
PL/SQL wrapper
PL/SQL Cursor variables
Checksums - DB_BLOCK_CHECKSUM, LOG_BLOCK_CHECKSUM
Parallel create table
Job Queues - DBMS_JOB
DBMS_SPACE
DBMS Application Info
Sorting Improvements - SORT_DIRECT_WRITES

Oracle 7.1

ANSI/ISO SQL92 Entry Level
Advanced Replication - Symmetric Data replication
Snapshot Refresh Groups
Parallel Recovery
Dynamic SQL - DBMS_SQL
Parallel Query Options - query, index creation, data loading
Server Manager introduced
Read Only tablespaces

Oracle 7.0 - June 1992

Database Integrity Constraints (primary, foreign keys, check
constraints, default values)
Stored procedures and functions, procedure packages
Database Triggers
View compilation
User defined SQL functions
Role based security
Multiple Redo members - mirrored online redo log files
Resource Limits - Profiles
Much enhanced Auditing
Enhanced Distributed database functionality - INSERTS,
UPDATES,DELETES, 2PC
Incomplete database recovery (e.g SCN)
Cost based optimiser
TRUNCATE tables
Datatype changes (i.e VARCHAR2 CHAR, VARCHAR)
SQL*Net v2, MTS
Checkpoint process
Data replication - Snapshots

Oracle 6.2

Oracle Parallel Server

Oracle 6 - July 1988

Row-level locking
On-line database backups
PL/SQL in the database

Oracle 5.1

Distributed queries

Oracle 5.0 - 1986

Supporting for the Client-Server model - PC’s can access the DB on remote host

Oracle 4 - 1984

Read consistency

Oracle 3 - 1981

Atomic execution of SQL statements and transactions (COMMIT and ROLLBACK of transactions)
Nonblocking queries (no more read locks)
Re-written in the C Programming Language

Oracle 2 - 1979

First public release
Basic SQL functionality, queries and joins