Satya's DBA Blog

AWS DynamoDB Shell - ddbsh

2025-09-01T13:31:00.000+05:30

DynamoDB Shell (ddbsh) commands in AWS

An open-source command line interface (CLI) and interactive shell to run SQL-like DDL and DML commands on Amazon DynamoDB (DDB). And these SQL commands will be automatically translated to DynamoDB queries. DynamoDB Shell is written in C++ and uses the DynamoDB API through the AWS DynamoDB SDK.

For installation and building the DynamoDB Shell tool, please refer to the project’s GitHub page.

https://github.com/awslabs/dynamodb-shell/blob/main/README.md

$ ddbsh

help update;

help;

HELP - provide help in ddbsh

HELP <keyword> [keyword [keyword ...]]

Provides help about the specified keyword, or statement.

HELP ALTER TABLE

HELP BACKUP

HELP BEGIN

HELP COMMIT

HELP CONNECT

HELP CREATE

HELP DELETE

HELP DESCRIBE BACKUP

HELP DESCRIBE

HELP DROP BACKUP

HELP DROP TABLE

HELP DROP

HELP EXPLAIN

HELP INSERT

HELP REPLACE

HELP RESTORE

HELP ROLLBACK

HELP SELECT

HELP SHOW BACKUPS

HELP SHOW CREATE TABLE

HELP SHOW LIMITS

HELP SHOW VERSION

HELP SHOW

HELP UPDATE

HELP UPSERT

connect us-east-2;

show version;

show limits;

show tables;

begin;

commit;

rollback;

describe dynamodb_tab;

show create table ddbsh_demo;

CREATE TABLE commands in DynamoDB shell

CREATE TABLE [IF NOT EXISTS][NOWAIT] <name> ( attribute_name, attribute_type [,...] )

primary_key billing_mode_and_throughput

[gsi_list] [lsi_list] [streams] [table_class] [tags] ;

create table ddbsh_demo (id number) primary key (id hash);

create table ddbsh_demo2 (pk number, rk number) primary key (pk hash, rk range);

create table ddb_tab ( a number, b number ) primary key ( a hash ) gsi ( gsione on (b hash) projecting all);

create table ddb_table ( id string, name string ) primary key ( id hash ) billing mode provisioned ( 5 rcu, 5 wcu ) gsi ( namegsi on (name hash) projecting all billing mode provisioned ( 5 rcu, 5 wcu ));

create table if not exists nowait ddbsh_demo3 (id string, accttype string, balance number) primary key (id hash, accttype range) billing mode provisioned (20 rcu, 20 wcu) gsi (balancegsi on (accttype hash, balance range) projecting all billing mode provisioned (20 rcu, 20 wcu)) stream (both images);

ALTER TABLE commands on DynamoDB tables using DDB shell

alter table dynamodb_tab set billing mode on demand;

alter table dynamodb_tab set billing mode provisioned (200 RCU, 300 WCU);

alter table dynamodb_tab set table class standard infrequent access;

alter table dynamodb_tab set table class standard;

alter table dynamodb_tab set stream (new image);

alter table dynamodb_tab set stream disabled;

alter table dynamodb_tab set pitr enabled;

alter table dynamodb_tab set pitr disabled;

alter table dynamodb_tab set deletion protection disabled;

alter table dynamodb_tab set deletion protection enabled;

alter table ddb_tab (x number, y number) create gsi xygsi on (x hash, y range) projecting all billing mode provisioned (20 RCU, 40 WCU);

alter table ddb_tab (x number, y number) create gsi xykeysgsi on (x hash, y range) projecting keys only billing mode provisioned (22 RCU, 41 WCU);

alter table ddb_tab (x number, y number, a number) create gsi xyagsi on (x hash) projecting include (y, a) billing mode provisioned (25 RCU, 50 WCU);

alter table ddb_tab set billing mode provisioned (20 rcu, 30 wcu) update gsi (pqgsi set billing mode provisioned (20 rcu, 30 wcu), xykeysgsi set billing mode provisioned (3 rcu, 5 wcu));

alter table ddb_tab drop gsi xygsi;

alter table ddbsh_demo add replica us-east-2;

alter table ddbsh_demo add replica us-west-2 table class standard infrequent access;

alter table ddbsh_demo drop replica us-east-2;

alter table ddbsh_demo set ttl (created_epoch_time);

alter table ddbsh_demo set ttl disabled;

If you want to add new column(s), there is NO command like alter table table-name add column column-name, you have to use INSERT or UPDATE or UPSERT commands with values for new columns (along with existing columns).

DROP TABLE commands on Amazon DynamoDB tables

drop table ddbsh_demo;

drop table if exists ddb_tab;

SELECT queries/commands on DynamoDB tables using DynamoDB shell

SELECT [CONSISTENT] attribute_list | * FROM <table>[.<index>] [WHERE where_clause] [return_clause | ratelimit] ]

select * from ddbsh_demo;

select * from ddbsh_demo where string_col = "available";

select * from ddbsh_demo where a = 5;

select id, a from ddbsh_demo where a > 3 and a < 5;

select * from ddbsh_demo where a between 2 and 5;

select * from ddbsh_demo where rangekey in (3, 9, 4, 7);

select consistent * from ddbsh_demo where attribute_exists(c) and a != 8;

select * from ddbsh_demo where attribute_exists(c) or a = 33;

select * from ddbsh_demo where attribute_exists(c);

select * from ddbsh_demo where not attribute_exists(amount);

select * from ddbsh_demo where attribute_type(b, string);

select * from ddbsh_demo where attribute_type(id, number) return total;

select * from ddbsh_demo where begins_with(b, "co");

select * from ddbsh_demo where contains(pk, "at");

select a, b, c from ddbsh_demo where attribute_type(b, string) or (a = 5 and c = "coffee");

select * from ddbsh_demo where size(id) < 5;

select * from ddbsh_demo where v.c = true;

select * from ddbsh_demo where v.b[1] = 11;

select * from ddbsh_demo.gsi1;

select * from ddb_tab.gsi2 where name = "Brutus";

select * from ddbsh_demo where a between 2 and 5 WITH RATELIMIT (5 RCU);

select * from ddbsh_demo where a between 2 and 5 WITH RATELIMIT (2 RCU, 2 WCU);

INSERTing records INTO DDB tables with DynamoDB shell

insert into ddbshell (custid, name) values (103, "Charlie");

insert into ddbshell (custid, name) values (101, "Alice"), (102, "Bob");

insert into ddbshell (pk, rk, x, y, z) values (4, "three", 11, 12, 13);

insert into ddbshell (id, v) values (3, 4), (4, "a string value"), (5, {a: 4, b: [10, 11, 12], c: true, d: {x: 10, y: 10}});

insert into ddb_tab (a, b) values ( 1, 2 ), (2, 3), (3, 4) with ratelimit ( 2 wcu );

REPLACE INTO <table> ( column [, column ...] ) VALUES ( values ) [ratelimit]

replace into ddbshell (pk, rk, ins) values (12, "nonexistant", "inserted");

UPDATE commands on Amazon DynamoDB tables using DDBShell

update ddbsh_demo set b = 15 where a = 5;

update ddb_tab age= 14 where name = "Brutus";

update ddbsh_demo set active = True where custid = 111;

update ddbsh_demo set newattr = 14 where pk = 1 and rk = "one";

update ddbsh_demo set newattr = if_not_exists(y, 3) + 6 where pk = 1;

update ddb_tab set balance = balance + 100 where id = "Satya" and accttype = "Savings";

update ddbsh_demo set z = 14, v.b[1] = 13 where id = 5;

update ddb_tab set updated = true with ratelimit ( 10 rcu, 5 wcu );

update ddbsh_demo remove c where a = 5; (works like alter table table-name drop column column-name)

UPSERT <name> SET <upsert_set> [where clause] [ratelimit]

upsert ddbsh_demo set op = "upsert" where pk = 2;

Deleting records from DynamoDB tables using DDB shell

delete from ddb_tabble where name = "Brutus";

delete from ddbsh_demo where pk = 1 and rk = "one";

Backup and Restore of AWS DynamoDB tables

backup table dynamodb_tab called dynamodb-table-backup;

show backups;

describe backup "arn:aws:dynamodb:us-east-2:1234567890:table/dynamodb_tab/backup/1681480720-2a11c134";

drop backup "arn:aws:dynamodb:us-west-2:1234567890:table/backup_test/ddbsh_demo/1681480720-2a11c134";

restore table "dynamodb_tab" from backup "arn:aws:dynamodb:us-east-2:1234567890:table/dynamodb_tab/backup/1681480720-2a11c134";

RESTORE TABLE new table name FROM old table name TO PITR "YYYY-MM-DDTHH:MM:SSZ";

Getting EXPLAIN plan for DynamoDB (shell) queries

explain select * from ddbsh_demo2;

explain select * from dynamodb_tab where attribute_type(b, string) or (a = 5 and c = "coffee");

explain create table ddbsh_demo (id string, name string) primary key (id hash) billing mode provisioned (5 rcu, 5 wcu) gsi (namegsi on (name hash) projecting all billing mode provisioned (5 rcu, 5 wcu));

explain alter table ddb_tab add replica us-west-2;

explain alter table ddbsh_demo (v number) create gsi gsi_v on (v hash) projecting all billing mode provisioned (10 rcu, 20 wcu);

explain update ddbsh_demo set z = 14, v.b[6] = 13 where id = 5;

explain update ddb_table.zipgsi set state = "CA" where zip = "90210" with ratelimit (10 rcu, 20 wcu);

explain delete from ddb_tab where rk = "two";

Related AWS Cloud Articles: AWS Cloud Database/DBA Interview Questions

Percona Xtrabackup Backup & Recovery

2025-07-12T19:18:00.000+05:30

Percona Xtrabackup (PXC Part 4)

XtraBackup is an open-source MySQL hot backup software program.

Features include hot, non-locking backups for InnoDB storage, incremental backups, streaming, parallel-compressed backups, throttling based on the number of I/O operations per second, etc.

Percona XtraBackup is an open-source hot backup utility for MySQL servers that doesn’t lock databases during the backup.

Part 1 of the article - Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration

Part 2 of the article - ProxySQL 2 configuration/installation

Part 3 of the article - Installation & Usage of Percona Toolkit

Part 5 of the article - PMM installation/configuration and dashboards

Percona Xtrabackup (8.0) installation from Repository

Percona provides repositories for yum (RPM packages for Red Hat, CentOS and Amazon Linux AMI) and apt (.deb packages for Ubuntu and Debian) for software such as Percona Server for MySQL, Percona XtraBackup, and Percona Toolkit.

yum list all | grep xtrabackup

dnf install percona-xtrabackup-80 -y

Percona XtraBackup can backup data from InnoDB, XtraDB, MyISAM tables on MySQL 8.0 servers as well as Percona Server for MySQL with XtraDB, Percona Server for MySQL 8.0, and Percona XtraDB Cluster 8.0.

Creating Backup

To create a backup, run xtrabackup with --backup option and specify --target-dir option where the backup will be stored.

xtrabackup --backup --target-dir=/data/backups/

xtrabackup --backup --user=satya --password=Secret9 --target-dir=/path/to/backupdir

xtrabackup --backup --user=satya --password=Secret9 --slave-info --target-dir=/path/to/backupdir

Preparing Backup

After making a backup with the --backup option, you need need to prepare it in order to restore it.

xtrabackup --prepare --target-dir=/data/backups/

xtrabackup --prepare --user=satya --password=Secret9 --target-dir=/path/to/backupdir

Restoring Backup

For convenience, xtrabackup binary has the --copy-back option to copy the backup to the datadir of the server. Backup needs to be prepared before it can be restored.

xtrabackup --copy-back --target-dir=/data/backups/

Incremental Backup

Percona xtrabackup supports incremental backups, which means that they can copy only the data that has changed since the last backup.

DBAs can perform many incremental backups between each full backup, to have a backup process such as a full backup once a week and an incremental backup every day, or full backups every day and incremental backups every hour.

To make an incremental backup, begin with a full backup as usual. The xtrabackup binary writes a file called xtrabackup_checkpoints into the backup’s target directory.

xtrabackup --backup --target-dir=/data/backups/full

xtrabackup --backup --target-dir=/root/backups/full -uroot --pSecret9

Now that you have a full backup, you can make an incremental backup based on it.

xtrabackup --backup --target-dir=/data/backups/incl --incremental-basedir=/data/backups/full

xtrabackup --backup --target-dir=/root/backups/incl --incremental-basedir=/root/backups/full -uroot --pSecret9

Preparing Incremental Backups

The --prepare step for incremental backups is not the same as for full backups. Beginning with the full backup, you can prepare it, and then apply the incremental differences to it.

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full

xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full -uroot --pSecret9

To apply the first incremental backup to the full backup. Preparing the second/third incremental backup is a similar process.

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/full --incremental-dir=/data/backups/incl

xtrabackup --prepare --apply-log-only --target-dir=/root/backups/full --incremental-dir=/root/backups/incl -uroot --pSecret9

Restoring Incremental Backups

Once prepared incremental backups are the same as the full backups and they can be restored in the same way.

.. shutdown database ..

xtrabackup --copy-back --target-dir=/root/backups/

xtrabackup --copy-back --target-dir=/data/backups/

Related MySQL Articles: Percona Monitoring and Management (PMM) How to use tools in Percona Toolkit

Installation & Usage of Percona Toolkit 3

2024-06-25T21:29:00.000+05:30

Percona Toolkit Usage and Installation (PXC Part 3)

Percona Toolkit is a collection of advanced command-line (CLI) tools that are created/used by Percona to help Percona support team or users to perform a variety of MongoDB and MySQL administration tasks that can be too difficult or complex to do manually.

Part 1 of the article - Percona XtraDB Cluster (PXC) 8.0 Installation/Configuration

Part 2 of the article - ProxySQL 2 configuration/installation

Part 4 of the article - Percona Xtrabackup Backup & Recovery

Part 5 of the article - Installation of Percona Monitoring and Management client/server

Installing Percona Toolkit (3.3)

Install Percona Toolkit using the corresponding package manager, on RHEL or CentOS:

yum list all | grep toolkit

dnf install percona-toolkit -y

If you want to download a specific tool, use http://www.percona.com/get

For example, to download the pt-slave-restart tool, run:

wget percona.com/get/pt-slave-restart

Using tools in Percona Toolkit

pt-summary # Summarize system information nicely

pt-summary

pt-summary > pt-summary.out

pt-mysql-summary # Summarize MySQL information nicely

pt-mysql-summary --user=root --password=secret

pt-mysql-summary --user=root --password=secret > "${PTDEST}/pt-mysql-summary.out"

pt-table-checksum # Checks whether master/replicas have consistent copy or not

pt-table-checksum -uroot --pSecret9

pt-table-checksum -uroot --pSecret9 h=localhost -d thirumani

pt-table-checksum -uroot --pSecret9 h=localhost -d thirumani --slave-user=replication --slave-password=Slave@123

pt-table-checksum -uroot --pSecret9 h=localhost -d thirumani --slave-user=replication --slave-password=Slave@123 --no-check-binlog-format

pt-table-checksum -uroot --pSecret9 --replicate testdb.sample h=master-host

pt-table-checksum -uroot --pSecret9 --replicate testdb.sample h=master-host -d sakila

pt-table-sync # Synchronize MySQL table data efficiently

pt-table-sync --execute h=localhost,D=thirumani,t=order_items_refund h=host2

pt-table-sync --execute h=localhost -uroot --pSecret9 --replicate thirumani.order_items_refund --slave-user=replication --slave-password=Slave@123

pt-table-sync --execute localhost host2 host3

pt-table-sync --execute --sync-to-master slave1

pt-table-sync --execute --replicate test.checksum master1

pt-table-sync --execute --replicate test.checksum --sync-to-master slave1

pt-table-sync --execute --sync-to-master h=master2,D=db,t=tbl

pt-table-sync -uroot --pSecret9 --replicate testdb.sample --print h=master-host

pt-slave-find # Find and print replication hierarchy tree of MySQL slaves

pt-slave-find --host localhost -uroot --pSecret9

pt-slave-delay # Make a MySQL slave server lag behind its master

pt-slave-delay --delay 1m --interval 15s --run-time 10m slavehost

pt-slave-delay --delay 2h h=slave

pt-slave-restart # Watch and restart MySQL replication after errors

pt-slave-restart [OPTIONS] [DSN]

pt-slave-restart

pt-slave-restart h=slave

pt-slave-restart --master-uuid

pt-heartbeat # Monitor MySQL replication delay

pt-heartbeat -h192.168.1.222 -uroot --pSecret9

pt-heartbeat -D percona --create-table --update h=master

pt-heartbeat -D test --update -h master-server --daemonize

pt-heartbeat -D test --monitor -h slave-server

pt-heartbeat -D test --check h=slave-server

pt-query-digest # Analyze MySQL queries from logs, processlist, and tcpdump

pt-query-digest master/data/slow.log

pt-query-digest --review h=host2 --no-report slow.log

pt-query-digest slow.log --no-report --output slowlog --filter '$event->{fingerprint} && make_checksum($event->{fingerprint}) eq "FDEA8D2993C9CAF3"'

pt-query-digest --create-review_table --review D=dbname,t=tablename slow.log

pt-query-digest --processlist h=localhost -uroot --pSecret9

pt-query-digest --processlist h=localhost -uroot --pSecret9 --print --no-report --interval=0.01 > slow.log

pt-query-digest --processlist h=localhost -uroot --pSecret9 --interval=0.01 --output=slowlog > slow.log

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt

pt-upgrade # Verify that query results are identical on different servers (before upgrade)

pt-upgrade h=host1 h=host2 slow.log

pt-upgrade h=host1 --save-results host1_results/ slow.log

pt-upgrade host1_results1/ h=host2

pt-upgrade h=host1 h=host2 queries.txt

pt-visual-explain # Format EXPLAIN output as a tree

pt-visual-explain <file_containing_explain_output>

pt-visual-explain -c <file_containing_query>

mysql -e "explain select * from mysql.user" | pt-visual-explain

pt-stalk # Collect forensic data about MySQL when problems occur

pt-stalk --function process list --variable Command --match Sleep --threshold 155 --cycles 0

pt-stalk --no-stalk --iterations=2 --sleep=30 -- --user=root --password=<mysql-root-pass>

pt-stalk --no-stalk --iterations=2 --sleep=30 --dest="${PTDEST}" -- --user=root --password=<mysql-root-pass>

pt-stalk --variable Threads_connected --cycles 2 -- --user satya

pt-find # Find MySQL tables and execute actions, like GNU find

pt-find --ctime +1 --engine MyISAM

pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM"

pt-find --connection-id '\D_\d+_(\d+)$' --server-id '\D_(\d+)_\d+$' --exec-plus "DROP TABLE %s"

pt-find --empty junk test --exec-plus "DROP TABLE %s"

pt-find --tablesize +5G

pt-find --printf "%T\t%D.%N\n" | sort -rn

pt-find --noquote --exec "INSERT INTO sysdata.tblsize(db, tbl, size) VALUES('%D', '%N', %T)"

====================================================

pt-align # Aligns output from other tools to columns

pt-archiver # Archive rows from a MySQL table into another table or a file

pt-archiver --source h=host,D=db,t=child --purge --where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'

pt-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server --file '/var/log/archive/%Y-%m-%d-%D.%t' --where "1=1" --limit 1000 --commit-each

pt-archiver --source h=127.0.0.1,D=sakila,t=payment --where 'rental_id > 1000' --limit 100 --commit-each --purge --dry-run

pt-archiver --source h=127.0.0.1,D=sakila,t=payment --where 'rental_id > 1000' --limit 100 --commit-each --purge --progress=100

pt-archiver --source h=127.0.0.1,D=sakila,t=payment --dest h=12.0.0.1,D=sakila_archive,t=payment --where 'rental_id > 1000' --limit 100 --commit-each

pt-config-diff # Diff MySQL configuration files and server variables

pt-config-diff h=host1 h=host2

pt-config-diff /etc/my.cnf h=host1

pt-config-diff /etc/my-small.cnf /etc/my-large.cnf

pt-deadlock-logger # Log MySQL deadlocks

pt-deadlock-logger h=host1

pt-deadlock-logger h=host1 --iterations 1

pt-deadlock-logger h=host1 --dest h=host2,D=percona_schema,t=deadlocks

pt-diskstats # An interactive I/O monitoring tool for GNU/Linux

pt-diskstats [OPTIONS] [FILES]

pt-diskstats

pt-duplicate-key-checker # Find duplicate indexes and foreign keys on MySQL tables

pt-duplicate-key-checker --host host1

pt-fifo-split # Split files and pipe lines to a fifo without really splitting

pt-fifo-split --lines 1000000 hugefile.txt

while [ -e /tmp/pt-fifo-split ]; do cat /tmp/pt-fifo-split; done

pt-fingerprint # Convert queries into fingerprints

pt-fingerprint --query "select a, b, c from users where id = 500"

pt-fingerprint /path/to/file.txt

pt-fk-error-logger # Log MySQL foreign key errors

pt-fk-error-logger h=host1

pt-fk-error-logger h=host1 --iterations 1

pt-fk-error-logger h=host1 --dest h=host2,D=percona_schema,t=fke

pt-index-usage # Read queries from a log and analyze how they use indexes

pt-index-usage /path/to/slow.log --host localhost

pt-index-usage slow.log --no-report --save-results-database percona

pt-ioprofile # Watch process IO and print a table of file and I/O activity

pt-ioprofile [OPTIONS] [FILE]

pt-ioprofile

pt-kill # Kill MySQL queries that match certain criteria

pt-kill --busy-time 60 --kill

pt-kill --busy-time 60 --print

pt-kill --match-command Sleep --kill --victims all --interval 10

pt-kill --match-state login --print --victims all

mysql -e "SHOW PROCESSLIST" > proclist.txt

pt-kill --test-matching proclist.txt --busy-time 60 --print

pt-mext # Look at many samples of MySQL SHOW GLOBAL STATUS side-by-side

pt-mext -r -- mysqladmin ext -i10 -c3

pt-mext -r -- cat mysqladmin-output.txt

pt-mext -r -- mysqladmin ext -i1 -c4

pt-mext -r -- cat 2021_05_01_12_55_30-mysqladmin > ptmext.out

pt-online-schema-change # ALTER tables without locking them

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

pt-online-schema-change --alter-foreign-keys-method auto --alter "add key actor_last_update(last_update)" --execute h=localhost,D=sakila,t=actor

pt-pmp # Aggregate GDB stack traces for a selected program, a poor man’s profiler

pt-pmp [OPTIONS] [FILES]

pt-secure-collect # collect, sanitize, pack and encrypt data

pt-secure-collect [<flags>] <command> [<args> ...]

pt-secure-collect collect <flags>

pt-show-grants #Canonicalize and print MySQL grants so you can effectively replicate, compare and version-control them

pt-show-grants

pt-show-grants --separate --revoke | diff othergrants.sql -

pt-sift # Browses files created by pt-stalk

pt-sift FILE|PREFIX|DIRECTORY

pt-table-usage # Analyze how queries use tables

pt-table-usage [OPTIONS] [FILES]

pt-variable-advisor # Analyze MySQL variables and advise on possible problems

pt-variable-advisor localhost

pt-variable-advisor --source-of-variables vars.txt

pt-mongodb-query-digest # reports query usage statistics by aggregating queries from MongoDB query profiler

pt-mongodb-query-digest [OPTIONS]

pt-mongodb-summary # collects information about a MongoDB cluster. It collects information from several sources to provide an overview of the cluster

pt-mongodb-summary [OPTIONS] [HOST[:PORT]]

pt-pg-summary # collects information about a PostgreSQL cluster

pt-pg-summary [options] [host:[port]]

pt-k8s-debug-collector # Collects debug data (logs, resource statuses etc.) from a k8s/OpenShift cluster. Data is packed into the cluster-dump.tar.gz archive in the current working directory

pt-k8s-debug-collector

Related Percona/MySQL Articles: Percona XtraDB Cluster (PXC) Configuration Percona Xtrabackup Full/Incremental Backup & Recovery

50 Top OGG (Oracle GoldenGate) Interview Questions

2024-02-10T20:23:00.000+05:30

Oracle Golden Gate (OGG) Interview Questions/answers Part 1

1. What is GoldenGate and how to set up Golden Gate?
Answer:

2. What are the processes/components in OGG (Oracle Golden Gate)?
Answer:
Manager, Extract, Replicat, Data Pump

3. What is the use of (Data) Pump process in Golden Gate (GG)?
Answer:

4. What is the command-line utility in Oracle GoldenGate (or) what is ggsci?
Answer:

5. What is the default port for Golden Gate Manager process?
Answer:
7809

6. What are the important files of Oracle Golden Gate (OGG)?
Answer:
GLOBALS, ggserr.log, dirprm, etc ...

7. What is a checkpoint table?
Answer:

8. How can you see Golden Gate errors?
Answer:
ggsci> VIEW GGSEVT
ggserr.log file

9. What is the difference between classical extract and integrated extract?

