Previewing Backup Restore

Introduction:

This is a short article on RMAN where we can check if our backups are really intact and can help us in critical situation when we have to restore and recover the database.

We have a command option called PREVIEW which we can use with RESTORE DATABASE. This option does not actually restore the datafiles from backup but it just tell us SCN number until which we should be recovering our database. It also tells us SCN number until which it can restore.

Lets take a simple example where I have a complete database backup taken for my database including archivelogs.

I will run RESTORE DATABASE PREVIEW command to check if I can get back my database in case of any issues. Then I will wipe out my database and try to recover from the backup to validate if PREVIEW option was showing correct information.

Validating Backupsets

We can validate if our backupsets are intact and does not have any corruption. We can check both physical and logical corruption for the backupsets and make sure they are good and can be used for restore.

Following RMAN command will confirm the same

Physical corruption validation

 


RMAN> backup validate database archivelog all;

Starting backup at 08-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=13 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
...
...
...

Logical corruption validation


RMAN> backup validate check logical database archivelog all;

Starting backup at 08-FEB-16
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/u01/app/oracle/oradata/orcl/datafiles/users02.dbf
...
...
...

If above command doesn’t report any issues, we can be sure that backupsets are intact and can be used for restore/recovery.

Checking PREVIEW

We can use “RESTORE DATABASE PREVIEW” command in RMAN to get preview of all backup sets we are going to use and until what SCN this backup will be able to restore.

This command also gives us the SCN number until which we need to recovery the DB to get all datafiles out of fuzzy status


RMAN> restore database preview;

Starting restore at 08-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=264 device type=DISK
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
48 Full 209.01M DISK 00:00:43 08-FEB-16
BP Key: 48 Status: AVAILABLE Compressed: YES Tag: TAG20160208T053652
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvy4y2_.bkp
List of Datafiles in backup set 48
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/system01.dbf
4 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/undotbs01.dbf
8 Full 2955419 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users02.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 152.79M DISK 00:00:41 08-FEB-16
BP Key: 47 Status: AVAILABLE Compressed: YES Tag: TAG20160208T053652
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvy4yx_.bkp
List of Datafiles in backup set 47
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/sysaux01.dbf
6 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users01.dbf
9 Full 2955421 08-FEB-16 /u01/app/oracle/oradata/orcl/datafiles/users03.dbf

...
...
...
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
54 3.97M DISK 00:00:01 08-FEB-16
BP Key: 54 Status: AVAILABLE Compressed: YES Tag: TAG20160208T055120
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws8m9_.bkp

List of Archived Logs in backup set 54
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 43 2955401 08-FEB-16 2956061 08-FEB-16

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
55 80.50K DISK 00:00:00 08-FEB-16
BP Key: 55 Status: AVAILABLE Compressed: YES Tag: TAG20160208T055120
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws9xm_.bkp

List of Archived Logs in backup set 55
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 44 2956061 08-FEB-16 2956142 08-FEB-16
1 45 2956142 08-FEB-16 2956308 08-FEB-16
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 2955419
Recovery must be done beyond SCN 2955603 to clear datafile fuzziness
Finished restore at 08-FEB-16

If you check the last 4 lines, it will show the SCN number until which RMAN can restore the backups – SCN 2955419

From SCN 2955419 we need to start applying archive logs. We can identify the sequence number in which this SCN falls using below SQL


SQL> select sequence#, first_change#, next_change# from v$archived_log

where FIRST_CHANGE# <= 2955419 and NEXT_CHANGE# >= 2955419;

SEQUENCE#  FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
43         2955401       2956061

So we will need archives from sequence# 43 and we need to recover beyond SCN 2955603.

Trying restore/recover

Lets see I have few/all datafiles missing for database. I tried to bounce the DB and got following error


SQL> startup
ORACLE instance started.

Total System Global Area 943718400 bytes
Fixed Size 2931136 bytes
Variable Size 348128832 bytes
Database Buffers 587202560 bytes
Redo Buffers 5455872 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/datafiles/system01.dbf'

Lets try to restore


[oracle@advait ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Feb 8 06:56:26 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1429382412, not open)

RMAN> restore database;

Starting restore at 08-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=248 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=13 device type=DISK

skipping datafile 5; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
skipping datafile 7; already restored to file /u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/datafiles/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/datafiles/users01.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/datafiles/users03.dbf
...
...
...
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:01
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/2B0D04DCEFAF55F5E0531438A8C0EDC4/backupset/2016_02_08/o1_mf_nnndf_TAG20160208T053652_ccjvzlmt_.bkp tag=TAG20160208T053652
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 08-FEB-16

RMAN>

Once restore complete, lets try to recover


SQL> recover database;
ORA-00279: change 2955419 generated at 02/08/2016 05:36:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_%u_.arc
ORA-00280: change 2955419 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

As expected, its asking for recovery starting from sequence# 43.

We can restore the required archivelogs as well


RMAN> restore archivelog from sequence 43;

Starting restore at 08-FEB-16
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=43
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2016_02_08/o1_mf_annnn_TAG20160208T055120_ccjws8m9_.bkp
...
...
...
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
Finished restore at 08-FEB-16

RMAN>

Once archives are restored, we can carry on the recovery


SQL> recover database
ORA-00279: change 2955419 generated at 02/08/2016 05:36:52 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_cck2fwv
k_.arc
ORA-00280: change 2955419 for thread 1 is in sequence #43
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_02_08/o1_mf_1_43_cck2fwvk_.arc
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

In this case it old took sequence# 43 as NEXT_CHANGE# for sequence# 43 was 2956061 which is more than SCN 2955603 required to clear the fuzzy state. Rest of the redo information was also present in online redo logs so database did crash recovery while doing the open.

Hope this helps !!

Library Cache: Mutex X – Bug 20879889 – Fixed in 11.2.0.4

I recently encountered a bug related to MView log causing very high library cache: mutex x wait events.

