Wednesday, November 18, 2015

How to grow xfs file system after extending the LVM Volume

[root@vmxdb01 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-root   36G   33G  3.7G  90% /
devtmpfs             1.6G     0  1.6G   0% /dev
tmpfs                2.0G  1.5G  582M  72% /dev/shm
tmpfs                1.6G  8.8M  1.6G   1% /run
tmpfs                1.6G     0  1.6G   0% /sys/fs/cgroup
/dev/sda1            997M  248M  749M  25% /boot

[root@vmxdb01 ~]# xfs_growfs /dev/mapper/ol-root
meta-data=/dev/mapper/ol-root    isize=256    agcount=4, agsize=2355968 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=0        finobt=0
data     =                       bsize=4096   blocks=9423872, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=0
log      =internal               bsize=4096   blocks=4601, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 9423872 to 12045312

[root@vmxdb01 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
/dev/mapper/ol-root   46G   33G   14G  71% /
devtmpfs             1.6G     0  1.6G   0% /dev
tmpfs                2.0G  1.5G  582M  72% /dev/shm
tmpfs                1.6G  8.8M  1.6G   1% /run
tmpfs                1.6G     0  1.6G   0% /sys/fs/cgroup
/dev/sda1            997M  248M  749M  25% /boot

Monday, November 9, 2015

Quick Steps on how to setup MariaDB Enterprise Cluster (Galera Cluster)

Repeat below steps on servers “mysql01”, “mysql02” & “mysql03”

[root@mysql01 ~]# groupadd -g 500 mysql
[root@mysql01 ~]# useradd -g mysql -u 500 -m -d /home/mysql mysql
[mysql@mysql01 my]$ cd /my
[mysql@mysql01 my]$ tar zxvf repo/mariadb-enterprise-cluster-10.0.21-linux-x86_64.tar.gz
[mysql@mysql01 my]$ ln -s mariadb-enterprise-cluster-10.0.21-linux-x86_64 mysql
[mysql@mysql01 mysql]$ mkdir /my/data

Follow the steps based on server name. (Sample my.cnf referring to end of this post)


[mysql@mysql01 mysql]$ cp mariadb-enterprise.cnf /my/my.cnf
[mysql@mysql01 mysql]$ ./scripts/mysql_install_db --defaults-file=/my/my.cnf

[mysql@mysql01 my]$ scp my.cnf  mysql02:/my
[mysql@mysql01 my]$ scp my.cnf  mysql03:/my
[mysql@mysql01 my]$ scp -rp data mysql02:/my/
[mysql@mysql01 my]$ scp -rp data mysql03:/my/
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep-new-cluster &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61 &
[mysql@mysql01 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql02 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql03 my]$ /my/mysql/bin/mysqladmin -u root -S /tmp/mysql.sock shutdown
[mysql@mysql01 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm:// &
[mysql@mysql02 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.63 &
[mysql@mysql03 my]$ /my/mysql/bin/mysqld --defaults-file=/my/my.cnf --wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62 &


MariaDB [(none)]> show status like 'wsre%';
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_local_state_uuid       | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_protocol_version       | 7                                                     |
| wsrep_last_committed         | 0                                                     |
| wsrep_replicated             | 0                                                     |
| wsrep_replicated_bytes       | 0                                                     |
| wsrep_repl_keys              | 0                                                     |
| wsrep_repl_keys_bytes        | 0                                                     |
| wsrep_repl_data_bytes        | 0                                                     |
| wsrep_repl_other_bytes       | 0                                                     |
| wsrep_received               | 10                                                    |
| wsrep_received_bytes         | 832                                                   |
| wsrep_local_commits          | 0                                                     |
| wsrep_local_cert_failures    | 0                                                     |
| wsrep_local_replays          | 0                                                     |
| wsrep_local_send_queue       | 0                                                     |
| wsrep_local_send_queue_max   | 1                                                     |
| wsrep_local_send_queue_min   | 0                                                     |
| wsrep_local_send_queue_avg   | 0.000000                                              |
| wsrep_local_recv_queue       | 0                                                     |
| wsrep_local_recv_queue_max   | 1                                                     |
| wsrep_local_recv_queue_min   | 0                                                     |
| wsrep_local_recv_queue_avg   | 0.000000                                              |
| wsrep_local_cached_downto    | 18446744073709551615                                  |
| wsrep_flow_control_paused_ns | 0                                                     |
| wsrep_flow_control_paused    | 0.000000                                              |
| wsrep_flow_control_sent      | 0                                                     |
| wsrep_flow_control_recv      | 0                                                     |
| wsrep_cert_deps_distance     | 0.000000                                              |
| wsrep_apply_oooe             | 0.000000                                              |
| wsrep_apply_oool             | 0.000000                                              |
| wsrep_apply_window           | 0.000000                                              |
| wsrep_commit_oooe            | 0.000000                                              |
| wsrep_commit_oool            | 0.000000                                              |
| wsrep_commit_window          | 0.000000                                              |
| wsrep_local_state            | 4                                                     |
| wsrep_local_state_comment    | Synced                                                |
| wsrep_cert_index_size        | 0                                                     |
| wsrep_causal_reads           | 0                                                     |
| wsrep_cert_interval          | 0.000000                                              |
| wsrep_incoming_addresses     | 192.168.6.61:3306,192.168.6.62:3306,192.168.6.63:3306 |
| wsrep_evs_delayed            |                                                       |
| wsrep_evs_evict_list         |                                                       |
| wsrep_evs_repl_latency       | 0/0/0/0/0                                             |
| wsrep_evs_state              | OPERATIONAL                                           |
| wsrep_gcomm_uuid             | ec8ccb15-86f0-11e5-9d6e-0632737bbcb8                  |
| wsrep_cluster_conf_id        | 3                                                     |
| wsrep_cluster_size           | 3                                                     |
| wsrep_cluster_state_uuid     | ec8dda0f-86f0-11e5-945a-53d2400df46b                  |
| wsrep_cluster_status         | Primary                                               |
| wsrep_connected              | ON                                                    |
| wsrep_local_bf_aborts        | 0                                                     |
| wsrep_local_index            | 0                                                     |
| wsrep_provider_name          | Galera                                                |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                     |
| wsrep_provider_version       | 25.3.9(r3387)                                         |
| wsrep_ready                  | ON                                                    |
| wsrep_thread_count           | 2                                                     |
+------------------------------+-------------------------------------------------------+
57 rows in set (0.00 sec)


Sample “/my/my.cnf” on mysql01.

[mariadb]
plugin-maturity=gamma

feedback-user-info='enterprise'

## -- Remove the following line to enable feedback reporting to mariadb.org
feedback-url=''

[mysqld]
# -- Disable for applications using old auth protocol
enable-secure-auth
# -- Auditing - pre-load Plugin
plugin-load=server_audit

###########################################
[mysqld]

# General
datadir=/my/data
basedir=/my/mysql
# bind_address = 127.0.0.1
skip-name-resolve
max-allowed-packet                       = 64M
open-files-limit                         = 65535
max-connect-errors                       = 100000
max-connections                          = 1000
thread-cache-size                        = 200
back-log                                 = 128
#net_buffer_length                       = 16K
#connect_timeout                         = 10
#net_read_timeout                        = 30
#net_write_timeout                       = 60
#interactive_timeout                     = 28800
#wait_timeout                            = 28800
#net_retry_count                         = 10
character-set-server                     = 'utf8'
collation-server                         = 'utf8_general_ci'
# -- Do not change these defaults, unless you really know.
#read_rnd_buffer_size                    = 256K
#sort_buffer_size                        = 2M
#read_buffer_size                        = 128K
#max_tmp_tables                          = 32
tmp-table-size                           = 32M
max-heap-table-size                      = 32M
# -- This will enable a stricter SQL mode to avoid bad data
sql-mode                                 = STRICT_TRANS_TABLES

optimizer_search_depth                   = 0
optimizer_switch                         = 'index_merge=on'
optimizer_switch                         = 'index_merge_union=on'
optimizer_switch                         = 'index_merge_sort_union=on'
optimizer_switch                         = 'index_merge_intersection=on'
optimizer_switch                         = 'index_merge_sort_intersection=off'
optimizer_switch                         = 'index_condition_pushdown=on'
optimizer_switch                         = 'derived_merge=on'
optimizer_switch                         = 'derived_with_keys=on'
optimizer_switch                         = 'firstmatch=on'
optimizer_switch                         = 'loosescan=on'
optimizer_switch                         = 'materialization=on'
optimizer_switch                         = 'in_to_exists=on'
optimizer_switch                         = 'semijoin=on'
optimizer_switch                         = 'partial_match_rowid_merge=on'
optimizer_switch                         = 'partial_match_table_scan=on'
optimizer_switch                         = 'subquery_cache=on,mrr=on'
optimizer_switch                         = 'mrr_cost_based=on'
optimizer_switch                         = 'mrr_sort_keys=off'
optimizer_switch                         = 'outer_join_with_cache=on'
optimizer_switch                         = 'semijoin_with_cache=on'
optimizer_switch                         = 'join_cache_incremental=on'
optimizer_switch                         = 'join_cache_hashed=on'
optimizer_switch                         = 'join_cache_bka=on'
optimizer_switch                         = 'optimize_join_buffer_size=on'
optimizer_switch                         = 'table_elimination=on'
optimizer_switch                         = 'extended_keys=on'

join-buffer-space-limit                  = 4M
join-cache-level                         = 6
join-buffer-size                         = 4M

# Security
symbolic-links                           = 0
local-infile                             = 0

# Logging
log-warnings                             = 2
slow-query-log                           = 1
long-query-time                          = 5
log-slow-verbosity                       = 'query_plan,innodb'
#log_slow_rate_limit                     = 1000

# Replication / Binary logs
# -- Set to unique number on each server participating in replication
server-id                                 = 1
slave_net_timeout                         = 60
#max_prepared_stmt_count                   = 16382
# -- Annotate queries as comments in binary log, for row based events (Breaks replication to MySQL)
#If you plan to downgrade to MySQL don’t set binlog_annotate_row_events to ON.
binlog-annotate-row-events               = ON
log-bin=mariadb-bin
# BIN.2  Binlog File
binlog-format                            = ROW
expire-logs-days                         = 7
max-binlog-size                          = 1024M
# -- Recommended for binary log durability. Expect 60% performance hit without write cache on controller
sync-binlog                              = 1
binlog-stmt-cache-size                   = 128K
binlog-cache-size                        = 256K
#slave_compressed_protocol                = ON
slave-transaction-retries                = 10
#relay_log_recovery                       = ON
#sync_master_info                         = 1
#sync_relay_log                           = 0
#sync_relay_log_info                      = 1

# InnoDB
default-storage-engine                   = 'InnoDB'
innodb-stats-on-metadata                 = 0
innodb-stats-sample-pages                = 32
table-definition-cache                   = 2048
table-open-cache                         = 2048
transaction-isolation                    = READ-COMMITTED
# To be set to 0 if not multi transactional storage engine
innodb-support-xa                        = ON

# Query Cache
query-cache-size                         = 0
query-cache-type                         = 0
innodb-buffer-pool-instances             = 8
# -- Set the following to maximum 60/70% of physical RAM.
innodb-buffer-pool-size                  = 2G
# innodb_max_dirty_pages_pct should be compute with redo log size in mind: It’s recommended to set the dirty pages smaller than redo log space. The more you have dirty page, the less io will be produced on disk.
innodb-max-dirty-pages-pct               = 50
innodb-file-per-table                    = 1
innodb-file-format                       = Barracuda
innodb-flush-log-at-trx-commit           = 1
#Save and restore buffer pool to be transparent for user
#innodb_flush_method                      = O_DIRECT
innodb-log-buffer-size                   = 64M
innodb-log-files-in-group                = 2
#innodb-log-file-size                     = 1024M
#innodb_purge_threads                     = 1
# -- Raid1: 200. Raid10: 200 x write arrays. SSD: 5000. FusionIO: 20000.
#innodb_io_capacity                      = 200
# -- SSD & FusionIO can flush pages in random disk order, keep default for spinning disks
#innodb_flush_neighbors                   = 0
# -- Increase these numbers to increase global throughput on SAN (16,32,64)
innodb-read-io-threads                   = 8
innodb-write-io-threads                  = 8
# -- Set this to 2-4 times # of Cpus, maximum recommended is 64.
# -- iostat report io service time: io_capacity / service_time is a good starting point for innodb_write_io_threads
# -- innodb_read_io_threads starting point monitoring status # read ahead per second
#innodb-thread-concurrency                = 64

innodb-open-files                       = 2048

# MyISAM
key-buffer-size                          = 64M
flush                                    = OFF
myisam-recover-options                   = BACKUP,FORCE
myisam-sort-buffer-size                  = 64M

# Other
# -- Enable INFORMATION_SCHEMA.%_STATISTICS tables
userstat                                 = ON
archive                                  = OFF
blackhole                                = OFF
#federated                               = OFF
#innodb                                  = FORCE

[mysql]
default-character-set                    = 'utf8'
auto-rehash                              = FALSE
local-infile                             = 1
max-allowed-packet                       = 64M
secure-auth                              = TRUE

[mysqldump]
max-allowed-packet                       = 1G
default-character-set                    = 'utf8'

[myisamchk]
key-buffer-size                          = 1G
sort-buffer-size                         = 1G
read-buffer-size                         = 8M
write-buffer-size                        = 8M

#
# * Galera-related settings
#

[galera]
# Mandatory settings:
# -- The path to the galea library, usually its
# -- /usr/lib(64)/galera/libgalera_smm.so
wsrep_provider=/my/mysql/lib/libgalera_smm.so
# -- Address of any single member node of the cluster. You can also specify
# -- addresses of multiple nodes in the cluster (recommended). It takes the
# -- address(es) in the following format : gcomm://ip[:port][,ip[:port]].
# -- An empty address (gcomm://) would cause the node to bootstrap.
# below Bootstrap for first time initialization on mysql01
#wsrep_cluster_address=gcomm://

#After this single-node cluster is started,
#variable wsrep_cluster_address should be updated to the list of all nodes in the cluster
wsrep_cluster_address=gcomm://192.168.6.61,192.168.6.62,192.168.6.63


# -- Binary log format for write sets.
binlog_format=row
# -- Default storage engine to create tables.
default_storage_engine=InnoDB
# -- interleaved lock-mode
innodb_autoinc_lock_mode=2
# -- Accept remote connections (for mysqldump state-transfer method).
bind-address=0.0.0.0
#
# Optional settings:
# -- Number of worker threads to apply replicated transactions in parallel.
#wsrep_slave_threads=1
# -- Do not write log buffer to log file at transaction commit, but once per
# -- second.
innodb_flush_log_at_trx_commit=0
# -- Do not allow to create table without primary key.
innodb_force_primary_key=1

VM Image Clone Problem: eth0 Renamed As eth1

After cloning a Linux image, noticed that Linux server renamed eth0 as eth1.

Further study discovered that this issue caused by previous entries in “70-persistent-net.rules”, after clean up these obsoleted lines related to previous image, it works perfect.

[root@mysql03 ~]# cp /etc/udev/rules.d/70-persistent-net.rules /root/
[root@mysql03 ~]# diff /etc/udev/rules.d/70-persistent-net.rules /root/70-persistent-net.rules
8c8,14
< SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:50:56:26:1e:69", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"
---

> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:0c:29:8c:f7:1e", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"
>
> # PCI device 0x8086:0x100f (e1000)
> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:0c:29:0d:4f:31", ATTR{type}=="1", KERNEL=="eth*", NAME="eth1"
>
> # PCI device 0x8086:0x100f (e1000)
> SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:50:56:26:1e:69", ATTR{type}=="1", KERNEL=="eth*", NAME="eth2"

How do I find which rpm package supplies a file I'm looking for?

[mysql@mysql01 lib]$ ldd /my/mysql/lib/libgalera_smm.so
        linux-vdso.so.1 =>  (0x00007fff1affe000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f6d4daa6000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f6d4d89e000)
        libssl.so.6 => not found
        libcrypto.so.6 => not found

        libm.so.6 => /lib64/libm.so.6 (0x00007f6d4d619000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f6d4d402000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f6d4d06e000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003094000000)

       
[mysql@mysql01 lib]$ yum whatprovides 'libssl.so.6'
Loaded plugins: security, ulninfo
openssl098e-0.9.8e-18.0.1.el6_5.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

openssl098e-0.9.8e-17.0.1.el6.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

openssl098e-0.9.8e-17.0.1.el6_2.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libssl.so.6

 

[mysql@mysql01 lib]$  yum whatprovides 'libcrypto.so.6'
Loaded plugins: security, ulninfo
openssl098e-0.9.8e-18.0.1.el6_5.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

 

openssl098e-0.9.8e-17.0.1.el6.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

 

openssl098e-0.9.8e-17.0.1.el6_2.2.i686 : A compatibility version of a general cryptography and TLS library
Repo        : public_ol6_latest
Matched from:
Other       : libcrypto.so.6

[root@mysql01 ~]# yum install openssl098e

[mysql@mysql01 lib]$ ldd /my/mysql/lib/libgalera_smm.so
        linux-vdso.so.1 =>  (0x00007fff418b9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f7b6dc57000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f7b6da4f000)
        libssl.so.6 => /usr/lib64/libssl.so.6 (0x00007f7b6d7ff000)
        libcrypto.so.6 => /usr/lib64/libcrypto.so.6 (0x00007f7b6d4a3000)

        libm.so.6 => /lib64/libm.so.6 (0x00007f7b6d21f000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f7b6d008000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f7b6cc74000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003094000000)
        libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f7b6ca30000)
        libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f7b6c748000)
        libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f7b6c544000)
        libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f7b6c318000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f7b6c113000)
        libz.so.1 => /lib64/libz.so.1 (0x00007f7b6befd000)
        libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f7b6bcf2000)
        libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f7b6baee000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f7b6b8d4000)
        libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f7b6b6b4000)

