How To : Recover the InnoDB Cluster from complete outage.

I’ve been playing with InnoDB cluster the whole weekend. on this post i’m trying to start my cluster from complete outage.

i don’t have idea which is the primary to bootstrap. using the manual way i know how to do it.
But now, i want to use MySQL Shell.

after shutdown all my 3 node then start it again. below is the current status. —>

How To : Creating an InnoDB Cluster From an Existing Group Replication Deployment

#MySQL #InnoDB cluster is a collection of products that work together to provide a complete High Availability solution for MySQL.
A group of MySQL servers can be configured to create a cluster using MySQL Shell.
In the default single-primary mode, the cluster of servers has a single read-write primary.
Multiple secondary servers are replicas of the primary.
Creating a cluster with at least three servers ensures a high availability cluster.
A client application is connected to the primary via MySQL Router.
If the primary fails, a secondary is automatically promoted to the role of primary,
and MySQL Router routes requests to the new primary.
Advanced users can also configure a cluster to have multiple-primaries.

On my previous post i already created my group replication, but to maximize the use feature of mysql router, we need a shell. —>

How To : Setup MySQL Group Replication

MySQL Group Replication is a MySQL Server plugin that enables you to create elastic, highly-available, fault-tolerant replication topologies.

Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.

There is a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view is updated accordingly. Sometimes servers can leave the group unexpectedly, in which case the failure detection mechanism detects this and notifies the group that the view has changed. This is all automatic.

As i prepare to roll out a 6 Node Group Replication in production, we are currently in the process of setting up our UAT environment.
It will be running 3 Node cluster.


Installing Oracle Database Software on Linux

Install Required Linux Packages for Oracle

i’ll be using CentOS 5.5 distro for this installation
cd /media/cdrom/CentOS
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
rpm -Uvh unixODBC-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-devel-0.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh sysstat-7.*

Operating System Configuration
> kernel requirement for 11g is 2.6.18
root@localhost app]# uname -rm
2.6.18-194.el5 x86_64

>RAM minimum of 1G
root@localhost app]# cat /proc/meminfo | grep MemTotal
MemTotal:       509404 kB
[root@localhost app]# cat /proc/meminfo | grep SwapTotal
SwapTotal:     1048568 kB
If you have less than 1GB of memory (between your RAM and SWAP), you can add temporary swap space by creating a temporary swap file. This way you do not have to use a raw device or even more drastic, rebuild your system.
As root, make a file that will act as additional swap space, let’s say about 1000MB:
# dd if=/dev/zero of=tempswap bs=1k count=1000000
Now we should change the file permissions: 
# chmod 600 tempswap
Finally we format the “partition” as swap and add it to the swap space: 
# mke2fs tempswap
# mkswap tempswap
# swapon tempswap
[root@localhost app]#  dd if=/dev/zero of=tempswap bs=1k count=1000000
1000000+0 records in
1000000+0 records out
1024000000 bytes (1.0 GB) copied, 9.16479 seconds, 112 MB/s
[root@localhost app]# chmod 600 tempswap
[root@localhost app]# mke2fs tempswap
mke2fs 1.39 (29-May-2006)
tempswap is not a block special device.
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
125184 inodes, 250000 blocks
12500 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=260046848
8 block groups
32768 blocks per group, 32768 fragments per group
15648 inodes per group
Superblock backups stored on blocks:
    32768, 98304, 163840, 229376

Writing inode tables: done                            
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 35 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@localhost app]# mkswap tempswap
Setting up swapspace version 1, size = 1023995 kB
[root@localhost app]# swapon tempswap

> edit sysctl.conf
# +———————————————————+
# | KERNEL PARAMETERS FOR ORACLE                         |
# +———————————————————+
# | Configure the kernel parameters for all Oracle Linux    |
# | servers by setting shared memory and semaphores,        |
# | setting the maximum amount of file handles, setting the |
# | networking parameters, and finally setting the IP local |
# | port range.                                             |
# +———————————————————+

# +———————————————————+
# | SHARED MEMORY                                           |
# +———————————————————+
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.shmall = 2097152

# +———————————————————+
# | SEMAPHORES                                              |
# | ———-                                              |
# |                                                         |
# | SEMMSL_value  SEMMNS_value  SEMOPM_value  SEMMNI_value  |
# |                                                         |
# +———————————————————+
kernel.sem = 250 32000 100 128

# +———————————————————+
# | FILE HANDLES                                            |
# ———————————————————-+
fs.file-max = 102696

# +———————————————————+
# | LOCAL IP RANGE                                          |
# ———————————————————-+
net.ipv4.ip_local_port_range = 1024 65000

# +———————————————————+
# | NETWORKING                                              |
# ———————————————————-+
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

>configure oracle shell limits
cat >> /etc/security/limits.conf <<EOF
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

cat >> /etc/pam.d/login <<EOF
# Added for Oracle Shell Limits
session    required     /lib/security/
session    required

Update the default shell startup file for the “oracle” UNIX account.

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file by running the following command:
cat >> /etc/profile <<EOF
if [ \$USER = “oracle” ]; then
    if [ \$SHELL = “/bin/ksh” ]; then
        ulimit -p 16384
        ulimit -n 65536
        ulimit -u 16384 -n 65536
    umask 022
For the C shell (csh or tcsh), add the following lines to the /etc/csh.login file by running the following command:
cat >> /etc/csh.login <<EOF
if ( \$USER == “oracle” ) then
    limit maxproc 16384
    limit descriptors 65536

Network Configuration
edit /etc/hosts
# that require network functionality will fail.            localhost.localdomain     localhost            lnxdg01

