Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 134,693
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Archive for the ‘Basics’ Category

Core/Basics/Theory

No active extraction maps – Golden Gate STATS command, what’s that ?

Posted by FatDBA on April 8, 2018

Hey Mates,

Not sure if you guys have ever encountered a situation where you’ve got a return message “No active extraction maps” from Golden Gate STATS command against your Golden Gate process.
For GG newcomers, STATS command is used to display statistics for one or more Extract,Pump/Replicat groups and the output includes DML and DDL operations that are included in the Oracle GoldenGate configuration.

Now coming back to the error scenario. Let’s see where we got that message and what does that means.

GGSCI (rac1.localdomain as ggadmin1@dixitdb1) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     GGTUNEX1    00:00:03      00:00:08
EXTRACT     RUNNING     GGTUNPU1    00:00:00      00:00:01
REPLICAT    RUNNING     GGTUNRP1    00:00:00      00:00:03

Now when i tried to check statistics for my processes, we’ve got a message which says that there are “No active extraction maps”

GGSCI (rac1.localdomain as ggadmin1@dixitdb1) 18> stats GGTUNPU1

Sending STATS request to EXTRACT GGTUNPU1 ...
No active extraction maps.

GGSCI (rac1.localdomain as ggadmin1@dixitdb1) 22> stats GGTUNPU1

Sending STATS request to EXTRACT GGTUNPU1 ...
No active extraction maps.

So what does that means ??
The message that is displayed indicates that nothing has been processed in the extract or replicat. Once data for the tables in the extract and replicat are processed (captured from or applied to DB) the STATS command produces processing statistics.

Let me try to do some manipulations at the source table which is part of replication and see if this brings something to STATS results.

SQL> update emp set ENAME='KARTIKEY' where EMPNO=8090;
1 row updated.

SQL> commit;
Commit complete.

Let’s try now.

GGSCI (rac1.localdomain as ggadmin1@dixitdb1) 43> stats GGTUNEX1

Sending STATS request to EXTRACT GGTUNEX1 ...

Start of Statistics at 2018-04-05 04:03:08.

Output to /acfsmounts/acfsggv1/ggdir/dirdat/s1:
Extracting from SCOTT.EMP to SCOTT.EMP:

*** Total statistics since 2018-04-05 03:59:38 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Daily statistics since 2018-04-05 03:59:38 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Hourly statistics since 2018-04-05 03:59:38 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

*** Latest statistics since 2018-04-05 03:59:38 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   1.00

End of Statistics.

Yup, we’ve got stats for the process now after the change.

Hope It Helps
Prashant

Advertisements

Posted in Basics | Tagged: | Leave a Comment »

Some new features of Oracle Database 18c

Posted by FatDBA on February 28, 2018

Hey Everyone,

Today’s post is to discuss few of the new features (Small but nice) of the all new Oracle 18c (Not In depth) that i have tested.

0. Oracle Database 18c is the first version of the product to follow a yearly release pattern. Yup, that’s correct!

1. Read Only Oracle Home (ROOH)
Yes, finally we have the RO Oracle Homes.

2. Columnar Format Exadata Flash Cache Enhancements
With Oracle Database 18c we now support accessing non-HCC objects in the Exadata Flash cache in full Database In-Memory columnar format. In 12.2 this was restricted to just HCC objects.

3. Scalable Sequences
A scalable sequence is particularly efficient when used to generate unordered primary or unique keys for data ingestion workloads having high level of concurrency. Scalable sequences significantly reduce the sequence and index block contention.

4. Converting Normal or High Redundancy Disk Groups to Flex Disk Groups without Restricted Mount
You can convert a conventional disk group (disk group created before Oracle Database18c) to an Oracle ASM flex disk group without using the restrictive mount (MOUNTED RESTRICTED) option.

5. ALTER SYSTEM CANCEL SQL
Another way to kill/cancel a SQL in a session.
The syntax would be like …

ALTER SYSTEM CANCEL SQL ‘SID, SERIAL[, @INST_ID][, SQL_ID]’;
e.g. ALTER SYSTEM CANCEL SQL ‘448, 98175, @1, 761hchah78addfj’;

6. The default value of PARALLEL_THREADS_PER_CPU is finally set 1 as default!
PARALLEL_THREADS_PER_CPU describes the number of parallel execution processes or threads that a CPU can handle during parallel execution. Good Move! 🙂