Thursday, October 29, 2015

AutoStart/Shutdown Oracle and Listener on RHEL7/OL7 using systemd

1. Create unit file in the /etc/systemd/system/ directory and make sure it has correct file permissions. Execute as root:
[root@em12c ~]#  touch /etc/systemd/system/Oracle_Listener_LISTENER.service 
[root@em12c ~]#  chmod 664 /etc/systemd/system/Oracle_Listener_LISTENER.service
2. Add the following content to the file: /etc/systemd/system/Oracle_Listener_LISTENER.service
[Unit]
Description=Oracle Listener (LISTENER)
After=network.target

[Service]
Type=forking
PIDFile=/home/oracle/scripts/LISTENER.pid
ExecStart=/bin/su - oracle -c "/home/oracle/scripts/lsnrstart.sh LISTENER"
ExecReload=/bin/su - oracle -c "/home/oracle/scripts/lsnrshut.sh LISTENER;/home/oracle/scripts/lsnrstart.sh LISTENER"
ExecStop=/bin/su - oracle -c "/home/oracle/scripts/lsnrshut.sh LISTENER"

[Install]
WantedBy=multi-user.target

3. Execute the following commands to reload the configuration and start the custom service:

[root@em12c ~]# systemctl daemon-reload
[root@em12c ~]# systemctl start Oracle_Listener_LISTENER.service [root@em12c ~]# systemctl enable Oracle_Listener_LISTENER.service


