Mani's Blog

November 16, 2011

Sample Oracle 11g init.ora file for OLTP

Filed under: Uncategorized — mani @ 12:03 am

This is sample init.ora parameter file for OLTP application.   The stand-alone DB is configured with 32GB memory.

*._replace_virtual_columns=FALSE
*._shared_io_pool_size=0
*._simple_view_merging=TRUE
*.audit_file_dest=’/local/opt/oracle/admin/DBNAME/adump’
*.audit_trail=’NONE’
*.compatible=’11.1.0.0.0′
*.control_files=’+XN_DATA/DBNAME/controlfile/control01.ctl’,’+XN_DATA/DBNAME/controlfile/control02.ctl’,’+XN_DATA/DBNAME/controlfile/control03.ctl’
*.cursor_sharing=’SIMILAR’
*.db_32k_cache_size=2147483648
*.db_block_size=8192
*.db_cache_size=12884901888
*.db_create_file_dest=’+XN_DATA’
*.db_create_online_log_dest_1=’+XN_DATA’
*.db_domain=’xxx.com’
*.db_file_multiblock_read_count=16
*.db_name=’DBNAME’
*.db_recovery_file_dest=’/local/data/oracle/nfs’
*.db_recovery_file_dest_size=1610612736000
*.db_writer_processes=2
*.diagnostic_dest=’/local/opt/oracle’
*.filesystemio_options=’setall’
*.java_pool_size=268435456
*.job_queue_processes=10
*.large_pool_size=268435456
*.log_archive_dest_1=’location=/arch/DBNAME’
*.log_archive_format=’%d-%S_%T_%r.oarl’
*.memory_max_target=28991029248
*.memory_target=0
*.open_cursors=1000
*.optimizer_mode=’ALL_ROWS’
*.pga_aggregate_target=3221225472
*.processes=1000
*.remote_login_passwordfile=’exclusive’
*.resource_manager_plan=”
*.service_names=’xxx’,’yyy’
*.sessions=1105
*.sga_max_size=26843545600
*.sga_target=0
*.shared_pool_size=5368709120
*.streams_pool_size=0
*.undo_tablespace=’UNDOTBS1′

November 15, 2011

Sample MySQL my.cnf file for OLTP

Filed under: Database,MySQL — mani @ 11:54 pm

This is sample my.cnf file for MySQL server running on 32G memory and 200G data for OLTP operations.   With Queries per second avg: 1321.714 and 35 threads.  Configured with Statement based replication.

[client]
socket = /tmp/mysql.sock
port = 3306

[mysql]
prompt = (\\u@\\h) [\\d]>\\_
default-character-set = utf8

[mysqld]
core-file
socket = /tmp/mysql.sock
port = 3306
pid-file = /local/mysql/data/mysqld.pid
old-passwords = 0
skip-name-resolve
#basedir = /usr/bin
datadir = /local/mysql/data
back_log = 100
max_connections = 1000
max_connect_errors = 10000
key_buffer_size = 16M
myisam_sort_buffer_size = 32M
max_tmp_tables = 64
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 3000
query_cache_limit = 16M
query_cache_size = 0M
query_cache_type = 0
thread_cache = 50
thread_stack = 512k
wait_timeout = 500
interactive_timeout = 500
net_read_timeout = 15
net_write_timeout = 30
tmp_table_size = 64M
max_heap_table_size = 512M
group_concat_max_len = 5120
max_allowed_packet = 256M
local_infile = 0
character_set_client = utf8
character_set_server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake

performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

slow-query-log = 1
long_query_time = 1
log-queries-not-using-indexes
slow-query-log-file = /var/log/mysqld/mysqld.slow.log

default_storage_engine = INNODB
innodb_file_format = barracuda
innodb_file_per_table = 1
#innodb_use_native_aio = 0

innodb_log_group_home_dir = /local/mysql/data
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

transaction_isolation = REPEATABLE-READ
innodb_data_home_dir = /local/mysql/data
innodb_data_file_path = ibdata1:512M:autoextend
innodb_buffer_pool_size = 24G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 20M
innodb_doublewrite = 1
innodb_checksums = 1
innodb_io_capacity = 400
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_locks_unsafe_for_binlog = 0
innodb_max_purge_lag = 0
innodb_fast_shutdown = 0

