Oracle 12c - In Database Archiving

In-Database Archiving enables us to archive rows within a table/database by marking them as inactive/disable (mark for delete).

By enabling the database to distinguish from active data and ‘older’ in-active data while at the same time storing everything in the same database.

These inactive/disabled rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

Below example of using In-Database Archiving

SQL> select count(*) from Audit;

  COUNT(*)
----------
    80001

SQL> alter table Audit row archival;

Table altered.

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
0

Please Note – New column ORA_ARCHIVE_STATE is now added to the table Audit and is a hidden column.

We now want to designate all rows in the Audit table which belong to the years 2001 and 2002 as old and historical data.

All data after 01-JAN-2003 should be treated as current and active data.

SQL> update Audit
  2  set ORA_ARCHIVE_STATE=DBMS_ILM.ARCHIVESTATENAME(1)
  3  where time_id < '01-JAN-03';

40000 rows updated.

SQL> Commit;

If we now issue a select * from Audit command, we see that only about half the actual number of rows are being returned by the query as Oracle is not returning the rows where the value is 1 for the column ORA_ARCHIVE_STATE

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------
0

SQL> select count(*) from Audit;

  COUNT(*)
----------
    40001

Now let as assume there is a requirement to view the historical and inactive data as well.
At the session level we can set the value for the parameter ROW ARCHIVAL VISIBILITY to ALL

SQL> alter session set row archival visibility=ALL;

Session altered.

SQL> select count(*) from Audit;

  COUNT(*)
----------
    80001

SQL> select distinct ora_archive_state from Audit;

ORA_ARCHIVE_STATE
--------------------------------------------------------------------------------
1
0

Partition the Audit table on the ORA_ARCHIVE_STATE column is a good option, so that we can then compress the partitions containing the archive(old) data. 
The current/Active data will be left in an uncompressed state as it is frequently accessed by application and we do not want to impact performance.


We can also make those partitions containing the older data read only and exclude them from our regular daily database backups.

'buffer busy waits' Wait Event explained

'log file sync' Wait Event Explained

'log file sync' Wait Event Explained

Diagnosing RAC Instance eviction issue

An instance eviction occurs when a member was evicted from the group by another member of the cluster database for one of several reasons, which may include a communications error in the cluster, failure to issue a heartbeat to the control file, and other reasons.  This mechanism is in place to prevent problems from occuring that would affect the entire database.
 For example,instead of allowing a cluster-wide hang to occur, Oracle will evict the problematic instance(s) from the cluster.  When an ORA-29740 error occurs, a surviving instance will remove the problem instance(s) from the cluster.  When the problem is detected the instances 'race' to get a lock on the control file (Results Record lock) for updating.  The instance that obtains the lock tallies the votes of the instances to decide membership.  A member is evicted if:

a) A communications link is down
b) There is a split-brain (more than 1 subgroup) and the member is not in the largest subgroup
c) The member is perceived to be inactive


The various reasons for instance eviction :-

Reason 0 = No reconfiguration  
Reason 1 = The Node Monitor generated the reconfiguration.
Reason 2 = An instance death was detected.
Reason 3 = Communications Failure
Reason 4 = Reconfiguration after suspend


To determine the reason behind your instance eviction-

a) Review each instance's alert log
b) Review instance's LMON trace file
c) Review OS Watcher logs if used. If OSWATCHER is not installed in all the nodes of your RAC clusterware, request you to please install it . Refer Note 301137.1 for  procedure of installing and how to use OS watcher
d) Review the CKPT process trace file of the evicted instance
e) Other bdump or udump files generated at the exact time of the instance eviction
f) Review each node's syslog or messages file

Configure ASM mirroring for OCR and Voting Disk

1.Create another disk group OCR_VOTE_NORM with three asm disks with normal redundancy.

2.Replaced the current OCR_VOTE disk group (external redundancy) with OCR_VOTE_NORM
./crsctl replace votedisk +OCR_VOTE_NORM

3. Added OCR_VOTE_NORM disk group to OCR
./ocrconfig -add +OCR_VOTE_NORM

4. There are two copies of OCR files and three copies of Voting disks. 

