Tales From A Lazy Fat Oracle DBA

Are the Cardinality Estimates Correct in my Execution Plan ?

Posted by FatDBA on September 26, 2017

Struck in a difficult performance issue related with a SQL and you have to verify if the Cardinality estimates made by the MIGHTY CBO are correct, No idea how t0 do that 😦 😦

Lets things make little easy for ourselves!
Let me take an example and explain how to do this.

SQL Statement (From my Personal Test Environment):
SELECT COUNT (DISTINCT SB_NO) FROM OPS$EXP.C_AL_SB WHERE SB_NO IN (SELECT DISTINCT SB_NO FROM OPS$EXP.C_AL_AWB WHERE EGM_DT BETWEEN :1 AND :1 ) AND ERR_MESG =’S’

Below is the execution plan for the SQL (Lets forgot about the behemoth elapsed time and Cost and Rows Processed in the plan for a minute 🙂 ) ….

So the above plan doesn’t show any estimations or Cardinality details what it considered during the creation of the plan, But starting from 10g we have GATHER_PLAN_STATISTICS hint. The GATHER_PLAN_STATISTICS hint tells Oracle to collect execution statistics for a SQL statement.

These execution statistics are then shown next to the original Optimizer estimates in the execution plan if you use the function DBMS_XPLAN.DISPLAY_CURSOR to display the plan. You also have to set the FORMAT parameter to ‘ALLSTATS LAST’ (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>’ALLSTATS LAST’)).

SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT (DISTINCT SB_NO) FROM OPS$EXP.C_AL_SB WHERE SB_NO IN (SELECT DISTINCT SB_NO FROM OPS$EXP.C_AL_AWB WHERE EGM_DT BETWEEN :1 AND :1 ) AND ERR_MESG =’S’;

The execution plan for the query is as follows:

The original Optimizer estimates are shown in the E-Rows column while the actual statistics gathered during execution are shown in the A-Rows column.

How to tune the IO contentions related with the Compaction in Cassandra ?

Posted by FatDBA on August 20, 2017

Hi Fellas,
Back and this time with some performance tuning scopes for Cassandra DB during the ‘Compaction’ process.
Before i proceed, would like to explain a bit about the compaction in Cassandra and what exactly is this and why a necessary evil …

Compaction in Cassandra refers to the operation of merging multiple SSTables into a single new one. Typically, compaction is done in a database for two primary reasons:

– To reduce the storage usage.
– To improve read performance by merging keys and obtaining a consolidated index.

For example, in Apache Cassandra, data files are merged periodically to form compacted SSTables.

There is a good chance of contention happening in database due to Compaction activity as the Compaction increases I/O contention on SSTable data read. Writing data in Cassandra database is generally fast and the write impacts may not be seen but reading data from SSTables will be slow in case when I/O contention increases due to compaction activities and degrades the performance of the database.

First would like to discuss how to identify the compaction related contentions on the database.
– We can use the “nodetool tablestats” or the old “nodetool cfstats” command to
monitor or watch-keep SSTables.
Below is a sample result from one of the Cassandra database server, here we need to check
– Check if the count is keep on growing, because that points out that there may be contention between reading SST
and the compaction process.
– Read generally slows down due to an obvious reason of data distributed or fragmented across many SSTs and
Compaction running continuous in the background.

%nodetool tablestats -H dixit.playlist
Keyspace: dixit
Read Count: 182849
Read Latency: 0.11363755339104945 ms.
Write Count: 435355
Write Latency: 0.01956930550929701 ms.
Pending Flushes: 0
Table: standard1
SSTable count: 2
Space used (live): 51.62 MB
Space used (total): 51.62 MB
Space used by snapshots (total): 0 bytes
Off heap memory used (total): 302.36 KB
SSTable Compression Ratio: 0.0
Number of keys (estimate): 376390
Memtable cell count: 200120
Memtable data size: 45.16 MB
Memtable off heap memory used: 0 bytes
Memtable switch count: 2
Local read count: 182849
Local read latency: 0.125 ms
Local write count: 435355
Local write latency: 0.022 ms
Pending flushes: 0
Bloom filter false positives: 11
Bloom filter false ratio: 0.00000
Bloom filter space used: 265.81 KB
Bloom filter off heap memory used: 265.8 KB
Index summary off heap memory used: 36.57 KB
Compression metadata off heap memory used: 0 bytes
Compacted partition minimum bytes: 216 bytes
Compacted partition maximum bytes: 258 bytes
Compacted partition mean bytes: 258 bytes
Average live cells per slice (last five minutes): 1.0
Maximum live cells per slice (last five minutes): 1
Average tombstones per slice (last five minutes): 1.0
Maximum tombstones per slice (last five minutes): 1

Below is the command that can be used to check for compaction statistics, here you need to look at the ‘pending tasks’, and ‘bytes total in progress’.

$ nodetool compactionstats
pending tasks: 5
compaction type keyspace table completed total unit progress
Compaction Keyspace1 Standard1 282310680 302170540 bytes 93.43%
Compaction Keyspace1 Standard1 58457931 307520780 bytes 19.01%
Active compaction remaining time : 0h00m16s

Solution to the problem
1. First one is quite simple – Avoid merging of update/delete requests.
2. Reduce the frequency of in-memory objects (In Memtables) flush.

This can be done by increasing the size of the memtables to avoid or stop database to perform frequent flushes.
– Less number of flushes leads to fewer SSTs compaction.
– Less Compaction reduces the I/Contentions and this in turn improve reads.
– There are couple of parameters that you can adjust in your cassandra.yaml file to control the flushing.
i.e. memtable_flush_after_mins, memtable_throughput_in_mb , memtable_operations_in_millions.

3. One more solution but that only applies on systems where this stress in IO is not much frequent, we can reduce
the “thread priority” which reduces the IOs.
As lowering the priority slows down the compaction writes but only applies if it doesn’t happen frequently.

Add below lines in cassandra-env.conf file (Under /conf folder) to lower the compaction priority.

JVM_OPTS=”$JVM_OPTS -XX:+UseThreadPriorities -XX:ThreadPriorityPolicy=42 -Dcassandra.compaction.priority=1″

One last line would like to add.
In case when the IO is a genuine problem, you will need to add more nodes or replace disks with better performing one’s or high IO disks.

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: cassandra, performance, Tuning | Leave a Comment »

Golden Gate Logdump Utility: How to find RBA using TIMESTAMP.

Posted by FatDBA on July 28, 2017

Hey Mates,
Would like to discuss about the well known Golden Gate troubleshooting tool ‘Logdump’, specially about one of the command that is quite handy when you have million of records present in the Trail file and doing that Constant press of character ‘n’ on the keyboard could be a huge pain.
So, if you have the timestamp you will be able to do it using SFTS of SCANFORTIMESTAMP command of Logdump.

Logdump 885 > usertoken on
Logdump 886 > ggstoken on
Logdump 887 > ghdr on
Logdump 888 > detail on

Logdump 889 > open ./dirdat/pe000067

Logdump 889 > sfts 2017/07/28 11:15:30
Scan for timestamp >= 2017/07/28 11:15:30.000.000 CEST
Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 705 (x02c1) IO Time : 2017/07/28 11:15:30.000.000 IOType : 5 (x05) OrigNode : 255 (xff) TransInd : . (x03) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 101 AuditPos : 223086608 Continued : N (x00) RecCount : 1 (x01) 2014/04/07 10:06:16.000.000 Insert Len 705 RBA 63547 Name: EAST.ORDERS After Image: Partition 4 G s 0000 000a 0000 0000 0000 0000 0001 0001 000a 0000 | .................... 0000 0000 0000 0001 0002 0010 0000 000c 4c6f 7265 | ................Lore 6e20 5065 6e74 6f6e 0003 0004 ffff 0000 0004 0014 | n Penton............ 0000 0010 3338 3230 2042 7572 6775 6e64 7920 5374 | ....3820 Burgundy St 0005 0004 ffff 0000 0006 000f 0000 000b 4e65 7720 | ................New 4f72 6c65 616e 7300 0700 0900 0000 0537 3031 3137 | Orleans........70117 0008 000d 0000 0009 4c6f 7569 7369 616e 6100 0900 | ........Louisiana... GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4157 534c 4141 4541 4141 414a 3141 4141 0001 | AAAWSLAAEAAAAJ1AAA.. TokenID x4c 'L' LOGCSN Info x00 Length 7 3831 3633 3430 34 | 8163404 TokenID x36 '6' TRANID Info x00 Length 9 312e 3239 2e39 3835 30 | 1.29.9850
Filtering suppressed 12 records

Hope It Helps
Prashant Dixit

Posted in Advanced | Tagged: golden gate | Leave a Comment »

Installing Cassandra Cluster Manager (CCM) on Oracle Linux 7

Posted by FatDBA on July 20, 2017

Hi All,
Today going to discuss about the CCM or the Cassandra Cluster Manager, which is basically a tool that we can use to create a multi-node cluster of Cassandra database on a local machine. This can be easily used to mimic the production like clustering setup for Cassandra on a local machine. This will help you to understand how clustering works in case of Cassandra databases.

Below i am going to show how to create a 3 node Cassandra cluster on the top of OEL7 with Cassandra version 3.11.0

Step 1:
First download the the PIP and then install it along with PyYAML packages.
Download the ‘PIP’, ‘Wheel’ and ‘Python Setuptools’, follow the link
https://packaging.python.org/tutorials/installing-packages/#install-pip-setuptools-and-wheel

[root@fatdba ~]# ls -ltrh
total 168M
-rw-r–r–. 1 root root 163M Mar 16 01:35 jdk-8u131-linux-x64.rpm
-rw——-. 1 root root 1.4K Jun 17 12:59 anaconda-ks.cfg
-rw-r–r–. 1 root root 1.5K Jun 17 13:34 initial-setup-ks.cfg
-rw-r–r–. 1 root root 4.2M Jun 17 17:01 master.zip
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Templates
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Public
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Downloads
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Desktop
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Videos
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Pictures
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Music
drwxr-xr-x. 2 root root 6 Jul 17 11:08 Documents
-rw-r–r–. 1 root root 1.6M Jul 17 13:44 get-pip.py

[root@fatdba ~]# python get-pip.py
Collecting pip
Downloading pip-9.0.1-py2.py3-none-any.whl (1.3MB)
100% ████████████████████████████████ 1.3MB 51kB/s
Collecting wheel
Downloading wheel-0.29.0-py2.py3-none-any.whl (66kB)
100% ████████████████████████████████ 71kB 430kB/s
Installing collected packages: pip, wheel
Successfully installed pip-9.0.1 wheel-0.29.0
[root@fatdba ~]#
[root@fatdba ~]#
[root@fatdba ~]# which pip
/usr/bin/pip
[root@fatdba ~]#