I will brief about the debugging steps I tried and fix for the same.

Few things to note before I proceed:-

  • We observed huge wait events for library cache: mutex X whenever we performed flip to standby or when DB was bounced. I am implying that library cache was cold and didn’t had required cursor information and object handles.
  • Load on the database was very high. This includes very high number of sessions connected to database and high number of DMLs performed on same table by many sessions.
  • Table on which DMLs are performed is having MLOG created on that based on primary key. This is required as these changes and data needs to flow to downstream databases via MView (fast refresh). So around 10 downstream sites were registered on this MLOG.

Root Cause Analysis:

After looking at the wait events, we immediately started digging into the root cause. We wanted to understand the bottleneck which is causing these wait events and remove whatever it takes to reduce these wait events.

I started with checking top 10 wait events in last 20 mins from v$active_session_history


SQL>select * from (select event, count(1) from v$active_session_history
2 where sample_time > (sysdate - 20/1440) group by event order by 2 desc) where rownum < 10;

EVENT                                    COUNT(1)
---------------------------------------- ----------
library cache: mutex X                        50943
checkpoint completed                          15170
read by other session                          5487
row cache lock                                 4205
log file sync                                  3137
flashback buf free by RVWR                     1815
db file sequential read                        1675
log file switch completion                     1611
cursor: pin S wait on X                        1516

9 rows selected.

Clearly library cache: mutex X was way higher.

We can check who is causing library cache: mutex X by checking P1 and P2 of that wait event


SQL>select event, p1, count(1) from v$active_session_history where sample_time > (sysdate - 20/1440) and event = 'library cache: mutex X' group by event, p1 order by 3;

EVENT                                    P1         COUNT(1)
---------------------------------------- ---------- ----------
library cache: mutex X                    421399181          1
library cache: mutex X                   3842104349          1
library cache: mutex X                   3477606468          1
library cache: mutex X                   2432877226          1
library cache: mutex X                    955484820          2
library cache: mutex X                        37005         25
library cache: mutex X                   1412465886        297
library cache: mutex X                   2417922189      50615

8 rows selected.

As you can see max wait events are caused by P1 = 2417922189

P1 is idn – can be used for finding the cursor related to mutex

For library cache: mutex X

  1. P1 represents library cache hash bucket number (if idn <= 131072)
  2. P1 represents hash value of the library cache object under protection (if idn > 131072)

In our case hash value was 2417922189. So it represents library cache object.

We can check if this hash value belongs to a cursor (SQL) using v$sql


SQL>select sql_id from v$sql where hash_value = 2417922189;

SQL_ID
-------------
3b7aa6f81x44d

SQL>@sql
Enter SQL_ID:- 3b7aa6f81x44d
old 3: where sql_id = '&SQL_ID'
new 3: where sql_id = '3b7aa6f81x44d'

INSERT /*+ IDX(0) */ INTO "BOOKER"."MLOG$_FULFILLMENT_DEMAND_I"
(dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"WAREHOUSE
_ID","CO_WAREHOUSE_ITEM_ID") VALUES (:d,:o,to_date('4000-01-01:0
0:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1,:2)

As you can see this is DML on MLOG table. So clearly MLOG seems to be the bottleneck.

Immediate Action:

Wait events were making every thing stand still and nothing was getting processed. Since database availability was critical and we couldn’t afford to loose any more time because of wait events, our immediate action was to drop MLOG.

But since DB was having thousands of sessions and all stuck in library cache: mutex X, we cannot even get a lock to drop MLOG (not even with ddl_lock_timeout). Killing a session was not helping as they were keep coming back in no time.

So we decided to take quick bounce of DB (by keeping listeners down), dropped this MLOG and started listeners again.

This has fixed the issue and there were no wait events.

On down side, all downstream sites had to do complete refresh of MView followed by fast refresh as they got deregistered from MLOG. But it was OK as size of master table was not very big.

Patch Fix:

We also tried to investigate why an MLOG can cause this issue ? But were not able to get any insight.

MLOG is a table where primary key of changed records in main table will get inserted and when downstream table refreshes the data, these records will get deleted. So we don’t expect much higher size of MLOG. Unfortunately this is case it was 7.7GB (bigger than master table size).

Because the size of MLOG was too high, whenever a downstream database is doing fast refresh it has to update SNAPTIME$$ column in MLOG table which tells MLOG which rows are refreshed by downstream DB at what time. This update might be taking more time and holding enqueue locks. But inserts should not be blocked because of this update as it inserts a new record and doesn’t collied at enqueue level. Also, we were seeing these library cache wait events just after bounce or failover.

This is identified as a bug and Oracle has provided following patch as a fix for this issue

Patch 20879889: INSERT INTO MV LOG LEAVING TOO MANY OPEN CURSORS AFTER UPGR TO 11.2.0.4

Make sure you have this patch applied if you are using MLOG in your database.

Hope this helps !!

Reference:

https://support.oracle.com/epmos/faces/PatchDetail?requestId=19179589&_afrLoop=14055698364071&patchId=20879889&_afrWindowMode=0&_adf.ctrl-state=xqyu7jtft_77

 

 

Mutex: What do we know ?

In my previous article on Latches, I mentioned various things related to latches including different types and their behavior.

In this article I will describe similar things about mutexes.

Mutex are low level serialization / locking mechanism to protect memory structure inside library cache.

Why there was a change from Latches ? 

Mutex was introduced from 10.2 onwards and have been proved very efficient in managing library cache operations.

Mutex takes less memory then latches. Typically latch structure takes around 110 bytes on 32 bit system whereas mutex takes only 30 bytes. This variation is because of number of instructions it takes to acquire latch vs mutex. Latch takes around 150-200 instructions where as mutex takes 30-35 instructions. But the down side is that mutex gives less information about the waits and blockers compared to latches.

