Undo Advisor help to estimate the undo tablespace size and undo retention to avoid ORA-1555

Prepare by: Nurullah Sharif Scope: Undo Advisor   Undo Advisor help to estimate the undo tablespace size and also advise of undo retention. SQL> @db NAME      OPEN_MODE ——— ——————– COLLPROD  READ WRITE   Undo retention is 900 sec which 15 min SQL> sho parameter undo NAME                                 TYPE        VALUE ———————————— ———– —————————— undo_management                      string      AUTO undo_retention                       integer     900 undo_tablespace                      string      UNDOTBS2 […]

How to check and implement best practice for Oracle Database 11g onwards

Author: Saibal Ghosh About author: I am an Oracle professional with twenty plus years of experience and have deep knowledge and understanding of various facets of Oracle technology including basic Oracle Database Administration, Performance Tuning, Real Application Clusters, Data Guard and Maximum Availability Architecture. I also work a lot in the Oracle Database Cloud space, and I believe that the […]


FLASHBACK FEATURES   Purging the Recycle Bin   purge table sales.woodscrews; purge index sales.woodscrews_pk_idx; purge tablespace sales; purge recyclebin;   Undropping Objects in the Recycle Bin   flashback table ws_app.woodscrews to before drop; select object_name, original_name, droptime, dropscn from user_recyclebin; flashback table bonus to before drop rename to bonus_deb;   ———–Accidentaly Delete——————————————   Accidentally dropped table MHC_PA_INT_PPA_SPN_RL_TBL from MHCPA schema […]

Network configuration listener and tnsnames in oracle

Oracle Network Configuration =========================== In its most basic form, Oracle uses three files (listener.ora, tnsnames.ora & sqlnet.ora) for network configuration. This article gives an example of each file as a starting point for simple network configuration. •Assumptions •Listener.ora •Tnsnames.ora •Sqlnet.ora •Testing Assumptions ========== The example files below are relevant for an Oracle installation and instance with the following values. Parameter […]

Memory and Process in Oracle generic note

MEMORY MANAGEMENT 11g ===================== http://blog.tanelpoder.com/2007/08/21/oracle-11g-internals-part-1-automatic-memory-management/ PGA+SGA=MEMORY_TARGET what are the shared memory IDs for my instance $ sysresv corresponding SysV SHM segments: $ ipcs -m mapped memory for an Oracle instance process – as the SGA should be definitely mapped there! $ pmap `pgrep -f lgwr` $ ls -l /dev/shm Removing Shared Memory Sometimes after an instance crash you may have […]

control files in general

What Is a Control File? Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes: n The database name n Names and locations of associated datafiles and online redo log files n The timestamp of the database creation n The current log sequence number n […]