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

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;

EXPDP/IMPDP – ORA-04063 (view “SYS.KU$_RADM_FPTM_VIEW” Has Errors) on Full Export / Import

This is caused by a mismatch between the character set in which the table radm_fptm$ is created and in which the type ku$_radm_fptm$_t is created. This leads to the ORA-932 error when trying to recompile the view SYS.KU$_RADM_FPTM_VIEW, you have specified UTF8 for the national characaterset instead of the default AL16UTF16 national characaterset:

To Check Character set

col parameter for a35
col value for a40
select * from nls_database_parameters where parameter like ‘%SET’ order by 1;

 

Workaround is  to recompile the Recompile the View ku$_radm_fptm_t

alter type ku$_radm_fptm_t compile reuse settings;

Type altered.

SQL> alter view SYS.KU$_RADM_FPTM_VIEW compile;

View altered.

set lines 140
col status for a9
col object_type for a20;
col owner.object for a50
select con_id, status, object_id, object_type,
owner||’.’||object_name “OWNER.OBJECT”
3 from cdb_objects where object_name like ‘%KU$_RADM_FPTM_VIEW%’ order by 4,1;

 

CON_ID STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
———- ——— ———- ——————– ————————————————–
0 VALID 14188 VIEW SYS.KU$_RADM_FPTM_VIEW

 

 

 

Oracle 12c Transparent Data Encryption (TDE) on RAC Database with Physical Standby Database : Multiple Databases in Single Oracle Home

Oracle 12c TDE on RAC Database with Physical Standby RAC. The Oracle Database Home has Multiple Instances, due to which we have to use parameter $ORACLE_UNQNAME for ENCRYPTION_WALLET_LOCATION in sqlnet.ora file

In Oracle 12c ALTER SYSTEM and orapki has been deprecated and the correct method is to use ADMINISTER KEY MANAGEMENT commands. Many of the statements from the ALTER SYSTEM SQL statement correspond to the ADMINISTER KEY MANAGEMENT statement.

1. Create folder for wallets, since this is RAC cluster database ,it is better to have a shared filesystem. In our case it is ACFS
mkdir -p /ACFS/wallets/primeprd
mkdir -p /ACFS/wallets/onlnprd
chown -R oracle:oinstall /ACFS/wallets/primeprd
chown -R oracle:oinstall /ACFS/wallets/onlnprd
2. Create sqlnet.ora file with encryption wallet location, do on both nodes with oracle user
ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))
3. Set DB Unique Name for the databases and verfiy the encryption wallet location, do on both nodes with oracle user

Also it is better to set the ORACLE_UNQNAME in the user profile

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primeprd -T “ORACLE_UNQNAME=primeprd”
select * from v$encryption_wallet;

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlnprd -T “ORACLE_UNQNAME=onlnprd”
select * from v$encryption_wallet;

4. At Dr side, with Oracle user in DB home directory
mkdir -p /ACFS/wallets/primedr
mkdir -p /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/onlndr
chown -R oracle:oinstall /ACFS/wallets/primedr

Create sqlnet.ora file in DR side with encryption wallet location, do on both nodes with oracle user

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=/ACFS/wallets/$ORACLE_UNQNAME/)))

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d onlndr -T “ORACLE_UNQNAME=onlndr”

export ORACLE_UNQNAME=`$ORACLE_HOME/bin/srvctl config database |grep -w ${ORACLE_SID%?}`
echo $ORACLE_UNQNAME
srvctl setenv database -d primedr -T “ORACLE_UNQNAME=primedr”

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management create keystore ‘/ACFS/wallet/primeprd/’ identified by “Abc1234$#”;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> administer key management create key identified by Abc1234$# with backup;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN_NO_MASTER_KEY PASSWORD SINGLE UNDEFINED
0
SQL> select key_id from v$encryption_keys;

KEY_ID
——————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL> administer key management use key ‘AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’ identified by Abc1234$# with backup;

keystore altered.

SQL> select key_id,activation_time from v$encryption_keys;

KEY_ID
——————————————————————————
ACTIVATION_TIME
—————————————————————————
AcVlvDzDNE8iv/rD38jpCtYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
23-NOV-16 09.53.46.485930 AM +00:00
SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
CLOSED UNKNOWN SINGLE UNDEFINED
0
SQL> administer key management set keystore open identified by Abc1234$#;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN PASSWORD SINGLE NO
0
— Create Auto Login for the Keystore —

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE ‘/ACFS/wallet/primeprd/’ identified by Abc1234$#;

