rootupgrade.sh failed 18c

 sh rootupgrade.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/18.0.0

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]: y
   Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
y
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/18.0.0/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/vm33/crsconfig/rootcrs_vm33_2018-08-08_05-26-12PM.log
2018/08/08 17:27:55 CLSRSC-595: Executing upgrade step 1 of 19: 'UpgradeTFA'.
2018/08/08 17:27:55 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
2018/08/08 17:29:04 CLSRSC-4003: Successfully patched Oracle Trace File Analyzer (TFA) Collector.
2018/08/08 17:29:04 CLSRSC-595: Executing upgrade step 2 of 19: 'ValidateEnv'.
2018/08/08 17:29:20 CLSRSC-595: Executing upgrade step 3 of 19: 'GetOldConfig'.
2018/08/08 17:29:20 CLSRSC-464: Starting retrieval of the cluster configuration data
2018/08/08 17:29:33 CLSRSC-692: Checking whether CRS entities are ready for upgrade. This operation may take a few minutes.
2018/08/08 17:32:53 CLSRSC-693: CRS entities validation completed successfully.
2018/08/08 17:33:02 CLSRSC-515: Starting OCR manual backup.
2018/08/08 17:33:22 CLSRSC-516: OCR manual backup successful.
2018/08/08 17:33:34 CLSRSC-486:
 At this stage of upgrade, the OCR has changed.
 Any attempt to downgrade the cluster after this point will require a complete cluster outage to restore the OCR.
2018/08/08 17:33:34 CLSRSC-541:
 To downgrade the cluster:
 1. All nodes that have been upgraded must be downgraded.
2018/08/08 17:33:34 CLSRSC-542:
 2. Before downgrading the last node, the Grid Infrastructure stack on all other cluster nodes must be down.
2018/08/08 17:33:34 CLSRSC-615:
 3. The last node to downgrade cannot be a Leaf node.
2018/08/08 17:33:49 CLSRSC-465: Retrieval of the cluster configuration data has successfully completed.
2018/08/08 17:33:49 CLSRSC-595: Executing upgrade step 4 of 19: 'GenSiteGUIDs'.
2018/08/08 17:33:53 CLSRSC-595: Executing upgrade step 5 of 19: 'UpgPrechecks'.
2018/08/08 17:34:00 CLSRSC-363: User ignored prerequisites during installation
2018/08/08 17:34:19 CLSRSC-595: Executing upgrade step 6 of 19: 'SaveParamFile'.
2018/08/08 17:34:36 CLSRSC-595: Executing upgrade step 7 of 19: 'SetupOSD'.
2018/08/08 17:34:36 CLSRSC-595: Executing upgrade step 8 of 19: 'PreUpgrade'.
CLSCH-2005 : Oracle Cluster Health Analysis Service (OCHAD) failed due to a Grid Infrastructure Management Repository error.

Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLRecoverableException: Listener refused the connection with the following error:

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor



2018/08/08 17:34:44 CLSRSC-180: An error occurred while executing the command '/u01/app/12.2.0/grid/bin/chactl query model'
Died at /u01/app/18.0.0/crs/install/orachm.pm line 1036.


Soltuon:
=========

srvctl stop cha
srvctl remove cha

re-run rootupgrade.sh

opatchauto failed : 'ERROR: Invalid Patch location: /media/sf_Downloads/patch/27486326

Error:

2018-06-20 15:36:24,766 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='
Analysis for applying patches has failed:
'}
2018-06-20 15:36:24,767 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='Host:vm32'}
2018-06-20 15:36:24,767 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='CRS Home:/u01/app/12.1.0.2'}
2018-06-20 15:36:24,768 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='Analysis for patches has failed.'}
2018-06-20 15:36:24,769 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='ERROR: Invalid Patch location: /media/sf_Downloads/patch/27486326