Answer:
Classic Capture/extract: (deprecated in OGG 21c)
* The Classic Capture mode is the traditional Extract process that accesses the database redo logs (optionally archive logs) to capture the DML changes occurring on the objects specified in the parameter files.
* At the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs.
* This capture mode is available for other RDBMS as well.
* There are some data types that are not supported in Classic Capture mode.
* Classic capture can’t read data from the compressed tables/tablespaces.

Integrated Capture (IC)/Extract:
* In the Integrated Capture/Extract mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
* IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
* This feature is only available for oracle databases in Version 11.2.0.3 or higher.
* It also supports various object types which were previously not supported by Classic Capture.
* This Capture mode supports extracting data from source databases using compression.
* Integrated Capture can be configured in an online or downstream mode.

10. What is the difference between classical replicat and integrated replicat?

Answer:

11. What is the difference between classical replicat and coordinated replicat?

Answer:

12. What is the use assumetargetdefs in replicat parameter file?
Answer:
Use the ASSUMETARGETDEFS parameter when the source and target objects specified in a MAP statement have identical column structure, such as when synchronizing a hot site. It directs Oracle GoldenGate to assume that the data definitions of the source and target objects are identical, and to refer to the target definitions when metadata is needed for the source data.

13. Can you configure multiple extracts to write to the same exttrail file?
Answer:
Only one Extract process can write to one exttrail at a time. So you can’t configure multiple extracts to write to the same exttrail.

14. What is the use of PASSTHRU and NOPASSTHRU parameters ?
Answer:
parameter passthru indicates the source and target database tables structure are identical. In pass-through mode (configured by PASSTHRU), the Extract process does not lookup table definitions from the database or from a data-definitions file. To use pass-through mode, the source and target table names and structures must be identical.

15. What is a Handle collision in OGG?
Answer:
The Golden Gate HANDLECOLLISIONS parameter is configured on the target database in the Replicat process to enable processing of the data when there are duplicate data integrity issues in the destination database.
Use the HANDLECOLLISIONS and NOHANDLECOLLISIONS parameters to control whether or not Replicat tries to resolve duplicate-record and missing-record errors when applying SQL on the target.

16. List important considerations for bi-directional (active-active setup) replication?
Answer:
The customer should consider the following points in an active-active replication environment.
* Primary Key: Helps to identify conflicts and resolve them.
* Sequences: Are not supported. The workaround is used to use odd/even, range or concatenate sequences.
* Triggers: These should be disabled or suppressed to avoid using uniqueness issue
* Data Looping: This can easily be avoided using OGG itself
* LAG: This should be minimized. If a customer says that there will not be any LAG due to network or huge load, then we don’t need to deploy CDR. But this is not the case always as there would be some LAG and these can cause Conflicts.
* CDR (Conflict Detection & Resolution): OGG has built-in CDRs for all kinds of DMLs that can be used to detect and resolve them.
* Packaged Application: These are not supported as they may contain data types that are not supported by OGG or it might not allow the application modification to work with OGG.

17. What is the difference between Discard and Ignore (in RESOLVECONFLICT) ?
Answer:

18. How you will check whether replicat is classical replicat or coordinated replicat or integrated replicat?

Answer:

19. How to find out the RBA of a Golden Gate (OGG) process ? [or] Which command will you use to find out the RBA of extract/replicat ?
Answer:

20. What is the heartbeat table in OGG ? Who will insert data into the heartbeat table ?
Answer:

21. How to check whether the table is in OGG replication or not ?
Answer:

22. What is trace table? What is the use of trace table?
Answer:

23. What is the use of initial load ?
Answer:

24. What are the different OGG Initial load methods available?

Answer:

25.

Answer:

OGG (Oracle Golden Gate) Interview Questions for experienced Part2

30 Top Oracle RMAN basic/advanced Interview Questions for DBAs

2023-10-01T20:23:00.000+05:30

Frequently asked Oracle DBA RMAN (Recovery Manager) Real-time Interview Questions

0. What is RMAN and why to use RMAN?

Answer :

1. Difference between catalog and nocatalog?
Answer :
Recovery catalog is central and can have information from many databases.

2. Difference between using the recovery catalog and the control file in Oracle?
Answer :
When a new incarnation happens, the old backup information in the control file will be lost. It will be preserved in the recovery catalog.
In the recovery catalog, we can store scripts.
Recovery catalog is central and can have information from many databases.

3. Can we use the same target database as catalog?
Answer :
No. The recovery catalog should not reside in the target database (database to be backed up), because the database can't be recovered in the mounted state.

4. As DBA, how do you know the progress of the Oracle RMAN task?
Answer :
By querying v$rman_status or v$session_longops

5. From where list and report commands will get input?
Answer :
Both the commands command querying v$ and recovery catalog (RC) views. V$BACKUP_FILES or many of the recovery catalog views such as RC_DATAFILE_COPY or RC_ARCHIVED_LOG.

6. What is the command to delete archive logs older than 7days?
Answer :
RMAN> delete archivelog all completed before sysdate-7;

7. By default how many days backup RMAN stores in Oracle Database?
Answer :

8. What is the use of crosscheck command in RMAN?
Answer :
Crosscheck will be useful to check whether the catalog information is intact with OS-level information.

9. What are the differences between crosscheck and validate commands?
Answer :
Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that the backup can be successfully restored if necessary.

Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.

10. Which is one is good, differential (incremental) backup or cumulative (incremental) backup?
Answer :
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0.
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0.

11. What are Level 0, Level 1 backups in Oracle?
Answer :
A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. A level 1 incremental backup can be either of the following types:
A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0.
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0.

12. Can we perform level 1 backup without level 0 backup?
Answer :
If no level 0 backup is available, then the behavior depends upon the compatibility mode setting.

If compatibility < 10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup.

If compatibility is >= 10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN.

13. Will RMAN put the database/tablespace/datafile in backup mode?
Answer :
Nope.

14. What is a snapshot control file in Oracle?
Answer :
The snapshot control file is a copy of a database control file created in an operating system-specific location by RMAN. RMAN creates the snapshot control file so that it has a consistent version of a control file to use when either resynchronizing the recovery catalog or backing up the control file.

15. What is the difference between a backup set and a backup piece?
Answer :
Backup set is logical and backup piece is physical.

16. RMAN command to backup for creating standby database?
Answer :
RMAN> duplicate target database to standby database ....

17. How to do cloning by using Oracle RMAN?
Answer :
RMAN> duplicate target database …

18. Suppose you lost one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week/day old and don’t have a backup of the (newly created) datafile. How do you restore/recover file?
Answer :
Create the datafile and recover that datafile.
SQL> alter database create datafile '…path...' size n;
RMAN> recover datafile file_id;

19. What is an obsolete backup and expired backup?
Answer :
A status of "expired" means that the backup piece or backup set is not found in the backup destination.
A status of "obsolete" means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.

20. What is the difference between a hot backup and RMAN backup?
Answer :
For hot backup, we have to put the database in begin backup mode, then take backup.
RMAN won’t put the database in backup mode.

21. How to put manual/user-managed backup in RMAN (recovery catalog)?
Answer :
By using catalog command.
RMAN> CATALOG START WITH '/tmp/backup.ctl';

22. What were the new features in Oracle 11g RMAN?
Answer :

23. What were the new features in Oracle 12c RMAN?
Answer :

24. What is the difference between the auxiliary channel and the maintenance channel?

Answer :

25. What is the use of nofilenamecheck in RMAN ?
Answer :
When NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the filenames during restoration. If the primary database and the standby database are on the same host, The DUPLICATE NOFILENAMECHECK option should not be used.

26. Do we need to shutdown the database to change the block change tracking file?
Answer :
No need

27. How will you identify a Physical Corruption/Logical corruption?
Answer :

28. What happens if you lose SPFILE and PFILE and DB crashed with some error, how will you start the database?

Answer :

29. What are the database file's that RMAN cannot backup ?
Answer :
RMAN cannot take backup of the pfile, redo logs, TNS or network configuration files, password files, external tables and the contents of the Oracle home files.

30. Can I take RMAN backup when the database is down ?
Answer :
No, we can take RMAN backup only when the target database is open or in the mount stage.

31. How to check RMAN configuration ?

Answer :

32. Can we have archive logs and datafile backup in a single backupset ?

Answer : No

33. Can we take RMAN backup when the target database is down ?

Answer : No

Frequently asked Oracle PL/SQL Interview Questions

2023-07-23T20:25:00.001+05:30

Oracle PLSQL Interview Questions/answers

1. What is the use of package/function/procedure?

Answer:

A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.
Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function.
Functions can be used in SQL query but procedure we can't use in SQL query.

2. What is the use of FORALL, BULK COLLECT in PL/SQL?
Answer:

3. What is PL/SQL table (now PL/SQL tables are called Associative Arrays)?

Answer:
PL/SQL table or associative array is a one-dimensional array.
An associative array is an unbounded set of key-value pairs. Each key is unique, and serves as the subscript of the element that holds the corresponding value.

4. How to compile procedure/package?

Answer:
alter procedure upd_int compile;
alter package SCOTT.LOCA_SER compile;

5. What are the differences between if and nullif?
Answer:

6. What is the maximum number of triggers, can apply to a single table?

Answer:
12 triggers

7. What are the differences between decode and case?
Answer:

8. What is pragma?

Answer:
Triggers can run under PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.
Pragma is a compiler directive in oracle.
Autonomous_Transaction is creating a new or independent session in the same transaction.
Autonomous Transaction provides functionality to the developer in which it allows to do changes in a separate transaction and to save/discard that particular transaction without affecting the main session transaction.

9. Want output in below format, salary in descending order and with serial numbers.

Answer:
Empno empname Sal S.No
123    Satya    12000 1
345    Rajesh    10000 2
796    Prasad     9000 3
986    Bhanu    8000 4

select t1.*, rownum from (select empno, empname, sal from emp order by sal desc)t1 order by rownum;

10. What is the difference between function and procedure?

Answer:

Functions return a single value, procedures are used to return multiple values, otherwise, it is generally similar to a function.
Functions can be used in SQL query but procedure we can't use in SQL query.
A Procedure that performs an action and a function that computes a value.

Package is a collection of related variables, cursor, procedure and functions. A package is a schema object which groups logically related PL/SQL types, items and subprograms.

11. What is mutating of a trigger?

Answer:
A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired.

12. What are cursor attributes?

Answer:
Cursor attributes are used to get information about the current status of the cursor.

Both explicit and implicit cursors have four attributes:
• FOUND – It Returns TRUE if the record was fetched successfully, FALSE otherwise
• NOTFOUND - Returns TRUE if the record was not fetched successfully, FALSE otherwise.
• ROWCOUNT - Returns the number of records fetched from the cursor at that point in time.
• ISOPEN - Returns TRUE if the cursor is open, FALSE otherwise.

13. What are the different types of triggers?
Answer:

14. Can trigger contain commit?

Answer:
No, regular triggers can not contain commit.
Any change that a trigger does is committed with the transaction that fired the trigger.
You will get the below error.
ORA-04092: cannot COMMIT in a trigger

Autonomous triggers (PRAGMA AUTONOMOUS_TRANSACTION) can contain COMMIT and ROLLBACK.

15. Can you call a procedure/function inside a trigger?

Answer:
Yes, you can fire a procedure/function from a Trigger.
But, keep in mind that trigger & procedure/function should not access the same table.

Related Oracle Articles: Oracle Exadata Interview Questions Oracle Data Guard Interview Questions/FAQs Oracle Export/Import - Data Pump Interview Questions/FAQs

Top Oracle Database 23c new features

2023-07-16T08:02:00.000+05:30

New Features in Oracle Database 23c

Oracle 23c - Long-Term Support release (succeeding 19c), with 5 years of Premier Support followed by 3 years of Extended Support.

23c Oracle Database released on April 2023.

Here are some new features of Oracle 23c, out of 300+ new features and enhancements.

We can use SELECT without FROM clause. No need to use the DUAL table.
At last, error messages are more verbose/clear, and will explain why a statement failed to execute.
Until Oracle Database 21c, we can have up to 1000 columns in a table. But 23c onwards, we can define 4096 columns in a table.
OKafka, for Oracle Database <=> Kafka integration.
JavaScript stored procedures. And In-Database JavaScript.
JSON Schema can be defined on a table column.
Real-time SQL plan management.
New developer dole – quickly provides a user with useful privileges for doing database development, Instead of granting connect, resource to the user.
Schema level privileges.
We can use column aliases (instead of entire expressions) in GROUP BY and HAVING clauses.
Sharding will be easier.
Able to define metadata for database objects.
Gradual password rollover.
Like in MySQL, we can keep new IF EXISTS and IF NOT EXIST clauses in DDL statements
Table Value Constructor - TVC (like in SQL Server) – allows grouping multiple rows of data in a single DML or SELECT statement.
Oracle 23c onwards, RETURN clause for MERGE statement and also returning OLD column value for UPDATE.
Oracle RAC on Kubernetes (K8s).

