Home Blog
Query is picking a bad execution plan , fix it using Oracle baselines ?
The goal of SQL plan baselines is to preserve the performance of corresponding SQL statements, regardless of changes in the database. Examples of changes...
LOG MINER in Oracle
Oracle LogMiner, a component of Oracle Database, provides a SQL interface for querying both live and archived redo log files. The history of activity on...
Manual creation of SQL Profiles in Oracle Database
SQL Profiles are a powerful tool to improve performance of SQL queries in your Oracle Database.
A SQL profile is a database object that contains auxiliary statistics...
Resizing redo logs and standby Redologs in Dataguard
IN PRIMARY DATABASE :-
1) Check Instance details :-
select status,instance_name,database_role from v$database,v$instance;
------------------------------------------------------------------------------------------------------------------------------------
2) Check redo log and standby redo log size in Primary and standby :-
Redo...
Crontab : Blocking session
Blocking sessions in Oracle are sessions which holds an exclusive lock on an object and doesn’t release it before another sessions wants to update...
How To Run SQL Tuning Advisor
When we run SQL tuning advisor against a SQL statement or sql_id, it provides tuning recommendations that can be used to improve performance. It might give suggestion to...
MEB Backup & Restore in MySQL
MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac &...
ORA-04021: timeout occurred while waiting to lock object while dropping Materialized View.
A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either...
Unable to generate Listener Log in Prod RAC DB
Cause: Looks like ADR is having the issue.
Solution: Added the below parameter in the both the nodes and restarted the listener
DIAG_ADR_ENABLE_LISTENER=OFF
There are 3 parameters...
Moving datafile from one location to another in Standby
$ ps -ef|grep pmon
oracle 2113 1 0 Sep20 ? ...
ORA-16047: DGID mismatch between destination setting and target database
Issue : ORA-16047: DGID mismatch between destination setting and target database
alert.log
alter database register logfile '/opt/data/oracle3/prod101/FRA/prod101/archivelog/2019_12_01/o1_mf_1_190698_gy5z61jw_.arc'
There are 1 logfiles specified.
ALTER DATABASE REGISTER LOGFILE
Completed: alter...
Apache Cassandra Performance Tuning
What is Apache Cassandra?
▪ NoSQL database
▪ Schema-free
▪ Very fast ‘write’
▪ Cassandra is designed to handle big data workloads across multiple nodes with no single...
Cassandra import & export
Cassandra.csv file data:
car_make,car_model,start_year,id,first_name,last_name,department
BMW,Saloon,2011,1,Johny,Depp,IT
AUDI,Saloon,2013,2,Meryl,Streep,HR
LEXUS,Sports,2011,3,Brad,Pitt,IT
AUDI,Sports,2012,4,Tom,Hanks,FI
BMW,Compact,2012,5,Angelina,Jolie,FI
BMW,Saloon,2011,6,Tom,Cruise,HR
AUDI,Compact,2013,7,Scarlett,Johansson,IT
LEXUS,Compact,2013,8,Matt,Damon,IT
NISSAN,Saloon,2013,9,Julia,Roberts,FI
BMW,Saloon,2011,10,Nicole,Kidman,HR
NISSAN,Compact,2012,11,George,Clooney,HR
AUDI,Saloon,2012,12,Dwayne,Johnson,IT
AUDI,Sports,2011,13,Natalie,Portman,FI
Create keyspace and create tables and then importing the data form local system to Cassandra:
$./bin/nodetool status
$./cqlsh 192.168.0.104
Cqlsh>CREATE KEYSPACE test_keyspace WITH replication =...
Protected: Cassandra Data Modelling
Why Database Modelling?
Database model and it’s methodologies
Database Modelling
Different types of Data Models
Database Modelling Steps
Benefits of Data Modelling
RDBMS and Cassandra Data ModeL
Cassandra Data Model
Data Modelling...
Linux Administration.
A Linux distribution is a collection of (usually open source) software on top of a Linux kernel. A distribution (or short, distro) can bundle...
MySQL HA DR Setup
/etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.10 mysql-prim.localdomain mysql-prim
192.168.56.11 mysql-dr.localdomain ...
Undo Tablespace
There are two types of Undo Modes in 12.2
1) Shared Undo - The Undo tablespace is common, it is shared among the entire CDB....
Renaming and Relocating Datafiles Online
Relocating an Online Data File
SQL> ALTER DATABASE MOVE DATAFILE '/rman_backup/backup/datafile1' TO '+DATA';
Database altered.
Copying an Online Data File
This example copies the data file to ...
Oracle Managed Files 11g & 12c
11g
When OMF is enabled and If a datafile location is incorrect the file gets created in dbs location
SQL> show parameter db_create_file_dest
NAME ...
Managing Tablespace in CDB and PDB
Managing tablespace in container database is similar to non-container database. Provided you are logged in as a privileged user and point to right container.
Manage...
Common user password change:
Common user password we can change from only cdb level .container class is optional.
CDB container:
====================================
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> alter user C##USERTEST identified by test container=all;
User...
Oracle-maintained users password change
From oracle 12c onwards the default user’s account passwords have to change from cdb container. container class is optional.
sys user password change
=======================
CDB level using...
Lockdown Profile:
From 12c onwards we have new feature called lockdown profile to control the PDB level activities. It can be created in CDB container.
SQL> show...
Common Roles Vs Local Roles
Privilege Management:
Privilege management has been classified into 2 sub divisions. They are:
1) Grants
2) Roles
Roles:
Collection of privileges called role.
In 12c we have 2 different...
Common Grant Vs Local Grant
Privilege Management:
Privilege management has been classified into 2 sub divisions. They are:
1) Grants
2) Roles
Grants:
oracle12c have two variant of grants.
They are:
1) Common grants
2) Local...