Series : MongoDB for Oracle DBA pt 1: Introduction

If you have worked in any sort of Enterprise big or small, most likely you have worked on a Relational Database Management Systems (RDBMS). They have been around for so long that we have taken the relational databases for granted. Relational in RDBMS stands for the relation which exists in a group of objects contained in a abstraction called Schema. A Schema contains tables of Rows and Columns. This has been the defacto way of storing data for the last 40 years since RDBMS have been around. The big vendors in this market today are Oracle, Microsoft, IBM, Teradata, SAP etc.

But with the advent of the Internet, Social media and Web 2.0 companies like Google, Facebook, Twitter, Linkedin etc a new sort of challenge for data arised. Data was no longer a neat structure of rows and columns. The data was now unstructured and being generated in volumes unsuitable to be handled by the old workhorses of traditional enterprise technology. To address these new challenges a new breed of databases had to be designed.

NOSQL databases, deriving their name from their USP that normal SQL queries don’t work or aren’t required anymore. These new databases were designed to address the challenges of the new age world.  The SQL query engine was replaced by data retrieval and inserting API’s. Big Data, real-time capability and ability to scale-out to tens of thousands of servers  across data centers sitting in different continents made it attractive for the HyperScale web companies.

One of the leading companies driving this NOSQL revolution has been a database called MongoDB. MongoDB lacks any of the traditional ACID compliance capabilities of an RDBMS, but it makes up for those in terms of massive scale-out capabilities and sharding of data across thousands of nodes. Though MongoDB is not the only NOSQL database in the market; there are many like Cassandra, CouchDB, Redis etc but it is certainly the leading in the white Noise of  NOSQL databases. It has even launched an IPO recently .

MongoDB is a Key-Value Store unlike a traditional DB storage engine, it is document oriented and stores the data in the form of JSON documents. Tables are called Collections, Rows are called Documents, Columns are called Fields and Primary Key is the unique ID assigned to each Document (Rows).  It is a Schemaless database which gives developers a lot of freedom with how they design and evolve their data models.

Example of a Key Value pair Document

Below is Example from MongoDB’s website

Capture

The Fields are name,age,status,groups and each has an associated value. Also a unique value (key) is assigned to each document. Look at the below example :

{
_id” : ObjectId(“59edd3bfa95893611d8ba674“),
“address” : {
“building” : “2780”,
“coord” : [
-73.98241999999999,
40.579505
],
“street” : “Stillwell Avenue”,
“zipcode” : “11224”
},
“borough” : “Brooklyn”,
“cuisine” : “American”,
“grades” : [
{
“date” : ISODate(“2014-06-10T00:00:00Z”),
“grade” : “A”,
“score” : 5
},
{
“date” : ISODate(“2013-06-05T00:00:00Z”),
“grade” : “A”,
“score” : 7
},
{
“date” : ISODate(“2012-04-13T00:00:00Z”),
“grade” : “A”,
“score” : 12
},
{
“date” : ISODate(“2011-10-12T00:00:00Z”),
“grade” : “A”,
“score” : 12
}
],
“name” : “Riviera Caterer”,
“restaurant_id” : “40356018”
}

If you see the first line it has a field called _id  line , it is a unique hexadecimal value assigned to the document. It globally identifies the document in the entire collection of data. It is similar to a Primary Key in a Relational database.

Other features of Mongo DB are

  1. Indexing – It supports a  wide array of indexing viz. Single, Compound, Geo-Spatial, Text and Hashed indexes
  2. MapReduce-  Supports MapReduce algorithms for Data aggregation purpose
  3. Stored JavaScript –  Instead of PL/SQL procedures or Stored Procedures you have Javascript fucntions and their values on the server side.
  4. High Availability and Sharding of Data across cluster of computers

 

In the Second Part of this Series we will Install MongoDB on a CentOS 7 Server, create a Database and import a Collection and do some basic data retrieval.

So keep watching this space for more….

 

Advertisements

ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned a 12c Database which was on a lower home version to a 12c home on a higher version. It requires data patch to be run and rollback the old patch and apply the new JVM patch.

 

If it is a RAC database, put it in single instance mode by disabling cluster_database parameter and mount the database in upgrade exclusive mode.

 

SQL> show parameter cluster

 

NAME     TYPE VALUE
———————————— ———– ——————————
cluster_database     boolean TRUE
cluster_database_instances     integer 2
cluster_interconnects     string
SQL>

SQL>

SQL> alter system set cluster_database=false scope=spfile sid=’*’;

 

 

System altered.

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup upgrade exclusive;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
nessy1::12c–> cd /u03/app/oracle/product/12.1.0.2/dbhome_1/OPatch

nessy1::12c–> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sat Aug 12 14:47:54 2017Copyright (c) 2012, 2017, Oracle.  All rights reserved.
Log file for this invocation: /u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_125122_2017_08_12_14_47_54/sqlpatch_invocation.log
Connecting to database…OKBootstrapping registry and package to current versions…doneDetermining current state…done
Current state of SQL patches:Patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY):  Installed in the SQL registry onlyPatch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):  Installed in the SQL registry onlyPatch 25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017)):  Installed in the binary registry onlyBundle series DBBP:  ID 170418 in the binary registry and ID 160419 in the SQL registry
Adding patches to installation queue and performing prereq checks…Installation queue:  The following patches will be rolled back:    22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))    21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)  The following patches will be applied:    25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017))    25397136 (DATABASE BUNDLE PATCH 12.1.0.2.170418)
Installing patches…Patch installation complete.  Total patches installed: 4
Validating logfiles…Patch 22674709 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_rollback_NESSY_2017Aug12_14_48_20.log (no errors)Patch 21171382 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/21171382/19741104/21171382_rollback_NESSY_2017Aug12_14_49_09.log (no errors)Patch 25437695 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25437695/21057393/25437695_apply_NESSY_2017Aug12_14_49_17.log (no errors)Patch 25397136 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25397136/21145057/25397136_apply_NESSY_2017Aug12_14_49_17.log (no errors)SQL Patching tool complete on Sat Aug 12 14:49:54 2017nessy1::12c–>nessy1::12c–>nessy1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 12 14:50:05 2017

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.
Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> set lines 9990
SQL>

