Wednesday, November 04, 2009

view open cursor usage in Oracle

--count open cursors by username, SID
break on report
comp sum of curs on report
select user_name, SID, count(*) cursors from V$OPEN_CURSOR group by User_Name, SID order by User_Name, SID;

--displays information on cursor usage for the current session
select * from V$SESSION_CURSOR_CACHE;

--displays information on cursor usage for the system.
select * from V$SYSTEM_CURSOR_CACHE;

--displays open_cursors usage in details
select user_name,to_char(sysdate,'hh24:mi:ss') col1, sql_text from V$OPEN_CURSOR

Sunday, October 11, 2009

Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Resolving Flashback Database Error: (ORA-38760: This database instance failed to turn on flashback database)

Unable to allocate flashback log of 1634 blocks from
current recovery area of size 42949672960 bytes.
Recovery Writer (RVWR) is stuck until more space is
available in the recovery area.
Unable to write Flashback database log data because the
recovery area is full, presence of a guaranteed
restore point and no reusable flashback logs.
Use ALTER SYSTEM SET db_recovery_file_dest_size command
to add space. DO NOT manually remove flashback log files
to create space.


so I brought down the db (aborted it)

SQL:.:(.):.> startup mount;
ORACLE instance started.

Total System Global Area 1694498816 bytes
Fixed Size 2139448 bytes
Variable Size 673405640 bytes
Database Buffers 889192448 bytes
Redo Buffers 129761280 bytes
Database mounted.
SQL:.:(.):.> select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------ -------------- -------------------- ---------
0 4.2926E+10 0

SQL:.:(.):.> SELECT NAME, SCN, TIME,
GUARANTEE_FLASHBACK_DATABASE
FROM V$RESTORE_POINT
WHERE GUARANTEE_FLASHBACK_DATABASE='YES'; 2 3 4

NAME SCN TIME GUA
------------------------------ ---------- --------------------------------------------------------------------------- ---
GRP_1 9.2118E+12 21-AUG-09 02.21.03.000000000 PM YES
GRP_2 9.2138E+12 26-AUG-09 08.29.42.000000000 AM YES

SQL:.:(.):.> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
-------------------- ---------
0


SQL:.:(.):.> flashback database to restore point GRP_2;

Flashback complete.

SQL:.:(.):.> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0

SQL:.:(.):.> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database


in the log
---------------------------------------------------------------------------------------------------------------
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 15 processes
Flashback Media Recovery Log /sbclocal/app/oracle/admin/TESTDB/arch/TESTDB_0001_0695484339_0000000024.arc
Fri Aug 28 10:13:39 2009
Incomplete Recovery applied until change 9213823318145
Flashback Media Recovery Complete
Completed: flashback database to restore point GRP_2
Fri Aug 28 10:24:07 2009
alter database open resetlogs
Fri Aug 28 10:25:21 2009
RESETLOGS after incomplete recovery UNTIL CHANGE 9213823318145
Resetting resetlogs activation ID 4173768512 (0xf8c6a740)
Fri Aug 28 10:27:56 2009
Setting recovery target incarnation to 3
Fri Aug 28 10:27:56 2009
ORA-38760 signalled during: alter database open resetlogs...
Fri Aug 28 10:52:29 2009
---------------------------------------------------------------------------------------------------------------



SQL:oradbhost:(TESTDB):PRIMARY> drop restore point GRP_1;

Restore point dropped.


SQL:oradbhost:(TESTDB):PRIMARY> select status from v$instance;

STATUS
------------
MOUNTED

SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES'l
2 .
SQL:oradbhost:(TESTDB):PRIMARY> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
----------
0


SQL:oradbhost:(TESTDB):PRIMARY> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


SQL:oradbhost:(TESTDB):PRIMARY> alter system set db_recovery_file_dest_size=50G;

System altered.

SQL:oradbhost:(TESTDB):PRIMARY> flashback database to restore point GRP_2;
flashback database to restore point GRP_2
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL:oradbhost:(TESTDB):PRIMARY> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database


-things to do if you got these scenario-
1) turn off the flashback database
2) drop the GRP
3) open the database normally(no resetlogs)
4) create a new GRP from that point

Sunday, September 20, 2009

Check log sequece required for recovery in Oracle

select sequence# from V$log_history where 9151813979322 between first_change# and next_change#

Open cursor usage in Oracle

