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

Oracle installation issues and solutions

Do the following steps in remote system:

START THE XFS PROCESS:

sudo /sbin/service xfs restart
Shutting down X Font Server
Starting X Font Server

START THE VNCSERVER

vncserver

You will require a password to access your desktops.
Password:
Verify:
Would you like to enter a view-only password (y/n)? y
Password:
Verify:
xauth: creating new authority file /home/oracle/.Xauthority

New 'X' desktop is x.x.x.x:2


LOGIN INTO VNC CLIENT FROM YOUR LOCAL SYSTEM to connect remote DB system:

open shell command prompt:

export DISPLAY=:1.0
xhost +
access control disabled, clients can connect from any host

Now screens will be shown on local pc

Two scripts that needs to be run while installing oracle software:

1.
sudo /xxx/xx/oracle/oraInventory/orainstRoot.sh
Changing permissions of /xxx/xx/oracle/oraInventory to 770.
Changing groupname of /xxx/xx/oracle/oraInventory to oinstall.
The execution of the script is complete

2.

sudo /xxx/xx/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /xxx/xx/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.


Solutions for collector errors :
Error message:

Exception String: Error in invoking target 'collector' of makefile '/xxx/xx/oracle/product/10.2.0/db_1/sysman/lib/ins_emdb.mk'

Install the following RPM's to solve the issue or latest version of those.

glibc-devel-2.7-2.i386
glibc-headers-2.7-2.x86_64
glibc-common-2.7-2.x86_64
glibc-2.7-2.i686
glibc-2.7-2.x86_64
glibc-devel-2.7-2.x86_64

Make file errors:

Install following RPM's or latest version of those

gcc-3.3.3-43
gcc-c++-3.3.3-43
libaio-devel-0.3.98-18
openmotif-libs-2.2.2-519.1


Installation errors ( make file etc):
Sometimes while installing oracle 10g on latest linux/suse systems we face installation errors in between stating that the required packages does not existed which results makefile etc errors but in reality we can see newer version rpms/packages got installed on OS. we have latest packages on Linux system but oracle requires older package for software getting installed. For this if we try to install old package on that system it will deny saying that the newer version exists on OS. So as a workaround we can create a soft link i.e. link the old package to new one by below command.
ln -s newversion oldversion

newverions: already existed one
oldversion: which oracle need to continue

and click retry button..........

ORACLE INSTALLATION QUICK FIX on Suse-11

While installing oracle 10.2 on Suse-11 there are couple of issues bound to happen.
When we run installer it fails giving error

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<
As it seems clearly that Suse9 is in the list but not Suse 11, the workaround for this is modifying the file
/etc/SuSE-release with below version.
SUSE Linux Enterprise Server 9 (i586)
VERSION = 9
and now execute ./runinstaller and it works fine.
Before we run oracle setup make sure that the below packages are installed with given version for oracle 10.2 DB.

openmotif
openmotif-libs
gcc
gcc-c++
libstdc
libaio-devel
glibc-devel-
libstdc++-devel
openmotif-libs
sysstat
XFree86-libs-
expat-
XFree86 xf86tools cabextract libpng libjpeg XFree86-Mesa
binutils-2.15.90.0.1.1-32.5
glibc-2.3.3-98.28
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.24
libstdc++-devel-3.3.3-43.24
make-3.80-184.1
pdksh-5.2.14-780.1
xscreensaver-4.16-2.6

we did solve this problem a couple of times and if anyone have any other better solution than this, please share it with us...

Creation of Oracle 10g database manually:


We often required to create oracle database manually or at least for learning purpose. I have created oracle 10g database manually by following the below method on RHEL 4 and thought to post it here...


1. Set oracle sid

 export ORACLE_SID=mydb 


2. Creation of init.ora file for new database

 vi $ORACLE_HOME/dbs/initmydb.ora

