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
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
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
Posted by at No comments:
Labels: , ,

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
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


Posted by at No comments:

Swap Space

About Swap Space

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:
  1. 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
  2. Initialize the file as a swap file:
    # mkswap /swapfile
  3. Enable swapping to the swap file:
    # swapon /swapfile
  4. 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:
  1. 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.
  2. Initialize the partition (for example, /dev/sda2) as a swap partition:
    # mkswap /dev/sda2
  3. Enable swapping to the swap partition:
    # swapon /swapfile
  4. 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:
  1. Disable swapping to the swap file or swap partition, for example:
    # swapoff /swapfile
  2. Remove the entry for the swap file or swap partition from /etc/fstab.
  3. Optionally, remove the swap file or swap partition if you do not want to use it in future.
Posted by at No comments:
Subscribe to: Posts (Atom)