install sqldeveloper on ubuntu 14.04 or linux mint 17

https://community.oracle.com/docs/DOC-888316

apt-get install alien
Download sqldeveloper rpm



Install the sqldeveloper rpm file using alien

# sudo alien -ic sqldeveloper..rpm

Download  jdk-8u121-linux-x64.tar.gz   from Here

extract it to /opt/java

vim /usr/local/bin/sqldeveloper


    unset GNOME_DESKTOP_SESSION_ID
    export JAVA_HOME=/opt/java
    /opt/sqldeveloper/sqldeveloper.sh

go to system programs and find sqldeveloper to start

End

1-oracle architecture - control files

Control Files
its disk file, binary file
if you loose control files database is lost , its best to keep multiple copies of control files(multiplexing)
stores meta data eg. database creation date,
tracks physical components of db, gives answer - thats where my database is
name and location of datafiles ,online redo, time db is created, checkpoints etc.
imp: Backup your control files

Datafiles
physica os/binary file that stores actual data, 1 + more DataFile = table space


Tablespace is logical container for segments, and physically composed of multiple datafiles

Segment is made up of multiple extents ,objects(table, indexes),--extents are grouped together to form segment

Extents is made up of blocks

OnlineRedoLog - recovery method, it is physical file that stores changes made to db, readback the db changes and recover the db changes in the event of crash. archiving redo means copying the redo log to remote machine before its overwritten

parameter file - stores initialization parameters, server info -- num of process, how many process, memory,open cursors, db name, dump location ,user and system dumps etc. some can be changed online and some require restart

password file - stores SYSDBA user info , any user with sysdba grants have entry in this file

Fast Recovery Area - or flash recovery area,  is disk location that does management of backup related files
it basically simplifies db administration, speeds up rman backup ,holds backups, archived redo

AlertLog - stores error and exception messages, this is the first place to look when errors occur

Trace Files- diagonistic files. tool for query tuning, find what is running ,trace session
server and user process write to this file

ASM -automatic storage management
- LVM for oracle
- does striping and raid 10

Grid - G in 11g and 10g
-pools multiple resources and servers into on demand resource
- RAC enable multiple db instances to share a single database, so multiple servers accessing same data files
- Clusterware enable fault tolerance ,performance make multiple server act as single

Cloud backups - oracle secure backup cloud module
allow backups to S3

Oracle grid installation part 1

1) install necessary rpms for asm support , download the rpm here ASM libs or  google "oracle asm rpm"
2) install the rpm

grep MemTotal /proc/meminfo
grep SwapTotal /proc/meminfo

 /etc/init.d/oracleasm configure
/etc/init.d/oracleasm createdisk ASMDISK01 /dev/sda2
/etc/init.d/oracleasm createdisk ASMDISK02 /dev/sdb1
/etc/init.d/oracleasm createdisk ASMDISK03 /dev/sdc1
/etc/init.d/oracleasm createdisk ASMDISK04 /dev/sdd1
/etc/init.d/oracleasm createdisk ASMDISK05 /dev/sde1
/etc/init.d/oracleasm createdisk ASMDISK06 /dev/sdf1
/etc/init.d/oracleasm createdisk ASMDISK07 /dev/sdg1
/etc/init.d/oracleasm createdisk ASMDISK08 /dev/sdh1
/etc/init.d/oracleasm createdisk ASMDISK09 /dev/sdi1
/etc/init.d/oracleasm createdisk ASMDISK10 /dev/sdj1
/etc/init.d/oracleasm createdisk ASMDISK11 /dev/sdk1
/etc/init.d/oracleasm createdisk ASMDISK12 /dev/sdl1
/etc/init.d/oracleasm createdisk ASMDISK13 /dev/sdm1
/etc/init.d/oracleasm createdisk ASMDISK14 /dev/sdn1

chmod 777 grid/install/.oui
chmod 777 grid/install/unzip

groupadd --gid 54321 oinstall
groupadd --gid 54322 dba
groupadd --gid 54323 asmdba
groupadd --gid 54324 asmoper
groupadd --gid 54325 asmadmin
groupadd --gid 54326 oper

useradd --uid 54321 --gid oinstall --groups dba,oper,asmdba,asmoper oracle
passwd oracle

useradd --uid 54322 --gid oinstall --groups dba,asmadmin,asmdba,asmoper grid
passwd grid


mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01


yum install oracle-rdbms-server-11gR2-preinstall libaio glibc compat-libstdc libgcc libstdc++ unixODBC unixODBC-devel  elfutils-libelf-devel compat-libstdc++-33

http://myorastuff.blogspot.com/2015/10/crs-4124-oracle-high-availability.html


and
vim /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl
bash /u01/app/oraInventory/orainstRoot.sh
bash /u01/app/oracle/product/11.2.0/grid/root.sh
https://oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-6#Installation

mysql terminologies

data dictionary

Metadata that keeps track of InnoDB-related objects such as tables, indexes, and table columns. This metadata is physically located in the InnoDB system tablespace. For historical reasons, it overlaps to some degree with information stored in the .frm files

data files

The files that physically contain table and index data. The InnoDB system tablespace, which holds the InnoDB data dictionary and is capable of holding data for multiple InnoDB tables, is represented by one or more .ibdata data files. File-per-table tablespaces, which hold data for a single InnoDB table, are represented by a .ibd data file. General tablespaces (introduced in MySQL 5.7.6), which can hold data for multiple InnoDB tables, are also represented by a .ibd data file.

file-per-table

A general name for the setting controlled by the innodb_file_per_table option, which is an important configuration option that affects aspects of InnoDB file storage, availability of features, and I/O characteristics. As of MySQL 5.6.7, innodb_file_per_table is enabled by default.