Related Oracle Articles: Oracle Database 21c new features Postgres 15 new features

80 Best Oracle DBA Interview Questions answers for experienced professionals Part1 [ 2023 ]

2023-07-01T20:29:00.000+05:30

Oracle DBA Interview Questions/FAQs Part1

1. What is an instance?
Answer :

Memory/SGA + background processes.

2. What is SGA?
Answer :

System/Shared Global Area.

3. What is PGA (or) what is pga_aggregate_target?
Answer :

Programmable Global Area.

4. What are the new memory parameters in Oracle 10g?
Answer :

SGA_TARGET PGA_TARGET

5. What are the new memory parameters in Oracle 11g?
Answer :

MEMORY_TARGET

6. What are the mandatory background processes?
Answer :

DBWR LGWR SMON PMON CKPT RECO.

7. What are the optional background processes in the Oracle database?
Answer :

ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx, etc.

8. What were the new background processes introduced in Oracle 10g?
Answer :

MMAN MMON MMNL CTWR ASMB RBAL ARBx

9. What were the new features in Oracle 9i?
Answer :

http://satya-dba.blogspot.com/2009/01/whats-new-in-9i.html

10. What were the new features in Oracle 10g?
Answer :

http://satya-dba.blogspot.com/2009/01/whats-new-in-10g.html

11. What were the new features in Oracle 11g?
Answer :

http://satya-dba.blogspot.com/2009/01/whats-new-in-11g.html

12. What were the new features in Oracle 11g R2 (release 2)?
Answer :

http://satya-dba.blogspot.com/2009/09/whats-new-in-11g-release-2.html

13. What were the new features in Oracle 12c (release 1)?
Answer :

http://satya-dba.blogspot.com/2012/10/new-features-in-oracle-database-12c.html

14. What process will get data from datafiles to DB cache (Database buffer cache)?
Answer :

Server process

15. Which background process will write data to datafiles?
Answer :

DBWR

16. Which background process will write undo data?
Answer :

DBWR

17. What are the physical components of the Oracle database?
Answer :

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.

18. What are the logical components of Oracle database?
Answer :

Blocks, Extents, Segments, Tablespaces.

19. What is segment space management?
Answer :

LMTS and DMTS.

20. What are the differences between LMTS and DMTS?

Answer :
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

Related Oracle Interview Articles: Oracle DBA Interview Questions/FAQs – Part2 Oracle DBA Interview Questions/FAQs – Part3 Oracle DBA Interview Questions/FAQs – Part4

30 Top Oracle DBA Performance Interview Questions with Answers [2023]

2023-07-01T18:07:00.000+05:30

Oracle Database Admin (Performance) Interview Questions for experienced - Part 1

1. What is direct path read?

Answer :
A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA. The direct path read is available only when the Oracle optimizer chooses a full table scan.

2. What are the index related things you will check in explain plan ?

Answer :
Index range scan
Index fast full scan
Index unique scan
BITMAP INDEX SINGLE VALUE

3. What is the cache buffer chains wait event in Oracle Database?

Answer :
A user process acquires the CACHE BUFFERS CHAINS latch to scan the System Global Area (SGA) for database cache buffers. Blocks in the buffer cache are placed on linked lists (cache buffer chains). Blocks are put on the hash chain according to their DBA (data block address) and CLASS of the block. Each hash chain is protected by a single child latch. The latch allows a process to scan a hash chain without having the linked list change while it scans.
Hot blocks are a common cause of cache buffers chains latch contention.

4. What are Scattered read and sequential read ?

Answer :
A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. This is most commonly used for single block reads. Single block reads are most commonly seen for index block access or table block access by a rowid.
A db file scattered read will read multiple data blocks. Multi-block reads are typically used on full table scans. The "scattered read" refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory.

5. What is the difference between Hash join and nested loop join?

Answer :
Hash join uses a hash-table based lookup mechanism while nested loop doesn't or

the hash join can use cursor work-area memory (allocated in UGA) for buffering rows, while nested loops join can not.

6. What is cursor sharing ?

Answer :
A cursor is a name to a specific private SQL area. The cursor contains session-specific state information such as bind variable values and result sets. you can think of a cursor as a pointer on the client side and as a state on the server side.
Exact means exactly use my cursor and don’t do any changes. In this state, Oracle searches different execution plans for every query.

Force means force to cursor sharing. In this state, the database engine replaces every literal with bind variables. One child cursor is used for every SQL.

Similar means use cursor sharing but take advantage of histograms. If no histogram exists for one of the replaced literals, similar is the same as force state and an already existing cursor will be used. If a histogram exists, a new child cursor is created.

7. What is the parent and child cursor in oracle?

Answer :
PARENT CURSOR stores the SQL text of the cursor. When two statements are identical textually, they will share the same parent Cursor.
The Child Cursor holds other required information like the identity of the objects referenced by the SQL Cursor; the names, type and length of the bind variables used. Child cursors are also called versions.

8. What is bind peeking?

Answer :
One of the first drawbacks of bind variables is the explain plan. Oracle optimizer is able to peek the value of the bind variable and generate a plan like if the query where with a fixed value and so using histograms and statistics.
Bind variable peeking is used with SQL statements in the library cache to see if the value of a host variable is going to change the existing execution plan.

9. What is adaptive cursor sharing ?

Answer :
Oracle 11g uses Adaptive Cursor Sharing to solve bind peeking problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

10. What is cardinality? What is high & low cardinality?
Answer :

11. What is the cardinality feedback init parameter ?
Answer :

12. What is dynamic_sampling?
Answer :

13. What are histograms in the database?

Answer :
Histograms are a feature of the cost-based optimizer (CBO) that allows the Oracle engine to determine how data is distributed within a column. They are most useful for a column that is included in the WHERE clause of SQL and the data distribution is skewed.
Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with non-uniform data distributions.

14. What are the hints? give some examples?
Answer :

15. What is a reverse key index ?

Answer :

16. What is enq: hw contention ?

Answer :
The enq: HW - contention occurs when competing processing is inserting into the same table and are trying to increase the high watermark of a table simultaneously. HW enqueues are acquired in Exclusive mode before updating HWM (High Water Mark) and essentially HW enqueues operate as a serializing mechanism for HWM updates.

17. What is Ora-4031?

Answer :
ORA-04031: unable to allocate string bytes of shared memory

ORA-04031 is an error message related to the lack of available SGA memory components.

While ORA-04030 is related to the lack of available memory in the PGA area.
* Heavy fragmentation of the shared pool - This can be fixed by increasing the shared_pool_size or doing a "alter system flush shared pool" or bouncing the instance.
* Too many pinned packages - If you have pinned lots of packages with dbms_shared_pool.keep, they have not left enough room for new work.

18. how to reduce index contention (en: TX - index contention) in oracle?

Answer :
The most probable reasons are:

Indexes on the tables which are being accessed heavily from the application.
Indexes on table columns which are monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
Large data purge has been performed, followed by high concurrent insert
Solutions:
Rebuild or shrink associated index after huge amount of data purge
Rebuild the index as reverse key indexes or hash partition the indexes which are listed in the 'Segments by Row Lock Waits' of the AWR reports
Increase PCT_FREE for the index
Consider increasing the CACHE size of the sequences

19. What is the difference between locks, latches, enqueues and semaphores?

Answer :
Semaphores: Inside Oracle database there are many processes or threads are running and for successful running they need to communicate with each other. The process through which they communicate with each other is known as semaphores. It is just like a signal which tells the thread that when to run and stop. There is a semaphore for every Oracle server process.

Locks and Latches: There are several processes inside Oracle SGA (System Global Area) that need to be accessed concurrently by many different database processes. And it is very important that only one process is able to modify a particular data structure at a time. Oracle does this with the help of Locks and Latches.

Latches: Latches are the more restrictive mechanism, because they do not allow multiple processes to inspect the protected data structure at the same time. Latches do not support queuing. Oracle uses latch for the data is accessed in detail.

Locks: Locks allow for better concurrency, because they may be held in a shared mode when the data structure is simply being inspected. Queuing is supported by Latch. Locks are used by Oracle for performance tuning.

Enqueues: Many of the Locks in Oracle are known as Enqueues locks. To enqueue a lock request is to place that request on the queue for its resource.

20. How to find out whether the query has run with RBO or CBO?
Answer :

Related Oracle Interview Articles: Oracle Database Admin Interview Questions/FAQs – Part1 Oracle DBA Interview Questions/FAQs – Part4

Best 90 Oracle DBA interview questions answers

2023-06-28T19:15:00.000+05:30

Oracle DBA interview questions answers - Part 5

81. What is extent management?
Answer :

Auto and Manual.

82. What will happen when we open the database in resetlogs ?

Answer :
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

83. As DBA, can you offline system tablespace?

Answer :
No, we can’t off-line system and undo tablespaces.

84. What is the use of set dbid?

Answer :
While connecting to recovery manager, where db_name is not unique, better to set dbid.
We must set dbid value, while recovering backup of spfile.

85. How to restore spfile without backup?
Take spfile or pfile backup from standby and restore it.
Use alert log to get non-default init parameters.
Use RMAN auto or manual spfile backups.

86. What will happen when a node crashes?

Answer :
* 1. One or more RAC instances fail.
* 3. Node failure is detected by any one of the remaining instances.
* 4. Global Resource Directory(GRD) is reconfigured and distributed among surviving nodes.
* 5. The instance which first detected the failed instance, reads the failed instances redo logs to determine the logs which are needed to be recovered. The above task is done by the SMON process of the instance that detected failure.
* 6. Until this time database activity is frozen, The SMON issues recovery requests for all the blocks that are needed for recovery. Once all the blocks are available, the other blocks which are not needed for recovery are available for normal processing.
* 7. Oracle performs roll forward operation against the blocks that were modified by the failed instance but were not written to disk using redo log recorded transactions.
* 8. Once redo logs are applied, uncommitted transactions are rolled back using undo tablespace.
* 9. Database on the RAC in now fully available.

87. What ACID properties ?

Answer :

88. What is CAP Theorem ?

Answer :

89. What is normalization and in which normal form do you want to keep your database ?

Answer :

90.

Related Oracle Articles: Datawarehouse Interview Questions Oracle DBA Interview Questions – Part 4

Top Oracle DBA Export/Import - Data Pump Interview Questions and Answers - 2023

2023-06-04T20:10:00.000+05:30

Essential Oracle Export/Import (exp/imp) - Data Pump (expdp/impdp) Interview Questions/FAQs for experienced

1. What is the use of CONSISTENT option in exp?

Answer:
Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2. What is the use of DIRECT=Y option in exp?

Answer:
Setting direct=yes, to extract data by reading the data directly, bypasses the SGA, bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.

3. What is the use of COMPRESS option in exp?

Answer:
Imports to one extent. Specifies how export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y. If the table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have an initial of 20M. Later on, when importing the extents will be coalesced. Sometimes it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on the disk (tablespace), and do not want imports to fail.

4. How to improve exp performance?

Answer:
1. Set the BUFFER parameter to a high value. Default is 256KB.
2. Stop unnecessary applications to free the resources.
3. If you are running multiple sessions, make sure they write to different disks.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).

5. How to improve imp performance?

Answer:
1. Place the file to be imported in a separate disk from datafiles.
2. Increase the DB_CACHE_SIZE.
3. Set LOG_BUFFER to big size.
4. Stop redolog archiving, if possible.
5. Use COMMIT=n, if possible.
6. Set the BUFFER parameter to a high value. Default is 256KB.
7. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
8. Use STATISTICS=NONE
9. Disable the INSERT triggers, as they fire during import.
10. Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

6. What is the use of INDEXFILE option in imp?

Answer:
Will write DDLs of the objects in the dump file into the specified file.

7. What is the use of IGNORE option in imp?

Answer:
Will ignore the errors during import and will continue the import.

8. What are the differences between expdp and exp (Data Pump or normal exp/imp)?