7. For users of Exadata and Real Application Clusters (RAC), Oracle Database 18c brings changes that will enable a significant reduction in the amount of undo that needs to be transferred across the interconnect. It achieves this by using RDMA, over the Infiniband connection, to access the undo blocks in the remote instance. This feature combined with a local commit cache significantly improves the throughput of some OLTP workloads when running on top of RAC.

8. 18c Authenticate and authorize Oracle users directly with Microsoft Active Directory

9. New Oracle Spatial features in Oracle Database 18c include distributed transactions, sharding, easier to use web services admin console.

 

Hope It Helps
Prashant Dixit

Posted in Basics | Tagged: , | 2 Comments »

All about Oracle 12c Database In-Memory!

Posted by FatDBA on December 27, 2017

Hi Mates,

Today i will discuss about the Database In-Memory option of Oracle 12c databases. I know i am little late to add about this feature but recently I’ve implemented the option for one of my customer in Latin Americas region and there I’ve got more exposure to understand it better and implement the feature.

So, lets start with most probably my last post of the year!

Recently i have got a chance to test and to benchmark the performance gains in one of out Pre-Prod environment. This feature applies on Tablespaces, Tables, MViews, (Sub) Partitions (Except objects owned by SYS, SYSTEM and SYSAUX). To understand the feature i would first like to shed some light on two of the ways how Oracle stores tables on both Disk and Memory using conventional ‘Row Format’ and with the all new In-Memory ‘Column’ format.

Row Arrangement: Is the same old traditional method to store data in row formats. This is best for OLTP systems as queries runs faster with this approach as it quickly fetches all of the columns in a record.
Column Based Arrangement: This way it stores records in a separate column store. This proves good for OLAP systems where large set of data is chosen but only for few number of columns.

Hence, based on above two methods or data arrangements in database it is clear that the row based method is best for DMLs and column based arrangement is good when selecting large portion of data, so both of the two methods have their own respective pros and cons. But starting from Oracle 12.1.0.2 we have the all new feature of ‘DB In-Memory’ which use best of both the approaches. I mean it uses both row and column arrangements to keep data in memory. Our smart optimizer automatically knows which query to route as per the workload (OLTP and for Analytical processing).

The In-Memory feature uses the IM Column store which is a new occupant of the SGA (In-Memory Area). This In-Memory Area is adjusted by a parameter INMEMORY_SIZE.

As far as IM Column Store it is filled by the information collected by worker processes e.g. w001, w002 etc.; each of the worker process updates the IM Compression Units or IMCUs.

So, enough the background and internals involved, lets jump to the real work and check how this thing practically works.
First check the minimum comparability of the database

COMPATIBLE = 12.1.0.0.0

Lets first enable the in-memory column store. There are many of the related parameters and are given below.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled                 boolean     TRUE
inmemory_clause_default              string
inmemory_expressions_usage           string      ENABLE
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     0
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0
inmemory_trickle_repopulate_servers_ integer     1
percent
inmemory_virtual_columns             string      MANUAL
optimizer_inmemory_aware             boolean     TRUE

Before i show next logs on how to enable it, i would first like to show you the database startup details with no In-Memory enabled.

SQL> startup
ORACLE instance started.

Total System Global Area 1191182336 bytes
Fixed Size                  8620032 bytes
Variable Size             771753984 bytes
Database Buffers          402653184 bytes
Redo Buffers                8155136 bytes
Database mounted.
Database opened.
SQL>

Okay now lets enable it!
Now a question might be asked about the criterion of adding objects to the pool. So in my opinion the best candidates would
be — Very hot data, large segments (not less than 1MB), collect these stats from sources like Oracle segment statistics, number of column scans, AWR reports etc. Analytical queries etc.

SQL> alter system set inmemory_size=250m scope=spfile;

System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1191182336 bytes
Fixed Size                  8620032 bytes
Variable Size             822085632 bytes
Database Buffers           83886080 bytes
Redo Buffers                8155136 bytes
In-Memory Area            268435456 bytes
Database mounted.
Database opened.

Now i will try to load some stuff in to the memory.

SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate 
FROM user_tables WHERE table_name='TEST1';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
TEST1                DISABLED

00:14:42 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 DONE                         >>>>>>> 1MB pool is used to store the column formatted data.
64KB POOL                     50331648          0 DONE                         >>>>>>> 64 KB pool is used to store the metadata about its residents.

Now i am moving the discussed table to in memory.

