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...........

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...........

Tuesday, April 28, 2009

Basic Unix/Linux commands

HOW TO CONFIGURE SAR COMMAND IN OPEN SUSE:

The prerequisite sar command to work is that sysstat rpm must be loaded into os system.


The source files that are required for sar to work can be found in /usr/lib/sa directory, there are three files sa1, sa2, sadc. the output files of sar command can be found in /var/log/sa/ ( there is one file for each day sar i.e. date mean only dd) and we may see what was the system performance yesterday or past in time.

SAR configuration on open SuSE:

I came to know that the sar command is automatically configured in red hat linux versions but one has to configure in solaris, Open SuSe and HP-UX (not sure).
The following are the simple steps to configure sar in Open SuSE.

1. Login as root or with any user having sudo access
2. Create a crontab entry with following info

0,10,20,30,40,50 * * * * /usr/lib/sa/sa1
0,10,20,30,40,50 * * * * /usr/lib/sa/sa2 -A

The actual binary system performance information of system is created by sadc script.
The sa1 shell script is a wrapper for sadc and we use it in crontab entry.
The sa2 script is used to print a report in ASCII format from sa1 script output file.

The files sa1 used above shows how often we want to take snapshot of system performance data and second line is used to how often you want to create report in human readable format to read.

Wait for some time and you will see the files being created in log directory.

Hope this will help and I personally tried it in Open SuSE and worked... this will also work in Solaris and HP-US with/without minor changes, please comment if any change or modifications.



Top ten overloaded process in unix/linux:


Sometimes we would be required to find the processes causing heavy load on Linux system,
so here is the command that one can use
"
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10

"
the CPU intensive processes can be seen with it.

Basic Unix/Linux commands

HOW TO CONFIGURE SAR COMMAND IN OPEN SUSE:

The prerequisite sar command to work is that sysstat rpm must be loaded into os system.


The source files that are required for sar to work can be found in /usr/lib/sa directory, there are three files sa1, sa2, sadc. the output files of sar command can be found in /var/log/sa/ ( there is one file for each day sar i.e. date mean only dd) and we may see what was the system performance yesterday or past in time.

SAR configuration on open SuSE:

I came to know that the sar command is automatically configured in red hat linux versions but one has to configure in solaris, Open SuSe and HP-UX (not sure).
The following are the simple steps to configure sar in Open SuSE.

1. Login as root or with any user having sudo access
2. Create a crontab entry with following info

0,10,20,30,40,50 * * * * /usr/lib/sa/sa1
0,10,20,30,40,50 * * * * /usr/lib/sa/sa2 -A

The actual binary system performance information of system is created by sadc script.
The sa1 shell script is a wrapper for sadc and we use it in crontab entry.
The sa2 script is used to print a report in ASCII format from sa1 script output file.

The files sa1 used above shows how often we want to take snapshot of system performance data and second line is used to how often you want to create report in human readable format to read.

Wait for some time and you will see the files being created in log directory.

Hope this will help and I personally tried it in Open SuSE and worked... this will also work in Solaris and HP-US with/without minor changes, please comment if any change or modifications.



Top ten overloaded process in unix/linux:


Sometimes we would be required to find the processes causing heavy load on Linux system,
so here is the command that one can use
"
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -10

"
the CPU intensive processes can be seen with it.

Monday, April 27, 2009

Oracle installtion and DB creation- issues & solutions

This summary is not available. Please click here to view the post.

Oracle installtion and DB creation- issues & solutions

This summary is not available. Please click here to view the post.

DB Files in backup mode

To verify which datafiles are currently included in a tablespace that have been placed in backup mode:

SELECT t.name AS TB_NAME, d.file# as DFILEID, d.name AS DF_NAME, b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE'

DB Files in backup mode

To verify which datafiles are currently included in a tablespace that have been placed in backup mode:

SELECT t.name AS TB_NAME, d.file# as DFILEID, d.name AS DF_NAME, b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';

Basic ORACLE db Administration:

The below provided info is basic and can be used on daily basis to monitor database and its functionality. Hope this would help you all....

1. Display the oracle DB name and user/schema name currently one connected on:

