ORA-00354: corrupt redo log block header - online redo logfile - INACTIVE file corrupted

Data migration from one database to another database, importing the data & creating the 22000 indexes (login as sys user) on database.

Suddenly session was hanged & indexes also not created. Logging using my account into database throws below error:

ORA-00257:

Archiver error. Connect internal only, until freed.

I was surprised...Because whenever I used huge data import on non production database, I put the shell script to remove the old archive files every 10 minutes. This script keeps last 10 archive log files.

I verified the shell script & it was running fine and also archive log file system have enough space to keep 50 GB archive.

Finally I checked the alertlog file. It contains enough information & helped to identify the issue.

Alert Log Error:

ORA-00353: log corruption near block 655832 change 10092289716415 time 04/27/2011 22:58:57

ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'

Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc1_3950.trc:

ORA-00354: corrupt redo log block header

ORA-00353: log corruption near block 655832 change 10092289716415 time 04/27/2011 22:58:57

ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'

ARC1: All Archive destinations made inactive due to error 354

Sweep Incident[65571]: completed

Thu Apr 28 02:50:55 2011

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance test1 - Archival Error

ORA-16014: log 5 sequence# 228 not archived, no available destinations

ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'

Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc1_3950.trc:

ORA-16014: log 5 sequence# 228 not archived, no available destinations

ORA-00312: online log 5 thread 1: '/app/oracle/dbdata/data01/test1/otest1RD05.log'

Thu Apr 28 02:56:23 2011

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance test1 - Archival Error

ORA-16038: log 5 sequence# 228 cannot be archived

ORA-00001: unique constraint (.) violated

Errors in file /app/oracle/admin/test1/diag/rdbms/test/test1/trace/test1_arc2_3955.trc:

ORA-16038: log 5 sequence# 228 cannot be archived

ORA-00001: unique constraint (.) violated

ARC1: Log corruption near block 655832 change 10092289716415 time ?

ISSUE:

Redo log group 5 was corrupted & redo log group 5 status also INACTIVE status

.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIME

------------- ----------

5 1 236 536870912 1 YES INACTIVE

1.0092E+13 2011-04-28

6 1 238 536870912 1 NO CURRENT

1.0092E+13 2011-04-28

7 1 235 536870912 1 YES INACTIVE

1.0092E+13 2011-04-28

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

---------- ---------- ---------- ---------- ---------- --- ----------------

FIRST_CHANGE# FIRST_TIME

------------- ----------

8 1 237 536870912 1 YES INACTIVE

1.0092E+13 2011-04-28

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$logfile where group#=5;

GROUP# STATUS TYPE

---------- ------- -------

MEMBER

--------------------------------------------------------------------------------

IS_

---

5 ONLINE

/app/oracle/dbdata/data01/test1/oTEST1RD05.log

NO

While unarchived the logfile group, below information were registered in alert log.

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5

Thu Apr 28 03:02:13 2011

Beginning global checkpoint up to RBA [0xe7.df308.10], SCN: 10092289771542

Completed checkpoint up to RBA [0xe7.df308.10], SCN: 10092289771542

WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN

BEFORE 04/27/2011 23:41:29 (CHANGE 10092289717904) CANNOT BE USED FOR RECOVERY.

Clearing online log 5 of thread 1 sequence number 228

Thu Apr 28 03:02:15 2011

ORACLE Instance test1 - Can not allocate log, log 5 still clearing

Thread 1 cannot allocate new log, sequence 232

Thu Apr 28 03:02:15 2011

Archiver process freed from errors. No longer stopped

Online log 5 still clearing

Current log# 7 seq# 231 mem# 0: /app/oracle/dbdata/sys03/test1/oTEST1RD07.log

Thu Apr 28 03:02:39 2011

Beginning log switch checkpoint up to RBA [0xe8.2.10], SCN: 10092289771557

Thread 1 advanced to log sequence 232 (LGWR switch)

Current log# 5 seq# 232 mem# 0: /app/oracle/dbdata/sys01/test1/oTEST1RD05.log