# replication
server-id = 1
sync_binlog = 1
binlog-format = STATEMENT
log-bin=/local/mysql/mojo/binary-logs
binlog_cache_size=262144
log_slave_updates
#skip-slave-start
relay-log=/local/mysql/mojo/mysqld-relay-bin
relay-log-index=/local/mysql/mojo/mysqld-relay-bin

[mysqld_safe]
log-error = /var/log/mysqld/mysqld.err
open-files-limit = 65535

Guidelines for Oracle RAC Network Requirements

Filed under: Database,Oracle,RAC — mani @ 6:25 pm

Below is some guidelines recommended for Network requirements for Oracle RAC setup in Oracle RAC linked group. Hope it would be useful for others.

Here it is:

1. A physical database server must have at least 2 physical NICs with at least 2 ports on each NIC to avoid having a SPOF on any one NIC.

2. A physical database server must have at least 2 physical HBAs with at least 2 ports on each HBA if SAN is being used for data storage.

3. If data traffic and cluster inter-node communication are combined on the same NICs, then:
a. Multiple VLANs must be created
b. VLAN tagging and Quality of Service (QoS) must both be used.
c. Cluster inter-node communication must be separated from public traffic on different VLANs.

4. Automated failover (such as Etherchannel, 802.3ad), must be used for primary/secondary failover of each NIC.

5. Independent network switches must be configured so that during failover from the primary to secondary NIC, a different network switch will also be used.

6. Network link aggregation may be used to combine network ports into a larger network channel, but this link itself must not be a SPOF. For example, if using 2 dual port NICs, then port A on NIC 1 must be aggregated to port B on the same NIC. NIC 2 must be configured to support failover of both port A and port B from NIC 1.

7. The server and networking infrastructure must be configured to allow multicast communications and large IP packets.

November 14, 2011

ORA-00444: background process “MMAN” failed while starting

Filed under: Database,Oracle — mani @ 10:33 pm

Generally we run into this error while restoring database or other instances of starting the database.

RMAN> startup nomount

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/14/2011 22:09:05
RMAN-04014: startup failed: ORA-00444: background process “MMAN” failed while starting
ORA-07446: sdnfy: bad value ” for parameter .

RMAN> exit

Cause: 

Alert log is not able write into the log since directory defined in init.ora file doesn’t exist.

*.background_dump_dest=’/local/opt/oracle/admin/dsp0001/bdump’

For example directory  ‘/local/opt/oracle/admin/dsp0001/bdump’ doesn’t exist.

Solutiion: 

Create the directory that is missing and start the database again.

mkdir -p /local/opt/oracle/admin/dsp0001/bdump

November 10, 2011

Managing Oracle Exadata with Oracle Enterprise Manager 12c

Filed under: Database,Enterprise Manager,Oracle — mani @ 5:00 pm

Managing Oracle Exadata with Oracle Enterprise Manager 12c

Download the presentation.

Managing_Exadata_with_Enterprise_Manager_12c

Upgrade MySQL 5.5.16

Filed under: MySQL,Upgrade — mani @ 6:52 am
Tags:

Overview

