Database recovery when datafile lost from physical HDD !

Prob : Datafile is lost physically but the related tablespace is not drop from database

In oracle database 10g , I created a test database name 'support' . I created a tablespace 'support_ts' here .
In my absent mind , I deleted the datafile which is associated to "support_ts" physically .Logically the tablespace information is in database but physically the datafile is deleted .

Lets , 'SUPPORT' database has no backup (export and rman ) ,also the database is in noarchivelog mode .In this moment , is it possible to recover it or can i focely remove the tablespace information from database ? so that , in next startup database don't search the "support_ts" tablespace information ?




Solution :


Recover the datafile is not possible without backup of datafile.

You can recreate the control file and startup the database.

Steps to recrete the control file.

1. Alter database backup controlfile to trace as '';
2. Open the control file and remove the support tablespace and datafile entry in noreset logs part.
3. Connect sys as sysdba
4. Shutdown immediate/ shut abort
5. startup nomount
6. paste the controlfile content of noreset logs
7. after created the controlfile mount and open the database.


startup nomount

Run the below script , without last "comma" :

CREATE CONTROLFILE REUSE DATABASE "SUPPORT" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 '/dbfs/oradata/support/redo01.log' SIZE 100M,
GROUP 2 '/dbfs/oradata/support/redo02.log' SIZE 100M,
GROUP 3 '/dbfs/oradata/support/redo03.log' SIZE 100M,
GROUP 4 '/orafs/oradata/support/redo04.log' SIZE 100M,
GROUP 5 '/orafs/oradata/support/redo05.log' SIZE 100M,
GROUP 6 '/orafs/oradata/support/redo06.log' SIZE 500M,
GROUP 7 '/orafs/oradata/support/redo07.log' SIZE 500M
DATAFILE
'/dbfs/oradata/support/system01.dbf',
'/dbfs/oradata/support/undotbs01.dbf',
'/dbfs/oradata/support/sysaux01.dbf',
'/dbfs/oradata/support/users01.dbf',
'/dbfs/oradata/support/ababil_ts01.dbf',
'/dbfs/oradata/support/ababil_index01.dbf',
'/dbfs/oradata/support/ababil_ts02.dbf',
'/orafs/support/datafile/ababil_ts03.dbf'
CHARACTER SET WE8ISO8859P1
;


recover databsae ;

alter database open ;

Command to get SGA Size Advice and Usage !

The Following Query helps to find out the SGA Size Advice :

select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from v$sga_target_advice order by sga_size_factor;


SGA usage :
-----------
select sum(value)/1024/1024 "Megabytes" from v$sga;

select component, current_size from v$sga_dynamic_components;

How to Identify OS or Oracle 64 bit or 32 bit on Unix !

How to Identify OS or Oracle 64 bit or 32 bit on Unix
---------------------------------------------------------------
It is very common question which version of oracle I need to install. 32 bit or 64 bit? As a 64-bit operating system can support either a 32-bit database or a 64-bit database. A 32-bit operating system cannot support a 64-bit database. So, version identification of the OS is necessary prior to install oracle. The following procedure will hopefully help you.

Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,
SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules
SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu
This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.
On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,
On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
On linux,
$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
If the output is x86_64 then 64-bit and i686 or similar for 32-bit.
On windows,
Start>All Programs>accessories> System Tools>System Information>look for under System summary.
Or start>run>dixdiag>Then check for WHQL digital signature.

Determine of whether Oracle Software is 32 bit or 64 bit.
--------------------------------------------------------------------------------
Method 1:
---------
Go to $ORACLE_HOME/bin and see.
# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped
Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.
If you had 32 bit oracle software installed then output will be like,
oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped

Method 2:
---------
Log on to SQL*plus and see the banner.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Method 3:Query from v$version.
------------------------------
sys@THERAP> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production


Method 4: Check for lib, lib32
------------------------------
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.

If there is only an ORACLE_HOME/lib directory then it is 32 bit client.


Related Documents:
------------------------
http://arjudba.blogspot.com/2008/05/memory-usage-of-solaris-operating.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-host.html

About DB Time !

What is DB Time :
==================

DB Time : Amount of elapsed time (in microseconds) spent performing Database user-level calls.This does not include the time spent on instance background processes such as PMON.

DB Time = (cpu time+Wait Time); How many time spent against a user .

DB CPU : Amount of CPU time (in microseconds) spent on database user-level calls. This does not
include the CPU time spent on instance background processes such as PMON.

DB Time is the cumulative time spent by the database server in processing user requests .It includes wait time and cpu time of all non-idle user sessions . DB Time is displayed in the v$sess_time_model and v$sys_time_model

background cpu time : Amount of CPU time (in microseconds) consumed by database background processes

The follwing query helps to find out the DB time of Sessions :

select a.username,a.sid,b.stat_id,b.stat_name,b.value from v$session a, v$sess_time_model b where a.sid=b.sid and stat_name like 'DB time' order by value desc;

Here ,

STAT_ID : Statistic identifier for the time statistic

STAT_NAME: Name of the statistic

VALUE : Amount of time (in microseconds) that the system has spent in this operation


Note :
------

1 sec = 1000000 micro seconds

899413577 ms = 899 sec = 15 min

We can use the below query also :

select * from v$sys_time_model
select * from v$sess_time_model