Tuesday, December 29, 2009

BACKUP AND RECOVERY TECHNIQUES IN ORACLE 10G

Recover database from loss of control file in oracle 10g:


We may face problem with loss of control files and the following method can be emploed to restore/recreate control file:

if control files are already multiplexed and few of those are available, then copy a right/good state of control file over a damaged/missing one. ie.. copy surviving control file with copy command giving missing control filename for new control file and open database or edit the parameter file to remove the reference to the missing or damaged controld file, if db SPFILE being used then use alter system set control_files="avaliable controlfiles path" scope=spfile; and startup

Example:

alter system set control_files='/control01.ctl',
'/control02.ctl',
'/control03.ctl' scope=spfile
startup

Note: Any change to control_files requires db restart

if no control files are available then follow below method.

startup nomount;
SHOW PARAMETER CONTROL


SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS NOARCHIVELOG
2 NOARCHIVELOG
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 10
7 MAXLOGHISTORY 10000
8 LOGFILE
9 GROUP 1 'F:\oracle\product\10.2.0\oradata\MYDB\REDO01.LOG' SIZE
10 100M,
11 GROUP 2 'F:\oracle\product\10.2.0\oradata\MYDB\REDO02.LOG' SIZE
12 100M,
13 GROUP 3 'F:\oracle\product\10.2.0\oradata\MYDB\REDO03.LOG' SIZE
14 100M
15 DATAFILE
16 'F:\oracle\product\10.2.0\oradata\MYDB\SYSTEM01.DBF',
17 'F:\oracle\product\10.2.0\oradata\MYDB\SYSAUX01.DBF',
18 'F:\oracle\product\10.2.0\oradata\MYDB\EXAMPLE01.DBF',
19 'F:\oracle\product\10.2.0\oradata\MYDB\UNDOTBS01.DBF',
20 'F:\oracle\product\10.2.0\oradata\MYDB\USERS01.DBF'
21 CHARACTER SET UTF8
22 ;

Control file created.

Note:
check alert_sid.log file for character set incase if you dont have an idea what character set of db is..
alert_sid.log default location is background_dump_dest (show parameter background_dump_dest)
RESETLOGS synchronizes the SCN between the database files and control files and redo log files and oracle redo log files will be recreated when we open with resetlogs option.

SQL> SHOW PARAMETER CONTROL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:\ORACLE\PRODUCT\10.2.0\ORADA
TA\MYDB\CONTROL01.CTL, F:\ORAC
LE\PRODUCT\10.2.0\ORADATA\MYDB
\CONTROL02.CTL, F:\ORACLE\PROD
UCT\10.2.0\ORADATA\MYDB\CONTRO
L03.CTL
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> DESC V$CONTROLFILE;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER

SQL> SELECT NAME, STATUS FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
STATUS
-------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL
SQL>


SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL>

Create temporary tablespace:

It may be necessary to add files to these tablespaces. That can be done using the SQL statement:

ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF' REUSE;
SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF'
REUSE;

Tablespace altered.

SQL> select file#,status,enabled,name from V$tempfile;

FILE# STATUS ENABLED
---------- ------- ----------
NAME
--------------------------------------------------------------------------------
1 ONLINE READ WRITE
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Note:

1. multiplex Control files and Redo log files.
2. Backup of the database regularly with either RMAN or in traditional method.
3. execute alter database backup controlfile to trace; after each change to the database, USE alter session set tracefile_identifier before you take control file backup to have meaning full name. check user_dump_dest (show parameter user_dump_dest as sys)


how to multiplex control files in oracle 10g:

steps:
1. shutdown immediate;
2. copy existing controld file to new control file.
cp existingcontrolfile.ctl newcontrolfile.ctl
3. startup nomount
4. show parameter control
5.
alter system set control_files='F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL04.CTL' scope=spfile

6. startup force;
7. select name from v$controlfile;
8. show parameter control;

How to multiplex Online redo log files

1. select * from v$log;
2. select * from v$logfile;
if there is one member in each group, then it is the time to multiplex redo log files.. the minimum is that each group at least consist two members i.e. not less than 2 members in each group.
3. alter system swith logfile; -- do this couple of times till you find all the groups are gone through it.

4. Adding A New Member To An Existing Group

alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO01B.LOG' TO GROUP
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO2B.LOG' TO GROUP 2
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO3B.LOG' TO GROUP 3

5. SELECT GROUP#,MEMBER FROM V$LOGFILE -- To see the logfile members
select group#, members, status from v$log; -- To see log group level.

Optimal size of redo log files can be find using

select optimal_logfile_size from v$instance_recovery;


size of redo log files:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

select group#,sum(bytes)/1024/1024 "Meg" from sys.v_$log group by group#;

it gives u size of each redo log file from each group... not all the redo log files size if group has more than 1 redo log files...



very nice post on redo log files addition or deletion can be seen at

http://oralog.wordpress.com/2009/12/16/how-to-add-remove-or-relocate-online-redolog-files-and-groups/

Recovering Database from lost multiplexed online redo log files

select group#,status from v$log;
select group#,status,member from v$logfile order by group#
Ensure two members at least there in each group
now to test how we do recover from redo log file is

1. shutdown immediate;
2. remove one of the mulitplexed redo log file either using del on windows or rm on unix
3. startup
database will open normally but there is an error entry in alert log file.. see the details in alert log file.

4. alter system switch logfile; run this more then number of total number of groups.
5. select group#,status,member from v$logfile order by group#
there will an entry with status = invalid.

6. select group#, status from v$log;

ensure that the about to restore group not in active or current i.e. should be inactive state.

7. alter database clear logfile group 1;

which will delete and re-create the members of given logfile group.

8.alter system switch logfile; run this more then number of total number of groups.
9. select group#,status,member from v$logfile order by group#;
run this to find all are in well state...

Backup No Archive Mode oracle 10g Database with RMAN


open command prompt
set ORACLE_SID=ORCL OR export ORACLE_SID=ORCL --- ORCL is sid here

rman target /

run
{
shutdown immediate;
startup mount;
backup as copy database;
alter database open;
}

it would start backup now...

output of this process when default configuration is being used:

it will create a directory with sid i.e. ORCL in this case in flash recovery area
show parameter db_recovery_file_dest
D:\oracle\product\10.2.0\flash_recovery_area\ORCL
Three direcotries will be created automatically
1. backupset -- spfile will be backed up in backup set format
2. controlfile -- where control file being backed up in copy format
3. DATAFILE -- All the dbf files are backed up here....

no temp files , no redo log files are backed up and it is normal...

as last step .. database will be open for ready..

How to restore and recover a Temporary Tablespace:

The first thing to know that the temporary tablespaces and temp files are never backed up and even RMAN do not do the backup of thses and backup of those never needed.

The only way to restore and recover of temparary tablespace/temp file is to recreate when one is missing.
Method :

Add another temp filee:
alter tablespace temp_ts add tempfile '' size 1000M;
take the damaged tempfile offline
alter database tempfile '' offline;
drop the damaged temp file;
alter database tempfile '' drop;

or

