oracle

The Problem Following errors are reported in the alert log file: ORA-354: corrupt redo log block header ORA-353: log corruption near block %s change %s time %s ORA-312: online log %s thread %s: '%s' ORA-312: online log %s thread %s: '%s' ORA-312 reports all the redo log file names for the same redo log group number. Example for redo log group number 3 which has two redo log file members: ORA-00354: corrupt redo log block header ORA-00353: log corruption near block 1892904 change 878787 … [Read more...] about ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group

Offline Method 1. Shut down the database. 2. Copy the online redo log files to the new location. Operating system files, such as online redo log members, must be copied using the appropriate operating system commands. The following example uses operating system commands (UNIX) to move the online redo log members to a new location: $ mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo $ mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo 3. Startup the database in mount mode, but do not … [Read more...] about How to relocate the redo log files to a different location on disk

Question: How to add or drop online redo logs? How to change redo log file location? How to determine the optimal size for redo logs using OPTIMAL_LOGFILE_SIZE? How to add or drop online redo logs 1. Review information on existing redo logs: SELECT a.group#, b.member, a.status, a.bytes FROM v$log a, v$logfile b WHERE a.group#=b.group# 2. Add new groups: ALTER DATABASE ADD LOGFILE group 4 ('/log01A.dbf', '/log01B.dbf ') SIZE 512M; ALTER DATABASE ADD LOGFILE group 5 ('/log02A.dbf', … [Read more...] about Oracle Database: Redo log operations (Add/Drop/Change Location)

Mirroring online redo logs The Oracle database maintains online redo log files to minimize the loss of data in the database. The redo log files record all changes made to data in the database buffer cache with some exceptions; for example, in the case of direct writes. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are only used for recovery. Online Redo Log Groups - A set of … [Read more...] about How to add and drop online redo log members and groups in Oracle

This post intends to provide an SQL script to find tables that are fragmented (i.e. Data is much lower than High Water Mark) so that we can target those segments (tables) for recreation. pre-requisites Requires dba privileges as the script is to be run as the owner SYS or SYSTEM. It Will not work on Compressed tables, and may return negative numbers. Do an Analyze with compute statistics on all tables present in the Users schema. The SQL script The script is as shown below: REM This is … [Read more...] about SQL script to find tables that are fragmented

When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is the default), it will release the space back to the tablespace. But we see cases that event truncating won't release the space back to the tablespace. The answer is simple, When a truncate is issued on a table, Oracle deallocates all space used by the removed rows … [Read more...] about TRUNCATE TABLE not releasing space from tablespace

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing … [Read more...] about How to reclaim entire space of an oracle database table with “Truncate Table” statement