AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Oracle E-Business Suite Daily SQL: Current version: SELECT RELEASE_NAME FROM APPS.FND_PRODUCT_GROUPS; Find out application server information from database server:   column name format a20 SELECT NAME, NODE_ID, SERVER_TYPE FROM APPS.FND_APP_SERVERS; NAME                                     NODE_ID       SERVER_TYPE --------------------                      ----------          ------------------------------ <hostname>_EBSDEV_APPS      10113            APPS <hostname>_EBSDEV_DB          10112            DB Find out Oracle E-Business Suite Shared products: set pagesize 0 column applicaiton_name format a500 column status format a200 SELECT at.application_name || '      ' ||    decode(pi.STATUS,'I','Licensed', 'N','Not Licensed','S','Shared','Unknown') STATUS    FROM APPLSYS.fnd_product_installations pi        ,APPLSYS.fnd_application a        ,APPLSYS.fnd_application_tl at   WHERE pi.application_id = a.application_

Oracle E-Business Suite Application Tables: APPLSYS FND_APPLICATION APPLSYS FND_APP_SERVERS_U2 APPLSYS FND_APPLICATION_S APPLSYS FND_APPLICATION_SERVERS APPLSYS FND_APPLICATION_SERVERS# APPLSYS FND_APPLICATION_SERVERS_U1 APPLSYS FND_APPLICATION_SERVERS_U2 APPLSYS FND_APPLICATION_TL APPLSYS FND_APPLICATION_TL# APPLSYS FND_APPLICATION_TL_U1 APPLSYS FND_APPLICATION_TL_U2 APPLSYS FND_APPLICATION_U1 APPLSYS FND_APPLICATION_U3 APPLSYS FND_APPL_SESSIONS APPLSYS FND_APPL_SESSIONS# APPLSYS FND_APPL_SESSIONS_N1 APPLSYS FND_APPL_SESSIONS_N2 APPLSYS FND_APPL_TOPS APPLSYS FND_APPL_TOPS# APPLSYS FND_APPL_TOPS_U1 APPLSYS FND_APPL_TOPS_U2 APPLSYS FND_APPS_SYSTEM APPLSYS FND_APPS_SYSTEM# APPLSYS FND_APPS_SYSTEM_U1 APPLSYS FND_APPS_SYSTEM_U2 APPLSYS FND_APP_SERVERS APPLSYS FND_APP_SERVERS# APPLSYS FND_APP_SERVERS_U1 APPLSYS FND_APPLICATION# APPLSYSPUB FND_APPLICATION APPLSYSPUB FND_APPLICATION_TL APPLSYSPUB FND_APPLICATION_VL APPS FND_APPFLDR APPS FND_APPLICATION_TL= APPS FND_APPLET_LAUNCHER APPS FND_AP

Oracle E-Business Suite FND product tables: APPLSYS FND_PRODUCT_DEPENDENCIES APPLSYS FND_PRODUCT_DEPENDENCIES# APPLSYS FND_PRODUCT_INSTALLATIONS_PK APPLSYS FND_PRODUCT_INSTALLATIONS# APPLSYS FND_PRODUCT_INSTALLATIONS APPLSYS FND_PRODUCT_INIT_DEPENDENCY_U1 APPLSYS FND_PRODUCT_INIT_DEPENDENCY# APPLSYS FND_PRODUCT_INIT_DEPENDENCY APPLSYS FND_PRODUCT_INIT_CONDITION_U1 APPLSYS FND_PRODUCT_INIT_CONDITION# APPLSYS FND_PRODUCT_INIT_CONDITION APPLSYS FND_PRODUCT_INITIALIZATION_U1 APPLSYS FND_PRODUCT_INITIALIZATION# APPLSYS FND_PRODUCT_INITIALIZATION APPLSYS FND_PRODUCT_GROUPS# APPLSYS FND_PRODUCT_GROUPS APPLSYS FND_PRODUCT_DEPENDENCIES_U1 APPLSYSPUB FND_PRODUCT_GROUPS APPLSYSPUB FND_PRODUCT_INSTALLATIONS APPS FND_PRODUCT_INIT_DEPENDENCY APPS FND_PRODUCT_INIT_CONDITION= APPS FND_PRODUCT_INIT_CONDITION+ APPS FND_PRODUCT_INIT_CONDITION APPS FND_PRODUCT_INITIALIZATION_PKG APPS FND_PRODUCT_INITIALIZATION_PKG APPS FND_PRODUCT_INITIALIZATION= APPS FND_PRODUCT_INITIALIZATION+ APPS FND_PRODUCT_INITIALIZ