[root@fatdba ~]# pip install cql PyYAML
Collecting cql
Downloading cql-1.4.0.tar.gz (76kB)
100% ████████████████████████████████ 81kB 252kB/s
Collecting PyYAML
Downloading PyYAML-3.12.tar.gz (253kB)
100% ████████████████████████████████ 256kB 308kB/s
Collecting thrift (from cql)
Downloading thrift-0.10.0.zip (87kB)
100% ████████████████████████████████ 92kB 568kB/s
Requirement already satisfied: six>=1.7.2 in /usr/lib/python2.7/site-packages (from thrift->cql)
Building wheels for collected packages: cql, PyYAML, thrift
Running setup.py bdist_wheel for cql … done
Stored in directory: /root/.cache/pip/wheels/e6/b3/50/fdb7532df6817694ae467c7aaedb991c2104b463ab31f7a94f
Running setup.py bdist_wheel for PyYAML … done
Stored in directory: /root/.cache/pip/wheels/2c/f7/79/13f3a12cd723892437c0cfbde1230ab4d82947ff7b3839a4fc
Running setup.py bdist_wheel for thrift … done
Stored in directory: /root/.cache/pip/wheels/e7/f1/d3/b472914d95caa1781fb29b1257b85808324b0bfd1838961752
Successfully built cql PyYAML thrift
Installing collected packages: thrift, cql, PyYAML
Successfully installed PyYAML-3.12 cql-1.4.0 thrift-0.10.0

Step 2: Now using the PIP, install the CCM package.

[root@fatdba ~]# pip install ccm
Collecting ccm
Downloading ccm-2.7.0.tar.gz (68kB)
100% ████████████████████████████████ 71kB 186kB/s
Requirement already satisfied: pyYaml in /usr/lib64/python2.7/site-packages (from ccm)
Requirement already satisfied: six>=1.4.1 in /usr/lib/python2.7/site-packages (from ccm)
Building wheels for collected packages: ccm
Running setup.py bdist_wheel for ccm … done
Stored in directory: /root/.cache/pip/wheels/9d/ec/85/e971d86de3002809194d0c4bb7ee72f9fab55b428c8293cd79
Successfully built ccm
Installing collected packages: ccm
Successfully installed ccm-2.7.0
[root@fatdba ~]#

Step 3: Make required entries in your /etc/hosts file.

bash-4.2$ more /etc/hosts
#127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.40.131 fatdba.localdomain fatdba

#Cassandra Nodes for CCM
127.0.0.1 127.0.0.2
127.0.0.1 127.0.0.3
127.0.0.1 127.0.0.4

Step 4: Now, lets create the cluster using the CCM.
I will be creating this cluster with name ‘dixit’ with 3 nodes available.

-bash-4.2$ ccm create dixit -v 3.11.0
████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████
-bash-4.2$ ccm status
Cluster: ‘dixit’
—————-
No node in this cluster yet
-bash-4.2$
-bash-4.2$ ccm populate -n 3

-bash-4.2$ ccm status
Cluster: ‘dixit’
—————-
node1: DOWN (Not initialized) node3: DOWN (Not initialized) node2: DOWN (Not initialized)

Lets start the cluster now when all the nodes are successfully added.
Just to make things little easier and more understandable, i will be starting each node one-by-one which otherwise can be enabled in a single command or in one go.

-bash-4.2$ ccm node1 start
-bash-4.2$ ccm node2 start
-bash-4.2$ ccm node3 start
-bash-4.2$
-bash-4.2$ ccm status
Cluster: 'dixit' ---------------- node1: UP node3: UP node2: UP

Step 5: Verify the cluster status.

bash-4.2$ ccm liveset
127.0.0.1,127.0.0.3,127.0.0.2

bash-4.2$ ccm cqlsh node1
Unknown node or command: cqlsh

bash-4.2$ ccm node1 cqlsh
Connected to dixit at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh>
cqlsh>

bash-4.2$
bash-4.2$ ccm node1 show
node1: UP
cluster=dixit
auto_bootstrap=False
thrift=(‘127.0.0.1’, 9160)
binary=(‘127.0.0.1’, 9042)
storage=(‘127.0.0.1’, 7000)
jmx_port=7100
remote_debug_port=0
byteman_port=0
initial_token=-9223372036854775808
pid=16852
bash-4.2$
bash-4.2$
bash-4.2$ ccm node2 show
node2: UP
cluster=dixit
auto_bootstrap=False
thrift=(‘127.0.0.2’, 9160)
binary=(‘127.0.0.2’, 9042)
storage=(‘127.0.0.2’, 7000)
jmx_port=7200
remote_debug_port=0
byteman_port=0
initial_token=-3074457345618258603
pid=16947
bash-4.2$
bash-4.2$
bash-4.2$ ccm node3 show
node3: UP
cluster=dixit
auto_bootstrap=False
thrift=(‘127.0.0.3’, 9160)
binary=(‘127.0.0.3’, 9042)
storage=(‘127.0.0.3’, 7000)
jmx_port=7300
remote_debug_port=0
byteman_port=0
initial_token=3074457345618258602
pid=17191
bash-4.2$

Some additional monitoring of Java processes and heap usage etc. can be done using Java Management Console.

Hope That Helps
Prashant Dixit

Posted in Advanced | Tagged: cassandra | Leave a Comment »

Cassandra NodeTool Utility

Posted by FatDBA on July 14, 2017

The nodetool utility gives a easy CLI to perform some of the admin activities and configure the database.
Today i would like to share few of the command/operations that i have tried and tested by my own, below mentioned are few of the commands with its syntax and usage details.

Let’s first explore all possible options or attributes of this utility.


Starting NodeTool

Missing required option: h

usage: java org.apache.cassandra.tools.NodeCmd --host  
 -h,--host         node hostname or ip address

 -p,--port         remote jmx agent port number

 -pw,--password    remote jmx agent password

 -u,--username     remote jmx agent username
Available commands:

  ring                   - Print informations on the token ring

  join                   - Join the ring

  info                   - Print node informations (uptime, load, ...)

  cfstats                - Print statistics on column families

  clearsnapshot          - Remove all existing snapshots

  version                - Print cassandra version

  tpstats                - Print usage statistics of thread pools

  drain                  - Drain the node (stop accepting writes and flush all column families)

  decommission           - Decommission the node

  loadbalance            - Loadbalance the node

  compactionstats        - Print statistics on compactions

  disablegossip          - Disable gossip (effectively marking the node dead)

  enablegossip           - Reenable gossip

  disablethrift          - Disable thrift server

  enablethrift           - Reenable thrift server

  snapshot [snapshotname] - Take a snapshot using optional name snapshotname

  netstats [host]        - Print network information on provided host (connecting node by default)

  move        - Move node on the token ring to a new token

  removetoken status|force| - Show status of current token removal, force completion of pending removal or remove provided token

  flush [keyspace] [cfnames] - Flush one or more column family

  repair [keyspace] [cfnames] - Repair one or more column family

  cleanup [keyspace] [cfnames] - Run cleanup on one or more column family

  compact [keyspace] [cfnames] - Force a (major) compaction on one or more column family

  scrub [keyspace] [cfnames] - Scrub (rebuild sstables for) one or more column family

  invalidatekeycache [keyspace] [cfnames] - Invalidate the key cache of one or more column family

  invalidaterowcache [keyspace] [cfnames] - Invalidate the key cache of one or more column family

  getcompactionthreshold   - Print min and max compaction thresholds for a given column family

  cfhistograms   - Print statistic histograms for a given column family

  setcachecapacity     - Set the key and row cache capacities of a given column family

  setcompactionthreshold     - Set the min and max compaction thresholds for a given column family

Provides a histogram of network statistics at the time you fired this command.


bash-4.2$ nodetool proxyhistograms

proxy histograms

Percentile       Read Latency      Write Latency      Range Latency   CAS Read Latency  CAS Write Latency View Write Latency

                     (micros)           (micros)           (micros)           (micros)           (micros)           (micros)

50%                      0.00               0.00               0.00               0.00               0.00               0.00

75%                      0.00               0.00               0.00               0.00               0.00               0.00

95%                      0.00               0.00               0.00               0.00               0.00               0.00

98%                      0.00               0.00               0.00               0.00               0.00               0.00

99%                      0.00               0.00               0.00               0.00               0.00               0.00

Min                      0.00               0.00               0.00               0.00               0.00               0.00

Max                      0.00               0.00               0.00               0.00               0.00               0.00

Note: I haven’t done any activity on the database, so obvious we getting 0 for all the values or sections.

To do a sequential repair of all keyspaces on the current node:
bash-4.2$ nodetool repair -seq

Describe the cluster details.


bash-4.2$ nodetool describecluster

Cluster Information:

        Name: Test Cluster

        Snitch: org.apache.cassandra.locator.DynamicEndpointSnitch

        Partitioner: org.apache.cassandra.dht.Murmur3Partitioner

        Schema versions:

                1852b5d8-f9ba-3549-b4b7-eaae1da39062: [127.0.0.1]

Status of the node.


bash-4.2$ nodetool status

Datacenter: datacenter1

=======================

Status=Up/Down

|/ State=Normal/Leaving/Joining/Moving

--  Address    Load       Tokens       Owns (effective)  Host ID                               Rack

UN  127.0.0.1  190.25 KiB  256          100.0%            0277aea4-d06c-4175-8d57-6100101f0491  rack1

History of database comp actions done in the DB.


bash-4.2$ nodetool compactionhistory

Compaction History:

id                                   keyspace_name columnfamily_name compacted_at            bytes_in bytes_out rows_merged

39d4ff90-66df-11e7-ba43-41553ec85c87 system        size_estimates    2017-07-12T14:20:59.209 172588   42619     {4:4}

36186cc0-66df-11e7-ba43-41553ec85c87 system        sstable_activity  2017-07-12T14:20:52.664 475      82        {1:8, 4:1}

05558d20-66c6-11e7-ba43-41553ec85c87 system        size_estimates    2017-07-12T11:20:33.714 173036   43201     {4:4}

0424fc60-66c6-11e7-ba43-41553ec85c87 system        sstable_activity  2017-07-12T11:20:31.718 548      83        {1:12, 4:1}

20b362b0-660b-11e7-ba43-41553ec85c87 system        size_estimates    2017-07-11T13:02:43.739 166052   43228     {3:1, 4:3}

203ab040-660b-11e7-ba43-41553ec85c87 system        sstable_activity  2017-07-11T13:02:42.948 687      82        {1:28, 3:1}

62569400-65fa-11e7-ba43-41553ec85c87 system        local             2017-07-11T11:02:52.416 10157    5164      {4:1}

a1d34560-65f5-11e7-ba43-41553ec85c87 system_schema keyspaces         2017-07-11T10:28:51.446 668      277       {1:4, 2:2}

a1955200-65f5-11e7-ba43-41553ec85c87 system_schema tables            2017-07-11T10:28:51.040 5486     2689      {1:3, 2:2}

a0d906e0-65f5-11e7-ba43-41553ec85c87 system_schema columns           2017-07-11T10:28:49.806 10214    5654      {1:3, 2:2}