SQL>

 

SQLselect * from dba_registry;

 

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
APEX       Oracle Application Express       4.2.5.00.08      VALID  02-JUL-2016 16:08:09 SERVER       SYS APEX_040200 VALIDATE_APEX       FLOWS_FILES
OLS       Oracle Label Security       12.1.0.2.0      VALID  02-JUL-2016 16:08:08 SERVER       SYS LBACSYS VALIDATE_OLS
DV       Oracle Database Vault       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS DVSYS VALIDATE_DV       DVF
ORDIM       Oracle Multimedia       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS ORDSYS VALIDATE_ORDIM       MDSYS,ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA
CONTEXT       Oracle Text       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS CTXSYS VALIDATE_CONTEXT
OWM       Oracle Workspace Manager       12.1.0.2.0      VALID  02-JUL-2016 16:08:01 SERVER       SYS WMSYS VALIDATE_OWM
SDO       Spatial       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS MDSYS VALIDATE_SDO       MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR
XDB       Oracle XML Database       12.1.0.2.0      VALID  02-JUL-2016 16:07:57 SERVER       SYS XDB DBMS_REGXDB.VALIDATEXDB       ANONYMOUS,XS$NULL
CATALOG       Oracle Database Catalog Views       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC       Oracle Database Packages and Types       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC       APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM
JAVAVM       JServer JAVA Virtual Machine       12.1.0.2.0      VALID  12-AUG-2017 14:49:17 SERVER       SYS SYS INITJVMAUX.VALIDATE_JAVAVM

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
XML       Oracle XDK       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS XMLVALIDATE
CATJAVA       Oracle Database Java Packages       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
APS       OLAP Analytic Workspace       12.1.0.2.0      VALID  02-JUL-2016 16:08:06 SERVER       SYS SYS APS_VALIDATE
XOQ       Oracle OLAP API       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS SYS XOQ_VALIDATE
RAC       Oracle Real Application Clusters       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS SYS DBMS_CLUSTDB.VALIDATE

 

16 rows selected.

 

SQL>

SQL>

SQL>
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

Script to SET NEWNAME for DATAFILES in RMAN for Cloning

— If doing on Source Server —

::12c–>sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file_id || ‘ to ”/u01/oradata/’ ||
substr(file_name,instr(file_name,’/’,-1)+1) || ”’;’
from dba_data_files ;
spool off
exit;

— If doing on Target Server —

[oracle@testorcl ~]$ sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file# || ‘ to ”/u01/oradata/’ ||
substr(name,instr(name,’/’,-1)+1) || ”’;’
from v$datafile ;
spool off
exit;

Rebuild All Indexes on a Schema in Oracle

spool index_rebuild.sql

select ‘alter index ‘||owner||’.’||index_name ||’ rebuild online nologging;’
from dba_indexes
where owner=upper(‘SAMPLE’);
spool off

First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month

 

select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual;

select trunc((sysdate),’month’) as FirstDay from dual;

Move Table to Another Tablespace Online Oracle Database 12c

The new PL/SQL procedure REDEF_TABLE in Oracle 12c  lets you redefine a table online in a single step. It can do the below changes

  • Move table,partition or index to another tablespace
  • Compression type change on a table including for table,partition and index

More information on DBMS_REDFINITION can be accessed on Oracle Support : Master Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)

We will now go ahead and move the table HR.EMP to another tablespace called TBS01 online.

EXEC
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => ‘HR’,
tname => ‘EMP’,
table_part_tablespace => ‘TBS01’
);
END;
/

uname –> Schema Name in which Table resides
tname –> Table Name
table_part_tablespace –> New Tablespace to which table will be moved
index_tablespace => If you need to move the indexes to another table it can be done using an additional arguement

Restore Table from RMAN Backup Oracle 12c

 

One of the amazing features of Oracle Database 12c is that now you can do a logical restore from a physical backup. Before 12c it was not possible to restore a table from a RMAN backup. This feature is amazing speciall when you have a large database and needs to restore a small table. One of the pre-requisites is you must have a Full Backup of the database and also archivelogs after the fully backup must be available. We can proceed to do the restore with below RMAN command

$ rman target /

RECOVER TABLE HR.EMP
UNTIL TIME ‘SYSDATE-0.22’
AUXILIARY DESTINATION ‘/backups/rmanbkp’
REMAP TABLE ‘HR’.’EMP’:’EMP_RECVR’

HR.EMP is the table to be recovered from the RMAN backup

SYSDATE-0.22 is the time till where the table will be rolled back; this is to specify the point in time for recovery: UNTIL TIME, UNTIL SCN, or UNTIL SEQUENCE can be used

Auxiliary Destination is destination where a instance is created for the recovery process

REMAP TABLE clause is used to specify to restore the table with another name

Using this method even individual table paritions can be restored.