4. Create unit file in the /etc/systemd/system/ directory and make sure it has correct file permissions. Execute as root:
[root@em12c ~]#  touch /etc/systemd/system/Oracle_Instance_orcl.service [root@em12c ~]#  chmod 664 /etc/systemd/system/Oracle_Instance_orcl.service
5. Add the following content to the file: /etc/systemd/system/Oracle_Instance_orcl.service
[Unit]
Description=Oracle Instance (orcl)
After=network.target
Wants=Oracle_Listener_LISTENER.service

[Service]
Type=forking
PIDFile=/home/oracle/scripts/orcl.pid
ExecStart=/bin/su - oracle -c "/home/oracle/scripts/dbstart.sh orcl"
ExecReload=/bin/su - oracle -c "/home/oracle/scripts/dbshut.sh orcl;/home/oracle/scripts/lsnrstart.sh orcl"
ExecStop=/bin/su - oracle -c "/home/oracle/scripts/dbstart.sh orcl"

[Install]
WantedBy=multi-user.target


6. Execute the following commands to reload the configuration and start the custom service:

[root@em12c ~]# systemctl daemon-reload 
[root@em12c ~]# systemctl start Oracle_Instance_orcl.service
[root@em12c ~]# systemctl enable Oracle_Instance_orcl.service