003788e0-65f2-11e7-ba43-41553ec85c87 system        local             2017-07-11T10:02:51.822 5358     5170      {4:1}

fd05d0f0-65f1-11e7-ba43-41553ec85c87 system        local             2017-07-11T10:02:46.463 5324     5199      {4:1}

fca0f4a0-65f1-11e7-ba43-41553ec85c87 system        local             2017-07-11T10:02:45.802 5346     5171      {4:1}

c604f720-6551-11e7-9add-f1b60320c550 system        local             2017-07-10T14:55:54.706 5166     5067      {4:1}

bd3430c0-6551-11e7-9add-f1b60320c550 system        local             2017-07-10T14:55:39.916 301      148       {4:1}

bb8e9710-6551-11e7-9add-f1b60320c550 system        local             2017-07-10T14:55:37.153 324      148       {4:1}

Statistics related to any ongoing compaction task, 0 if not any.

bash-4.2$ nodetool compactionstats
pending tasks: 0

Garbage collection statistics.


bash-4.2$ nodetool gcstats

       Interval (ms) Max GC Elapsed (ms)Total GC Elapsed (ms)Stdev GC Elapsed (ms)   GC Reclaimed (MB)         Collections      Direct Memory Bytes

            36066339                9200               57107                2102          2612889352                  32                       -1

bash-4.2$

Log levels defined in database for all areas.

bash-4.2$ nodetool getlogginglevels

Logger Name Log Level
ROOT INFO
com.thinkaurelius.thrift ERROR
org.apache.cassandra DEBUG
bash-4.2$

Tracing probabilities currently set in DB.

bash-4.2$ nodetool gettraceprobability
Current trace probability: 0.0
bash-4.2$

Gossip protocol related statistics.


bash-4.2$ nodetool gossipinfo

localhost/127.0.0.1

  generation:1499747570

  heartbeat:36966

  STATUS:15:NORMAL,-1019516550404639999

  LOAD:36910:255305.0

  SCHEMA:1623:1852b5d8-f9ba-3549-b4b7-eaae1da39062

  DC:6:datacenter1

  RACK:8:rack1

  RELEASE_VERSION:4:3.11.0

  RPC_ADDRESS:3:127.0.0.1

  NET_VERSION:1:11

  HOST_ID:2:0277aea4-d06c-4175-8d57-6100101f0491

  RPC_READY:20:true

  TOKENS:14:

Provides network information about the host machine.


bash-4.2$ nodetool netstats

Mode: NORMAL

Not sending any streams.

Read Repair Statistics:

Attempted: 0

Mismatch (Blocking): 0

Mismatch (Background): 0

Pool Name                    Active   Pending      Completed   Dropped

Large messages                  n/a         0              0         0

Small messages                  n/a         0              4         0

Gossip messages                 n/a         0              0         0

bash-4.2$

Hope It Helps
Prashant Dixit

Posted in Basics | Tagged: cassandra | Leave a Comment »

Installing Cassandra 3.0 on OEL7 – Steps!

Posted by FatDBA on July 11, 2017

Hi All,
Though many of you knows and heard about Cassandra database, still i would first like to explain a little for one who not much familiar with the term and will follow with the steps on how to install Cassandra 3.0 on OEL7.

Apache Cassandra is a highly scalable, high-performance distributed database designed to handle large amounts of data across many commodity servers, providing high availability with no single point of failure. It is a type of NoSQL database. Let us first understand what a NoSQL database does.

A NoSQL database (sometimes called as Not Only SQL) is a database that provides a mechanism to store and retrieve data other than the tabular relations used in relational databases. These databases are schema-free, support easy replication, have simple API, eventually consistent, and can handle huge amounts of data.

In this post, we will be installing Apache Cassandra on Oracle Enterprise Linux 7 server.

Step 1: Installing Java
Java is the main prerequisite for Cassandra.

[root@fatdba ~]# wget –no-cookies –no-check-certificate –header “Cookie:oraclelicense=accept-securebackup-cookie” “http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm”
–2017-07-10 14:31:35– http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm
Resolving download.oracle.com (download.oracle.com)… 96.17.182.42, 96.17.182.49
Connecting to download.oracle.com (download.oracle.com)|96.17.182.42|:80… connected.
HTTP request sent, awaiting response… 302 Moved Temporarily
Location: https://edelivery.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm [following]
–2017-07-10 14:31:37– https://edelivery.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm
Resolving edelivery.oracle.com (edelivery.oracle.com)… 104.108.215.179, 2600:1417:2c:190::2d3e, 2600:1417:2c:195::2d3e
Connecting to edelivery.oracle.com (edelivery.oracle.com)|104.108.215.179|:443… connected.
HTTP request sent, awaiting response… 302 Moved Temporarily
Location: http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm?AuthParam=1499677417_13d7c840391385e13fc7e785791cb8d6 [following]
–2017-07-10 14:31:37– http://download.oracle.com/otn-pub/java/jdk/8u131-b11/d54c1d3a095b4ff2b6607d096fa80163/jdk-8u131-linux-x64.rpm?AuthParam=1499677417_13d7c840391385e13fc7e785791cb8d6
Connecting to download.oracle.com (download.oracle.com)|96.17.182.42|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 169983496 (162M) [application/x-redhat-package-manager]
Saving to: ‘jdk-8u131-linux-x64.rpm’

100%[==============================================================================================================================>] 169,983,496 1.13MB/s in 2m 27s

2017-07-10 14:34:04 (1.11 MB/s) – ‘jdk-8u131-linux-x64.rpm’ saved [169983496/169983496]

[root@fatdba ~]# yum -y localinstall jdk-8u131-linux-x64.rpm
Loaded plugins: langpacks, ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
Examining jdk-8u131-linux-x64.rpm: 2000:jdk1.8.0_131-1.8.0_131-fcs.x86_64
Marking jdk-8u131-linux-x64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package jdk1.8.0_131.x86_64 2000:1.8.0_131-fcs will be installed
–> Finished Dependency Resolution
ol7_UEKR4/x86_64 | 1.2 kB 00:00:00
ol7_UEKR4/x86_64/updateinfo | 73 kB 00:00:00
ol7_UEKR4/x86_64/primary | 18 MB 00:00:17
ol7_addons/x86_64 | 1.2 kB 00:00:00
ol7_addons/x86_64/updateinfo | 38 kB 00:00:00
ol7_addons/x86_64/primary | 73 kB 00:00:00
ol7_latest/x86_64 | 1.4 kB 00:00:00
ol7_latest/x86_64/updateinfo | 1.3 MB 00:00:01
ol7_latest/x86_64/primary | 26 MB 00:00:26

Dependencies Resolved

========================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================
Installing:
jdk1.8.0_131 x86_64 2000:1.8.0_131-fcs /jdk-8u131-linux-x64 269 M

Transaction Summary
========================================================================================================================================================================
Install 1 Package

Total size: 269 M
Installed size: 269 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : 2000:jdk1.8.0_131-1.8.0_131-fcs.x86_64 1/1
Unpacking JAR files…
tools.jar…
plugin.jar…
javaws.jar…
deploy.jar…
rt.jar…
jsse.jar…
charsets.jar…
localedata.jar…
Verifying : 2000:jdk1.8.0_131-1.8.0_131-fcs.x86_64 1/1

Installed:
jdk1.8.0_131.x86_64 2000:1.8.0_131-fcs

Complete!

Verify the version of JAVA now after the installation.

[root@fatdba ~]# java -version
openjdk version “1.8.0_131”
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

Step 2: Installing Cassandra

[root@fatdba etc]# yum -y install cassandra
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
–> Running transaction check
—> Package cassandra.noarch 0:3.11.0-1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

========================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================
Installing:
cassandra noarch 3.11.0-1 cassandra 28 M

Transaction Summary
========================================================================================================================================================================
Install 1 Package

Total download size: 28 M
Installed size: 37 M
Downloading packages:
cassandra-3.11.0-1.noarch.rpm | 28 MB 00:02:25
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction

Installing : cassandra-3.11.0-1.noarch 1/1
Verifying : cassandra-3.11.0-1.noarch 1/1

Installed:
cassandra.noarch 0:3.11.0-1

Complete!
[root@fatdba etc]#
[root@fatdba etc]#

Reload your system daemons by running:

[root@fatdba etc]# systemctl daemon-reload

Start Cassandra by using following command.

[root@fatdba etc]# systemctl start cassandra

You can verify the status of Cassandra by using following command.

[root@fatdba default.conf]# nodetool status
Datacenter: datacenter1
=======================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
— Address Load Tokens Owns (effective) Host ID Rack
UN 127.0.0.1 175.32 KiB 256 100.0% 0277aea4-d06c-4175-8d57-6100101f0491 rack1

bash-4.2$ nodetool describecluster
Cluster Information:
Name: Test Cluster
Snitch: org.apache.cassandra.locator.DynamicEndpointSnitch
Partitioner: org.apache.cassandra.dht.Murmur3Partitioner
Schema versions:
1852b5d8-f9ba-3549-b4b7-eaae1da39062: [127.0.0.1]

Other checks to conform Cassandra status.

[root@fatdba default.conf]# service cassandra sattus
Usage: cassandra start|stop|status|restart|reload
[root@fatdba default.conf]# service cassandra status
● cassandra.service – LSB: distributed storage system for structured data
Loaded: loaded (/etc/rc.d/init.d/cassandra; bad; vendor preset: disabled)
Active: active (running) since Tue 2017-07-11 10:02:03 IST; 1min 37s ago
Docs: man:systemd-sysv-generator(8)
Process: 28704 ExecStart=/etc/rc.d/init.d/cassandra start (code=exited, status=0/SUCCESS)
Main PID: 28795 (java)
Memory: 8.0K
CGroup: /system.slice/cassandra.service
‣ 28795 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.131-3.b12.el7_3.x86_64/jre/bin/java -Xloggc:/var/log/cassandra/gc.log -ea -XX:+UseThreadPriorities -XX:Threa…

Jul 11 10:01:36 fatdba.localdomain systemd[1]: Starting LSB: distributed storage system for structured data…
Jul 11 10:01:37 fatdba.localdomain su[28714]: (to cassandra) root on none
Jul 11 10:02:03 fatdba.localdomain systemd[1]: Started LSB: distributed storage system for structured data.
Jul 11 10:02:03 fatdba.localdomain cassandra[28704]: Starting Cassandra: OK

[root@fatdba etc]#