create new temp tablespace;
create temporary tablespace temp1 tempfile '' size 1000M;
switch database to use newly created temp tablespace;
alter database default temporary tablespace temp1;
drop damaged temp tablespace;
drop tablespace temp_old including contents and datafiles;

this is fastest method as as file being created is not formated.


.. Hope this will help you all... cheers...

BACKUP AND RECOVERY TECHNIQUES IN ORACLE 10G

Recover database from loss of control file in oracle 10g:


We may face problem with loss of control files and the following method can be emploed to restore/recreate control file:

if control files are already multiplexed and few of those are available, then copy a right/good state of control file over a damaged/missing one. ie.. copy surviving control file with copy command giving missing control filename for new control file and open database or edit the parameter file to remove the reference to the missing or damaged controld file, if db SPFILE being used then use alter system set control_files="avaliable controlfiles path" scope=spfile; and startup

Example:

alter system set control_files='/control01.ctl',
'/control02.ctl',
'/control03.ctl' scope=spfile
startup

Note: Any change to control_files requires db restart

if no control files are available then follow below method.

startup nomount;
SHOW PARAMETER CONTROL


SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS NOARCHIVELOG
2 NOARCHIVELOG
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 10
7 MAXLOGHISTORY 10000
8 LOGFILE
9 GROUP 1 'F:\oracle\product\10.2.0\oradata\MYDB\REDO01.LOG' SIZE
10 100M,
11 GROUP 2 'F:\oracle\product\10.2.0\oradata\MYDB\REDO02.LOG' SIZE
12 100M,
13 GROUP 3 'F:\oracle\product\10.2.0\oradata\MYDB\REDO03.LOG' SIZE
14 100M
15 DATAFILE
16 'F:\oracle\product\10.2.0\oradata\MYDB\SYSTEM01.DBF',
17 'F:\oracle\product\10.2.0\oradata\MYDB\SYSAUX01.DBF',
18 'F:\oracle\product\10.2.0\oradata\MYDB\EXAMPLE01.DBF',
19 'F:\oracle\product\10.2.0\oradata\MYDB\UNDOTBS01.DBF',
20 'F:\oracle\product\10.2.0\oradata\MYDB\USERS01.DBF'
21 CHARACTER SET UTF8
22 ;

Control file created.

Note:
check alert_sid.log file for character set incase if you dont have an idea what character set of db is..
alert_sid.log default location is background_dump_dest (show parameter background_dump_dest)
RESETLOGS synchronizes the SCN between the database files and control files and redo log files and oracle redo log files will be recreated when we open with resetlogs option.

SQL> SHOW PARAMETER CONTROL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:\ORACLE\PRODUCT\10.2.0\ORADA
TA\MYDB\CONTROL01.CTL, F:\ORAC
LE\PRODUCT\10.2.0\ORADATA\MYDB
\CONTROL02.CTL, F:\ORACLE\PROD
UCT\10.2.0\ORADATA\MYDB\CONTRO
L03.CTL
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> DESC V$CONTROLFILE;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER

SQL> SELECT NAME, STATUS FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
STATUS
-------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL
SQL>


SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL>

Create temporary tablespace:

It may be necessary to add files to these tablespaces. That can be done using the SQL statement:

ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF' REUSE;
SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF'
REUSE;

Tablespace altered.

SQL> select file#,status,enabled,name from V$tempfile;

FILE# STATUS ENABLED
---------- ------- ----------
NAME
--------------------------------------------------------------------------------
1 ONLINE READ WRITE
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Note:

1. multiplex Control files and Redo log files.
2. Backup of the database regularly with either RMAN or in traditional method.
3. execute alter database backup controlfile to trace; after each change to the database, USE alter session set tracefile_identifier before you take control file backup to have meaning full name. check user_dump_dest (show parameter user_dump_dest as sys)


how to multiplex control files in oracle 10g:

steps:
1. shutdown immediate;
2. copy existing controld file to new control file.
cp existingcontrolfile.ctl newcontrolfile.ctl
3. startup nomount
4. show parameter control
5.
alter system set control_files='F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL04.CTL' scope=spfile

6. startup force;
7. select name from v$controlfile;
8. show parameter control;

How to multiplex Online redo log files

1. select * from v$log;
2. select * from v$logfile;
if there is one member in each group, then it is the time to multiplex redo log files.. the minimum is that each group at least consist two members i.e. not less than 2 members in each group.
3. alter system swith logfile; -- do this couple of times till you find all the groups are gone through it.

4. Adding A New Member To An Existing Group

alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO01B.LOG' TO GROUP
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO2B.LOG' TO GROUP 2
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO3B.LOG' TO GROUP 3

5. SELECT GROUP#,MEMBER FROM V$LOGFILE -- To see the logfile members
select group#, members, status from v$log; -- To see log group level.

Optimal size of redo log files can be find using

select optimal_logfile_size from v$instance_recovery;


size of redo log files:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

select group#,sum(bytes)/1024/1024 "Meg" from sys.v_$log group by group#;

it gives u size of each redo log file from each group... not all the redo log files size if group has more than 1 redo log files...



very nice post on redo log files addition or deletion can be seen at

http://oralog.wordpress.com/2009/12/16/how-to-add-remove-or-relocate-online-redolog-files-and-groups/

Recovering Database from lost multiplexed online redo log files

select group#,status from v$log;
select group#,status,member from v$logfile order by group#
Ensure two members at least there in each group
now to test how we do recover from redo log file is

1. shutdown immediate;
2. remove one of the mulitplexed redo log file either using del on windows or rm on unix
3. startup
database will open normally but there is an error entry in alert log file.. see the details in alert log file.

4. alter system switch logfile; run this more then number of total number of groups.
5. select group#,status,member from v$logfile order by group#
there will an entry with status = invalid.

6. select group#, status from v$log;

ensure that the about to restore group not in active or current i.e. should be inactive state.

7. alter database clear logfile group 1;

which will delete and re-create the members of given logfile group.

8.alter system switch logfile; run this more then number of total number of groups.
9. select group#,status,member from v$logfile order by group#;
run this to find all are in well state...

Backup No Archive Mode oracle 10g Database with RMAN


open command prompt
set ORACLE_SID=ORCL OR export ORACLE_SID=ORCL --- ORCL is sid here

rman target /

run
{
shutdown immediate;
startup mount;
backup as copy database;
alter database open;
}

it would start backup now...

output of this process when default configuration is being used:

it will create a directory with sid i.e. ORCL in this case in flash recovery area
show parameter db_recovery_file_dest
D:\oracle\product\10.2.0\flash_recovery_area\ORCL
Three direcotries will be created automatically
1. backupset -- spfile will be backed up in backup set format
2. controlfile -- where control file being backed up in copy format
3. DATAFILE -- All the dbf files are backed up here....

no temp files , no redo log files are backed up and it is normal...

as last step .. database will be open for ready..

How to restore and recover a Temporary Tablespace:

The first thing to know that the temporary tablespaces and temp files are never backed up and even RMAN do not do the backup of thses and backup of those never needed.

The only way to restore and recover of temparary tablespace/temp file is to recreate when one is missing.
Method :

