Today the the main topic is:  FLASHBACK DATABASE

So ....

1- VALIDATE STATUS:
sql> SELECT flashback_on, log_mode FROM v$database;
sql> SELECT name, value  FROM gv$parameter  WHERE name LIKE '%flashback%';

2- ACTIVATION OR NOT :
sql> show parameters db_recovery_file_dest
sql> SHUTDOWN immediate;
sql> startup mount exclusive;
sql> alter database flashback on / off --> activar ou desactivar
sql> alter system set db_recovery_file_dest=/oracle/flash_recovery_area scope = both;
sql> alter system set db_recovery_file_dest_size = 250g scope =both ;-- (default) sid='*' (RAC) ;
sql> alter database open;

2- TUNNING
sql> SELECT round(estimated_flashback_size /1024/1024,2) "ESTIMETED SIZE MB" FROM gv$flashback_database_log;
sql> alter system set DB_FLASHBACK_RETENTION_TARGET = 2880; --2 days

3- PERMISSIONS to specified user:
sql> GRANT flashback any table TO user; --> if neded

4- CREATING RESTORE POINT
sql> CREATE RESTORE POINT ;
sql> CREATE RESTORE POINT ; -- GUARANTEE FLASHBACK DATABASE (if it is realy importante to keep it beond retention window);
sql> DROP RESTORE POINT ;
 
4.1 - From this point prevent add datafiles or resize TBS. Because you can have some problems when you rollback database.

5- VALIDADE RESTORES POINTS in DB
sql> col name format a15
sql> col time format a32
sql> SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, round(storage_size /1024/1024,2) FROM gv$restore_point;

6- ROLLBACK TO RESTORE_POINT
sql> SHUTDOWN immediate;
sql> startup mount exclusive;
sql> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); or:
sql> FLASHBACK DATABASE  TO TIMESTAMP to_timestamp('2002-11-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS'); ora
sql> flashback database to restore point ; -- simplest way.  :-)
sql> alter database open resetlogs;


7- Cleanig archive area after rollback DB 
$hostname>rman target sys/pwd@orabase
or
$hostname>rman
RMAN> connect target /
RMAN> crosscheck archivelog all;
RMAN> delete archivelog all;
RMAN> list archivelog all;

I see you later ...

Parallel sessions + Spent CPU

Hi, i'm back!
Today it's querie about parallel sessions and used cpu:

select decode(aa.qcinst_id,NULL,aa.username,' -  '||lower(substr(aa.program,length(aa.program)-4,4) ) )  Usern, "QC/Slave",  "SID",  "QC SID", status,  cpu_secs_total, aa.program , aa.sql_id, aa.sql_hash_value,
    aa.prev_sql_id from (select username,px.qcinst_id,s.program, upper(decode(px.qcinst_id,NULL,username,

lower(substr(s.program,length(s.program)-4,4) ) ) ) userx,  decode(px.qcinst_id,NULL, 'Querie Cordenadora', '(Slave)') "QC/Slave" , to_char(s.sid) "SID",   decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID" ,  s.sql_id, s.sql_hash_value, s.prev_sql_id  from v$px_session px,  v$session s where   px.sid=s.sid (+)  and px.serial#=s.serial# ) aa, (SELECT upper(slave_name) slave_name, status, cpu_secs_total FROM v$pq_slave) bb where upper(bb.slave_name(+)) = aa.userx
order by 4 , 1 desc ;

Enjoy it!
JMaia