Ulfat`s DataBase Blog

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

If you face ORA-19804 error just increase db_recovery_file_dest_size value channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of backup plus archivelog command at 12/18/2015 15:10:43 RMAN-03009: failure of backup command on ORA_DISK_1 channel at 12/18/2015 15:10:40 ORA-19809: limit exceeded for recovery files ORA-19804: cannot reclaim 52428800 bytes disk space from 214748364800 limit SQL> set linesize 120 SQL> col name for a40 \SQL> col type for a40 SQL> show parameter db_re NAME TYPE VALUE ------------------------- --------- ---------------------------- db_recovery_file_dest string /u01/app/oracle/flash_recovery_area db_recovery_file_dest_size big i...

Today after restore and recovery prod database on test environment I faced with strange error: ORA-16072: a minimum of one standby database destination is required. My database environment : OS: OEL 6.5 DB: 11.2.0.4 After recovery database stays on MOUNT mode and could not open it. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 11/27/2015 12:55:01 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9573 and starting SCN of 556583605 RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure ...

Today I will show you how to handle ORA-16053 error --Check maximum sequence number of archive log SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 36 --Now check parameter SQL> col destination for a70 SQL> select dest_id, status, destination, error from v$archive_dest; DEST_ID STATUS DESTINATION ERROR ---------- --------- ---------------------------------------------------------------------- ----------------------------------------------------------------- 1 BAD PARAM ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration 2 VALID PROD 3 INACTIVE 4 INACTIVE 5 INACTIVE 6 INACTIVE 7 INACTIVE 8 INACTIVE 9 INACTIVE 10 INACTIVE 11 VALID /u01/app/oracle/product/11.0.1/db_1/dbs/arch 11 rows selected. Here is error which means db_unique_name not setted o...

Sometimes we face "[INS-32025] The chosen installation conflicts with software already installed in the given Oracle home" error when we reinstall Oracle home. To fix this error, go to oraInventory folder. cd /u01/oraInventory/ContentsXML --this is my folder edit inventory.xml any editor remove below line: <HOME NAME="OraDb11g_home1" LOC="/u01/oracle/product/11.2.0/dbhome_1" TYPE="O" IDX="1"/> execute runInstaller again

I requested to gather most used tables list in production database. After investigation I found some advice and of course metalink note. Here is OTN link:   https://forums.oracle.com/forums/thread.jspa?threadID=511661 Use below query. SQL> SELECT ROWNUM AS RANK, Seg_Lio.* FROM ( SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit FROM V$segment_Statistics St WHERE St.Statistic_Name = 'logical reads' ORDER BY St.VALUE DESC) Seg_Lio WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20 UNION ALL SELECT ROWNUM AS RANK, Seq_Pio_r.* FROM ( SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Reads' AS Unit FROM V$segment_Statistics St WHERE St.Statistic_Name = 'physical read...