Tuesday, September 13, 2005

I asked to tom abt. db_2k_cache_size

Hi Tom,

If my database server have below
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=8M
DB_8K_CACHE_SIZE=4M

and if my database server have
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=0
DB_8K_CACHE_SIZE=0

what are the proc and cros between this two ?

Regards,
Nikunj


Followup:
if you don't have any 2k or 8k tablespaces - you would not configure the 2k/8k
caches - they only get used if you have them.

If you "unconfigure" them and have tablespaces with those blocksizes - you'll
not be able to use them.

Monday, September 12, 2005

How to get Block size base on OS

For Windows on Intel.
1. For window on Intel default size is 512 byte, but you can change it by pre-formatting hard drive.

2. Use fsutil fsinfo ntfsinfo c: to get

c:\>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number : 0x1e345b64345b3e49
Version : 3.1
Number Sectors : 0x0000000004a852c0
Total Clusters : 0x0000000000950a58
Free Clusters : 0x00000000003ae433
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096 -- this is answer
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a852c
Mft Zone Start : 0x0000000000800020
Mft Zone End : 0x000000000083b520

3. To get all possible OS block sizes use
c:\ format /?

4. In explorer select your hard drive, click right button, select "Format..."
from
context menu, and see your OS block size as "Allocation unit size".

Redhat linux on Intel
dump2fs /dev/hda

Saturday, September 10, 2005

set your sql prompt

please create login.sql with as below and put it in to ORACLE_HOME/bin.

define gname=idle
connect &connect_to
column global_name new_value gname
select lower(user) || '@' || substr( global_name,1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

now when ever you connect to database use @login instead of connect so you get idea where you are.
i.e.
varakh@NIKUNJ> @login
Enter value for connect_to: ex2005/test@test
Connected.

GLOBAL_NAME
--------------------------------------------------------------------------------
ex2005@TEST

Input truncated to 24 characters
ex2005@TEST>

Friday, September 09, 2005

Worked example for using Excel through ODBC

I had read it on asktom i thing its good artical i had not tested but its good so i had posted here
Click Here

Tuesday, September 06, 2005

SPFILE

I have Book of Thomas Kyte Effective Oracle by Design. He had write good things on SPFILE which I want to share with you.

The Problem with PFILEs
PFILE resided on the database server. To start and shutdown the server everyone need to access the database server physically to ensure the correct init.ora file being used.

Also, if you used system management tools OEM, OMS to start and stop the database, the PFILEs needed to be physically wherever the system management tools was, and this generally, was not the machine where your database ran. In short you great confusion and “missing parameters” from time to time, If you simply started the database from a different machine, an entirely different set of parameters could be used, since a different PFILE would be used.

Another problem was that changes where made to the instance via the ALTER SYSTEM command, you would need to remember to update each and every copy of the PFILE out there in order to have the new setting take effect after next restart of the database. If you forgot, the system would run file until someone restarted the database, and then you would discover that the new settings you made were no longer there.

How SPFILEs Work
As SPFILE is stored in binary format on the server itself. You no longer need to have a local copy to start the database remotely. The binary file format serves two purposes:
• Change made via the ALTER SYSTEM command may now persist across server restarts. If is no longer necessary to update the init.ora text file with every little change. You can use SQL to maintain your parameter settings.
• It prevents us from being tempted to edit the file using any sort of editor. If provides SQL to manipulate it and prohibits us from editing it directly. However, if you do want to edit the file, there are techniques you can employ to accomplish that.

How to force database to use SPFILE.
If you are using oracle 9i or later by default server used SPFILE but if you migrated you can do it with.

Create spfile from pfile=’location of your pfile’;

Next time you start your database it will use SPFILE.

You can create textual version of this binary SPFILE using below command.

Create pfile=’path where you want to be created’ from spfile=’path of spfile’;

Now if you want to force database to use PFILE instead of SPFILE.
Start your database with below command instead of just startup.

Startup pfile=’location of your pfile’;

PFILE is not still obsolete?

Using a PFILE meant that if you started a database on Unix from your windows pc, the parameter file for the database need to be accessible on the PC. For some this was not a big deal. They would simply telnet into the database server itself and start it up or shut it down. The drawback to this was that everyone needed to physically access the server in order to ensure the correct init.ora file was being used to start the database (so everyone needed the ability to physically access the server)

refreshing my memory

Hi friends,

I get interview call and I am just refreshing my memory after going through with my manuals which I get from performance tuning training from Oracle Education centre.

So get some good point and here I want to share with you.

Why we need to check alter log file regularly.
•To detect internal errors (ora-600)
•Block corruption errors (ora-1578 or ora-1498)
•Operation that effect database structures and parameters, statement such as CREATE
DATABASE, STARTUP, SHUTDOWN, RECOVER.
•View nondefualt initialization parameter
•Checkpoint start and end times
•Incomplete checkpoints
•Time to perform archiving
•Instance recovery start and complete times
•Dead lock and timeout errors.

Difference between V$view and X$tables.
V$ views
•These are based on X$tables, therefore some V$ views are available in NOMOUNT and
MOUNT stages.
•They are listed in v$fixed_table.
•The V$ views actually synonyms for V_$ views and they are belong to the sys user.

X$ tables
•These are not usually queried directly, not all the information is necessarily
useful.
•The X$ tables are memory structures that hold instance information and are available
when the instance is in NOMOUNT and MOUNT state.
•The X$ tables are dynamic and there contents are constantly changing.

The V$ views and the underlying X$ tables are populated at instance startup and cleared at shutdown.

When SYSTEM tablespace is locally managed
•It is not possible to create dictionary managed tablespaces.
•It is not possible to migrate locally managed tablespace to dictionary managed.
•It is not possible to transport dictionary managed tablespace into database with
locally managed system tablespace. Note that after the transport, dictionary managed
tablespaces cannot be made read/write. The tablespace can be migrated to locally
managed and then read/write.
•It is possible to DROP and READ ONLY dictionary managed tablespace when system
tablespace locally managed.

Note : If the database create using DBCA then locally managed system tablespace is the default.