As we know the basics of library cache architecture, it consists of hash buckets and each bucket will contain linked list of library cache object handles. Whenever an access to library cache object happens, it is hashed to a bucket and corresponding latch should be obtained to traverse the chain in that bucket. Latch will be released when corresponding object is found or not found.

But there are only 67 library cache latches available to protect 131,072 buckets created by default in library cache. So single latch covers multiple buckets. This creates a false contention, meaning if 2 process are trying to access 2 different buckets protected by same latch, one of them have to wait until other completes traversing its bucket. So even though they are not colliding on same bucket they still will be blocked on each other because latch mechanism.

Further, if required object is found in library cache, process needs to pin that object while it is being used. Pin will basically protect object so that no other process can modify that object or that object will not be discarded from memory while you are accessing. When you are done, you need to take a latch again to unpin the object. These latches to pin and unpin needs memory allocation and deallocation to happen, which is expensive.

Compared to above process, mutex has many benefits

As against 67 latches to protect 131,072 buckets, Oracle introduced 131,072 mutexes to protect each bucket. So false contention is reduced drastically. False contention can still occur if 2 process want to access to 2 different objects which belongs to same bucket but its very rare.

After a mutex is obtained process will traverse the chain until it finds the required object. Once an object is found, a process needs to pin the object to access the same. But in case of mutex, there is no need for “latch: library cache pin”, instead mutex itself will act as a pin. Mutex acts as serialization mechanism to traverse a linked list as well as cursor pin structure. A mutex pin can be referenced by multiple sessions, providing that all sessions reference the mutex in shared mode only. The total number of sessions referencing a mutex in shared (S) mode is called reference count (ref count). The ref count for a mutex is stored in the mutex itself. Whenever a session wants to pin an object in shared pool, it increments this ref count of the mutex associated with that object. This is much faster and no need for any memory allocation or deallocation. Once process is done with that object, it will reduce the ref count. An object is not discarded from memory until ref count is zero.

Why not to increase number of latches ?

a. latches needs more memory and they are allocated upfront. As against Mutexes which are dynamic and are created when requested, latches are created when instance starts and its memory locations are externalized in view v$latch, v$latch_children

b. more latches you have, the more work you may have to do in some form of maintenance, reporting, or garbage collection. This will increase processing demand on server.

Now that we know why Oracle introduced mutex, lets move further in understanding more.

Shared, Exclusive and Examine mode:

Shared mode: Multiple sessions can access the memory structure protected by mutex in shared mode. Meaning every session has read access to structure protected by mutex. Every time a session access or acquire mutex in shared mode, ref count of mutex needs to be updated in mutex structure. Number of sessions accessing a mutex can be seen in lower bytes of P3 value of mutex wait event (cursor: pin S).

Exclusive mode: This mode is incompatible with all modes. Only 1 session can hold the lock in exclusive mode. In this mode, upper bytes of P3 value of mutex wait events represents the SID of holding session.

Examine mode: This mode indicates that mutex is in transition phase from shared mode to exclusive mode. During this time no other session can access or modify the mutex. In this mode, upper bytes of P3 value of mutex wait events represents the SID of holding session and lower bytes represents number of session holding mutex in shared mode.

Mutex Acquisition:

So how mutex acquisition works ?

We have seen in the past article on latches about how latch acquisition has worked. Mutex acquisition is similar to latch acquisition in that it tries for immediate gets and if its not able to acquire, it will spin followed by sleep (but sleep depends on oracle version).

Over past years with different releases of Oracle, mutex acquisition algorithm has changed drastically and I would say it got more stabilized with new releases. In Oracle 10.2, mutex was used for only pinning the object in library cache (only when _kks_use_mutex_pin=true) and there was no sleep.

Below algorithm applies to other mutexes which were introduced in 11g.

Oracle 10.2 – 11.1

Following is the mutex acquisition algorithm in 10.2 – 11.2

- Immediate gets mutex
      - spin gets mutex
            - yield()

Session will try to acquire mutex in immediate mode, if mutex is not available, it will spin. If still mutex is not available it will yield CPU, meaning, that process will be placed at the end of run queue but it will still be “ON CPU”. Because of this, CPU consumption was very high. Also because there was no sleep, wait interface was not able to record actual time waited for mutex acquisition. In AWR report, in top 5 timed events, we could see high number of waits, but total time waited used to be very low and CPU time always used to be close to DB time.

If CPU resources are not available OS will de-schedule such process (which is spinning and yielding CPU) and at that time that actual wait event for cursor: pin S gets recorded. But if a system has lot of CPU (like in many systems), that process will not even get de-scheduled, and Oracle thinks it is not waiting at all.

Therefore, in Oracle 10.2-11.1 the “cursor: pin S” wait time is the pure wait for CPU

For systems which do not have enough spare CPU, “cursor: pin S” used to be top wait events because it used to get de-scheduled from run queue and also whenever it used to spin and yield CPU consumption used to sky rocket. These are the systems where mutex issue was coming into light.

Because of these issue, Oracle released patch 6904068: High CPU usage when there are “cursor: pin S” waits.

This patch was released for 11.1 and later back ported to 10.2. With this patch, Oracle introduced underscore parameter – _first_spare_parameter. Default value of this parameter was 1 (centisec). This parameter provides a sleep time for mutex if its not acquired after first spin. So mutex behavior became similar to latch behavior but wait timeout of 10 ms was too high.

On better side, CPU consumption on system decreased and because of sleep time, this wait event (cursor: pin S) was shown up correctly on top 5 timed event (because total time waited was quiet accurate because of sleep). Parameter _first_spare_parameter was dynamic and if set to 0, mutex acquisition will behave without sleep (aggregated CPU usage).

But will all above discussion, question arises – if cursor: pin S is a shared mutex which can be acquired by multiple sessions concurrently why there would be blocking or wait events ?

Well couple of scenario when we can see this wait event

  1. If a mutex is in transition state “E” as mentioned above
  2. Everytime a session acquires mutex in shared mode, it has to update ref count of that mutex. That can cause contention.

