PostgreSQL 9.x – pg_basebackup: could not get transaction log end position from server: FATAL: archive member “core.56229” too large for tar format

PostgreSQL 9.x – pg_basebackup: could not get transaction log end position from server: FATAL:  archive member “core.56229” too large for tar format

While I was trying to re-deploy a PostgreSQL streaming replication slave from the master node, I hit the following error:

[root@node2~]# su - postgres
-bash-4.1$ /usr/pgsql-9.2/bin/pg_basebackup -D /data/var/lib/pgsql/9.2/data -h node1 --xlog -P -v
transaction log start point: 70/CD000020
166537602/235436024 kB (100%), 2/2 tablespaces
pg_basebackup: could not get transaction log end position from server: FATAL: archive member "core.56229" too large for tar format

From node1, I found the following files in the $PGDATA:

4.2G ./core.53808
4.2G ./core.55595
4.2G ./core.55698
4.2G ./core.56229

The above was the core dump from previous PostgreSQL DB crashes. By default, PostgreSQL does not create files larger than 1G, so I figured this is not the datafiles. I asked Uncle Google and found out that it’s the core dump by PostgreSQL DB crashes.

Once I removed the files from the $PGDATA directory. The pg_basebackup command executes successfully. The interesting thing that I learnt is, pg_basebackup uses tar in the backend to compress and send the files over to the slave database.

Regards,
Wei Shan

Leave a comment

MariaDB – ERROR 1033 (HY000): Incorrect information in file:

MariaDB/TokuDB – ERROR 1033 (HY000): Incorrect information in file:

While I was testing the TokuDB HotBackup plugin from Percona, I faced some error during a complete database recovery. After the recovery was completed, I tried verifying the table contents.

MariaDB [partitiontest]> select count(*) from salaries;
ERROR 1033 (HY000): Incorrect information in file: './partitiontest/salaries.frm'

Looking at the error message, I was under the impression that the tables were corrupted.

From the logs:

160127 11:10:56 [ERROR] mysql.plugin: 1 client is using or hasn't closed the table properly
160127 11:10:56 [ERROR] Can't open shared library '/usr/lib64/mysql/plugin/tokudb_backup.so' (errno: 0, cannot open shared object file: No such file or dir
ectory)
160127 11:10:56 server_audit: MariaDB Audit Plugin version 1.3.0 STARTED.
160127 11:10:56 [Note] Server socket created on IP: '::'.
160127 11:10:56 [Warning] 'user' entry 'root@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Warning] 'user' entry '@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Warning] 'proxies_priv' entry '@% root@centos7.local' ignored in --skip-name-resolve mode.
160127 11:10:56 [Note] Reading of all Master_info entries succeded
160127 11:10:56 [Note] Added new Master_info '' to hash table
160127 11:10:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-enterprise-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Enterprise Certified Binary
160127 11:11:28 [ERROR] mysqld: Incorrect information in file: './partitiontest/salaries.frm'

The error message above looks really scary. While snooping around Google, I realized that the error is very vague, I decided to look deeper. On examining the logs again, I realized that the following error:

160127 11:10:56 [ERROR] Can't open shared library '/usr/lib64/mysql/plugin/tokudb_backup.so' (errno: 0, cannot open shared object file: No such file or dir
ectory)

Once I enabled the TokuDB plugin in /etc/my.cnf.d/tokudb.conf, the recovery works fine. I learnt to be more careful when dealing with such MariaDB errors.

Regards,
Wei Shan

Leave a comment

Oracle 11gR2 RMAN – Recovery from loss of all controlfiles

Oracle 11gR2 RMAN – Recovery from loss of all controlfiles

While I’m preparing for my OCM 11gR2 exam in June, I started to practice on various backup and recovery scenarios. Hopefully, it will also be helpful to anyone looking to refresh their mind on RMAN recovery scenarios.

Verify database controlfiles information.

SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------
 /u01/app/oracle/oradata/orcl/orcl/control01.ctl NO 16384 594
 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl NO 16384 594
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- --------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------

Delete all controlfiles and simulate failure