Answer:
Data Pump is server centric (files will be at the server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if the user doesn’t exist.

9. Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Answer:
Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.

10. How to improve expdp performance?

Answer:
Using parallel option which increases worker threads. This should be set based on the number of CPUs.

11. How to improve impdp performance?

Answer:
Using parallel option which increases worker threads. This should be set based on the number of CPUs.

12. In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Answer:
Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has been completed and from where to continue etc.
Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

13. What is the order of importing objects in impdp?

Answer:
Tablespaces
Users
Roles
Database links
Sequences
Directories
Synonyms
Types
Tables/Partitions
Views
Comments
Packages/Procedures/Functions
Materialized views

14. How to import only metadata?

Answer:
CONTENT= METADATA_ONLY

15. How to import into different user/tablespace/datafile/table?

Answer:
REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

16. How to export/import without using an external directory?
Answer:

17. Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?
Answer:

18. Using normal exp/imp, how to export in higher version (11g) and import into lower version (10g/9i)?
Answer:

19. How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?
Answer:

20.

Related Oracle interview questions: Oracle ASM Interview Questions/FAQs Oracle RMAN Interview Questions/FAQs Oracle DBA Interview Questions/FAQs – Part1

50 Essential MySQL/MariaDB DBA interview questions answers

2023-06-04T10:51:00.000+05:30

MySQL/MariaDB DBA Interview Questions and Answers - Part 3

Q41. How do you monitor and identify slow queries in MySQL?

answer:

You can enable the slow query log in MySQL and set a threshold for query execution time using the long_query_time variable. Slow queries exceeding the threshold will be logged, allowing you to analyze and optimize them.

Q42. How do you enable slow query logging in MySQL?

answer:

To enable slow query logging, you need to set the slow_query_log variable to ON and configure the long_query_time variable to specify the threshold for query execution time.

Q43. How do you enable query caching in MySQL?

answer:

To enable query caching, you need to set the query_cache_type variable to a non-zero value (e.g., ON), and configure the query_cache_size variable to allocate memory for caching query results.

Q44. How do you import data from a CSV file into a MySQL table?

answer:

You can use the LOAD DATA INFILE statement to import data from a CSV file into a MySQL table. The statement allows you to specify the file path, field delimiters, and column mappings.

Q45. What is the purpose of the EXPLAIN statement in MySQL?

answer:

The EXPLAIN statement is used to analyze and optimize the execution plan of a query. It provides information about how MySQL executes a query, the order of table access, the indexes used, and estimated row counts.

Q46. What is the difference between MyISAM and InnoDB?

answer:

MyISAM is a non-transactional storage engine with high read performance and is suitable for read-heavy workloads. InnoDB is a transactional storage engine with support for ACID (Atomicity, Consistency, Isolation, Durability) properties, providing better data integrity and concurrency control.

Q47. How do you perform point-in-time recovery in MySQL?

answer:

To perform point-in-time recovery, you need to restore a backup taken before the desired point in time and then apply binary logs containing changes made after the backup. This process brings the database to a specific state as of the desired point in time.

Q48. What is the purpose of the --skip-lock-tables option in mysqldump?

answer:

The --skip-lock-tables option in mysqldump allows you to perform a logical backup of the database without acquiring read locks on the tables. It enables you to backup a database while it is still being actively used, avoiding interruptions.

Q49. What is the purpose of the REPAIR TABLE statement in MySQL?

answer:

The REPAIR TABLE statement is used to repair corrupted or damaged MyISAM tables. It automatically detects and fixes common table issues, such as incorrect links, missing rows, or index problems.

Q50. What is the purpose of the mysqlbinlog utility?

answer:

The mysqlbinlog utility is used to read and display the contents of binary log files. It allows you to view the logged changes, including SQL statements and events, facilitating analysis, troubleshooting, and replication tasks.

Oracle SQLcl (SQL Developer command line) commands

2023-05-20T20:11:00.000+05:30

SQLcl - Oracle SQL Developer command line - commands

New free command line interface, by Oracle SQL Developer Team, for Oracle Database. And advanced version of SQL* Plus utility. SQLcl also introduces new commands and features which are missing from SQL*Plus.

sqlcl first released in 2016, and shipped by default with Oracle Database 12c Release 2.

sqlcl is not having any prerequisites, except Java (JDK/JRE). Installation is super easy. Just download sqlcl tool zip file (e.g. sqlcl-19.2.1.206.1649.zip) from oracle website and copy that tool to the Oracle database server and unzip.

SQLcl supports connections via EZConnect, TNS, LDAP, TWO_TASK, and more, and all without an Oracle client installed or configured.

Below are advantages of SQLcl (over SQL * PLUS):

TAB for auto completion of object names (like in any IDE)
Scrollback works, that is, UP ARROW & DOWN ARROW will work
Provides in-line editing, can navigate/edit any line in SQL prompt/buffer
HISTORY of commands/scripts (including previous sessions)
Can have aliases for frequently used commands
Automatic formatting (no need to setup so many COLUMN FORMAT commands)
Database Administrators (DBAs) can get DDL easily
Can generate SQL query output in CSV, HTML, XML, JSON formats
Will display UTF characters
CTAS command would automatically generate the 'create table as ...' command
Supports SQL * Plus commands

SQLcl is also known as SDSQL (SQLDeveloper SQL), New SQL*Plus tool and SQL*Plus++

Best features of SQLCL

We can easily get SQL output in CSV format, with sqlcl (NO need to set SQL*Plus variables and spool it to CSV file).
SQLcl > SET SQLFORMAT CSV
SQLcl > SELECT * FROM emp WHERE deptno = 20;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,17-DEC-80,800,,20
7566,"JONES","MANAGER",7839,02-APR-81,2975,,20
7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20
7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20
7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20

Below sqlcl command also do the same.
SELECT /*csv*/ * FROM emp;

SQLcl help us to get INSERT statements so easily, for any record in database.
SQLcl > SET SQLFORMAT INSERT
SQLcl > SELECT * FROM emp WHERE deptno = 20;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH24.MI.SSXFF'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH24.MI.SSXFF'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH24.MI.SSXFF'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);

Below command in sqlcl prompt will do the same.
SELECT /*insert*/ * FROM emp;

SQLcl also handles login scripts (login.sql), like SQL Plus. If you would like to setup some SQLcl configuration/formatting parameters permanently, the easiest way is to create a file called login.sql in current directory, then call SQLcl from that directory.
$ cat login.sql
set lines 333 pages 44444 time on
col db_name new_value db_name
select name db_name from v$database;
SET SQLFORMAT ansiconsole
set sqlprompt "@|blue _USER|@@@|green &db_name|@@|red >|@ "

SQLCL [ [option] [logon | / NOLOG] [start] ]
SQLCL [ -H[ELP] | -V[ERSION] | [ [-C[OMPATIBILITY] x.y[.z]]] [-L[OGON]] [-NOLOGINTIME] [-R[ESTRICT] {1 | 2 | 3}] [-S[ILENT]] [-AC]] [ {username[/password] [@connect_identifier] | /} [AS {SYSASM |SYSBACKUP |SYSDBA |SYSDG |SYSOPER |SYSRAC |SYSKM}] [edition=value] | / NOLOG] [ [start] ]
SQLCL -help
SQLCL -h
SQLCL -version

HELP | ? [topic]
help
help alias

DBA@DEVDB> help
For help on a topic type help topic
List of Help topics available:

/ @ @@ ACCEPT ALIAS APEX APPEND ARCHIVE_LOG BRIDGE BTITLE CD CHANGE CLEAR COLUMN COMPUTE CONNECT COPY DDL DEFINE DEL DESCRIBE DISCONNECT EDIT EXECUTE EXIT FIND GET HISTORY HOST INFORMATION INPUT LIST LOAD NET OERR PAUSE PRINT PROMPT QUIT REMARK REPEAT RESERVED_WORDS REST RUN SCRIPT SET SETERRORL SHOW SHUTDOWN SODA SPOOL SSHTUNNEL START STORE TIMING TNSPING TTITLE UNDEFINE VARIABLE VAULT WHENEVER WHICH

@{url | file_name[.ext]} [arg ...]
@@ { url | file_name[.ext] } [arg ...]

/ (slash)
Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
Reads a line of input and stores it in a given substitution variable.

ALIAS [name=SQL statement;| LOAD [filename]|SAVE [filename] | LIST [NAME] | DROP name | DESC name Description String]
Alias is a command which used to save a SQL, PL/SQL or SQL*Plus script/command and assign it a shortcut command.
SQLcl > alias
SQLcl > alias future=select sysdate + :days from dual;
SQLcl > alias list
SQLcl > alias list future
SQLcl > future 12
SQLcl > ALIAS dl=select * from dual;
SQLcl > alias active_users=select sid,serial#,username from v$session where status='ACTIVE' and username is not null;

APEX [export application_id]
Lists Application Express Applications.

A[PPEND] text
Adds specified text to the end of the current line in the SQL buffer.

ARCHIVE LOG LIST
Displays information about archive/redo log files.

BRE[AK] [ON report_element [action [action]]] ...
Specifies where changes occur in a report and the formatting action to perform
BRE[AK] [ON {column | expression | ROW | REPORT} [action [[SKI[P] n | [ SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]] ]]] ...

BRIDGE
Works like a database link to move between two connections/schemas.
BRIDGE department_new as "jdbc:oracle:thin:SYSTEM/ORACLE@172.33.242.33:1540/employees"(select * from department_new);
BRIDGE INSERT INTO department_new as "jdbc:oracle:thin:SYSTEM/ORACLE@172.33.242.33:1540/employees"(select * from department_new);

BTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
BTI[TLE] [ [ BOLD| CE[NTER]| COL n| FORMAT text| LE[FT]| R[IGHT] | S[KIP] [n] TAB ] | [text | variable] ...] | [ON | OFF]
Places and formats a title at the bottom of each report page, or lists the current BTITLE definition.

CD [directory]
Change directory at OS (Operation System) level
cd /home/oracle/satya/sql/

C[HANGE] sepchar old [sepchar [new [sepchar]]]
Changes first occurrence of old on the current line of the SQL buffer.

CL[EAR] option ...
CL[EAR] [ BRE[AKS]| BUFF[ER]| COL[UMNS]| COMP[UTES]| CONTEXT| SCR[EEN]| SQL| TIMI[NG] ]
Resets or erases the current value or setting for the specified option.
clear screen
cl scr
clear sql
clear columns

COL[UMN] [{column | expr} [option ...]]
Specifies display attributes for a given column.
col table_name format a30

COMP[UTE] [function [LAB[EL] text] ... OF {expr | column | alias} ...ON {expr | column | alias | REPORT | ROW} ...]
calculates and prints summary lines using various standard computations.

CONN[ECT] [{logon| / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
CONN[ECT] [{ username[/password] [@connect_identifier] | / |proxy} [AS {SYSOPER | SYSDBA | SYSASM}] [edition=value]]
Connects a given username to the Oracle Database.

connect ogg_owner/goldengate@proddb

CTAS table new_table
Used to get DDL to create new table from existing/mentioned table name.
CTAS - Create Table AS
help ctas
ctas orders orders_backup

COPY {FROM database | TO database | FROM database TO database} {APPEND | CREATE | INSERT | REPLACE | APPEND_BYTE | CREATE_BYTE | REPLACE_BYTE} destination_table[(column, column, column, ...)] USING query
Copies data from a query to a table in the same or another database.

DDL [object_name [type] [SAVE filename]]
Generates the code to reconstruct the object listed.

help set ddl
set ddl
set ddl comment off
set ddl constraints on
set ddl storage off

show ddl

help DDL
ddl hr.employees
ddl emp
DDL TEST100 SAVE TEST.LOG

DEF[INE] [variable] | [variable = text]
Specifies a substitution variable and assigns a CHAR value to it, or lists the value and variable type of a single variable or all variables.

DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Deletes one or more lines of the SQL buffer.

DESC[RIBE] { [schema.]object[@connect_identifier] }
Lists the column definitions for a table, view or synonym, or the specifications for a function or procedure.
describe orders

DISC[ONNECT]
Commits pending changes to the database and logs the current user out of Oracle (but will not exit SQLcl)
disconnect;

ED[IT] [file_name[.ext]]
Invokes an OS (operating system) text editor on the contents of the specified file or SQL buffer.
ed

EXEC[UTE] statement
Executes a single PL/SQL statement or runs a stored procedure.

{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.
exit;

FIND [filename]
Searches the SQLPATH and its directories for the specified file name.

FORMAT [BUFFER | RULES filename | FILE input_file output_file]
Formats the script in the SQLcl Buffer
FORMAT BUFFER
FORMAT RULES filename
FORMAT FILE input_file output_file

GET [FILE] file_name[.ext] [LIST | NOLIST]
Loads a SQL statement or PL/SQL block from a file into the SQL buffer.

HISTORY HISTORY [index | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?]
To show previously ran SQLcl commands
SQLcl retains a history of the last 100 commands that were used (including previous sessions).
The USAGE option keeps a count of the number of times a particular command has been executed.
The TIME option shows the total execution time for the statement. This will help DBAs to find out how much time the query took yesterday/last week.
SQLcl > set history
SQLcl > set history limit 500
SQLcl > set history on
SQLcl > set history off
SQLcl > set history clear
SQLcl > SET HISTORY BLACKLIST

SQLcl > history
SQLcl > history 6
SQLcl > history full
SQLcl > his usage
SQLcl > history script
SQLcl > history time
SQLcl > his clear
SQLcl > history fails

SQLcl > set nohistory
SQLcl > set nohistory SET, SHOW, HISTORY, CLEAR

SQLcl > show nohistory

HO[ST] [command]
Executes an operating system command without leaving SQLcl.
host ls -tlr
! pwd

INFO[RMATION] { [schema.]object[@connect_identifier] }
Lists more detailed information about the column definitions for a table, view or synonym, or the specifications for a function or procedure.
info hr.employees
info+ hr.employees
information test_emp

I[NPUT] [text]
Adds one or more new lines of text after the current line in the SQL buffer.

L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Lists one or more lines of the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

LOAD [schema.]table_name[@db_link] file_name
Loads a comma separated value (csv) file into a table.

OERR facility error
Displays information about Oracle errors.

PASSW[ORD] [username]
Allows us to change a password without displaying it on an input device.

PAU[SE] [text]
Displays the specified text then waits for the user to press RETURN.

PRINT [variable ...]
Displays the current values of bind variables, or lists all bind variables.

PRO[MPT] [text]
Sends the specified message or a blank line to the user's screen.

{QUIT | EXIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Commits or rolls back all pending changes, logs out of Oracle, terminates SQLcl and returns control to the operating system.
quit;

REM[ARK]
Begins a comment in a script.

REPEAT iterations sleep
Repeats the current SQL in the buffer at the specified times with sleep intervals.
repeat [number-of-repeats-max-2Billion] [sleep-duration-max-120seconds]
repeat help
repeat 10 1

REST [export [module_name | module_prefix] | modules | privileges | schemas]
REST
Used to export Oracle REST Data Services 3.x services.
REST export module_name
REST modules
REST privileges
REST schemas

R[UN]
Lists and executes the most recently executed SQLcl command or PL/SQL block which is stored in the SQL buffer.

SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves the contents of the SQL buffer in a script.

SCRIPT script file
Runs the SQLcl statements in the specified script.

SET system_variable value
Sets a system variable to alter the SQLcl environment settings for current session.
set pagesize 88

SET SQLFORMAT
Outputs reports in various formats.
help set sqlformat
SET SQLFORMAT DEFAULT
SET SQLFORMAT ansiconsole
SET SQLFORMAT INSERT
SET SQLFORMAT CSV
SET SQLFORMAT html
SET SQLFORMAT xml
SET SQLFORMAT json
SET SQLFORMAT fixed
SET SQLFORMAT loader
SET SQLFORMAT delimited
SET SQLFORMAT delimited |^| ' '
SET SQLFORMAT DELIMITED ~del~ " "
show sqlformat

Below commands also works.
SELECT /*csv*/ * FROM emp;
SELECT /*html*/ * FROM emp;
SELECT /*xml*/ * FROM emp;
SELECT /*json*/ * FROM emp;
SELECT /*ansiconsole*/ * FROM emp;
SELECT /*insert*/ * FROM emp;
SELECT /*loader*/ * FROM emp;
SELECT /*fixed*/ * FROM emp;
SELECT /*delimited*/ * FROM emp;
SELECT /*text*/ * FROM emp;

System Variables and Environment Settings through the SET Command
• autoprint
• autorecovery
• cmdsep
• copytypecheck
• describe
• eschar
• flagger
• flush
• fullcolname
• logsource
• loboffset
• markup
• recsep
• recsepchar
• shiftinout
• sqlcase
• sqlprefix
• sqlterminator
• tab
• underline
• xmloptimizationcheck

SET APPI[NFO]{ON | OFF | text}
SET ARRAY[SIZE] {15 | n}
SET AUTO[COMMIT] {ON | OFF | IMM[EDIATE] | n}
SET AUTOP[RINT] {ON | OFF}
SET AUTORECOVERY {ON | OFF]
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]}
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
SET CLEAR [ TOP | BOTTOM | SAME ]
SET CMDS[EP] {; | c | ON | OFF}
SET COLSEP {_ | text}
SET CON[CAT] {. | c | ON | OFF}
SET COPYC[OMMIT] {0 | n}
SET COPYTYPECHECK {ON | OFF}
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS | CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING | SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION | BODY | FORCE | INSERT | |INHERIT | RESET] {on|off} ] | OFF ]
SET DEF[INE] {& | c | ON | OFF}
SET ECHO {ON | OFF}
SET EDITF[ILE] file_name[.ext]
SET EMB[EDDED] {ON | OFF}
SET ENCODING
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
SET ESC[APE] {\ | c | ON | OFF}
SET ESCCHAR {@ | ? | % | $ | OFF}
SET EXITC[OMMIT] {ON | OFF}
SET [EXP[LAIN]] [STAT[ISTICS]]
SET FEED[BACK] {6 | n | ON | OFF}
SET FLU[SH] {ON | OFF}
SET HEA[DING] {ON | OFF}
SET HEADS[EP] { | | c | ON | OFF}
SET INSTANCE [instance_path | LOCAL]
SET LDAPCON
SET LIN[ESIZE] {80 | n}
SET LOBOF[FSET] {n | 1}
SET LOGSOURCE [pathname]
SET LONG {80 | n}
SET LONGC[HUNKSIZE] {80 | n}
SET NET {ON | OFF | READONLY}
SET NEWP[AGE] {1 | n | NONE}
SET NOVERWRITE {ON | OFF | WARN}
SET NULL text
SET NUMF[ORMAT] format
SET NUM[WIDTH] {10 | n}
SET PAGES[IZE] {14 | n}
SET PAU[SE] {ON | OFF | text}
SET RECSEP {WR[APPED] | EA[CH] | OFF}
SET RECSEPCHAR { | c}
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]
SET SHIFT[INOUT] {VIS[IBLE] | INV[ISIBLE]}
SET SHOW[MODE] {ON | OFF}
SET SQLBL[ANKLINES] {ON | OFF}
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
SET SQLCO[NTINUE] { | text}
SET SQLFORMAT {csv | html | xml | json | ansiconsole | insert | loader | fixed | default}
SET SQLN[UMBER] {ON | OFF}
SET SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
SET SQLPRE[FIX] {# | c}
SET SQLP[ROMPT] {SQL | text}
SET SQLT[ERMINATOR] {; | c | ON | OFF}
SET SUF[FIX] {SQL | text}
SET TAB {ON | OFF}
SET TERM[OUT] {ON | OFF}
SET TI[ME] {ON | OFF}
SET TIMI[NG] {ON | OFF}
SET TRIM[OUT] {ON | OFF}
SET TRIMS[POOL] {ON | OFF}
SET UND[ERLINE] {- | c | ON | OFF}
SET VER[IFY] {ON | OFF}
SET WRA[P] {ON | OFF}
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS | CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING | SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION | BODY | FORCE | INSERT | |INHERIT | RESET] {on|off} ] | OFF ]

