Ulfat`s DataBase Blog

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 Pr...

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 bi...

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_TYP...