7. Check the service status:
[root@em12c ~]# systemctl |grep -e Oracle -e DESCRIPTION
UNIT                               LOAD   ACTIVE SUB       DESCRIPTION
Oracle_Instance_orcl.service       loaded active running   Oracle Instance (orcl)
Oracle_Listener_LISTENER.service   loaded active running   Oracle Listener (LISTENER)

[root@em12c ~]# systemctl list-unit-files|grep -e Oracle -e STATE
UNIT FILE                                   STATE
Oracle_Instance_orcl.service                enabled
Oracle_Listener_LISTENER.service            enabled

[root@em12c ~]# systemctl status Oracle_Instance_orcl.service
Oracle_Instance_orcl.service - Oracle Instance (orcl)
   Loaded: loaded (/etc/systemd/system/Oracle_Instance_orcl.service; enabled)
   Active: active (running) since Thu 2015-10-29 00:15:20 SGT; 14s ago
  Process: 15214 ExecStart=/bin/su - oracle -c /home/oracle/scripts/dbstart.sh orcl (code=exited, status=0/SUCCESS)
Main PID: 15295 (ora_smon_orcl)
   CGroup: /system.slice/Oracle_Instance_orcl.service
           ‣ 15295 ora_smon_orcl

Oct 29 00:15:13 em12c.dbaglobe.com systemd[1]: Starting Oracle Instance (orcl)...
Oct 29 00:15:13 em12c.dbaglobe.com su[15214]: (to oracle) root on none
Oct 29 00:15:20 em12c.dbaglobe.com systemd[1]: Started Oracle Instance (orcl).