[root@fatdba etc]# ps -ef|grep cassandra
cassand+ 28795 1 12 10:02 ? 00:00:01 /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.131-3.b12.el7_3.x86_64/jre/bin/java -Xloggc:/var/log/cassandra/gc.log -ea -XX:+UseThreadPriorities -XX:ThreadPriorityPolicy=42 -XX:+HeapDumpOnOutOfMemoryError -Xss256k -XX:StringTableSize=1000003 -XX:+AlwaysPreTouch -XX:-UseBiasedLocking -XX:+UseTLAB -XX:+ResizeTLAB -XX:+UseNUMA -XX:+PerfDisableSharedMem -Djava.net.preferIPv4Stack=true -XX:+UseParNewGC -XX:+UseConcMarkSweepGC -XX:+CMSParallelRemarkEnabled -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=1 -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -XX:CMSWaitDuration=10000 -XX:+CMSParallelInitialMarkEnabled -XX:+CMSEdenChunksRecordAlways -XX:+CMSClassUnloadingEnabled -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+PrintHeapAtGC -XX:+PrintTenuringDistribution -XX:+PrintGCApplicationStoppedTime -XX:+PrintPromotionFailure -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=10 -XX:GCLogFileSize=10M -Xms1024M -Xmx1024M -Xmn100M -XX:CompileCommandFile=/etc/cassandra/conf/hotspot_compiler -javaagent:/usr/share/cassandra/lib/jamm-0.3.0.jar -Dcassandra.jmx.local.port=7199 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.password.file=/etc/cassandra/jmxremote.password -Djava.library.path=/usr/share/cassandra/lib/sigar-bin -Dlogback.configurationFile=logback.xml -Dcassandra.logdir=/var/log/cassandra -Dcassandra.storagedir= -Dcassandra-pidfile=/var/run/cassandra/cassandra.pid -cp /etc/cassandra/conf:/usr/share/cassandra/lib/airline-0.6.jar:/usr/share/cassandra/lib/antlr-runtime-3.5.2.jar:/usr/share/cassandra/lib/asm-5.0.4.jar:/usr/share/cassandra/lib/caffeine-2.2.6.jar:/usr/share/cassandra/lib/cassandra-driver-core-3.0.1-shaded.jar:/usr/share/cassandra/lib/commons-cli-1.1.jar:/usr/share/cassandra/lib/commons-codec-1.9.jar:/usr/share/cassandra/lib/commons-lang3-3.1.jar:/usr/share/cassandra/lib/commons-math3-3.2.jar:/usr/share/cassandra/lib/compress-lzf-0.8.4.jar:/usr/share/cassandra/lib/concurrentlinkedhashmap-lru-1.4.jar:/usr/share/cassandra/lib/concurrent-trees-2.4.0.jar:/usr/share/cassandra/lib/disruptor-3.0.1.jar:/usr/share/cassandra/lib/ecj-4.4.2.jar:/usr/share/cassandra/lib/guava-18.0.jar:/usr/share/cassandra/lib/HdrHistogram-2.1.9.jar:/usr/share/cassandra/lib/high-scale-lib-1.0.6.jar:/usr/share/cassandra/lib/hppc-0.5.4.jar:/usr/share/cassandra/lib/jackson-core-asl-1.9.2.jar:/usr/share/cassandra/lib/jackson-mapper-asl-1.9.2.jar:/usr/share/cassandra/lib/jamm-0.3.0.jar:/usr/share/cassandra/lib/javax.inject.jar:/usr/share/cassandra/lib/jbcrypt-0.3m.jar:/usr/share/cassandra/lib/jcl-over-slf4j-1.7.7.jar:/usr/share/cassandra/lib/jctools-core-1.2.1.jar:/usr/share cassandra/lib/jflex-1.6.0.jar:/usr/share/cassandra/lib/jna-4.4.0.jar:/usr/share/cassandra/lib/joda-time-2.4.jar:/usr/share/cassandra/lib/json-simple-1.1.jar:/usr/share cassandra/lib/jstackjunit-0.0.1.jar:/usr/share/cassandra/lib/libthrift-0.9.2.jar:/usr/share/cassandra/lib/log4j-over-slf4j-1.7.7.jar:/usr/share/cassandra/lib/logback-classic-1.1.3.jar:/usr/share/cassandra/lib/logback-core-1.1.3.jar:/usr/share/cassandra/lib/lz4-1.3.0.jar:/usr/share/cassandra/lib/metrics-core-3.1.0.jar:/usr/share/cassandra/lib/metrics-jvm-3.1.0.jar:/usr/share/cassandra/lib/metrics-logback-3.1.0.jar:/usr/share/cassandra/lib/netty-all-4.0.44.Final.jar:/usr/share/cassandra/lib/ohc-core-0.4.4.jar:/usr/share/cassandra/lib/ohc-core-j8-0.4.4.jar:/usr/share/cassandra/lib/reporter-config3-3.0.3.jar:/usr/share/cassandra/lib/reporter-config-base-3.0.3.jar:/usr/share/cassandra/lib/sigar-1.6.4.jar:/usr/share/cassandra/lib/slf4j-api-1.7.7.jar:/usr/share/cassandra/lib/snakeyaml-1.11.jar:/usr/share/cassandra/lib/snappy-java-1.1.1.7.jar:/usr/share/cassandra/lib/snowball-stemmer-1.3.0.581.1.jar:/usr/share/cassandra/lib/ST4-4.0.8.jar:/usr/share/cassandra/lib/stream-2.5.2.jar:/usr/share/cassandra/lib/thrift-server-0.3.7.jar:/usr/share/cassandra/apache-cassandra-3.11.0.jar:/usr/share/cassandra/apache-cassandra-thrift-3.11.0.jar:/usr/share/cassandra/stress.jar: org.apache.cassandra.service.CassandraDaemon
root 28828 25570 0 10:02 pts/1 00:00:00 grep –color=auto cassandra

Cassandra has an inbuilt command line shell cqlsh to run query on the database/cluster. CQL is Cassandra Query language. To connect with the CQL CMD, run following command.

[root@fatdba default.conf]# cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cqlsh>
cqlsh>
cqlsh>

Lets do some tests on the command line.
– Let’s check the entire list of commands that exists for Cassandra terminal.

cqlsh> help

Documented shell commands:
===========================
CAPTURE CLS COPY DESCRIBE EXPAND LOGIN SERIAL SOURCE UNICODE
CLEAR CONSISTENCY DESC EXIT HELP PAGING SHOW TRACING

CQL help topics: ================ AGGREGATES CREATE_KEYSPACE DROP_TRIGGER TEXT ALTER_KEYSPACE CREATE_MATERIALIZED_VIEW DROP_TYPE TIME ALTER_MATERIALIZED_VIEW CREATE_ROLE DROP_USER TIMESTAMP ALTER_TABLE CREATE_TABLE FUNCTIONS TRUNCATE ALTER_TYPE CREATE_TRIGGER GRANT TYPES ALTER_USER CREATE_TYPE INSERT UPDATE APPLY CREATE_USER INSERT_JSON USE ASCII DATE INT UUID BATCH DELETE JSON BEGIN DROP_AGGREGATE KEYWORDS BLOB DROP_COLUMNFAMILY LIST_PERMISSIONS BOOLEAN DROP_FUNCTION LIST_ROLES COUNTER DROP_INDEX LIST_USERS CREATE_AGGREGATE DROP_KEYSPACE PERMISSIONS CREATE_COLUMNFAMILY DROP_MATERIALIZED_VIEW REVOKE CREATE_FUNCTION DROP_ROLE SELECT CREATE_INDEX DROP_TABLE SELECT_JSON

– Let me check the CLUSTER details and CONSISTENCY level (Default).

cqlsh> CONSISTENCY
Current consistency level is ONE.
cqlsh> describe cluster;

Cluster: Test Cluster
Partitioner: Murmur3Partitioner

– Now i am going to create a table.

cqlsh> CREATE TABLE prashant.dixit_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday timestamp, nationality text, weight text, height text );
ConfigurationException: Keyspace prashant doesn’t exist

Oops an error, this reads that the keyspace which i used doesn’t exists, let me quickly create one.
cqlsh> create keyspace prashant
… WITH replication = {‘class’:’SimpleStrategy’, ‘replication_factor’ : 3};

Let’s try to create the table once again.
cqlsh> CREATE TABLE prashant.dixit_alt_stats ( id UUID PRIMARY KEY, lastname text, birthday timestamp, nationality text, weight text, height text );

Done!

Now, lets query the newly created object and send the result to a file at any of the location.
For that we have the CAPTURE option, This command captures the output of a command and adds it to a file.

cqlsh> CAPTURE ‘/tmp/outputfile’
Now capturing query output to ‘/tmp/outputfile’.

[root@fatdba tmp]# more outputfile

id | birthday | height | lastname | nationality | weight
—-+———-+——–+———-+————-+——–

(0 rows)

Hope It Helps
Prashant Dixit

Posted in Basics | Tagged: cassandra | Leave a Comment »

New HISTORY command in SQLPlus on 12c Release 2 :)

Posted by FatDBA on July 5, 2017

The newly release Oracle Database version 12c Release 2 comes with more than 300 new features, this is one of the coolest (Non-Vital) new feature that i have noticed. This is the History command for SQLPlus … Finally!!
Me being a great fan of CMD prompt advocate i thought to share this one. No need to use RLWRAP rpm’s now.

Let me show you how to use this feature.

First you will have to enable this feature.

SQL> history
SP2-1650: History is off, use “SET HIST[ORY] ON” to enable History.
SQL> set history on
SQL>

Verify if it is enabled.

SQL> show history
history is ON and set to “100”

Execute some command on the prompt and check the history.

SQL> history
1 select count(*) from dba_segments;
2 select * from tab;
3 select * from dixit.tab;
4 select * from tab.dixit;
5 select * from dixit.t214;

In case if you want to run the command coming at order number 1, then do it like this …

SQL> history 1 run

COUNT(*)
———-
11393

If there is a need to edit the command at ID 5.

SQL> history 5 edit

SQL> history
1 select count(*) from dba_segments;
2 select * from tab;
3 select * from dixit.tab;
4 select * from tab.dixit;
5 select * from dixit.t214;
6 show history
7 select count(*) from dixit.t214;

Hope This Helps
Prashant Dixit

Posted in Basics | Leave a Comment »

How to create a DOCKER Image for Oracle Database!

Posted by FatDBA on June 18, 2017

Hey Mates,
I am back! 🙂 🙂

I was little free this weekend hence i planned to wet my hands to containerize Oracle 12c R2 using DOCKER!
I have already tried few of the possibilities with Dockers lately but this is my first time to create an Image for an Oracle Database 12c inside a Docker Container and was able to do with quite ease.

Before i start with the steps/methods, i would like to explain little nitty gritty of Docker.

What is a Docker?
Docker is a tool designed to make it easier to create, deploy, and run applications by using containers. Containers allow a developer to package up an application with all of the parts it needs, such as libraries and other dependencies, and ship it all out as one package. By doing so, thanks to the container, the developer can rest assured that the application will run on any other Linux machine regardless of any customized settings that machine might have that could differ from the machine used for writing and testing the code.
In a way, Docker is a bit like a virtual machine. But unlike a virtual machine, rather than creating a whole virtual operating system, Docker allows applications to use the same Linux kernel as the system that they’re running on and only requires applications be shipped with things not already running on the host computer.

