This place holds space for subject line. Stay tuned.
I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)
We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.
I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.
We needed “something” to hold a copy of the EBS instance while it was being virtualized.
So, I cloned this machine & before continuing let me describe what this is hardware is like:
Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 1 Core(s) per socket: 4 Socket(s): 2 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 23 Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz Stepping: 6 CPU MHz: 1992.000 BogoMIPS: 4987.29 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 6144K NUMA node0 CPU(s): 0-7
[root@erp-bak ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sdj2 59G 12G 44G 22% / tmpfs 9.7G 3.9M 9.7G 1% /dev/shm /dev/sdj1 2.0G 325M 1.5G 18% /boot /dev/sdj5 738G 531G 170G 76% /u01 /dev/mapper/mpathbp1 591G 320G 242G 57% /u02 /dev/mapper/mpathcp1 197G 38G 150G 20% /u03 /dev/sdf1 591G 70M 561G 1% /u04 /dev/sdi1 269G 59M 256G 1% /u05
in addition to the local disks this server have partitions mounted from a IBM SAN.
Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)
I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.
Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.
While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.
Few of the persistent issues were:
After a cold boot
- The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
- It takes ages for to open the concurrent programs window
- Our custom forms & LOVs lag to extremes and so on..
Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!
Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…
I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye
I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!
I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!
Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:
Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.
Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)
I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!
Usually I prefer a virtual machine for all my Linux/Development requirements. Well, Microsoft is “trying” to setup a Linux subsystem within Windows 10 from a while, which is “yet” to be fully functional in many means. Many benchmarks proven that Microsoft Subsystem for Linux for has the poorest I/O which Microsoft claims, will be addressed in the future releases.
So, why someone will choose WSL against a VM which has better functionalities? From an eager inquisitive person’s perspectives, I will choose WSL for just couple of reasons and they are:
WSL could access the full hardware, when a VM has limited resources. Ie, say your computer running has 4 cores and 8GB memory, you will be limited to 2 cores for your VM (anything beyond that would cause unexpected issues) and maximum of 4GB memory. Here WSL don’t have such limitations, it could access all your 4 cores and the maximum memory & could be launched like just another win32 application!
I’ve been trying to setup WSL using both Ubuntu & OpenSUSE Leap 42.3 over my Windows 10 1709 & I had better results with Ubuntu. OpenSuse has many issues those were beyond my skills to address and resolve. Hence, I chose to stick with Ubnutu for now.
So, installation of WSL on Windows 10 recent versions were made pretty easier compared to the previous releases. A seasoned Windows user could install Ubuntu for WSL very easily by following the instructions below
Once rebooted, from the Windows store, search for Ubuntu or OpenSUSE and just install it! That simple it is.
Once installed, as mentioned with the first image, one can start the WSL either from command prompt or by clicking the tile that is available with the start menu.
As a developer, I would take the pain to setup the WSL for ONLY few reasons like testing PHP application(s) those are based on MySQL database (and outta curiosity)
So next, let us see we can setup LAMP server (LAMP is an archetypal model of web service stacks, named as an acronym of the names of its original four open-source components: the Linux operating system, the Apache HTTP Server, the MySQL relational database management system (RDBMS), and the PHP programming language. The LAMP components are largely interchangeable and not limited to the original selection. As a solution stack, LAMP is suitable for building dynamic web sites and web applications) on our new WSL
You may refer to this post for a detailed information about how to install individual components like apache, MySQL and PHP. Please note, the link that I provided deals with PHP5, which is not anymore available from Ubuntu repositories, hence I chose to go ahead with PHP7.0 for my WSL instance
Issue the following command(s) to install Apache, MySQL,PHP and phpMyAdmin
sudo apt-get install apache2 sudo apt-get install mysql-server
During the installation, you will be prompted for setting up MySQL root credentials, which is mandatory in order to access the MySQL server at later stages using phpmyadmin module
For PHP, you must install libapache2-mod-php, else you would experience php pages being blank when functions like phpinfo(); are called.
sudo apt-get install php libapache2-mod-php
Once the packages are installed (without errors) you can restart apache to verify everything is working fine
sudo service apache2 restart or sudo /etc/init.d/apache2 restart
If your windows box already has IIS listening to port 80 and a Windows instance for MySQL is listening to port 3306, then you must change the ports for both apache and MySQL before restarting the services.
For changing the port of Apache server edit the following file
For changing the port of MySQL server edit the following file
Although no real show stoppers, these couple or warning/error messages could make you feel irritated whenever apache and MySQL services are started
[core:warn] [pid 47:tid 140569079777152] (92)Protocol not available: AH00076: Failed to enable APR_TCP_DEFER_ACCEPT
Edit the file
add the following line by the end of the file
AcceptFilter http none
Restart the apache service
No directory, logging in with HOME=/
Edit the file
and change the MySQL user details like following
Restart all services to make sure that there are no more warning/error messages
So far so good, let us install phpmyadmin, which you will use to manage your MySQL instance
sudo apt-get install phpmyadmin php-mbstring php-gettext
During the installation you will be prompted for selecting the http server stack and password for MySQL root account. You must provide the root password that you have set during the MySQL installation at this prompt
(Refer https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-16-04 for more details)
Once the phpmyadmin is installed, you need to manually enable couple of extensions and they are dealt like following:
#sudo phpenmod mcrypt #sudo phpenmod mbstring
Restart all services
sudo service apache2 restart sudo service MySQL restart
rajesh@rt04:/etc/apache2$ sudo service apache2 restart * Restarting Apache httpd web server apache2 [ OK ] rajesh@rt04:/etc/apache2$ sudo service mysql restart * Stopping MySQL database server mysqld [ OK ] * Starting MySQL database server mysqld [ OK ] rajesh@rt04:/etc/apache2$
Now you can see that there are no errors or warning while starting both apache and MySQL. So let us go ahead and check phpmyadmin
That’s all folks!
There is no shutdown for WSL, just type “exit” and you are good. I am yet to test whether mere exiting does anything harmful to running instances of apache and MySQL. Until I find them…
I don’t really understand why, yet, many users are asking for a “way” to ssh into the Linux subsystem. The pre-installed openBDS ssh server doesn’t allow a connection from clients like putty. Hence a re-installation of SSH server is required.
sudo apt-get remove --purge openssh-server sudo apt-get install openssh-server sudo service ssh --full-restart Create a firewall inbound rule if required
Now you should able to connect to WSL instance using a client like putty.
Some time back, we had a requirement of sending a text message to all the client computers to inform about some activity.
I have created a little script which can send a POP UP MESSAGE to the computer(s), and it will simply prompt for input, and at the time of finish it will show you all the statistics.
The script can accept a single computer name or multiple computer names separated with comma (,) or path of txt file which is containing the list of computers.
When you run this script it will ask you for below Input.
Message :- Type your message which you want to send to the computers. Like Hi, “Hello, Update are being installed on your computer, pleas wait” Etc.
Computer Name: – Type the computer name to which you want to send the message, if you type PC1, it will send message to PC1 computer.
View original post 439 more words
Not your everyday task, yet, when nothing else you could do with a database that won’t start, give it a try. If you have valid Oracle support, then execute only after Oracle confirms.
Please note, there are no guarantees that using the “_ALLOW_RESETLOGS_CORRUPTION” hidden parameter will bring your database up and running. For example, I have made several attempts to duplicate a database in the same machine, few of the exercises were like following:
At home, I copied .dbf files from a live database and created a new database using the “_ALLOW_RESETLOGS_CORRUPTION” which was successful, when the same approach failed without any hopes on my work laptop where I have the same 10.2.0.3 to 22.214.171.124 upgraded instance.
Other hand whenever I copied the .dbf files from a shutdown database and tried to create a standby database, it always succeeded with “alter database open resetlogs”
So, don’t be over confident about recovering a database using “another” hidden parameter. If you are lucky, you may, on the other hand ONLY a proper backup will help you to recover from a significant crash.
So, start backing up your database on regular intervals. I suggest RMAN with archivelogs which helps you to backup the database without downtime.
Recently while opening a TEST RAC database we found the database down and was not available, we tried to start the database but received communication errors and left us clueless. Due to some urgent POC activity scheduled on the database we started investigating the root cause right away for error.
ORA-03113: end-of-file on communication channel
[oracle@testdbdixit ~]$ sqlplus / as sysdba
SQL*Plus: Release 126.96.36.199.0 Production on Thu Feb 26 01:04:23 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
ORA-03113: end-of-file on communication channel
We tried to open the database in MOUNT mode and it reached to phase easily without any error.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 1.6034E+10 bytes
Fixed Size 2269072 bytes
Variable Size 2449473648 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 26480640 bytes
But the recovery of any kind got failed because of the…
View original post 632 more words
Few important RMAN Commands. Stay tuned, I am compiling a RMAN article for those who are NOT DBA, yet want to have proper backups for their databases.
Show all of the RMAN parameters for the database
RMAN> show all;
Configure the disk channel to the format /backup/ora_df%t_s%s_s%p’
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;
Configure the disk channel to the format +FLASHDG
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘+FLASHDG’;
Backup the database using the standard RMAN parameters
RMAN> backup database;
List all of the current backups for the database
RMAN> list backup;
List Backup summary
RMAN> list summary;
Crosscheck backups to see if they still exist
RMAN> crosscheck backup;
Crosscheck archive logs to see if they still exist
RMAN> crosscheck archivelog all;
Remove any expired backup
RMAN> delete expired backup;
Remove any expired archivelos
RMAN> delete expired archivelog;
Backup the database and archivelogs using the standard RMAN parameters and delete the archivelogs after the backup
RMAN> backup archivelog all delete input;
3> backup database;
4> backup …
View original post 270 more words