Saturday, October 24, 2015

How to Configure hostname in Oracle Linux 7

Set the hostname:

[root@localhost ~]# hostnamectl set-hostname em12c.dbaglobe.com
[root@localhost ~]# hostnameem12c.dbaglobe.com


Re-login terminal session:

login as: oracle
oracle@192.168.56.130's password:
Last login: Sat Oct 24 17:44:29 2015 from 192.168.56.1
[oracle@em12c ~]$ hostnamectl status
   Static hostname: em12c.dbaglobe.com
         Icon name: computer
           Chassis: n/a
        Machine ID: 15d9d5269a7143d5b035b02bf229b005
           Boot ID: 5253a1c387ef488aa662eec3b7d52f39
  Operating System: Oracle Linux Server 7.1
       CPE OS Name: cpe:/o:oracle:linux:7:1
            Kernel: Linux 3.10.0-229.14.1.el7.x86_64
      Architecture: x86_64

Friday, October 23, 2015

Fix 3 installation issues on OEM12c Cloud Control on OL7 (RHEL7)

Issue 1:  cannot find crt1.o


INFO: 10/23/15 9:55:05 PM SGT: m12c/middleware/oms/sqlplus/bin/sqlplus32 -L/u01/em12c/middleware/oms/sqlplus/lib32/ -L/u01/em12c/middleware/oms/lib32/ -L/u01/em12c/middleware/oms/lib32/stubs/  /u01/em12c/middleware/oms/sqlplus/lib32/s0afimai.o -lsqlplus -lclntsh  `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lm
INFO: 10/23/15 9:55:05 PM SGT: m -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lcli
INFO: 10/23/15 9:55:05 PM SGT: ent11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /u01/em12c/middleware/oms/lib32/sysliblist` -Wl,-rpath,/u01/em12c/middleware/oms/lib32 -lm -lpthread   `cat /u01/em12c/middleware/oms/lib32/sysliblist` -ldl -lm -lpthread  -L/u01/em12c/middleware/oms/lib32

INFO: 10/23/15 9:55:05 PM SGT: /usr/bin/ld: cannot find crt1.o: No such file or directory
/usr/bin/ld: cannot find crti.o: No such file or directory
/usr/bin/ld: cannot find -lclntsh
/usr/bin/ld: skipping incompatible /usr/lib64/libc.so when searching for -lc
/usr/bin/ld: cannot find -lc
/usr/bin/ld: cannot find crtn.o: No such file or directory