We upgraded our environment to Enterprise Edition.  Upgrading MySQL process pretty much simple process.   However there are some know issues, even there is bug in upgrading performance schema.

  1. Download binaries from http://www.oracle.com
    MySQL-*-5.5.16*
  2. copy them to local Filesystems.
    cd /usr/local/src
    cp /local/mysql/backups/MySQL_Software/5.5.16_ent/* .
  3. Stop the mysql
    /etc/init.d/mysql stop
  4. Make sure mysql is not running
    ps -ef |grep mysqld |grep -v grep
  5. Uninstall existing MySQL binaries
    for x in `rpm -qa |grep MySQL |grep "5.5"`;
    do
    echo "rpm -e ${x}";
    done
  6. Install new 5.5.16 binaries
    rpm -ivh --force --nodeps MySQL-client-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-shared-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-devel-advanced-5.5.16-1.rhel5.x86_64.rpm
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  7. Start Mysql
    /etc/init.d/mysql start
  8. Run mysql_upgrade
    mysql_upgrade --skip-write-binlog -uroot -p
  9. Stop the mysql
    /etc/init.d/mysql stop
  10. Copy 5.5.16 performance_schema directory from one of the upgraded database.
    rm -rf /local/mysql/data/performance_schema
    cp -rp /local/mysql/backups/performance_schema /local/mysql/data
    chown -R mysql:mysql /local/mysql/data/performance_schema
  11. Start Mysql
    /etc/init.d/mysql start
  12. Check Performance_schema
    use performance_schema;
    select * from cond_instances;
  13. check the error log.
    tail -f /var/log/mysqld/mysql.err
  14. validate the replication.
    show slave status\G

Know issues and fixes

  1. Failed to uninstall
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -e MySQL-shared-5.5.6_rc-1.rhel5
    error: Failed dependencies:
    libmysqlclient.so.16()(64bit) is needed by (installed) php-mysql-5.2.10-11.0.x86_64
    Fix: Just Ignore
  2. While running mysql_upgrade
    Issue: Error : Can't get stat of './mysql/general_log.CSV' (Errcode: 2)
    Error : Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
    error : Corrupt
    Cause: .frm file is there for the table, not data file
    Fix: touch the file and rerun the upgrade
    touch /local/mysql/data/mysql/general_log.CSV
  3. While running mysql_upgrade
    Issue:
    ERROR 1194 (HY000) at line 719: Table 'general_log' is marked as crashed and should be repaired
    ERROR 1194 (HY000) at line 730: Table 'slow_log' is marked as crashed and should be repaired
    FATAL ERROR: Upgrade failed
    Fix: Run repair on these tables.
    repair table general_log;
    repair table slow_log;
  4. During the install
    Issue:
    root@@xnomdb912n1:/usr/local/src#rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
    Preparing... ########################################### [100%]
    Some previous upgrade was not finished:
    -rw-r--r-- 1 root root 214 Oct 10 21:42 /var/lib/mysql/RPM_UPGRADE_MARKER
    Please check its status, then do
        rm /var/lib/mysql/RPM_UPGRADE_MARKER
    before repeating the MySQL upgrade.
    error: %pre(MySQL-server-advanced-5.5.16-1.rhel5.x86_64) scriptlet failed, exit status 1
    error: install: %pre scriptlet failed (2), skipping MySQL-server-advanced-5.5.16-1.rhel5
    Fix: Remove the upgrade marker and run the install again.
    rm /var/lib/mysql/RPM_UPGRADE_MARKER
    rpm -ivh --force --nodeps MySQL-server-advanced-5.5.16-1.rhel5.x86_64.rpm
  5. While start up mysql
    Issue: 
    root@@xnamdb01n2:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL. ERROR! The server quit without updating PID file (/local/mysql/data/xnamdb01n2.ningops.com.pid).
    Error logs says:
    /usr/sbin/mysqld: File '/local/mysql/mojo/binary-logs.index' not found (Errcode: 13)
    111017 18:32:25 [ERROR] Aborting
    Fix: correct the privileges
    example:
    root@@xnamdb01n2:/local/mysql/mojo#chown mysql:mysql *
    /local/mysql/mojo#/etc/init.d/mysql start
  6. Replication error
    Issue: replication broken since we touched the file *.CSV
    Fix: skip the counter and start the slave.
    stop slave; set global sql_slave_skip_counter=1;start slave;
    show slave status\G
  7. Performance_schema issue
    Issue: wrong structure error when you read Performance_schema table.
    Fix: No fix yet.  Bug #13146778 is filed my Oracle support.
    Workaround: Copy upgraded performance-schema directory to the database and start MySQL.
    Installation steps are modified accordingly.
  8. Issue while MySQL startup on one or 2 databases.
    Issue: Error log shows.
    111024 23:16:04 InnoDB: Completed initialization of buffer pool
    InnoDB: Unable to lock /local/mysql/data/ibdata1, error: 11
    InnoDB: Check that you do not already have another mysqld process
    Solution: killall -9 mysqld_safe mysqld
    then /etc/init.d/mysql start
  9. PID file couldn’t be found
    Issue: I was trying to stop MySQL, but error reports as below.
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
     ERROR! MySQL server PID file could not be found!
    Fix: Only option worked was  
    root@@xnomdbcmn011n1:/usr/local/src#killall -9 mysqld_safe mysqld
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql start
    Starting MySQL.......... SUCCESS!
    root@@xnomdbcmn011n1:/usr/local/src#/etc/init.d/mysql stop
    Shutting down MySQL......... SUCCESS!

MySQL: Point_In_Time Recovery using Xtrabackup

Filed under: Backup,MySQL,Xtrabackup — mani @ 6:11 am

Point_In_Time Recovery

This blog would be helpful If you are using XtraBackup to backup MySQL databases and try to restore and recover databases.  This recovery would be needed when user/admin commits errors like running any statements(truncate table, drop table, etc.) mistakenly. In this case you may need to take your DB to Point-in-Time before the incident.

  1. Restore the database from selected backup. Backup file location may be different for everyone. /local/mysql/data is datadir location.
    cd /local/mysql/data
    tar -ixvzf /local/mysql/backups/xnomdb001n2/2011-10-26_01-00-02_35569.tar.gz
  2. Apply the log
    cd /local/mysql/data
    innobackupex-1.5.1 --apply-log ./
  3. Fix the privileges
    chown -R mysql:mysql *
  4. Start MySQL
    /etc/init.d/mysql start
  5. Identify the binlog file and position
    root@@xnomdb912n2:/local/mysql/data#cat xtrabackup_binlog_info
    binary-logs.000138      36567332
  6. Capture binlog files info from current production server
    (root@localhost) [(none)]> show binary logs;
    +--------------------+------------+
    | Log_name           | File_size  |
    +--------------------+------------+
    | binary-logs.000131 |    9774400 |
    | binary-logs.000132 | 1073742520 |
    | binary-logs.000133 | 1073741997 |
    | binary-logs.000134 | 1073743592 |
    | binary-logs.000135 | 1073745994 |
    | binary-logs.000136 | 1073742312 |
    | binary-logs.000137 | 1073742367 |
    | binary-logs.000138 | 1073742549 |
    | binary-logs.000139 | 1073760891 |
    | binary-logs.000140 550608024 |
    +--------------------+------------+
    10 rows in set (0.00 sec)
  7. Identify the current binlog position from the production.
    (root@localhost) [(none)]> show master status;
    +--------------------+-----------+--------------+------------------+
    | File               | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
    +--------------------+-----------+--------------+------------------+
    | binary-logs.000140 | 550917898 |              |                  |
    +--------------------+-----------+--------------+------------------+
    1 row in set (0.00 sec)
    (root@localhost) [(none)]>
  8. scp the required binlogs files to recovery server
    Example:
    scp binary-logs.000138 root@xnomdb912n2:/local/mysql/mojo
    scp binary-logs.000139 root@xnomdb912n2:/local/mysql/mojo
  9. Generate SQL file off of binlog, which generates all transaction statements into a file. Look at the file and find position and/or time, so the DB can be recovered to that point-in-time.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138   /local/mysql/mojo/binary-logs.000139 --start-position=36567332 > /local/mysql/mojo/mybinlog.sql
  10. Apply transactions to MySQL engine.
    mysqlbinlog /local/mysql/mojo/binary-logs.000138 /local/mysql/mojo/binary-logs.000139 --start-position=36567332 --stop-datetime="11-10-26 3:13:51" | mysql -u root -p

Keepalived Install, setup and usage

Filed under: MySQL — mani @ 1:13 am
Tags: , , ,

Overview

keepalived may help in failovers for a Multi-Master MySQL cluster.

What is Keepalived (http://www.keepalived.org/)

Keepalived is a utility that provides interface failover, it is virtual IP, and also can perform health checks. In the MySQL world and when using Multi-Master replication this is a very good mechanism to have. With a good implementation of Keepalived you will be able to fail over a virtual/floating IP address when the master (write) server becomes unavailable and switch that IP over the hot standby server.

Steps to set it up

If you have never installed keepalived please follow the steps below.

1. Get the tarball

# wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
--2010-08-19 10:47:35--  http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
Resolving www.keepalived.org... 188.165.36.82
Connecting to www.keepalived.org|188.165.36.82|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 233002 (228K) [application/x-gzip]
Saving to: `keepalived-1.1.20.tar.gz'

100%[======================================================================================>] 233,002      233K/s   in 1.0s

2010-08-19 10:47:37 (233 KB/s) - `keepalived-1.1.20.tar.gz' saved [233002/233002]

2. Untar and unzip the file

# /usr/local/src#tar xzvf keepalived-1.1.20.tar.gz

3. cd to the newly created directory

# /usr/local/src#cd keepalived-1.1.20

4. Make sure you have at a minium openssl-devel installed on your server

# /usr/local/src/keepalived-1.1.20#yum install openssl-devel.x86_64

5. If you want them all please run the following

yum -y install kernel-headers kernel-devel

6. Configure keepalived

# /usr/local/src/keepalived-1.1.20#./configure
checking for gcc... gcc
checking for C compiler default output file name... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking for a BSD-compatible install... /usr/bin/install -c
checking for strip... strip
checking how to run the C preprocessor... gcc -E
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for ANSI C header files... yes
checking for sys/wait.h that is POSIX.1 compatible... yes
checking for uname... yes

…

configure: creating ./config.status
config.status: creating Makefile
config.status: creating genhash/Makefile
config.status: creating keepalived/core/Makefile
config.status: creating keepalived/include/config.h
config.status: creating keepalived.spec
config.status: creating keepalived/Makefile
config.status: creating lib/Makefile
config.status: creating keepalived/vrrp/Makefile

Keepalived configuration
------------------------
Keepalived version       : 1.1.20
Compiler                 : gcc
Compiler flags           : -g -O2
Extra Lib                : -lpopt -lssl -lcrypto
Use IPVS Framework       : No
IPVS sync daemon support : No
Use VRRP Framework       : Yes
Use Debug flags          : No

7. Run Make

# /usr/local/src/keepalived-1.1.20#make
make -C lib || exit 1;
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/lib'
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c memory.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c utils.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c notify.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c timer.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c scheduler.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c vector.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c list.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c html.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c parser.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c signals.c
gcc -g -O2 -I. -Wall -Wunused -Wstrict-prototypes  -c logger.c

…

gcc -g -O2  -I/usr/src/linux/include -I../include -I../../lib -Wall -Wunused -Wstrict-prototypes -D_KRNL_2_6_ -D_WITHOUT_LVS_ -D_WITHOUT_IPVS_SYNCD_  -c vrrp_track.c

…

make[2]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived/vrrp'
Building ../bin/keepalived
strip ../bin/keepalived

Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
gcc -g -O2  -I/usr/src/linux/include -I../lib -Wall -Wunused -Wstrict-prototypes   -c -o main.o main.c

…

Building ../bin/genhash
strip ../bin/genhash

Make complete
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'

Make complete
# /usr/local/src/keepalived-1.1.20#make install
make -C keepalived install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/keepalived'
install -d /usr/local/sbin
install -m 700 ../bin/keepalived /usr/local/sbin/
install -d /usr/local/etc/rc.d/init.d
install -m 755 etc/init.d/keepalived.init /usr/local/etc/rc.d/init.d/keepalived
install -d /usr/local/etc/sysconfig
install -m 755 etc/init.d/keepalived.sysconfig /usr/local/etc/sysconfig/keepalived
install -d /usr/local/etc/keepalived/samples
install -m 644 etc/keepalived/keepalived.conf /usr/local/etc/keepalived/
install -m 644 ../doc/samples/* /usr/local/etc/keepalived/samples/
install -d /usr/local/share/man/man5
install -d /usr/local/share/man/man8
install -m 644 ../doc/man/man5/keepalived.conf.5 /usr/local/share/man/man5
install -m 644 ../doc/man/man8/keepalived.8 /usr/local/share/man/man8
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/keepalived'
make -C genhash install
make[1]: Entering directory `/usr/local/src/keepalived-1.1.20/genhash'
install -d /usr/local/bin
install -m 755 ../bin/genhash /usr/local/bin/
install -d /usr/local/share/man/man1
install -m 644 ../doc/man/man1/genhash.1 /usr/local/share/man/man1
make[1]: Leaving directory `/usr/local/src/keepalived-1.1.20/genhash'

8. Type the following commands to create the service and run level:

cd /etc/sysconfig
ln -s /usr/local/etc/sysconfig/keepalived .
cd /etc/rc3.d/
ln -s /usr/local/etc/rc.d/init.d/keepalived S100keepalived
cd /etc/init.d/
ln -s /usr/local/etc/rc.d/init.d/keepalived

The Configuration Files

Locate the main keepalived configuration file in the following directory, /usr/local/etc/keepalived. The file name should be obvious but if not then look for keepalived.conf. I like to make a backup of this file so I have something to reference really quick if I need it but this is not necessary.

The configuration file on the master (write) server will be slightly different than the configuration file on the slave (read) server. Below are the two example files:

MASTER CONFIGUTATION FILE

global_defs {
   router_id MYTEST
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 40
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        xx.xx.xx.xxx
    }
}

SLAVE CONFIGURATION FILE

global_defs {
   router_id MYTEST
}

vrrp_instance VI_1 {
    state BACKUP
    interface bond0
    virtual_router_id 40
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        xx.xx.xx.xxx
    }
}

After you have implemented the configuration files you should symlink them over to the /etc/keepalived directory. This is where the /etc/init.d/keepalived script looks for the configuration file.

# mkdir /etc/keepalived
# cd /etc/keepalived
# ln -s /usr/local/etc/keepalived/keepalived.conf .

You are now ready to start keepalived on the master server but before we do that you should check the output of the following for reference:

# /sbin/ip addr show bond0 |grep inet
 inet 999.999.9.100/23 brd 999.999.9.1 scope global bond0

Start keepalived by running the following on both the master and slave server. Start the master server first then the slave server.

# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]

You can check to see if the IP has been added to bond0 by running the ip addr line again.

Example for the Master server:

# /sbin/ip addr show bond0 |grep inet
 inet 999.999.9.100/23 brd 999.999.9.1 scope global bond0
inet 999.999.9.102/32 scope global bond0

You can also check /var/log/messages for good information about keepalived.

Example for the Master server:

# tail/var/log/messages
19 20:27:38 sandbox Keepalived: Starting VRRP child process, pid=10521
19 20:27:38 sandbox Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:27:39 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Entering MASTER STATE
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) setting protocol VIPs.
19 20:27:40 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 999.999.9.102
19 20:27:45 sandbox Keepalived_vrrp: VRRP_Instance(VI_1) Sending gratuitous ARPs on bond0 for 999.999.9.102

Example for the Slave server:

19 20:30:23 sandbox2 Keepalived_vrrp: Using LinkWatch kernel netlink reflector...
19 20:30:23 sandbox2 Keepalived_vrrp: VRRP sockpool: [ifindex(7), proto(112), fd(9,10)]
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Transition to MASTER STATE
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Received higher prio advert
19 20:30:24 sandbox2 Keepalived_vrrp: VRRP_Instance(VI_1) Entering BACKUP STATE

Testing

We can test our setup with a very simple ping. First you will need to have three shells open, one to the master server, one to the slave server and one running a ping to the virtual IP 999.999.9.102. While the ping is running you can simply stop keepalived and watch as the virtual IP flips over to the slave server. You should use the same techniques described above to check if the virtual IP has switched over to the slave server.

A ping test is the easiest way to test if the failover is going to work but it is NOT the only test you need to run. Running a simple BASH script that connects to MySQL on the Virtual IP (999.999.9.102) is a good way to test. Here is a VERY simple test for mysql:

# while true; do mysql –usomeuser --host=999.999.9.102 --port=3306 –e “select 1”; sleep .5; done

So instead of running a ping run the following script and see what the results are. I would expect that the Virtual IP would be flipped over to the slave server and the script above would not error out. Note that this is a very simple test and if you are implementing keepalived in production you should test with your production load.

MySQL Internal Check Script

#*/1 * * * * /bin/sh /var/mysql_support/mysqlping.sh >> /var/mysql_support/mysqlping.log 2>&1

