Oracle: Understanding REDO

In oracle, REDO concepts are not same as UNDO functionality. Like Microsoft word we cannot just REDO any operation after UNDOing. The scope of REDO is limited to the recovery purpose and syncing standby databases. REDO logs don’t hold any data, means REDO logs are actually statements fired by users as part of DML operations. These statements are mapped with SCN(System Change Number). SCN is unique number helps oracle to identify specific REDO operation. In Oracle, REDO logs are initially stored in the Memory (Redo log buffer part of SGA) and whenever user issues commit, Oracle flush all entries to the disk since last SCN change. The activity also takes place every 3rd second and when REDO log buffer is 1/3rd full. This is achieved by Log Writer process (LGWR) runs in the background.

The database must have more than 1 REDO log file and it should be multiplexed. If we do not have more than one file then Oracle will start overwriting same log file again and again. In the case of recovery then we will have only recent data available, therefore we must have more than one REDO log file. Multiplexing means creating more than 1 copy of REDO log files. So if one file set gets corrupted, then another can be used as backup options. This needs to happen parallel for Synchronization otherwise, both log files will have the different set of data which will not be having any use for recovery purpose. Apart from this, we must archive REDO logs files as well for higher recovery options. To achieve this database needs to be open in ARCHIVELOG mode.

How REDO logs are used then? During database recovery process, we can only recover database till the time when the last backup was taken. After this REDO logs are used to REDO all transactions sequentially till crash point. In the case of incomplete Recovery scenario, as well same technique followed to recover data. DBA uses SCN numbers to identify safe point for the recovery process.

REDO logs are also used to bring Standby(Disaster Recovery Site) databases in sync with the production database. There are various sub-categories in this as to how we can transfer data to the Standby sites depends upon how frequently active database is getting updated and impact of data non-availability on the business.

In summary, REDO logs are one of the core aspects of the recovery process and no functionality directly available to the end user usage like UNDO such as rollback or flashback etc.

Advertisements

Oracle – Checking LOB Segment Usage

In oracle, Lob datatype is stored as a separate segment. If your table consists any LOB datatype then its usage will not be displayed under table usage data. In order to find usage made by LOB segments under given schema following query can be used.

SELECT DL.OWNER,DL.TABLE_NAME,DL.COLUMN_NAME,SUM(DS.BYTES)/1024/1024/1024 FROM DBA_LOBS DL,DBA_SEGMENTS DS
WHERE DS.SEGMENT_NAME = DL.SEGMENT_NAME
AND DS.TABLESPACE_NAME = ‘USERS‘ AND DS.SEGMENT_TYPE = ‘LOBSEGMENT’
AND DS.OWNER LIKE ‘SCHEMA_NAME%’
GROUP BY DL.OWNER,DL.TABLE_NAME,DL.COLUMN_NAME
ORDER BY 4 DESC;

If above query not showing correct data then find correct tablespace name and replace in above query

Oracle – Viewing Foreign Key Mappings

Following command can be used to view all primary to foreign key mappings under given User’s schema. Please provide correct User name in the below query.

SELECT CON_TAB_M.TABLE_NAME FK_TABLE,CON_COL_M.COLUMN_NAME FK_COLUMN,CON_TAB_R.TABLE_NAME PK_TABLE,
CON_COL_R.COLUMN_NAME PK_COLUMN,CON_TAB_M.CONSTRAINT_NAME FK_CONSTRAINT,CON_TAB_R.CONSTRAINT_NAME PK_CONST
FROM DBA_CONSTRAINTS CON_TAB_M,DBA_CONSTRAINTS CON_TAB_R,DBA_CONS_COLUMNS CON_COL_M,DBA_CONS_COLUMNS CON_COL_R
WHERE CON_TAB_M.OWNER = :Schema_Name AND CON_TAB_M.CONSTRAINT_TYPE = ‘R’
AND CON_TAB_M.OWNER = CON_COL_M.OWNER AND CON_TAB_M.TABLE_NAME = CON_COL_M.TABLE_NAME
AND CON_TAB_M.CONSTRAINT_NAME = CON_COL_M.CONSTRAINT_NAME
AND CON_TAB_M.R_OWNER = CON_TAB_R.OWNER AND CON_TAB_M.R_CONSTRAINT_NAME = CON_TAB_R.CONSTRAINT_NAME
AND CON_TAB_R.OWNER = CON_COL_R.OWNER AND CON_TAB_R.TABLE_NAME = CON_COL_R.TABLE_NAME
AND CON_TAB_R.CONSTRAINT_NAME = CON_COL_R.CONSTRAINT_NAME
ORDER BY 1;