#rm /u01/app/oracle/oradata/orcl/orcl/control01.ctl
#rm /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> alter tablespace users offline;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> startup;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 255855476 bytes
Database Buffers 50331648 bytes
Redo Buffers 6328320 bytes
ORA-00205: error in identifying control file, check alert log for more info

Perform RMAN recovery. Note that I’m using RMAN catalog here with known DBID.

[oracle@localhost tmp]$ echo $ORACLE_SID
orcl
[oracle@localhost tmp]$ rman target / catalog rcuser@rman
connected to target database: ORCL (not mounted)
recovery catalog database Password:
connected to recovery catalog database
RMAN> set DBID 1426604546;
executing command: SET DBID
database name is "ORCL" and DBID is 1426604546
RMAN> run
2> {
3> set controlfile autobackup format
4> for device type disk to '%F';
5> restore controlfile from autobackup;
6> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 02-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_01_02/o1_mf_s_900095659_c8h9xcgo_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160102
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_01_02/o1_mf_s_900095659_c8h9xcgo_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/orcl/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 02-JAN-16
RMAN> exit

Now, let’s try to bring up the database since my controlfiles are restored :)

[oracle@localhost tmp]$ echo $ORACLE_SID
orcl
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 255855476 bytes
Database Buffers 50331648 bytes
Redo Buffers 6328320 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/orcl/system01.dbf'

Note the above error. Apparently, my controlfile is not insync with my datafile1 . Oh dear, let’s try to restore and recover the entire database then.

RMAN> recover database;
Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_27_c8h7hltr_.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_28_c8hlwc1w_.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/oradata/orcl/orcl/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_27_c8h7hltr_.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_28_c8hlwc1w_.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/oradata/orcl/orcl/redo02.log thread=1 sequence=29
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-JAN-16
RMAN> exit

Let’s try to bring up the database again! Note that we are opening the database with resetlogs as we are restoring controlfiles from backup.

[oracle@localhost tmp]$ sqlplus / as sysdba
SQL> alter database open resetlogs;
Database altered.

Verify that the controlfile records are the same.

SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- --------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------

Regards,
Wei Shan

1 Comment

Oracle 11gR2 RMAN – Recovering of single datafile (ORA-01109)

Oracle 11gR2 RMAN – Recovering of single datafile(ORA-01109)

Today, I learnt something new about Oracle RMAN, thus the need for this post to document it down.

Verify that tables in SCOTT schema is stored in USERS tablespace which is in /u01/app/oracle/oradata/orcl/orcl/users01.dbf

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SCOTT';
OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT DEPT USERS
SCOTT EMP USERS
SCOTT BONUS USERS
SCOTT SALGRADE USERS

Remove USERS tablespace datafile

# rm /u01/app/oracle/oradata/orcl/orcl/users01.dbf

Verify that there’s indeed an error with the database!

SQL> alter system flush buffer_cache
SQL> select count(*) from scott.emp;
select count(*) from scott.emp
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate;

Bring the USERS tablespace offline first

SQL> startup mount;
SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01109: database not open

Now, note the above error that I can’t offline the tablespace. I was unable to offline a tablespace when the database is not in OPEN mode. However, we can offline a datafile while not in OPEN mode :)

SQL> SELECT * FROM v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
 4 ONLINE ONLINE FILE NOT FOUND 0
SQL> alter database datafile 4 offline;
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_
-------------------------------------------------- ---------- 
/u01/app/oracle/oradata/orcl/orcl/users01.dbf 4 USERS OFFLINE
/u01/app/oracle/oradata/orcl/orcl/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u01/app/oracle/oradata/orcl/orcl/sysaux01.dbf 2 SYSAUX ONLINE
/u01/app/oracle/oradata/orcl/orcl/system01.dbf 1 SYSTEM SYSTEM
SQL> alter database open;
Database altered.

Now that the database is open, we should offline the USERS tablespace before performing recovery.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS OFFLINE

Perform RMAN restore and recover

[oracle@localhost admin]$ rman target / catalog rcuser@rman

connected to target database: ORCL (DBID=1426604546)
recovery catalog database Password:
connected to recovery catalog database
RMAN> restore datafile 4;
Starting restore at 02-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /archival/05qqcl6n_1_1
channel ORA_DISK_1: piece handle=/archival/05qqcl6n_1_1 tag=LONG01
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 02-JAN-16
RMAN> recover datafile 4;
Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-JAN-16