INFO: 10/23/15 9:55:05 PM SGT: make[1]: Leaving directory `/u01/em12c/middleware/oms/sqlplus/lib'

INFO: 10/23/15 9:55:05 PM SGT: collect2: error: ld returned 1 exit status
make[1]: *** [/u01/em12c/middleware/oms/sqlplus/bin/sqlplus32] Error 1
make: *** [newsqlplus32] Error 2

INFO: 10/23/15 9:55:05 PM SGT: End output from spawned process.
INFO: 10/23/15 9:55:05 PM SGT: ----------------------------------
INFO: 10/23/15 9:55:05 PM SGT: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'install' of makefile '/u01/em12c/middleware/oms/sqlplus/lib/ins_sqlplus.mk'. See '/u01/app/oraInventory/logs/installActions2015-10-23_09-52-50-PM.log' for details.
Exception Severity: 1
INFO: 10/23/15 9:55:05 PM SGT: POPUP WARNING:Error in invoking target 'install' of makefile '/u01/em12c/middleware/oms/sqlplus/lib/ins_sqlplus.mk'. See '/u01/app/oraInventory/logs/installActions2015-10-23_09-52-50-PM.log' for details.

Click "Retry" to try again.
Click "Ignore" to ignore this error and go on.
Click "Cancel" to stop this installation.

How to fix:


[root@localhost /]#  rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep glibc-devel
glibc-devel-2.17-78.0.1.el7 (x86_64)
[root@localhost /]# yum search glibc-devel
Loaded plugins: langpacks
================================================================= N/S matched: glibc-devel ==================================================================
glibc-devel.i686 : Object files for development using standard C libraries.
glibc-devel.x86_64 : Object files for development using standard C libraries.
[root@localhost /]# yum install glibc-devel.i686


Issue 2:  cannot find -lclntsh


gcc -m32 -o /u01/em12c/middleware/oms/sqlplus/bin/sqlplus32 -L/u01/em12c/middleware/oms/sqlplus/lib32/ -L/u01/em12c/middleware/oms/lib32/ -L/u01/em12c/middleware/oms/lib32/stubs/  /u01/em12c/middleware/oms/sqlplus/lib32/s0afimai.o -lsqlplus -lclntsh  `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lnc
INFO: 10/23/15 10:06:26 PM SGT: rypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnnz11 -lzt11 -lztkg11 -lztkg11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lmm -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncrypt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lnro11 `cat /u01/em12c/middleware/oms/lib32/ldflags`    -lncry
INFO: 10/23/15 10:06:26 PM SGT: pt11 -lnsgr11 -lnzjs11 -ln11 -lnl11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11   -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lclient11 -lnnetd11  -lvsn11 -lcommon11 -lgeneric11 -lsnls11 -lnls11  -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11   `cat /u01/em12c/middleware/oms/lib32/sysliblist` -Wl,-rpath,/u01/em12c/middleware/oms/lib32 -lm -lpthread
INFO: 10/23/15 10:06:26 PM SGT:   `cat /u01/em12c/middleware/oms/lib32/sysliblist` -ldl -lm -lpthread  -L/u01/em12c/middleware/oms/lib32

INFO: 10/23/15 10:06:26 PM SGT: /usr/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status