Follow Oracle Note 428681.1 -OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) to move OCR file from normal redundancy DG to external redundancy DG.

gc [current/cr] [2/3]-way Wait Event

A current or cr block was requested and received after 2 or 3 network hops. 
The request was processed immediately.

 If these events consume lot of time then check..

 - Network bandwidth saturation
 - Private Interconnect configuration
 - Socket send and receive buffer
 - Run queue length and system CPU usage

 Tuning
- System and load tuning to reduce latencies
- SQL and schema tuning to minimize IO and remote cache references 

Migrating Oracle Cluster Registry(OCR) location

Complete the following procedure:

1.Use the OCRCHECK utility to verify that a copy of OCR other than the one you are going to replace is online, using the following command:

$ ocrcheck

OCRCHECK displays all OCR locations that are registered and whether they are available (online). If an OCR location suddenly becomes unavailable, then it might take a short period for Oracle Clusterware to show the change in status.

Note:   The OCR location that you are replacing can be either online or offline.

2. Use the following command to verify that Oracle Clusterware is running on the node on which the you are going to perform the replace operation:

$ crsctl check crs

3. Run the following command as root to replace the current OCR location using either destination_file or +ASM_disk_group to indicate the current and target OCR locations:

# ocrconfig -replace <current_OCR_location> -replacement <new_OCR_location>

If you have only one OCR location, then use the following commands:

# ocrconfig -add +new_storage_disk_group
# ocrconfig -delete +current(old)_disk_group

Note:
If your cluster configuration changes while the node on which OCR resides is stopped, and the Oracle Clusterware stack is running on the other nodes, then OCR detects configuration changes and self-corrects the configuration by changing the contents of the ocr.loc file.

Migrating voting disks from one disk group to other

To migrate voting disks to other Oracle ASM diskgroup , specify the new Oracle ASM disk group name in the following command:

$ crsctl replace votedisk +asm_disk_group

You can run this command on any node in the cluster.

Verifying the voting disk location :-

After moving the voting disk, verify the voting disk location, as follows:

$ crsctl query css votedisk

"GC BUFFER BUSY" wait event explained

When a session wants to modify a buffer, which is cached in the remote instances, it waits on the buffer busy global cache event. This is similar to buffer busy waits in single instance architecture. This wait happens at cache layer. In other words, the session is waiting for a buffer in local cache, which is busy because it is waiting for a global cache request to complete. The waits at the RAC layer are accounted in the global cache busy wait event.

The interconnect statistics look good so this looks like a hot block issue.

You may want to investigate a hot block issue.

To resolve contention for this latch, you must first identify the segments that the blocks are associated with by doing the following 

Find the "hottest" child latches (usually top 5): 
SELECT addr, latch#, sleeps 
FROM v$latch_children 
WHERE name = 'cache buffers chains' 
AND sleeps > 0 
ORDER BY sleeps DESC 

Find the buffers associated with the hot latches: 
SELECT hladdr, dbarfil, dbablk, tch FROM x$bh 
WHERE hladdr = '&child_latch_addr' -- <== run this for each of the hot latches 
ORDER BY tch DESC 

Find the segments associated with the hot buffers: 
SELECT owner, segment_name, partition_name, segment_type, tablespace_name, 
relative_fno, block_id, blocks 
FROM dba_extents e 
WHERE &Block_No BETWEEN e.block_id AND (e.block_id + blocks) 
AND e.relative_fno = &File_no 

To relieve contention on the segments listed above: 

1) On rare occasions, a larger buffer cache may help relieve contention; if there is sufficient memory available, increase the buffer cache by 20% and check if the contention on this latch is reduced. (This almost never helps, but its simple to try). 

2) Examine SQL statements with a high number of buffer gets and tune them to reduce the number of buffer gets per execution or change the application to execute the statements less frequently. 

3) Alter (or even rebuild) tables listed above to use a higher PCTFREE setting. This will reduce the number of rows per block and hopefully, spread out contention for the blocks (at the expense of wasting space). 