Add another temp filee:
alter tablespace temp_ts add tempfile '' size 1000M;
take the damaged tempfile offline
alter database tempfile '' offline;
drop the damaged temp file;
alter database tempfile '' drop;

or

create new temp tablespace;
create temporary tablespace temp1 tempfile '' size 1000M;
switch database to use newly created temp tablespace;
alter database default temporary tablespace temp1;
drop damaged temp tablespace;
drop tablespace temp_old including contents and datafiles;

this is fastest method as as file being created is not formated.


.. Hope this will help you all... cheers...

Happy New Year 2010


** Happy New Year 2010 **
Copy this onto notepad, press cntrl + H, enter 6 in the find box and underscore (_) in replace box and click replace all button. Try this..




666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666996666699669966999999996699666669966666669966666699669999999966996666996666666666666696666666666666666666666
666996666699669966999999996699666669966666666996666996669999999966996666996666666666666999666666666666666666666
666996666699669966996666666699666669966666666699669966669966669966996666996666666666669969966666666666666666666
666996696699669966999999996699999999966666666669999666669966669966996666996666666666699666996666666666666666666
666996999699669966999999996699999999966666666666996666669966669966996666996666666666999999999666666666666666666
666999969999669966666666996699666669966666666666996666669966669966996666996666666669999999999966666666666666666
666999666999669966999999996699666669966666666666996666669999999966996666996666666699666666666996666666666666666
666996666699669966999999996699666669966666666666996666669999999966999999996666666996666666666699666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
669966666996666666669666666666999999996699999999669966666699666666669966666996699999999669966666996666666666666
669966666996666666699966666666999999996699999999666996666996666666669996666996699999999669966666996666666666666
669966666996666666996996666666996666996699666699666699669966666666669999666996699666666669966666996666666666666
669999999996666669966699666666999999996699999999666669999666666666669969966996699999999669966966996666666666666
669999999996666699999999966666999999996699999999666666996666666666669966996996699999999669969996996666666666666
669966666996666999999999996666999666666699666666666666996666666666669966699996699666666669999699996666666666666
669966666996669966666666699666996666666699666666666666996666666666669966669996699999999669996669996666666666666
669966666996699666666666669966996666666699666666666666996666666666669966666996699999999669966666996666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666996666669966999999996666666669666666666999999996666666699999996699999999666999666699999999666666666666666666
666699666699666999999996666666699966666666999999996666666699999999699666699666699666699666699666666666666666666
666669966996666996666666666666996996666666996666996666666666666699699966699666699666699966699666666666666666666
666666999966666999999996666669966699666666999999996666666666666996699696699666699666699696699666666666666666666
666666699666666999999996666699999999966666999999996666666666669966699669699666699666699669699666666666666666666
666666699666666996666666666999999999996666996996666666666666996666699666999666699666699666999666666666666666666
666666699666666999999996669966666666699666996699666666666699999999699666699666699666699666699666666666666666666
666666699666666999999996699666666666669966996666996666666699999999699999999666999966699999999666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666

Wish you all A Happy New Year 2010 .



...Cheers...

Happy New Year 2010


** Happy New Year 2010 **
Copy this onto notepad, press cntrl + H, enter 6 in the find box and underscore (_) in replace box and click replace all button. Try this..




666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666996666699669966999999996699666669966666669966666699669999999966996666996666666666666696666666666666666666666
666996666699669966999999996699666669966666666996666996669999999966996666996666666666666999666666666666666666666
666996666699669966996666666699666669966666666699669966669966669966996666996666666666669969966666666666666666666
666996696699669966999999996699999999966666666669999666669966669966996666996666666666699666996666666666666666666
666996999699669966999999996699999999966666666666996666669966669966996666996666666666999999999666666666666666666
666999969999669966666666996699666669966666666666996666669966669966996666996666666669999999999966666666666666666
666999666999669966999999996699666669966666666666996666669999999966996666996666666699666666666996666666666666666
666996666699669966999999996699666669966666666666996666669999999966999999996666666996666666666699666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
669966666996666666669666666666999999996699999999669966666699666666669966666996699999999669966666996666666666666
669966666996666666699966666666999999996699999999666996666996666666669996666996699999999669966666996666666666666
669966666996666666996996666666996666996699666699666699669966666666669999666996699666666669966666996666666666666
669999999996666669966699666666999999996699999999666669999666666666669969966996699999999669966966996666666666666
669999999996666699999999966666999999996699999999666666996666666666669966996996699999999669969996996666666666666
669966666996666999999999996666999666666699666666666666996666666666669966699996699666666669999699996666666666666
669966666996669966666666699666996666666699666666666666996666666666669966669996699999999669996669996666666666666
669966666996699666666666669966996666666699666666666666996666666666669966666996699999999669966666996666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
666996666669966999999996666666669666666666999999996666666699999996699999999666999666699999999666666666666666666
666699666699666999999996666666699966666666999999996666666699999999699666699666699666699666699666666666666666666
666669966996666996666666666666996996666666996666996666666666666699699966699666699666699966699666666666666666666
666666999966666999999996666669966699666666999999996666666666666996699696699666699666699696699666666666666666666
666666699666666999999996666699999999966666999999996666666666669966699669699666699666699669699666666666666666666
666666699666666996666666666999999999996666996996666666666666996666699666999666699666699666999666666666666666666
666666699666666999999996669966666666699666996699666666666699999999699666699666699666699666699666666666666666666
666666699666666999999996699666666666669966996666996666666699999999699999999666999966699999999666666666666666666
666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666

Wish you all A Happy New Year 2010 .



...Cheers...

Sunday, December 6, 2009

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:


The TIMESTAMP WITH TIME ZONE data type data is normalized to the database time zone when it is saved into the database and users see the data in their local session time zone when they select from it.

TIMESTAMP WITH TIME ZONE: The data will not be normalized when data is being saved into database but it has time zone indicator to which refers.

All other DATE, TIMESTAMP data type’s data is normalized to DB time zone when it is saved and data remain unchanged when selected.


NLS_DATE_FORMAT and NLS_TERRITORY parameter values can be used to format the data in desired format.


Difference between Schema and User in oracle

A schema is a collection of database objects, schema is owned by a database user and has same name as that user. By default user has all the access on its own schema objects and other schemas objects based on the grants been granted.
There is 1 to 1 relationship between schema and user.

...

..

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:

Difference between TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE in oracle 10g:


The TIMESTAMP WITH TIME ZONE data type data is normalized to the database time zone when it is saved into the database and users see the data in their local session time zone when they select from it.

TIMESTAMP WITH TIME ZONE: The data will not be normalized when data is being saved into database but it has time zone indicator to which refers.

All other DATE, TIMESTAMP data type’s data is normalized to DB time zone when it is saved and data remain unchanged when selected.


NLS_DATE_FORMAT and NLS_TERRITORY parameter values can be used to format the data in desired format.


Difference between Schema and User in oracle

A schema is a collection of database objects, schema is owned by a database user and has same name as that user. By default user has all the access on its own schema objects and other schemas objects based on the grants been granted.
There is 1 to 1 relationship between schema and user.

...

..

Thursday, December 3, 2009

How to display background processes,remote, local user processes in oracle 10g database.

