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