Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open.

 SQL> select name from v$database;  
 NAME  
 ---------  
 TEST

SQL> shut abort;  
 ORACLE instance shut down.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 6597406720 bytes  
 Fixed Size                2265664 bytes  
 Variable Size           3204451776 bytes  
 Database Buffers       3372220416 bytes  
 Redo Buffers            18468864 bytes  
 Database mounted.  
 SQL> alter database open;  
 alter database open  
 *  
 ERROR at line 1:  
 ORA-03113: end-of-file on communication channel  
 Process ID: 6552  
 Session ID: 191 Serial number: 3 

 What`s wrong? 

 SQL> alter database open resetlogs;  
 ERROR:   

 ORA-03114: not connected to ORACLE  

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

And instance got down

$ ps -ef | grep smon

oracle    6586  6246  0 17:50 pts/2    00:00:00 grep smon

 $ sqlplus "/as sysdba"  
 SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 17:50:35 2016  
 Copyright (c) 1982, 2013, Oracle. All rights reserved.  
 Connected to an idle instance.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 6597406720 bytes  
 Fixed Size                2265664 bytes  
 Variable Size           3204451776 bytes  
 Database Buffers       3372220416 bytes  
 Redo Buffers            18468864 bytes  
 Database mounted.  

Tried to reset logs

SQL> alter database open resetlogs;  
 alter database open resetlogs  
 *  
 ERROR at line 1:  
 ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

Unsuccessful...
Let`s recover database 

SQL> recover database until cancel;  
 Media recovery complete.  

Now, try to open with resetlogs

 SQL> alter database open resetlogs;  
 Database altered. 

Done, now it works

ORA-29339: tablespace block size 16384 does not match configured block sizes

While I create non standart db_block_size tablespace I faced ORA-29339: tablespace block size 16384 does not match configured block sizes.

16k = 16*1024 = 16384

 SQL> CREATE TABLESPACE DATA DATAFILE  
  '/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G  
 LOGGING  
 DEFAULT  
  NO INMEMORY  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 BLOCKSIZE 16K  
 SEGMENT SPACE MANAGEMENT AUTO  
 FLASHBACK ON;  

ORA-29339

 SQL> select name, block_size, current_size from v$buffer_pool;  
 NAME                 BLOCK_SIZE CURRENT_SIZE  
 -------------------- ---------- ------------  
 DEFAULT              8192       132096  

Check db_*k_cache_size parameters

SQL> show parameter cache_size  
 NAME     TYPE VALUE  
 ------------------------------------ ----------- ------------------------------  
 client_result_cache_size   big integer 0  
 db_16k_cache_size    big integer 0  
 db_2k_cache_size    big integer 0  
 db_32k_cache_size    big integer 0  
 db_4k_cache_size    big integer 0  
 db_8k_cache_size    big integer 0  
 db_cache_size    big integer 0  
 db_flash_cache_size    big integer 0  
 db_keep_cache_size    big integer 0  
 db_recycle_cache_size    big integer 0  
 SQL>  

Also db_block_size

 SQL> show parameter db_block_size  
 NAME     TYPE VALUE  
 ------------------------------------ ----------- ------------------------------  
 db_block_size    integer 8192  
 SQL>  

I need to set db_16k_cache_size.

 SQL> alter system set db_16k_cache_size = 128M scope=both; 

Then try again

 SQL> CREATE TABLESPACE DATA DATAFILE  
  '/u01/app/oracle/oradata/cdb1/TEST/data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 31G  
 LOGGING  
 DEFAULT  
  NO INMEMORY  
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE  
 BLOCKSIZE 16K  
 SEGMENT SPACE MANAGEMENT AUTO  
 FLASHBACK ON;  
 Tablespace created.  

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value.

Example result of sql

select
                 execution_name, advisor_name,
                 to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,
                 to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message
from dba_advisor_executions
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'
order by execution_start;

Check value of TIME_LIMIT`s parameter :

SQL> column parameter_value for A35   
 SQL> select   
      parameter_name, parameter_value   
      from dba_advisor_parameters   
      where task_name = 'SYS_AUTO_SQL_TUNING_TASK'   
      and parameter_name in ('TIME_LIMIT',   
                            'DEFAULT_EXECUTION_TYPE',   
                            'LOCAL_TIME_LIMIT'); 

 
 PARAMETER_NAME                 PARAMETER_VALUE  
 ------------------------------ ------------------------------  
 LOCAL_TIME_LIMIT                1200  
 TIME_LIMIT                      3600  
 DEFAULT_EXECUTION_TYPE          TUNE SQL  

In our case it is default value. Let`s increase it

 SQL> begin   
 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200);   
 end;   
 / 2  3  4   
 PL/SQL procedure successfully completed. 

Check again:

 PARAMETER_NAME,             PARAMETER_VALUE  
 LOCAL_TIME_LIMIT,           1200  
 TIME_LIMIT,                 7200  
 DEFAULT_EXECUTION_TYPE,     TUNE SQL