Big Data with Hadoop Training

Introduction

We have some exciting news for you. We are starting the Big Data with Hadoop training program and Srini Ramineni will be the faculty for this course. Srini Ramineni is the founder of DBA University and has rich experience in the IT industry in USA managing both relational databases as well as Big Data with Hadoop environments. He also has completed a graduate degree credit hour program in Big Data using Hadoop at a reputed university in USA.

The training topics include the Hadoop core components viz. The Hadoop File System (HDFS) and the Map Reduce computation paradigm.  All the other major sub projects of the Hadoop solution such as Apache Sqoop, Hive, Pig, Impala, Flume and Spark are also included in this training course.  Most of the topics have lab exercises too.

Course Highlights

1) 6 and half weeks course with 40 hours total duration.

2) Training will contain lab work on both single node machine as well as a Hadoop cluster (multi-node).

3) Evenings - Sun, Mon, Tue  - 7 pm Central Time to 9 pm Central Time

4) DBA University's training material will be provided.

5) Click here for a FREE DEMO session  (Name and Email only)

6) Click here to register for the actual training course.

Links and Resources

1) Click here to know more about the course topics.

2) Click here for a FREE DEMO session  (Name and Email only)

3) Click here to register for the actual training course.

How to fix VNC Viewer black screen

Introduction

Have you seen your VNC Viewer showing a black screen intermittently ?  We have a fix for it. This issue has been occurring in our lab servers for a few students intermittently since the past few months on Oracle Linux server 6.5 release.

Problem

The VNC Viewer desktop background turns into a full black screen intermittently. It does not happen all the time and one of our students observed that this issue happens consistently when she  hovers her mouse on the VNC Config icon on the top left hand corner. This issue happens even if this icon is minimized. This observation is correct.

Solution

A workaround is to just close the above VNC config icon instead of minimizing it. But the permanent solution is to let the vncconfig deamon run in nowin mode (no window icon). Our vncserver service is owned by oracle operating system account. 

a) Login to the server using a different protocol such as ssh on port 22.

b) cd /home/oracle/.vnc

b) Edit xstartup file and replace vncconfig -iconic & with vncconfig -nowin &

c) Now bounce the vncserver service using the below commands

vncserver -kill :1.0
vncserver
export DISPLAY=:1.0
xhost +

Conclusion

The -nowin parameter will make the vncconfig functionality stay intact, but without the desktop icon. The actual cause of the problem could be due to an issue with the vnc-server RPM itself, specifically tigervnc-server-module 1.1.0-8.el6_5.x86_64.

Learn SQL Language to work with Big Data in Hadoop

Introduction

SQL (Structured Query Language) is the most widely used language to read and write data in relational databases such as Oracle, MS SQL Server and MySQL databases etc. SQL language existed since the 1970's and is also a standard of the American National Standards Institute (ANSI).

Because of most of the traditional databases such as corporate internal databases and customer facing databases such as those driving retail websites use the relational database model, SQL language skills has become a mandatory skill for Information Technology (IT) staff such as developers, analysts, database administrators and even software QA engineers, project managers and IT auditors etc.

Big Data

