OCP DBA | Database Engineer | Oracle, MySQL, MariaDB, MS Access, VBA | Wordpress, Linux/UNIX, AWS, OpenStack
Requirements: MySQL 8.0 CentOS 7 Virtual Machine or Physical Server/Desktop Internet on the CentOS Server Installation: Create an operating system user named as “mysql” by logging in to the operating system root user and add the user to the “sudo” list. # useradd mysql # passwd mysql # usermod -aG wheel mysql Install the wget…
Scenario: ORA-01031: insufficient privileges raised during the creation of materialized view (MVIEW) on another schema by the SYSTEM user. CREATE MATERIALIZED VIEW kaysar.mview_test BUILD IMMEDIATE AS (SELECT * from kaysar.test); [Error] Execution (4: 26): ORA-01031: insufficient privileges Investigation: The SYSTEM already has the privilege of CREATE_MATERIALIZED_VIEW to create the MVIEW on another schema but failed…
Scenario: Got below error from application end. SQL state [HY001]; error code [1038]; (conn=15945) Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: (conn=15945) Out of sort memory, consider increasing server sort buffer size at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) Investigation: Detailed dig down on the MariaDB error log found the below error. [ERROR]…
Scenario: All the three nodes of Galera cluster uses 98 percent of memory and swapping heavily, no DML could be performed. Investigation: From the error log on the node 3 the below error has been notified: Detected table cache mutex contention at instance 1: 60% waits. Additional table cache instance activated. Number of instances after…
mysqldump backup utility of MySQL/MariaDB supports the export of metadata or specifically creating DDL scripts with the option –no-data. $ mysqldump -uroot -p test –no-data –routines –events –triggers > test_ddl.sql Note: Remember to mention the –routines, –events and –triggers also to export the related objects.
Background: During the application development phase and more often the DDL needs to be backed up for the schema objects in .SQL format which supports the readability and recover or portability of the objects creation later on when needed. This could be easily done by the oracle data pump utility. Workaround: Step1: First make the…
Prerequisites: Two virtual machine each of 2GB RAM, 10 GB HDD and Two vCPU with CentOS 7 installed on these. Workaround: Configure the networking between two nodes: Step1: Change the host name of the master node to master-node and make entries to the /etc/hosts file. # hostnamectl set-hostname master-node # echo ‘192.168.56.103 master-node’ >> /etc/hosts…
MariaDB Error: ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) Cause: The user who is executing the create function doesn’t have the required SUPER privilege. Solution: AS for security concern the user couldn’t be given the SUPER privilege…
Error: Experienced the below error during the import of the export dump file to the Oracle 12.1. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-39142: incompatible version number 5.1 in…
Applications generates temporary data which may need less than minutes to days, these data need to be purged to claim spaces on database and housekeeping. One way to do this is to create a partitioned table and drops the older partitions based on a regular interval. The below example shows how to do this by…
Range partitioning is a great way to manage historical data in oracle database, data to be inserted in a partitioned table should have the specific partition exists beforehand otherwise the insert operation will fail. How to create partition automatically when the data added to the table? Interval partitioning is used to implement this feature which…
Background: In enterprises most cases their database infrastructures or applications are built on different databases, sometimes data needs to be transferred or synced from one database to another heterogenous database. Oracle supports heterogenous connectivity to popular databases like MySQL, SQL Server, MariaDB, DB2, Sybase and more on the list. Here we will try to establish…
Error: Errors in file /u01/app/oracle/diag/rdbms/bpay/bpay/trace/bpay_j000_24499.trc: ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_586″ ORA-20001: Statistics Advisor: Invalid task name for the current user ORA-06512: at “SYS.DBMS_STATS”, line 47207 ORA-06512: at “SYS.DBMS_STATS_ADVISOR”, line 882 ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 20059 ORA-06512: at “SYS.DBMS_STATS_INTERNAL”, line 22201 ORA-06512: at “SYS.DBMS_STATS”, line 47197 Cause: It’s a known bug, the advisory…
Background: Clustering is used to achieve High Aaailability(HA) in many forms and is widely used in database technologies to meet the service uptime and to load balance clients requests. We will see how to configure a Opensource Galera cluster on MariaDB database. This documents assume that you are familiar with virtualization technologies especially VirtualBox, you…
Why partitioning: Enhances the performance, manageability, and availability. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces. When to partition a table: Tables larger than 2gb size. Historical data Contents must be stored in different storage Avoid index maintenance when data is removed Index maintenance on parts of data like rebuilding…
Steps to perform during migration: Install VPS with CentOS 7 without any LAMP stack. configure the x11 window and forwarding make a directory named as /bitnami to be used as installed directory Download the WordPress NGINX stack installer from bitnami. Login as root user, installation should be done as root user to avoid later complexities.…
Background: For the oracle errors like “ORA-12170: TNS:Connect timeout occurred”, “ORA-12535: TNS:operation timed out”, “ORA-12560: TNS:protocol adapter error”, “TNS-00505: Operation timed out” and like others related to oracle listener it is useful of tracing the listener activities to dig down further. Workaround: Connect to the listener control utility from the command line or shell. LSNRCTL…
Background: For the purpose of taking backup of the MS access database, VBA script failed for one of the tables of the source database, showing “Property not found Error: 3270“. Resolution: Compact and repairing didn’t solve the issue, recreated the table also didn’t help. Upon further dig down its been observed that removing the “Calculated”…
Background When you create a virtual machine in VirtualBox the virtual machine or OS needs internet or the virtual machine and your physical machine need to talk to each other or the virtual machine need to be reached from other computers from the network and vice-versa. These are done by VirtualBox networking which uses its…
Background Cloning of a virtual machine is the easiest way around to get the copy of the same operating system environment which saves time and effort of another installation and configuration of the same. Workaround Choose the virtual machine that should be cloned. Right click on the VM and select “Clone”. Provide the “Name”, “Path”…