keystore altered.

primeprd1::12c–>srvctl stop database -d primeprd
primeprd1::12c–>srvctl start database -d primeprd
primeprd1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:13:30 2016

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> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallet/primeprd/
OPEN AUTOLOGIN SINGLE NO
0
Copy the Encryption key Files to Standby Site as “Oracle” user

primeprd2::12c–>cd /ACFS/wallet/primeprd/
primeprd2::12c–>scp -r * oracle@exadrdbadm01:/ACFS/wallets/primedr

The authenticity of host ‘exadrdbadm01 (10.5.238.11)’ can’t be established.
RSA key fingerprint is c8:c0:69:80:1c:c8:ea:e3:6c:46:6b:c1:05:86:aa:c9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘exadrdbadm01,10.5.238.11’ (RSA) to the list of known hosts.
oracle@exadrdbadm01’s password:
cwallet.sso 100% 3893 3.8KB/s 00:00
ewallet_2016112309525312.p12 100% 2408 2.4KB/s 00:00
ewallet_2016112309534645.p12 100% 3656 3.6KB/s 00:00
ewallet.p12 100% 3848 3.8KB/s 00:00

— At DR Side on Both Nodes–
SQL> select * from v$encryption_wallet;
WRL_TYPE
——————–
WRL_PARAMETER
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID
—————————— ——————– ——— ——— ———-
FILE
/u03/app/oracle/admin/primedr/wallet
NOT_AVAILABLE UNKNOWN SINGLE UNDEFINED
/home/oracle::12c–>cd /u03/app/oracle/product/12.1.0.2/teststandby/network/admin/
/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>vi sqlnet.ora
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /ACFS/wallets/primedr/$ORACLE_UNQNAME/)))

/u03/app/oracle/product/12.1.0.2/teststandby/network/admin::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 23 13:27:22 2016

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> select * from v$encryption_wallet;

WRL_TYPE
——————–
WRL_PARAMETER
——————————————————————————–
STATUS WALLET_TYPE WALLET_OR FULLY_BAC
—————————— ——————– ——— ———
CON_ID
———-
FILE
/ACFS/wallets/primedr/
OPEN AUTOLOGIN SINGLE NO
0
SQL> exit

 

5. Create Encrypted Tablespace
CREATE TABLESPACE encrypt_ts
DATAFILE ‘+DATAC1’ SIZE 250M
ENCRYPTION USING ‘AES256’
DEFAULT STORAGE (ENCRYPT);

Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)

Downloadable free tool from MOS for doing a Security assessment for Oracle Database (compatible with 10.2,11.2,12)

https://support.oracle.com/epmos/faces/DocumentDisplay?id=2138254.1

RMAN — ORA-19909: datafile 1 belongs to an orphan incarnation in a Standby Database.

We had an interesting scenario where we did a full rman refresh of a standby database becuase the archivelogs went missing and there was a gap. After doing the restore we got the below error in the RMAN recovery

rman nocatalog target sys/******@mw auxiliary /
connected to target database: MW (DBID=000000000)
using target database control file instead of recovery catalog
connected to auxiliary database: MW (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
The restore was succesful but the recovery gave below error

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DATAC1/mwdr/datafile/system.572.867661777’
After doing a lot of googling and searching support.oracle.com it turns out it has something to do with the
incarnation of databases. We checked the incarnation for both the Primary and Standby database.

Output for Primary Database
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 MW 1312370394 PARENT 1 24-AUG-13
2 2 MW 1312370394 CURRENT 925702 10-JUL-14
Output for Standby Database

RMAN> list incarnation of database;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 MW 1312370394 PARENT 1 24-AUG-13
2 2 MW 1312370394 ORPHAN 925702 10-JUL-14
4 4 MW 1312370394 ORPHAN 20799166 23-OCT-14
3 3 MW 1312370394 CURRENT 22477563 30-OCT-14


Solution:

As we can see clearly from the output the primary database is currenly on a different incarnation from the standby database. The way to address it is to reset the incarnation on the standby database to match the primary incarnation.
RMAN> reset database to incarnation 2;

RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 MW 1312370394 PARENT 1 24-AUG-13
2 2 MW 1312370394 CURRENT 925702 10-JUL-14
4 4 MW 1312370394 ORPHAN 20799166 23-OCT-14
3 3 MW 1312370394 ORPHAN 22477563 30-OCT-14
As you can now the incarnations match. Now do the recovery for the database again and the logs will apply and start shipping like normal again.