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.

How to find out Different SQL Server Property?

SELECT
SERVERPROPERTY('MachineName') AS HostName
,SERVERPROPERTY('InstanceName') AS InstanceName
,SERVERPROPERTY('Edition') AS EditionInfo
,SERVERPROPERTY('EditionId') AS EditionID
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductType
,SERVERPROPERTY('EngineEdition') AS EngineEdition
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime
,SERVERPROPERTY('IsClustered') as IsClustered



Description:-
MachineName:- It shows  the hostname of the machine.
InstanceName:- It shows instance name if it is not default.In case of default it returns Null.
Edition :- It SQL Server edition installed on machine.
EditionId:- It shows Edition Id
ProductVersion:- It shows Product version.
ProductLevel:- It shows Level of the version of SQL Server instance
'RTM' = Original release version
'SPn' = Service pack version
'CTP', = Community Technology Preview version
EngineEdition:- It shows the engine edition.
1 = Desktop
2 = Standard
3 = Enterprise
4 = Express
5 = SQL Azure
ResourceLastUpdateDateTime:- It shows the date and time when the Resource database was last updated.
IsClustered:- It shows if  instance is configured in a failover cluster.
1 = Clustered.
0 = Not Clustered.
NULL = Input is not valid, or an error.

Please share your ideas and opinions about this topic.

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

Flashback table feature on Oracle 11g

FLASHBACK TABLE statement is used to restore an earlier state of a table in the  event of human or application error.Though It entirely depends  on the amount of undo data that is
present in the system.Also we cant flashback a table to earlier stage in case of any ddl operation that changes the table structure.flashback on is not required in order to do
the flashback table.
You cannot 'flashback table to before drop' a table which has been created in the SYSTEM tablespace.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create user soumya identified by soumya default tablespace users;

User created.
[oracle@server1 ~]$ conn soumya/soumya
SQL> create table test ( id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test;

        ID
----------
         1
         1
         1

SQL> drop table test;

Table dropped.


SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNA/E4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:23:47

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

        ID
----------
         1
         1
         1

Now lets try to flashback a table which resides in system tablespace.
SQL> show user
USER is "SYS"
SQL> create table flash (id number);    

Table created.

SQL> insert into flash values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_recyclebin;

no rows selected

SQL> show recyclebin;
SQL>

SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

So, if a table resides in system tablesapce and if its dropped it doesnt stay in recylebin, rather its being dropped permamnently from the database.

To query a dropped table:-
SQL> drop table test;

Table dropped.
SQL>  show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:37:02
While querying the recycle bin, make sure the system generated table name is enclosed in double quotes, else it will throw error.


SQL> select * from BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 ;

SQL> select * from "BIN$Ig9YoNBCE4/gUKjAZgINCg==$0" ;
        ID
----------
         1
         1
         1


Now lets try to insert some data inside the dropped table which is inside recyclebin.
SQL> insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2);
insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


So we can not perform DDL/DML over objects in Recycle Bin.

Flashback a table in the past to a specific point in time:-
SQL> set time on
16:52:51 SQL>
16:52:52 SQL>  alter table test enable row movement ;

Table altered.

16:53:21 SQL> select * from test;

        ID
----------
         1
         1
         1
         2

16:53:37 SQL>
16:53:44 SQL> update test set id=100 where id=1 ;

3 rows updated.

16:54:00 SQL> commit;

Commit complete.

16:54:04 SQL> select * from test;

        ID
----------
       100
       100
       100
         2

Now lets flashback the table
17:25:05 SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2015-10-14 16:54:01' ,'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

17:25:10 SQL>  select * from test;

        ID
----------
         1
         1
         1
         2

17:25:17 SQL>



To rename an object while flashing back from recyclebin:-
17:37:39 SQL> create table test11(id number);

Table created.

17:37:58 SQL>  insert into test11 values (1);

1 row created.

17:38:05 SQL> commit;

Commit complete.

17:38:09 SQL> drop table test11;

Table dropped.

17:39:21 SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST11           BIN$Ig9YoNBIE4/gUKjAZgINCg==$0 TABLE        2015-10-14:17:39:21

17:39:57 SQL> flashback table "BIN$Ig9YoNBIE4/gUKjAZgINCg==$0" to before drop rename to test12 ;
Flashback complete.

17:40:11 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST12                       TABLE

17:50:18 SQL> select * from test12;

        ID
----------
         1




Please share your ideas and opinions about this topic.

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

ORA-01466: unable to read data - table definition has changed

ORA-01466: unable to read data - table definition has changed

01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
//         w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute

While selecing a table for a specific point of time i faced the error.

15:23:07 SQL> SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS')
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


Reason:- There could be few reasons behind it.
1. DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for
the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

2.You need to have the time of your client (where you run sqlplus) set to a later (or same) value than the time of your database server.Else such error could generate.
3. This could be caused by a long running snapshot. Try committing or rolling-back all outstanding transactions and try again.
4.It also could happen if the table is newly created .


Please share your ideas and opinions about this topic.

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