What happened in 11.2 ?

Version 11.2.0.1

Behavior of mutex in 11.2.0.1 is same as 10.2. It was aggressive with no sleep. But it was visible as top event in-spite of no sleep calls.

This happened because Oracle counted wait time as time between first spin and successful mutex acquisition. So all the yield happening after the first spin was considered in waits. So total time waited was shown as high in AWR report and it was shown as one of the top timed events (even without sleep).

Version 11.2.0.2.2 PST

This version had a drastic change in behavior of mutex. Oracle came up with a exhaustive structure for controlling mutex called – mutex waits schemes.

Oracle introduced several underscore parameter to control behavior of mutex spin, yield and sleep.

Following underscore parameters were introduced starting this version

_mutex_wait_scheme – this parameter was introduced with 2 different values for 3 different type of mutex wait configurations.

0 - Always yield
1 - Always sleep for _mutex_wait_time
2 - Exponential back off with max sleep time of _mutex_wait_time

_mutex_spin_count – Number of times the process should spin. Default 255
_mutex_wait_time – amount of time process should sleep. Default 1ms

Along with this parameter, Oracle introduced following parameter to control yield and sleep of mutex

_wait_yield_mode – this defines if the process should yield first or sleep first. possible values are “yield” (default) or “sleep”

_wait_yield_hp_mode – defines high priority processes> default is SMON and VKTM

_wait_yield_sleep_time_msecs – defines how much a process should sleep before it yields again (in millisec). Default 1

_wait_yield_sleep_freq – number of yield cycles to do before it goes to sleep. default 100

_wait_yield_yield_freq – number of sleep cycles to do before it yields. default 20

_mutex_wait_scheme=0

I think in this scheme _mutex_wait_time is not applicable. Becuase sleep time depends on _wait_yield_sleep_freq. But I am not very sure.

With _mutex_wait_scheme=0 and default values for above _wait_yield* parameters above, mutex acquisition will work following way

- Immediate mutex gets
      - spin - _mutex_spin_count times
            - yield() - 100 times
                  - sleep - 20 times (20 cycles of 1 ms each)
            - yield() - 100 times
                  - sleep - 20 times (20 cycles of 1 ms each)

Using above underscore parameter we can vary the behaviour. Example if we change “_wait_yield_mode” to “sleep” instead of “yield”, oracle process will first sleep

- Immediate mutex gets
      - spin 
            - sleep - 20 times (20 cycles of 1 ms each)
      - yield() - 100 times
            - sleep - 20 times (20 cycles of 1 ms each)
      - yield() - 100 times

_mutex_wait_scheme=1

In this scheme _mutex_wait_time comes into picture. This is “always sleep” mode. So process goes to sleep after first spin, but wakes up after timeout of every _mutex_wait_time

So behavior will look like following

- Immediate mutex gets
      - Spin for _mutex_spin_count
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            - sleep for _mutex_wait_time
            ...
            ...

_mutex_wait_scheme=2

In this scheme _mutex_wait_time defines the max time a process should wait. This is “exponential backoff” scheme. Wait time increases exponentially until it reaches a max value set by _mutex_wait_time.

Other than this behavior differs in initial spin and yield cycle. It spins and yield 2 times initially before the sleep begins

So behavior will look like following

- Immediate mutex gets
      - Spin for _mutex_spin_count - 2 times
            - yield() - 2 times
                  - sleep - with exponential increase of timeouts

Starting from 11.2.0.2.2, _mutex_wait_scheme=2 is the default behavior

Hope this helps !!

References:

http://blog.tanelpoder.com/2008/08/03/library-cache-latches-gone-in-oracle-11g/

http://oracleinaction.com/latche-lock-pin-mutex/

https://andreynikolaev.wordpress.com

Latches: What do we know ?

Latches are low level serialization mechanism which protects memory areas inside SGA. They are light wait and less sophesticated than enqueues and can be acquired and released very quickly.

Latch acquisition does not involve any complex algorithm and is based on test-and-set atomic instruction of a computer processor.

Latch Classification:

Latch can be classified in multiple ways:-

Shared latch and exclusive latch:

Shared latch is the one which can be shared by multiple processes/sessions.

Example if a session wants to read a block in memory and at the same time other session also wants to read the same block in memory, they can acquire shared latch. Example of shared latch is “latch: cache buffer chain”. This latch was exclusive in older version and Oracle changed this to shared latch from Oracle 9i onwards.

Exclusive latch is the one which is acquired when a session wants to make modification to block or memory area. Exclusive latch can be held by only 1 session/process at a time and is not compatible with any other latch.

Both shared latch and exclusive latch are not compatible with each other. Process holding a shared latch will block other process which needs exclusive latch but will allow other process which needs shared latch on same memory area. Similarly process holding exclusive latch will not allow any other process which needs either shared latch or exclusive latch.

Example of exclusive latch is “library cache latches” in previous version. These were taken in exclusive mode even for traversing a hash bucket. These latches are no more present in 11g and they are replaced by mutex.

As per Andrey Nikolaev 460 out of 551 latches are exclusive in Oracle 11.2.0.2. In each new version, Oracle tries to make latches more sharable in order to reduce contention.

Another classification of latches is immedaite latch and willing to wait latch

Immedaite latch and willing to wait latch:

Immediate latches are the one that session try to acquire immediately without waits. If the latch is not available, session will not wait and may get terminated or check for another latch. Example of immediate latch is redo copy latch. This is immediate latch because Oracle only wants to know if anyone else is currently copying redo data to log buffer, but not who exactly is copying and where, as this does not matter to LGWR.

Willing to wait latches are the one which will wait if the latch is not available. These latches have little complex behavior then immediate latches. Most of the latches in oracle are willing to wait latches

Why do we see wait events on shared latches ?

After reading about shared latches and exclusive latches, one question comes to mind regarding shared latches.