SHO[W] [option]
Shows the value of a SQLcl system variable, or the current SQLcl environment.
where option represents one of the following terms or clauses:
system_variable
ALL
BTI[TLE]
CON_ID - Show the ID of the database connected
CON_NAME - Show the current database connected
CONNECTION - Show the current connection details
EDITION - Show the current enabled edition
ENCODING - Show the encoding which is set for the client
ENCODINGS - Show the available encodings for the client
ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
INSTANCE - Show the instance of the database that the client is connected to
JAVA - Java JRE properties, including, java location, version and platform
JDBC - Connection details including versions (of driver and database) and URL
LNO
NLS - Show NLS parameters set for the current session
PARAMETERS [parameter_name]
PDBS
PNO
RECYC[LEBIN] [original_name]
REL[EASE]
REPF[OOTER]
REPH[EADER]
SGA
SPOO[L]
SPPARAMETERS [parameter_name]
SQLCODE
TNS - Location of tnsnames.ora and list of aliases
TTI[TLE]
USER
VERSION - Show the version of SQLcl
show all
show all+
show version
show connection
show tns
show no failures
show failures
SHOW ENCODING
SHOW ENCODINGS
show pdbs

SHUTDOWN [ABORT | IMMEDIATE | NORMAL | TRANSACTIONAL [LOCAL]]
Shuts down a currently running Oracle instance, optionally closing and dismounting a database.
shutdown immediate;

SODA
SODA allows schemaless application development using the JSON data model.
SODA create collection_name
SODA list
SODA get collection_name [-all | -f | -k | -klist] [{key | k1 k2 ... | qbe}]
SODA insert collection_name json_str | filename
SODA drop collection_name
SODA count collection_name [qbe]
SODA replace collection_name oldkey new_{str | doc}
SODA remove collection_name [-k | -klist | -f] {key | k1 k2 ...| qbe}

SPO[OL] [filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Stores query results in a file, or optionally sends the file to a printer.

SSHTUNNEL username@hostname -i identity_file [-L localPort:Remotehost:RemotePort]
Creates a tunnel using standard ssh options.

STA[RT] { url | file_name[.ext] } [arg ...]
Runs the SQLcl statements in the specified script.

STARTUP db_options | cdb_options | upgrade_options
STARTUP [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] | [ OPEN [ READ {ONLY | WRITE [RECOVER]} | RECOVER ] [dbname] ] | NOMOUNT ]
| PLUGGABLE DATABASE pdbname [FORCE] | [RESTRICT] [ OPEN {open_pdb_options}] | [FORCE] | [RESTRICT] [ OPEN { READ WRITE | READ ONLY }]
| [PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
Starts an Oracle Database instance with several options, including mounting and opening a database.

startup;

STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves attributes of the current SQLcl environment in a file.

TIMI[NG]
Timing is only available as a switch

TNSPING address
determines whether the listener for a service on an Oracle Net network can be reached successfully.
tnsping devdb

TTI[TLE] [printspec [text | variable] ...] | [ON | OFF]
TTI[TLE] [ [ BOLD| CE[NTER]| COL n| FORMAT text| LE[FT]| R[IGHT] | S[KIP] [n] TAB ] [text | variable] ...] | [ON | OFF]
Places and formats a specified title at the top of each report page, or lists the current TTITLE definition.

UNDEF[INE] variable ...
Deletes one or more substitution variables that defined either explicitly (with the DEFINE command) or implicitly (with a START command argument).

VERSION
Displays the SQLcl version
version

WHENEVER OSERROR {EXIT [SUCCESS | FAILURE | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE[COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQLcl by default) if an operating system error occurs.

WHENEVER SQLERROR {EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]}
Performs the specified action (exits SQLcl by default) if a SQL command or PL/SQL block generates an error.

WHICH filename
Searches the SQLPATH and its directories for the specified file name and prints the name of the first file matching the specified file name in the SQLPATH.

XQUERY xquery_statement
Used to run an XQuery from SQLcl.

To change/setup SQL prompt with user/database name and with different colors and images :
set sqlprompt "_user'@'_connect_identifier "
set sqlprompt "_user'@'_connect_identifier 🍼 "
set sqlprompt "@|blue _USER|@@@|green _connect_identifier|@@|red |@ "
set sqlprompt "@|bold _USER|@@@|bg_cyan _CONNECT_IDENTIFIER|@@|blue 🍼 |@ "
set sqlprompt "@|red _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue 🍺 |@ "
set sqlprompt "@|white 'I am '|@@|bold _USER|@@|white ' on '|@@|blue,bg_white _CONNECT_IDENTIFIER|@@|bg_cyan ' '|@"

Related Oracle Articles: Changing SQL prompt

Postgres pg_upgrade utility

2023-05-18T16:20:00.000+05:30

pg_upgrade utility in PostgreSQL

pg_upgrade (formerly pg_migrator)

pg_upgrade upgrades a PostgreSQL cluster to a different major version.

pg_upgrade --help

Usage:

pg_upgrade [OPTION]...

Options:

-b, --old-bindir=BINDIR old cluster executable directory

-B, --new-bindir=BINDIR new cluster executable directory (default same directory as pg_upgrade)

-c, --check check clusters only, don't change any data

-d, --old-datadir=DATADIR old cluster data directory

-D, --new-datadir=DATADIR new cluster data directory

-j, --jobs=NUM number of simultaneous processes or threads to use

-k, --link link instead of copying files to new cluster

-N, --no-sync do not wait for changes to be written safely to disk

-o, --old-options=OPTIONS old cluster options to pass to the server

-O, --new-options=OPTIONS new cluster options to pass to the server

-p, --old-port=PORT old cluster port number (default 50432)

-P, --new-port=PORT new cluster port number (default 50432)

-r, --retain retain SQL and log files after success

-s, --socketdir=DIR socket directory to use (default current dir.)

-U, --username=NAME cluster superuser (default "thirumani")

-v, --verbose enable verbose internal logging

-V, --version display version information, then exit

--clone clone instead of copying files to new cluster

-?, --help show this help, then exit

Before running pg_upgrade you must:

create a new database cluster (using the new version of initdb)

shutdown the postmaster servicing the old cluster

shutdown the postmaster servicing the new cluster

When you run pg_upgrade, you must provide the following information:

the data directory for the old cluster (-d DATADIR)

the data directory for the new cluster (-D DATADIR)

the "bin" directory for the old version (-b BINDIR)

the "bin" directory for the new version (-B BINDIR)

For example:

pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin

$ export PGDATAOLD=oldCluster/data

$ export PGDATANEW=newCluster/data

$ export PGBINOLD=oldCluster/bin

$ export PGBINNEW=newCluster/bin

$ pg_upgrade

pg_upgrade --version

pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]

/usr/pgsql/12/bin/pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data --check

pg_upgrade --old-bindir=/usr/pgsql/10/bin --new-bindir=/usr/pgsql/12/bin --old-datadir=/var/lib/pgsql/10/data --new-datadir=/var/lib/pgsql/12/data

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --check

pg_upgrade -b=/usr/pgsql/11/bin -B=/usr/pgsql/13/bin -d=/var/lib/pgsql/11/data -D=/var/lib/pgsql/13/data --link

Related Postgres Articles: Comparison of PostgreSQL Oracle features

30 Top Cloud DBA Database Interview Questions

2023-05-14T18:46:00.000+05:30

AWS Cloud DBA/Database Interview Questions - Part 1

Q1. What are the advantages of cloud computing?

answer:

Q2. What is the database storage in AWS cloud? where are the RDS instance datafiles stored?

answer:

Q3. What database metrics will you be monitoring?

answer:

Q4. How will you check database performance issues/bottlenecks in AWS console?

answer:

Q5. Which EC2 instance type/class is the best for databases?

answer:

Q6. How to enforce the security of RDS instances?

answer:

Q7. How to encrypt data-at-rest in the cloud?

answer:

Q8. How to encrypt data-in-transit / data-in-motion in the cloud?

answer:

Q9. What is the difference/advantages between RDS instance and database running on EC2?

answer:

Q10. What is the difference between "General purpose" and "Provisioned IOPS" storage?

answer:

Q11. What is the advantage of Multi-AZ instance in AWS?

answer:

Q12. How do we export and import data/snapshots from production to non-production environments in different AWS accounts?

answer:

Q13. Please explain the main differences between RDS MySQL and AWS Aurora?

answer:

Q14. What are the main differences between RDS PostgreSQL and Amazon Aurora?

answer:

Q15. How to setup MySQL replication between two Amazon Aurora MySQL clusters?

answer:

Q16. How to setup logical replication between two AWS Aurora Postgres clusters?

answer:

Q17. How can you migrate data from MySQL RDS and Aurora MySQL?

answer:

Q18. How will you update the database parameter value of (Oracle) RDS instance?

answer:

Q19.

answer:

Q20.

answer:

initdb PostgreSQL tool

2023-04-25T04:30:00.000+05:30

Postgres initdb utility

initdb initializes/creates a new PostgreSQL database cluster.

initdb --help

Usage:

initdb [OPTION]... [DATADIR]

Options:

-A, --auth=METHOD default authentication method for local connections

--auth-host=METHOD default authentication method for local TCP/IP connections

--auth-local=METHOD default authentication method for local-socket connections

-D, --pgdata=]DATADIR location for this database cluster, data directory (if not specified, PGDATA will be used)