select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

check Undo users and uncommited transactions in Oracle

set lines 300
set pages 30000
SELECT a.sid, a.username, b.xidusn, b.used_urec Num_of_records, b.used_ublk USED_BLOCKS,(b.used_ublk * ( select value from v$parameter where name = 'db_block_size'))/1024/1024 MB_USED
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;


select
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) TOTAL_UNDO_SIZE_MB,
nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) TOTAL_UNDO_USED_MB,
(select sum(bytes/1024/1024)
from dba_data_files
where tablespace_name = (select upper(value)
from V$parameter where name = 'undo_tablespace')) - nvl((SELECT sum(b.used_ublk * ( select value from v$parameter where name = 'db_block_size')/1024/1024)
FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr),0) FREE_MB_UNDO
from dual;


--#uncommited transactions

col osuser for a10
col username for a10
set lines 300
set pages 300
col sql_text for a60


select V$SESSION.OSUSER,spid,V$SESSION.status,
V$SESSION.USERNAME,V$SESSION.SID,
V$SESSION.SERIAL#,
V$SESSION.PROGRAM,
V$SESSION.MACHINE,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from
V$SESSION , v$transaction,V$process
WHERE v$transaction.ses_addr = V$SESSION.saddr and V$process.addr = V$SESSION.paddr;


select OSUSER,V$SESSION.status,
USERNAME,SID,
SERIAL#,
SQL_TEXT,
v$transaction.start_time,
to_number(sysdate-to_date(v$transaction.start_time,'MM/DD/YY HH24:MI:SS'))*24*60 run_time
from v$transaction ,
V$SESSION ,
V$SQLAREA
WHERE( (V$SESSION.SQL_ADDRESS = V$SQLAREA.ADDRESS and V$SESSION.sql_hash_value = V$SQLAREA.HASH_VALUE )
OR ( V$SESSION.PREV_SQL_ADDR = V$SQLAREA.ADDRESS and V$SESSION.PREV_hash_value = V$SQLAREA.HASH_VALUE ))
and v$transaction.ses_addr = V$SESSION.saddr;

Temporary tablespace usage in Oracle

--
set lines 200
col sid_serial for a15
col osuser for a10
col module for a20
col program for a30
col tablespace for a10
-- Listing of temp segments.
--
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

--
-- Temp segment usage per session.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

--
-- Temp segment usage per statement.
--
SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Calculate Optimal Undo in Oracle

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

checking long operations in Oracle RAC