Now a question might be asked — Who needs Docker ?
Docker is a tool that is designed to benefit both developers and system administrators, making it a part of many DevOps (developers + operations) toolchains. For developers, it means that they can focus on writing code without worrying about the system that it will ultimately be running on. It also allows them to get a head start by using one of thousands of programs already designed to run in a Docker container as a part of their application. For operations staff, Docker gives flexibility and potentially reduces the number of systems needed because of its small footprint and lower overhead.

Let’s start with the steps to create docker for Oracle 12.2.0.1 EE database.

I tried this test on Oracle Linux 7.3 using Docker build files for the Oracle Database (You can download them from Docker website, Github etc.).

I have divided the entire exercise in Four pointers:
1. Pre Activities
2. Installation
3. Creation of Container and Images.
4. Post Checks & Test Cases.

STEP 1: Pre Activities

If your system is registered with ULN, disable access to the ol7_x86_64_UEKR3 channel and enable access to the ol7_x86_64_UEKR4 channel.
If you use the Oracle Yum repository, disable the ol7_UEKR3 repository and enable the ol7_UEKR4 repository in the /etc/yum.repos.d/public-yum-ol7.repo file, for example:


[ol7_UEKR3]

name=Latest Unbreakable Enterprise Kernel Release 3 for Oracle Linux $releasever ($basearch)

baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/UEKR3/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=0
[ol7_UEKR4]

name=Latest Unbreakable Enterprise Kernel Release 4 for Oracle Linux $releasever ($basearch)

baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/UEKR4/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=1

Run the following command to upgrade the system to UEK R4:


# yum update
Replaced:

PackageKit-device-rebind.x86_64 0:0.8.9-11.0.1.el7 adwaita-gtk3-theme.x86_64 0:3.8.4-3.el7 firefox.x86_64 0:31.4.0-1.0.1.el7_0

gdm-libs.x86_64 1:3.8.4-32.0.1.el7 gnome-settings-daemon-updates.x86_64 0:3.8.6.1-12.el7 iwl100-firmware.noarch 0:39.31.5.1-36.el7

iwl1000-firmware.noarch 1:39.31.5.1-36.el7 iwl105-firmware.noarch 0:18.168.6.1-36.el7 iwl135-firmware.noarch 0:18.168.6.1-36.el7

iwl2000-firmware.noarch 0:18.168.6.1-36.el7 iwl2030-firmware.noarch 0:18.168.6.1-36.el7 iwl3160-firmware.noarch 0:22.0.7.0-36.el7

iwl3945-firmware.noarch 0:15.32.2.9-36.el7 iwl4965-firmware.noarch 0:228.61.2.24-36.el7 iwl5000-firmware.noarch 0:8.83.5.1_1-36.el7

iwl5150-firmware.noarch 0:8.24.2.2-36.el7 iwl6000-firmware.noarch 0:9.221.4.1-36.el7 iwl6000g2a-firmware.noarch 0:17.168.5.3-36.el7

iwl6000g2b-firmware.noarch 0:17.168.5.2-36.el7 iwl6050-firmware.noarch 0:41.28.5.1-36.el7 iwl7260-firmware.noarch 0:22.0.7.0-36.el7

nautilus-open-terminal.x86_64 0:0.20-3.el7 obex-data-server.x86_64 1:0.4.6-6.el7 totem-mozplugin.x86_64 1:3.8.2-5.el7

xorg-x11-drv-modesetting.x86_64 0:0.8.0-13.el7 xorg-x11-glamor.x86_64 0:0.6.0-2.20140918git347ef4f.el7
Complete!

Reboot the system, selecting the UEK R4 kernel if this is not the default boot kernel.
# systemctl reboot

If you use the Oracle Yum repository, enable the ol7_addons repository in the /etc/yum.repos.d/public-yum-ol7.repo file, for example:


[root@fatdba ~]# vi /etc/yum.repos.d/public-yum-ol7.repo
[ol7_addons]

name=Oracle Linux $releasever Add ons ($basearch)

baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/addons/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=1

enabled=1

STEP 2: Installation:


[root@fatdba ~]# yum install docker-engine

Loaded plugins: langpacks, ulninfo

ol7_UEKR4 | 1.2 kB 00:00:00

ol7_addons | 1.2 kB 00:00:00

ol7_latest | 1.4 kB 00:00:00

(1/2): ol7_addons/x86_64/updateinfo | 30 kB 00:00:00

(2/2): ol7_addons/x86_64/primary | 71 kB 00:00:01

ol7_addons 232/232

Resolving Dependencies

--> Running transaction check

---> Package docker-engine.x86_64 0:1.12.6-1.0.3.el7 will be installed

--> Processing Dependency: docker-engine-selinux >= 1.12.6-1.0.3.el7 for package: docker-engine-1.12.6-1.0.3.el7.x86_64

--> Running transaction check

---> Package docker-engine-selinux.noarch 0:1.12.6-1.0.3.el7 will be installed

--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================

Package Arch Version Repository Size

=============================================================================================================================================================================================

Installing:

docker-engine x86_64 1.12.6-1.0.3.el7 ol7_addons 19 M

Installing for dependencies:

docker-engine-selinux noarch 1.12.6-1.0.3.el7 ol7_addons 28 k
Transaction Summary

=============================================================================================================================================================================================

Install 1 Package (+1 Dependent package)
Total download size: 19 M

Installed size: 79 M

Is this ok [y/d/N]: y

Downloading packages:

(1/2): docker-engine-selinux-1.12.6-1.0.3.el7.noarch.rpm | 28 kB 00:00:01

(2/2): docker-engine-1.12.6-1.0.3.el7.x86_64.rpm | 19 MB 00:00:34

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 563 kB/s | 19 MB 00:00:34

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : docker-engine-selinux-1.12.6-1.0.3.el7.noarch 1/2

Installing : docker-engine-1.12.6-1.0.3.el7.x86_64 2/2

Verifying : docker-engine-selinux-1.12.6-1.0.3.el7.noarch 1/2

Verifying : docker-engine-1.12.6-1.0.3.el7.x86_64 2/2
Installed:

docker-engine.x86_64 0:1.12.6-1.0.3.el7
Dependency Installed:

docker-engine-selinux.noarch 0:1.12.6-1.0.3.el7
Complete!

With above step successfully completed, we are done with the docker installation. Before we start creating images on this container its best to modify the parameters as per our needs for applications or software’s. For example we have to modify the storage size for container (Default of 10GB), I have increased the size of the container to 20GB which will be quite enough for Oracle database 12c.


[root@fatdba ~]# vi /etc/sysconfig/docker-storage

[root@fatdba ~]# cat /etc/sysconfig/docker-storage

# This file may be automatically generated by an installation program.
# By default, Docker uses a loopback-mounted sparse file in

# /var/lib/docker. The loopback makes it slower, and there are some

# restrictive defaults, such as 100GB max storage.
# If your installation did not set a custom storage for Docker, you

# may do it below.
# Example: Use a custom pair of raw logical volumes (one for metadata,

# one for data).

# DOCKER_STORAGE_OPTIONS = --storage-opt dm.metadatadev=/dev/mylogvol/my-docker-metadata --storage-opt dm.datadev=/dev/mylogvol/my-docker-data
DOCKER_STORAGE_OPTIONS=--storage-opt dm.basesize=20G

Lets start the Docker via systemctl command.


[root@fatdba ~]#

[root@fatdba ~]# systemctl start docker

[root@fatdba ~]#
[root@fatdba ~]# ps -ef|grep docker

root 1984 1 0 16:59 ? 00:00:00 /usr/bin/dockerd --selinux-enabled --storage-opt dm.basesize=20G

root 1987 1984 0 16:59 ? 00:00:00 docker-containerd -l unix:///var/run/docker/libcontainerd/docker-containerd.sock --shim docker-containerd-shim --metrics-interval=0 --start-timeout 2m --state-dir /var/run/docker/libcontainerd/containerd --runtime docker-runc

root 2210 1799 0 17:04 pts/0 00:00:00 grep --color=auto docker

Lets verify if the size of the container; what we have modified in last step is reflecting now.


[root@fatdba ~]#

[root@fatdba ~]# docker info

Containers: 0

Running: 0

Paused: 0

Stopped: 0

Images: 0

Server Version: 1.12.6

Storage Driver: devicemapper

Pool Name: docker-249:0-201730898-pool

Pool Blocksize: 65.54 kB

 Base Device Size: 21.47 GB

Backing Filesystem: xfs

Data file: /dev/loop0

Metadata file: /dev/loop1

Data Space Used: 11.8 MB

Data Space Total: 107.4 GB

Data Space Available: 39.47 GB

Metadata Space Used: 581.6 kB

Metadata Space Total: 2.147 GB

Metadata Space Available: 2.147 GB

Thin Pool Minimum Free Space: 10.74 GB

Udev Sync Supported: true

Deferred Removal Enabled: false

Deferred Deletion Enabled: false

Deferred Deleted Device Count: 0

Data loop file: /var/lib/docker/devicemapper/devicemapper/data

WARNING: Usage of loopback devices is strongly discouraged for production use. Use `--storage-opt dm.thinpooldev` to specify a custom block storage device.

Metadata loop file: /var/lib/docker/devicemapper/devicemapper/metadata

Library Version: 1.02.135-RHEL7 (2016-11-16)

Logging Driver: json-file

Cgroup Driver: cgroupfs

Plugins:

Volume: local

Network: null host bridge overlay

Swarm: inactive

Runtimes: runc

Default Runtime: runc

Security Options: seccomp selinux

Kernel Version: 4.1.12-94.3.6.el7uek.x86_64

Operating System: Oracle Linux Server 7.3

OSType: linux

Architecture: x86_64

CPUs: 1

Total Memory: 974.3 MiB

Name: fatdba.localdomain

ID: ZUT5:J4TI:5BRZ:UQDM:5XMY:V4BS:VUDY:X7S6:3WT7:FPIA:SJ57:EHOY

Docker Root Dir: /var/lib/docker

Debug Mode (client): false

Debug Mode (server): false

Registry: https://index.docker.io/v1/

Insecure Registries:

127.0.0.0/8

[root@fatdba ~]#

[root@fatdba ~]#

[root@fatdba ~]#

I have already downloaded the Docker built files from dockers website and Oracle Database software from Oracle’s website.

[oracle@fatdba ~]$ ls -ltrh total 3.3G -rw-r--r--. 1 oracle oinstall 3.3G Mar 23 15:52 linuxx64_12201_database.zip -rw-r--r--. 1 oracle oinstall 4.2M Jun 17 17:12 master.zip [oracle@fatdba ~]$

Lets un compress the docker file.