How to display oracle background processes,remote, local user processes in oracle 10g database at shell command prompt:

Occasionally we may require to see the background processes, remote, local users processes started against oracle database in server system and the way to find those is below.

The way to find is different from unix systems to windows system, but in unix it is just running PS commond and which shows everything.

On unix , use the ps command to display them.

ps -ef |grep ora10g -- ora10g is the databasename or SID

you would see many ora__ora10g, these are all background processes.

How to see remote user processes:

Any process ID that include (LOCAL=NO) ,which is an indication that the server process is servicing a remote user process.

How to see local user processes:

Any process ID that has (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beg))), which is an indication that the user process is running on the same db server machine which means it did not come via listener.

On windows, server processes are threads within the single oracle.exe process and can be seen within windows task manager.

The following query can be used to list out all the processes/sessions started in database.

SELECT ses.sid,ses.serial#,pro.spid,ses.osuser,ses.type,
ses.username,
ses.program
FROM v$process pro,
v$session ses
WHERE pro.addr = ses.paddr;

Note: there is no username shown for backgrund processs and it is normal.

The best way to kill sessions which I feel should be incase of heavy load on the dB system very rarley or if any user request dba to do so most of the cases in development environment, the best way i see is to login into dB system and kill those using
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- best way
OR
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

KILLING ORACLE USER PROCESSES AT UNIX COMMAND PROMPT:
oracle spid can be found using ps -ef | grep ora command
$ kill spid

IN WINDOWS COMMAND PROMPT:

orakill sid spid

sid,Spid can be taken from running above select statement.
Note: however killing oracle sessions at OS command prompt is NOT suggestable as it may crash database systems.. so please avoid it all the time... always login into dba sql plus command prompt to do dba activities...

-- cheers...

How to display background processes,remote, local user processes in oracle 10g database.

How to display oracle background processes,remote, local user processes in oracle 10g database at shell command prompt:

Occasionally we may require to see the background processes, remote, local users processes started against oracle database in server system and the way to find those is below.

The way to find is different from unix systems to windows system, but in unix it is just running PS commond and which shows everything.

On unix , use the ps command to display them.

ps -ef |grep ora10g -- ora10g is the databasename or SID

you would see many ora__ora10g, these are all background processes.

How to see remote user processes:

Any process ID that include (LOCAL=NO) ,which is an indication that the server process is servicing a remote user process.

How to see local user processes:

Any process ID that has (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beg))), which is an indication that the user process is running on the same db server machine which means it did not come via listener.

On windows, server processes are threads within the single oracle.exe process and can be seen within windows task manager.

The following query can be used to list out all the processes/sessions started in database.

SELECT ses.sid,ses.serial#,pro.spid,ses.osuser,ses.type,
ses.username,
ses.program
FROM v$process pro,
v$session ses
WHERE pro.addr = ses.paddr;

Note: there is no username shown for backgrund processs and it is normal.

The best way to kill sessions which I feel should be incase of heavy load on the dB system very rarley or if any user request dba to do so most of the cases in development environment, the best way i see is to login into dB system and kill those using
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- best way
OR
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

KILLING ORACLE USER PROCESSES AT UNIX COMMAND PROMPT:
oracle spid can be found using ps -ef | grep ora command
$ kill spid

IN WINDOWS COMMAND PROMPT:

orakill sid spid

sid,Spid can be taken from running above select statement.
Note: however killing oracle sessions at OS command prompt is NOT suggestable as it may crash database systems.. so please avoid it all the time... always login into dba sql plus command prompt to do dba activities...

-- cheers...

Thursday, November 26, 2009

ORA-29855 error in oracle and solution

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code


- wish this helps you too..

ORA-29855 error in oracle and solution

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code


- wish this helps you too..

Wednesday, November 11, 2009

Job scheduling in oracle 10g

The DBMS_JOB, DBMS_SCHEDULER packages are installed when the Oracle database is installed. The dbmsjob.sql script has source code of DBMS_JOB. In Oracle 10g the DBMS_JOB package functionality is replaced and/or enhanced by the DBMS_SCHEDULER package. The DBMS_JOB package is depricated and provided for backward compatibility only.

'CREATE JOB' privilege is required to create and run jobs.

There are vaious methods to do job scheduling in oracle 10g but the simple method of scheduling a job is as follows. ( I used couple of times hence thought good to post here)..


create or replace package pkg1
as
procedure makeentry;
end pkg1;
/