'}
2018-06-20 15:36:24,771 INFO  [1] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='OPATCHAUTO-72053: Analysis for the patches failed.
OPATCHAUTO-72053: Command execution failed.
OPATCHAUTO-72053: Please check the summary for more details.'}

Solution:

If file system shared from windows to linux on virtualbox then grid user permission should be like below

$ id grid

uid=501(grid) gid=54321(oinstall) groups=54321(oinstall),504(asmadmin),506(asmdba),507(asmoper),488(vboxsf)

Linux Error: 29: Illegal seek

Error

 lsnrctl start listener

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-APR-2018 18:37:22

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek


Workaround

  cat /etc/hosts
127.0.2.1 localhost ---> add this entry in /etc/hosts

libmawt.so: libXext.so.6: cannot open shared object file: No such file or?

yum -y install libXext*i686*

How to fix /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

yum -y install glibc*i686*

How to add raw disk for oracle rac in Linux 6?

1.Partition disk

 fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xbb9171e3.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-652, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-652, default 652):
Using default value 652

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

2. Repeat above disk for all

3. cat /etc/udev/rules.d/60-raw.rules

# Enter raw device bindings here.

#

# An example would be:

#   ACTION=="add", KERNEL=="sda", RUN+="/bin/raw /dev/raw/raw1 %N"

# to bind /dev/raw/raw1 to /dev/sda, or

#   ACTION=="add", ENV{MAJOR}=="8", ENV{MINOR}=="1", RUN+="/bin/raw /dev/raw/raw2 %M %m"

# to bind /dev/raw/raw2 to the device with major 8, minor 1.

ACTION=="add", KERNEL=="sdb1", RUN+="/bin/raw /dev/raw/raw1 %N"

ACTION=="add", KERNEL=="sdc1", RUN+="/bin/raw /dev/raw/raw2 %N"

ACTION=="add", KERNEL=="sdd1", RUN+="/bin/raw /dev/raw/raw3 %N"

ACTION=="add", KERNEL=="sde1", RUN+="/bin/raw /dev/raw/raw4 %N"

ACTION=="add", KERNEL=="raw*", OWNER="grid", GROUP=="asmdba", MODE=="0666"

4./sbin/start_udev

Starting udev:                                             [  OK  ]

5.

ls -lrt /dev/raw/

total 0

crw-rw-rw-. 1 grid asmdba 162, 0 Feb 28 07:19 rawctl

crw-rw-rw-. 1 grid asmdba 162, 2 Feb 28 07:19 raw2

crw-rw-rw-. 1 grid asmdba 162, 1 Feb 28 07:19 raw1

crw-rw-rw-. 1 grid asmdba 162, 4 Feb 28 07:19 raw4

crw-rw-rw-. 1 grid asmdba 162, 3 Feb 28 07:19 raw3

How to check and kill active file in windows while doing patching?

Issue:

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following files are active :
d:\oracle\product\12.1.0\db_1\bin\oci.dll


Workaround:

D:\oracle\software\Patch>Tasklist /m oci*

Image Name                     PID Modules
========================= ======== ============================================
vmtoolsd.exe                  2288 oci.dll

D:\oracle\software\Patch>net stop "VMTools"

The VMware Tools service was stopped successfully.

D:\oracle\software>Tasklist /m ora*


D:\oracle\software>taskkill /PID 2288 /F

How to get DB ID when database in nomount state?

Issue below command in nomount state.
alter session set tracefile_identifier = DBID_TRACE;

alter system dump datafile 'C:\APP\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF' block min 1 block max 10;

check the trace file

Start dump data block from file C:\APP\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
Compatibility Vsn = 202375680=0xc100200
Db ID=2736190072=0xa316f278, Db Name='TESTDB'
Activation ID=0=0x0

ORA-27069: attempt to do I/O beyond the range of the file

Error:

[oracle@cloud1 rdbms]$ sqlplus "/as sysdba"


SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 14 19:23:07 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 479
Additional information: 1
Additional information: 472
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 479
Additional information: 1
Additional information: 472