Apache Hadoop, which is the most popular framework for storing and processing Big Data, has a related sub project component called Apache Hive. Initially developed by Facebook Inc. and later open sourced, Apache Hive provides a data warehouse solution on top of Hadoop. It provides a relational database abstraction for Hadoop's Big Data (though Hadoop's databases are not relational databases). Of particular note is the HiveQL (SQL like language to query and analyze Hadoop's data) . If one already is an SQL language expert, learning HiveQL is very easy and helps a traditional (relational database) user work with most of Hadoop's Big Data easily.

Apache HCatalog extends  Hive (now since merged with Hive in 2013) by publishing a RESTful interface so that even external systems can access the Hive metadata using RESTful methods. Most of the popular distributions of Hadoop include the Hive component as well (now HCatalog) such as the HortonWorks Data Platform (HDP).  IBM's Hadoop distribution BigInsights has a similar component called as BigSQL.  Per IBM's blog, BigSQL is an enterprise-class SQL query engine and has better ANSI SQL support than HiveQL.

DBA University's Oracle SQL PL/SQL Training course

DBA University has a comprehensive Oracle SQL PL/SQL Training course and we encourage you to register for it if you don't have at least an intermediate level knowledge in SQL and PL/SQL.  It is a very detail oriented training course with remote lab server access for each student for practicing the SQL and PL/SQL lab exercises.

http://www.dbauniversity.com/oracle_sql_plsql_training.html

Classes :Sun,Mon,Tue,Wed   evenings

Duration: 7 to 8 weeks approximately.

Lab Work: Remote lab server access for each student.

This online course will cover Relational Database Design, Oracle SQL Language and PL/SQL programming topics. 

a) Week 1     - Relational Database Design and software installations. 

b) Week 2,3,4 - Oracle SQL Language. 

c) Week 5,6,7 - Oracle PL/SQL Language. 

d) Week 8     - Final week to catch up any remaining topics. 

How to backup sql server database with checksum option

Introduction

If you compare Oracle database and MS SQL Server databases, there are some wins for Oracle and some for MS SQL Server. In this blog post, I will talk about a backup feature with Oracle as the winner.

Oracle block checking during RMAN backups

By default, an Oracle RMAN database backup computes the checksum for each block and stores it in the backup, even if the DB_BLOCK_CHECKSUM command is set to TYPICAL. DB_BLOCK_CHECKSUM initialization parameter applies to data files in the database and not applicable to backups. 

Therefore one way of recognizing block media corruptions in an Oracle database is when Oracle RMAN database backup terminates with errors after it fails to perform a checksum for those corrupted blocks. So an Oracle DBA need not monitor for block media corruptions separately because the database backup (if the RMAN method is used) command itself acts as such a validation tool inherently.

MS SQL Server page (block) checking during backups

The corresponding term in MS SQL Server for datafile blocks is a page.  In SQL Server, most database administrators create the backup database task through maintenance plans. But one cannot rely on these maintenance plans for page (block) checksum and torn-page detection during backups. Even if the "Verify backup integrity"  option is enabled as part of the backup database task in a maintenance plan, it does nothing more than checking whether the backup media is readable without having to perform an actual restore. So it is superficial in nature.

Therefore, it is strongly recommended that MS SQL Server database administrators create separate T-SQL scripts or stored procedures to explicitly backup databases using the with checksum option. 

For example, a database called AdventureWorks can be backed up using the below command.

backup database AdventureWorks  to disk = 'D:\backups\AdventureWorks_01032015.bak' with NOINIT,CHECKSUM;

Additionally, in the same T-SQL script or stored procedure, verify backup integrity using the with checksum option. For example,

restore verifyonly from disk = 'D:\backups\AdventureWorks_01032015.bak' with CHECKSUM;

SQL Server will check every page in the backup file by reading and recomputing the page checksum and then compares it to the recorded page checksum in the backup file to ensure integrity.

Conclusion

It is recommended that MS SQL Server DBAs create a separate T-SQL script or a stored procedure to backup all databases using the with checksum option. This blog post is validated against Oracle 11g R2 and 10g R2 databases and MS SQL Server 2008 and 2008 R2 databases. Please note the extra time taken for a restore verifyonly with checksum command.  Another observation that may be noted is that a backup without a checksum and a backup with checksum  takes about the same time. 

Oracle tablespace export expdp - Tips and Restrictions

Introduction

In a previous post, we have explained about Oracle tablespace export using the export data pump utility (expdp).  In this post, we will add more details to provide information on some restrictions and tips using the tablespace export data pump option.

Oracle does not export tablespace metadata