Thu Apr 28 03:02:39 2011

Completed: ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 5

Thu Apr 28 03:02:40 2011

Clearing Resource Manager plan via parameter

Thu Apr 28 03:02:50 2011

Archived Log entry 7292 added for thread 1 sequence 230 ID 0xffffffffd697c6bc dest 1:

Thu Apr 28 03:02:55 2011

Archived Log entry 7293 added for thread 1 sequence 229 ID 0xffffffffd697c6bc dest 1:

Thu Apr 28 03:03:19 2011

Archived Log entry 7294 added for thread 1 sequence 231 ID 0xffffffffd697c6bc dest 1:

Thu Apr 28 03:04:43 2011

Reference: http://feedproxy.google.com/~r/blogspot/fnOMR/~3/_qx3Wa1BcAg/ora-00354-corrupt-redo-log-block-header.html?utm_source=feedburner&utm_medium=email

Thread 1 cannot allocate new log, sequence 124825, All online logs needed archiving

ARCH: Connecting to console port…
Thread 1 cannot allocate new log, sequence 124825
All online logs needed archiving

In database, log_archive_max_processes value is 2 & redo log file size is 250 MB (3 Groups only).

Why it’s happened?

Due to huge transactions (lot of DML operations – happened unexpectedly in database), archiver not able to archive the online redo log files.

Solution:

1.Increase the log_archive_max_processes from 2 to 5.
2.Add the new online redo log groups (3 groups to 5 groups).
3.Increase the online redo log file size from 250MB TO 500MB

Reference: http://dbarajabaskar.blogspot.com/2011/04/thread-1-cannot-allocate-new-log.html

Transparent Application Failover (TAF) Feature

Transparent Application Failover:

After an Oracle RAC node crashes - usually from a hardware failure, all new application transactions are automatically rerouted to a specified backup node. The challenge in rerouting is to not lose the transactions that were in "in flight" at the exact moment of the crash. One of the requirements of continuous availability is the ability to restart in-flight application transactions, allowing a failed node to resume processing on another server without interruption.

TAF feature is a run time failover for high availability environments. It enables client applications to automatically reconnect to the database if the connection fails and optionally resume a select statement that was in progress. The reconnection happens automatically from within the Oracle Call Interface (OCI) library.

The TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level:

SELECT failover: With SELECT failover, Oracle Net keeps track of all SELECT statements issued during the transaction, tracking how many rows have been fetched back to the client for each cursor associated with a SELECT statement. If the connection to the instance is not lost, Oracle Net establishes a connection to another Oracle RAC node and re-executes the SELECT statements.

SESSION failover: When the connection to an instance is lost, SESSION failover results only in the establishment of a new connection to another Oracle RAC node; any work in progress is lost. SESSION failover is ideal for OLTP systems, where the transactions are small.

Failover Methods:
BASIC failover: In this approach, the application connects to a backup node only after the primary connection fails. This approach has low overhead, but the end user experiences a delay while the new connection is created.

PRECONNECT failover: In this approach, the application simultaneously connects to both a primary and backup node. This offers fast failover, because a pre-spawned connection is ready to use. But the extra connection adds everyday overhead by duplicating connections.

• Make sure that instances are running:

crs_stat -t
If either of database is offline, then start the instance
srvctl start instance -d RACDB -i RAC1

• Create a service: Before you can use TAF, you need to create an application service using EM or DBMS_SERVICE package.

EM -> Click Maintenance -> Cluster Managed Database Services -> Create Service

exec dbms_service.modify_service (service_name => SERV1,
failover_method => dbms_service.failover_method_basic,
failover_type => dbms_service.failover_type_select,
failover_retries =>
.....)

• Before you can start using your service, add corresponding entry in tnsnames.ora file.

RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2_VIP) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1_VIP) (PORT=1521))
(FAILOVER = ON)
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SERV1)
)
)

TAF allows DBA to configure the type and method of failover for each Oracle Net Client.