Verify that everything is back to normal :)

SQL> alter tablespace users online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,ONLINE_STATUS from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME ONLINE_
-------------------------------------------------- ---------- ------------------------------ -------
/u01/app/oracle/oradata/orcl/orcl/users01.dbf 4 USERS ONLINE
/u01/app/oracle/oradata/orcl/orcl/undotbs01.dbf 3 UNDOTBS1 ONLINE
/u01/app/oracle/oradata/orcl/orcl/sysaux01.dbf 2 SYSAUX ONLINE
/u01/app/oracle/oradata/orcl/orcl/system01.dbf 1 SYSTEM SYSTEM

Summary

This was something that I encounter recently while practicing my backup and recovery skills for the upcoming OCM Exam. I forgotten that I could only offline datafile but not tablespace while the database is not in OPEN mode. The reason is because while the database is in MOUNTED state, only the controlfile is read, hence, you can only modify the physical structure but not the logical structure. (link1, link2)

Regards,
Wei Shan

Leave a comment

MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

MariaDB/MySQL + TokuDB Partitioning Error – ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The above error occurs while trying to add a new partition in a MariaDB/MySQL partitioned table.

Existing table definition

MariaDB [partitiontest]> show create table salaries;
| salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
/*!50500 PARTITION BY RANGE COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */

Error occur while trying to add a new partition

alter table salaries
add partition
(partition p20 values less than ('2003-12-31')
engine=TokuDB);
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

The error is due to the following reasons:

  • You are trying to add a “lesser” value that the current largest value, which captures all
  • In this case, I was trying to add <2003-12-13 date, which is lesser than the current last partition, MAXVALUE

I will now “REORGANIZE” the table so that I can add a partition between “<2002-12-13” and “MAXVALUE”.

alter table salaries
reorganize partition p19 into
(partition p19 values less than ('2003-12-31'),
partition p20 values less than (MAXVALUE)
)

Let’s take at a look at the happier table now!

MariaDB [partitiontest]> show create table salaries;
 | salaries | CREATE TABLE `salaries` (
 `emp_no` int(11) NOT NULL,
 `salary` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 PRIMARY KEY (`emp_no`,`from_date`)
 ) ENGINE=TokuDB DEFAULT CHARSET=latin1 `compression`='tokudb_zlib'
 /*!50500 PARTITION BY RANGE COLUMNS(from_date)
 (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = TokuDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = TokuDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = TokuDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = TokuDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = TokuDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = TokuDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = TokuDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = TokuDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = TokuDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = TokuDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = TokuDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = TokuDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = TokuDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = TokuDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = TokuDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = TokuDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = TokuDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = TokuDB,
 PARTITION p19 VALUES LESS THAN ('2003-12-31') ENGINE = TokuDB,
 PARTITION p20 VALUES LESS THAN (MAXVALUE) ENGINE = TokuDB) */|

Regards,
Wei Shan

Leave a comment

Puppet – err: Could not retrieve catalog from remote server: Error 400 on SERVER

Puppet – err: Could not retrieve catalog from remote server: Error 400 on SERVER

While I was deploying a simple Puppet setup, I faced the above error whenever I tried to execute the following the Puppet client:

[root@puppetclient ~]# puppet agent --test --noop
info: Retrieving plugin
info: Loading facts in /var/lib/puppet/lib/facter/puppet_vardir.rb
info: Loading facts in /var/lib/puppet/lib/facter/oracle_database_homes.rb
info: Loading facts in /var/lib/puppet/lib/facter/concat_basedir.rb
info: Loading facts in /var/lib/puppet/lib/facter/root_home.rb
info: Loading facts in /var/lib/puppet/lib/facter/facter_dot_d.rb
info: Loading facts in /var/lib/puppet/lib/facter/pe_version.rb
err: Could not retrieve catalog from remote server: Error 400 on SERVER: Could not find default node or by name with 'puppetclient.local, puppetclient' on node puppetclient.local
warning: Not using cache on failed catalog
err: Could not retrieve catalog; skipping run