4) If the hot blocks are index leaf blocks, there may be contention due to key values that are increasing steadly (using a sequence). Look at using reverse key indexes (if range scans aren't commonly used against the segment). Or, consider changing the application to use natural keys, partitioning or otherwise reduce the dependence on the sequence-generated keys.

Apache Ambari

What is Apache Ambari ?

Apache Ambari is a web-based tool for provisioning, managing, and monitoring Apache Hadoop clusters. Ambari provides a dashboard for viewing cluster health such as heatmaps and ability to view MapReduce, Pig and Hive applications visually along with features to diagnose their performance characteristics in a user-friendly manner. It has a very simple and interactive UI to install various tools and perform various management, configuring and monitoring tasks. 
Apache Ambari, as part of the Hortonworks Data Platform, allows enterprises to plan, install and securely configure Hadoop making it easier to provide ongoing cluster maintenance and management, no matter the size of the cluster.

Ambari's Extensibility Features 

- Ambari Stacks
- Ambari Blueprints
- Ambari Views 

What Ambari Does?

Ambari makes Hadoop management simpler by providing a consistent, secure platform for operational control. Ambari provides an intuitive Web UI as well as a robust REST API, which is particularly useful for automating cluster operations. With Ambari, Hadoop operators get the following core benefits:

• Simplified Installation, Configuration and Management. Easily and efficiently create, manage and monitor clusters at scale. Takes the guesswork out of configuration with Smart Configs and Cluster Recommendations.  Enables repeatable, automated cluster creation with Ambari Blueprints.
• Centralized Security Setup. Reduce the complexity to administer and configure cluster security across the entire platform. Helps automate the setup and configuration of advanced cluster security capabilities such as Kerberos and Apache Ranger.
• Full Visibility into Cluster Health. Ensure your cluster is healthy and available with a holistic approach to monitoring. Configures predefined alerts — based on operational best practices — for cluster monitoring. Captures and visualizes critical operational metrics — using Grafana — for analysis and troubleshooting. Integrated with Hortonworks SmartSense for proactive issue prevention and resolution.
• Highly Extensible and Customizable. Fit Hadoop seamlessly into your enterprise environment. Highly extensible with Ambari Stacks for bringing custom services under management, and with Ambari Views for customizing the Ambari Web UI.

Ambari have two components

1. Ambari server – This is the master process which communicates with Ambari agents installed on each node participating in the cluster. This has postgres database instance which is used to maintain all cluster related metadata.
2. Ambari Agent – These are acting agents for Ambari on each node. Each agent periodically sends his own health status along with different metrics, installed services status and many more things. According master decides on next action and conveys back to the agent to act.

Ambari System Architecture

Ambari Server Architecture

Oracle Local Registry (OLR) - New Component of Oracle Clusterware 11g Release 2

In Oracle Clusterware 11g Release2 an additional component related to the OCR called the Oracle Local Registry (OLR) is installed on each node in the cluster. The OLR is a local registry for node specific resources. THe OLR is not shared by other nodes in the cluster. It is installed and configured when Oracle clusterware is installed.

 

Purpose of OLR

It is the very first file that is accessed by OHASD service to startup 11gR2 clusterware when OCR is stored on ASM. OCR should be accessible to find out the resources which need to be started on a node. If OCR is on ASM, it can’t be read until ASM (which itself is a resource for the node and this information is stored in OCR) is up. To resolve this problem, information about the resources which need to be started on a node is stored in an operating system  file which is called Oracle Local Registry or OLR. Since OLR is a file an operating system file, it can be accessed by various processes on the node for read/write irrespective of the status of the clusterware (up/down). Hence, when  a node joins the cluster,  OLR on that node is read, various resources ,including ASM  are started on the node  . Once ASM is up , OCR is accessible and is used henceforth to manage all the clusterware resources. If OLR is missing or corrupted, clusterware can’t be started on that node!

 

Where is OLR located?

The OLR file is located in the grid_home/cdata/<hostname>.olr 

The location of OLR is stored in /etc/oracle/olr.loc.and used by OHASD .

 

What does OLR contain?

The OLR stores data about

• ORA_CRS_HOME
• localhost version
• active version
• GPnP details
• OCR latest backup time and location
• information about OCR daily, weekly backup location
•  node name etc.

This information stored in the OLR is needed by OHASD to start or join a cluster.

A quick  peek at the backup of the olr shows the resources that are being maintained.

[root@server01 ~]# ocrconfig -local -manualbackup

Server01     2016/12/12 11:12:27     /u01/app/11.2.0/grid/cdata/host01/backup_20161212_110527.olr

[root@Server01~]#strings /u01/app/11.2.0/grid/cdata/host01/backup_20161212_110527.olr |grep -v type |grep ora!

ora!drivers!acfs

ora!crsd

ora!asm

ora!evmd

ora!ctssd

ora!cssd

ora!cssdmonitor

ora!diskmon

ora!gpnpd

ora!gipcd

ora!mdnsd

 

OLR administration

You can view the status of the OLR file on each node by using the ocrcheck command with the –local parameter as seen here:

#ocrcheck -local

 

ocrdump can be used to  dump the contents of the OLR to tthe text terminal:

#ocrdump -local -stdout

 

You can use the ocrconfig command to export and import the OLR as seen in these examples:

#ocrconfig -local -export <export file name >

#ocrconfig -local -import <file name>

 

And you can repair the OLR file should it become corrupted with the ocrconfig command as seen in this example:

#ocrconfig -local -repair olr <file name>

 

The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.

To manually back up OLR:

# ocrconfig –local –manualbackup

To view the contents of the OLR backup file:

#ocrdump -local -backupfile olr_backup_file_name

To change the OLR backup location

#ocrconfig -local -backuploc new_olr_backup_path

To restore OLR: 

# crsctl stop crs

# ocrconfig -local -restore file_name 

# ocrcheck -local 

# crsctl start crs 

$ cluvfy comp olr

NoSQL (Not Only SQL)

NoSQL database, also called Not Only SQL, is an approach to data management and database design that's useful for very large sets of distributed data.  

NoSQL, which encompasses a wide range of technologies and architectures, seeks to solve the scalability and big data performance issues that relational databases weren’t designed to address. NoSQL is especially useful when an enterprise needs to access and analyze massive amounts of unstructured data or data that's stored remotely on multiple virtual servers in the cloud. .  

Contrary to misconceptions caused by its name, NoSQL does not prohibit structured query language (SQL). While it's true that some NoSQL systems are entirely non-relational, others simply avoid selected relational functionality such as fixed table schemas and join operations. For example, instead of using tables, a NoSQL database might organize data into objects,key or tuples.

Arguably, the most popular NoSQL database is Apache Cassandra. Cassandra, which was once Facebook’s proprietary database, was released as open source in 2008. Other NoSQL implementations include SimpleDB, Google BigTable, Apache Hadoop, MapReduce, MemcacheDB, and Voldemort. Companies that use NoSQL include NetFlix, LinkedIn and Twitter.

NoSQL is often mentioned in conjunction with other big data tools such as massive parallel processing, columnar-based databases and Database-as-a-Service (DaaS).

Relational databases built around the SQL programming language have long been the top -- and, in many cases, only -- choice of database technologies for organizations. Now, with the emergence of various NoSQL software platforms, IT managers and business executives involved in technology decisions have more options on database deployments. NoSQL databases support dynamic schema design, offering the potential for increased flexibility, scalability and customization compared to relational software. That makes them a good fit for Web applications, content management systems and other uses involving large amounts of non-uniform data requiring frequent updates and varying field formats. In particular, NoSQL technologies are designed with "big data" needs in mind.

But for prospective users, the array of NoSQL database choices may seem confusing or even overwhelming. NoSQL databases are grouped into four primary product categories with different architectural characteristics: document databases, graph databases, key-value databases and wide column stores. Many NoSQL platforms are also tailored for specific purposes, and they may or may not work well with SQL technologies, which could be a necessity in some organizations. In addition, most NoSQL systems aren't suitable replacements for relational databases in transaction processing applications,because they lack full ACID compliance for guaranteeing transnational integrity and data consistency.

As a result, IT and business professionals making database buying decisions must carefully evaluate whether the available NoSQL options fit their business needs. In this guide, you can learn more about what NoSQL software can do and how it differs from relational databases. Trend stories and user case studies document how NoSQL databases can be used to support big data, cloud computing and business analytics applications. And experienced users from companies that have already deployed NoSQL tools offer advice on how to make the technology selection and implementation process smoother.

Oracle 12c - How to plugin a non-CDB database to a Container Database (CDB) ?

The Container database cdb12c originally has one pluggable database testpdb (as well as the seed PDB). We will see how we can plugin the 12C non-cdb database testdb12c into the container database cdb12c.

Note that to plugin a non-CDB database into a CDB, the non-CDB database needs to be of version 12c as well. So existing 11g databases will need to be upgraded to 12c before they can be part of a 12c CDB.

Steps involved.

The first thing we need to do is to connect to the non-CDB, and run the DBMS_PDB.DESCRIBE procedure to construct an XML file that contains the metadata which describes the non-CDB.

We need to run this procedure while the database is in a read only mode.

[oracle@server- testdb12c]$ sqlplus sys as sysdba

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  801701888 bytes

Fixed Size                  2293496 bytes

Variable Size             314573064 bytes

Database Buffers          478150656 bytes

Redo Buffers                6684672 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

SQL> BEGIN

DBMS_PDB.DESCRIBE(

pdb_descr_file => '/home/oracle/testdb12c.xml');

END;

/

PL/SQL procedure successfully completed.

Now shutdown and open the non-CDB in read write node. We need to then check if the non-CDB is now ready for conversion into a Pluggable Database (PDD) which is going to be contained in the Container Database.

Connect to the CDB and run the DBMS_PDB.CHECK_PLUG_COMPATIBILITY package where we provide the location of the XML metadata file which was generated in the first step we executed.

  SQL> SET SERVEROUTPUT ON

  DECLARE

  compatible CONSTANT VARCHAR2(3) :=

  CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(

  pdb_descr_file => '/home/oracle/testdb12c.xml',

  pdb_name       => 'testdb12c')

  WHEN TRUE THEN 'YES'

  ELSE 'NO'

  END;

  BEGIN

  DBMS_OUTPUT.PUT_LINE(compatible);

  END;

  /

NO

PL/SQL procedure successfully completed.

Next we shutdown the non-CDB and plug it in to the CDB by converting it to a PLUGGABLE DATABASE.

SQL> CREATE PLUGGABLE DATABASE testdb12c using '/home/oracle/testdb12c.xml'

nocopy

tempfile reuse;

Pluggable database created.

The XML file accurately describes the current locations of the files of the PDB.  Since we are using the existing 12c non-CDB datafiles to create the CDB pluggable database , we will use the NOCOPY option.

If we want, we can leave the existing 12c non-cdb database and clone that to create a pluggable database

We can see that the testdb12c database is now a PDB and is in a mounted state.

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

    CON_ID NAME                           OPEN_MODE

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

         2 PDB$SEED                       READ ONLY

         3 testpdb                              READ WRITE

         4 testdb12c                          MOUNTED

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS

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

testpdb               571254526          1 NORMAL

PDB$SEED         4061785545       1 NORMAL

testdb12c            1141508778        1 NEW

SQL> ALTER SESSION SET CONTAINER =testdb12c;

Session altered.

SQL> show con_name

CON_NAME

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

testdb12c

Now run the ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql script. 

This script must be run before the PDB can be opened for the first time.

After the script has been run open the PDB testdb12c in READ WRITE mode.

Now check the status.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL>  show con_name

CON_NAME

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

CDB$ROOT

SQL> select  PDB_NAME, DBID , CON_ID, STATUS  from CDB_PDBS;

PDB_NAME                   DBID     CON_ID STATUS

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

testpdb               571254526          1 NORMAL

PDB$SEED       4061785545         1 NORMAL

testdb12c            1141508778        1 NORMAL

3 rows selected.

Connect to the newly plugged-in PDB testdb12c

[oracle@server- testdb12c]$ sqlplus sh/sh@localhost:1525/testdb12c

SQL> select count(*) from sales;

COUNT(*)

———-

918843