Friday, March 28, 2008

Password is case sensitive now - Oracle 11g

Good news for people who always wanted oracle schema passwords to be case sensitive.
Oracle has done this in 11g.

Just to show an example:

C:\Documents and Settings\sachin.arora>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 28 15:02:56 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user test identified by AroRa default tablespace users;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> connect test/arora
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect test/AroRa
Connected.

With 11g, Oracle has made significant changes in password maintenence algorithm.
The password verifier is based on 160 bit SHA-1 hashing algorithm. This empowers Oracle to not only allow case sensitive passwords, but also enable the users to keep a strong password that can include special characters.

For pre-11g users, when they migrate to 11g, their password still behave password insensitive. However, i feel its good to take the advantage of this useful feature and change the password.

For applications which cant afford to change their passwords for xyzee purpose, Oracle has supplied an init.ora i.e. SEC_CASE_SENSITIVE_LOGON that can be set to true/false.
Default value of this parameter is true which means passwords should be case sensitive.

Thursday, March 27, 2008

Memory parameters in 11g - MEMORY_TARGET and MEMORY_MAX_TARGET

Few days back, I was checking metalink and came across few good notes on 11g memory management.
Though we are not using 11g in our development environment, i have downloaded a copy of 11g rel1 on my windows machine.

Testing out these new paarmeters gave me an insight on how Oracle has improvised its memory management.

Oracle has introduced 2 new parameters (along with many others!) - MEMORY_TARGET and MEMORY_MAX_TARGET

Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g)

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

For instance :
If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

  • SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
  • SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
  • SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
  • PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.
When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.
This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.


I tested out few of these experiments mentioned above. Here are the results:

Test 1: When Memory_target is 1G

init.ora parameter
> memory_target=1073741824
> sga_max_size=0

SQL> startup
ORACLE instance started.

Total System Global Area 640303104 bytes
Fixed Size 1335024 bytes
Variable Size 226492688 bytes
Database Buffers 406847488 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.

>> It seems around 600M is used for SGA. The decesion of using 600 MB of SGA is of Oracle itself.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- -------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 612M
sga_target big integer 0

Test 2: When MEMORY_TARGET is 2g (with no change in any other parameter)

SQL> alter system set memory_target=2048m scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1288978432 bytes
Fixed Size 1339344 bytes
Variable Size 226492464 bytes
Database Buffers 1052770304 bytes
Redo Buffers 8376320 bytes
Database mounted.
Database opened.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 0

Test 3: When sga_max_size/sga_target=700M and memory_target=1G

SQL> startup
ORACLE instance started.

Total System Global Area 732352512 bytes
Fixed Size 1335696 bytes
Variable Size 192941680 bytes
Database Buffers 532676608 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 700M
sga_target big integer 700M
SQL>
SQL> sho parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G
shared_memory_address integer 0

This time, oracle has acknowledged the SGA_MAX_SIZE parameter.

Friday, March 14, 2008

Cost, selectivity and cardinality - all changes in 10g rel2 10053 trace file

Few days back o one of the google groups, there was issue posted by a gentleman about "index selectivity". Per him the "index selectivity" should be different than what is being shown by the optimizer. He was inquiring whether the procedure he followed to calculate "index selectivity" is correct or not.

Here is what he had to say:

I don't know how the optimizer has calculated ix_sel of the index
IDT_FCMO_TMS.
Oracle version: 10.2.0.3.0
Any idea?

SQL Statement:
select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL


10053 Trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TDTC_CAPACIDAD_MODALIDAD Alias: TDTC_CAPACIDAD_MODALIDAD
#Rows: 82240 #Blks: 1132 AvgRowLen: 82.00
Index Stats::
Index: IDT_FCMO_CHA Col#: 5
LVLS: 1 #LB: 170 #DK: 939 LB/K: 1.00 DB/K: 14.00 CLUF:13425.00
Index: IDT_FCMO_TMS Col#: 34 <----- the index
LVLS: 1 #LB: 214 #DK: 471 LB/K: 1.00 DB/K: 26.00 CLUF:12648.00 <----- the index
Index: IDT_FCMO_TSU Col#: 31
LVLS: 0 #LB: 1 #DK: 58 LB/K: 1.00 DB/K: 6.00 CLUF: 370.00
Index: RDT_PCMO Col#: 1 2
LVLS: 1 #LB: 206 #DK: 82061 LB/K: 1.00 DB/K: 1.00 CLUF:28928.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#3): CMO_CODTSG(VARCHAR2)
AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619 <---------- first column of the index
Column (#4): CMO_CODTMS(VARCHAR2)
AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584 <---------- second column of the index
Table: TDTC_CAPACIDAD_MODALIDAD Alias:
TDTC_CAPACIDAD_MODALIDAD
Card: Original: 82240 Rounded: 10 Computed: 10.12 Non Adjusted:
10.12
Access Path: TableScan
Cost: 184.60 Resp: 184.60 Degree: 0
Cost_io: 181.00 Cost_cpu: 29454495
Resp_io: 181.00 Resp_cpu: 29454495
Access Path: index (index (FFS))
Index: IDT_FCMO_TMS
resc_io: 36.00 resc_cpu: 15484960
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 37.89 Resp: 37.89 Degree: 1
Cost_io: 36.00 Cost_cpu: 15484960
Resp_io: 36.00 Resp_cpu: 15484960
Access Path: index (AllEqRange)
Index: IDT_FCMO_TMS
resc_io: 1.00 resc_cpu: 42971
ix_sel: 0.0021231 ix_sel_with_filters: 0.0021231 <-------------------- wrong ix_sel?
Cost: 1.01 Resp: 1.01 Degree: 1
Best:: AccessPath: IndexRange Index: IDT_FCMO_TMS
Cost: 1.01 Degree: 1 Resp: 1.01 Card: 10.12 Bytes: 0
***************************************


IX_SEL should be 0.047619 * 0.002584 (which is far from what optimizer showed i.e 0.0021231)

According to Jonathan lewis book "Cost-Based Oracle Fundamentals", it is well explain in Chapter-4 P 66,67 that in case of select using multiple column index, index selectivity is calculated using the multiplication of individual selectivities of columns.
And that is precisely what the person seeking support showed.

While testing this result on 10.2.0.2, I found the index selectivity never gets multiplied in case of multicolumn index. In fact optimizer calculates index selectivity based on "distinct keys" of index.In this case (per the trace shown above) it is 471.
So the index selectivity comes out to be 1/(DK of index)=1/471=0.0021231, which is what is shown by optimizer.

I think, by the time Jonathan's book was published, not all the changes were tested. In fact I visited his blog and some couple of finding similar to this posted there.

Thursday, March 13, 2008

ORA-01115 and ORA-27067 during crash recovery

Yesterday, one of our development server got a power outage because of which all our databases running on that server crashed.

After the server was brought up, I started these databases manually. One of the db refused to startup with normal "startup" command.

Here was the front-end error.

SQL> startup
ORACLE instance started.

Total System Global Area 432013312 bytes
Fixed Size 2169944 bytes
Variable Size 356115368 bytes
Database Buffers 62914560 bytes
Redo Buffers 10813440 bytes
Database mounted.
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/user/oracle/data/initdb/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864

And here is what I saw in alert.log

ALTER DATABASE OPEN
Wed Mar 12 10:01:14 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Wed Mar 12 10:01:15 2008
Started redo scan
Wed Mar 12 10:01:16 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:16 2008
Started redo application at
Thread 1: logseq 49756, block 4723
Wed Mar 12 10:01:16 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 49756 Reading mem 0
Mem# 0 errs 0: /mnt/filer15adapp/data/adap_dev/redo1.log
Wed Mar 12 10:01:16 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/bdump/adap_dev_p001_4721.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1409024
Wed Mar 12 10:01:19 2008
Aborting crash recovery due to slave death, attempting serial crash recovery
Wed Mar 12 10:01:19 2008
Beginning crash recovery of 1 threads
Wed Mar 12 10:01:19 2008
Started redo scan
Wed Mar 12 10:01:20 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:25 2008
Aborting crash recovery due to error 1115
Wed Mar 12 10:01:25 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/udump/adap_dev_ora_4717.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864
ORA-1115 signalled during: ALTER DATABASE OPEN...
Wed Mar 12 10:02:37 2008
Shutting down instance: further logons disabled


This was the first time i got this error in my experience. Db being running in noarchive log mode (this being dev server) just added to my worries.

Googling didnt help. After some research work, i encountered a note on metalink Note:423128.1.

Though they didnt explain the cause of the problem, but suggested the workaround.

i.e.

1) Mount the database
2) Issue "recover database" command
-- Wait for "Media recovery complete"
3) alter database open

I'm s not sure about the cause as this Note on metalink refers to a Bug 5362935 - "CRASH RECOVERY FAILS WITH ORA-27067 ERROR", which is closed with status "
Suspended, Req'd Info not Avail" and lots of useful information is hidden so people like us, who are not working in Oracle Corp cant see it.

Wednesday, March 5, 2008

How to pass a value from Oracle to Unix shell environment variable?

Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.
Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0

{{SELECT STMT}};
exit
EOF`


For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF
> set echo off verify off feed off termout off pages 0
> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8
> from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files
> group by tablespace_name) tot,
> dba_tablespaces tbs
> where tot.tablespace_name = tbs.tablespace_name
> and fre.tablespace_name(+) = tbs.tablespace_name
> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
> /
> exit
> EOF`

-bash-3.00$ echo $DB_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.