All about Databases - Oracle, PostgeSQL, Mysql, Big Data .....: 2017

Friday, September 29, 2017

Active Database Duplication (non-ASM to non-ASM on separate hosts)

Target (source) Host: Red Hat Enterprise Linux Server release 7.2 (Maipo)

Destination Host: CentOS Linux release 7.3.1611 (Core)

DB: 11.2.0.4.0

Target DB Name: maliyye

Destination DB Name: alfamal

Steps 1,2,9,10,12 will be executed from target (source) host.

Steps 3,4,5,6,7,8,11,13,14,15 will be executed from destination host.

1. Create a service for new database(alfamal) in tnsnames.ora file in target host.

[oracle@db admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

maliyye =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.25)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = maliyye)

)

stby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.26)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = stby)

)

alfamal =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.31)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME =alfamal)

)

2. Edit listener.ora file on target host.

[oracle@db admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=alfamal)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=alfamal)

)

ADR_BASE_LISTENER = /u01/app/oracle

And the restart listener: lsnrctl stop, lsnrctl start

3. Install only database software on destination host and create listener with netca.

4. Edit listener file on destination host as

[oracle@alfa admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = alfa)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=alfamal)

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

(SID_NAME=alfamal)

)

ADR_BASE_LISTENER = /u01/app/oracle

And the restart listener: lsnrctl stop, lsnrctl start

5. Create a service for new database(alfamal) on destination host

[oracle@alfa admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

maliyye =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.25)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = maliyye)

)

alfamal =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 112.100.11.31)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME =alfamal)

)

6. Add a line in the file /etc/oratab to reflect the new database instance on destination host

alfamal:/u01/app/oracle/product/11.2.0/dbhome_1:N

7. Set environment variables for duplicated database on destination host as

[oracle11g@orcl11g oracle]$ . oraenv

ORACLE_SID = [maliyye] ? alfamal

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle

8. Create folders in destination host for duplicated database

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/oradata

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/fast_recovery_area

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/admin/alfamal/adump

[oracle@alfa ~]$ mkdir -p /u01/app/oracle/admin/alfamal/dpdump

9. Create pfile from target (source) database.

SQL> create pfile='$ORACLE_HOME/dbs/initsource.ora' from spfile;

File created.

10. Copy the initialization parameter file from the target (source) database to destination database

oracle11g@orcl11g oracle]$ cp $ORACLE_HOME/dbs/initsource.ora

/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initalfamal.ora

11. Edit the pfile initalfamal.ora as:

*.audit_file_dest='/u01/app/oracle/admin/alfamal/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/alfamal/control01.ctl','/u01/app/oracle/fast_recovery_area/alfamal/control02.ctl'

*.db_block_size=8112

*.db_domain=''

*.db_name='alfamal'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=83687091200

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers=''

*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

*.log_archive_max_processes=5

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.db_create_file_dest='/u01/app/oracle/oradata'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

db_file_name_convert=("/u01/app/oracle/oradata/maliyye","/u01/app/oracle/oradata/alfamal")

log_file_name_convert=("/u01/app/oracle/oradata/maliyye","/u01/app/oracle/oradata/alfamal")

12. Copy the password file from target to destination host as well

oracle11g@db~]$ cp $ORACLE_HOME/dbs/orapwmaliyye

$ORACLE_HOME/dbs/orapwalfamal

13. Startup the destination database in nomount mode using modified parameter file and create spfile from it.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initalfamal.ora';

ORACLE instance started.

Total System Global Area 2388422656 bytes

Fixed Size 2215664 bytes

Variable Size 1342177552 bytes

Database Buffers 1023410176 bytes

Redo Buffers 20611264 bytes

SQL> sho parameter db_name

NAME TYPE VALUE

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

db_name string alfamal

SQL> create spfile from pfile;

File created.

14. Connect to target and auxiliary instance then duplicate database.

Note: We have to connect auxiliary(destination) database with TNS name. If not duplication will fail.

[oracle@alfa admin]$ rman target sys/maliyye12@maliyye auxiliary=sys/maliyye12@alfamal

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 29 11:43:11 2017

connected to target database: MALIYYE (DBID=2910117303)

connected to auxiliary database: ALFAMAL (not mounted)

RMAN> duplicate target database to ALFAMAL from active database;

Starting Duplicate Db at 29-SEP-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=66 device type=DISK

contents of Memory Script:

{

sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''MALIYYE'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''ALFAMAL'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/alfamal/control01.ctl';

restore clone controlfile to '/u01/app/oracle/fast_recovery_area/alfamal/control02.ctl' from

'/u01/app/oracle/oradata/alfamal/control01.ctl';

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''MALIYYE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ALFAMAL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

Starting backup at 29-SEP-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=4 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_maliyye.f tag=TAG20170929T114514 RECID=2 STAMP=955971914

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 29-SEP-17

Starting restore at 29-SEP-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=66 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished restore at 29-SEP-17

database mounted

contents of Memory Script:

{

set newname for datafile 1 to

"/u01/app/oracle/oradata/alfamal/system01.dbf";

set newname for datafile 2 to

"/u01/app/oracle/oradata/alfamal/sysaux01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/alfamal/users01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/alfamal/index1";

backup as copy reuse

datafile 1 auxiliary format

"/u01/app/oracle/oradata/alfamal/system01.dbf" datafile

2 auxiliary format

"/u01/app/oracle/oradata/alfamal/sysaux01.dbf" datafile

3 auxiliary format

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf" datafile

4 auxiliary format

"/u01/app/oracle/oradata/alfamal/users01.dbf" datafile

5 auxiliary format

"/u01/app/oracle/oradata/alfamal/index1" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

Starting backup at 29-SEP-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/maliyye/sysaux01.dbf

output file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/maliyye/system01.dbf

output file name=/u01/app/oracle/oradata/alfamal/system01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/maliyye/users01.dbf

output file name=/u01/app/oracle/oradata/alfamal/users01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/maliyye/index1

output file name=/u01/app/oracle/oradata/alfamal/index1 tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/maliyye/undotbs01.dbf

output file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf tag=TAG20170929T114521

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 29-SEP-17

sql statement: alter system archive log current

contents of Memory Script:

{

backup as copy reuse

archivelog like "/u01/app/oracle/fast_recovery_area/MALIYYE/archivelog/2017_09_29/o1_mf_1_205_dwvv1jtj_.arc" auxiliary format

"/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_%u_.arc" ;

catalog clone recovery area;

switch clone datafile all;

}

executing Memory Script

Starting backup at 29-SEP-17

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=205 RECID=396 STAMP=955971968

output file name=/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 29-SEP-17

searching for all files in the recovery area

List of Files Unknown to the Database

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

File Name: /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

datafile 1 switched to datafile copy

input datafile copy RECID=2 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=3 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=955971967 file name=/u01/app/oracle/oradata/alfamal/index1

contents of Memory Script:

{

set until scn 5557210;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-SEP-17

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 205 is already on disk as file /u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ALFAMAL/archivelog/2017_09_29/o1_mf_1_205_16sfluc1_.arc thread=1 sequence=205

media recovery complete, elapsed time: 00:00:01

Finished recover at 29-SEP-17

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

contents of Memory Script:

{

sql clone "alter system set db_name =

''ALFAMAL'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set db_name = ''ALFAMAL'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 254738432 bytes

Fixed Size 2252176 bytes

Variable Size 180355696 bytes

Database Buffers 67108864 bytes

Redo Buffers 5021696 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ALFAMAL" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( '/u01/app/oracle/oradata/alfamal/redo01.log' ) SIZE 50 M REUSE,

GROUP 2 ( '/u01/app/oracle/oradata/alfamal/redo02.log' ) SIZE 50 M REUSE,

GROUP 3 ( '/u01/app/oracle/oradata/alfamal/redo03.log' ) SIZE 50 M REUSE

DATAFILE

'/u01/app/oracle/oradata/alfamal/system01.dbf'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/alfamal/temp01.dbf";

switch clone tempfile all;

catalog clone datafilecopy "/u01/app/oracle/oradata/alfamal/sysaux01.dbf",

"/u01/app/oracle/oradata/alfamal/undotbs01.dbf",

"/u01/app/oracle/oradata/alfamal/users01.dbf",

"/u01/app/oracle/oradata/alfamal/index1";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/alfamal/temp01.dbf in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf RECID=1 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf RECID=2 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/users01.dbf RECID=3 STAMP=955971980

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/alfamal/index1 RECID=4 STAMP=955971980

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=955971980 file name=/u01/app/oracle/oradata/alfamal/index1

Reenabling controlfile options for auxiliary database

Executing: alter database force logging

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 29-SEP-17

RMAN>

15. Test the duplicated database.

[oracle@alfa ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 29 17:17:52 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/oradata/alfamal/control01.ctl

/u01/app/oracle/fast_recovery_area/alfamal/control02.ctl

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/alfamal/redo03.log

/u01/app/oracle/oradata/alfamal/redo02.log

/u01/app/oracle/oradata/alfamal/redo01.log

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/alfamal/system01.dbf

/u01/app/oracle/oradata/alfamal/sysaux01.dbf

/u01/app/oracle/oradata/alfamal/undotbs01.dbf

/u01/app/oracle/oradata/alfamal/users01.dbf

/u01/app/oracle/oradata/alfamal/index1

Tuesday, September 12, 2017

MySQL Source

MySql

https://medium.com/datadenys/tuning-mysql-8-0-server-for-better-performance-238e463f5583

https://severalnines.com/blog/mysql-performance-cheat-sheet/

https://hub.packtpub.com/optimize-mysql-8-servers-clients/

https://haydenjames.io/mysql-8-sample-config-tuning/

https://catonrug.blogspot.com/2019/11/reclaim-data-on-data-table.html

https://stackoverflow.com/questions/32760202/buffered-warning-changed-limits-max-connections-214-requested-800
https://stackoverflow.com/questions/22217186/difference-between-opened-files-and-open-files-in-mysql

http://www.datadisk.co.uk/html_docs/mysql/architecture.htm
https://lalitvc.wordpress.com/2016/11/03/mysql-architecture-and-components/
https://thinkingmonster.wordpress.com/database/mysql/mysql-architecture/
http://www.oracle.com/us/corporate/pricing/price-lists/mysql-pricelist-183985.pdf
http://blog.ronenb.com/2011/07/27/mysql-cluster-manager-hands-on/

https://blog.dotkam.com/2007/04/10/mysql-reset-lost-root-password/
https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/

https://opensourcedbms.com/dbms/passwordless-authentication-using-mysql_config_editor-with-mysql-5-6/
https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html
https://www.thegeekstuff.com/2017/08/mysqlbinlog-examples/

MySql Performance Links
https://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
http://www.speedemy.com/mysql/17-key-mysql-config-file-settings/innodb_log_file_size/
https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/
https://hostadvice.com/how-to/how-to-tune-and-optimize-performance-of-mysql-5-7-on-ubuntu-18-04/ ( https://github.com/RootService/tuning-primer )
https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/
https://www.saotn.org/mysql-innodb-performance-improvement/
https://dba.stackexchange.com/questions/161617/how-to-find-out-innodb-write-buffer-efficiency
https://dba.stackexchange.com/questions/1261/how-to-safely-change-mysql-innodb-variable-innodb-log-file-size/1265
https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
https://www.saotn.org/restore-single-mysql-table-full-mysqldump-backup-file/
https://spin.atomicobject.com/2010/12/13/extracting-a-table-from-a-mysql-dump/
https://dzone.com/articles/how-to-choose-the-mysql-innodb-log-file-size
https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/
https://dba.stackexchange.com/questions/114696/mysql-find-full-table-scan-queries

To merge backed up data with existing data
https://dba.stackexchange.com/questions/33807/dump-of-a-mysql-table-on-import-replaced-existing-records
http://www.mysqlab.net/knowledge/kb/detail/topic/backup/id/5285
https://dba.stackexchange.com/questions/5033/mysqldump-with-insert-on-duplicate
https://chartio.com/resources/tutorials/how-to-insert-if-row-does-not-exist-upsert-in-mysql/

MySQL Innodb Cluster Setup
https://santhinesh.wordpress.com/2018/09/21/innodb-cluster-in-mysql-8-0-11/
https://dinfratechsource.com/2018/11/10/mysql-innodb-cluster-a-complete-high-availability-solution-for-mysql/
http://shrenikp.blogspot.com/2018/10/mysql-innodb-cluster-troubleshooting.html
http://lefred.be/content/mysql-innodb-cluster-how-to-handle-fc/

Percona

https://www.percona.com/blog/2014/09/01/galera-replication-how-to-recover-a-pxc-cluster/

https://www.percona.com/blog/2012/08/17/percona-xtradb-cluster-multi-node-writing-and-unexpected-deadlocks/

http://galeracluster.com/documentation-webpages/restartingcluster.html

https://www.howtoforge.com/tutorial/mysql-hot-backup-with-percona-xtrabackup-on-ubuntu-16-04/

http://galeracluster.com/documentation-webpages/deploymentvariants.html

https://severalnines.com/blog/full-restore-mysql-galera-cluster-backup

https://www.hostlatte.com/blog/error-1805-hy000-column-count-mysql-user-wrong-table-probably-corrupted/
https://www.percona.com/blog/2012/12/19/percona-xtradb-cluster-pxc-what-about-gra_-log-files/
https://www.percona.com/doc/percona-monitoring-and-management/conf-mysql.html

Note: How do I completely remove MySQL from a linux installation?
If they installed it via yum on a RedHat based distribution you can remove with:
[redhat@redhat ~]$yum remove mysql-server

Oracle Linux uses swap space when your system does not have enough physical memory to store the text (code) and data pages that the processes are currently using. When your system needs more memory, it writes inactive pages to swap space on disk, freeing up physical memory. However, writing to swap space has a negative impact on system performance, so increasing swap space is not an effective solution to shortage of memory. Swap space is located on disk drives, which have much slower access times than physical memory. If your system often resorts to swapping, you should add more physical memory, not more swap space.

You can configure swap space on a swap file in a file system or on a separate swap partition. A dedicated swap partition is faster, but changing the size of a swap file is easier. Configure a swap partition if you know how much swap space your system requires. Otherwise, start with a swap file and create a swap partition when you know what your system requires.

Viewing Swap Space Usage

To view a system's usage of swap space, examine the contents of /proc/swaps:

# cat /proc/swaps
Filename                Type        Size      Used   Priority
/dev/sda2               partition   4128760   388    -1
/swapfile               file        999992    0      -2

In this example, the system is using both a 4-gigabyte swap partition on /dev/sda2 and a one-gigabyte swap file, /swapfile. The Priority column shows that the system preferentially swaps to the swap partition rather than to the swap file.

You can also view /proc/meminfo or use utilities such as free, top, and vmstat to view swap space usage, for example:

# grep Swap /proc/meminfo
SwapCached:          248 kB
SwapTotal:       5128752 kB
SwapFree:        5128364 kB
# free | grep Swap
Swap:      5128752        388    5128364

Creating and Using a Swap File

Note

Configuring a swap file on a btrfs file system is not supported.

To create and use a swap file:

Use the dd command to create a file of the required size (for example, one million one-kilobyte blocks):
```
# dd if=/dev/zero of=/swapfile bs=1024 count=1000000
```
Initialize the file as a swap file:
```
# mkswap /swapfile
```
Enable swapping to the swap file:
```
# swapon /swapfile
```
Add an entry to /etc/fstab for the swap file so that the system uses it following the next reboot:
```
/swapfile       swap       swap       defaults       0 0
```

Creating and Using a Swap Partition

To create and use a swap partition:

Use fdisk to create a disk partition of type 82 (Linux swap) or parted to create a disk partition of type linux-swap of the size that you require.
Initialize the partition (for example, /dev/sda2) as a swap partition:
```
# mkswap /dev/sda2
```
Enable swapping to the swap partition:
```
# swapon /swapfile
```
Add an entry to /etc/fstab for the swap partition so that the system uses it following the next reboot:
```
/dev/sda2       swap       swap       defaults       0 0
```

Removing a Swap File or Swap Partition

To remove a swap file or swap partition from use:

Disable swapping to the swap file or swap partition, for example:
```
# swapoff /swapfile
```
Remove the entry for the swap file or swap partition from /etc/fstab.
Optionally, remove the swap file or swap partition if you do not want to use it in future.

Reference: https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-s4-storage.html

Thursday, April 6, 2017

Linux Commands

We can check which ports are busy with the following command:

[root@master ~]# sudo lsof -i -P -n | grep LISTEN

sshd 1481 root 3u IPv4 19834 0t0 TCP *:22 (LISTEN)

sshd 1481 root 4u IPv6 19843 0t0 TCP *:22 (LISTEN)

cupsd 1482 root 12u IPv6 20163 0t0 TCP [::1]:631 (LISTEN)

cupsd 1482 root 13u IPv4 20164 0t0 TCP 127.0.0.1:631 (LISTEN)

master 1632 root 13u IPv4 21059 0t0 TCP 127.0.0.1:25 (LISTEN)

master 1632 root 14u IPv6 21060 0t0 TCP [::1]:25 (LISTEN)

dnsmasq 1672 nobody 6u IPv4 21403 0t0 TCP 192.168.122.1:53 (LISTEN)

postgres 3051 postgres 3u IPv6 33191 0t0 TCP [::1]:5432 (LISTEN)

postgres 3051 postgres 4u IPv4 33192 0t0 TCP 127.0.0.1:5432 (LISTEN)

postgres 3051 postgres 5u IPv4 33193 0t0 TCP 192.168.1.108:5432 (LISTEN)

Free command in Linux

We have 2 output format for free command.

1. Linux (RedHat/Oracle Linux/Centos) versions before 7.0

[oracle@radb1 ~]$ free -m

total used free shared buffers cached

Mem: 12010 11739 270 0 42 10666

-/+ buffers/cache: 1031 10979

Swap: 12287 939 11348

Some notes about interpreting the output (in MBs):

-> The first two lines of numbers are concerned about RAM. The final line of numbers is about your swap space.

-> The first three columns seem straightforward: the total capacity, how much of the total is used by processes
and how much of the total is free.

-> The next three columns are a bit more complicated. These are the memory shared among processes, memory that is being
used as buffers (temporary storage) by the kernel and as cached for pages.

-> The used and free entries in the first line show you how much RAM is being used and is free. You should not get worried if you see
the free number being low. Memory lying unused is useless, so kernel tries to use it as buffers and for caching. How much of
the used memory has been put up to use as buffers and cache is also shown in the first line.

-> Concerned about how much memory is truly being used by processes you are running? That is why the confusing second line exists!

used-in-second-line = used-in-first-line - buffers - cached and free-in-second-line = free-in-first-line + buffers + cached.

1031MB = 11739MB - 42MB - 10666MB 10979MB = 270MB + 42MB + 10666MB

In other words, Actual Total Used Memory = 1031MB Actual Total Available Memory = 10979MB

So, why free Linux system is displaying 11739 as used memory, because Linux counts cached memory, buffered memory to
this used memory.

In future any application want to use these buffer/cache then Linux will make it free.

Take a moment on these values. These calculations make sense since if your processes ask for more memory, the kernel
will happily free its buffers and cached resources and hand it over!

-> Finally, the shared is not factored into the second line computation because it is memory that is already shared among the processes.
It is already a part of the used memory. Now if you start asking how much memory is being used by a single process
then the computation factoring in shared memory gets harder.

-> Please ignore the -/+ buffers/cache text. It is completely confusing to the average user. To make any sense it should have
been -/+ buffers+cached.

-> Explanation detailed:

12010 : Display physical/memory available for your system. Output is in MBs.

11739 : Display memory used by system. Include buffers and cached data size as well.

270 : Display total free memory and available for new process to execute.

0 : Display shared memory.

42 : Display total memory buffered by different application on the system.

10666 : Display total memory used for caching of data for future uses.

What Is The Difference Between Buffers And Cache?

Buffers is used to store data for a particular application for temporarily and this data is not used by any other application.

Similar work like bandwidth if try to send burst of data through network, and network card is unable to send this data and
capable to send less data it will keep these hugs amounts of data in buffer so that it can send data constantly in lesser speeds.

Similarly cache is a memory location to store frequently used data for faster access. Another difference between a buffer
and cache is that cache can be used multiple times and buffer is used single time, both are used to temporary data storage
for processing.

reference: https://codeyarns.com/2016/06/15/free-command-in-linux/

2. Linux (RedHat/Oracle Linux/Centos) versions after 7.0

[oracle@rac1 ~]$ free -g

total used free shared buff/cache available

Mem: 2015 300 179 1353 1535 359

Swap: 127 0 127

In new format following formulas can be extracted from the output to calculate the total memory and usage.

total = free + used + buff/cache 2015 = 179 + 300 + 1535

total = shared + available + used 2015 = 1353 + 359 + 300

free = shared + avaialble - buff/cache 179 = 1353 + 359 - 1535

used = total - shared - available 300 = 2015 - 1353 - 359

available = total - shared - used 359 = 2015 - 1353 - 300

shared = total - avaialble - used 1353 = 2015 - 359 - 300

Buff/cache means cached pages from disk into memory for higher system speed. It is cache of contents from disk loaded into RAM.

When program requests reading of file already in cache, its contents is served from RAM. It does not have to wait each time until
it is read from disk. Loading times for SSD are close to acessing RAM, so this will not make huge difference.

Difference between free and available is that free is not used for anything useful. Available includes cache parts, that can be freed
if more memory is required. Buff will fill with IO operations of running system. It will load contents into RAM for higher speed.
Because your server is more busy and longer running than your desktop, it already has full cache. That is ok, free memory is
wasted memory.

--> Good Explanation for Memory Usage in Linux <--

What's going on?

Linux is borrowing unused memory for disk caching. This makes it looks like you are low on memory, but you are not! Everything is fine!

Why is it doing this?

Disk caching makes the system much faster and more responsive! There are no downsides, except for confusing newbies. It does not
take memory away from applications in any way, ever!

What if I want to run more applications?

If your applications want more memory, they just take back a chunk that the disk cache borrowed. Disk cache can always be given
back to applications immediately! You are not low on ram!

Do I need more swap?

No, disk caching only borrows the ram that applications don't currently want. It will not use swap. If applications want more memory,
they just take it back from the disk cache. They will not start swapping.

How do I stop Linux from doing this?

You can't disable disk caching. The only reason anyone ever wants to disable disk caching is because they think it takes memory
away from their applications, which it doesn't! Disk cache makes applications load faster and run smoother, but it NEVER EVER
takes memory away from them! Therefore, there's absolutely no reason to disable it!

Why does top and free say all my ram is used if it isn't?

This is just a difference in terminology. Both you and Linux agree that memory taken by applications is "used", while memory
that isn't used for anything is "free".

But how do you count memory that is currently used for something, but can still be made available to applications?

You might count that memory as "free" and/or "available". Linux instead counts it as "used", but also "available":

Memory that is You'd call it Linux calls it

used by applications Used Used

used, but can be made available Free(or Available) Used(and Available)

not used for anything Free Free

This "something" is (roughly) what top and free calls "buffers" and "cached". Since your and Linux's terminology differs, you might think
you are low on ram when you're not.

How do I see how much free ram I really have?

To see how much ram your applications could use without swapping, run free -m and look at the "available" column:

$ free -m

total used free shared buff/cache available

Mem: 1504 1491 13 0 855 792

Swap: 2047 6 2041

(On installations from before 2016, look at "free" column in the "-/+ buffers/cache" row instead.)

This is your answer in megabytes. If you just naively look at "used" and "free", you'll think your ram is 99% full when it's really just 47%!

For a more detailed and technical description of what Linux counts as "available", see the commit that added the field.

When should I start to worry?

A healthy Linux system with more than enough memory will, after running for a while, show the following expected and harmless behavior:

- free memory is close to 0

- used memory is close to total

- available memory (or "free + buffers/cache") has enough room (let's say, 20%+ of total)

- swap used does not change

Warning signs of a genuine low memory situation that you may want to look into:

- available memory (or "free + buffers/cache") is close to zero

- swap used increases or fluctuates

- dmesg | grep oom-killer shows the OutOfMemory-killer at work

reference: https://www.linuxatemyram.com/

List each file size in current directory

[oracle@cdb1 ~]$ du -sh *

4.0K afiedt.buf

4.0K check.sql
4.0K Desktop
4.0K Documents
4.0K Downloads
7.2G install

List files with time in reverse order

[root@rhel77 Desktop]# ls -ltr -h

total 114M

-rwxrwx---. 1 root root 114M Mar 28 23:47 postgresql-9.5.6.tar

drwxrwxrwx. 6 1107 1107 4.0K Mar 29 23:24 postgresql-9.5.6

-l: List in long format. If the output is to a terminal, a total sum for all the file sizes is output on a line before the long listing.
-r: Reverse the order of the sort to get reverse lexicographical order
-t: Sort by time modified (most recently modified first) before sorting the operands by lexicographical order.

How to reload udev rules without reboot?

[root@rac2 ~]# udevadm control --reload-rules && udevadm trigger
https://unix.stackexchange.com/questions/39370/how-to-reload-udev-rules-without-reboot

Usage of ps command

Type the following ps command to display all running processes:

# ps aux | less

a: select all processes

u: select all processes on a terminal, including those of other users

x: select processes without controlling ttys

See processes run by user dfs

-bash-3.00$ ps -fu dfs

UID PID PPID C STIME TTY TIME CMD

dfs 17262 1 0 Oct 23 ? 2:47 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1940 -classpath dfs_1_6.jar co

dfs 17260 1 0 Oct 23 ? 3:25 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1920 -classpath dfs_1_6.jar co

dfs 17264 1 0 Oct 23 ? 2:48 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1922 -classpath dfs_1_6.jar co

dfs 17266 1 0 Oct 23 ? 2:53 java -Xms256m -Xmx2024m -Dcom.cs.dfs.server.port=1930 -classpath dfs_1_6.jar co

Installing package without dependency as

[root@stageoem1]# rpm -ivh --nodeps --force oracleasmlib-2.0.4-1.el6.x86_64.rpm

   warning: oracleasmlib-2.0.4-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID
ec551f03: NOKEY
Preparing...                ########################################### [100%]
1:oracleasmlib           ########################################### [100%]

To determine the available RAM and swap space, enter the following command:

[oracle11g@localhost ~]$ grep MemTotal /proc/meminfo

MemTotal: 5850308 kB

[oracle11g@localhost ~]$ grep SwapTotal /proc/meminfo
SwapTotal: 6176764 kB

To determine the amount of free disk space on the system, enter the following command:

[oracle11g@localhost ~]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ol-root 50G 4.5G 46G 9% /

devtmpfs 2.8G 0 2.8G 0% /dev

tmpfs 2.8G 84K 2.8G 1% /dev/shm

tmpfs 2.8G 8.8M 2.8G 1% /run

tmpfs 2.8G 0 2.8G 0% /sys/fs/cgroup

/dev/mapper/ol-home 64G 4.6G 60G 8% /home

/dev/sda1 497M 206M 292M 42% /boot

none 532G 49G 484G 10% /media/sf_Shared_Folder

To determine the amount of shared memory available, enter the following command:

[oracle11g@localhost ~]$ df -h /dev/shm/
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.8G 84K 2.8G 1% /dev/shm

To display system information type uname command

[oracle@rac2 ~]$ uname -r

4.1.12-94.3.9.el7uek.x86_64

-a: print all information, in the following order except omit -p and -i if unknown

-s: print the kernel name

-n: print the network node hostname

-r: print the kernel release

-v: print the kernel version

-m:print the machine hardware name

-p: print the processor type or "unknown"

-i: print the hardware platform or "unkown"

-o: print the operating system

--help: display the help and exit

--version: output version information and exit

To determine the distribution and version of Linux installed, enter the following command

[oracle@rac2 ~]$ cat /proc/version
Linux version 4.1.12-94.3.9.el7uek.x86_64 (mockbuild@) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) ) #2 SMP Fri Jul 14 20:09:40 PDT 2017

Display the umask in octal and in symbolic form

[oracle@rac2 ~]$ umask

0022

[oracle@rac2 ~]$ umask -S

u=rwx,g=rx,o=rx

To determine if the required packages are installed, enter commands similar to the following:

[oracle11g@localhost ~]$ rpm -q glibc-2.12-1.7.el6

package glibc-2.12-1.7.el6 is not installed

To determine if a listener process is running and to identify its name and the Oracle home directory in which it is installed:

[oracle@Oracle11g ~]$ ps -ef | grep tnslsnr

oracle 2467 1 0 13:16 ? 00:00:03 /home/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit

oracle 2939 1 0 13:17 ? 00:00:02 /home/oracle/app/product/11.2.0/grid/bin/tnslsnr LISTENER -inherit

oracle 25392 5891 0 19:04 pts/0 00:00:00 grep tnslsnr

To determine if the oraInst.loc file exists, enter the following command:

[oracle11g@localhost ~]$ more /etc/oraInst.loc

inventory_loc=home/oracle/oraInventory inst_group=oinstall

All groups are in /etc/group

[root@localhost ~]# cat /etc/group

oinstall:x:1001:

dba:x:1002:oracle11g

oper:x:502:oracle11g

grid:x:1003:grid

asmadmin:x:1005:grid

asmdba:x:1006:oracle11g,grid

asmoper:x:1007:grid

To determine if an Oracle software owner user named oracle, or grid exists, enter a command similar to the following:

[oracle11g@localhost ~]$ id oracle

uid=501(oracle) gid=501(oinstall) groups=502(dba),503(oper)
To create an oracle user, enter a command similar to the following:

# /usr/sbin/useradd -u 502 -g oinstall -G dba,asmdba oracle

- The -u option specifies the user ID. Using this command flag is optional because the system can provide you with an automatically generated user ID number. You must note the oracle user ID number because you need it during preinstallation.

- The -g option specifies the primary group, which must be the Oracle Inventory group, for example oinstall.

- The -G option specifies the secondary groups, which must include the OSDBA group, and, if required the OSOPER and ASMDBA groups.

Set the password of the oracle user:

# passwd oracle

Add groups

[root@localhost oracle11g]# usermod -g oinstall -G grid, vboxsf, asmdba, asmadmin, asmoper grid

[root@localhost oracle11g]# groups grid

grid : oinstall vboxsf asmadmin asmdba asmoper grid

[root@localhost oracle11g]# groups oracle

oracle : oinstall wheel vboxsf dba oper asmdba oracle

Check 'oracle' related environment parameters
[oracle@Oracle11g ~]$ env | grep ORACLE

ORACLE_UNQNAME=testdb

ORACLE_SID=testdb2

ORACLE_BASE=/home/oracle/app

ORACLE_HOME=/home/oracle/app/product/11.2.0/dbhome_1

The difference between "-" and "no hyphen" is that the latter keeps your existing environment (variables, etc); the former creates a new environment (with the settings of the actual user, not your own).

Ex: [oracle11g@oracle11g~]$ su - grid versus [oracle11g@oracle11g~]$ su grid

Output for former is

[oracle11g@orcl11g ~]$ su - grid

[grid@orcl11g~]$ env | grep ORACLE

ORACLE_UNQNAME=+ASM

ORACLE_SID=+ASM

ORACLE_BASE=/u01/app/grid

ORACLE_HOME=/u01/app/grid /product/11.2.0/grid

Output for latter is

[oracle11g@oracle11g ~]$ su grid

[grid@orcl11g~]$ env | grep ORACLE

ORACLE_UNQNAME=ora11g

ORACLE_SID=ora11g

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle /product/11.2.0/dbhome_1

If you encounter the ORA-00845 error, is to increase the /dev/shm mountpoint size.

For example:

# mount -t tmpfs shmfs -o size=7g /dev/shm

To make this change persistent across system restarts, add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=7g 0

To mount NFS for Oracle we have to choose correct options. Because of bug if not correct options selected then following error will be occured:

channel ORA_DISK_1: reading from backup piece /backup/cdb_prod_bakup/ivs1evnc_1_1
RMAN-00571: =======================================
RMAN-00569: =====ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =======================================================
RMAN-03002: failure of recover command at 04/13/2017 10:22:09
ORA-19870: error while restoring backup piece /backup/cdb_prod_bakup/ivs1evnc_1_1
ORA-19625: error identifying file /backup/cdb_prod_bakup/data_D-CDBPROD_I-3983442973_TS-ACCOUNTING_FNO-6_07rhhtg0
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
Additional information: 12

Mount as
[root@cdb1 scripts]# mount -t nfs 10.254.212.1:/backup /backup -o rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600,actimeo=0

And add following line two /etc/fstab file as

10.254.212.1:/backup /backup nfs rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,noac,vers=3,timeo=600,actimeo=0 0 0

To mount or unmount

umount -t nfs 10.254.212.1:/backup /backup

mount -t nfs 10.254.212.1:/backup /backup

references:

http://www.dba-oracle.com/t_ora_27054.htm

http://dbaforums.org/oracle/index.php?showtopic=21342

http://oletange.blogspot.com/2012/04/umount-device-is-busy-why.html

Set backspace as erase key to kill characters in SQL*Plus and RMAN environment

In Linux shell type following

$stty erase <ctrl V backspace key>

In .bash_profile save as following for permanent erase
stty erase ^H #Erase and kill characters

To find and delete files older than specific date

Example: rm -rf $(find /u01/app/oracle/dump -type d -mtime +4) # Find all dump directories older than +4 day and delete it from local

Example: cd <path to the directory where logs are>

rm -rf `ls -t | awk "NR>10"` # Delete all files older than 10 days

Using the Locate Command

The locate command is faster than the find command because it uses a previously built database, whereas the find command searches
in the real system, through all the actual directories and files. The locate command returns a list of all path names containing
the specified group of characters.

All other Linux command links:

http://www.thegeekstuff.com/2010/12/5-ways-to-execute-linux-command

http://www.comptechdoc.org/os/linux/usersguide/linux_ugbasics.html

http://www.computerhope.com

https://ask.fedoraproject.org/en/question/8847/just-installed-postgresql-what-path-is-it-stored/

https://www.cyberciti.biz/faq/unix-linux-check-if-port-is-in-use-command/

https://www.cyberciti.biz/faq/show-all-running-processes-in-linux/