#!/bin/sh
PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin:/usr/local/sbin; export PATH
uname="script_user"
pass=""  #no password given
/bin/date
for sock in `cat /etc/my.sockets`
do
#echo ;echo "Socket: $sock"; echo;
check=`/usr/local/mysql/bin/mysqladmin -u$uname -p$pass -S /tmp/${sock} ping`
echo; echo $check; echo;
  if [[ "$check" != "mysqld is alive" ]]; then
    ip addr show dev bond0 | grep xxx.xxx.xxx.xxx/xx >/dev/null 2>&1 ##  replace with your vip
    if [ $? -eq 0 ]; then
      echo "Killing Keepalived"
      /usr/bin/killall keepalived
    else
      echo "MySQL is down but no VIP present"
    fi
  elif [ "$check" == "mysqld is alive" ]; then
    echo "Socket: $sock = OK"
  fi
done

November 4, 2011

RMAN-00571 RMAN-00569 RMAN-06026 RMAN-06023: no backup or copy of datafile 4 found to restore

Filed under: Backup,Oracle,RMAN — mani @ 6:22 pm

Issue: I tried to restore one of our RMAN backups to our standby server to make sure the backups are good to recover.  I ran into below errors.

Error:

released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/04/2011 16:48:56
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Fix: As usual tried checking permission on ASM, access to backup files, CROSSCHECK backupset, Delete obsolete and few others.  Finally noticed that database was set to incorrect incarnation.  I had to reset it to the correct one.  Run “LIST INCARNATION;” on RMAN prompt.  Then choose the right incarnation. After the reset of database to right incarnation, restore and recover went well.
RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

