Pages

Friday, April 22, 2022

How To Optimize MySQL Tables

How To Optimize MySQL Tables


mysql> show table status like "user" \G

*************************** 1. row ***************************

           Name: user

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 6

 Avg_row_length: 115

    Data_length: 692

Max_data_length: 281474976710655

   Index_length: 2048

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2022-04-16 04:36:27

    Update_time: 2022-04-16 04:36:27

     Check_time: 2022-04-16 07:13:50

      Collation: utf8_bin

       Checksum: NULL

 Create_options:

        Comment: Users and global privileges

1 row in set (0.00 sec)


The output shows some general information about the table. The following two numbers are important:


Data_length represents the amount of space the database takes up in total.

Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.


Show Unused Space for all tables.

select table_name, data_length, data_free from information_schema.tables where table_schema='user' order by data_free desc;


Display Data in Megabytes.

select table_name, round(data_length/1024/1024), round(data_free/1024/1024) from information_schema.tables where table_schema='user' order by data_free desc;


Optimize a Table Using MySQL

optimize table user;


Optimize Multiple Tables at Once

optimize table user, db, proc;


Optimize Tables Using the Terminal

Syntax: 

mysqlcheck -o <schema> <table> -u <username> -p <password>

mysqlcheck -o <schema> <table> -u <username> -p <password>

 

How to findout of MySQL my.cnf file?

How to findout of MySQL my.cnf file?


mysql --help | grep "Default options" -A 1

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

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

What is MySQL Query Caching

What is MySQL Query Caching?


MySQL Query Caching provides database caching functionality. The SELECT statement text and the retrieved result are stored in the cache. When you make a similar query to the one already in the cache, MySQL will respond and give a query already in the cache. In this way, fewer resources are used, and your query runs faster.

To check the status

show variables like 'have_query_cache';

show variables like 'query_cache_%';


To adjust in MySQL configuration file:

query_cache_type=1

query_cache_size = 10M

query_cache_limit=256k


Disable the MySQL query cache without restarting MySQL.

set global query_cache_size = 0;

MySQL Troubleshooting

MySQL Troubleshooting


1. Not able to start the database.

-MySQL configuration file /etc/my.cnf is present and have all valid configuration.

-System has enough free resources to cater MySQL.

-Filesystems are not in READ ONLY mode.

-Check and fix the errors reported in /var/log/mysqld.log


2. MySQL is running, But users are unable to connect remotely.

-Verify you are connected to correct port of the database.

-Check firewall status on MySQL connectivity.

-Verify you are using correct user/password.


3. Users are unable to create new connections after a certain limit.

-Verify you are not hitting "max_connections limits" if yes, you need to increase it as per requirement.

show status like '%onn%';

show variables like "max_connections";

set global max_connections = 200; 

MySQL Statements For Table Maintenance

MySQL Statements For Table Maintenance


-CHECK TABLE    -------> For integrity checking
-REPAIR TABLE   -------> For repairs
-ANALYZE TABLE  -------> For analysis
-OPTIMIZE TABLE -------> For optimization


1.CHECK TABLE
The check table statement performs an integrity check on table structure and contents, and if the output from CHECK TABLE indicates that a table has problems, the table structure should be repaired.

check table <table_name>
check table <table_name>,<table_name>,<table_name>

2.REPAIR TABLE 
The repair table statement corrects the problem in a table that has become corrupted.
repair table <table_name>
repair table <table_name>,<table_name>,<table_name>

3.ANALYZE TABLE
The analyze table statement updates a table with information about the distribution of key values in the table, and this information is used the optimizer to make better choices about query execution plans.
analyzer table <table_name>
analyzer table <table_name>,<table_name>,<table_name>


4.OPTIMIZE TABLE
The optimize table statement cleans up a MyISAM table by defragmenting it, It involves reclaiming unused space resulting from deletes & updates, coalescing split records and stored non-contiguously and it also sorts the index pages if they are out of order and updates the index statistics.
optimize table <table_name>
optimize table <table_name>,<table_name>,<table_name>

To optimize all tables in all MySQL database using mysqlcheck 
mysqlcheck -o --all-databases -u root -p

To repair multiple MySQL databases using mysqlcheck
mysqlcheck -r --databases mysql qtest

To analyze databases using mysqlcheck
mysqlcheck -u root -p --analyze mysql

Tuesday, April 19, 2022

Sed Filter Command In Linux

Sed Filter Command In Linux


[root@mqmopensource ~]# cat qtest

My name is khadar and this is my first testing file

My son name is mohammed shoaib

My daughter name is rumaysa

[root@mqmopensource ~]# sed -n -e '/My son name is mohammed shoaib/p' qtest > shoaib.txt

[root@mqmopensource ~]# cat shoaib.txt

My son name is mohammed shoaib

Revoke privileges In MySQL

Revoke privileges In MySQL


Example:


revoke privileges on <object> from user;

revoke delete, update on <table_name> from 'user'@'localhost';

revoke delete, update on <table_name> from 'user'@'%';

revoke all on <table_name> from 'user'@'localhost';

revoke all on <table_name> from 'user'@'%';

revoke select on <object> from 'username'@'localhost';

revoke select on <object> from 'username'@'localhost'; 

Granting a user in MySQL

Granting a user in MySQL


Examples on GRANT command


grant all privileges on *.* to 'user_name'@'localhost';

grant all privileges on *.* to 'user_name'@'%';

grant all privileges on *.* to 'user_name'@'localhost' with grant option;

grant all privileges on *.* to 'user_name'@'%' with grant option;


grant all privileges on dbname.* to 'user_name'@'localhost';

grant all privileges on dbname.* to 'user_name'@'%';


grant insert,update,delete on table dbname.tablename to 'user_name'@'localhost';

grant insert,update,delete on table dbname.tablename to 'user_name'@'%';


grant create on database.* to 'user_name'@'localhost';

grant create on database.* to 'user_name'@'%';


grant select , execute on database.* to 'user_name'@'%';

grant select , execute on database.* to 'user_name'@'localhost'; 

How to convert a user In MySQL

How to convert a user In MySQL


Make sure you are connected with root/admin user:

mysql> select current_user();


Create normal user if not created.

mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';


mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';


Grant privileges for making it super user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;


Reload all the privileges

mysql> flush privileges;


View the grants:

mysql> show grants for super; 

MySQL User Management

MySQL User Management 


-- List all users present in mysql

mysql> select user,host,account_locked,password_expired from mysql.user;


Create user in mysql:

mysql> create user 'mqmtest_user'@'%' identified by 'mqmtest_user';

mysql> GRANT ALL ON *.* TO 'mqmtest_user'@'localhost';


View already created users:

mysql> select host, user from mysql.user;


Drop user:

mysql> drop user 'mqmtest_user'@'localhost';


Rename a user:

mysql> rename user 'mqmtest_user3' to 'mqmtest_user007';


change password of a user:

mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';


Change resource option:

mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;


Lock/unlock an account:

mysql> alter user 'dbaprod'@'%' account lock;

mysql> alter user 'dbaprod'@'%' account unlock; 

MySQL General Queries

MySQL General Queries


-- Drop database:

mysql> drop database qtest1;

mysql> show databases like 'information_schema%';

mysql> SELECT schema_name FROM information_schema.schemata;


How to get MySQL / MariaDB db size:

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 / 1024 as "size (gb)" from information_schema.tables group by table_schema;

mysql> select table_schema as "database", sum(data_length + index_length) / 1024 / 1024 as "size (mb)" from information_schema.tables group by table_schema;


How to find timezone info

Check whether time_zone table is updated or not.

mysql> select DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp()), 'GMT', 'Europe/Dublin'), '%Y%u');

mysql> select @@global.time_zone, @@session.time_zone;


How to check the time and date?

mysql> select now();


Below commands can be used to find MySQL / MariaDB version:

mysql> select version();

OS

#[root@mqmopensource ~]# mysql --version

mysql> show variables like "%version%";


show all processes in mysql cluster.

mysql> show processlist;


show processes for a specific user:

mysql> select * from information_schema.processlist where user='root'\G;


Show processes for a particular database:

mysql> select * from information_schema.processlist where DB='information_schema';


Get the processid for the session:

mysql> show processlist;


Uptime of server

mysql> status;

Alternative command

mysql> \s


Server startup time:

mysql> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';


How to check the data directory:

mysql> show variables like '%data%';

mysql> select variable_value from information_schema.global_variables where variable_name = 'datadir';

mysql> select @@datadir;


Find current data/time on MySQL / MariaDB

mysql> select current_timestamp,current_date,localtime(0),localtimestamp(0);


Find MySQL / MariaDB configuration values

mysql> select variable_name,variable_value from information_schema.global_variables;

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'port';

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name = 'datadir';


2. Alternatively you can check my.cnf file in /etc folder in Linux

cat /etc/my.cnf


Find the last MySQL / MariaDB Database service restarted / server reboot time

mysql> status;


-- In older version:


mysql> select time_format(sec_to_time(variable_value ),'%hh %im') as uptime from information_schema.global_status where variable_name='uptime';


-- In latest mysql version:


mysql> show global status like 'uptime';

mysql> show engines;


If the valid backup file is available with you, then you can restore the same using below command.

mysqldump -u root -p classicmodels < mysqldump.sql


-- If  dbdump.sql is the backup file , then restore command will be 

[mysql@localhost]# mysqldump –u root –pmysql sampledb > dbdump.sql


-- Backup multiple databases

For example, if you want to take backup of both sampledb and newdb database, execute the mysqldump as shown below:

[root@localhost]# mysqldump –u root –p --databases sampled newdb > /tmp/dbdump.sql

We cannot take backup of information_schema and performance_schema databases as these are metadata databases.


-- Backup all databases:

root# mysqldump -u root -p --all-databases > all-database.sql


Backup a specific table

SYNATX:

mysqldump –c –u username –p databasename tablename > /tmp/databasename.tablename.sql

In this example, we backup only the ta2 table from sampledb database.

root$ mysqldump –u root –p sample ta2 > /tmp/nwedb_ta2.sql


Restore all databases

[root]$ mysql -u root -p < /tmp/alldbs55.sql


Backup databases in compress format

With bzip2:

mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 


With gzip:

mysqldump --all-databases | gzip> databasebackup.sql.gz  

How to connect to MySQL / MariaDB database

How to connect to MySQL / MariaDB database


hostname$ export PATH=/usr/local/mysql/bin:$PATH

hostname$ which mysql

/usr/local/mysql/bin


SYNTAX - mysql -u user -p

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB 


Find current connection info:

mysql> \s

Below is the alternative command:

mysql> status;


Get current user and current database:

mysql> select current_user,database();


switch to another database:

mysql> use qtest1;

Database changed


mysql> select database(); 

Database Management In MySQL?

Database Management In MySQL?


Create a database in MySQL / MariaDB

-- Below commands can be used to create database

mysql> CREATE DATABASE QTEST;

Query OK, 1 row affected (0.00 sec)


mysql> create database qtest1 character set UTF8mb4 collate utf8mb4_bin;

Query OK, 1 row affected (0.00 sec)


-- View the create database statement used for creating db 

mysql> SHOW CREATE DATABASE qtest1;


View database list

mysql> show databases;

mysql> select * from information_schema.schemata;

mysql> show databases like 'QTEST%';


 

Backups and Recovery in MYSQL ?

Backups and Recovery in MYSQL ?


1. Physical backup (cold backup/offline backup)

Taking backup of physical files.


2. Logical backups (online backups)

Taking backup of logical objects


3. Incremental backups ( we can perform this on only logical backups only)


Note:

Take backup using MYSQLDUMP utility.

Restore using MYSQLIMPORT or MYSQL utility.


1. Physical backup (cold backup/offline backup)


-Stop the mysql services

service mysqld stop

-Copy the datadir and mysql config file to different locations

select @@datadir;

find / -name *cnf*

find / -name *.cnf*

cp -rf /var/lib/mysql/auto.cnf /root/mysqlcoldbackup_16042022/

cp -rf /var/lib/mysql /root/mysqlcoldbackup_16042022/mysqlbkp

cp -rfv /var/lib/mysql /root/mysqlcoldbackup_16042022/mysql$(date +%s)

service mysqld start


2. Logical backups (online backups)


To check the databases:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| QTEST              |

| classicmodels      |

| mysql              |

| performance_schema |

+--------------------+

5 rows in set (0.00 sec)


Database Level:

Backup a single database:

mysqldump -u root -p mysql > mysqldump.sql


Backup multiple databases:

mysqldump -u root -p --databases mysql classicmodels > multidatabasesdump.sql


Backup all databases:

mysqldump -u root -p --all-databases > all_databases.sql


Compressed MySQL Database Backup:

mysqldump -u root -p mysql | gzip > mysql.sql.gz


Backup with timestamp option:

mysqldump -u root -p mysql > mysql-$(date +%Y%m%d).sql


Table Level:

Backup a single table:

mysqldump -u root -p mysql servers > singletable.sql


Backup a multiple tables:

mysqldump -u root -p mysql servers db user > multi_table.sql


Restore Commands:

mysql> drop database mysql;

Query OK, 28 rows affected, 2 warnings (0.05 sec)


mysql> use mysql

ERROR 1049 (42000): Unknown database 'mysql'


mysql> create database mysql;

Query OK, 1 row affected (0.00 sec)


Restoring a database using backups:


[root@mqmopensource ~]# mysql -u root -p mysql > mysqldbkp.sql------> using same database backup dump

[root@mqmopensource ~]# mysql -u root -p mysql < all_database.sql  ------> using all database backup dump


mysql> use mysql

Database changed


mysql> show tables;

Empty set (0.00 sec)


Restore Command:

mysql -u root -p mysql < mysqldbkp.sql


mysql> use mysql

mysql> show tables;


To restore a single table from full/db backups in MYSQL:


mysql> use mysql;

mysql> show tables;