exec DBMS_INMEMORY.POPULATE(schema_name => dixit, table_name => 'TEST1');
or Using

00:15:01 SQL> alter table test1 inmemory;

Table altered.


00:17:59 SQL> SELECT table_name,inmemory,inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate
FROM user_tables WHERE table_name='TEST1';

TABLE_NAME           INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
TEST1                ENABLED  NONE     AUTO            FOR QUERY LOW     NO DUPLICATE




00:20:17 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 DONE
64KB POOL                     50331648          0 DONE


00:18:25 SQL> select owner, segment_name, populate_status from v$im_segments;

no rows selected

Above results shows that no segment is added to the pool. Now lets try to query the table and see the results again.

00:20:26 SQL> select count(*) from test1;

  COUNT(*)
----------
   1000000



00:20:48 SQL> select pool, alloc_bytes, used_bytes, populate_status from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016          0 POPULATING
64KB POOL                     50331648          0 POPULATING


Okay now it's doing something as status from DONE has changed to POLULATING. Lets repeat the same command again.

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL                     200278016   49283072 DONE
64KB POOL                     50331648     458752 DONE

Okay its there in the pool now! Lets check the in memory area, what we have inside it.

00:23:56 SQL> select owner, segment_name, populate_status from v$im_segments;

OWNER      SEGMENT_NAME         POPULATE_STAT
---------- -------------------- -------------
DIXIT      TEST1                COMPLETED

Lets conform if table is fully populated we have to look at v$im_segments_detail which compares the number of blocks in In-Memory and in the TEST1 table.

SELECT m.inst_id, 
       m.blocksinmem, 
       m.datablocks 
FROM   v$im_segments_detail m, 
       user_objects o 
WHERE  m.dataobj = o.object_id 
AND    o.object_name = 'TEST1';

   INST_ID BLOCKSINMEM DATABLOCKS
---------- ----------- ----------
         1        6922       6922

Lets see if there is any changes captured in Execution plan of the query.

SQL> explain plan for select count(*) from test1;

Explained.

SQL> @xplan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3896847026

-----------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    71   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE             |       |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| TEST1 |  1000K|    71   (2)| 00:00:01 |
-----------------------------------------------------------------------------

9 rows selected.

Now we have a new operation type added to the plan ‘TABLE ACCESS INMEMORY FULL’. It says that the object was accessed directly from the memory.

You can compare the response times with/without the In-Memory Store by simply disabling In-Memory functionality in your session:

ALTER SESSION SET inmemory_query = ENABLE;
ALTER SESSION SET inmemory_query = DISABLE;

You can anytime remove the objects from the pool. Other options to use with In memory.

SQL> ALTER TABLESPACE example INMEMORY;

Enabling the In-Memory attribute on the TEST1 table but excluding the “dest_id” column

SQL> ALTER TABLE TEST1 INMEMORY NO INMEMORY(dest_id);

Enabling the in memory option for table TEST1 and setting the priority to CRITICAL. With this option set on object (Other options are HIGH, MEDIUM, LOW), it will be populated immediately after the database is opened.

SQL> ALTER TABLE TEST1 INMEMORY PRIORITY CRITICAL;

There are few other options like Compression (Objects compressed during population) , Joins, Scans are also there and will be covered in a separate post or you can read the official documentation on in memory to understand them.

But i would like to discuss little about how the In-Memory option works in RAC environment.

In case of user querying the database in-memory in RAC, serial queries will only access a fraction of the data from its own node. IMCUs or IM Compression Units are not traveled using interconnect or using cache fusion. It fetches the remainder data from the disk itself.
Parallel execution helps as it starts multiple processes and ensures that at least one parallel server slave is allocated for each RAC instance. For that we have to set the parallel_degree_policy or Auto DOP to AUTO which makes the query coordinator ICMU aware and it (QC) automatically starts parallel server processes on correct server.

Now after all the discussion time to share my final words on the subject.
As we know that caching or pooling are the concepts there with Oracle RDBMS or with any of the databases from a very long time; then – why this in memory now ?
We already have few of the areas like KEEP/RECYCLE pools, Result cache etc. to keep cache data or results.
So my answer is that the Oracle In-Memory column store enables objects to load in memory in compressed columnar format which makes the scans to perform better that on-disk reads and leads to performance boosts. Use it when there is a SQL related performance issue and you’ve tried all tuning methods and each one of them are failed to fix the problem and yes If the In-Memory performance benefit can outperform the additional Oracle license costs.