INFO: 10/23/15 10:06:26 PM SGT: make[1]: Leaving directory `/u01/em12c/middleware/oms/sqlplus/lib'

INFO: 10/23/15 10:06:26 PM SGT: make[1]: *** [/u01/em12c/middleware/oms/sqlplus/bin/sqlplus32] Error 1
make: *** [newsqlplus32] Error 2

How to fix:


[oracle@em12c bin]$ pwd
/u01/em12c/middleware/oms/bin
[oracle@em12c bin]$ export ORACLE_HOME=/u01/em12c/middleware/oms
[oracle@em12c bin]$ ./genclntsh

[oracle@em12c bin]$ ls -l /u01/em12c/middleware/oms/lib32//libclntsh.so
lrwxrwxrwx. 1 oracle oinstall 17 Oct 23 22:16 /u01/em12c/middleware/oms/lib32//libclntsh.so -> libclntsh.so.11.1

Issue 3:  libwebcache.a(wxsmdms.o): undefined reference to symbol 'yodSensorInvalidate'


gcc -o webcached -L/u01/em12c/middleware/Oracle_WT/webcache/lib/ -L/u01/em12c/middleware/Oracle_WT/lib/ -L/u01/em12c/middleware/Oracle_WT/lib/stubs/  main.o libwebcache.a -Wl,-rpath,/u01/em12c/middleware/Oracle_WT/lib -liau -lnnz11 -lxml11 -lclntsh  -lcore11 -lunls11 -lnls11  /u01/em12c/middleware/Oracle_WT/lib/liboraz.a -ldmsapp -lons    `cat /u01/em12c/middleware/Oracle_WT/lib/sysliblist` -lrt -Wl,-rpath,/u01/em12c/middleware/Oracle_WT/lib -lm    `cat /u01/em12c/middle
INFO: 10/23/15 10:23:56 PM SGT: ware/Oracle_WT/lib/sysliblist` -lrt -ldl -lm   -L/u01/em12c/middleware/Oracle_WT/lib

INFO: 10/23/15 10:23:56 PM SGT: /usr/bin/ld: libwebcache.a(wxsmdms.o): undefined reference to symbol 'yodSensorInvalidate'
/usr/bin/ld: note: 'yodSensorInvalidate' is defined in DSO /u01/em12c/middleware/Oracle_WT/lib/libdms2.so so try adding it to the linker command line
/u01/em12c/middleware/Oracle_WT/lib/libdms2.so: could not read symbols: Invalid operation
collect2: error: ld returned 1 exit status

INFO: 10/23/15 10:23:56 PM SGT: make: *** [webcached] Error 1

INFO: 10/23/15 10:23:56 PM SGT: End output from spawned process.
INFO: 10/23/15 10:23:56 PM SGT: ----------------------------------
INFO: 10/23/15 10:23:56 PM SGT: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'install' of makefile '/u01/em12c/middleware/Oracle_WT/webcache/lib/ins_calypso.mk'. See '/u01/app/oraInventory/logs/cloneActions2015-10-23_10-23-38-PM.log' for details.
Exception Severity: 1
INFO: 10/23/15 10:23:56 PM SGT: POPUP WARNING:Error in invoking target 'install' of makefile '/u01/em12c/middleware/Oracle_WT/webcache/lib/ins_calypso.mk'. See '/u01/app/oraInventory/logs/cloneActions2015-10-23_10-23-38-PM.log' for details.

Click "Retry" to try again.
Click "Ignore" to ignore this error and go on.
Click "Cancel" to stop this installation.


How to fix: (add -ldms2 to sysliblist)

[root@localhost trace]# cp -p /u01/em12c/middleware/Oracle_WT/lib/sysliblist /u01/em12c/middleware/Oracle_WT/lib/sysliblist.orig
[root@localhost trace]# vi /u01/em12c/middleware/Oracle_WT/lib/sysliblist
[root@localhost trace]# diff /u01/em12c/middleware/Oracle_WT/lib/sysliblist /u01/em12c/middleware/Oracle_WT/lib/sysliblist.orig
1c1
< -ldl -lm -lpthread -lnsl -lirc -lipgo -ldms2
---
> -ldl -lm -lpthread -lnsl -lirc -lipgo

Thursday, October 22, 2015

Linux kernel parameters set by “oracle-rdbms-server-12cR1-preinstall”

[oracle@localhost /]$ rpm -qa|grep preinstall
oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64
[oracle@localhost /]$
[oracle@localhost /]$ cat /etc/sysctl.conf
# System default settings live in /usr/lib/sysctl.d/00-system.conf.
# To override those settings, enter new settings here, or in an /etc/sysctl.d/<name>.conf file
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

# oracle-rdbms-server-12cR1-preinstall setting for fs.file-max is 6815744
fs.file-max = 6815744

# oracle-rdbms-server-12cR1-preinstall setting for kernel.sem is '250 32000 100 128'
kernel.sem = 250 32000 100 128

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmni is 4096
kernel.shmmni = 4096

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmall is 1073741824 on x86_64
kernel.shmall = 1073741824

# oracle-rdbms-server-12cR1-preinstall setting for kernel.shmmax is 4,398,046,511,104 on x86_64
kernel.shmmax = 4398046511104

# oracle-rdbms-server-12cR1-preinstall setting for kernel.panic_on_oops is 1 per Orabug 19212317
kernel.panic_on_oops = 1

# oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_default is 262144
net.core.rmem_default = 262144

# oracle-rdbms-server-12cR1-preinstall setting for net.core.rmem_max is 4194304
net.core.rmem_max = 4194304

# oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_default is 262144
net.core.wmem_default = 262144

# oracle-rdbms-server-12cR1-preinstall setting for net.core.wmem_max is 1048576
net.core.wmem_max = 1048576

# oracle-rdbms-server-12cR1-preinstall setting for net.ipv4.conf.all.rp_filter is 2
net.ipv4.conf.all.rp_filter = 2

# oracle-rdbms-server-12cR1-preinstall setting for net.ipv4.conf.default.rp_filter is 2
net.ipv4.conf.default.rp_filter = 2

# oracle-rdbms-server-12cR1-preinstall setting for fs.aio-max-nr is 1048576
fs.aio-max-nr = 1048576

# oracle-rdbms-server-12cR1-preinstall setting for net.ipv4.ip_local_port_range is 9000 65500
net.ipv4.ip_local_port_range = 9000 65500

Wednesday, October 21, 2015

Adding SSRS instances without configuring scale-out (in Standard Edition Scenario)

 

Below are steps to manually configure SSRS failover to alternative instances based on Standard Edition, lab experimental only, use in production at your own risk.

1. Below Key operations added to Keys table during the Report Server DB configuration

declare @p5 int
set @p5=NULL
exec AnnounceOrGetKey @MachineName=N'VMMSQL02',@InstanceName=N'SE',@InstallationID='74706FB6-0740-4F57-931E-54702B387643',@PublicKey=0x0602000000A40000525341310008000001000100C1284D20B9F13FD33C12CEF6E791A238B78472AB467F32CC4B9B12537F7BAE4FF8D369C75039503EA55AC461C79E9482CBAB08242E6FB67A12596C52A0BD8AED961C1D0175557826388F8B9E1E92FD36EE4BB5DDB46320BD990C928D3D46AAD344015478957CBA0EFB692019DE1215696AF72B5E8AA6C419D1C7CC881F4399FE69F03995AEEFAA9DCDB108F6022428942C05E4405C80E8198584C4D564100DC5F8A39D844A0C68546E9E9E36623604990E7792DDD62919729C2CCCF800995880EEFFBF9E994A196824DBBF41B79A04E8C1FB8FCF87AA7D18EA50BD874D140BF8B2E7D79196FD2FBC14C7FF3AB8D574C3A0298C95BC17D216BA403D272556EABA,@NumAnnouncedServices=@p5 output
select @p5
go

image

2. On VMMSQL01 (original), the “Test Report” runs without problem, but failed on VMMSQL02.

image

image

library!ReportServer_0-1!9f0!10/21/2015-23:06:31:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerNotActivatedException: , Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.;
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: Initializing crypto as user: DBAGLOBE\sqlsvcs
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: Exporting public key
crypto!ReportServer_0-1!f9c!10/21/2015-23:07:29:: i INFO: NT Service not activated
library!ReportServer_0-1!f9c!10/21/2015-23:07:29:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerNotActivatedException: , Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.;
library!ReportServer_0-1!f9c!10/21/2015-23:07:29:: e ERROR: Throwing Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: , Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerNotActivatedException: The report server installation is not initialized.
   --- End of inner exception stack trace ---;

3. Manually Restore the encryption key to VMMSQL02

image

 

image

4. VMMSQL02 still failed to render report due to feature limitation of Standard Edition

image

5. Manually removed the SSRS instance (VMMSQL01) from KEY table, Reports worked on VMMSQL02

select *
into [ReportServer].[dbo].[Keys_BAK]
from  [ReportServer].[dbo].[Keys]
GO

delete from [ReportServer].[dbo].[Keys] where MachineName='VMMSQL01'
GO

image

image

6. In the event of failback required, manually insert the KEY for VMMSQL01 SSRS instance again

insert into [ReportServer].[dbo].[Keys]
select * from [ReportServer].[dbo].[Keys_BAK] where MachineName='VMMSQL01'
GO
delete from [ReportServer].[dbo].[Keys] where MachineName='VMMSQL02'
GO

image

Machine related configuration during SSRS DB creation

declare @p5 int
set @p5=NULL
exec AnnounceOrGetKey @MachineName=N'VMMSQL01',@InstanceName=N'SE',@InstallationID='1E40A75C-0BE4-4D7C-A0AC-F0746AC8BC9B',@PublicKey=0x0602000000A400005253413100080000010001008D3BDCD0EEC49331A789635C801D03D655D47209A1B950842F1E5A377C151DE87D85C93DF5CDA47F60F06B68D5713B9AFE8E6DC31FF473888C6DFEDE810D4339FD24349E274DF4DCF5295CC75E3DB1064E3F01B1DE49DB4196CA4B342941FD0AF273B11A575D7B3721460D07C56B30AB082E3C07A6F70FC789EF7C14AFB57D7B2B4E506FA5DE32B7586CF813A19D6AADCE36D341596E645840AB3489B7AE91A70E4E827CB160388DF97B76E2171D5D771D1099A9C40EE2E38359B2D369C4B554215368AA8C909327397E60310FFB900C0C7DDF23144B0F33DCA2AC51D12EBAC014419E1616DD2F3997660C95D9602312E97E5186CBD912659A6B9197ADC8AEF2,@NumAnnouncedServices=@p5 output
select @p5
go
exec SetKeysForInstallation @InstallationID='1E40A75C-0BE4-4D7C-A0AC-F0746AC8BC9B',@SymmetricKey=0x010200000366000000A40000237C083E606AE3E4B5D3D2A181B74FE287BB57603AEBC7BD9297020A42825D57304A6983D49EB729FF2A39CD0D86E87401FEA56CAE657C0446910771B23FCFE64B3FC08FD1DF6D547CDEDC3D1676B3D254532F363FABD395ECD93ECD6F202C4FD5DA060C6D4310402281834E41F112CAFF7DB05F90955652968F7E565E483675101DAFEE9E932C9425DFE58FB886FD3FC162B75892CAFD7EBF92D37D25CFB84530437541A014A88AA1D3F7D6DCB9CEF4A68902DBE88142D50A4ED0462B9095F8B1397EC2F3DD9BCF182A9ABDD943F4DE9DE4613EED31E10B2E17B73282973AD0F9048DCCB68C64DBE7B9D95B0D15750C948E6E4303BAE7BAD80B3937A5E4C298,@PublicKey=0x0602000000A400005253413100080000010001008D3BDCD0EEC49331A789635C801D03D655D47209A1B950842F1E5A377C151DE87D85C93DF5CDA47F60F06B68D5713B9AFE8E6DC31FF473888C6DFEDE810D4339FD24349E274DF4DCF5295CC75E3DB1064E3F01B1DE49DB4196CA4B342941FD0AF273B11A575D7B3721460D07C56B30AB082E3C07A6F70FC789EF7C14AFB57D7B2B4E506FA5DE32B7586CF813A19D6AADCE36D341596E645840AB3489B7AE91A70E4E827CB160388DF97B76E2171D5D771D1099A9C40EE2E38359B2D369C4B554215368AA8C909327397E60310FFB900C0C7DDF23144B0F33DCA2AC51D12EBAC014419E1616DD2F3997660C95D9602312E97E5186CBD912659A6B9197ADC8AEF2
go

exec GetMyRunningJobs @ComputerName=N'VMMSQL01',@JobType=1
go

image

image

“The feature using other editions of sql server for report data sources and/or the report server database”is not supported in this edition of Reporting Services.

image

The feature: "The feature using other editions of sql server for report data sources and/or the report server database”is not supported in this edition of Reporting Services.

image

Step to fix:

Upgrade SQL Server to standard edition or enterprise edition