-E, --encoding=ENCODING set default encoding for new databases

-g, --allow-group-access allow group read/execute on data directory

--icu-locale=LOCALE set ICU locale ID for new databases

-k, --data-checksums use data page checksums

--locale=LOCALE set default locale for new databases

--lc-collate=, --lc-ctype=, --lc-messages=LOCALE

--lc-monetary=, --lc-numeric=, --lc-time=LOCALE set default locale in the respective category for new databases (default taken from environment)

--no-locale equivalent to --locale=C

--locale-provider={libc|icu} set default locale provider for new databases

--pwfile=FILE read password for the new superuser from file

-T, --text-search-config=CFG default text search configuration

-U, --username=NAME database superuser name

-W, --pwprompt prompt for a password for the new superuser, to force super user to provide password

-X, --waldir=WALDIR location for the write-ahead log directory

--wal-segsize=SIZE size of WAL segments, in megabytes

Less commonly used options:

-d, --debug generate lots of debugging output

--discard-caches set debug_discard_caches=1

-L DIRECTORY where to find the input files

-n, --no-clean do not clean up after errors

-N, --no-sync do not wait for changes to be written safely to disk

--no-instructions do not print instructions for next steps

-s, --show show internal settings

-S, --sync-only only sync database files to disk, then exit

Other options:

-V, --version output version information, then exit

-?, --help show this help, then exit

initdb --version

initdb -D /usr/local/pgsql/data

initdb -D /data/postgres/9.5/5301 -U

initdb -D /data/postgres/14.6/5301 -W

initdb -D /usr/edbstore --wal-segsize 1024 -W

initdb -D /data/postgres/15.2/5301 --pwprompt --username=postgresadmin

Related PostgreSQL Articles: pg_upgrade utility in PostgreSQL

Best 30 AWS Cloud Database Interview Questions

2023-04-24T13:38:00.011+05:30

AWS Cloud DBA/Database Interview Questions - part 2

Q21. How to perform point-in-time recovery (PITR) of the RDS database?

answer:

Q22. What is the advantage and disadvantage of the Aurora Global cluster?

answer:

Q23. How to create and maintain AWS resources?

answer:

Q24. What is Terraform/IaC (Infrastructure as Code)? How do you manage Cloud resources using Terraform?

answer:

Q25. What are the providers/modules in Terraform?

answer:

Q26. What are the different storage classes in S3 storage?

answer:

Q27. What are latency and throughput in Cloud computing?

answer:

Q28. What are the Cloudwatch Metrics you are monitoring for your databases?

answer:

Q29. What is Aurora serverless option? what are the advantages of Aurora serverless?

answer:

Q30. How do you encrypt an unencrypted RDS database instance?

answer:

Q31. What is Host Based Access (HBA) control in Postgres databases? Do we need to enable/use HBA in AWS PostgreSQL RDS instances?

answer:

Q32.

answer:

Q33.

answer:

Related Database Articles: Cloud DBA - AWS Database Interview Questions - Part 1

Postgres pg_verifybackup tool

2023-04-18T16:13:00.011+05:30

pg_verifybackup tool in PostgreSQL

pg_verifybackup verifies a backup against the backup manifest. New backup verification tool was introduced in Postgres 13.

pg_verifybackup --help

Usage:

pg_verifybackup [OPTION]... BACKUPDIR

Options:

-e, --exit-on-error exit immediately on error

-i, --ignore=RELATIVE_PATH ignore indicated path

-m, --manifest-path=PATH use specified path for manifest

-n, --no-parse-wal do not try to parse WAL files

-q, --quiet do not print any output, except for errors

-s, --skip-checksums skip checksum verification

-w, --wal-directory=PATH use specified path for WAL files

-V, --version output version information, then exit

-?, --help show this help, then exit

pg_verifybackup --version

pg_verifybackup c:\pgbackup

pg_verifybackup /backup/postgres

pg_verifybackup /backup/postgres --no-parse-wal --quiet

pg_verifybackup /backup/postgres --skip-checksums -q

Related Postgres Articles: PostgreSQL pg_basebackup utility

MySQL Router mysqlrouter commands

2023-04-13T11:39:00.012+05:30

MySQL Router

MySQL Router is part of the InnoDB Cluster and is lightweight middleware to provide transparent routing between applications and MySQL Servers.

mysqlrouter --help

mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/router

mysqlrouter --bootstrap localhost:3310 --directory /opt/myrouter --user snoopy

mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter -conf-use-sockets --account routerfriend --account-create always

mysqlrouter --bootstrap foo@bar.com --connect-timeout=20

mysqlrouter --bootstrap foo@bar.com --conf-set-option=logger.level=debug --conf-set-option=DEFAULT.unknown_config_option=warning --conf-set-option=DEFAULT.connect_timeout=20 --connect-timeout=10

mysqlrouter --bootstrap=127.0.0.1:3310 --master-key-reader=./reader.sh --master-key-writer=./writer.sh

mysqlrouter --bootstrap localhost:3310 --account-host host1 --account-host host2 --account-host host3

mysqlrouter --bootstrap root@localhost:3310 --conf-base-port 0

mysqlrouter -c /tmp/myrouter/mysqlrouter.conf

mysqlrouter --config /custom/path/to/router.conf --extra-config /another/config.conf

Related MySQL Articles: mysqlsh - MySQL Shell utility

MySQL Shell mysqlsh commands

2023-04-03T10:48:00.005+05:30

mysqlsh - MySQL Shell commands

mysqlsh

mysqlsh --mysqlx -u user -h localhost -C required --compression-algorithms=lz4,zstd --compression-level=5

echo "show databases;" | mysqlsh --sql --uri user@192.0.2.20:33060

mysqlsh < code.js

Usage: mysqlsh [OPTIONS] [URI]

mysqlsh [OPTIONS] [URI] -f path [script-args...]

mysqlsh [OPTIONS] [URI] --cluster|--replicaset

mysqlsh [OPTIONS] [URI] --object method [method-args...]

mysqlsh [OPTIONS] [URI] --dba enableXProtocol

mysqlsh [OPTIONS] [URI] --import {file|-} [collection|table column]

-?, --help Display this help and exit.

-- Triggers API Command Line integration, which allows execution of methods of the Shell global objects from command line using

syntax: -- object method [arguments]

For more details execute '\? cmdline' inside of the Shell.

-e, --execute=cmd Execute command and quit.

-c, --pyc=cmd Execute Python command and quit. Any options specified after this are used as arguments of the processed command.

-f, --file=file Specify a file to process in batch mode. Any options specified after this are used as arguments of the processed file.

--ssh=value Make SSH tunnel connection usingUniform Resource Identifier. Format: [user[:pass]@]host[:port]

--ssh-identity-file=file File from which the private key for public key authentication is read.

--ssh-config-file=file Specify a custom path for SSH configuration.

--uri=value Connect to Uniform Resource Identifier. Format: [user[:pass]@]host[:port][/db]

-h, --host=name Connect to host.

-P, --port=# Port number to use for connection.

--connect-timeout=ms Connection timeout in milliseconds.

-S, --socket[=sock] Socket name to use. If no value is provided will use default UNIX socket path.

-u, --user=name User for the connection to the server.

--password=[pass] Password to use when connecting to server. If password is empty, connection will be made without using a password.

-p, --password Request password prompt to set the password

--password1[=pass] Password for first factor authentication plugin.

--password2[=pass] Password for second factor authentication plugin.

--password3[=pass] Password for third factor authentication plugin.

-C, --compress[=value] Use compression in client/server protocol. Valid values: 'REQUIRED', 'PREFFERED', 'DISABLED', 'True', 'False', '1', and '0'. Boolean values map to REQUIRED and DISABLED respectively. By default compression is set to DISABLED in classic protocol and PREFERRED in X protocol connections.

--compression-algorithms=list Use compression algorithm in server/client protocol. Expects comma separated list of algorithms. Supported algorithms include 'zstd', 'zlib', 'lz4' (X protocol only), and 'uncompressed', which if appears in a list, causes connection to succeed even if compression negotiation fails.

--compression-level=int Use this compression level in the

--zstd-compression-level=int client/server protocol. Supported by X protocol and zstd compression in classic protocol.

--import file collection Import JSON documents from file to

--import file table col collection or table in MySQL Server. Set file to - if you want to read the data from stdin. Requires a default schema on the connection options.

-D, --schema=name Schema to use.

--database=name Same as --schema.

--fido-register-factor=name Specifies authentication factor, for which registration needs to be done.

--recreate-schema Drop and recreate the specified schema. Schema will be deleted if it exists!

--mx, --mysqlx Uses connection data to create an X protocol session.

--mc, --mysql Uses connection data to create a classic session.

--redirect-primary Ensure that the target server is part of an InnoDB cluster or ReplicaSet and if it is not a primary, find the primary and connect to it.

--redirect-secondary Ensure that the target server is part of an InnoDB Cluster or ReplicaSet and if it is not a secondary, find a secondary and connect to it.

--cluster Ensure that the target server is part of an InnoDB Cluster and if so, set the 'cluster' global variable. Also sets 'clusterset' if the cluster is part of a ClusterSet.

--replicaset Ensure that the target server is part of an InnoDB ReplicaSet and if so, set the 'rs' global variable.

--sql Start in SQL mode, auto-detecting the protocol to use if it is not specified as part of the connection information.

--sqlc Start in SQL mode using a classic session.

--sqlx Start in SQL mode using an X protocol session.

