Deleted Oracle flashback logs ?

Deleted Oracle flashback logs ?

Nothing will until you try to flashback database or bounce instance.
You cannot bounce , nor flashback the DB,  But following is a way to start your database with minimal effort.


My Test case


[orpncsni@testserver ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 09:34:18 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
SQL> select status from v$instance;
STATUS
------------------------------------------------
MOUNTED


########--> Mounted


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


########--> Cannot open


SQL> alter database flashback off;
Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
########--> Still cannot open


########--> Check what is the flashback status


SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------------------------------------------------------------
RESTORE POINT ONLY

SQL>  select * from v$restore_point;
 select * from v$restore_point
               *
ERROR at line 1:
ORA-38701:  Flashback database log 1024 seq 1285 thread 1:
"/pncsni/arch/flashback/PNCSNI/flashback/o5_az_8tb7frtb_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

########--> I did remember the  GRP NAME, if not get it from Alert log
 Also use (Doc ID 1288189.1)

SQL> DROP RESTORE POINT NAVEENGRP;
DROP RESTORE POINT NAVEENGRP
*
ERROR at line 1:
ORA-38701: Flashback database log 1024 seq 1285 thread 1:
"/pncsni/arch/flashback/PNCSNI/flashback/o5_az_8tb7frtb_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


(Doc ID 1554596.1) - Did not work



SQL> alter database backup controlfile to trace as '/tmp/control.ctl';


Database altered.


SQL> alter database backup controlfile to trace as '/tmp/naveencontrol.ctl';


Database altered.


Edit this file
[orpncsni@testserver ~]$ vi '/tmp/control.ctl';


Keep only following
CREATE CONTROLFILE REUSE DATABASE "PNCSNI" NORESETLOGS  ARCHIVELOG
..........
...........
CHARACTER SET XXXXXX
;


SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> startup nomount
ORACLE instance started.
....
...


-- > Backup existing control file
-- > Create new control files and open database


SQL> @/tmp/control.ctl;
Control file created.


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter database open;


Database altered.




Database has been started and opened - all done.


Then do all the rest of the activities like flashback on / archive log ,
block change tracking or other RMAN settings. 
These statements can be seen form the '/tmp/naveencontrol.ctl';


Like
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/pncsni/oradata/data02/temp01.dbf' REUSE;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/pncsni/oradata/data02/temp02.dbf' REUSE;

EHCC Analysis

-- http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_compress.htm#BEICEJED
-- Script analyse the EHCC benefit 

set serverout on verify off feed off
SET PAGESIZE 50000
ACCEPT myScrTbs CHAR DEFAULT 'EHCCSCRATCHPAD' PROMPT 'ScratchPad Tablespace [EHCCSCRATCHPAD] : ';
ACCEPT myCntTbls NUMBER DEFAULT 10 PROMPT 'Number of Top Tables to be Analysed  [10] : ';
create directory ehcc_estimate_dir as '/tmp';
grant read, write on directory ehcc_estimate_dir to sys

declare 
dyn_stmt varchar2(500); 
oradata varchar2(10); 
tbs_num number; 
v_blkcnt_cmp     pls_integer;
v_blkcnt_uncmp   pls_integer;
v_row_cmp        pls_integer;
v_row_uncmp      pls_integer;
v_cmp_ratio      number;
v_comptype_str   varchar2(60);
fileHandler UTL_FILE.FILE_TYPE;

begin
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Copy this Data to an Xls file for better analysis');
UTL_FILE.FCLOSE(fileHandler);

if '&myScrTbs' is not null and &myCntTbls is not null
then
dbms_output.put_line('');
dbms_output.put_line('#################');
dbms_output.put_line('Tablespace is  : &myScrTbs');
dbms_output.put_line('Count of tables  is : &myCntTbls' );
dbms_output.put_line('#################');
select count(1) into tbs_num from dba_tablespaces where tablespace_name='&myScrTbs'; 
IF  tbs_num > 0  then 
dbms_output.put_line('Tablespace  &myScrTbs already exisits'); 
 else 
select value into oradata from v$parameter2 where name ='db_create_file_dest';
dbms_output.put_line('Creating Tablespace &myScrTbs with files in '|| oradata);
EXECUTE IMMEDIATE 'create tablespace &myScrTbs';  
EXECUTE IMMEDIATE 'alter tablespace &myScrTbs add datafile'; 
end if; 
-- Find Object Names 
FOR tbls IN (SELECT OWNER, SEGMENT_NAME , SIZE_GB
FROM (SELECT OWNER, SEGMENT_NAME, BYTES/1024/1024/1024 SIZE_GB 
     FROM DBA_SEGMENTS 
     WHERE SEGMENT_TYPE = 'TABLE'
     ORDER BY     BYTES/1024/1024/1024  DESC )
WHERE ROWNUM <= &myCntTbls
order by SIZE_GB DESC)
LOOP
  DBMS_OUTPUT.PUT_LINE ('Owner = ' || tbls.OWNER || ', Table = ' || tbls.SEGMENT_NAME || ', Size is  = ' ||tbls.SIZE_GB );
  dyn_stmt := 'alter user '||tbls.OWNER||'  QUOTA UNLIMITED ON  &myScrTbs '; 
  EXECUTE IMMEDIATE dyn_stmt ; 
  
  dbms_compression.get_compression_ratio(
scratchtbsname   => upper('&myScrTbs'),      
ownname          => tbls.OWNER,           
tabname          => upper(tbls.SEGMENT_NAME),   
partname         => NULL,           
comptype         => dbms_compression.comp_for_query_high,    
blkcnt_cmp       => v_blkcnt_cmp,    
blkcnt_uncmp     => v_blkcnt_uncmp,  
row_cmp          => v_row_cmp,    
row_uncmp        => v_row_uncmp,  
cmp_ratio        => v_cmp_ratio,  
comptype_str     => v_comptype_str);
-- dbms_output.put_line('Estimated Compression Ratio: '||to_char(v_cmp_ratio));
-- dbms_output.put_line('Blocks used by compressed sample: '||to_char(v_blkcnt_cmp));
-- dbms_output.put_line('Blocks used by uncompressed sample: '||to_char(v_blkcnt_uncmp));
dbms_output.put_line( tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp)) ;
fileHandler := UTL_FILE.FOPEN('EHCC_ESTIMATE_DIR', 'EHCC_ESTIMATE_DATA.txt', 'A');
UTL_FILE.PUTF(fileHandler, tbls.OWNER||','|| tbls.SEGMENT_NAME||','|| to_char(v_cmp_ratio)||','||to_char(v_blkcnt_uncmp)||','||to_char(v_blkcnt_cmp));
UTL_FILE.FCLOSE(fileHandler);
END LOOP;
dbms_output.put_line('Dropping Tablespace &myScrTbs');
EXECUTE IMMEDIATE 'drop  tablespace &myScrTbs'; 
EXECUTE IMMEDIATE 'drop DIRECTORY ehcc_estimate_dir';
else
dbms_output.put_line('#################');
dbms_output.put_line('#################');
dbms_output.put_line('ERR : at least one of the variables is null ! Cannot proceed ');
dbms_output.put_line('#################');
dbms_output.put_line('#################');
end if;
dbms_output.put_line('Check file /tmp/EHCC_ESTIMATE_DATA.txt for data');
end;
/
exit;