Workaround:

trace sqlplus session

strace -o /tmp/problem_strace_sysdba.out -fTtt sqlplus / as sysdba

18885 19:20:43.797919 stat("/u01/app/oracle/audit/test12/", {st_mode=S_IFDIR|0750, st_size=4096, ...}) = 0 <0 .000011="">

18885 19:20:43.798632 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000010="">

18885 19:20:43.798682 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 14 <0 .000010="">

18885 19:20:43.799524 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000009="">

18885 19:20:43.799578 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000008="">

18885 19:20:43.799622 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 14 <0 .000009="">

18885 19:20:43.799758 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000008="">

18885 19:20:43.799803 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 14 <0 .000009="">

18885 19:20:43.799967 statfs("/u01/app/oracle/audit/test12/ora_audit_00.bin", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=15173137, f_bfree=2119411, f_bavail=1350712, f_files=3858432, f_ffree=3546627, f_fsid={-11706456, 1069661174}, f_namelen=255, f_frsize=4096}) = 0 <0 .000010="">

18885 19:20:43.800015 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 14 <0 .000009="">

18885 19:20:43.800150 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDWR|O_DSYNC) = 14 <0 .000010="">

18885 19:20:43.803933 stat("/u01/app/oracle/audit/test12/", {st_mode=S_IFDIR|0750, st_size=4096, ...}) = 0 <0 .000011="">

18885 19:20:43.804003 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000012="">

18885 19:20:43.804062 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 15 <0 .000016="">

18885 19:20:43.805632 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000011="">

18885 19:20:43.805691 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000008="">

18885 19:20:43.805735 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 15 <0 .000012="">

18885 19:20:43.805945 stat("/u01/app/oracle/audit/test12/ora_audit_00.bin", {st_mode=S_IFREG|0640, st_size=241664, ...}) = 0 <0 .000009="">

18885 19:20:43.805992 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 15 <0 .000009="">

18885 19:20:43.806076 statfs("/u01/app/oracle/audit/test12/ora_audit_00.bin", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=15173137, f_bfree=2119406, f_bavail=1350707, f_files=3858432, f_ffree=3546626, f_fsid={-11706456, 1069661174}, f_namelen=255, f_frsize=4096}) = 0 <0 .000009="">

18885 19:20:43.806123 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDONLY) = 15 <0 .000009="">

18885 19:20:43.806241 open("/u01/app/oracle/audit/test12/ora_audit_00.bin", O_RDWR|O_DSYNC) = 15 <0 .000009="">

remove or rename ora_audit_00.bin

Oracle Support Matrix

Release Schedule of Current Database Releases (Doc ID 742060.1)

SQLServer Database Mirroring failed

Error:

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'foo_copy'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

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

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

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

The server network address "TCP://sql02.domain.com:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

Workaround

Below grants fixed issue

Grant connect on endpoint::mirroring to public --primary

Grant connect on endpoint::mirroring to public --Mirror

WARNING OGG-01877 Oracle GoldenGate Manager for Oracle, mgr.prm: Missing explicit accessrule for server collector.

ACCESSRULE, PROG server, IPADDR *, ALLOW
ACCESSRULE, PROG *, IPADDR *, ALLOW

Restart the manager

how to execute dbcc checkdb for all databases in sql server?

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'

exadata cell starting issue

Issue:

Error 18446744073709551605 from io_setup, async ctx = 0x228a8c8
Errors in file /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/log/diag/asm/cell/cell/trace/svtrc_5110_main.trc  (incident=97):
ORA-00600: internal error code, arguments: [LinuxBlockIO::init], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/log/diag/asm/cell/cell/incident/incdir_97/svtrc_5110_main_i97.trc
Sweep [inc][97]: completed
Mon Mar 20 23:53:45 2017 State dump completed for CELLSRV<5110> after ORA-600 occurred
CELLSRV error - ORA-600 internal error
[RS] Started Service MS with pid 5106
Mon Mar 20 23:54:05 2017
Could not connect to MS socket. Communication with MS may be degraded. errno=110
[RS] monitoring process /opt/oracle/cell12.1.1.1.0_LINUX.X64_131219/cellsrv/bin/cellrsomt (pid: 5103) returned with error: 161


