I have tried tar -xvf *.tar.gz command, but getting an error that read as:
tar (child): *.gz: Cannot open: No such file or directory
tar (child): Error is not recoverable: exiting now
tar: Child returned status 2
tar: Error is not recoverable: exiting now
How can I extract multiple *.tar.gz files in directory using Linux or Unix-shell prompt?

Linux or Unix-like system use the tar command to list, test, or extract files from a tar ball archive, commonly found on Unix-like systems.

The problem with multiple tarballs files on Linux/Unix

Assuming that you have four file in the current directory as follows:

1. conf.tar.gz
2. mysql.tar.gz
3. www.tar.gz

Let us verify it with the ls command
$ ls
Sample outputs:

conf.tar.gz  mysql.tar.gz  www.tar.gz

To untar all *.tar.gz file, enter:
$ tar xvf *.gz
OR
$ tar -zxvf *.tar.gz
OR
$ tar xvf "*.gz"
OR
$ tar -zxvf '*.gz'
Sample outputs:

Method #1: Untar multiple tarballs using bash for loop

Use the bash for loop as follows:
for f in *.tar.gz; do tar xf "$f"; done
OR
for f in *.tar.gz; do tar -xvf "$f"; done
OR
for f in *.tgz; do tar -xzvf "$f"; done
OR
for f in *.tar.bz2; do tar -xvf "$f"; done
OR
for f in *.tar.bz2; do tar -xjvf "$f"; done
OR
for f in *.tar.xz; do tar -xvf "$f"; done
OR
for f in *.tar.xz; do tar -xvJf "$f"; done
Verify it:
ls
Sample outputs:

conf.tar.gz  etc  home  mysql.tar.gz  root  www.tar.gz

Method #2: Untar multiple tarballs using bash/sh/ksh pipes

The syntax is:
cat *.tar.gz | tar zxvf - -i
cat *.tgz | tar zxvf - -i
cat *.tar.xz | tar Jxvf - -i
cat *.tar.bz2 | tar jxvf - -i
Sample outputs:

etc/mysql/
etc/mysql/ssl/
etc/mysql/ssl/server-req.pem
etc/mysql/ssl/ca-key.pem
etc/mysql/ssl/client-key.pem
etc/mysql/ssl/server-key.pem
etc/mysql/ssl/server-cert.pem
etc/mysql/ssl/client-cert.pem
etc/mysql/ssl/client-req.pem
etc/mysql/ssl/ca-cert.pem
etc/mysql/conf.d/
etc/mysql/conf.d/mysql.cnf
etc/mysql/conf.d/mysqldump.cnf
etc/mysql/my.cnf.fallback
etc/mysql/my.cnf
etc/mysql/mariadb.cnf
etc/mysql/mariadb.conf.d/
etc/mysql/mariadb.conf.d/50-client.cnf
etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
.....
...
..

The -i option force the tar command to ignore zeroed blocks in archive (EOF). This is needed if you are using method #2.