create or replace package body pkg1
as
procedure makeentry
is
begin
insert into test1 values (to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') );
commit;
end;
end;


BEGIN
dbms_scheduler.create_job(
job_name => 'makeentry',
job_type => 'STORED_PROCEDURE',
job_action => 'PKG1.MAKEENTRY',
repeat_interval => 'FREQ=DAILY;BYHOUR=13;BYMINUTE=0;BYSECOND=0',
comments => 'cleanup job runs every day at 1 pm',
enabled=>true);
END;


=============Notes==============================

Specifying Intervals

FREQ takes YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
FREQ=DAILY; INTERVAL=7 executes a job every 7 days
FREQ=HOURLY; INTERVAL=2 executes a job every other hour
FREQ=WEEKLY; BYDAY=FRI executes a job every Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI executes a job every other Friday.
FREQ=MONTHLY; BYMONTHDAY=1 executes a job on the last day of the month
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31 executes a job on the 31st of December.
FREQ=MONTHLY; BYDAY=2FRI executes a job every second Friday of the month

.. Hope this help you as well.....

Job scheduling in oracle 10g

The DBMS_JOB, DBMS_SCHEDULER packages are installed when the Oracle database is installed. The dbmsjob.sql script has source code of DBMS_JOB. In Oracle 10g the DBMS_JOB package functionality is replaced and/or enhanced by the DBMS_SCHEDULER package. The DBMS_JOB package is depricated and provided for backward compatibility only.

'CREATE JOB' privilege is required to create and run jobs.

There are vaious methods to do job scheduling in oracle 10g but the simple method of scheduling a job is as follows. ( I used couple of times hence thought good to post here)..


create or replace package pkg1
as
procedure makeentry;
end pkg1;
/

create or replace package body pkg1
as
procedure makeentry
is
begin
insert into test1 values (to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') );
commit;
end;
end;


BEGIN
dbms_scheduler.create_job(
job_name => 'makeentry',
job_type => 'STORED_PROCEDURE',
job_action => 'PKG1.MAKEENTRY',
repeat_interval => 'FREQ=DAILY;BYHOUR=13;BYMINUTE=0;BYSECOND=0',
comments => 'cleanup job runs every day at 1 pm',
enabled=>true);
END;


=============Notes==============================

Specifying Intervals

FREQ takes YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
FREQ=DAILY; INTERVAL=7 executes a job every 7 days
FREQ=HOURLY; INTERVAL=2 executes a job every other hour
FREQ=WEEKLY; BYDAY=FRI executes a job every Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI executes a job every other Friday.
FREQ=MONTHLY; BYMONTHDAY=1 executes a job on the last day of the month
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31 executes a job on the 31st of December.
FREQ=MONTHLY; BYDAY=2FRI executes a job every second Friday of the month

.. Hope this help you as well.....

Friday, September 25, 2009

Default port numbers of various databases

Default port numbers of various databases:

Often we wonder what is the default port number of various databases that we work excessively on since we don’t keep in touch with these stuff often, hence I thought it would be good idea to post that stuff here for quick review when required rather checking here and there.
(There are just too many things to remember, and the things we don't use everyday get lost in brain cells.)

What is the default port number of Oracle Database:

1521

The more information can be seen in listener.ora (server) and tnsnames.ora (client & server) files.

Other database utilities port numbers:
The port number of iSQLPLUS and EM control etc can been seen in /ORACLE_HOME/install/portlist.ini file.

usually EM control port number is 1158 or 5500
http://hp-pc:1158/em

iSQL*Plus number =5560
http://<>:5560/isqlplus

DBA iSQL *Plus access:
http://hp-pc:5560/isqlplus/dba

What is the default port number of MySQL database:

3306

Command to know whether port is listening with netstat -ln | grep mysql

What is the default port number of MSSQL 2005 Database:

1433

the port numbers are remain intact unless one change.


Cheers.......

Default port numbers of various databases

Default port numbers of various databases:

Often we wonder what is the default port number of various databases that we work excessively on since we don’t keep in touch with these stuff often, hence I thought it would be good idea to post that stuff here for quick review when required rather checking here and there.
(There are just too many things to remember, and the things we don't use everyday get lost in brain cells.)

What is the default port number of Oracle Database:

1521

The more information can be seen in listener.ora (server) and tnsnames.ora (client & server) files.

Other database utilities port numbers:
The port number of iSQLPLUS and EM control etc can been seen in /ORACLE_HOME/install/portlist.ini file.

usually EM control port number is 1158 or 5500
http://hp-pc:1158/em

iSQL*Plus number =5560
http://<>:5560/isqlplus

DBA iSQL *Plus access:
http://hp-pc:5560/isqlplus/dba

What is the default port number of MySQL database:

3306

Command to know whether port is listening with netstat -ln | grep mysql

What is the default port number of MSSQL 2005 Database:

1433

the port numbers are remain intact unless one change.


Cheers.......

Wednesday, July 8, 2009

Performance tuning in oracle 10g database

Performance tuning in oracle 10g database:

Current sessions and their activity in oracle 10g:

The following query statement can be used to disaply all the current sessions in current instance/db:

-- GV$INSTANCE view for RAC database
--- v$instance for single instance database

select sid, serial#, username, status, osuser,process, machine, program,type,module, event,
decode(command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
command||': Other') command
from v$session
where username is not null

How to find Oracle Buffer Cache Hit Ratio:

Here are the few sql statements that can be used whether oracle database is functioning well or not, however buffer cache hit ratio is an indicator and can not be used as a prime factor when tuning database, one has to look more into oracle wait events and V$DB_CACHE_ADVICE view data before tuning buffer chache.
The following statement can be used to find buffer cache hit ratio in oracle 10g.

select
100*(1 - (v3.value / (v1.value + v2.value))) "Buffer Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets from cache' and
v2.name = 'consistent gets from cache' and
v3.name = 'physical reads cache'

There will not be much use of increasing buffer cache in case one doing full table scans or operations that are not using the buffer cache. Running oracle database under automatic memory management mode is best option.

The following sql stements list the sql statements that are waiting for some action to be completed and/or waiting for some resource to be available or currently running in instance. the text that is given below is simple and I feel it can help us to find status of running statements with wait events details

Oracle Library Cache Hit Ratio:

Here is the simple query
to find oracle library cache hit ratio, the more hit ratio you see the more efficiently the library cache is being used and should be more than 96% or even 99%

select sum(pinhits)/sum(pins)
,100* (sum(pinhits)/sum(pins) ) "Library Cache Hit Ratio" from v$librarycache

The best way I feel to decide whether to increase library cache allocated memory or not is based on reloads column of v$librarycache view. the values should be near to 0.

shared_pool_size is what we need to increase in case required to do so for library cache.

HOW TO FIND FREE MEMORY IN SGA:

The amount of free memory can be find using v$sgastat view and here is the simple query to find SGA free memory

select * from v$sgastat where name like '%free memory%'

Dictionary Cache hit ratio:

SELECT sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) "Dictionary Cache hit ratio"
FROM V$ROWCACHE
WHERE gets > 0


we do not have to worry in case it returns 0 records as it shows no load on dictionary cache. we have to find at parameter level in case hit ration is not acceptable and once can find by grouping parameter column of v$rowache to zero on exact problems and able to see in
granularity level.

Redo Log Buffer configuration:

The following statement tell us whether redo log buffer is well adequate or not, however one has to check how check point and archiving taking place in addition to this when one plan to increase redo log buffer size. the parameter used for redo log buffer is LOG_BUFFER



select name, value from v$sysstat where NAME in
('redo buffer allocation retries',
'redo log space wait time')
/
the values should be near to zero and one has to check when peak load on database system and it is static parameter i.e. required instance startup.

redo buffer allocation retries is the number of times user process waited for redo log buffer space.
redo log space wait time is the total time waited by all the process for redo log buffer space.

the value should be near to zero for best performance otherwise one has to check checkpointing and archiving before one can go ahead with increasing log_buffer size.


Find the running queries SQL statements including events

select
substr(a.spid,1,12) pid,
substr(b.sid,1,6) sid,
substr(b.serial#,1,6) serial#,
substr(b.machine,1,6) machine,
substr(b.username,1,8) username,
b.STATUS,b.state,b.EVENT,
substr(b.osuser,1,12) os_user,
substr(b.program,1,30) program,
b.sql_id
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and lower(event) not like '%sql*net%'
order by spid

we get all the sql stetements that we are interest of and can be list what exactly being executed in db with following query statement.

select sql_text from v$sql where sql_id='sql_id from above query'

once we get sql statements the are waiting for events to be completed, we can employ explain plan or sqltrace utitilies to tune the query or can go further to find what exactly cuasing wait events to appear in instance/db.

-- more info will come soon--

Performance tuning in oracle 10g database

Performance tuning in oracle 10g database:

Current sessions and their activity in oracle 10g:

The following query statement can be used to disaply all the current sessions in current instance/db:

-- GV$INSTANCE view for RAC database
--- v$instance for single instance database

select sid, serial#, username, status, osuser,process, machine, program,type,module, event,
decode(command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
command||': Other') command
from v$session
where username is not null

How to find Oracle Buffer Cache Hit Ratio:

Here are the few sql statements that can be used whether oracle database is functioning well or not, however buffer cache hit ratio is an indicator and can not be used as a prime factor when tuning database, one has to look more into oracle wait events and V$DB_CACHE_ADVICE view data before tuning buffer chache.
The following statement can be used to find buffer cache hit ratio in oracle 10g.

select
100*(1 - (v3.value / (v1.value + v2.value))) "Buffer Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets from cache' and
v2.name = 'consistent gets from cache' and
v3.name = 'physical reads cache'

There will not be much use of increasing buffer cache in case one doing full table scans or operations that are not using the buffer cache. Running oracle database under automatic memory management mode is best option.

The following sql stements list the sql statements that are waiting for some action to be completed and/or waiting for some resource to be available or currently running in instance. the text that is given below is simple and I feel it can help us to find status of running statements with wait events details

Oracle Library Cache Hit Ratio:

Here is the simple query
to find oracle library cache hit ratio, the more hit ratio you see the more efficiently the library cache is being used and should be more than 96% or even 99%

select sum(pinhits)/sum(pins)
,100* (sum(pinhits)/sum(pins) ) "Library Cache Hit Ratio" from v$librarycache

The best way I feel to decide whether to increase library cache allocated memory or not is based on reloads column of v$librarycache view. the values should be near to 0.

shared_pool_size is what we need to increase in case required to do so for library cache.

HOW TO FIND FREE MEMORY IN SGA:

The amount of free memory can be find using v$sgastat view and here is the simple query to find SGA free memory

select * from v$sgastat where name like '%free memory%'

Dictionary Cache hit ratio:

SELECT sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) "Dictionary Cache hit ratio"
FROM V$ROWCACHE
WHERE gets > 0


we do not have to worry in case it returns 0 records as it shows no load on dictionary cache. we have to find at parameter level in case hit ration is not acceptable and once can find by grouping parameter column of v$rowache to zero on exact problems and able to see in
granularity level.

Redo Log Buffer configuration:

The following statement tell us whether redo log buffer is well adequate or not, however one has to check how check point and archiving taking place in addition to this when one plan to increase redo log buffer size. the parameter used for redo log buffer is LOG_BUFFER



select name, value from v$sysstat where NAME in
('redo buffer allocation retries',
'redo log space wait time')
/
the values should be near to zero and one has to check when peak load on database system and it is static parameter i.e. required instance startup.

redo buffer allocation retries is the number of times user process waited for redo log buffer space.
redo log space wait time is the total time waited by all the process for redo log buffer space.

the value should be near to zero for best performance otherwise one has to check checkpointing and archiving before one can go ahead with increasing log_buffer size.


Find the running queries SQL statements including events

select
substr(a.spid,1,12) pid,
substr(b.sid,1,6) sid,
substr(b.serial#,1,6) serial#,
substr(b.machine,1,6) machine,
substr(b.username,1,8) username,
b.STATUS,b.state,b.EVENT,
substr(b.osuser,1,12) os_user,
substr(b.program,1,30) program,
b.sql_id
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and lower(event) not like '%sql*net%'
order by spid

we get all the sql stetements that we are interest of and can be list what exactly being executed in db with following query statement.

select sql_text from v$sql where sql_id='sql_id from above query'

once we get sql statements the are waiting for events to be completed, we can employ explain plan or sqltrace utitilies to tune the query or can go further to find what exactly cuasing wait events to appear in instance/db.

-- more info will come soon--

Wednesday, June 17, 2009

The different types of joins in oracle database

The different types of joins in oracle database:
Joins are used to produce the single data set output from multiple tables based on requirement to get matched records or mismatched records.
the different types of joins we can see in oracle database and one can write joins in either ANSI/ISO or SQL92 standard and both will work on 9i later version of oracle database systems.

I found interesting in writing of join conditions as joins drives the entire query efficient execution and returns output in best optimized way. one can use join concepts in best possible way to get desired results in faster way.

These are the joins one can see oracle database and will explain one by one.

Self joins
Inner joins
Equi-joins
non equi-joins
Outer joins
Cross joins
Anti-joins
Semi-joins

Self Joins:
As we know it is a joining of table itself and we can say table is joined or compared to itself. This table name appears twice in the FROM clause of select statement and followed by table aliases to qualify the column names in the join condition.

Example:


Inner join:
An inner join is a join of more than two tables that returns all those rows that satisfy the given join condition.

Equi-joins

non equi-joins

Outer joins

Cross joins

Anti-joins

Semi-joins

-- more yet to come...
----- Have a fun in working in Oracle Technologies-- Cheers--

The different types of joins in oracle database

The different types of joins in oracle database:
Joins are used to produce the single data set output from multiple tables based on requirement to get matched records or mismatched records.
the different types of joins we can see in oracle database and one can write joins in either ANSI/ISO or SQL92 standard and both will work on 9i later version of oracle database systems.

I found interesting in writing of join conditions as joins drives the entire query efficient execution and returns output in best optimized way. one can use join concepts in best possible way to get desired results in faster way.

These are the joins one can see oracle database and will explain one by one.

Self joins
Inner joins
Equi-joins
non equi-joins
Outer joins
Cross joins
Anti-joins
Semi-joins

Self Joins:
As we know it is a joining of table itself and we can say table is joined or compared to itself. This table name appears twice in the FROM clause of select statement and followed by table aliases to qualify the column names in the join condition.

Example:


Inner join:
An inner join is a join of more than two tables that returns all those rows that satisfy the given join condition.

Equi-joins

non equi-joins

Outer joins

Cross joins

Anti-joins

Semi-joins

-- more yet to come...
----- Have a fun in working in Oracle Technologies-- Cheers--

Tuesday, June 16, 2009

Revoking oracle directory creation permission from a user in oracle

Revoking oracle directory creation permissions from a user in oracle:

CREATE ANY DIRECTORY usually useful when one want to create oracle directories in schema level, however it is found that one can create oracle directories and drop any directories without having create any directory system privileges in case schema has either imp_full_database or exp_full_database, bit surprising but may be it has been included in the view of impdp and expdp point of view. however when schema is not supposed to create any directories then we must check whether it has above said privileges to avoid misuse of oracle directories and misuse of directory concept when user supposed to exp or imp of his own user data..

hopefully this info may help in tightening security where security is mandatory.

Revoking oracle directory creation permission from a user in oracle

Revoking oracle directory creation permissions from a user in oracle:

CREATE ANY DIRECTORY usually useful when one want to create oracle directories in schema level, however it is found that one can create oracle directories and drop any directories without having create any directory system privileges in case schema has either imp_full_database or exp_full_database, bit surprising but may be it has been included in the view of impdp and expdp point of view. however when schema is not supposed to create any directories then we must check whether it has above said privileges to avoid misuse of oracle directories and misuse of directory concept when user supposed to exp or imp of his own user data..

hopefully this info may help in tightening security where security is mandatory.

Thursday, June 11, 2009

MD 50 and MD 70 Documents - oracle application development

Some technical jargon of oracle -apps- process documents in implementation:

What are MD 50 and MD 70 Documents?

AIM (Oracle Application Implementation Methodology)

I have come across in my career with following documents and would like to have information here for future references

The documents explained here are mainly used in oracle development which consists of SQL, PLSQL, Forms, Reports, Graphics -Oracle Developer suits development. I used these docs quite some time back and I liked these most as I found information on existing components and understanding of user requirement in better way. It was excitement for me to make this MD 70 documents in initial days of my career.

Yes, there are other documents that are known as

BR Documents: Business Requirement Documents -Personally not used

MD Documents: Modular Designing Documents - these are usually based on BR 120 etc

MD50: This document holds the functional specifications that are required for component to be developed and it is meant for Technical guys or programmers to understand the functional requirements for customization or design/implementations of software artifacts. Usually Functional guy will be made this.

MD60: Module expert would create MD60 containing the requirements for the problem or the solution to be developed.

MD70: Technical guy or Programmer would create MD70 which holds design solutions for the requirements or component solution to be designed. This document would have all the technical stuff, sql, plsql code ,forms, reports etc .. for reuse purpose and process adherence purpose.


If any one of you know more info on this, please add comments here..


cheers..

MD 50 and MD 70 Documents - oracle application development

Some technical jargon of oracle -apps- process documents in implementation:

What are MD 50 and MD 70 Documents?

AIM (Oracle Application Implementation Methodology)

I have come across in my career with following documents and would like to have information here for future references

The documents explained here are mainly used in oracle development which consists of SQL, PLSQL, Forms, Reports, Graphics -Oracle Developer suits development. I used these docs quite some time back and I liked these most as I found information on existing components and understanding of user requirement in better way. It was excitement for me to make this MD 70 documents in initial days of my career.

Yes, there are other documents that are known as

BR Documents: Business Requirement Documents -Personally not used

MD Documents: Modular Designing Documents - these are usually based on BR 120 etc

MD50: This document holds the functional specifications that are required for component to be developed and it is meant for Technical guys or programmers to understand the functional requirements for customization or design/implementations of software artifacts. Usually Functional guy will be made this.

MD60: Module expert would create MD60 containing the requirements for the problem or the solution to be developed.

MD70: Technical guy or Programmer would create MD70 which holds design solutions for the requirements or component solution to be designed. This document would have all the technical stuff, sql, plsql code ,forms, reports etc .. for reuse purpose and process adherence purpose.


If any one of you know more info on this, please add comments here..


cheers..

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

How to extract records that starts Only with Alphabets and Numerical with Regular Expressions in Oracle

How to extract records that starts with only Alphabets and Numerical & both with Regular Expressions in Oracle:

Occasionally we may required to display records having only numerical numbers or only alphabets data in a particular column and here are the few simple tips and tricks to use, thanks to Regular Expressions concept in oracle as we do a lot with this concept and few of those are here with simple examples…

the example table structure is

SQL> desc test1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 VARCHAR2(15)


List the records having column data with only Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:alpha:]]+$');

COL1
---------------
thisisatestdata

List the records having column data with only Numerical/digits

SQL> select * from test1 where REGEXP_LIKE(col1,'^[[:digit:]]+$');

COL1
---------------
1234456789

List the records starting with Numerical in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'^[0-9]+');

COL1
---------------
1234456789
123end

List the records starting with Alphabets in particular column but not with Numericals

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'^[a-z]+');