allocated channel: d1
channel d1: SID=10980 device type=DISK

allocated channel: d2
channel d2: SID=10983 device type=DISK

allocated channel: d3
channel d3: SID=10986 device type=DISK

allocated channel: d4
channel d4: SID=10981 device type=DISK

Starting restore at 04-NOV-11

channel d1: starting datafile backup set restore
channel d1: specifying datafile(s) to restore from backup set
channel d1: restoring datafile 00002 to +XN_DATA/xnodbcor/datafile/sysaux.257.715025373
channel d1: restoring datafile 00003 to +XN_DATA/xnodbcor/datafile/undotbs1.258.715025373
channel d1: restoring datafile 00004 to +XN_DATA/xnodbcor/datafile/users.286.766341725
channel d1: restoring datafile 00006 to +XN_DATA/xnodbcor/datafile/undotbs3.265.715025525
channel d1: restoring datafile 00012 to +XN_DATA/xnodbcor/datafile/xn_opensocial.266.715203251
channel d1: restoring datafile 00013 to +XN_DATA/xnodbcor/datafile/content_data.271.715203641
channel d1: restoring datafile 00017 to +XN_DATA/xnodbcor/datafile/xn_index.275.715221723
channel d1: reading from backup piece /local/data/oracle/nfs/XNODBCOR/backupset/2011_11_04/o1_mf_nnndf_BKP_XNODBCOR_FULL_11_7c6rtl40_.bkp
channel d2: starting datafile backup set restore
channel d2: specifying datafile(s) to restore from backup set