Hope It Helps
Prashant Dixit

Posted in Advanced, Basics | Tagged: , | Leave a Comment »

MySQL – How to use LOAD DATA INFILE and INTO OUTFILE

Posted by FatDBA on December 20, 2017

Today i will discuss about the the useful but script/SQL based data export/import method in MySQL database that is – LOAD DATA INFILE and INTO OUTFILE.

Lets first create an export file/script for the table using SELECT … INTO OUTFILE, here you can specify the location of the export file.

mysql> select * from country into outfile 'countrycreate.sql';
Query OK, 109 rows affected (0.00 sec)

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql

As there is no table definition captured using SELECT INTO OUTFILE way, so you should always ensure that you have a copy of the table definition for restoration of the file.

bash-4.1$ mysqldump -u root -p --no-data dixit country > /var/lib/mysql/dixit/countryschemadef.sql
Enter password:

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql
-rw-r--r--. 1 mysql mysql 1.6K Dec 20 01:10 countryschemadef.sql

Lets see the contents of this newly created file.

bash-4.1$ more countryschemadef.sql
-- MySQL dump 10.13  Distrib 5.7.20, for Linux (x86_64)
--
-- Host: localhost    Database: dixit
-- ------------------------------------------------------
-- Server version       5.7.20

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
  `country_id` int(11) DEFAULT NULL,
  `country` text,
  `last_update` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-12-20  1:10:20

Lets create the new user and load the table data to it.


bash-4.1$ mysqladmin -u root -p create dixit2
Enter password:


bash-4.1$ mysql -u root -p dixit2  load data infile '/var/lib/mysql/dixit/countrycreate.sql' into table country;
Query OK, 109 rows affected (0.01 sec)
Records: 109  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql>
mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

All set!

Hope It Helps!
Prashant Dixit

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

MySQL ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’ – Version 5.7

Posted by FatDBA on November 27, 2017

mysql> update mysql.user set Password = PASSWORD(‘mysql’) where user =’root’;
ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’

WHY ??????
This was working all good in other instances of MySQL where i had earlier versions installed, why not this one – Puzzled, Perplexed!
Let me check version information of this instance.

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.20 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.20 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

Well, starting from MySQL version 5.7 the PASSWORD column from mysql.user table has been removed and now replaced with ‘authentication_string’.
So the all new syntax for this password reset would be like this …

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> update user set authentication_string=password(‘mysql’) where user=’root’;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 1


Hope That Helps
Prashant Dixit

Posted in Basics | Tagged: | 2 Comments »

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: | 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: | 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 »

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 »

Part 1: ASM Installation on 11gR2 (VMWare)

Posted by FatDBA on January 10, 2016

Hello Everyone,
Today i would like to start series/chapters describing Oracle Automatic Storage Management (Oracle ASM) concepts and provides an overview of Oracle ASM features. Followed posts will covers subjects like Installation, Configuration, Administration/Management, Monitoring. Troubleshooting and Optimization etc.

In this maiden post (Part 1) i would like to discuss and elaborate about ASM installation and related areas.

Prerequisites:
Considering that you already have the OS ready with all packages per-installed before we begin our ASM installation on the top. I will start with right from the scratch.

Step 1:
Preparing Disks or Partitions which will be used while creating the ASM diskgroups.
I’ve created 3 Persistent Disks each of 4GB in size from the VM Disk (I will perform all steps in VM environment).

This is how the VM Setting will look like once you are done with the Disk creation.
*Forgot about the Fifth Hard Disk of 10GB for now. Will explain the usage later on the series.

1

Once you have the disks created, Next you’ll have to format the newly created disks to make them usable: Using fdisk command. Command displays the status of available newly created partitions/disks as:
/dev/sdb, /dev/sdc, /dev/sdd – Each of 4GB (4294 MBs) in size.

[root@localhost ~]# fdisk -l

Disk /dev/sda: 91.3 GB, 91268055040 bytes, 178257920 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000aab6c

Device Boot Start End Blocks Id System
/dev/sda1 * 2048 1026047 512000 83 Linux
/dev/sda2 1026048 178257919 88615936 8e Linux LVM

Disk /dev/sdb: 4294 MB, 4294967296 bytes, 8388608 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Click Here to Read More!!

Posted in Advanced, Basics | Tagged: | Leave a Comment »

 
%d bloggers like this: