restore

Oracle: info about the corrupted block

Monday, February 25th, 2008

SELECT * FROM dba_extents
WHERE  file_id = &file_id
AND    &block_id BETWEEN block_id AND block_id + blocks - 1;

Oracle: using recycling bin

Monday, February 18th, 2008

Information about the objects in recycle bin:

SELECT object_name,
 original_name, type,
 can_undrop , can_purge ,
 droptime
FROM recyclebin

or
show recyclebin
Restore

flashback TABLE mytable TO before DROP;

Clear recycle bin
purge recyclebin;

Oracle: flashback usage

Friday, January 18th, 2008

– To save the flashback information for the last 30 minutes:
ALTER SYSTEM SET UNDO_RETENTION = 1800;
–
SELECT * FROM some_table AS OF TIMESTAMP (SYSDATE – INTERVAL ‘5‘ MINUTE);
SELECT * FROM some_table AS OF TIMESTAMP (’2008-01-18 06:31:58′, ‘YYYY-MM-DD HH24:MI:SSS’);
SELECT * FROM some_table AS OF SCN 364583948;
– dbms_flashback could be also used
EXECUTE dbms_flashback.enable_at_time (‘18-JAN-08 11:00:00‘);

Oracle: file needs recovery (offline mode)

Tuesday, January 15th, 2008

SELECT d.file# f#, d.name, d.status, h.status
FROM v$datafile d, v$datafile_header h
WHERE d.file# = h.file#
AND (d.STATUS NOT IN (’SYSTEM’,'ONLINE’) OR h.STATUS != ‘ONLINE’ );

If there are such files, the recovery is necessary:

restore the file from the backup
recover datafile ‘&the_file_name’ ;
alter database datafile ‘&the_file_name’ online;

Another possibility (if there are a lot of files):

restore the files from the backup
recover [...]

Keep on coding