RMAN-00571 RMAN-00569 RMAN-03002 ORA-19870 error while restoring backup piece

Filed under: Backup,Oracle,RMAN — mani @ 5:44 pm

I ran into issue while restoring RMAN backup to standby server.

Issue:

channel d4: restoring datafile 00020 to +XN_DATA
channel d4: reading from backup piece /local/data/oracle/nfs/XNODBCOR/backupset/2011_07_28/o1_mf_nnndf_BKP_XNODBCOR_FULL_07_731qqngm_.bkp
released channel: d1
released channel: d2
released channel: d3
released channel: d4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/03/2011 22:32:35
ORA-19870: error while restoring backup piece /local/data/oracle/nfs/XNODBCOR/backupset/2011_07_28/o1_mf_nnndf_BKP_XNODBCOR_FULL_07_731qqng5_.bkp
ORA-00600: internal error code, arguments: [krbrrd_comp_ksfqrd_noblks], [/local/data/oracle/nfs/XNODBCOR/backupset/2011_07_28/o1_mf_nnndf_BKP_XNODBCOR_FULL_07_731qqng5_.bkp], [125696], [], [], [], [], [], [], [], [], []

RMAN>

Recovery Manager complete.
Solution:

Oracle doc says, It occurs due to corrupt backup piece or some issues with reading from the OS layer.
Try to validate the backupset that was thrown by the error.

RMAN > validate backupset <bs_key>

BUT, in our case, I realized the backupset which is referred in the restored has pretty old time stamp. I had to change the incarnation to restore and recover successfully.

Run “list incarnation;” and switched to the correct one as below.

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

Next Page »

Create a free website or blog at WordPress.com.