| user

| columns_priv              

| db                        

| event                     

| func                      

| general_log   


mysql> drop table user;

Query OK, 0 rows affected (0.00 sec)

MySQL Important Logfiles

MySQL Important Logfiles 


1. Error log file:

During instalation time this file is created , startup & shutdown, hang, maintenance related details are recorded.

show variables like '%log%';

log_error | /var/log/mysqld.log


2. General query log file:

Query related issues, instert, update, delete statements are recorded here.

Bydefault this log is not enabled.

show variables like '%log%';

general_log       | OFF                                   

general_log_file  | /var/lib/mysql/mqmopensource.log  

set global general_log=on/off;


3. Binary log file (STATIC VALUE):

Very useful for auditing like how many records are updated/deleted.

It records changes made to the database.

show variables like '%log%';

log_bin | OFF

log_bin_basename 

set global log_bin=on/off;

show binary logs;

flush logs;


4. Slow query log:

This is also very useful for slow query performance logs

slow_query_log      | OFF                                   |

slow_query_log_file | /var/lib/mysql/mqmopensource-slow.log |

set global slow_query_log=on/off;

show variables like '%time%';

long_query_time | 10.000000 (Seconds) 

Check data file location and tablespaces in MariaDB or MySQL

Check data file location and tablespaces in MariaDB or MySQL


Check size and location of data files or tablespaces present in MySQL or MariaDB

Check innodb_file_per_table parameter.

innodb_file_per_table=ON, InnoDB uses one tablespace file per table.

innodb_file_per_table=OFF, InnoDB stores all tables in the InnoDB system tablespace.

show variables like 'innodb_file_per_table'


Check the location of datafile/tablespace present in MySQL or MariaDB:

-- On MySQL

show variables like 'datadir';


Variable_name|Value                        

-------------+------------------------+

datadir      |/var/lib/mysql/|


Check files and tablespace for MySQL:

select * from information_schema.FILES;


Check files and tablespace detail for MariaDB:

select * from information_schema.innodb_sys_tablespaces; 

Check the Version of MariaDB / MySQL

Check the Version of MariaDB / MySQL


The first way to open a Command prompt and connect with MariaDB will show the version as follows:

C:\WINDOWS\system32>mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.6.5-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();

Check used & available maximum connection in MySQL

Check used & available maximum connection in MySQL


Check maximum available connection in MySQL

mysql> show global variables like 'max_connections';


Check used connection in MySQL

mysql> show global status like 'max_used_connections';


Check connected session list or Process list in MySQL

select id,user,host,db,command,time,state,info from information_schema.processlist; 

Create structure, duplicate & back up the table in MySQL

Create structure, duplicate & back up the table in MySQL

Use of CTAS for creating the Duplicate table with data or only structure (by using where 1 = 0) in the following database as follows:

MySQL: Use CTAS Syntax:

-- Create structure only

create table test_table_new like test_table;

-- Insert data into backup table

insert test_table_new select * from test_table;

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

Kill idle connection in MySQL

Kill idle connection in MySQL


Login with root user:

Check the session is in sleep state and get kill command for release the sessions:

select count(*) from information_schema.processlist where Command='Sleep';
select concat('KILL ',id,';') from information_schema.processlist where Command='Sleep';

concat('KILL ',id,';')|
----------------------+
KILL 10;              |
KILL 9;               |

Execute the command to Kill the session got from upper query output.

KILL 10; 
Automatic Clean the idle Connection in MariaDB/MySQL:

Interactive operation: this means opening the MySQL client on your local computer and doing various SQL operations on the command prompt.
INTERACTIVE_TIMEOUT is used to automatically clean the interactive connection in MariaDB/MySQL. The number of seconds the server waits for activity on an interactive connection before closing it

Non-interactive: means calls from the program. Example Tomcat Web service call to the database server through JDBC to connect etc.
WAIT_TIMEOUT is used to automatically clean the idle connection in MariaDB/MySQL.
The number of seconds the server waits for activity on a connection before closing it.

Note: Default time for both is 28800 seconds i.e. 8 hours

Check the Variables Values:

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Variable_name           |Value|
------------------------+-----+
wait_timeout            |28800|

show global variables like '%interactive_timeout%';

Variable_name      |Value|
-------------------+-----+
interactive_timeout|28800|

Change the time limit to 30 minutes for idle connection timeout: The value used in both variables is in Seconds i.e. 30 min – 1800 seconds or 1 hour – 3600 seconds.

SET GLOBAL wait_timeout=1800;
SET GLOBAL interactive_timeout=1800;

You can also change to the Default value:

SET GLOBAL wait_timeout=DEFAULT;
SET GLOBAL interactive_timeout=DEFAULT;

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>