control_files = (/u02/oracle/oradata/mydb/control01.ctl,/u02/oracle/oradata/mydb/control02.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = mydb
db_block_size = 8192
sga_max_size = 573741824
sga_target = 573741824


3. Creation of password file

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwmydb password=xxxx entries=5 force=y


ensure there is password file has no extension and no upper case letters

4. start instance

sqlplus / as sysdba
startup nomount


5. Run the create db statement

create database mydb
logfile group 1 ('/u02/oracle/oradata/mydb/redo1.log') size 100M,
group 2 ('/u02/oracle/oradata/mydb/redo2.log') size 100M,
group 3 ('/u02/oracle/oradata/mydb/redo3.log') size 100M
character set utf8
national character set utf8
datafile '/u02/oracle/oradata/mydb/system.dbf' size 500M autoextend on next 10M maxsize 1G extent management local
sysaux datafile '/u02/oracle/oradata/mydb/sysaux.dbf' size 100M autoextend on next 10M maxsize 1G
undo tablespace undotbs1 datafile '/u02/oracle/oradata/mydb/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u02/oracle/oradata/mydb/temp01.dbf' size 100M;

6. Create SPFILE from PFILE

create spfile='/opt/app/oracle/product/10.1.0/dbs/spfilemydb.ora' from PFILE='/opt/app/oracle/product/10.1.0/dbs/initmydb.ora'
Shutdown immediate;
startup;
show parameter spfile -- ensure the spfile is used now..


7. Run Catalog scripts to Build Data Dictionary

@?/rdbms/admin/catalog.sql -- Creats the data dictionary and grants the access to public.
@?/rdbms/admin/catproc.sql -- Runs the all required scripts for PL/SQL .

catproc and catalog can be re-run anytime and there will not be any issues unless one run wrong version of the scripts.

8. Change sys,systems passwords with alter user statement.
   alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
Shutdown immediate;
@?/sqlplus/admin/pupbld.sql -- only as system user


example:
conn system
password: xxxx
SQL> show user
must be system
execute @?/sqlplus/admin/pupbld.sql

Check whether table PRODUCT_USER_PROFILE is already existed and if this table exists, then one should not run the pupbld.sql script. If the table does not exist, then run the script as SYSTEM.


9. Change listener,tnsnames.ora, sqlnet.ora as required.
10. Create additional tablespaces and users
11. Backup database.
12. Make entry in /etc/oratab/ with new db info
13. Create directories for dump (trace file directories) , flash recovery area (db_recovery_file_dest) and DB_CREATE_FILE_DEST etc which are required for basic db functioning and alter those relevant parameters using alter system set ''=''

These are the minimum steps to create a database and make it up and running instantly.


How to configure OEM manually in oracle 10g database:

Ensure that the job_queue_processes at least more than 1 or 1 to complete OEM installation manually
if not do the follow
show parameter job_queue_processes
alter system set job_queue_processes=2 scope=spfile;
show parameter job_queue_processes
emca -repos create

STARTED EMCA at Fri Jan 08 09:26:26 CET 2010
Enter the following information about the database to be configured
Listener port number: 1160
Database SID: mydb
Service name: mydb
Email address for notification: xxxx@xxxxxxx.com
Email gateway for notification: xxx.xxxx.com
Password for dbsnmp:
Password for sysman:
Password for sys:


-----------------------------------------------------------------
....

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


Any issues or Troubleshoot :
go to the folder
/u02/app/oracle/product/10.1.0/<>_test/sysman/config
the file to check is emoms.properties

How to solve Io Exception: The network adapter could not establish the connection in oracle manual OEM installation
check $ORACLE_HOME/hostname_sid/sysman/emd/targets.xml file to ensure correct "oracle_database", "Port", "LsnrName", ListenerOraDir and "OracleHome" etc are set correctly.

Also check $ORACLE_HOME/hostname_sid/sysman/config/emoms.properties file parameter values are set correctly...

ensure there are no errors in $ORACLE_HOME/hostname_sid/sysman/config/log/emoms.trc file... and restart db console...

Personally i got error i.e. Io Exception: The network adapter could not establish the connection and able to solve this by ensuring correct values are set in said configuration files...



How to Start, stop, status of OEM control in oracle 10g database
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole


how to install jaccelerator ncomp in oracle 10g:
One can check whether jaccelerator is installed or not using following query:

select dbms_java.full_ncomp_enabled from dual;

It show the following output if it not installed

select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236


if it is installed then the following output can be seen

SQL> select dbms_java.full_ncomp_enabled from dual;

FULL_NCOMP_ENABLED
----------------------------------------------------
OK

How to install jaccelerator and configure:

.,.... comming soon....


IF anyone have any other better solution than this, please share ... thankx

Oracle installtion and DB creation- issues & solutions

Oracle installation issues and solutions

Do the following steps in remote system:

START THE XFS PROCESS:

sudo /sbin/service xfs restart
Shutting down X Font Server
Starting X Font Server

START THE VNCSERVER

vncserver

You will require a password to access your desktops.
Password:
Verify:
Would you like to enter a view-only password (y/n)? y
Password:
Verify:
xauth: creating new authority file /home/oracle/.Xauthority

New 'X' desktop is x.x.x.x:2


LOGIN INTO VNC CLIENT FROM YOUR LOCAL SYSTEM to connect remote DB system:

open shell command prompt:

export DISPLAY=:1.0
xhost +

access control disabled, clients can connect from any host

Now screens will be shown on local pc

Two scripts that needs to be run while installing oracle software:

1.
sudo /xxx/xx/oracle/oraInventory/orainstRoot.sh
Changing permissions of /xxx/xx/oracle/oraInventory to 770.
Changing groupname of /xxx/xx/oracle/oraInventory to oinstall.
The execution of the script is complete

2.

sudo /xxx/xx/oracle/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /xxx/xx/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.


Solutions for collector errors :
Error message:

Exception String: Error in invoking target 'collector' of makefile '/xxx/xx/oracle/product/10.2.0/db_1/sysman/lib/ins_emdb.mk'

Install the following RPM's to solve the issue or latest version of those.

glibc-devel-2.7-2.i386
glibc-headers-2.7-2.x86_64
glibc-common-2.7-2.x86_64
glibc-2.7-2.i686
glibc-2.7-2.x86_64
glibc-devel-2.7-2.x86_64

Make file errors:

Install following RPM's or latest version of those

gcc-3.3.3-43
gcc-c++-3.3.3-43
libaio-devel-0.3.98-18
openmotif-libs-2.2.2-519.1


Installation errors ( make file etc):
Sometimes while installing oracle 10g on latest linux/suse systems we face installation errors in between stating that the required packages does not existed which results makefile etc errors but in reality we can see newer version rpms/packages got installed on OS. we have latest packages on Linux system but oracle requires older package for software getting installed. For this if we try to install old package on that system it will deny saying that the newer version exists on OS. So as a workaround we can create a soft link i.e. link the old package to new one by below command.
ln -s newversion oldversion

newverions: already existed one
oldversion: which oracle need to continue

and click retry button..........

ORACLE INSTALLATION QUICK FIX on Suse-11

While installing oracle 10.2 on Suse-11 there are couple of issues bound to happen.
When we run installer it fails giving error

Checking operating system version: must be redhat-3, SuSE-9, redhat-4, UnitedLinux-1.0, asianux-1 or asianux-2
Failed <<<<
As it seems clearly that Suse9 is in the list but not Suse 11, the workaround for this is modifying the file
/etc/SuSE-release with below version.
SUSE Linux Enterprise Server 9 (i586)
VERSION = 9
and now execute ./runinstaller and it works fine.
Before we run oracle setup make sure that the below packages are installed with given version for oracle 10.2 DB.

openmotif
openmotif-libs
gcc
gcc-c++
libstdc
libaio-devel
glibc-devel-
libstdc++-devel
openmotif-libs
sysstat
XFree86-libs-
expat-
XFree86 xf86tools cabextract libpng libjpeg XFree86-Mesa
binutils-2.15.90.0.1.1-32.5
glibc-2.3.3-98.28
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.24
libstdc++-devel-3.3.3-43.24
make-3.80-184.1
pdksh-5.2.14-780.1
xscreensaver-4.16-2.6

we did solve this problem a couple of times and if anyone have any other better solution than this, please share it with us...

Creation of Oracle 10g database manually:


We often required to create oracle database manually or at least for learning purpose. I have created oracle 10g database manually by following the below method on RHEL 4 and thought to post it here...


1. Set oracle sid

export ORACLE_SID=mydb

2. Creation of init.ora file for new database

vi $ORACLE_HOME/dbs/initmydb.ora

control_files = (/u02/oracle/oradata/mydb/control01.ctl,/u02/oracle/oradata/mydb/control02.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = mydb
db_block_size = 8192
sga_max_size = 573741824
sga_target = 573741824

3. Creation of password file

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwmydb password=xxxx entries=5 force=y

ensure there is password file has no extension and no upper case letters

4. start instance

sqlplus / as sysdba
startup nomount

5. Run the create db statement

create database mydb
logfile group 1 ('/u02/oracle/oradata/mydb/redo1.log') size 100M,
group 2 ('/u02/oracle/oradata/mydb/redo2.log') size 100M,
group 3 ('/u02/oracle/oradata/mydb/redo3.log') size 100M
character set utf8
national character set utf8
datafile '/u02/oracle/oradata/mydb/system.dbf' size 500M autoextend on next 10M maxsize 1G extent management local
sysaux datafile '/u02/oracle/oradata/mydb/sysaux.dbf' size 100M autoextend on next 10M maxsize 1G
undo tablespace undotbs1 datafile '/u02/oracle/oradata/mydb/undotbs1.dbf' size 100M
default temporary tablespace temp tempfile '/u02/oracle/oradata/mydb/temp01.dbf' size 100M;


6. Create SPFILE from PFILE

create spfile='/opt/app/oracle/product/10.1.0/dbs/spfilemydb.ora' from PFILE='/opt/app/oracle/product/10.1.0/dbs/initmydb.ora'
Shutdown immediate;
startup;
show parameter spfile

-- ensure the spfile is used now..

7. Run Catalog scripts to Build Data Dictionary

@?/rdbms/admin/catalog.sql 
-- Creats the data dictionary and grants the access to public.
@?/rdbms/admin/catproc.sql 
-- Runs the all required scripts for PL/SQL .

catproc and catalog can be re-run anytime and there will not be any issues unless one run wrong version of the scripts.

8. Change sys,systems passwords with alter user statement.
  alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;
Shutdown immediate;
@?/sqlplus/admin/pupbld.sql
-- only as system user

example:
conn system
password: xxxx
SQL> show user
must be system
execute @?/sqlplus/admin/pupbld.sql

Check whether table PRODUCT_USER_PROFILE is already existed and if this table exists, then one should not run the pupbld.sql script. If the table does not exist, then run the script as SYSTEM.


9. Change listener,tnsnames.ora, sqlnet.ora as required.
10. Create additional tablespaces and users
11. Backup database.
12. Make entry in /etc/oratab/ with new db info
13. Create directories for dump (trace file directories) , flash recovery area (db_recovery_file_dest) and DB_CREATE_FILE_DEST etc which are required for basic db functioning and alter those relevant parameters using alter system set ''=''

These are the minimum steps to create a database and make it up and running instantly.


How to configure OEM manually in oracle 10g database:

Ensure that the job_queue_processes at least more than 1 or 1 to complete OEM installation manually
if not do the follow
show parameter job_queue_processes
alter system set job_queue_processes=2 scope=spfile;
show parameter job_queue_processes
emca -repos create

STARTED EMCA at Fri Jan 08 09:26:26 CET 2010
Enter the following information about the database to be configured
Listener port number: 1160
Database SID: mydb
Service name: mydb
Email address for notification: xxxx@xxxxxxx.com
Email gateway for notification: xxx.xxxx.com
Password for dbsnmp:
Password for sysman:
Password for sys:

-----------------------------------------------------------------
....

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


Any issues or Troubleshoot :
go to the folder
/u02/app/oracle/product/10.1.0/<>_test/sysman/config
the file to check is emoms.properties

How to solve Io Exception: The network adapter could not establish the connection in oracle manual OEM installation
check $ORACLE_HOME/hostname_sid/sysman/emd/targets.xml file to ensure correct "oracle_database", "Port", "LsnrName", ListenerOraDir and "OracleHome" etc are set correctly.

Also check $ORACLE_HOME/hostname_sid/sysman/config/emoms.properties file parameter values are set correctly...

ensure there are no errors in $ORACLE_HOME/hostname_sid/sysman/config/log/emoms.trc file... and restart db console...

Personally i got error i.e. Io Exception: The network adapter could not establish the connection and able to solve this by ensuring correct values are set in said configuration files...



How to Start, stop, status of OEM control in oracle 10g database
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

how to install jaccelerator ncomp in oracle 10g:
One can check whether jaccelerator is installed or not using following query:

select dbms_java.full_ncomp_enabled from dual;
It show the following output if it not installed

select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236

if it is installed then the following output can be seen

 SQL> select dbms_java.full_ncomp_enabled from dual;

FULL_NCOMP_ENABLED
----------------------------------------------------
OK

How to install jaccelerator and configure:

.,.... comming soon....


IF anyone have any other better solution than this, please share ... thankx

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