How to change default character set to UTF-8 in mysql 5.6

Step 1:- If mysql is already running stop it.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]

Step 2:- Add the following lines in my.cnf file and start mysql.

[root@server1 ~]# vi /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8


--save & exit (:wq)

[root@server1 ~]# service mysql start

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)


mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Specify Character Settings per Database:
mysql> create database soumya
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)

Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

--Done

How to find out tablespaces with free space < 15%

SQL> set pagesize 300
SQL> set linesize 100
SQL> column tablespace_name format a15 heading 'Tablespace'
SQL> column sumb format 999,999,999
SQL> column extents format 9999
SQL> column bytes format 999,999,999,999
SQL> column largest format 999,999,999,999
SQL> column Tot_Size format 999,999 Heading 'Total Size(Mb)'
SQL> column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
SQL> column Pct_Free format 999.99 heading '% Free'
SQL> column Max_Free format 999,999,999 heading 'Max Free(Kb)'
SQL> column Min_Add format 999,999,999 heading 'Min space add (MB)'
SQL>
SQL> ttitle center 'Tablespaces With Less Than 15% Free Space' skip 2
SQL> set echo off
SQL>
SQL> select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
  2  sum(a.sumb/1024) Tot_Free,
  3  sum(a.sumb)*100/sum(a.tots) Pct_Free,
  4  ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
  5  from
  6  (
  7  select tablespace_name,0 tots,sum(bytes) sumb
  8  from dba_free_space a
  9  group by tablespace_name
 10  union
 11  select tablespace_name,sum(bytes) tots,0 from
 12  dba_data_files
 13  group by tablespace_name) a
 14  group by a.tablespace_name
 15  having sum(a.sumb)*100/sum(a.tots) < 15
 16  order by pct_free;

                         Tablespaces With Less Than 15% Free Space

Tablespace      Total Size(Mb) Total Free(Kb)  % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
SYSAUX                     500         24,448    4.78                 61
SYSTEM                     710         37,504    5.16                 83

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How STARTUP works in oracle database

Startup consists of 3 phases.

First of all, in order to issue the startup command you must be logged into an account that has sysdba or sysoper privileges such as the SYS account.
When oracle tries to open a database using startup command it goes through 3 phases.

1.NOMOUNT
2.MOUNT
3.OPEN

1. NOMOUNT Stage:- When we issue the startup command, oracle first enters into nomount stage.In this stage it reads the initialization parameter file(spfile) in $ORACLE_HOME/dbs location.
Lets assume database sid is orcl. So in order to start the orcl instance oracle would first look for spfileorcl.ora . if it cant find the file, then it looks for spfile.ora if not found
initorcl.ora.

After the parameter file is read by oracle, memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started.
Together, these processes and the associated allocated memory are known as  Oracle instance. Now once instance has started its considered to be in nomount stage.

2.MOUNT Stage:-When the startup command steps into mount stage, it first reads the spfile/pfile to know the control file location and read controlfile's content.
From control file's content it gets to know about
a.The database name
b.The location of datafiles and  redo logfiles
c.Current log sequence number.
d.Time stamp of database creation.
e.Checkpoint information.

In this stage , oracle confirms the location of the datafiles, but does not open them. Once the datafile locations have been identified, the database is ready to be opened.



3.OPEN Stage :- The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it
has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent. Finally users can access the database now.


Reference:- http://www.dba-oracle.com/concepts/starting_database.htm
Reference:-https://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm#i1006285




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Shell script for Webfile Backup for webserver

#  mkdir /backups/web_backup/

#  vi /backups/webbackup.sh 
#!/bin/bash

export path1=/backups/web_backups
date1=`date +%y%m%d_%H%M%S`

/usr/bin/find /backups/web_backups/* -type d -mtime +3 -exec rm -r {} \; 2> /dev/null

mkdir $path1/$date1

cp -r /var/www/html $path1/

cd $path1/html

for i in */; do /bin/tar -zcvf "$path1/$date1/${i%/}.tar.gz" "$i"; done

if [ $? -eq 0 ] ; then
cd
rm -r /backups/web_backups/html
fi
done

:wq (save & exit)

Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/webbackup.sh > /dev/null


Please share your ideas and opinions about this topic. 
If you like this post, then please share with others.

Please subscribe on email for every updates on mail.