COL1
---------------
thisisatestdata
youand144mereers
end123
Or
select * from test1 where REGEXPR_LIKE(col1,'^[a-z]+','i');
few match parameters:
'c' - case sensitive
'i'- case insensitive
'x'-ignore whitespace chars
and 'm', 'n' ... etc..


List the records ending with Numerical numbers in particular column but not with Alphabets

SQL> select * from test1 where REGEXP_LIKE(col1,'[0-9]$');

COL1
---------------
1234456789
end123

2 rows selected.

List the records ending with Alphabets in particular column but not with Numerical numbers

SQL> select * from test1 where REGEXP_LIKE(lower(col1),'[a-z]$');

COL1
---------------
thisisatestdata
youand144mereers
123end

------ A real time example -----

We got a requirement few days back to know the internal trainee count and external trainee count and we did come up with an efficient select statement having regular expression in it. The key point to differentiate trainee is that the internal trainee identification number starts with number and external trainee start with alphabets.

Here is the query

SELECT TO_CHAR(COMPL_DTE,'YYYY-MM') AS COMPLETION_MONTH, COUNT(stud_id) AS COUNT_,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[0-9]+')
THEN stud_id
ELSE NULL
END
) AS INTRNL
,
COUNT(
CASE WHEN REGEXP_LIKE (stud_id, '^[a-z]+')
THEN stud_id
ELSE NULL
END
) AS EXTRNAL
FROM ORACLE_TABLE
GROUP BY
TO_CHAR(DTE,'YYYY-MM');



A lot more examples will come soon on Regular Expressions concepts...

.. cheers... hope this will help..

Tuesday, June 9, 2009

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;
-- returns the IP address of client machine from which connection is established

few more..
select USERENV('LANGUAGE') ,
USERENV('ISDBA') from dual;
..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;
-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...

Identifying ORACLE DB Host Name and its IP Address

Identifying ORACLE DB Host Name and its IP Address including client IP address (from which client is connected):

SYS_CONTEXT function returns the current session information:

There are various ways to find db name to which connection is made are as follows:

select sys_context('userenv','db_name') from dual;
select ora_database_name from dual;
select global_name from global_name;

select sys_context('USERENV','IP_ADDRESS' ) FROM DUAL;

-- returns the IP address of client machine from which connection is established

few more..
select USERENV('LANGUAGE')  ,
USERENV('ISDBA') from dual;

..etc..

We can find local and remote hosts information using UTL_INADDR package in oracle 10g.
more info just type desc UTL_INADDR

SELECT UTL_INADDR.get_host_address from dual;

-- returns the IP address of database physical system

there is lot more info we can find with UTL_INADDR and SYS_CONTEXT.

... cheers...

Monday, June 8, 2009

Number of connections established on or hitting oracle listener port

Number of connections established on or hitting oracle listener port:

The easiest way to find the listener(s) running on oracle db system is as follows:

lsnrctl status

it will list all the database names that are running currently.

whats the way to find listener port then?.. here is the way to find using simple command

tnsping service_name

example: tnsping mydb

finally we got host , port and service_name in simple text, we got everything that one can use to make a connection with port

the final query answer and purpose of this post come here that is finding the number of of connections established/hitting oracle listener port
( there are many options, one can opt which is best based on purpose and results)

netstat -an | grep port_number

example: netstat -an | grep 1521

netstat -anp | grep port_number

there are many usages of this command, with which one can find what is running on a particular port or how many connections established etc ..

(looking more info on netstat, the easiest way to find is man netstat )

Hope this will help you .. or anything missed out here that one feel to add.. so why wait, make a comment now.. :)

Number of connections established on or hitting oracle listener port

Number of connections established on or hitting oracle listener port:

The easiest way to find the listener(s) running on oracle db system is as follows:

lsnrctl status

it will list all the database names that are running currently.

whats the way to find listener port then?.. here is the way to find using simple command

tnsping service_name

example: tnsping mydb

finally we got host , port and service_name in simple text, we got everything that one can use to make a connection with port

the final query answer and purpose of this post come here that is finding the number of of connections established/hitting oracle listener port
( there are many options, one can opt which is best based on purpose and results)

netstat -an | grep port_number

example: netstat -an | grep 1521

netstat -anp | grep port_number

there are many usages of this command, with which one can find what is running on a particular port or how many connections established etc ..

(looking more info on netstat, the easiest way to find is man netstat )

Hope this will help you .. or anything missed out here that one feel to add.. so why wait, make a comment now.. :)

Friday, May 8, 2009