If you unload the tablespace using the tablespace export mode, Oracle does not export the metadata about the tablespace even if the expdp was run by a user with the DATAPUMP_EXP_FULL_DATABASE role.  This is unlike a schema mode export which exports even the schema metadata (schema creation definition, system and role grants to the schema etc.) if you perform such an export using a database user with the DATAPUMP_EXP_FULL_DATABASE role. But there is a workaround to obtain the tablespace metadata as well using the DBMS_METADATA package.

For example, if you want to perform a tablespace export data pump operation on HRTS tablespace, you can save its metadata (tablespace creation definitions) first using the below command.

select dbms_metadata.get_ddl('TABLESPACE','HRTS') from dual;

(Tip: This functions returns output as CLOB datatype, so if you use in SQL*Plus , it may truncate the returned output. One option is to use a bigger output buffer option such as "set long 100000")

Oracle does not export PL/SQL procedures dependent on tables in the tablespace

Tablespace export data pump operation only unloads tables only. Though Oracle mentions in  the utilities guide that the table's dependent objects are also unloaded, this seems to be only for indexes.  If you have a procedure which refers these tables in the tablespace, they are not exported. This could be because procedures are stored in the SYSTEM tablespace and not in the tablespace in question that is being exported out of the database.

Oracle expects tablespaces created already during import data pump operation.

Because Oracle does not export tablespace metadata (tablespace creation definitions), you have to create the tablespaces with adequate size (atleast equal to the source tablespace size) in your destination database.  If your plan is to use different tablespace names, you can use the REMAP_TABLESPACE option in the import data pump (impdp) command. The below example import data pump (impdp) command demonstrates how to import the EMP table only into a HRTS_NEW tablespace in the destination database.

impdp tables=EMP remap_tablespace=(HRTS:HRTS_NEW) dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp logfile=DATA_PUMP_DIR:impdp_orders_log.log

Conclusion

Use the tablespace export mode if your sole aim is to only export all tables and their dependent indexes in a tablespace. A related use case for a tablespace export is for taking  a backup of tables and indexes in the tablespace prior to dropping the tablespace.  The examples used in this post are tested on a 11g R2 Oracle database and should work on 12c R1 and 11g R1 databases as well.

ORA-01501: CREATE DATABASE failed ORA-01519 sql.bsq

Introduction

If you are still supporting Oracle 10g R2 databases and are encountering the error ORA-01092: ORACLE instance terminated. Disconnection forced instance termination while running the CREATE DATABASE statement using the Oracle provided syntax in the administrator's guide, then you may be hitting this bug. After further investigation of the alert log file, you may notice the below errors.

ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 6268
ORA-00604: error occurred at recursive SQL level 1
ORA-02236: invalid file name

Problem

Most Oracle DBAs use the Oracle's example CREATE DATABASE command in the administrator's guide.

There is a documentation bug in Oracle 10g R2 administrator's guide (Part No B14231-02).  Specifically, the CREATE DATABASE statement in Page 77 of the PDF

If you observe closely, these errors are caused by the line "DEFAULT TABLESPACE tbs_1". There is no data file specified for this tablespace in the CREATE DATABASE command.


CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Solution

Just remove the line DEFAULT TABLESPACE tbs_1 in the CREATE DATABASE statement and retry. This time your database will be created successfully.

How to configure and start VNCServer on Linux

How to configure and start VNCServer on Linux 6

Please follow the below guidelines to successfully configure and start VNCServer service on Linux operating system versions 6.x (Red Hat Enterprise Linux, Oracle Enterprise Linux, CentOS etc.). If your operating system has version 7.x, then please follow this article instead.

1) As root, edit /etc/sysconfig/vncservers and add the below line at the end of the file.
VNCSERVERS=”1:root”

This is to start the vncserver process automatically after a server reboot using root as the vncserver process owner. If you want a different operating system user, then replace the user root with the other operating system user name

2) As root, run the command chkconfig vncserver on


3) Run the vncpasswd command to set the password to connect to the vncserver service.


4) If you want to terminate the vncserver process, execute the vncserver -kill :1.0 command. The :1.0 is the display setting.