--js, --javascript Start in JavaScript mode.

--py, --python Start in Python mode.

--pym module Run Python library module as a script. Remaining args are forwarded to it.

--json[=format] Produce output in JSON format. Allowed values: raw, pretty, and off. If no format is specified pretty format is produced.

--table Produce output in table format (default for interactive mode). This option can be used to force that format when running in batch mode.

--tabbed Produce output in tab separated format (default for batch mode). This option can be used to force that format when running in interactive mode.

-E, --vertical Print the output of a query (rows) vertically.

--result-format=value Determines format of results. Allowed values: [table, tabbed, vertical, json, ndjson, json/raw, json/array, json/pretty].

--get-server-public-key Request public key from the server required for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic sessions with SSL mode DISABLED.

--server-public-key-path=p The path name to a file containing a client-side copy of the public key required by the server for RSA key pair-based password exchange. Use when connecting to MySQL 8.0 servers with classic sessions with SSL mode DISABLED.

-i, --interactive[=full] To use in batch mode. It forces emulation of interactive mode processing. Each line on the batch is processed as if it were in interactive mode.

--force In SQL batch mode, forces processing to continue if an error is found.

--log-file=path Override location of the Shell log file.

--log-level=value Set logging level. The log level value must be an integer between 1 and 8 or any of [none, internal, error, warning, info, debug, debug2, debug3] respectively.

--dba-log-sql[={0|1|2}] Log SQL statements executed by AdminAPI operations: 0 - logging disabled; 1 - log statements other than SELECT and SHOW; 2 - log all statements. Option takes precedence over --log-sql in Dba.* context if enabled.

--log-sql=off|error|on|all|unfiltered Log SQL statements: off - none of SQL statements will be logged; (default) error - SQL statement with error message will be logged only when error occurs; on - All SQL statements will be logged except these which match any of logSql.ignorePattern and logSql.ignorePatternUnsafe glob pattern; all - All SQL statements will be logged except these which match any of logSql.ignorePatternUnsafe glob pattern; unfiltered - All SQL statements will be logged.

--syslog Log filtered interactive commands to the system log. Filtering of commands depends on the patterns supplied via histignore option.

--verbose[={0|1|2|3|4}] Enable diagnostic message output to the console: 0 - display no messages; 1 - display error, warning and informational messages; 2, 3, 4 - include higher levels of debug messages. If level is not given, 1 is assumed.

--passwords-from-stdin Read passwords from stdin instead of the console.

--show-warnings={true|false} Automatically display SQL warnings on SQL mode if available.

--column-type-info Display column type information in SQL mode. Please be aware that output may depend on the protocol you are using to connect to the server, e.g. DbType field is approximated when using X protocol.

--histignore=filters Shell's history ignore list.

--pager=value Pager used to display text output of statements executed in SQL mode as well as some other selected commands. Pager can be manually enabled in scripting modes. If you don't supply an option, the default pager is taken from your ENV variable PAGER. This option only works in interactive mode. This option is disabled by default.

--mysql-plugin-dir[=path] Directory for client-side authentication plugins.

--name-cache Enable database name caching for autocompletion and DevAPI (default).

-A, --no-name-cache Disable automatic database name caching for autocompletion and DevAPI. Use \rehash to load DB names manually.

--nw, --no-wizard Disables wizard mode.

--no-password Sets empty password and disables prompt for password.

-V, --version Prints the version of MySQL Shell.

--ssl-key=file_name The path to the SSL private key file in PEM format.

--ssl-cert=file_name The path to the SSL public key certificate file in PEM format.

--ssl-ca=file_name The path to the certificate authority file in PEM format.

--ssl-capath=dir_name The path to the directory that contains the certificate authority files in PEM format.

--ssl-cipher=cipher_list The list of permissible encryption ciphers for connections that use TLS protocols up through TLSv1.2.

--ssl-crl=file_name The path to the file containing certificate revocation lists in PEM format.

--ssl-crlpath=dir_name The path to the directory that contains certificate revocation-list files in PEM format.

--ssl-mode=mode SSL mode to use. Allowed values: DISABLED,PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY.

--tls-version=version TLS version to use. Allowed values: TLSv1.2, TLSv1.3.

--tls-ciphersuites=name TLS v1.3 cipher to use.

--auth-method=method Authentication method to use. In case of classic session, this is the name of the authentication plugin to use, i.e. caching_sha2_password. In case of X protocol session, it should be one of: AUTO, FROM_CAPABILITIES, FALLBACK, MYSQL41, PLAIN, SHA256_MEMORY.

--dba=enableXProtocol Enable the X protocol in the target server. Requires a connection using classic session.

--quiet-start[={1|2}] Avoids printing information when the shell is started. A value of 1 will prevent printing the shell version information. A value of 2 will prevent printing any information unless it is an error. If no value is specified uses 1 as default.

--credential-store-helper=h Specifies the helper which is going to be used to store/retrieve the passwords.

--save-passwords=value Controls automatic storage of passwords. Allowed values are: always, prompt, never.

The following options may be given as the first argument:

--print-defaults Print the program argument list and exit.

--no-defaults Don't read default options from any option file, except for login file.

--defaults-file=# Only read default options from the given file #.

--defaults-extra-file=# Read this file after the global files are read.

--defaults-group-suffix=# Also read groups with concat(group, suffix)

--login-path=# Read this path from the login file.

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

The following groups are read: mysqlsh client

Usage examples:

$ mysqlsh --login-path=server1 --sql

$ mysqlsh root@localhost/schema

$ mysqlsh mysqlx://root@some.server:3307/world_x

$ mysqlsh --uri root@localhost --py -f sample.py sample param

$ mysqlsh root@targethost:33070 -s world_x -f sample.js

$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON

\help

\help connect

\connect

\connect dbadmin@localhost:3306

\c root@linuxhost1

\connect --mysqlx root@localhost:33060

\connect --ssh root@198.51.100.4:2222 root@localhost:3310

\disconnect

\js

\py

\sql

\status

shell.status()

\history

\show

\show threads --foreground -o tid,ptid,cid,user,hot,progname,command,memory

\show thread --tid 54 --general --client --locks

\source /tmp/mydata.sql

\use schema_name

\sql show databases;

\sql select * from sakila.actor limit 3;

JS> var session1 = mysql.getClassicSession('root@localhost:3306, 'hive');

JS> session.runSql('select * from mysql.user');

JS> db.createCollection('openjs');

JS> \watch query --interval=0.5 show global status like 'Com%'

Related MySQL Articles: mysqlrouter MySQL Router commands

Best 50 MySQL DBA interview questions answers

2023-03-07T19:16:00.013+05:30

MySQL/MariaDB DBA Interview Questions and Answers - Part 2

Q21. How a Galera cluster works?

answer:

Q22. What are the different types of replication in MySQL?

answer:

MySQL supports various replication topologies, including master-slave replication, master-master replication, and group replication. Each has its own benefits and use cases.

Q23. What is the purpose of the binary log in MySQL?

answer:

The binary log contains a record of all changes made to the MySQL database. It is used for various purposes, including replication, point-in-time recovery, and auditing.

Q24. How does MySQL Group Replication (GR) work?

answer:

Q25. What is the between MySQL Group Replication and Galera Cluster and Percona XtraDB Cluster?

answer:

Q26. How to setup MySQL replication from AWS MySQL RDS to Amazon Aurora MySQL?

answer:

Q27. What are the advantages of the Percona XtraDB Cluster (PXC)?

answer:

Q28. What is the use of ProxySQL/HAProxy?

answer:

Q29. How to use the Percona tool kit and what utilities do you know in Percona tool kit?

answer:

Q30. What is the difference between IST (Incremental State Transfer) and SST (Snapshot State Transfer) in PXC (Percona XtraDB Cluster)?

answer:

Q31. How do you restore a MySQL database from a backup?

answer:

To restore a MySQL database from a backup, you can use the MySQL command-line client or a graphical tool like phpMyAdmin. The process typically involves creating an empty database, importing the backup file, and ensuring data consistency.

Q32. What are the efficient ways to do (online) schema changes?

answer:

Q33. What is the best way to do DDL changes without locking the table?

answer:

Q34. What are the different storage engines supported by MySQL?

answer:

MySQL supports various storage engines, including InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, and more. InnoDB is the default storage engine from MySQL version 5.5 onwards.

Q35. What is the purpose of the InnoDB storage engine?

answer:

InnoDB is designed for high-performance and reliability, offering features such as transactions, row-level locking, foreign key constraints, and crash recovery.

Q36. How do you monitor the performance of a MySQL server?

answer:

You can monitor a MySQL server using various tools, including MySQL Enterprise Monitor, Performance Schema, EXPLAIN statement, slow query logs, and third-party monitoring solutions.

Q37. What is a deadlock in MySQL?

answer:

A deadlock occurs when two or more transactions wait indefinitely for each other to release resources, resulting in a state where none of the transactions can proceed. MySQL automatically detects and resolves deadlocks by rolling back one of the involved transactions.

Q38. What is the purpose of the ANALYZE TABLE statement?

answer:

The ANALYZE TABLE statement is used to analyze and update index statistics for a table. It helps the MySQL optimizer make better decisions when creating query execution plans.

Q39. How do you secure a MySQL server? what are the best practices for securing a MySQL database?

answer:

Using strong passwords and avoiding default or easily guessable usernames.
Limiting network access to the database server.
Regularly applying security patches and updates.
Using SSL/TLS for encrypted connections.
Implementing firewall rules to restrict access.
Applying the principle of least privilege.

Q40. What is the purpose of the mysqlcheck utility?

answer:

The mysqlcheck utility is used to check, repair, optimize, and analyze MySQL tables. It performs maintenance tasks to ensure data integrity and improve performance.

Related MySQL Articles: Top MySQL DBA Basic/advanced Interview Questions and Answers Part 1

Satya's DBA Blog

AWS DynamoDB Shell - ddbsh

DynamoDB Shell (ddbsh) commands in AWS

CREATE TABLE commands in DynamoDB shell

ALTER TABLE commands on DynamoDB tables using DDB shell

DROP TABLE commands on Amazon DynamoDB tables

SELECT queries/commands on DynamoDB tables using DynamoDB shell

INSERTing records INTO DDB tables with DynamoDB shell

UPDATE commands on Amazon DynamoDB tables using DDBShell

Deleting records from DynamoDB tables using DDB shell

Backup and Restore of AWS DynamoDB tables

Getting EXPLAIN plan for DynamoDB (shell) queries

Percona Xtrabackup Backup & Recovery

Percona Xtrabackup (PXC Part 4)

Percona Xtrabackup (8.0) installation from Repository

Creating Backup

Preparing Backup

Restoring Backup

Incremental Backup

Preparing Incremental Backups

Restoring Incremental Backups

Top New Features in PostgreSQL 16 database

Postgres 16 new features

Installation & Usage of Percona Toolkit 3

Percona Toolkit Usage and Installation (PXC Part 3)

Installing Percona Toolkit (3.3)

Using tools in Percona Toolkit

50 Top OGG (Oracle GoldenGate) Interview Questions

30 Top Oracle RMAN basic/advanced Interview Questions for DBAs

Top New Features in MySQL 8.1 database

MySQL 8.1 new features

Top 50 MySQL DBA interview questions answers

MySQL/MariaDB DBA Interview Questions and Answers - Part 1

Frequently asked Oracle PL/SQL Interview Questions

Top Oracle Database 23c new features

New Features in Oracle Database 23c

80 Best Oracle DBA Interview Questions answers for experienced professionals Part1 [ 2023 ]

30 Top Oracle DBA Performance Interview Questions with Answers [2023]

Top New Features in PostgreSQL 15 database

Postgres 15 new features

Best 90 Oracle DBA interview questions answers

Oracle DBA interview questions answers - Part 5

Top Oracle DBA Export/Import - Data Pump Interview Questions and Answers - 2023

50 Essential MySQL/MariaDB DBA interview questions answers

MySQL/MariaDB DBA Interview Questions and Answers - Part 3

Oracle SQLcl (SQL Developer command line) commands

Postgres pg_upgrade utility

pg_upgrade utility in PostgreSQL

30 Top Cloud DBA Database Interview Questions

AWS Cloud DBA/Database Interview Questions - Part 1

initdb PostgreSQL tool

Postgres initdb utility

Best 30 AWS Cloud Database Interview Questions

AWS Cloud DBA/Database Interview Questions - part 2

Postgres pg_verifybackup tool

pg_verifybackup tool in PostgreSQL

MySQL Router mysqlrouter commands

MySQL Router

MySQL Shell mysqlsh commands

mysqlsh - MySQL Shell commands

Best 50 MySQL DBA interview questions answers

MySQL/MariaDB DBA Interview Questions and Answers - Part 2