If “latch: cache buffer chain” latch is a shared latch (past 9i version) and they dont block other shared latches, why do we see “latch: cache buffer chain” latch wait events even in latest version of Oracle ?

The answer to this is explained by Andrey Nikolaev. Andrey has given a very practical example of why this happens. When multiple processes are trying to acquire shared latch (example CBC latch), we should not see any wait events or process blocking each other. The moment another session try to acquire exclusive lock on the resource (where other sessions were having shared latch), exclusive latches are given higher preference than shared latch. So session with exclusive latch will get access to resource and it will block all other sessions having shared latch. This will form a chain of sessions willing to acquire shared latch. Strange part of this algorithm is that even after exclusive latch has been released by the processing holding it, other processes cannot acquire shared latch concurrently. They still continue to be in latch queue and they get access to shared latch one by one. First process in queue will get shared latch and once its done with the access, it will release the latch and post the next process that latch is available. This is one of the major reason why we see wait events on shared latches.

Process flow for latch acquisition

Following is the process flow for acquiring a latch

 - Immediate latch gets 
       - spin latch gets
             - Add process to queue of latch waits
                   - Sleep until posted

The last step in process flow (sleep until posted) is a changed behavior. Initially until 9i, Oracle used to wake up periodically to check if latch has been freed or not and if latch is still not avaialble go back to sleep. So process flow in 9i used to be following

 - Immediate latch gets 
       - spin latch gets
             - Add process to queue of latch waits
                   - Sleep for fixed time and wake up
             - immediate latch gets
                   - Sleep for fixed time and wake up
             - immediate latch gets
                   - Sleep for fixed time and wake up

This behavior has been change from 10g onwards and holding process will wakeup the waiter process after the latch becomes free. This has been explain well by Tannel Podder. Also behavior is explained in more details by ALEX FATKULIN.

Some times, holding process was not able to wake up the process waiting for latch because of bugs or lower kernel version. So older version of Oracle used to have some default timeout available so that in case holding process miss the wakeup call, it will not wait indefinitely and will wakeup after timeout. Oracle has introduced a parameter _enable_reliable_latch_waits={true|false}, which alters this behavior. If this is set to true then no timeout is added and holding process continue to sleep until it gets posted by holding process. False represents the behavior otherwise.

Only latch which is exception to above process flow is “process allocation” latch. This latch does not depends on holding process to post when latch is free. It wakes up preriodically to acquire latch.

Latch Behavior

Oracle has introduced few parameters to control behavior of latch. We will discuss brief about those parameters and how they affect shared latch and exclusive latches

When process try to acquire latch, it attempts to acquire latch in immediate mode. If latch is not available in immediate mode, it will spin for defined number of times and try again. If still latch is not free, process will go to sleep.

Process Spin:

Spinning is a process of consuming/burning CPU so that process will stay “ON CPU” and not to get descheduled from CPU cycles. During spinning, process will burn CPU for few micro seconds with the hope that after passing that much time, latch will be available for it to acquire. This does increase CPU consumption, but it saves time as it may avoid the needing for the process to sleep (which is expensive as it involves context switches).

Number of times a process spins to acquire latch depends on type of latch.

Exclusive latch:

For exclusive latch, this “can be” controlled by _spin_count but needs database bounce. I said “can be” because exclusive latch spins ar actually decided by “spin” column in x$ksllclass table.


SQL>select indx,spin from x$ksllclass;

INDX SPIN
---------- ----------
0 20000
1 20000
2 20000
3 20000
4 20000
5 20000
6 20000
7 20000

8 rows selected.

There are 8 classes of latches (as indicated by indx column) that we will discuss later in this article. By default all latches belongs to class 0.
If we want to change spin count for exclusive latches, we need to change value of SPIN column for class 0. This can be done by changing _spin_count and bouncing the instance (but that will change spin count for all classes), or by setting _latch_class_0 parameter (which will change spin count for only class 0). We have similar parameter to change spin count for other classes (_latch_class_[0-7]).

So changing _spin_count is not a good idea. Instead we can move a specific latch for which we want to change the spin count to another class (1-7) and change specific underscore parameter (_latch_class_[1-7]).

By details _spin_count parameter is not applicable to exclusive latches as default value of _spin_count is 2000 where as exclusive latch spin for 20000 times as mentioned in above table x$ksllclass.
But changing _spin_count will make it applicable for exclusive latch as well.

Shared Latch:

For shared latch, number of times process spins is _spin_count * 2. This has been proved by Andrey Nikolaev. Also, _spin_count parameter is applicable to shared latches by default. So since default value of _spin_count is 2000, shared latches spins 4000 times (2000 * 2).

Diagnosing latch contention:

I am not going to mention much here because Tannel Podder has already written great script – latchprof.sql and latchprofx.sql which can be used to analyze latch wait events.

Tannel has also written great article on how to diagnose latch wait events – http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

In next article, I will try to cover mutex.

Hope this helps !!

Reference:

https://andreynikolaev.wordpress.com

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

Brief about Workload Management in Oracle RAC

This is a brief article about workload management in RAC. I tried to cover different components of workload management in RAC and how they are configured at client side or server side. I haven’t gone into details of configuration steps but just mentioned in brief about how it can be done.

Readers are advised to refer Oracle documentation to understand details about configuration of workload management.

Workload management on RAC

There are 2 major components of workload management:

  1. Failover – if connection to one instance fails, Oracle should failover the connection to another instance
  2. Load Balancing – Workload on RAC instances should be distributed equally

Failover can be connect time or run time. Similarly load balancing can be achieved during connect time or run time.

We can also configure these components either on client side or on server side.

I tried to put workload management in RAC in single block diagram to get high level overview. Following figures gives a summary of configuring workload management in RAC.

workload

Lets check how to configure each of these components.

Failover

We can achieve failover at connect time or at run time. So depending on how we want to achieve failover (during connect time or run time), we can configure the same on client side or on server side

Connect time failover, Client side

