Linus Torvalds on Windows 7 ... nice

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

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'
);

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

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

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

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