Find the dependence on Table in MYSQL / MariaDB

Find the dependence on Table in MYSQL / MariaDB

Check the Procedures and functions depending on the table:

select a.routine_name,b.table_name,a.routine_schema,a.routine_type from information_schema.routines a inner join (select table_name , table_schema from information_schema.tables ) b on a.routine_definition like concat('%',b.table_name,'%') where  b.table_schema = 'dbname'  and b.table_name = 'test_table' ;

Check the views dependence on the table.

select * from information_schema.views where table_schema='mqm1' and table_name = 'test_table';

  

  

Check the foreign key constraints depend on the table.

SELECT Constraint_Type ,Constraint_Name ,Table_Schema ,Table_Name FROM information_schema.table_constraints

WHERE Table_Schema ='dbname' AND Table_Name = 'tablename' and Constraint_Type = 'FOREIGN KEY';

Start and Stop and restart the MariaDB or MySQL on Linux

Start and Stop and restart the MariaDB or MySQL on Linux

Check status for the MariaDB or MySQL

ps -ef|grep mysql

Check status for the MariaDB or MySQL

service mysqld status

Service mysql status

/etc/init.d/mysql status

/etc/init.d/mysqld status

Start the MariaDB or MySQL in Linux Environment:

service mysqld start

service mysql start

/etc/init.d/mysql start

/etc/init.d/mysqld start

Stop the MariaDB or MySQL in Linux Environment

service mysqld stop

service mysql stop

/etc/init.d/mysql stop

/etc/init.d/mysqld stop

Restart the MariaDB or MySQL Services in Linux Environment

service mysqld restart

service mysql restar

/etc/init.d/mysql restart

/etc/init.d/mysqld restart

Trace the SQL Queries in MYSQL

Trace the SQL Queries in MYSQL

For trace, the Queries executing from the application side in MariaDB provides a general_log parameter for enabling and disabling the traces.

Check status of General Log

mysql> select @@general_log;

For Enable the trace of SQL Queries:

mysql> set global general_log=1;

mysql> select @@general_log;

For Disable the trace of SQL Queries:

set global general_log=0;

Check and change the general log output as FILE or TABLE

-- Check the log file output format:

mysql> select @@log_output;

Check the file location:

mysql> select @@general_log_file;

--Change the log file format to TABLE:

SET GLOBAL log_output='TABLE';

Check the table for Log generation or SQL traces:

select * from mysql.general_log;

--Empty the table if no need

truncate table mysql.general_log;

How to check the block corruptions in database ?

How to check the block corruptions in database ?

RMAN Level:

RMAN> backup validate check logical datafile <<<file_number>>>;

RMAN> backup validate check logical database;

SQLPLUS Level:

sqlplus / as sysdba

select * from v$database_block_corruption;

OS Level:

The following example shows a sample use of the command-line interface to this mode of DBVERIFY.

% dbv FILE=t_db1.dbf feedback=100 >replace the file

dbv FILE=/u05/oradb/TEST/db/apps_st/data/eqddasm.dbf FEEDBACK=100

How to set RSYNC commands and set it in a crontab

How to set RSYNC commands and set it in a crontab

Crontab Entries:

crontab -l

*/30  *  *  *  * sh /u02/oracle/archsynch.sh

*/45 * * * * sh /u02/oracle/qfhcrmarchsynch.sh

Above sync commands will execute at every 30 and 45 minutes.

Below RSYNC Commands will be useful.

cat /u02/oracle/archsynch.sh

rsync -e ssh -Pazv /u03/archives/qprod oracle@192.168.1.50:/u03/archives/

cat /u02/oracle/qfhcrmarchsynch.sh

rsync -e ssh -Pazv /u03/archives/qdrum oracle@192.168.1.51:/u03/archives/

Standby RFS & MRP Process

Standby RFS & MRP Process

select process, status,sequence#,block#,blocks, delay_mins from v$managed_standby;


PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CONNECTED 0 0 0 0

ARCH CLOSING 23760 813056 1925 0

ARCH CLOSING 23713 567296 1119 0

ARCH CLOSING 23759 958464 656 0

ARCH CLOSING 23715 1 116 0

RFS IDLE 0 0 0 0

RFS IDLE 0 0 0 0

RFS IDLE 23761 200510 1 0

MRP0 APPLYING_LOG 23761 200510 1024000 0

RFS IDLE 0 0 0 0

10 rows selected.


Here RFS process is idle its mean there is no archive log generation on primary.



SQL> select thread#, process,status,sequence#, block#,blocks from v$managed_standby where process='RFS';


THREAD# PROCESS STATUS SEQUENCE# BLOCK# BLOCKS

---------- --------- ------------ ---------- ---------- ----------

0 RFS IDLE 0 0 0

0 RFS IDLE 0 0 0

1 RFS IDLE 23761 204221 1

0 RFS IDLE 0 0 0



SQL> select process,pid,status from v$managed_standby;


PROCESS PID STATUS

--------- ------------------------ ------------

ARCH 9352 CONNECTED

ARCH 9354 CLOSING

ARCH 9356 CLOSING

ARCH 9358 CLOSING

ARCH 9360 CLOSING

RFS 1468 IDLE

RFS 1462 IDLE

RFS 1464 IDLE

MRP0 9376 APPLYING_LOG

RFS 1466 IDLE

10 rows selected.


SQL> !ps -ef|grep 9376

oraprod 7178 7176 0 12:09:19 pts/1 0:00 grep 9376

oraprod 9376 1 0 Mar 06 ? 1:30 ora_mrp0_QASKDR

oraprod 7176 7149 0 12:09:19 pts/1 0:00 /usr/bin/bash -c ps -ef|grep 9376


SQL> !ps -ef|grep 1466

oraprod 7181 7179 0 12:09:49 pts/1 0:00 grep 1466

oraprod 1466 1 0 Apr 15 ? 0:00 oracleQASKDR (LOCAL=NO)

oraprod 7179 7149 0 12:09:49 pts/1 0:00 /usr/bin/bash -c ps -ef|grep 1466

SQL>