Connect time failover can be configured on client side as connection failover happens if instance is down before it can even get connected. So logically its not possible to have it on server side (because that will need connection to complete and in that case it wont be connect time failover).

This is achieved on client side using FAILOVER=ON parameter in TNS string.
Example:

ORCL=
     (DESCRIPTION=
         (ADDRESS_LIST=
         (FAILOVER=ON)
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node2-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node3-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node4-vip) (PORT=1521))
     )
    (CONNECT_DATA= (SERVICE_NAME= ORCL))
)

 

Run time failover, Client side

At run time, we can achieve failover using Transparent Application Failover (TAF).
TAF can be configured on client side in TNS string using FAILOVER_MODE parameter.
Example:

ORCL=
     (DESCRIPTION=
         (ADDRESS_LIST=
         (FAILOVER=ON)
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node2-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node3-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node4-vip) (PORT=1521))
     )
     (CONNECT_DATA= (SERVICE_NAME=ORCL)
     (FAILOVER_MODE=(TYPE=select)(METHOD=basic))
)

 

If you check above TNS string, we have FAILOVER_MODE parameter and it specifies the failover type and method. If FAILOVER_MODE is specified then in case of instance outage, existing connected sessions will automatically failover at run time to other existing instances. TAF has more details then specified here. You can check Oracle documentation or reference links in this article for complete details about TAF.

Run time failover, Server side

Same TAF implementation can be done on server side as well. This is done as part of service management in RAC.
We can use SRVCTL to configure services on RAC add TAF parameters.

[oracle@orcl_node1 ~]$ srvctl add service -d orcl -s test -r orcl1 -P BASIC -e SESSION -m BASIC
[oracle@orcl_node1 ~]$ srvctl start service -d orcl -s test
[oracle@orcl_node1 ~]$ srvctl status service -d orcl -s test
Service test is running on instance(s) orcl1
[oracle@orcl_node1 ~]$ srvctl config service -d orcl -s test
Service name: test
Service is enabled
Server pool: orcl_test
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: 
Runtime Load Balancing Goal:
TAF policy specification: BASIC
Preferred instances: orcl1
Available instances:
[oracle@orcl_node1 ~]$

We can also use Fast Connection Failover (FCF) using Fast Application Notification (FAN) events in OCI clients to get notifications about instance availability. Based on these notification, clients can reconnect to available instances.

Load Balancing

We can achieve load balancing at connect time or at run time. Depending upon when we want to achieve load balancing (connect time or run time), we can configure load balancing on client side or on server side.

Connect time load balancing, Client side

We can achieve connect time load balancing on client side using LOAD_BALANCE=ON parameter in TNS string.

Example:

ORCL=
     (DESCRIPTION=
         (ADDRESS_LIST=
         (LOAD_BALANCE=ON)
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_node1-vip) (PORT=1521))
     )
    (CONNECT_DATA= (SERVICE_NAME= ORCL)
)

LOAD_BALANCE parameter is set to ON by default and we do not have to specify explicitely. However, putting LOAD_BALANCE=OFF will disable load balancing. Oracle picks ramdom hosts from address list and try to load balance connections to database instances. With 11.2, Oracle introduced SCAN listener which provide host IPs in round robin fashion. So with single SCAN alias in TNS names, connections to different hosts are balanced automatically. This is going to deprecate LOAD_BALANCING parameter in TNS. Example:

ORCL=
     (DESCRIPTION=
         (LOAD_BALANCE=ON)
         (ADDRESS= (PROTOCOL=TCP) (HOST=orcl_scan) (PORT=1521))
     )
     (CONNECT_DATA= (SERVICE_NAME= ORCL)
 )

 

Connect time load balancing, Server side

We can enable server side load balancing using CLB_GOAL service attribute.
Oracle has introduced load balanving advisory in 10g which keeps track of loads on individual instances. Having dynamic registration keeps all listeners aware of load profile of each instances. We need to set remote_listener to tns alias containing all virual IP address of all nodes in cluster. Even with SCAN listener, we need to keep SCAN VIP in remote_listener parameter.

CLB_GOAL stands for connect time load balancing goal. It is used to define expected session duration for the service. For example if we have OLTP service and we expect lots of short sessions which last for very short time (few secs to few mins), then we can set CLB_GOAL for that service as SHORT. If service is expected to serve sessions which are going to be connected for longer duration (few mins to hours), we can set CLB_GOAL to LONG. Setting CLB_GOAL will instruct listener to route connections based on metrics. Possible metrics are load per node (based on CPU run queue) (CLB_GOAL=short) or number of current connections (CLB_GOAL = long).

  • If CLB_GOAL is short then Oracle considers load per node (based on CPU run queue) as metrics and route connection to host where load is less.
  • If CLB_GOAL is long then Oracle considers number of connections to instance as metrics and route connection to host where number of connections are less.

Example:

[oracle@orcl_node1 ~]$ srvctl modify service -d orcl -s test -j SHORT
[oracle@orcl_node1 ~]$ srvctl config service -d orcl -s test
Service name: test
Service is enabled
Server pool: orcl_test
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: SHORT
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Preferred instances: orcl1
Available instances:
[oracle@orcl_node1 ~]$

Run time load balancing, Server side

We cannot have client side load balancing during run time. This is because when we consider run time load balancing, each transaction needs to be balanced as against each connection.
Load balancing advisory serve as basic for runtime connection load balancing. Using dynamic service registration services are registered with all listeners. PMON of each instance updates the load profile to all listeners. Since listeners knows the load profile of all instances sessions are directed to most appropriate instance depending on the goal of runtime load balancing. Connection allocation is based on current performance level provided by the database instances as indicated by load balancing advisory FAN events. This provides load balancing at the transaction level instead of load balancing at the time of initial connection.

Service level of instances are analyzed based on runtime load balancing goal

  • Service time (internet web processing)
  • Throughput (batch processing)

Above runtime load balancing goals can be set using GOAL parameter of server (dont get confused with CLB_GOAL parameter, which is for connect time load balancing).