With the innodb_file_per_table option enabled, you can create a table in its own .ibd file rather than in the shared ibdata files of the system tablespace. When table data is stored in an individual .ibd file, you have more flexibility to choose row formats required for features such as data compression. The TRUNCATE TABLE operation is also faster, and reclaimed space can be used by the operating system rather than remaining reserved for InnoDB.

dirty page

A page in the InnoDB buffer pool that has been updated in memory, where the changes are not yet written (flushed) to the data files. The opposite of a clean page.

flush

To write changes to the database files, that had been buffered in a memory area or a temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.

    Flushing can happen because a memory area becomes full and the system needs to free some space, because a commit operation means the changes from a transaction can be finalized, or because a slow shutdown operation means that all outstanding work should be finalized. When it is not critical to flush all the buffered data at once, InnoDB can use a technique called fuzzy checkpointing to flush small batches of pages to spread out the I/O overhead.

redo log

A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.

The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.3.

The disk layout of the redo log is influenced by the configuration options innodb_log_file_size, innodb_log_group_home_dir, and (rarely) innodb_log_files_in_group. The performance of redo log operations is also affected by the log buffer, which is controlled by the innodb_log_buffer_size configuration option.

undo

Data that is maintained throughout the life of a transaction, recording all changes so that they can be undone in case of a rollback operation. It is stored in the undo log either within the system tablespace or in separate undo tablespaces.

undo log

A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area.

    By default, this area is physically part of the system tablespace. In MySQL 5.6 and higher, you can use the innodb_undo_tablespaces and innodb_undo_directory configuration options to split it into one or more separate tablespace files, the undo tablespaces, optionally stored on another storage device such as an SSD.

The undo log is split into separate portions, the insert undo buffer and the update undo buffer.

innodb_undo_logs = Defines the number of rollback segments used by InnoDB for data-modifying transactions that generate undo records. Each rollback segment can support a maximum of 1024 data-modifying transactions.
This setting is appropriate for tuning performance if you observe mutex contention related to the undo logs. The innodb_undo_logs option replaces innodb_rollback_segments. For the total number of available rollback segments, rather than the number of active ones, see the Innodb_available_undo_logs status variable.

buffer

    A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the change buffer.

buffer pool

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

    Several InnoDB status variables, INFORMATION_SCHEMA tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can avoid a lengthy warmup period after restarting the server, particularly for instances with large buffer pools, by saving the buffer pool state at server shutdown and restoring the buffer pool to the same state at server startup

cardinality

The number of different values in a table column. When queries refer to columns that have an associated index, the cardinality of each column influences which access method is most efficient. For example, for a column with a unique constraint, the number of different values is equal to the number of rows in the table. If a table has a million rows but only 10 different values for a particular column, each value occurs (on average) 100,000 times. A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of rows, and the database server might process the query differently depending on the cardinality of c1.

If the values in a column have a very uneven distribution, the cardinality might not be a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x; might return 1 row when x=50 and a million rows when x=30. In such a case, you might need to use index hints to pass along advice about which lookup method is more efficient for a particular query.

Cardinality can also apply to the number of distinct values present in multiple columns, as in a composite index.

Isolation levels in mysql

PHANTOM reads
A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

This occurrence is known as a phantom read. It is harder to guard against than a non-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.


Among different isolation levels, phantom reads are prevented by the serializable read level, and allowed by the repeatable read, consistent read, and read uncommitted levels.

isolation level
One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.

From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: SERIALIZABLE, REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

With InnoDB tables, many users can keep the default isolation level (REPEATABLE READ) for all operations. Expert users might choose the READ COMMITTED level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (SERIALIZABLE and READ UNCOMMITTED) change the processing behavior to such an extent that they are rarely used.

READ COMMITTED
An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.

When a transaction with this isolation level performs UPDATE ... WHERE or DELETE ... WHERE operations, other transactions might have to wait. The transaction can perform SELECT ... FOR UPDATE, and LOCK IN SHARE MODE operations without making other transactions wait.

REPEATABLE READ
The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

When a transaction with this isolation level performs UPDATE ... WHERE, DELETE ... WHERE, SELECT ... FOR UPDATE, and LOCK IN SHARE MODE operations, other transactions might have to wait.

https://blog.jcole.us/2014/04/16/the-basics-of-the-innodb-undo-logging-and-history-system/

Calculate size of mysql databases or tables

Gives size of all Databases

SELECT table_schema AS "DB_NAME",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

Table Size

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "DB_NAME_007"
ORDER BY (data_length + index_length) DESC;

mysql buffer pool

http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_buffer_pool

http://dev.mysql.com/doc/refman/5.7/en/innodb-performance-read_ahead.html

buffer pool

The memory area that holds cached InnoDB data for both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances.

Several InnoDB status variables, INFORMATION_SCHEMA tables, and performance_schema tables help to monitor the internal workings of the buffer pool. Starting in MySQL 5.6, you can avoid a lengthy warmup period after restarting the server, particularly for instances with large buffer pools, by saving the buffer pool state at server shutdown and restoring the buffer pool to the same state at server startup. See Section 15.6.3.8, “Saving and Restoring the Buffer Pool State”.

See Also buffer pool instance, LRU, page, warm up.

buffer pool instance

Any of the multiple regions into which the buffer pool can be divided, controlled by the innodb_buffer_pool_instances configuration option. The total memory size specified by innodb_buffer_pool_size is divided among all buffer pool instances. Typically, having multiple buffer pool instances is appropriate for systems that allocate multiple gigabytes to the InnoDB buffer pool, with each instance being one gigabyte or larger. On systems loading or looking up large amounts of data in the buffer pool from many concurrent sessions, having multiple buffer pool instances reduces contention for exclusive access to data structures that manage the buffer pool.