It often to be required to know which oracle Database and db user currently we logged in while we are working with SQL plus promt, here is the SQL statement to know that in easy manner without having access to v$database.

SELECT SYS_CONTEXT('USERENV','DB_NAME'), USER FROM DUAL;

2.Finding Oracle home directories and databases created in system:
The simple way of finding total number of oracle database home directories and databases is to check /etc/oratab file
-> login as oracle account -> just type cat /etc/oratab
the above command list the oracle SID, path info and Y/N ( Y - start automatically when system restart)

3. oracle listener is up ? and running?
To find whether listener is up and running, run the below commands:
lsnrctl status
u will see all the databases running on systems
to check whether a particular db is available for users access:
tnsping <sid/db>
the above commands would complete successfully if everything is fine otherwise there is an issue.

4. Database size in GB:

Here is the SQL query to find total size of database.
select  sum (bytes/1024/1024/1024) "dbsizeinGB"  from v$datafile;


5. Database status:
Here is the one with which we can find database status and can be seen whether database is in read and write mode.
select NAME,OPEN_MODE from v$database;


6. Oracle tablespace usage monitor:
It is often to be required to check and monitor table space size, here is the simple SQL statement to check for the same.

select a.tablespace_name, round(a.bytes/(1024*1024)) ttl_mbsize,
round(b.bytes/(1024*1024)) mb_free,
round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
from
(
select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
)
a,
(
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name
)
b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc


7. How one can grant on v$ views to users in oracle:
See what happens when one try to give below grant:

 SQL> grant select on v$database to XXXXX;
grant select on v$database to XXXXX
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


This results error because trying to give select privilege on a synonym. Oracle v$ views are based on V_$ tables and they have synonyms with v$views, one can not give grant on synonym.

The solution/workaround(!!??) for this issue is to give grant on V_$ table.

For example:

 Grant select on v_$database to scott;
Grant succeeded


8. How to check your oracle is 32 bit or 64 bit?
There are multiple ways to find this information using different way, here are few tips on how we can

i. One can find using select * From $version. Banner will shows us whether it is 64-bit if 64-bit version is installed.If it does not shows any bit information then it is 32-bit

ii. login into unix machine and type the following command in oracle home directory
file `which oracle`

we can find clearly whats the db bit information is all about.

9. How to check your unix OS is 32 bit or 64 bit?

uname -i


and it depends on which Os is installed (HP-US, SunOS etc)
........

UNALBE TO DROP TABLES AFTER CHANGES DONE TO DUAL TABLE:

DO NOT EVER MAKE ANY CHANGES WITH DUAL TABLE EVEN IF ONE HAVE ALL THE ACCESS I.E. DBA OR SYSDBA/SYSOPER, IT WILL RESULT DATABASE MALFUNCTIONING AND KEEP IT MIND THAT DUAL TABLE IS NOT FOR TO PERFORM DDL, DML OPERATIONS BUT FOR ONLY SELECTION USING SELECT STATEMENT.
cheers.......

Calculate the minimum necessary size of UNDO tablespace size in oracle 10g:

The following query can be used to calculate the minimum size of undo tablespace in mb based on workload of the oracle 10g database:

 SELECT (A.VALUE1*B.VALUE)/(1024*1024) MINIMUM_UNDO_MB FROM
(SELECT MAX(UNDOBLKS)/600* MAX(MAXQUERYLEN) AS VALUE1 FROM V$UNDOSTAT) A,
(SELECT VALUE FROM V$PARAMETER WHERE UPPER(NAME) ='DB_BLOCK_SIZE')B



Reclamation of space available from database objects:

One can deallocate unused extents from database objects using the following SQL:

ALTER TABLE table_name DEALLOCATE UNUSED;


How to enable change tracking for faster backup in oracle 10g:

Enable of change tracking option is to fasten the incremental backup with RMAN and it avoids RMAN to read the whole datafile to know which data blocks are changed since last full backup.
CTWR background process will write all the block change tracking info into a file which consist of updated data block info and it helps RMAN to decide which blocks actually need to be backed up.
How to enable change tracking option is as follows:

 1  alter database enable block change tracking using file
2* 'f:\oracle\chngtrack'
SQL> /

Database altered.

SQL>

we can see whether CTWR process is started by checking with v$session or v$process view

SQL> select program from v$session where username is null;

PROGRAM
---------------------------------------------------------------
ORACLE.EXE (CTWR)

--it is started now..

... Cheers..

How to Change database into Archive log mode:

Create one or two directory structure - multiplexing is always good idea

 mkdir/md c:\oracle\archive1
mkdir/md c:\oracle\archive2

connect / as sysdba
alter system set log_archive_dest_1='location=/oracle/archive1/' scope=spfile;
alter system set log_archive_dest_2='location=/oracle/archive2/' scope=spfile;
alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfiel

Note:
%d is an unique database identifier
%t - The thread number
%r - The incarnation number
%s - The log switch sequence number
All these required to ensure that files are unique and would be a good idea when multiple databases are being archived on same db system and perhaps in same directory structure.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open
you may type the following to see the log mode:

select log_mode from v$database; or select archiver from v$instance or archive log list
do alter system swithc log file; a couple of times and check the arhive log directories to see the files are generated i.e. redo log files are archived.
there is view to see all the archive log files and it is v$archived_log;


How to change REPEAT_INTERVAL of scheduler job in oracle 10g:

We some times required to change scheduler job attributes such as start date, repeat_interval etc.. and the following text can considered while doing the same.


 BEGIN
sys.dbms_scheduler.disable( '"SCHEMA"."SCHEDULERJOBNAME"' );
sys.dbms_scheduler.set_attribute( name => '"SCHEMA"."SCHEDULERJOBNAME"', attribute => 'REPEAT_INTERVAL', value => 'FREQ=DAILY; BYHOUR=5; BYMINUTE=0');
sys.dbms_scheduler.enable( '"SCHEMA"."SCHEDULERJOBNAME"' );
END;


Wish this help you all...


Basic ORACLE db Administration:

The below provided info is basic and can be used on daily basis to monitor database and its functionality. Hope this would help you all....

1. Display the oracle DB name and user/schema name currently one connected on:

It often to be required to know which oracle Database and db user currently we logged in while we are working with SQL plus promt, here is the SQL statement to know that in easy manner without having access to v$database.

SELECT SYS_CONTEXT('USERENV','DB_NAME'), USER FROM DUAL;

2.Finding Oracle home directories and databases created in system:
The simple way of finding total number of oracle database home directories and databases is to check /etc/oratab file
-> login as oracle account -> just type cat /etc/oratab
the above command list the oracle SID, path info and Y/N ( Y - start automatically when system restart)

3. oracle listener is up ? and running?
To find whether listener is up and running, run the below commands:
lsnrctl status
u will see all the databases running on systems
to check whether a particular db is available for users access:
tnsping <sid/db>
the above commands would complete successfully if everything is fine otherwise there is an issue.

4. Database size in GB:

Here is the SQL query to find total size of database.
select  sum (bytes/1024/1024/1024) "dbsizeinGB"  from v$datafile;

5. Database status:
Here is the one with which we can find database status and can be seen whether database is in read and write mode.
select NAME,OPEN_MODE from v$database;

6. Oracle tablespace usage monitor:
It is often to be required to check and monitor table space size, here is the simple SQL statement to check for the same.

select a.tablespace_name, round(a.bytes/(1024*1024)) ttl_mbsize,
round(b.bytes/(1024*1024)) mb_free,
round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
from
(
select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name
)
a,
(
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name
)
b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc

7. How one can grant on v$ views to users in oracle:

See what happens when one try to give below grant:

SQL> grant select on v$database to XXXXX;
grant select on v$database to XXXXX
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

This results error because trying to give select privilege on a synonym. Oracle v$ views are based on V_$ tables and they have synonyms with v$views, one can not give grant on synonym.

The solution/workaround(!!??) for this issue is to give grant on V_$ table.

For example:

Grant select on v_$database to scott;
Grant succeeded

8. How to check your oracle is 32 bit or 64 bit?

There are multiple ways to find this information using different way, here are few tips on how we can

i. One can find using select * From $version. Banner will shows us whether it is 64-bit if 64-bit version is installed.If it does not shows any bit information then it is 32-bit

ii. login into unix machine and type the following command in oracle home directory

file `which oracle`

we can find clearly whats the db bit information is all about.

9. How to check your unix OS is 32 bit or 64 bit?

uname -i 


and it depends on which Os is installed (HP-US, SunOS etc)
........

UNALBE TO DROP TABLES AFTER CHANGES DONE TO DUAL TABLE:

DO NOT EVER MAKE ANY CHANGES WITH DUAL TABLE EVEN IF ONE HAVE ALL THE ACCESS I.E. DBA OR SYSDBA/SYSOPER, IT WILL RESULT DATABASE MALFUNCTIONING AND KEEP IT MIND THAT DUAL TABLE IS NOT FOR TO PERFORM DDL, DML OPERATIONS BUT FOR ONLY SELECTION USING SELECT STATEMENT.
cheers.......

Calculate the minimum necessary size of UNDO tablespace size in oracle 10g:

The following query can be used to calculate the minimum size of undo tablespace in mb based on workload of the oracle 10g database:

 SELECT (A.VALUE1*B.VALUE)/(1024*1024) MINIMUM_UNDO_MB FROM
(SELECT MAX(UNDOBLKS)/600* MAX(MAXQUERYLEN) AS VALUE1 FROM V$UNDOSTAT) A,
(SELECT VALUE FROM V$PARAMETER WHERE UPPER(NAME) ='DB_BLOCK_SIZE')B


Reclamation of space available from database objects:

One can deallocate unused extents from database objects using the following SQL:

ALTER TABLE table_name DEALLOCATE UNUSED;

How to enable change tracking for faster backup in oracle 10g:

Enable of change tracking option is to fasten the incremental backup with RMAN and it avoids RMAN to read the whole datafile to know which data blocks are changed since last full backup.
CTWR background process will write all the block change tracking info into a file which consist of updated data block info and it helps RMAN to decide which blocks actually need to be backed up.
How to enable change tracking option is as follows:

  alter database enable block change tracking using file
2* 'f:\oracle\chngtrack'
SQL> /

Database altered.

SQL>

we can see whether CTWR process is started by checking with v$session or v$process view

SQL> select program from v$session where username is null;

PROGRAM
---------------------------------------------------------------
ORACLE.EXE (CTWR)

--it is started now..

... Cheers..

How to Change database into Archive log mode:

Create one or two directory structure - multiplexing is always good idea

mkdir/md c:\oracle\archive1
mkdir/md c:\oracle\archive2

connect / as sysdba
alter system set log_archive_dest_1='location=/oracle/archive1/' scope=spfile;
alter system set log_archive_dest_2='location=/oracle/archive2/' scope=spfile;
alter system set log_archive_format='arch_%d_%t_%r_%s.log' scope=spfiel

Note:
%d is an unique database identifier
%t - The thread number
%r - The incarnation number
%s - The log switch sequence number
All these required to ensure that files are unique and would be a good idea when multiple databases are being archived on same db system and perhaps in same directory structure.

shutdown immediate;
startup mount;
alter database archivelog;
alter database open
you may type the following to see the log mode:

select log_mode from v$database; or select archiver from v$instance or archive log list at sql command prompt;
do alter system swithc log file; a couple of times and check the arhive log directories to see the files are generated i.e. redo log files are archived.
there is view to see all the archive log files and it is v$archived_log;


How to change REPEAT_INTERVAL of scheduler job in oracle 10g:

We some times required to change scheduler job attributes such as start date, repeat_interval etc.. and the following text can considered while doing the same.


BEGIN
sys.dbms_scheduler.disable( '"SCHEMA"."SCHEDULERJOBNAME"' );
sys.dbms_scheduler.set_attribute( name => '"SCHEMA"."SCHEDULERJOBNAME"', attribute => 'REPEAT_INTERVAL', value => 'FREQ=DAILY; BYHOUR=5; BYMINUTE=0');
sys.dbms_scheduler.enable( '"SCHEMA"."SCHEDULERJOBNAME"' );
END;

Wish this help you all...