Ulfat`s DataBase Blog

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

While rebuilding index my toad session was hanged and I decided to kill session but it also hanged SQL> alter index xxx.xxxxx rebuild online parallel 8; To fix it I connected db with new sesssion and got object_id of mentioned index SQL> select object_id obj#, i.status from dba_indexes i, dba_objects o where o.object_name=i.index_name and i.index_name='LOYAL_RES_IDX1'; --Note flags SQL> select flags from ind$ where obj#=356001; Using DBMS_REPAIR package clean index SQL> declare isclean boolean; begin isclean :=false; while isclean=false loop isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN (dbms_repair.all_index_id,dbms_repair.lock_wait); dbms_lock.sleep(10); end loop; end; / --Note flags SQL> select flags from ind$ where obj#=356001; As you see it was decreased and normal

Today after analyzing segments I found out some tables have 0 rows but segment size more than 2 GB. For practice purpose I simulated some actions on test environment. To do that I created a simple user, grant him essential grants, created table, load some data etc. create user ulfet identified by ulfet; grant connect, resource to ulfet; --create table with 1 mln rows create table ulfet.btt nologging as SELECT LEVEL id, dbms_random.string ('U', 10) text1, reverse(dbms_random.string ('U', 10)) text2 FROM dual CONNECT BY LEVEL <= 1000000; --Check segment, block size SQL> select bytes, bytes/1024/1024 SIZEMB, extents, s.blocks, t.num_rows, t.last_analyzed, t.blocks from dba_segments s, dba_tables t where s.segment_name = t.table_name and segment_name='BTT'; BYTES,SIZEMB,EXTENTS,BLOCKS,NU...

On Oracle 11g to activate standby database we should do: --stop apply process SQL> alter database recover managed standby database cancel; --finish recovery managed SQL> alter database recover managed standby database finish; Database altered. --activate database SQL> alter database activate physical standby database; Database altered. --open database SQL> alter database open; Database altered. SQL> select name, open_mode from v$database; NAME OPEN_MODE --------- -------------------- PROD1 READ WRITE SQL>

Today I will show you how to remove DG configuration DGMGRL> show configuration; Configuration Name: DGMANAGER Enabled: YES Protection Mode: MaxAvailability Databases: PROD - Primary database STAND - Physical standby database Fast-Start Failover: DISABLED Current status for "DGMANAGER": SUCCESS DGMGRL> remove configuration; Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode Failed. I tried to remove database from configuration DGMGRL> remove database "STAND"; Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode Failed. But result was same. Unsuccess. I changed protection mode to MaxPerformance and then retied again DGMGRL> edit configuration set protection mode as MaxPerformance; Succeeded. DGMGRL> show configuration; Configuration Name: DGMANA...

Today early morning I saw strange error on listener log. ... Mon Feb 09 07:53:24 2015 WARNING: Subscription for node down event still pending 09-FEB-2015 07:53:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=????)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599744)) * status * 0 ... After searching on metalink I found : MOS document with ID 372959.1 In a nutshell to fix it we should add SUBSCRIBE_FOR_NODE_DOWN_EVENT_YOURLISTENER_NAME=OFF on listener.ora file In my case: SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF Then restart/reload listener. lsnrctl reload;