DBA

I want to find the size....

sql>show parameter db_block_size;

Name Type Value
-----------------------------------------------------------
db_block_size integer 8192

How to check the size in Oracle 9i

sql>select segment_name ,bytes/1024 as totsize_bytes, (blocks * 8192)/1024 as totbytes from user_segments where segment_name = upper('&1') and segment_type='TABLE';

Enter value for 1:

If you put any segment name and then you will see that table size.

Step 1 :-

• Put the tablespace in backup mode
• Copy the datafiles which tablespace in backup mode
• Once copy the datafiles is completed putout from the backup mode
• switch logfile
• archive log mode is enabled

Step 2 :-

sql> select tablespace_name from dba_data_files;

tablespace_name

----------------------------------

system

sysaux

users

undo

temp

example

sql>alter tablespace system begin backup;

sql>host copy E:\oracle\product\10.2.0\oradata\localdb\system01.dbf e:\oracle\backup;

1 files copied

sql>select * from v$backup;

file status change# time

-----------------------------------------------

1 active 1134456 22-nov-08

2 not active 0

" " "

sql> alter tablespace system end backup;

system altered.

After exec, you should check the any datafiles in backup mode. If any datafiles in backup mode ,you should exec end backup condition after taken backup datafiles.

sql>alter system switch logfile;

system altered.

This is consistent backup that means all datafiles and controlfiles are consistent to point in time.database in no archive log mode.

Steps....

• shutdown the database ,sql>shutdown normal
• Copy the controlfiles and datafiles,parameter file and passwordfile os level and then paste it in another location.
• startup database ,sql>startup

Example.....

• shutdown the database, drop the all controlfiles and datafiles and then startup the database.
• Error ora-00205-Error in identifying the control files for the location.
• If you have 5 multiplexing controlfiles,2 multiplexing in different location and remain 3 controlfiles were dropped,you should modify the location in parameter file current controlfile and then save it.
• startup the database using parameter files.
• In mount database,error will appear.
• Recover datafile system and then some datafile , in this case you should copy the backup files and paste it in default location.

sql>recover database;

media recovery complete.

sql>alter database open;

Database opened.

ora-01031- insufficent privilege

Suppose these error occur means..your ora_dba group not configured in OS authentication.

Solve...

• Control Panel -----------> Adminstrative tools -------> Computer management
• Click Local groups and then users or groups
• on right side administrative is appear.right click on this and click properties.

In this case You should find Ora_dba ..its not appear in this. so you go to add the Ora_dba privilege to OS.

• run -------> cmd enter
• > net localgroup ora_dba /add enter

And then you find the Ora_dba is there and then add ora_dba to your administrator groups.

• >set oracle_sid=oracle
• sqlplus
• sys/sys@localdb as sysdba

database connected.........and then you can easily access it.

Due to error occur on Oracle Listner not properly mentioned

So.., you should check lisntener.ora file and modify

Example :-

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

< (SID_DESC =
(SID_NAME = localdb )
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = localdb) >

Note : < ... > here check your service name either there or not?localdb -------- db name
And then go to
lsnrctl >stop

lsnrctl>start

Or

>set oracle_sid=localdb
>sqlplus
username : / as sysdba
>startup


& then you connect sys/sys@localdb as sysdba

Your service should be started
------------------------------------------------------------------------------------------
Thanks & Regards
S.Mohamed Azarudeen
azarmohds@gmail.com

Buffer Hit Ratio :-

Buffer hit ratio notes:

• Consistent Gets - The number of blocks accesses made to the block buffer to retrive data in a consistent mode.
• DB Blk gets - The number of blocks accessed via single block gets.
• Physical Reads - The cumulative number of blocks read from disk.
• Logical reads are the sum of consisent gets and db block gets.
• the db block gets statstic value is incremented when a block is read for update and when segment header blocks are accessed.
• Hit ratio should be > 80% , else increase db_block_buffers in init.ora

sql>select sum(decode(name, 'consistent gets',value,0)) "consistent gets",

sum(decode(name,'db block gets',value,0)) "db block gets",

sum(decode(name,'physical reads',value,0)) " physical reads",

round((sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0)) - sum(decode(name,'physical reads',value,0))) /

(sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0))) * 100,2) "Hit Ratio" from v$sysstat;

Data Dict Hit Ratio :-

• Gets - Total number of requests for information on the data object.
• Cache Misses -- Number of data requests resulting in cache misses.
• Hit ratio should be > 90%,else increse Shared_pool_size in init.ora

Sql> Select sum(gets),sum(getmisses), round((1-(sum(getmisses)/sum(gets))) * 100,2) from v$rowcache;

Library Cache Miss Ratio :-

• Executions - The number of times a pin was requested for objects of this namespace.
• Cache misses - any pin of an object that is not the first pin performed since the object handle was created and which requires loading the object from disk.
• Hit ratio should be <>

Sql> Select sum(pins) executions,sum(reloads) cache_misses,sum(reloads) / sum(pins) miss_ratio from v$librarycache;