Create “oracle” User and Directories
> create group and oracle user
groupadd -g 501 oinstall
groupadd -g 502 dba
groupadd -g 503 oper
groupadd -g 504 asm
groupadd -g 506 asmdba
useradd -m -u 501 -g oinstall -G dba,oper,asm -d /home/oracle -s /bin/bash -c “Oracle Software Owner” oracle
id oracle

> check nobody id
[root@localhost tmp]# id nobody
uid=99(nobody) gid=99(nobody) groups=99(nobody)

> create oracle base directory
mkdir -p /opt/app/oracle
chown -R oracle:oinstall /opt/app
chmod -R 775 /opt/app

>configure oracle user
edit bash profile.
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc

alias ls=”ls -FA”
alias s=”screen -DRRS iPad -t iPad”

export JAVA_HOME=/usr/local/java

# User specific environment and startup programs
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_PATH=$ORACLE_BASE/dba_scripts/common/sql:.:$ORACLE_HOME/rdbms/admin

export PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/dba_scripts/common/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp
Disk Configuration

[root@localhost oracle]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.

The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e   extended
p   primary partition (1-4)
Partition number (1-4): 1
First cylinder (1-2610, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610):
Using default value 2610

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost oracle]# mkfs.ext3 -b 4096 /dev/sdb1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
2621440 inodes, 5241198 blocks
262059 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
160 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 34 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@localhost oracle]# mkdir /u01
[root@localhost oracle]# echo “/dev/sdb1   /u01   ext3   defaults   1 1” >> /etc/fstab
[root@localhost oracle]# mount /u01
[root@localhost oracle]# df -h
Filesystem            Size  Used Avail Use% Mounted on
                       19G  3.8G   14G  22% /
/dev/sda1              99M   13M   82M  14% /boot
tmpfs                 249M     0  249M   0% /dev/shm
/dev/sdb1              20G  173M   19G   1% /u01

Download the Oracle Database Software

go to and download the software.

Install the Oracle Database Software

To install the 11.1 database, and assuming you use the default oracle user, oinstall and dba groups, the variables you need to set are the following:
ORACLE_BASE defines the ORACLE_BASE value that is now mandatory with 11.1
ORACLE_HOME defines the location of the software you want to install;
ORACLE_HOME_NAME defines a name for the ORACLE_HOME and helps to avoid inadvertently deleting an ORACLE_HOME.

n_configurationOption defines if you want to configure ASM or create a database with DBCA as part of the install. Use the value “3″ install the software only.
FROM_LOCATION defines the location of the products.xml file and may
have to be set with That’s because for some platforms, the value of that variable is not set correctly in the default response files.
For more details about all the variables you can use as part of the runInstaller command, you can view the content of the response files located in $DISTRIB/response
Once you’ve decided what values you want to use to install the software, you can run runInstallerin silent mode WITHOUTchanging the content of the response file. Below is the command you can run to install the 11.1 database server Enterprise Edition:
–> for
$ runInstaller -silent                                 \
      -responseFile $DISTRIB/response/enterprise.rsp   \
       ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1 \
       ORACLE_HOME_NAME=ORADB102_Home10                 \

$ runInstaller -silent                                 \
      -responseFile $DISTRIB/response/enterprise.rsp   \
       FROM_LOCATION=$DISTRIB/stage/products.xml       \
       ORACLE_BASE=/opt/app/oracle                     \
       ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1 \
       ORACLE_HOME_NAME=ORADB111_Home1                 \

Once the software installed, you can create the oraInst.loc file if it’s the first Oracle software you install on the server. To proceed, connect as root and navigate to the newly created Oracle Inventory:
# cd /opt/app/oraInventory
# ./
Then run the script from the ORACLE_HOME you’ve created. Connect as root and run:
# cd /opt/app/oracle/product/10.2.0/db_1
# ./

Configure Oracle Networking
Here are some sample listener.ora files:
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    (SID_DESC =
      (SID_NAME = test01)
      (ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)


      (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))

start listener

Create the Oracle Database

in my installation DB is directory is /db<DBname> so i just created a link to /u01 > /dbTEST01
$ dbca -silent                           \
       -createDatabase                   \
       -templateName General_Purpose.dbc     \
       -gdbName TEST01                     \
       -sid TEST01                         \
       -SysPassword change_on_install    \
       -SystemPassword manager           \
       -emConfiguration NONE             \
       -datafileDestination /dbTEST01/u04/oradata \
       -storageType FS                   \
       -characterSet WE8ISO8859P15       \
       -memoryPercentage 40

> files system
dbca -silent                           \
       -createDatabase                   \
       -templateName General_Purpose.dbc     \
       -gdbName TEST01                     \
       -sid TEST01                         \
       -SysPassword change_on_install    \
       -SystemPassword manager           \
       -emConfiguration NONE             \
       -datafileDestination /dbTEST01/oradata \
       -redoLogFileSize 100              \
       -storageType FS                   \
       -characterSet WE8ISO8859P15       \
       -totalMemory 500

$ dbca -silent                             \
       -createDatabase                     \
       -templateName General_Purpose.dbc       \
       -gdbName TEST01                       \
       -sid TEST01                           \
       -SysPassword change_on_install      \
       -SystemPassword manager             \
       -emConfiguration NONE               \
       -redoLogFileSize 100                \
       -storageType ASM                    \
         -asmSysPassword change_on_install \
         -diskGroupName DGDATA             \
       -characterSet WE8ISO8859P15         \
       -totalMemory 500

Post-Installation Tasks

recompile invalid objects using utlrp.sql