[oracle@fatdba ~]$ unzip master.zip Archive: master.zip 7db020b25ea9d603b2fa97ed850ef4b372c1821e creating: docker-images-master/ extracting: docker-images-master/.gitattributes inflating: docker-images-master/.gitignore inflating: docker-images-master/.gitmodules creating: docker-images-master/ContainerCloud/ inflating: docker-images-master/ContainerCloud/.gitignore inflating: docker-images-master/ContainerCloud/README.md creating: docker-images-master/ContainerCloud/imag .................. ........................ ..............................
inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/21_start_managed_servers.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/22_start_webtier.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/23_start_webtier2.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/24_start_webtier_running.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/25_webtier_container_onmaster.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/26_call_sample.png inflating: docker-images-master/OracleWebLogic/workshops/multihost/images/28_docker_rmi.png inflating: docker-images-master/README.md
[oracle@fatdba ~]$ ls -ltrh total 3.3G -rw-r--r--. 1 oracle oinstall 3.3G Mar 23 15:52 linuxx64_12201_database.zip drwxr-xr-x. 15 oracle oinstall 4.0K Jun 15 20:40 docker-images-master -rw-r--r--. 1 oracle oinstall 4.2M Jun 17 17:12 master.zip

Now next we need to move/copy the database software to docker version directory. As we are doing for oracle database 12.2.0.1, lets move it to releavnt directory under docker home.

[oracle@fatdba ~]$ cp linuxx64_12201_database.zip docker-images-master/OracleDatabase/dockerfiles/12.2.0.1/

Step 3: Creation of Container and Images.
With all preparation is done, lets invoke the build docker script (buildDockerImage.sh).
There are few of the arguments which can be used for example -v for the version and -e for enterprise editions.

In order to communicate with the docker process from oracle user we need to sudo and run commands.


[oracle@fatdba dockerfiles]$ sudo ./buildDockerImage.sh -v 12.2.0.1 -e

[sudo] password for oracle:

Checking if required packages are present and valid...

linuxx64_12201_database.zip: OK

==========================

DOCKER info:

Containers: 0

Running: 0

Paused: 0

Stopped: 0

Images: 0

Server Version: 1.12.6

Storage Driver: devicemapper

Pool Name: docker-253:0-69348515-pool

Pool Blocksize: 65.54 kB

Base Device Size: 21.47 GB

Backing Filesystem: xfs

Data file: /dev/loop0

Metadata file: /dev/loop1

Data Space Used: 11.8 MB

Data Space Total: 107.4 GB

Data Space Available: 32.52 GB

Metadata Space Used: 581.6 kB

Metadata Space Total: 2.147 GB

Metadata Space Available: 2.147 GB

Thin Pool Minimum Free Space: 10.74 GB

Udev Sync Supported: true

Deferred Removal Enabled: false

Deferred Deletion Enabled: false

Deferred Deleted Device Count: 0

Data loop file: /var/lib/docker/devicemapper/devicemapper/data

WARNING: Usage of loopback devices is strongly discouraged for production use. Use `--storage-opt dm.thinpooldev` to specify a custom block storage device.

Metadata loop file: /var/lib/docker/devicemapper/devicemapper/metadata

Library Version: 1.02.135-RHEL7 (2016-11-16)

Logging Driver: json-file

Cgroup Driver: cgroupfs

Plugins:

Volume: local

Network: null host bridge overlay

Swarm: inactive

Runtimes: runc

Default Runtime: runc

Security Options: seccomp selinux

Kernel Version: 3.10.0-514.21.1.el7.x86_64

Operating System: Oracle Linux Server 7.3

OSType: linux

Architecture: x86_64

CPUs: 1

Total Memory: 976.5 MiB

Name: fatdba.localdomain

ID: ZUT5:J4TI:5BRZ:UQDM:5XMY:V4BS:VUDY:X7S6:3WT7:FPIA:SJ57:EHOY

Docker Root Dir: /var/lib/docker

Debug Mode (client): false

Debug Mode (server): false

Registry: https://index.docker.io/v1/

WARNING: bridge-nf-call-iptables is disabled

WARNING: bridge-nf-call-ip6tables is disabled

Insecure Registries:

127.0.0.0/8

==========================

Building image 'oracle/database:12.2.0.1-ee' ...

Sending build context to Docker daemon 3.454 GB

Step 1 : FROM oraclelinux:7-slim

7-slim: Pulling from library/oraclelinux

497341ef9d71: Pull complete

Digest: sha256:c5ead12c82851e6b316cbcabe8ddef18f42949484a0625b030ddbf726cc4bcdb

Status: Downloaded newer image for oraclelinux:7-slim

---> 0b44f4e6980d

Step 2 : MAINTAINER Prashant Dixit http://www.prashant.d.dixit.ericsson.com

---> Running in 46896f5576db

---> a914b944c9aa

Removing intermediate container 46896f5576db

Step 3 : ENV ORACLE_BASE /opt/oracle ORACLE_HOME /opt/oracle/product/12.2.0.1/dbhome_1 INSTALL_FILE_1 "linuxx64_12201_database.zip" INSTALL_RSP "db_inst.rsp" CONFIG_RSP "dbca.rsp.tmpl" PWD_FILE "setPassword.sh" PERL_INSTALL_FILE "installPerl.sh" RUN_FILE "runOracle.sh" START_FILE "startDB.sh" CREATE_DB_FILE "createDB.sh" SETUP_LINUX_FILE "setupLinuxEnv.sh" CHECK_SPACE_FILE "checkSpace.sh" CHECK_DB_FILE "checkDBStatus.sh" INSTALL_DB_BINARIES_FILE "installDBBinaries.sh"

---> Running in 34fba01bac8e

---> 8d660ec5fe0b

Removing intermediate container 34fba01bac8e

Step 4 : ENV INSTALL_DIR $ORACLE_BASE/install PATH $ORACLE_HOME/bin:$ORACLE_HOME/OPatch/:/usr/sbin:$PATH LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib CLASSPATH $ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

---> Running in ad3781a5a953

---> 483beb390a16

Removing intermediate container ad3781a5a953

Step 5 : COPY $INSTALL_FILE_1 $INSTALL_RSP $PERL_INSTALL_FILE $SETUP_LINUX_FILE $CHECK_SPACE_FILE $INSTALL_DB_BINARIES_FILE $INSTALL_DIR/

---> 4131886586e3

Removing intermediate container 4ac4ced73bf6

Step 6 : COPY $RUN_FILE $START_FILE $CREATE_DB_FILE $CONFIG_RSP $PWD_FILE $CHECK_DB_FILE $ORACLE_BASE/

---> 2938c306b151

Removing intermediate container d5b3c7095bc1