I tried the PuppetLabs troubleshooting guide, however I still could not find the root cause. I also tried to “cleanup” the Puppet SSL certifications.

[root@puppetclient ~]# rm -f /var/lib/puppet/ssl/certs/puppetclient.local.pem
[root@puppetmaster ~]# puppet cert clean puppetclient.local

After further investigation, it was yet another silly mistake! In the Puppet-Master, puppetclient.local was not defined in the nodes.pp. After adding the node definition, it was resolved!

node 'puppetnewclient.local' {
 include ntp
 include sysctl
 }

Regards,
Wei Shan

 

 

Leave a comment

Puppet, a tool in the DevOps engineer’s Arsenal

Puppet, a tool in the DevOps engineer’s Arsenal

What is Puppet?

Puppet is a open-source configuration management tool. Adoption of DevOps means that you will need some sort of configuration management tool, else you can’t automate your software delivery. Puppet is one of the many configuration management tools out there. There are many others like Chef, Ansible or Salt. There are already a HUGE number of discussion online regarding Puppet vs Chef vs Ansible vs Salt so I’m not going to talk about them over here.

What is the value of Configuration Management tool?

The power of configuration management tool will be obvious when you have hundreds and thousands of nodes to manage. When you have less than 50 nodes, you can decide the purpose of each node and manually install the required packages on it. If there’s a patch, you can just install the new fix easily. If there’s a need to disable SSLv2, you can just login to each of the server to disable them.

  • What happens if you have 1000 nodes, and you need to disable SSLv2 on all of them?
  • What if you need to update the gcc packages on 40/1000 nodes?
  • What if the annoying IT auditors want to audit your security patches in your environment?

Why do I like Puppet?

I prefer using Puppet because the following reasons:

  • Non-programmer friendly. You don’t have to write any OO code. Puppet DSL is a declarative language. You just have to specify the end state.
  • Gentle learning curve. The Puppet DSL is quite intuitive.
  • Largest community around. This is important because you can get help easily via Google
  • Additional plugin modules are available if you want to provision any software using Puppet. (For etc, Oracle)

How is using Puppet going to help me as a Database Administrator?

The typical databases to DBA ratio is about 250:1. The volume and complexity of the databases you manage is growing. On top of that, you’ve got demands from the business to do more and faster, with the same amount of resources and manpower. Now, with additional Puppet modules, you can actually manage the database level configurations using Puppet.

For example;

tablespace {'PIO_DATA': 
ensure => 'present', 
bigfile => 'yes', 
datafile => 'pio_data.dbf',
size => '200G', 
logging => 'yes',
autoextend => 'on', 
next => '100M', 
max_size => '12288M',
extent_management => 'local', 
segment_space_management => 'auto', }

Summary

I believe that using configuration management tool to deploy databases is a new trend for DBAs. It allow us to manage thousands of databases easily. However, there certain caveats to it. The Puppet oradb module is still quite new, there are still certain bugs that needs to be iron out. That said, I still believe that it is another alternative to to building your own DBaaS using Oracle OEM.

It is something I believe DBAs should explore and determine if it’s suitable for your own environment.

Regards,
Wei Shan

2 Comments

SQL Vs NoSQL Vs NewSQL Databases

SQL Vs NoSQL Vs NewSQL Databases

Before I join the database field in 2011, I was told that the role of a Database Administrator(DBA) is extremely dull and boring. However, until now, it has been nothing but exciting. Buzz words like Big Data and IoT have generated new found interest in databases. The number of new databases that have sprung out of nowhere is tremendous. CockroachDB, Riak, Voldemort and etc.. In this post, I will be sharing about 3 different category of databases.

SQL Databases

SQL, also known as relational database management system(RDBMS), is what most companies are running on today. Popular relation databases are Oracle, SQL Server, MySQL and PostgreSQL. Data are store in rows and columns. You can use SQL to query for data in a RDBMS. A lot of applications are running on either of them. Bank transactions, Data Warehouse operations and payment transactions. These are the core applications that any companies will be supporting.

Relational databases are famous for ACID. Atomicity, Consistency, Isolation and Durability. You can read about them over here. Basically, it means that your transaction, once committed, it will be safe and there should not be any data loss. Simultaneous transactions should not interfere with each other.