Configure MySQL Database parameters for Galera replication in /etc/my.cnf file: #Defines the path to the Galera Replication Plugin. wsrep_provider=/usr/lib/libgalera_smm.so #Semicolon (;) separated list of wsrep options, #https://mariadb.com/kb/en/library/wsrep_provider_options/ wsrep_provider_options="gcache.size=300M; gcache.page_size=300M" # logical name for your cluster. You must use the same name for every node in your cluster. wsrep_cluster_name="my_cluster" #IP addresses of nodes in a comma separated list. wsrep_cluster_address="gcomm://node1,node2,node3" #method or script the node uses in a State Snapshot Transfer, mysqldump, rsync, #rsync_wan, xtrabackup,wsrep_%.sh For instance, giving the node a transfer method of #MyCustomSST causes it to look for wsrep_MyCustomSST.sh in /usr/bin wsrep_sst_method=rsync #logical name for the individual node. wsrep_node_name=node1 #set the IP address for the individual node. w srep_node_address="192.168.0

Features of Galera Cluster: Multi-master - Galera cluster is real multi master, all the nodes are available for read and write operation. Synchronous replication - Replication in Galera cluster is synchronous. There is no slave, so there is no slave lag, either write to all node or all roll back. Tightly coupled - All nodes in Galera cluster are coupled tightly. Data on all node is same. Performance - Galera cluster support multi threaded to balance workload No failover - As there is no slave so there is not master slave failover. Hot standby - Standby nodes are always available and have same data, so there is hot standby. Automatic node provisioning - While adding new node to cluster family there is no need to manually back up the database, no need to restore backup to new node. InnoDB - Galera cluster supports only InnoDB engine. Transparent to application - Galera cluster is transparent to application No splitting - No read and write node, all nodes are read a

Kill queries running longer than 60s: pt-kill --busy-time 60 --kill Print, do not kill, queries running longer than 60s: pt-kill --busy-time 60 --print Print all login processes: pt-kill --match-state login --print --victims all Find all tables created more than a day ago, which use the MyISAM engine, and print their names:  pt-find --ctime +1 --engine MyISAM Find InnoDB tables and convert them to MyISAM: pt-find --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM" Find empty tables in the test and junk databases, and delete them: pt-find --empty junk test --exec-plus "DROP TABLE %s" Find tables more than five gigabytes in total size: pt-find --tablesize +5G Summarize MySQL information nicely: pt-mysql-summary --user=root Diff MySQL configuration files and server variables: Diff host1 config from SHOW VARIABLES against host2: pt-config-diff h=host1 h=host2 Diff config from [mysqld] section in my.cnf against host1 config: pt-config-diff /etc/my.cn

Disables locking while copying the non-InnoDB files: [mysqlbackup] no-locking After issuing FLUSH TABLES WITH READ LOCK: We can see one of the following status: Flushing tables Waiting for table flush Waiting for global read lock Pending FLUSH TABLES WITH READ LOCK because connection has: executed LOCK TABLES ... WRITE; for one or more tables a long running query (including DDL, simple SELECT, etc.) Identify Queries Taking Longer Than 10 Seconds: SELECT * FROM sys.session WHERE command = 'Query' AND time > 10\G SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_DB, PROCESSLIST_COMMAND, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO FROM performance_schema.threads WHERE PROCESSLIST_COMMAND = 'Query' AND PROCESSLIST_TIME > 10; SELECT * FROM information_schema.PROCESSLIST WHERE Command = 'Query' AND TIME > 10; Flush MySQL Host: flush hosts; Flush MySQL bin logs: flush bin logs; Select current date and time: s