Websites suddenly inaccessible but Google search works I'm using a Sony Ericsson Xperia Arc S running Android 2.3.4 as a secondary phone. I used to surf internet until recently without so much hassle (using WiFi, not mobile data). But recently, some strange issue showed up.

I can Google search anything. But when I try to browse certain websites from search results, I get the error "Web page is not available". However, this happens when browsing only some of the websites, including Wikipedia (strangely, when I turn the Extreme mode in the Opera Mini I can browse the website). 

I tried force stopping the browsers, cleared their data and cache, even did a  reinstall of the two browsers, UC mini and Opera Mini. It is to be noted that I did root the phone sometime ago, but I was able to surf the web with this rooted device before this issue occurred.

The problem persists on other WiFi networks as well so this couldn't be an issue with my network. That said, other devices on the network works fine.
At the moment a large number of popular website are dropping support for all SSL/TLS versions below TLS1.2 because of the known vulnerabilities in TLS1.1/TLS1.0 and older.

Your device is very very old and does not support TLS1.2 therefore you can't connect to certain sites like https://wikipedia.org which requires TLS 1.2 according to the test of SSLLabs.

If you use extreme/compressing mode of Opera Mini you connect to a proxy at Opera instead that compresses and re-encrypts the traffic. Therefore using this redirection you can still access those sites as long as Opera not also increases the minimum TLS version on this proxy.

Some Webbrowsers may also include their own TLS implementation with TLS1.2 support. But usually they have dropped support for Android 2.x long time ago.

Conclusion: Your device finally comes to an end. If there is no newer custom ROM available (AFAIR for TLS1.2 Android 4.2/4.4 is required) allow it to rest in peace and get a new phone.