CategoryDatabases

MySQL an introduction and basic tutorial

MySQL is a Open source database management systems. It ‘s a powerful database management system with a lot of flexibility. This tutorial covers basic introduction to MySQL. In this tutorial I’m using Centos operating system for installing MySQL.

Installing MySQL in centos

MySQL can be installed using yum repository, and can be installed using the below comment.

#yum install mysql-server

image

Once the installation is complete, you can start mysql using the below comment.

#/etc/init.d/mysqld start

No password is set by default during MySQL installation.

For setting up password for MySQL for the first time use mysqladmin to set root password

mysqladmin -u root password NEWPASSWORD

eg: mysqladmin –u root password Password123

Accessing MySQL Shell

MySQL prompt can be accessed using the below command

#mysql –u root –p

and enter the MySQL password created.

image

Now we are in MySQL prompt.

How to list databases?

type ‘show databases’ at mysql prompt.

image

How to create a database?

Syntax: create database <dbname>

image

How to access a database?

Syntax: use <dbname>

image

How to delete a database?

Syntax: drop database <dbname>

image

Most commonly used opensource databases

Almost all developers has his/her own favorite databases, here I’m going have a small insights on the commonly used Opensource databases.

Here’s the list of most commonly used Opensource databases

  • MySQL
  • PostgreSQl
  • MongoDB
  • SQLite

There are a  lot more databases, here I’m planning have a short intro on the above mentioned databases.

MySQL

According to wikipedia,

“MySQL is the world’s most widely used open-source Relational Database Management Systems (RDMS) that runs as a server providing multi-user access to a number of databases.”

MySQLThe MySQL development project has made its source code available under the terms of the GNU General Public License.  MySQL was owned and sponsored by a Swedish company MySQLAB, now owned by Oracle Corporation.

MySQL downloads can be obtained from http://www.mysql.com/downloads/

The latest version of MySQL is MySQL 5.6, the latest whitepaper on MySQL can be obtained from http://www.mysql.com/why-mysql/white-papers/whats-new-mysql-5-6/

PostgreSQL

PostgreSQL is a powerful, opensource object-relational database system. It runs on all major operating systems, including Linux, Windows, Unix flavours such as AIX, BSD, HP-UX. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

PostgreSQL It includes most SQL:2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video.

Some general PostgreSQL limits are included in the table below.

              Limit  Value
Maximum Database Size Unlimited
Maximum Table Size 32 TB
Maximum Row Size 1.6 TB
Maximum Field Size 1 GB
Maximum Rows per Table Unlimited
Maximum Columns per Table 250 – 1600 depending on column types
Maximum Indexes per Table Unlimited

PostgreSQL can be download here for free.

MongoDB

mongoMongoDB (from “humongous”) is an open-source document database, and the leading NoSQL database. Written in C++, MongoDB features:

  • Document-Oriented storage: JSON styled documents with dynamic schemas offer simplicity and power
  • Replication and High Availability
  • Auto -sharding: Scale without affecting functionality.
  • Querying: Rich, document based queries.
  • And more

Latest releases can be downloaded from here

SQLite

sqliteSQLite is a software library that implements a self-containedserverless,zero-configurationtransactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

And the downloads can be obtained from here.

Working with Oracle HTTP Server

In this blog i’m covering some basics on Oracle HTTP server(OHS) like

  • Checking OHS status.
  • Starting, stopping and restarting OHS.
  • Creating an Oracle HTTP server component.
  • Deleting an Oracle HTTP server component.

Checking OHS Status

We can determine the status of OHS by using the opmnctl command

$ORACLE_INSTANCE/bin/opmnctl status

Processes in Instance: instance1
---------------------------------+--------------------+---------+---------+-------
ias-component                    | process-type       |     pid | status | Ports
---------------------------------+--------------------+---------+---------+-------
ohs1                             | OHS                |    4789 | Alive  | https:1000, https:4444, http:7778

Starting, Stopping and restarting OHS

To Start use: >$ORACLE_INSTANCE/bin/opmnctl startall

To Stop use:  >$ORACLE_INSTANCE/bin/opmnctl stopproc process-type=OHS   or use  $ORACLE_INSTANCE/bin/opmnctl stopall

To restart: all Oracle HTTP Server components use: $ORACLE_INSTANCE/bin/opmnctl restartproc process-type=OHS

Creating an Oracle HTTP server component

The syntax for creating a Oracle HTTP server component is

$ORACLE_INSTANCE/bin/opmnctl createcomponent -componentType OHS -componentName component_name

For eg: to create an Oracle HTTP Server component named ohs1, use the following command:

>$ORACLE_INSTANCE/bin/opmnctl createcomponent -componentType OHS -componentName ohs1

When you create the Oracle HTTP Server component, ports are automatically assigned. However, you can use the following parameters to specify the ports of your choice:

-listenPort: HTTP listening port
-sslPort: HTTPS (SSL) listening port
-proxyPort: Proxy MBean port internally used by Oracle HTTP Server to communicate with Fusion Middleware Control

Deleting an Oracle HTTP server component

The syntax for deleting an Oracle HTTP server component using opmnctl is