We can set this parameter GOAL on server side for each service using SRVCTL.

Once we set this parameter to either GOAL_SERVICE_TIME or GOAL_THROUGHPUT, Oracle will balance the load using following metrics

  • If GOAL_SERVICE_TIME is used, Oracle will check the service time ie. how fast an instance if serving a single transaction. Oracle will have these metrics for each of the instances and connection for a service will be diverted to an instance where service time is best. This is mainly for OLTP transactions.
  • If GOAL_THROUGHPUT is used, Oracle will check the throughput metrics ie. which instance is doing max amount of work in least time and forward the instance which is having best throughput. This is mainly for batch processing.

Example:

[oracle@orcl_node1 ~]$ srvctl modify service -d orcl -s test -B SERVICE_TIME
[oracle@orcl_node1 ~]$ srvctl config service -d orcl -s test
Service name: test
Service is enabled
Server pool: orcl_test
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SESSION
Failover method: BASIC
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: SHORT
Runtime Load Balancing Goal: SERVICE_TIME
TAF policy specification: BASIC
Preferred instances: orcl1
Available instances:

Reference:

http://www.oracle.com/technetwork/database/features/oci/taf-10-133239.pdf

http://docs.oracle.com/cd/B19306_01/rac.102/b14197/hafeats.htm

How many checkpoints in Oracle database ?

This Question came to my mind when I was discussing “direct path reads” with DBA candidates. I was surprised that many DBAs were not aware of object level checkpoint that happens while doing direct path read. So I thought many DBAs may not be knowing different level of checkpoints that happens in the database and hence the question.

Well, the answer is 7 (as per my knowledge). Oracle does 7 different checkpoints at various stages. Lets check out what are those checkpoints and at what point they happen

  1. Full checkpoint
  2. Thread checkpoint
  3. File level checkpoint
  4. Object level checkpoint
  5. Parallel query checkpoint <– BTW. This is the checkpoint that happens during direct path reads
  6. Incremental checkpoint
  7. Checkpoint during log switch

Full Checkpoint

This happens when DBA explicitly issues checkpoint command using “alter system checkpoint”. When this happens, all dirty blocks from (all instances in case of RAC) db_cache are written to disk. This includes committed as well as uncommitted data blocks.

This checkpoint also happens when DBA shutdown database cleanly using “shutdown immediate” or puts entire database in begin backup mode using “alter database begin backup”

Thread Checkpoint

Thread checkpoint is basically a full checkpoint in single instance database. So redo thread is associated with an instance. Thread checkpoints basically write all dirty blocks of a single thread or instance to a database. In case of RAC when a checkpoint is done for a specific single instance, its called thread checkpoint. This is done using “alter system checkpoint local”

File level Checkpoint

File level checkpoint is writing dirty blocks associated with set of files belonging to a tablespace. This happens when we put a tablespace in begin backup mode or when we take a tablespace offline or when we make tablespace readonly. Oracle writes all dirty blocks associated with datafiles of that tablespace to database before it changes the status of that tablespace.

Object level Checkpoint

All the dirty blocks that belong to specific object is written to database by DBWR process. This happens when you perform following action on the object (example table or index):

  • Drop table
  • truncate table
  • drop index
  • drop table purge

You might be thinking if we are dropping an object, why Oracle has to write its blocks to database. 2 reasons

  1. Oracle writes these blocks to database “before” performing above DDL tasks
  2. It’s required for recovery purpose. In future if you have to restore and recover database, Oracle needs to have its previous blocks so that it can roll forward and rollback.

Parallel query checkpoint

Whenever you are reading queries using parallel workers, Oracle does direct path reads and reads data from a datafile directly into PGA bypassing SGA. Starting from 11g, direct path reads also happens for full table scans where table size is larger than _small_table_threshold parameter.

Imagine we have a session which connected prior and did some DML on a table in SGA (buffer cache) and committed the changes. Since checkpoint doesn’t happen as soon as we commit the changes what happens when another session connects immediately and do a full table scan or parallel scan of that table. How will it see the latest data ?

This is where parallel query checkpoint comes in. When you run full table scan or parallel query scan, you will see direct path reads wait event but in the beginning, you will also see enq: KO fast object checkpoint wait event. This will checkpoint any blocks that belong to the object you are doing direct path read so that latest change goes into datafile.

Incremental Checkpoint

Prior to Oracle 8i, Oracle used to do checkpoint during log switch and nothing before that. So during log file switch, Oracle has to write lot of blocks to disk and we will see sudden spike in IO. Also, this has an effect of increasing recovery time if checkpoint hasn’t happen for until we are at the end of log file and database has crashed.

Starting 8i, Oracle started doing incremental checkpoints time to time. This logic has also evolved from Oracle 8i version till now and different parameters control this behavious in latest versions compared to older version.

In prior releases, we used to have log_checkpoint_interval and log_checkpoint_timeout parameters which used to control duration of incremental checkpoints that should happen in order to meet recovery SLAs. In later release, Oracle provided fast_start_mttr_target and fast_start_io_target parameters. These parameters takes our SLA timings and internally Oracle decides how frequently it has to take incremental checkpoints.

We have another situation where Oracle has to go for incremental checkpoint even though above parameters (fast_start_mttr_target or fast_start_io_target) has not met the condition yet. This happens when Oracle is not able to find any free blocks in buffer cache. At this point Oracle has to flush least recently used blocks to datafiles in order to make room for new buffers comming in. By default Oracle scans _db_block_max_scan_pct of blocks before it decides to flush LRU blocks to datafiles. If its not able to find required number of free blocks even after scanning _db_block_max_scan_pct blocks, it will go for incremental checkpoint starting with LRU blocks.

Checkpoint logic is much more complex that what I explained here. But objective of this article was to just introduce  different types of checkpoints so I am not covering details of checkpoint algorithm here.

Checkpoint during log switch