Limitations of TAF:
The following types of transactions do not automatically failover and must be restarted by TAF:
Transactional statements: Transactions invloving INSERT, UPDATE, or DELETE statements are not supported by TAF
ALTER SESSION statements. ALTER SESSION and SQL*Plus SET statements do not failover

The following do not failover and cannot be restarted:
Temporary objects. Transactions using temporary segments in the TEMP tablespace and global temporary tables do not failover.

Refrence: Oracle Video Presentation by Ritesh Das, Oracle India.

Oracle Cluterware

Oracle clusterware is software that enables servers to operate together as if they are one server. Each server looks like any standalone server. However, each server has additional processes that communicate with each other so the seperate servers appear as if they are one server to applications and end users.

The benefits of using a cluster include:

• Scalability for applications
• Using lower-cost hardware
• Ability to fail over
• Ability to grow the capacity over time by adding servers, when needed

Oracle Clusterware provides:

• Event Management: Monitoring what's going on and processes events. Alerting other pieces of what's going on
• High Availability Framework: Makes sure that everything is maintained in an up capability
• Process Monitor: Verify that the required processes are running
• Group Membership: Keeps track of who is part of the cluster

Oracle Clusterware has 2 heartbeats: Network heartbeat and Disk heartbeat.
If a node does not send a network heartbeat for misscount (time in seconds), then the node is evicted from the cluster
If disk heartbeat is not updated in IO Timeout, then the node is evicted from the cluster

Miscount (MC) in RAC:
The Cluster Sychronization Services (CSS) on RAC has miscount parameter. This value represents maximum time, in seconds, that a network heartbeat can be missed before enterigng into a cluster reconfiguration to evict the node. The default value is 30 seconds.(Linux 60 seconds in 10g, 30 sec in 11g)

Clusterware Process Details:

• CSS: Cluster Sychronization Services: Notifies members when a node joins or leaves the cluster
• CRS: Cluster Ready Services: Monitors, starts, stops, failover operation of CRS resources (Instances, Listeners, Services, etc.)
• EVM: Event Management: The background process that publishes Oracle Clusterware events. EVM scans the designated callout directory and with the help of ONS - runs all scripts in that directory when an event occurs.
• ONS: Oracle Notification Service: Allows clusterware events to be sent to: Nodes in the cluster, Middle-tier application servers, Clients, EVMD publishes events through ONS.
• OPROCD: Process Monitor Daemon: Provides I/O fencing, OPROCD performs its check, stops runnning, and if the wake up is beyond the expected time, then OPROCD resets the processor and rebobots the node.

I/O Fencing:
There will be situations where the leftover write operations from failed database instances (the cluster functions failed on nodes, but the nodes are still running at OS level) reach the storage system after the recovery process starts. Since these write operations are no longer in proper serail order, they can damage the consistency of the stored data. Therefore, when a cluster fails, the failed node needs to be fenced off from all the shared disk devices or disk groups. This methodology is called I/O Fencing or Disk Fencing or Failure fencing.

The two main functions of I/O fencing are to prevent updates by failed instances, and to detect failue and prevent split-brain in the cluster.

CRS Alert Log file:
$CRS_HOME/log/hostname/alerthostname.log

commands like ocrconfig, ocrcheck etc dump their log here.

Voting Disk and OCR (Oracle Cluster Registry): Oracle Clusterware requires following critical two files:

Voting Disk:

• Oracle RAC uses Voting Disk to determine which instances are members of the cluster
• The voting file is essentially used by CSSD
• In the event of node failure, voting disk is used to determine which instance takes control of cluster
• The voting disk must reside on shared disk
• For high availability, Oracle recommends that you have multiple voting disks. Oracle Clusterware supports enabling multipling voting disks but you must have an odd number of voting disks.(having odd number of voting disks helps in Split-Brain scenarios, so that CRS would know which node to reboot)
• Voting disk is used by CSS to resolve split-brain scenarios and is owned by oracle user.

OCR:

• Is a binary file owned by root user which maintains cluster configuration information (cluster node list, which instances are runnning on which nodes)
• OCR must reside on shared disk that is accessible by all nodes in your cluster
• Oracle clusterware can multiples the OCR
• You can replace a failed OCR online and you can update OCR through supported APIs such as Enterprise Manager, the Server Control Utility (SRVCTL), or the Database Configuration Assistant (DBCA)

Why use Virtual IP?
The goal is application availability. When a node fails, the VIP associated with it automatically failed over to some other node. When this occurs follwing things will happen:

• VIP detects public network failure which generates a FAN event
• The new node announces the world indicating a new MAC address for the IP
• Connected clients through VIP, immediately receive ORA-3113 error or equivalent
• New connection requests rapidly traverse the tnsnames.ora address list skipping over the dead nodes, instead of having to wait on TCP-IP timeouts

Wihtout using VIPs, clients connected to a node that died will often wait for a TCP timeout period (which can up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.

Public IP: Clients use these to connect to nodes
Private IP: Used to interconnect, cache fusion
VIP: for failover (all applications connect using VIP) which is a floating IP.

Connecting with Public IP:
RACDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1) (PORT=1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)

If a session is connected using above public IP, and if the node it connected to fails, the session will TCP Timeout errors i.e. End-Of-Communication errors

Connecting with VIP:
RACDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-VIP) (PORT=1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT=1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)

In above case, NO TCP TIMEOUT WILL ENCOUNTER

Refrence: Oracle Video Presentation by Ritesh Das, Oracle India.

UPDATE query in SQL

In the following query, the subquery

select name1 from testa where no1=6;

returns no rows. In that case, it will update with NULL

SQL> update mytab set name=(select name1 from testa where no1=6), tno=9 where tno=1;

1 row updated.

SQL> select * from mytab;

TNO NAME
---------- ---------------
9
2 suman
3 ravi
4 sai
5 sample
6
7

7 rows selected.

sudo and cpio UNIX commands

sudo: super user do, that allows users to run programs with the security privileges of another user (normally the superuser, aka. root). By default, sudo will prompt for a user password but it may be configured to require the root password, or require it only once per pseudo terminal, or no password at all.

cpio: Copies files into and out of archive storage and directories.
cpio -o [ a ] [ c ] [ v ] [ B | C Value ] Output

cpio -i [ b ] [ c ] [ d ] [ f ] [ m ] [ M ] [ r ] [ s ] [ t ] [ u ] [ v ] [ S ] [ 6 ] [ B | C Value ] [ Pattern... ] Input

cpio -p [ a ] [ d ] [ l ] [ m ] [ M ] [ u ] [ v ] Directory FileName

iostat , vmstat and netstat are three most commonly used tools for performance monitoring . These comes built in with the operating system and are easy to use .iostat stands for input output statistics and reports statistics for i/o devices such as disk drives . vmstat gives the statistics for virtual Memory and netstat gives the network statstics .

The most common shells available on UNIX systems are the Bourne Shell (sh), the C Shell (csh) and the Korn shell (ksh)


Directions
To extract the cpio file, move the cpio file to an empty directory, then do: cat filename.cpio | cpio -icd

Extract from each uncompressed downloadable using "cpio" (example: cpio -idmv <>

ORDER BY clause in SQL

SQL> select * from sample;

SNO SNAME SALARY
---------- -------------------- ----------
5 test 1000
6 sdf 3223
1 vinay 8000
2 suman 2000
1 vinay 9000
3 ravi 3000
4 sai 2000
2 suman 4000
1 vinay 3000
1 vinay 3000
2 suman 4000


SQL> select * from sample order by sno;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000


[OR]

Above query can also be rewritten as follows:
SQL> select * from sample order by 1;

SNO SNAME SALARY
---------- -------------------- ----------
1 vinay 3000
1 vinay 9000
1 vinay 3000
1 vinay 8000
2 suman 4000
2 suman 2000
2 suman 4000
3 ravi 3000
3 ravi 5000
4 sai 8000
4 sai 2000