>$ORACLE_INSTANCE/bin/opmnctl deletecomponent -componentName component_name

For eg: to delete an Oracle HTTP Server component named ohs1 use the following command:

>$ORACLE_INSTANCE/bin/opmnctl deletecomponent -componentName ohs1

 

ORA-00845: MEMORY_TARGET not supported on this system

Getting ORA-00845: MEMORY_TARGET not supported on this system, while trying to start a database.

Error
sql> startup
ORA-00845: MEMORY_TARGET not supported on this system

Reason:

This is a very common error which you might face while starting up your oracle database. This happens when your system is out of temporary storage. While starting up the database oracle uses /dev/shm to store temporary files but when it runs out of memory you get this error.

/dev/shm is also know as tmpfs i.e. temporary file system which keeps all the file system in virtual memory to speed up several processes.

Solution:

Increase the size of /dev/shm

To check the size of /dev/shm
root@localhost# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 10G 4.4G 2.9G 61% /
tmpfs 504M 76K 504M 1% /dev/shm
/dev/sda1 194M 25M 160M 14% /boot

To increase the size
root@localhost# mount -o remount,size=3G /dev/shm
Verify the size
root@localhost# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 7.6G 4.4G 2.9G 61% /
tmpfs 3G 1007M 2.1G 33% /dev/shm
/dev/sda1 194M 25M 160M 14% /boot

To make permanent changes to your file system update your fstab
root@localhost# vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=3G 0 0

 

Update the new fstab file
root@localhost# mount -a
 

PostgreSQL database fixes “persistent denial-of-service” bug

Maintainers of the PostgreSQL open-source database have patched a vulnerability that allowed attackers to corrupt files and in some cases, execute malicious code on underlying servers.

The bug, categorized as CVE-2013-1899, opened users to “persistent denial-of-service” attacks, in which unauthenticated hackers could corrupt files in a way that caused the database server to crash and refuse to reboot. Affected servers could only be restarted by removing garbage text from the files or by restoring them from a backup. Versions 9.0, 9.1, and 9.2 are all vulnerable.

The bug also allowed limited users of a PostgreSQL database to escalate their privileges when it was configured in a way that assigned the same name to the user and the database. When those conditions are met “then this vulnerability may be used to temporarily set one configuration variable with the privileges of the superuser,” PostgreSQL maintainers wrote. Such users who also had the ability to save files to the system could also execute malicious code, except in cases where the database is running on the SELinux operating system.

The vulnerability was reported to maintainers on March 12. More details are available in this advisory.

Parameter processes in oracle DB

How to update parameter processes value in oracle Database

1. Login to oracle

$sqlplus SYS as SYSDBA

2. Check the current processes value

$SQL> show parameter processes;

3. update it to the desired value, for eg: updating to 500

$SQL>alter system set processes=500 scope=spfile;

$SQL> commit

$SQL>shutdown immediate

$SQL> startup

4. you are done, recheck the value.

 

Oracle Internet directory (OID) Installation steps

Here’s are the simple steps to create an OID instance.

1. Install and configure your database instance and make sure it is working with all listener and emctl ports up.

2. Install weblogic, no need to configure a domain right now.

3. Install idm or OID, during installation you are provided with an option of creating a schema and adding a new weblogic domain.

4. once you have create schema and configured weblogic server using the OID installation menu, it take another 30-50 minutes to complete the entire configuration of OID.

 

Planning to have a better guide with screenshots, will update it soon.

Cheers.

Connecting to a remote Oracle Database.

Local database

Connecting to a local database is easy, just use:

$ sqlplus dbUser/dbPassword@dbSid
Here’s the  syntax for connecting to a remote database using its SID:

$ sqlplus dbUser/dbPassword@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=remoteServer)(PORT=1521)))(CONNECT_DATA=(SID=dbSid)))’

ORA-12162 TNS:net service name is incorrectly specified

This error mainly occurs due to incorrect ORACLE_SID value set.  Make sure you have set the ORACLE_HOME & ORACLE_SID value set properly.

ORA-01034: ORACLE Not Available

What to do when we get  errors  like ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist.

Solution:

The ORA-01034 is  simple error, it means that your database is down!  Oracle oerr utility notes this about the ORA-01034 error:

ORA-01034: ORACLE not available

Cause:

 Oracle was not started up. Possible causes include the following:

  • The SGA requires more space than was allocated for it.
  • The operating-system variable pointing to the instance is improperly defined.

Action: 

Refer to accompanying messages for possible causes and correct the problem mentioned in the other messages. If Oracle has been initialized, then on some operating systems, verify that Oracle was linked correctly. See the platform specific Oracle documentation.

You may find that you are experiencing ORA-01034 when attempting to upgrade your Oracle database.ORA-01034 is thrown during the attempt to upgrade because there is an invalid entry in one of the following files:

  1. /etc/oratab
  2. /var/opt/oracle/oratab

The ORA-01034 is a result of a discrepancy between ORACLE_HOME andORACLE_SID

To resolve ORA-01034, be sure that the ORACLE_HOME and ORACLE_SIDproperly match within the files /etc/oratab or /var/opt/oracle/oratab .

 

© 2017 My techbook

Theme by Anders NorénUp ↑