This is the most obvious checkpoint. It happens whenever log switch happens. But note that during log switch only dirty blocks whose information is protected by that log file will be written to datafiles. So not all dirty blocks are written during log switch.

Hope this helps !!

Reference

https://bdrouvot.wordpress.com/2015/04/30/direct-path-read-and-enq-ko-fast-object-checkpoint/

Coverting MySQL database character set to UTF8

Recently I was engaged to convert the character set for few MySQL databases from latin1 to UTF8 collation utf8_general_ci. This article describes the approach taken for doing the same.

First I will describe various levels at which we can change the character set and collation and then we will see how to convert existing data in a database to required character set.

Backup your database:

Before even thinking about changing character set for your database, take backup of database using whatever backup method that is tested by you – mysqldump, mysql-enterprise-backup, export etc

In case something goes wrong, we can always have data and recreate requried table/database etc.

Make sure your backups and restore methods are proven, meaning that you have sucecssfully done restore of tables/database etc

Setting Character set at various level:

We can see following parameters for character sets

<pre>root [mysql] >show variables like '%character%set%';
+--------------------------+---------------------------------------------------------------+
| Variable_name            | Value                                                         |
+--------------------------+---------------------------------------------------------------+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | latin1                                                        |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | latin1                                                        |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.00 sec)

character_set_client – The character set for statements that arrive from the client. If client is not setting any character set while connecting, this character set will be used for statements send by client. Else value set by client during connection will override this value

character_set_connection – The character set used for literals that do not have a character set introducer and for number-to-string conversion.

character_set_database – Character set used by default database. This character set will be used whenever we change database on server and if that database does not have any character set defined.

character_set_filesystem – This character set is used to interpret string literals that refer to file names at file system level, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements.

character_set_results – The character set used for returning query results such as result sets. If client has used character set in its connection, then this value will not be used for returning the result.

character_set_server – Character set defined at the server level. Any new database created will used this character set, unless we are defining character set at database level

character_set_system – The character set used by the server for storing metadata infomration. Example – the return values of the USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE(), VERSION() etc will be retuned in character set assigned to this variable.

character_sets_dir – The directory where all character sets are installed.

We can set character set at following level

Server Level:

We can do this by setting parameter character_set_server in our main my.cnf file. But this needs a bounce. Once MySQL server is bounced it will pick new value of this parameter and new character set will be the one we set for this parameter. But this does not change anything in existing data or objects. Only new database creation will take this effect.

Database Level:

We can alter any database on our MySQL server and change the character set to UTF8. We can use following command:-

ALTER DATABASE <db_name> DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Again, this does not affect existing data or objects. This will only take affect for future objects/tables that we create in this database.

Table Level:

We can use alter table command to set the character set for a table.

ALTER TABLE <table_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

So, if you have many tables in database, you can use following command to dynamically generate a script which can be used to set character set for all required tables in database

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<db_name>';

But this will not change existing columns/data in the table. This will take effect only for new columns getting added to the table.

Changing Character set of existing data:

Above steps are required for setting the character set at various level so that future objects and data will be created in UTF8

Now, for changing character set for existing data, we need to change character set for every text columns of every table in database where we want to change it

We can use multiple approaches for converting character set for existing data and percona blog has provided a very good reference for these methods along with advantages and disadvantages –

Here, I would like to highlight difference between 2 methods that can be used to convert character sets

Doing at table level:

Following command can be used to convert character set at table level

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This will take care of converting the character set for all columns in table. problem with this approach is that, if you have TEXT columns (TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT, VARCHAR, ENUM), it can end up changing the data type for these columns. Example of the same is giving in above percona blog where TEXT got converted to MEDIUMTEXT

Best and careful way to convert character set is to do it for each text column separately on each table.

You can use following command to change the character set for column

alter table <table_name> change <column_name> <column_name> CHARACTER SET UTF8;

Example:

alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;

In above command/example, we used column name twice. That is required.

But wait, can above approach convert data correctly to required character set?
It may not. Check this article which describes issues we face when we directly try to convert the character set of the column.

Sometimes directly converting can grabble the data. Best way is to convert to binary equivalent and then convert the data type and character set of the column to required once. Following command can be used

alter table P1_TABLE change COL1 COL1 BLOB;
alter table P1_TABLE change COL1 COL1 TEXT CHARACTER SET UTF8;

Since, my column had the text data type, its equivalent is BLOB. Following are the binary equivalent of various text data types

  • CHAR –> BINARY
  • TEXT –> BLOB
  • TINYTEXT –> TINYBLOB
  • MEDIUMTEXT –> MEDIUMBLOB
  • LONGTEXT –> LONGBLOB
  • VARCHAR() –> VARBINARY() (Use same data length)

Automating character set conversion:

You can create simple script with all required commands using following dynamic SQL

Note, that if CHARACTER_SET_NAME is NULL in COLUMNS table for columns, it means that those columns are numbers or binary or of data types which does not need character set conversion

Following dynamic SQL can be used to create automatic script

select concat("alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," BLOB;",
"alter table ",TABLE_NAME," change ",COLUMN_NAME," ",COLUMN_NAME," ",IF(DATA_TYPE in ('varchar','char'),concat(DATA_TYPE,"(",CHARACTER_MAXIMUM_LENGTH,")"),DATA_TYPE)," CHARACTER SET utf8;")
from information_schema.columns
where TABLE_SCHEMA = '<db_name>'
and DATA_TYPE <> 'ENUM'
and CHARACTER_SET_NAME is not null;

ENUM case is different. You need to specify all ENUM value when you convert back to required data type and character set format. Check wordpress codex blog for more details.

Disclaimer: Please do not run above SQLs directly on production without testing. Make sure you write your own SQL as per your need based on database and objects present in your database.

Hope this helps !!

References:

https://codex.wordpress.org/Converting_Database_Character_Sets

Converting Character Sets

http://www.bothernomore.com/2008/12/16/character-encoding-hell/