Solution for Unable to extend temp segment by 128 in table space

Solution for Unable to extend temp segment by 128 in table space:

At least once in a time, A DBA or oracle professional might have faced this error in his/her experience. Here are the workarounds and solutions that we feel work and that can be employed

. Check the temp table space usage using following query

select * From v$temp_space_header;

Dynamic view to list free and used space

If BYTES_FREE column data is 0 then increase the tablespace size (associated db files size)

Check for tablespace in v$tablespace view and either add new file or increase existing file using one of the following statement.

ALTER TABLESPACE TEMP ADD TEMPFILE '..FILEPATH' SIZE XM;

OR

ALTER DATABASE TEMPFILE ' ....' RESIZE XM;

IF one see the same problem happening again and again even after doing resize/add things couple of times continuosuly one can go for below steps ( adding unlimited space to temporary tablespace will not help):

. Create new temporary tablespace with new name

CREATE TEMPORARY TABLESPACE TBLSPACENAME '..' SIZE xM;

CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/ temp02.dbf' SIZE 5M;

. Make it as a default temporary tablespace using

alter database default temporary tablespace TBLSPCNAME;

Wait for some time till no transactions are using old temporary table space and bring old temp files offline.

ALTER DATABASE TEMPFILE ' ' OFFLINE;

ALTER TABLESPACE temp TEMPFILE OFFLINE

Finally

Drop temp tablespace using

DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES
drop tablespace temp INCLUDING CONTENTS AND DATAFILES;


In summary:

CREATE TEMPORARY TABLESPACE temp TEMPFILE 'temp01.dbf' SIZE 1g;
alter database default temporary tablespace temp;
ALTER TABLESPACE TEMP02 TEMPFILE OFFLINE;
drop tablespace TEMP02 INCLUDING CONTENTS AND DATAFILES;


If still errors, I guess one need to contact on meta-link.

Solution for Unable to extend temp segment by 128 in table space

Solution for Unable to extend temp segment by 128 in table space:

At least once in a time, A DBA or oracle professional might have faced this error in his/her experience. Here are the workarounds and solutions that we feel work and that can be employed

. Check the temp table space usage using following query

select * From v$temp_space_header;

Dynamic view to list free and used space

If BYTES_FREE column data is 0 then increase the tablespace size (associated db files size)

Check for tablespace in v$tablespace view and either add new file or increase existing file using one of the following statement.

ALTER TABLESPACE TEMP ADD TEMPFILE '..FILEPATH' SIZE XM;

OR

ALTER DATABASE TEMPFILE ' ....' RESIZE XM;

IF one see the same problem happening again and again even after doing resize/add things couple of times continuosuly one can go for below steps ( adding unlimited space to temporary tablespace will not help):

. Create new temporary tablespace with new name

CREATE TEMPORARY TABLESPACE TBLSPACENAME '..' SIZE xM;

CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/ temp02.dbf' SIZE 5M;


. Make it as a default temporary tablespace using

alter database default temporary tablespace TBLSPCNAME;

Wait for some time till no transactions are using old temporary table space and bring old temp files offline.

ALTER DATABASE TEMPFILE '  ' OFFLINE;

ALTER TABLESPACE temp TEMPFILE OFFLINE


Finally

Drop temp tablespace using

DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; 

ALTER DATABASE TEMPFILE 'temp01.dbf' DROP INCLUDING DATAFILES
drop tablespace temp INCLUDING CONTENTS AND DATAFILES;



In summary:

CREATE TEMPORARY TABLESPACE temp        TEMPFILE 'temp01.dbf' SIZE 1g;
alter database default temporary tablespace temp;
ALTER TABLESPACE TEMP02 TEMPFILE OFFLINE;
drop tablespace TEMP02 INCLUDING CONTENTS AND DATAFILES;


If still errors, I guess one need to contact on meta-link.

Tuesday, May 5, 2009

EXIT from sqlplus prompt without commit results pending changes are automatically committed

It is often happen to be with anyone who has been working on oracle databases i.e. forgetting committing transaction or roll back changes that we made in current session and quitting with exit command from SQLPLUS prompt without having idea what it make an affect on db. It happens to me a couple of time but never faced any problems as commit take place automatically but things will go wrong when we supposed to rollback changes. Be careful
It always good to check whether there are any pending changes to commit or to rollback before we actually quitting from SQLPLUS prompt because when we exit from SQLPLUS without thinking of those commit and rollback issues, we end up getting those changes permanent in database as EXIT command makes pending changes are automatically committed. Be caution
Exiting by closing the SQLPLUS window by clicking cross mark at right corner of window without issuing an EXIT command will cause rollback get performed and moreover Network failures can also cause ROLLBACK to occur. Quit is similar to exit i.e. makes pending changes are automatically committed.

EXIT from sqlplus prompt without commit results pending changes are automatically committed

It is often happen to be with anyone who has been working on oracle databases i.e. forgetting committing transaction or roll back changes that we made in current session and quitting with exit command from SQLPLUS prompt without having idea what it make an affect on db. It happens to me a couple of time but never faced any problems as commit take place automatically but things will go wrong when we supposed to rollback changes. Be careful
It always good to check whether there are any pending changes to commit or to rollback before we actually quitting from SQLPLUS prompt because when we exit from SQLPLUS without thinking of those commit and rollback issues, we end up getting those changes permanent in database as EXIT command makes pending changes are automatically committed. Be caution
Exiting by closing the SQLPLUS window by clicking cross mark at right corner of window without issuing an EXIT command will cause rollback get performed and moreover Network failures can also cause ROLLBACK to occur. Quit is similar to exit i.e. makes pending changes are automatically committed.

Thursday, April 30, 2009

Books I read usually….

Technical:
Oracle online documentation
Oracle Press Books on Oracle, Linux Administration
Sybex Publications on Oracle, Linux

Non-Technical and others:
The Power of Your Subconscious Mind by Joseph Murphy
Seven Habits of Highly Effective People, Stephen F. Covey's
Dr. Robert H. Schuller's "Tough Times Never Last, But Tough People Do!"
The Alchemist by Paulo Coelho
I'm OK, You're OK by Thomas A. Harris
You Can Win -- Shiv Khera
.. etc..few to name :)

Books I read usually….

Technical:
Oracle online documentation
Oracle Press Books on Oracle, Linux Administration
Sybex Publications on Oracle, Linux

Non-Technical and others:
The Power of Your Subconscious Mind by Joseph Murphy
Seven Habits of Highly Effective People, Stephen F. Covey's
Dr. Robert H. Schuller's "Tough Times Never Last, But Tough People Do!"
The Alchemist by Paulo Coelho
I'm OK, You're OK by Thomas A. Harris
You Can Win -- Shiv Khera
.. etc..few to name :)

Wednesday, April 29, 2009

MSSQL material

MSSQL material:

A simple Query to display date and time in MSSQL database is :

select getdate()

...

Creation of new table from existing table in MSSQL:
The easiest way to create a table from an existing table or make a copy of table is often required while working with MSSQL db and method to create it is as follows:

select *
into test1copy --- new table
from test1 --- source table


Started working on this, hopefully i will post my learning here...........