Workaround :

/etc/sysctl.conf

fs.aio-max-nr = 50000000

Sort by size in linux

du -sk * | sed -e 's_^\([0-9]*\)_\1 KB_' | sort -n

Check OEM threshold settings

select target_name,key_value ,WARNING_THRESHOLD,CRITICAL_THRESHOLD from mgmt$target_metric_settings where metric_column='pctUsed' and target_type='oracle_database' and WARNING_THRESHOLD is not null;

sql query history

SELECT
   h.sample_time,
   u.username,
   h.program,
   h.module,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  sample_time >= SYSDATE - 60
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
   and s.sql_text like '%'
   and u.username not in ('SYS')
ORDER BY h.sample_time
/

Agent is Unreachable (REASON = The agent is running but is currently not ready to accept client requests).

1.) Stop the agent:
emctl stop agent

2.) Set the following properties as shown below:
emctl setproperty agent -allow_new -name MaxInComingConnections -value 150
emctl setproperty agent -allow_new -name _cancelThread  -value 210

3.) Take backup and edit AGENT_INST/sysman/config/emd.properties

Change MaxThreads value to 250

Default
MaxThreads=10

to
MaxThreads=250

4.) Start the agent:
emctl start agent

Disable port from dynamic to static on SQLcluster 2012

Step1 :

Stop SQL services

Get cluster resource from powershell

PS C:\Users\Administrator.DOMAIN> Get-ClusterResource "SQL Network Name (sqlclustername)"

Step 2:

Remove checkpoint using below command from powershell

Get-ClusterResource "SQL Network Name (sqlclustername)" | Remove-ClusterCheckpoint -RegistryCheckpoint


Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\Cluster' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y


Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\MSSQLServer' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\Replication' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\Providers' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\SQLServerSCP' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\CPE' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\SQLServerAgent' on resource 'SQL Network Name (sqlclustername)'?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Remove-ClusterCheckpoint
Are you sure you want to remove registry checkpoint 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.sqlclustername\Cluster' on resource 'SQL Network Name (sqlclustername)'?

[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"):

Step 3 :

Chage port from network protocals

Step 4 : Repeat above steps on remaining nodes

Step 5 : Add Checkpoints from powershell

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\MSSQLServer" 

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\Replication" 

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\Providers" 

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\SQLServerSCP" 

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\CPE" 

Add-ClusterCheckpoint -ResourceName "SQL Network Name (sqlclustername)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceID\SQLServerAgent" 

Step 6: Now all set. Start the cluster services 

Activating SSL on clients

Activating SSL on clients

To connect  through SSL, ensure you have set up the user’s grant with ‘REQUIRE SSL’ syntax, similar to below:


$ CREATE SCHEMA testdb;
$ GRANT ALL PRIVILEGES ON testdb.* TO 'test'@'127.0.0.1' IDENTIFIED BY 'password' REQUIRE SSL;
$ FLUSH PRIVILEGES;


Test the console connections by using the following command:


$ mysql -u test-p -h 127.0.0.1 -P3306 --ssl-ca=/etc/ssl/mysql/ca-cert.pem --ssl-cert=/etc/ssl/mysql/client-cert.pem --ssl-key=/etc/ssl/mysql/client-key.pem


Or specify the SSL configuration options inside my.cnf (or .my.cnf for user’s option file) under [client] directive:


[client]
ssl-ca=/etc/ssl/mysql/ca-cert.pem
ssl-cert=/etc/ssl/mysql/client-cert.pem
ssl-key=/etc/ssl/mysql/client-key.pem

 All connections from client applications  now fully encrypted.