#longopsrac.sql#
#--------------#
SELECT s.sid,
s.serial#,
s.machine,
TRUNC(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
TRUNC(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#
AND s.inst_id = sl.inst_id
AND sl.sofar < sl.totalwork
;

View RMAN jobs in Oracle

set lines 150 pages 30000
col ins format a10
col outs format a10
col TIME_TAKEN_DISPLAY format a10
select SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,input_type,
INPUT_BYTES_PER_SEC_DISPLAY ins,
OUTPUT_BYTES_PER_SEC_DISPLAY outs,
TIME_TAKEN_DISPLAY,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs
,status
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

select output
from v$rman_output
where session_key = &session_key
order by recid;

@longopsrac

Views on Flashback Area Usage in Oracle

alter session set nls_date_format='DD-MON-YYYY HH24:MI:ss';
col OLDEST_FLASHBACK_SCN for 999999999999999
col name for a80
set lines 140 pages 3000

select name,SPACE_LIMIT/1024/1024 limit_mb,SPACE_USED/1024/1024 used_mb,round((SPACE_USED/SPACE_LIMIT)*100,2) percent_usage from V$RECOVERY_FILE_DEST;

select * from V$FLASH_RECOVERY_AREA_USAGE;

select estimated_flashback_size,flashback_size,oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

SELECT * FROM gv$flashback_database_stat;

set heading off

select '---------------' from dual;
select 'Flashback usage' from dual;
select '---------------' from dual;

set heading on


@/home/casenaem/flash_back_free.sql

set heading off

select '---------------' from dual;
select 'Restore Points' from dual;
select '---------------' from dual;

set heading on

select * from V$RESTORE_POINT;

check Library Cache Lock in Oracle

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/

Backup passwords of users in a database

select 'alter user ' || username || ' identified by values ''' || password ||''';' from dba_users order by username;

Check Session Details in Oracle

set pages 49999
set lines 300
col PID for a7
col SID for 9999999999
col ser# for a7
col box for a29
col status for a9
col username for a20
col os_user for a12
col program for a25
col LOGON_TIME for a14
col module for a14
col machine for a10
select b.status as status,sql_hash_value,prev_hash_value,module,machine,
to_char(a.spid) as pid, to_char(b.sid) as sid, to_char(b.serial#) as "ser#", substr(b.machine,1,25) as box, b.username as username,
b.osuser as os_user, b.program program, to_char(b.LOGON_TIME,'dd-mon hh24:mi') LOGON_TIME from v$session b, v$process a where
b.paddr = a.addr
and type='USER'
and b.sid in (&session_id)
order by 3;

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
col SQL_TEXT for a100

SELECT b.sid,a.sql_text
FROM v$sqltext_with_newlines a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid in (&session_id)
ORDER BY b.sid,a.piece;

PROMPT
SET PAGESIZE 14

Waiting sessions in Oracle database

col wait_status for a30
col event for a40
set lines 170 pages 3000
SELECT sid, serial#, spid, username, event, wait_status, wait_time_milli
FROM(
SELECT s.inst_id AS inst_id,
s.indx AS sid,
se.ksuseser AS serial#,
-- spid from v$process
p.ksuprpid AS spid,
-- columns from v$session
se.ksuudlna AS username,
decode(bitand(se.ksuseidl,11),1,'ACTIVE',0,
decode(bitand(se.ksuseflg,4096),0,'INACTIVE','CACHED'),2,'SNIPED',3,
'SNIPED', 'KILLED') AS status,
decode(ksspatyp,1,'DEDICATED',2,'SHARED',3,'PSEUDO','NONE') AS server,
se.ksuseunm AS osuser,
se.ksusepid AS process,
se.ksusemnm AS machine,
se.ksusetid AS terminal,
se.ksusepnm AS program,
decode(bitand(se.ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?') AS type,
se.ksusesqh AS sql_hash_value,
se.ksusepha AS prev_hash_value,
se.ksuseapp AS module,
se.ksuseact AS action,
se.ksuseclid AS client_identifier,
se.ksuseobj AS row_wait_obj#,
se.ksusefil AS row_wait_file#,
se.ksuseblk AS row_wait_block#,
se.ksuseslt AS row_wait_row#,
se.ksuseltm AS logon_time,
se.ksusegrp AS resource_consumer_group,
s.ksussseq AS seq#,
e.kslednam AS event,
e.ksledp1 AS p1text,
s.ksussp1 AS p1,
s.ksussp1r AS p1raw,
e.ksledp2 AS p2text,
s.ksussp2 AS p2,
s.ksussp2r AS p2raw,
e.ksledp3 AS p3text,
s.ksussp3 AS p3,
s.ksussp3r AS p3raw,
decode(s.ksusstim,
-2, 'WAITED UNKNOWN TIME',
-1,'LAST WAIT < 1 microsecond', -- originally WAITED SHORT TIME
0,'CURRENTLY WAITING SINCE '|| s.ksusewtm || 's',
'LAST WAIT ' || s.ksusstim/1000 || ' ms (' ||
s.ksusewtm || 's ago)') wait_status,
to_number(decode(s.ksusstim,0,NULL,-1,NULL,-2,NULL, s.ksusstim/1000))
AS wait_time_milli
from x$ksusecst s, x$ksled e , x$ksuse se, x$ksupr p
where bitand(s.ksspaflg,1)!=0
and bitand(s.ksuseflg,1)!=0
and s.ksussseq!=0
and s.ksussopc=e.indx
and s.indx=se.indx
and se.ksusepro=p.addr
and e.kslednam != 'SQL*Net message from client'
and e.kslednam != 'Streams AQ: waiting for messages in the queue'
and e.kslednam != 'rdbms ipc message'
and e.kslednam != 'Streams AQ: waiting for time management or cleanup tasks'
and e.kslednam != 'Streams AQ: qmn coordinator idle wait'
);

Saturday, September 12, 2009

Response Time Analysis Made Easy in Oracle Database 10g

Historically, in trying to achieve maximum database performance, Oracle DBAs and performance analysts have fought an uphill battle to obtain solid response time metrics for system as well as user session activity. The problem facing DBAs has always had two facets: first, determining exactly "where" the database or user sessions have been spending their time; and second, determining the objective nature of the user experience.



Response Time Analysis Made Easy in Oracle Database 10g

Shared via AddThis

Sunday, August 23, 2009

GMANews.TV - Hello, Palace? OPS attention called over P5-M phone bill - Nation - Official Website of GMA News and Public Affairs - Latest Philippine News

GMANews.TV - Hello, Palace? OPS attention called over P5-M phone bill - Nation - Official Website of GMA News and Public Affairs - Latest Philippine News

Kung titingnan daw talaga natin, yung P5 million na cellular phone bill over a period of one year for an information and communications office like the OPS is not really big. Yung mga long distance calls…mga interviews, it really racked up costs," said Remonde in a recent interview with reporters.

Shared via AddThis

Sunday, August 09, 2009

Unix/Linux script to check Oracle standby database

#!/bin/ksh
#Author: Emmanuel Caseñas
#Note: this will give you a wealth of information for your standby database
################################################################################

$ORACLE_HOME/bin/sqlplus -s '/ as sysdba'<set lines 200 pages 2000
select * from v\$archive_gap;
select maX(sequence#),applied,registrar from V\$archived_log group by applied,registrar;
select PROCESS,PID,SEQUENCE#,BLOCK#,BLOCKS,STATUS from v\$managed_standby;
select NAME,DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from V\$database;
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x\$kcccp cp, x\$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
archive log list
exit
!
bd=`echo ${CLUSTER_HOME}/admin/${SID}/bdump`
ll=`grep "^Media Recovery Log" $bd/al*.log | tail -1 |awk -F"/" '{print $NF}'`
lw=`grep "^Media Recovery Waiting for" $bd/al*.log | tail -1 | awk '{print $NF}'`
echo "Latest Log Applied: $ll"
echo "Waiting for Log: $lw"

Check tablespace ts_details.sql

set lines 300
col "Data File" for a75
variable ts_name varchar2(100);
exec :ts_name := '&&tablespace_name';
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",max(fs.bytes) / (1024 * 1024) "Max Chunk Avail",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used",autoextensible
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes,autoextensible
FROM dba_data_files
GROUP BY tablespace_name,autoextensible) df
WHERE fs.tablespace_name (+) = df.tablespace_name
and df.tablespace_name = :ts_name
GROUP BY df.tablespace_name,df.bytes,autoextensible;
select file_name "Data File",bytes/1024/1024 "Size",status from dba_data_files where tablespace_name = :ts_name;
set pages 3000
spool /tmp/tmp.sql
set heading off
set term off
select '!ls -lrt ' || file_name ||'|'|| 'awk ''{print $9,$10,$11}''' from dba_data_files where tablespace_name = :ts_name;
spool off
set heading on
set term on
@/tmp/tmp.sql

Adding a datafile after the flashback database is on

1)enable flashback database in your database

*assuming you database is no in archivelog mode else skip this task*

ENABLE ARCHIVELOG MODE
----------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

CREATE A DIRECTORY FOR FLASHBACK AREA
-------------------------------------
UNIX> mkdir -p /sbcdump/01/oradumps/EMINUS/flashback_area


CONFIGURE FLASHBACK
-------------------

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest='/sbcdump/01/oradumps/EMINUS/flashback_area' scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=10g scope=both;

System altered.

SQL> shutdown immediate;


TURN FLASHBACK DATABASE ON
--------------------------

SQL> alter database mount;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> alter database open;

Database altered.


ADD DATAFILE
------------

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> set numwidth 30

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
------------------------------ --------------------
9205819379813 09-aug-2009 16:59:32


SQL> @/home/casenaem/ts_details -- this is my script to check on a tablespace
Enter value for tablespace_name: USERS

PL/SQL procedure successfully completed.


Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 500 499.6875 499.6875 100 0 NO


Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE




SQL> alter tablespace users
2 add datafile '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10m;

Tablespace altered.

SQL> @/home/casenaem/ts_details

PL/SQL procedure successfully completed.


Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ---------- --------------- ---------- ---------- ---------- ---
USERS 510 499.6875 509.375 100 0 NO


Data File Size STATUS
--------------------------------------------------------------------------- ---------- ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf 10 AVAILABLE



SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
------------------------------
9205819381790

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
2
OLDEST_FLASHBACK_SCN OLDEST_FL
------------------------------ ---------
9205819379813 09-AUG-09



FLASHBACK THE DATABASE TO THE OLDEST_FLASHBACK_SCN
--------------------------------------------------


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 9205819379813;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> @/home/casenaem/ts_details

PL/SQL procedure successfully completed.


Tablespace Size (Mb) Max Chunk Avail Free (Mb) % Free % Used AUT
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---
USERS 500 499.6875 499.6875 100 0 NO


Data File Size STATUS
--------------------------------------------------------------------------- ------------------------------ ---------
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf 500 AVAILABLE


Note:
As you can see that oracle will delete the file from dictionary,controlfile and the physical file



*Now if you want to recover the database before you executed flashback database*


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 671088640 bytes
Fixed Size 2043008 bytes
Variable Size 356520832 bytes
Database Buffers 310378496 bytes
Redo Buffers 2146304 bytes
Database mounted.
SQL> recover database;
ORA-00279: change 9205819379814 generated at 08/09/2009 16:40:19 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc
ORA-00280: change 9205819379814 for thread 1 is in sequence #17


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 9205819380249 generated at 08/09/2009 17:09:55 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000018_0694319678.arc
ORA-00280: change 9205819380249 for thread 1 is in sequence #18
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000017_0694319678.arc' no longer needed for this recovery

.......
........
..........

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf'


ORA-01112: media recovery not started


SQL> select count(1) from v$datafile_header where fuzzy = 'YES';

COUNT(1)
------------------------------
5

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006

6 rows selected.

SQL> !ls -l /sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf: No such file or directory

SQL> !ls -l /sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006
/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006: No such file or directory



SQL> alter database create datafile '/sbclocal/app/oracle/product/10.2.0.4/dbs/UNNAMED00006' as '/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf' size 10M;

Database altered.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/sbcdata/03/oradata/EMINUS/EMINUS_system_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_undo1_01.dbf
/sbcdata/01/oradata/EMINUS/EMINUS_sysaux_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_tools_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_01.dbf
/sbcdata/04/oradata/EMINUS/EMINUS_users_02.dbf

6 rows selected.

SQL> recover database;
ORA-00279: change 9205819381724 generated at 08/09/2009 17:41:53 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc
ORA-00280: change 9205819381724 for thread 1 is in sequence #22


Specify log: {=suggested | filename | AUTO | CANCEL}
aUTO
ORA-00279: change 9205819381766 generated at 08/09/2009 17:42:51 needed for thread 1
ORA-00289: suggestion : /sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000023_0694319678.arc
ORA-00280: change 9205819381766 for thread 1 is in sequence #23
ORA-00278: log file '/sbclocal/app/oracle/admin/EMINUS/arch/EMINUS_0001_0000000022_0694319678.arc' no longer needed for this recovery

....
......
.......

Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.


Note: Since Oracle has removed the physical file when I did a flashback you need to create it yourself in order for the recovery to conitnue

Wednesday, July 15, 2009

Meralco planning internet over power lines in the Philippines

While the concept of channeling the internet over power lines is far from new, it has yet to be implemented in any significant manner. If a top power distributor in the Philippines has its druthers, however, all that will change in the not-too-distant future. Manila Electric Company, better known as Meralco, is gearing up to use its power lines to bring broadband internet to more of the country, which currently sees just 20 million out of its 90 million inhabitants with access. In fact, the company has already made clear that it is "set to implement the pilot test," with the results guiding it in "determining scope and coverage of the project." Come to think of it, we've got a few dollars to spend on a rural broadband initiative here in the States. Hmm...

read more

Monday, July 13, 2009

Microsoft VP on Chrome OS: “Most of What Google Does Is Defensive"

Microsoft's Vice President of Developer and Platform Evangelism, Walid Abu-Hadba, explained in an interview what he thinks Google's real motivation for creating the Chrome OS might be, and according to him, it's not out of love for the consumer.

click me for more of this issue

Tuesday, February 03, 2009

Church Sayings

 
Church Parking Lot Sign: "FOR MEMBERS ONLY. Tresspassers will be baptized."
"No God-No Peace... Know God-Know Peace."
"Free Trip to Heaven... Details Inside!"
Try out Sundays.  They are better than Baskin-Robbins."
"Wanting for a new look?  Have your faith lifted here!"
An ad for one church has a picture of two hands holding stone tablets on which the Ten Commandments are inscribed and a headline that reads, "For fast, fast relief, take two tablets."
When the resturant next to a church put out a big sign with red letters that said, "Open Sundays," the church reciprocated with its own message: "We are open on Sundays, too."
A singing group called "The Resurrection" was scheduled to sing at a church.  When a big snowstorm postponed the preformance, the pastor fixed the outside sign to read, "Resurrection is postponed."
People are like tea bags-you have to put them in hot water before you know how strong they are.
"God so loved the world that He did not send a committee."
"Come in and pray today.  Beat the Christian rush!"
"When down in the mouth, remember Jonah.  He came out all right."
"Sign broken.  Message inside this Sunday."
"Right truth decay-study the Bible daily."
How will you spend eternity-Smoking or Non-smoking?"
"Dusty Bible leads to Dirty Lives."
"Come work for the Lord.  The work is hard, the hours are long and the pay is low.  But the retirement benefits are out of this world."
"It is unlikely there'll be a reduction in the wages of sin."
"Do not wait for the hearse to take you to church."
"If you're headed in the wrong direction, God allows U-turns."
"If you don't like the way you were born, try being born again."
"Looking at the way some people love, they ought to obtain eternal fire insurance soon."
"This is a Ch_ _ch.  What is missing?" (U R)
"Forbidden fruit creates many jams."
"In the dark?  Follow the Son."
"Running low on faith?  Stop in for a fill-up."
"If you can't sleep, don't count sheep.  Talk to the Shepherd."
If you pause to think-- You'll have cause to thank!
As sure as God puts his children in the furnace, He will be in the furnace with them.
God won't be looking for your medals, degrees or diplomas--, He'll be looking for your scars.
Give God what's right--, not what's left!
Trade God your pieces for His peace.
When you get tired talking to your friends about God--, talk to God about your friends.
It's hard to stumble when you're on your knees.
"Will the road you're on get you to my place?"....God
'Pray' is a four letter word that you can say anywhere (except in a public school).
Make your eternal reservations now--- 'smoking' or 'non-smoking'?
Jesus built us a bridge, with 2 boards and 3 nails.
Count your blessings! Recounts are OK---
Don't be God's weakest link!
It's not the outlook-- it's the uplook that counts!
He who sows sparingly will reap sparingly.
They see our methods, He sees our motives.
Plenty of folks give the Lord credit-- few give Him cash!
Finding hell is easy ! It's at the end of a 'Christ-less' life.
The greatest of evils is our indifference towards evil!
If you cheat on the test, don't thank the Lord for the "A".
Count your blessings, not your problems.
If you can't sleep, don't count sheep; talk to the Shepherd.
Good old knee-ology is as good as some theology.
A good place for the "buck to stop" is at the collection plate.
In this life it's not what you have but Who you have that counts!
A hypocrite is a person who's not himself on Sunday.
Money is a great servant but a terrible master!
God gives every bird its' food, but He does not throw it into its nest.
He who loses money, loses much; He who loses a friend, loses more; He who loses faith, loses all.
God made round faces; man makes 'em long.
Honesty is not only the best policy, it is the will of GOD!
What does it take for God to get our attention?
There are many things in my life for which I am ashamed, but Jesus is not one of them.
You can't walk with God and hold hands with Satan at the same time.
Faith is a journey, not a destination.
Jesus never taught how to preach--- only how to pray.
Jesus declared the truth; He never gave opinions.
When was the last time you told God you love Him? He is still listening.
We are as full of the Holy Spirit as we want to be.
We need to seek God Himself more than His gifts.
We become like what we worship.
Sin will keep you from the Bible but the Bible can keep you from sin.
Give Satan an inch and he'll be a ruler.
A good tree cannot bear bad fruit, and a bad tree cannot bear good fruit....Thus, by their fruit you will recognize them.Mat 7:18&20NIV
The Bread of life never gets stale.
Knowledge puffs you up-- Love lifts you up.
Feed your faith and your doubts will starve to death.
For all you do,  His blood's for you!
Big Bang theory-- God Spoke and "Bang!"  It happened--
Christians aren't perfect-- Just forgiven.
"I'm a fool for Christ-- Who's fool are you?"
Would you rather trust a guy who wrote a book--- or the One who wrote The Book?
Into each life a little rain must fall-- Who's your umbrella?
T.G.I.F.-- Thank God I'm Forgiven.
Forbidden fruits creates many jams.
If you're looking for a sign from God to get back to church, this is it! (seen in front of new church in Florida)
A wise child hears his Father's instruction.
GOD IS
Be quiet enough to hear God's whisper.
It's good to be saved and know it!  It's also good to be saved and show it!
Help is just a prayer away.
Be an organ donor--- give your heart to Jesus!
If you walk with the Lord, you'll never be out of step.
Jesus! Don't leave earth without Him!
Jesus Christ-- He paid the price!
Christ's return is near-- Don't miss it for the world!
Don't sway or turn away from the old commandments. They're still new today.
An early walk and talk with the Lord  will last all day.
Hate is not a family value.
Be ye fishers of  men. You catch them--   He will clean them.
Deciding not to choose is still making a choice.
Where death finds you, eternity will keep you.
PSALMS read here.
Things that are not eternal are already out of date. C.S.Lewis
The wages of sin have never been reduced.
The Bible isn't antique or modern,  it's eternal.
Seven days without prayer makes one weak.
Patience is a virtue which carries a lot of wait.
Firefighters rescue, only Jesus saves.
May we live simply so that others may simply live.
TITHE! Anyone can honk!
Don't let troubles get you down-- except on your knees.
Only God can give all of Himself to everyone.
How do you make a good day better? Make it a Godly day.
Only one life, 'twill soon be past-- Only what's done for Christ will last.
Things that please are temporary. Things that disturb are temporary. Things that are important are eternal.
Who is Jesus Christ?  Inquire within---
Jesus is returning...resistance is futile
Can't follow the stars? Follow the One who made them.
If you think you are perfect-- Try walking on water.
We are not on this earth to get rich, we are on earth to be enriched.
...it is not our culture to sin, it is our nature to sin; and only GOD will change
that nature.
If your knees are knocking-- kneel on 'em!
Give your troubles to God--- He never slumbers nor sleeps anyway.
It only take a few moments to open deep wounds-- but it takes years to heal them.
Christ traded in the comfort of the manger for the cruelty of the cross.
If you don't like the devil's fruit, stay out of his orchard.
You can't compromise and conquer sin at the same time.
WARNING! In case of rapture, this car will be unmanned.
I would rather walk with God in the dark than go alone in the light.
Without Jesus in your life you have no life.  With Jesus in your life you have eternal life......It's your choice.
When it comes time to die--- make sure all you have to do is die.
Going to church does not make you a Christian anymore than going to McDonalds makes you a hamburger.
There are two things I've learned: There is a God! And I'm not Him!
TODAY IS A GIFT FROM GOD.  THAT'S WHY IT IS CALLED "THE PRESENT"
If you're ready to die--- you're ready to live!
If you can't be an "Onward Christian Soldier", at least don't pass the ammunition to the enemy.
God doesn't call us to be successful--- only faithful.
Even Jesus had a fish story.
God's laughter is heard in the song of birds.
A beautiful day starts with a beautiful thought.
God without man is still God.   Man without God is nothing.
The key to Heaven was hung on a nail.
Hatred stirs up quarrels, but love covers all offences. Proverbs 10:12
Sow a seed of friendship, reap a bouquet of happiness.
Does your spiritual house need spring cleaning?
Service is love in overalls!
My little children, let us not love in word or in tongue, but in deed and in truth.1 John 3:18
Where will you spend eternity?
 
a) Here
b) Heaven
c) Hell
d) Not sure?
Is that your final answer?

Sunday, February 01, 2009

Engineer VS MBA

This particular joke won an award for the best joke in a competition organized in Britain...Enjoy!
An MBA and an Engineer go on a camping trip, set up their tent, and fall asleep. Some hours later, the Engineer wakes his MBA friend.
"Look up at the sky and tell me what you see?The MBA replies, "I see millions of stars."
The Engineer asks "What does that tell you?"
The MBA ponders for a minute:
"Astronomically speaking, it tells me that there are millions of galaxies and potentially billions of planets.
"Astrologically, it tells me that Saturn is in Leo.
Time wise, it appears to be approximately a quarter past three.
Theologically, it's evident the Lord is all-powerful and we are small and insignificant.
Meteorologically, it seems we will have a beautiful day tomorrow. What does it tell you?
"The Engineer friend is silent for a moment, and then speaks:
"Practically...it tells me that someone has stolen our tent