Step 7 : RUN chmod ug+x $INSTALL_DIR/*.sh && sync && $INSTALL_DIR/$CHECK_SPACE_FILE && $INSTALL_DIR/$SETUP_LINUX_FILE

---> Running in f04b4d3ec11e

Loaded plugins: ovl

Resolving Dependencies

--> Running transaction check

---> Package openssl.x86_64 1:1.0.1e-60.el7_3.1 will be installed

--> Processing Dependency: make for package: 1:openssl-1.0.1e-60.el7_3.1.x86_64

............

.......................

---> Package libtevent.x86_64 0:0.9.28-1.el7 will be installed

--> Finished Dependency Resolution
Dependencies Resolved
================================================================================

Package Arch Version Repository Size

================================================================================

Installing:

openssl x86_64 1:1.0.1e-60.el7_3.1 ol7_latest 713 k

oracle-database-server-12cR2-preinstall

x86_64 1.0-2.el7 ol7_latest 18 k

tar x86_64 2:1.26-31.el7 ol7_latest 843 k

.........

..............

xz x86_64 5.2.2-1.el7 ol7_latest 228 k
Transaction Summary

================================================================================

Install 5 Packages (+109 Dependent packages)
Total download size: 43 M

Installed size: 129 M

Downloading packages:

--------------------------------------------------------------------------------

Total 584 kB/s | 43 MB 01:15

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : libuuid-2.23.2-33.0.1.el7_3.2.x86_64 1/114

Installing : libblkid-2.23.2-33.0.1.el7_3.2.x86_64 2/114

Installing : tcp_wrappers-libs-7.6-77.el7.x86_64 3/114

..............

.........................

Installing : wget-1.14-13.el7.x86_64 114/114

Verifying : libXext-1.3.3-3.el7.x86_64 1/114

Verifying : libnfsidmap-0.25-15.el7.x86_64 2/114

Verifying : libXtst-1.2.2-2.1.el7.x86_64 3/114

Verifying : 2:ethtool-4.5-3.el7.x86_64 4/114

...............

.........................

Verifying : iputils-20160308-8.el7.x86_64 114/114
Installed:

openssl.x86_64 1:1.0.1e-60.el7_3.1

oracle-database-server-12cR2-preinstall.x86_64 0:1.0-2.el7
Dependency Installed:

GeoIP.x86_64 0:1.5.0-11.el7

acl.x86_64 0:2.2.51-12.el7

bind-libs.x86_64 32:9.9.4-38.el7_3.3

.................

............................

xorg-x11-utils.x86_64 0:7.5-14.el7

xorg-x11-xauth.x86_64 1:1.0.9-1.el7

xz.x86_64 0:5.2.2-1.el7
Complete!

Loaded plugins: ovl

Cleaning repos: ol7_UEKR4 ol7_latest

Cleaning up everything

---> 101bf83e6a49

Removing intermediate container f04b4d3ec11e
Step 8 : USER oracle

---> Running in 254ccd228112

---> 287e020fa0ed

Removing intermediate container 254ccd228112

Step 9 : RUN $INSTALL_DIR/$INSTALL_DB_BINARIES_FILE EE

---> Running in 5469cd105f01

Archive: linuxx64_12201_database.zip

creating: database/

creating: database/sshsetup/

inflating: database/sshsetup/sshUserSetup.sh

creating: database/rpm/

inflating: database/rpm/cvuqdisk-1.0.10-1.rpm

.................

...........................

inflating: database/stage/install1.jar

inflating: database/runInstaller

Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 16480 MB Passed

Checking swap space: must be greater than 150 MB. Actual 1828 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-06-17_07-18-01PM. Please wait ...[WARNING] [INS-32055] The Central Inventory is located in the Oracle base.

ACTION: Oracle recommends placing this Central Inventory in a location outside the Oracle base directory.

You can find the log of this install session at:

/opt/oracle/oraInventory/logs/installActions2017-06-17_07-18-01PM.log

The installation of Oracle Database 12c was successful.

Please check '/opt/oracle/oraInventory/logs/silentInstall2017-06-17_07-18-01PM.log' for more details.
As a root user, execute the following script(s):

1. /opt/oracle/oraInventory/orainstRoot.sh

2. /opt/oracle/product/12.2.0.1/dbhome_1/root.sh
Successfully Setup Software.
This is perl 5, version 22, subversion 0 (v5.22.0) built for x86_64-linux-thread-multi
Copyright 1987-2015, Larry Wall
Perl may be copied only under the terms of either the Artistic License or the

GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on

this system using "man perl" or "perldoc perl". If you have access to the

Internet, point your browser at http://www.perl.org/, the Perl Home Page.
---> db1aebac731b

Removing intermediate container 5469cd105f01

Step 10 : USER root

---> Running in 40c84cd51204

---> dadbabb39b54

Removing intermediate container 40c84cd51204

Step 11 : RUN $ORACLE_BASE/oraInventory/orainstRoot.sh && $ORACLE_HOME/root.sh && rm -rf $INSTALL_DIR

---> Running in f290a72da88d

Changing permissions of /opt/oracle/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.
Changing groupname of /opt/oracle/oraInventory to dba.

The execution of the script is complete.

Check /opt/oracle/product/12.2.0.1/dbhome_1/install/root_3c014b3eef40_2017-06-17_20-43-44-737487272.log for the output of root script

---> 281e843af9d6

Removing intermediate container f290a72da88d

Step 12 : USER oracle

---> Running in 3bbff924c8fe

---> a6da7b879840

Removing intermediate container 3bbff924c8fe

Step 13 : WORKDIR /home/oracle

---> Running in 4ed5aa548eec

---> f081c5739e78

Removing intermediate container 4ed5aa548eec

Step 14 : VOLUME $ORACLE_BASE/oradata

---> Running in 6c808b5e0678

---> 0050ad20c1fe

Removing intermediate container 6c808b5e0678

Step 15 : EXPOSE 1521 5500

---> Running in 23a52c4ba0d4

---> fe2aa564cde9

Removing intermediate container 23a52c4ba0d4

Step 16 : CMD exec $ORACLE_BASE/$RUN_FILE

---> Running in ca819e2f43d3

---> fb879885b6cf

Removing intermediate container ca819e2f43d3

Successfully built fb879885b6cf
Oracle Database Docker Image for 'ee' version 12.2.0.1 is ready to be extended:
--> oracle/database:12.2.0.1-ee
Build completed in 7290 seconds.
[oracle@fatdba dockerfiles]$

Alright, so the creation of the container is successfully completed with that last success message, it was created under 7290 seconds and the name of the image is 12.2.0.1-ee and with image id fb879885b6cf.
Lets conform the same using docker commands.

[root@fatdba /]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE oracle/database 12.2.0.1-ee fb879885b6cf 4 hours ago 14.84 GB oraclelinux 7-slim 0b44f4e6980d 2 weeks ago 114.4 MB

Now next step is to create Oracle database instance inside of this container and to start the same in the end. For that we have to run the ‘docker run’ command with few of the necessary arguments i.e. -p for port, –name which is to give any understandable name to the container,

[oracle@fatdba ~]$ [oracle@fatdba ~]$ sudo docker run -p 1521:1521 --name pdixit1 oracle/database:12.2.0.1-ee ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: fp7rcB5yT6w=1
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-JUN-2017 01:51:40 Copyright (c) 1991, 2016, Oracle. All rights reserved. Starting /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 12.2.0.1.0 - Production System parameter file is /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora Log messages written to /opt/oracle/diag/tnslsnr/3546456aff5c/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 18-JUN-2017 01:51:42 Uptime 0 days 0 hr. 0 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/3546456aff5c/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521))) The listener supports no services The command completed successfully [WARNING] [DBT-10102] The listener configuration is not selected for the database. EM DB Express URL will not be accessible. CAUSE: The database should be registered with a listener in order to access the EM DB Express URL. ACTION: Select a listener to be registered or created with the database. Copying database files 1% complete 13% complete 25% complete Creating and starting Oracle instance 26% complete 30% complete 31% complete 35% complete 38% complete 39% complete 41% complete Completing Database Creation 42% complete 43% complete 44% complete 46% complete 47% complete 50% complete Creating Pluggable Databases 55% complete 75% complete Executing Post Configuration Actions 100% complete Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details. SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 18 02:19:57 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> System altered. SQL> Pluggable database altered. SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production ######################### DATABASE IS READY TO USE! ######################### The following output is now a tail of the alert.log: ORCLPDB1(3):CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 2017-06-18T02:19:41.269624+00:00 ORCLPDB1(3):Completed: CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ORCLPDB1(3):ALTER DATABASE DEFAULT TABLESPACE "USERS" ORCLPDB1(3):Completed: ALTER DATABASE DEFAULT TABLESPACE "USERS" 2017-06-18T02:19:58.014477+00:00 ALTER SYSTEM SET control_files='/opt/oracle/oradata/ORCLCDB/control01.ctl' SCOPE=SPFILE; ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE 2017-06-18T02:20:03.021412+00:00 Completed: ALTER PLUGGABLE DATABASE ORCLPDB1 SAVE STATE 2017-06-18T02:23:27.637232+00:00 ORCLPDB1(3):Resize operation completed for file# 10, old size 358400K, new size 368640K

Great, we are done with the DB Instance creation. The message text “DATABASE IS READY TO USE” is the conformation that all went successfull.
Note: The other entries next to that OK message is the snippet from database alert log to show current status of the database.

Next we can try creating a new password for DB Image because if you remember the default password what docker created during ‘docker run’ command was little complicated “fp7rcB5yT6w=1“.


[oracle@fatdba ~]$ sudo docker exec pdixit1 ./setPassword.sh oracle90

The Oracle base remains unchanged with value /opt/oracle
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 18 02:31:10 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL>

User altered.
SQL>

User altered.
SQL>

Session altered.
SQL>

User altered.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

STEP 4: “Post Checks & Test Cases”.

In order to see running containers, use docker ps:


[oracle@fatdba ~]$ sudo docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

3546456aff5c oracle/database:12.2.0.1-ee "/bin/sh -c 'exec $OR" 51 minutes ago Up 13 minutes 0.0.0.0:1521->1521/tcp, 5500/tcp pdixit1

[oracle@fatdba ~]$

If you wonder what is going on inside the container, you can create a shell in it using the ‘docker exec’ command. This can not be done if the container is stopped, only if the container is running:
Unlike an attached start, a docker exec bash session can exit without interrupting the container.


[oracle@fatdba ~]$ sudo docker exec -ti pdixit1 bash

[oracle@3546456aff5c ORCLCDB]$ pwd

/opt/oracle/oradata/ORCLCDB
[oracle@3546456aff5c ORCLCDB]$ ls -ltr

total 2000808

drwxr-x---. 2 oracle oinstall 4096 Jun 18 01:57 pdbseed

-rw-r-----. 1 oracle oinstall 34611200 Jun 18 01:59 temp01.dbf

drwxr-x---. 2 oracle oinstall 4096 Jun 18 02:19 ORCLPDB1

-rw-r-----. 1 oracle oinstall 209715712 Jun 18 02:30 redo01.log

-rw-r-----. 1 oracle oinstall 209715712 Jun 18 02:30 redo03.log

-rw-r-----. 1 oracle oinstall 5251072 Jun 18 02:30 users01.dbf

-rw-r-----. 1 oracle oinstall 838868992 Jun 18 02:40 system01.dbf

-rw-r-----. 1 oracle oinstall 492838912 Jun 18 02:45 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 62922752 Jun 18 02:45 undotbs01.dbf

-rw-r-----. 1 oracle oinstall 209715712 Jun 18 02:45 redo02.log

-rw-r-----. 1 oracle oinstall 18726912 Jun 18 02:46 control01.ctl
[oracle@3546456aff5c ~]$ . oraenv

ORACLE_SID = [] ? ORCLCDB

The Oracle base remains unchanged with value /opt/oracle

[oracle@3546456aff5c ~]$ !sql

bash: !sql: event not found

[oracle@3546456aff5c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 18 02:50:36 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name, open_mode, log_mode from v$database;
NAME OPEN_MODE LOG_MODE

--------- -------------------- ------------

ORCLCDB READ WRITE NOARCHIVELOG
SQL>

SQL>

SQL>

SQL>

SQL> show user

USER is "SYS"

SQL> COLUMN name FORMAT A30

SQL> SELECT name, pdb FROM v$services ORDER BY name;
NAME PDB

------------------------------ --------------------------------------------------------------------------------------------------------------------------------

ORCLCDB CDB$ROOT

ORCLCDBXDB CDB$ROOT

SYS$BACKGROUND CDB$ROOT

SYS$USERS CDB$ROOT

orclpdb1 ORCLPDB1
SQL> alter session set container=orclpdb1;
Session altered.
SQL> show con_name
CON_NAME

------------------------------

ORCLPDB1

SQL> show user

USER is "SYS"
SQL> create user dixit identified by oracle90;
User created.
SQL> grant connect, create session, grant create any table, alter any table, select any dictionary to dixit;
Grant succeeded.
SQL> conn dixit/oracle90@orclpdb1

Connected.

SQL>

SQL> show user

USER is "DIXIT"
SQL>

SQL> conn dixit/oracle90@orclpdb1

Connected.

SQL> CREATE TABLE DIXIT.WLSLOG(time_stamp VARCHAR2(45) PRIMARY KEY,category VARCHAR2(25),type VARCHAR2(25),servername VARCHAR2(25),code VARCHAR2(25),msg VARCHAR2(45));
Table created.
SQL> select * from tab;
TNAME

--------------------------------------------------------------------------------

TABTYPE CLUSTERID

------- ----------

WLSLOG

TABLE

Some more checks on this new DOCKER container.


[oracle@fatdba ~]$

[oracle@fatdba ~]$ sudo docker stats 3546456aff5c

CONTAINER CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS

3546456aff5c 0.70% 1.889 GiB / 2.889 GiB 65.40% 1.386 kB / 1.086 kB 1.326 GB / 88.57 MB 52

CONTAINER CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS

3546456aff5c 0.70% 1.889 GiB / 2.889 GiB 65.40% 1.386 kB / 1.086 kB 1.326 GB / 88.57 MB 52

CONTAINER CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS

3546456aff5c 0.69% 1.889 GiB / 2.889 GiB 65.40% 1.386 kB / 1.086 kB 1.326 GB / 88.6 MB 52

CONTAINER CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS

3546456aff5c 0.69% 1.889 GiB / 2.889 GiB 65.40% 1.386 kB / 1.086 kB 1.326 GB / 88.6 MB 52

CONTAINER CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS

3546456aff5c 0.39% 1.889 GiB / 2.889 GiB 65.40% 1.386 kB / 1.086 kB 1.326 GB / 88.6 MB 52

[oracle@fatdba ~]$

[oracle@fatdba ~]$


[oracle@fatdba ~]$

[oracle@fatdba ~]$ sudo docker top 3546456aff5c

[sudo] password for oracle:

UID PID PPID C STIME TTY TIME CMD

oracle 6399 6388 0 08:00 ? 00:00:00 /bin/bash /opt/oracle/runOracle.sh

oracle 6423 6399 0 08:00 ? 00:00:00 /opt/oracle/product/12.2.0.1/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle 6430 6399 0 08:00 ? 00:00:00 ora_pmon_ORCLCDB

oracle 6432 6399 0 08:00 ? 00:00:00 ora_clmn_ORCLCDB

oracle 6434 6399 0 08:00 ? 00:00:00 ora_psp0_ORCLCDB

oracle 6436 6399 0 08:00 ? 00:00:00 ora_vktm_ORCLCDB

oracle 6440 6399 0 08:00 ? 00:00:00 ora_gen0_ORCLCDB

oracle 6442 6399 0 08:00 ? 00:00:00 ora_mman_ORCLCDB

oracle 6446 6399 0 08:00 ? 00:00:00 ora_gen1_ORCLCDB

oracle 6450 6399 0 08:00 ? 00:00:00 ora_diag_ORCLCDB

oracle 6452 6399 0 08:00 ? 00:00:00 ora_ofsd_ORCLCDB

oracle 6456 6399 0 08:00 ? 00:00:00 ora_dbrm_ORCLCDB

oracle 6458 6399 0 08:00 ? 00:00:00 ora_vkrm_ORCLCDB

oracle 6460 6399 0 08:00 ? 00:00:00 ora_svcb_ORCLCDB

oracle 6462 6399 0 08:00 ? 00:00:00 ora_pman_ORCLCDB

oracle 6464 6399 0 08:00 ? 00:00:00 ora_dia0_ORCLCDB

oracle 6466 6399 0 08:00 ? 00:00:00 ora_dbw0_ORCLCDB

oracle 6468 6399 0 08:00 ? 00:00:00 ora_lgwr_ORCLCDB

oracle 6470 6399 0 08:00 ? 00:00:00 ora_ckpt_ORCLCDB

oracle 6472 6399 0 08:00 ? 00:00:00 ora_smon_ORCLCDB

oracle 6474 6399 0 08:00 ? 00:00:00 ora_smco_ORCLCDB

oracle 6476 6399 0 08:00 ? 00:00:00 ora_w000_ORCLCDB

oracle 6478 6399 0 08:00 ? 00:00:00 ora_reco_ORCLCDB

oracle 6480 6399 0 08:00 ? 00:00:00 ora_w001_ORCLCDB

oracle 6482 6399 0 08:00 ? 00:00:00 ora_lreg_ORCLCDB

oracle 6484 6399 0 08:00 ? 00:00:00 ora_pxmn_ORCLCDB

oracle 6488 6399 0 08:00 ? 00:00:01 ora_mmon_ORCLCDB

oracle 6490 6399 0 08:00 ? 00:00:00 ora_mmnl_ORCLCDB

oracle 6492 6399 0 08:00 ? 00:00:00 ora_d000_ORCLCDB

oracle 6494 6399 0 08:00 ? 00:00:00 ora_s000_ORCLCDB

oracle 6496 6399 0 08:00 ? 00:00:00 ora_tmon_ORCLCDB

oracle 6506 6399 0 08:00 ? 00:00:00 ora_tt00_ORCLCDB

oracle 6508 6399 0 08:00 ? 00:00:00 ora_tt01_ORCLCDB

oracle 6510 6399 0 08:00 ? 00:00:00 ora_tt02_ORCLCDB

oracle 6512 6399 0 08:00 ? 00:00:00 ora_aqpc_ORCLCDB

oracle 6516 6399 0 08:00 ? 00:00:00 ora_p000_ORCLCDB

oracle 6518 6399 0 08:00 ? 00:00:00 ora_p001_ORCLCDB

oracle 6520 6399 0 08:00 ? 00:00:00 ora_p002_ORCLCDB

oracle 6522 6399 0 08:00 ? 00:00:00 ora_p003_ORCLCDB

oracle 6526 6399 0 08:00 ? 00:00:00 ora_qm02_ORCLCDB

oracle 6530 6399 0 08:00 ? 00:00:00 ora_q002_ORCLCDB

oracle 6532 6399 0 08:00 ? 00:00:00 ora_q003_ORCLCDB

oracle 6745 6399 0 08:01 ? 00:00:01 ora_cjq0_ORCLCDB

oracle 6750 6399 0 08:01 ? 00:00:00 tail -f /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/alert_ORCLCDB.log

oracle 6869 6399 0 08:01 ? 00:00:00 ora_w002_ORCLCDB

oracle 6917 6399 0 08:02 ? 00:00:00 ora_w003_ORCLCDB

oracle 7058 6399 0 08:06 ? 00:00:00 ora_w004_ORCLCDB

[oracle@fatdba ~]$

Hope It Helps
Prashant Dixit

Posted in Advanced | Leave a Comment »

What’s new with Oracle 12C Release 2: RMAN Recovery Options

Posted by FatDBA on March 24, 2017

Hi Everyone,

Oracle introduced the long awaited Database 12c Release 2 earlier this year at Oracle Open World. The latest major release since 2013, originally announced in beta at Oracle Open World 2015, may have left some customers scratching their head after hearing the latest release would be what Oracle is calling “Cloud first availability”.

Some of the key Oracle Database 12c Release 2 highlights are:
Multitenancy improvements, which allow for more databases to run on a single machine, 4,096 to be exact, up from 252 in 12.1.
Performance increases with SQL Performance Analyzer, DB Replay and SQL Plan Management. Users can now increase query execution with the Optimizer Statistics Advisor, SQL JOIN processing.
Availability enhanced features in RMAN like table recovery, transport data across platforms, enhancements related to online operations and ongoing enhancements with Oracle Data Pump, SQL*Loader and external tables.

One of the coolest feature i have noticed during recent tests of Oracle 12c R2 was related with the RMAN recovery.
With this newest offering from Oracle Corporation now you can “RECOVER DATABASE UNTIL AVAILABLE REDO” 🙂

Hope That Helps
Prashant Dixit

Posted in Basics | Leave a Comment »

Golden Gate High Availability using XAG Bundles!

Posted by FatDBA on November 9, 2016

Oracle Grid Infrastructure Bundled Agents (XAG) are Oracle Grid Infrastructure components that provide the HA framework to application resources and resource types managed through the bundled agent management interface, AGCTL.
This post will help to understand, implement the XAG agents to have the HA for Golden Gate.

To begin installation, this zip file needs to be downloaded and expanded in a temporary directory.
The $XAG_HOME and sub-directories must be owned by Oracle Grid Infrastructure install owner. The setup script, xagsetup.sh, must be run as the Oracle Grid Infrastructure install owner. The xagsetup.sh script offers the option to install local only (the default) or to install on all nodes in the cluster or, a subset of nodes in the cluster where the application is targeted to run. When deploying the bundled agents to multiple nodes in the cluster, the xagsetup.sh is executed on only one node of the cluster, and the xagsetup.sh script will attempt to create $XAG_HOME directory on all remote nodes.

[oracle@monkey02 xag]$ ./xagsetup.sh –install –directory /u01/app/grid/product/xag –all_nodes
Installing Oracle Grid Infrastructure Agents on: monkey01
Installing Oracle Grid Infrastructure Agents on: monkey02
Installation of Oracle Grid Infrastructure Agents Successfull!

Next step is where you creates the VIP resource that will be used by the GG resource to relocate itself on the other remaining nodes when needed.
[root@monkey02 bin]# ./appvipcfg create -network=1 -ip=192.168.81.101 -vipname=testxag-ggs-vip -user=oracle -group=oinstall

Assign the required set of permissions to the grid install owner on this newly created VIP resource.
[root@monkey02 bin]# ./crsctl setperm resource testxag-ggs-vip -u user:oracle:r-x

Alright, so the VIP resource is created and is running fine.

——————————————————————————-
Cluster Resources
——————————————————————————–
testxag-ggs-vip
1 ONLINE ONLINE monkey02 STABLE

Next we will create the golden gate resource and configure it from the golden gate user.
Call the AGCTL utlity from the XAG_HOME location and choose all required parameters/arguments with GG processes those you want to monitor through this.

[oracle@monkey02 bin]#./agctl add goldengate xagtest_ggs \
–gg_home /olala/app/ggg \
–instance_type dual \
–nodes monkey01,monkey02 \
–vip_name testxag-ggs-vip \
–filesystems ora.monkeydds.monkeydds_vol.acfs \
–databases ora.tunedb.db \
–oracle_home /u01/app/oracle/product/12.1.0.2/dbhome_1 \
–monitor_extracts ETEST1,ETEST2,ETEST3,PTEST1,EPUMP1,EPUMP2,EPUMP3 \
–monitor_replicats PTEST1,PTEST2,PTEST3 \

Options & arguments explained:
–gg_home **GoldenGate installation directory**
–instance_type <source|target|dual*** —- dual is for bi directional replication.
–oracle_home ***path***
–databases ***associated database resources***
–monitor_extracts ***ext1,ext2,ext3,…***
–monitor_replicats ***rep1,rep2,rep3,…***
–vip_name ***VIP resource name***
–filesystems ***acfs1,acfs2,…***

Okay, now when we are done with the configuration of the GG resource, lets verify it.

[oracle@monkey02 bin]$ ./agctl config goldengate xagtest_ggs
GoldenGate location is: /olala/app/ggg
GoldenGate instance type is: dual
Configured to run on Nodes: monkey01 monkey02
ORACLE_HOME location is: /u01/app/oracle/product/12.1.0.2/dbhome_1
Databases needed: ora.tunedb.db
File System resources needed: ora.monkeydds.monkeydds_vol.acfs
VIP name: testxag-ggs-vip
EXTRACT groups to monitor: ETEST1,ETEST2,ETEST3,PUMP1,PUMP2,PUMP3
REPLICAT groups to monitor: RTEST1,RTEST2,RTEST3
Critical EXTRACT groups:
Critical REPLICAT groups:
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

Alright, so this is what we want. Next we have to start the Golden Gate resource which we have created at the last step.

[oracle@monkey02 bin]$ ./agctl start goldengate xagtest_ggs

In the beginning the status will be in STARTING mode

xag.lab_ggate.goldengate
1 ONLINE OFFLINE monkey01 STARTING
——————————————————————————–

Now, once the resource is started the status of the cluster resource will be STABLE state.

xag.xagtest_ggs.goldengate
1 ONLINE OFFLINE monkey01 STABLE
——————————————————————————–

Now, we are good to go and can perform manual tests like relocation of resources on other nodes.

Hope It Helps!
Prashant Dixit

Share this:
Twitter
Facebook
Google
LinkedIn
Email
Like this:
Like Loading...

Posted in Advanced | Tagged: golden gate | 1 Comment »

« Previous Entries

Tales From A Lazy Fat Oracle DBA

$ prashantdixit/oracle90@ace as sysdba

Likes

Archives

Categories

Blogs I Follow

Cause I Support!!

Subscribe

Prashant Dixit is the FatDBA

My Twitter Feeds

Oracle Radio

Disclaimer!

Are the Cardinality Estimates Correct in my Execution Plan ?

How to tune the IO contentions related with the Compaction in Cassandra ?

Golden Gate Logdump Utility: How to find RBA using TIMESTAMP.

Installing Cassandra Cluster Manager (CCM) on Oracle Linux 7

Cassandra NodeTool Utility

Installing Cassandra 3.0 on OEL7 – Steps!

New HISTORY command in SQLPlus on 12c Release 2 :)

How to create a DOCKER Image for Oracle Database!

What’s new with Oracle 12C Release 2: RMAN Recovery Options

Golden Gate High Availability using XAG Bundles!