NoSQL Databases

NoSQL is a term for databases that does not store its data in rows and columns. They are typically not ACID but BASE compliant. This is because they sacrifice C for AP in CAP theorem.  Also, you don’t have to define your schema upfront and is considered “schema-less”. Below are the different types of NoSQL databases

  • Document => MongoDB
  • Key-Value => Redis
  • Graph => Neo4j
  • Column => Cassandra

Different types of NoSQL database are specific for different use case. For example, Neo4j is perfect as a data-store for social media sites because supports defining relations between entities. MongoDB are typically used for e-commerce sites because each cart can be defined as 1 collection. Redis is often being used as a queue or caching tier that sits in-front of another database. NoSQL can be an entirely subject of it’s own and I’m probably not qualified to write anything more than an introduction on it. (But this won’t be true after I finish my MongoDB course!)

NoSQL can be accessed use SQL-LIKE language or their own native language like Neo4j-Cypher.

NewSQL Databases

NewSQL is our shorthand for the various new scalable/high-performance SQL database vendors. We have previously referred to these products as “ScalableSQL” to differentiate them from the incumbent relational database products. Since this implies horizontal scalability, which is not necessarily a feature of all products, we adopted the term NewSQL in the new report. And to clarify, like NoSQL, NewSQL is not to be taken too literally: the new thing about the NewSQL vendors is the vendor, not the SQL. NewSQL is a set of various new scalable/high-performance SQL database vendors (or databases). These vendors have designed solutions to bring the benefits of the relational model to the distributed architecture, and improve the performance of relational databases to an extent that the scalability is no longer an issue.

– 451 Group’s senior analyst, Matthew Aslett

NewSQL are pretty new to the database industry. They are supposed to be ACID-compliant yet be highly scalable. Also, they are also using SQL-LIKE for CRUD operations. Below are some NewSQL databases.

  • VoltDB
  • Clustrix
  • NuoDB

In order to achieve the above requirements, different NewSQL vendors have implemented their design different.For example, NuoDB uses a “2 tier approach”. Transaction Engines hold a subset of the objects in memory while Storage Managers are servers that have a complete copy of all objects on disks. Also, NuoDB uses Durable Distributed Cache (DDC) which sound like a massive RAC-Cache Fusion to me. VoltDB uses a mixture of partitioning, stored procedures as a unit of transaction and deterministic command to attain the same. Each of the NewSQL databases have very different concepts to it!

Summary

In my opinion, NoSQL will never replace SQL databases. They are simply used for different scenario and it’s never meant to replace each other. The RDBMS is just so good in handling OTLP transactions that you will never want to migrate it to MongoDB or Riak because of performance or ACID compliance. Likewise, you wouldn’t want your 25-node MongoDB cluster to be migrated to Oracle databases. You will either die trying or your company will go bankrupt first.

However, I believe that we will see a huge increase in companies adopting NoSQL because storing and analysing data is becoming very crucial or business to succeed these days. It’s becoming a key differentiator in the corporate world. It helps understand your customers better. Look at companies like Uber or Amazon. They are real good examples of using data to succeed.

Some people may ask, if NoSQL/NewSQL is so powerful, why don’t people use it to replace their existing Oracle Data Warehouse. Typical Oracle shops spend at least 7-figure sum on the Data Warehouse licensing per year (EE Edition, RAC, AWR packs..). In my opinion, there are a couple of reasons why:

  1. Current analytics tool are pretty robust and mature. But they are only support on ANSI compliant SQL language. Using non-SQL or SQL-LIKE tool will require a re-write of all their existing software.
  2. RDBMS contains dimension and fact tables which are already “cleaned” as compared to NoSQL
  3. SQL is a very easy language to learn and the existing users are just too comfortable on it

However, I believe that NoSQL will soon rise to join the ranks of SQL database in the Data Warehouse scene. This is because companies now want to store their non-structured data as part of their data lake. So they could be using RDBMS for their existing applications and using NoSQL for non-structured data. Then they could use something like Big SQL to query data from both type of databases, forming something called Data Lake.

For DBAs like myself, it means you will get to play with more databases! :)
Cheers,
WeiShan

Leave a comment

Follow

Get every new post delivered to your Inbox.