Restore, Recover and Open Oracle Database with Missing REDO

After you finish Resote & Recover DB. But you can not open DB for some reason. The primary is that you don't have enought REDO.
you can use this sql to check the fuzziness and PIT (Point In Time) of the datafiles after an incomplete recovery:

select fuzzy, status, checkpoint_change#, to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time, count(*)
from v$datafile_header
group by fuzzy, status, checkpoint_change#, checkpoint_time
order by fuzzy, status, checkpoint_change#, checkpoint_time;

Recovering without Consistency
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
SQL> alter system set undo_management='MANUAL' scope=spfile;
SQL> shutdown abort;
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> create undo tablespace UNDOTBS2 datafile size 50M;
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile;

Select for rollback segments
SQL> set linesize 150
SQL> select  segment_name,status from  dba_rollback_segs where    status = 'NEEDS RECOVERY';
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name like '%2';

Create pfile and edit some parameter.  The values for rollback_segments paramet come from above sql result:
SQL> create pfile from spfile
edit your pfile as below:
*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*._offline_rollback_segments=(_SYSSMU137_4114137959$,_SYSSMU156_2905474506$,_SYSSMU159_3144578331$,_SYSSMU166_3380345480$)
*._corrupted_rollback_segments=(_SYSSMU137_4114137959$,_SYSSMU156_2905474506$,_SYSSMU159_3144578331$,_SYSSMU166_3380345480$)

SQL> shutdown abort;
SQL> startup pfile='/u01/your_pfile.ora';
SQL> drop tablespace undotbs2 including contents and datafiles;
SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile;
SQL> alter system set undo_management='AUTO' scope=spfile;
SQL> starup force;

HR full script

Rem
Rem
Rem
Rem NAME
Rem Full.sql - Human Resources, HR schema
Rem
Rem DESCRIPTION
Rem This script creates six tables, associated constraints
Rem and indexes in the human resources (HR) schema.
Rem
Rem NOTES
Rem
Rem Oracle Corporation 2001.
Rem CREATED by Nancy Greenberg, Nagavalli Pataballa - 06/01/00
Rem INTEGRATED by Nelson Piedra nopiedra.wordpress.com
Rem
Rem MODIFIED (MM/DD/YY)
Rem ahunold 09/14/00 - Added emp_details_view
Rem ahunold 02/20/01 - New header
Rem vpatabal 03/02/01 - Added regions table, modified regions
Rem column in countries table to NUMBER.
Rem Added foreign key from countries table
Rem to regions table on region_id.
Rem Removed currency name, currency symbol
Rem columns from the countries table.
Rem Removed dn columns from employees and
Rem departments tables.
Rem Added sequences.
Rem Removed not null constraint from
Rem salary column of the employees table.
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF
REM ********************************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.
Prompt ****** Creating REGIONS table ....
CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ;
REM ********************************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations.
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.
Prompt ****** Creating COUNTRIES table ....
CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, region_id NUMBER
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id)
)
ORGANIZATION INDEX;
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
) ;
REM ********************************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.
Prompt ****** Creating LOCATIONS table ....
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;
CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;
ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
PRIMARY KEY (location_id)
, CONSTRAINT loc_c_id_fk
FOREIGN KEY (country_id)
REFERENCES countries(country_id)
) ;
Rem Useful for any subsequent addition of rows to locations table
Rem Starts with 3300
CREATE SEQUENCE locations_seq
START WITH 3300
INCREMENT BY 100
MAXVALUE 9900
NOCACHE
NOCYCLE;
REM ********************************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.
Prompt ****** Creating DEPARTMENTS table ....
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, manager_id NUMBER(6)
, location_id NUMBER(4)
) ;
CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
PRIMARY KEY (department_id)
, CONSTRAINT dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;
Rem Useful for any subsequent addition of rows to departments table
Rem Starts with 280
CREATE SEQUENCE departments_seq
START WITH 280
INCREMENT BY 10
MAXVALUE 9990
NOCACHE
NOCYCLE;
REM ********************************************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.
Prompt ****** Creating JOBS table ....
CREATE TABLE jobs
( job_id VARCHAR2(10)
, job_title VARCHAR2(35)
CONSTRAINT job_title_nn NOT NULL
, min_salary NUMBER(6)
, max_salary NUMBER(6)
) ;

Create ora1 user

DROP USER ora1 CASCADE;

CREATE USER ora1
  IDENTIFIED BY ora1
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 2 Roles for ora1
  GRANT RESOURCE TO ora1;
  GRANT SELECT_CATALOG_ROLE TO ora1;
  ALTER USER ora1 DEFAULT ROLE ALL;
  -- 7 System Privileges for ora1
  GRANT ALTER SESSION TO ora1;
  GRANT CREATE DATABASE LINK TO ora1;
  GRANT CREATE SEQUENCE TO ora1;
  GRANT CREATE SESSION TO ora1;
  GRANT CREATE SYNONYM TO ora1;
  GRANT CREATE VIEW TO ora1;
  GRANT UNLIMITED TABLESPACE TO ora1;
  -- 1 Object Privilege for ora1
    GRANT EXECUTE ON SYS.DBMS_STATS TO ora1;
------------------------------------------------------------------------------------------------------------