SET MYSQL PASSWORD

SET PASSWORD for root@localhost = PASSWORD('root');

SET PASSWORD for root@localhost = PASSWORD('');



UPDATE mysql.user SET Password=PASSWORD('') WHERE User='root';

FLUSH PRIVILEGES;

kill oracle sessions

select count(1),user from v$session group by user;
select 'alter system kill session '||''''||sid||','||serial#||''''||'immediate;' from v$session
select * from v$locked_object; ----- get the sid
select sid,serial# from v$session where sid=134; ------get serial#
alter system kill session '134,2386';

Increase the session in Oracle

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

E.g.
alter system set processes=1000 scope=spfile;
alter system set sessions=1500 scope=spfile ;


processes=500
sessions=555
transactions=610


alter system set transactions=610 scope=both;

AWR report

run the script in sqlplus prompt and follow the instructions
@?/rdbms/admin/awrrpt.sql

Restore a single table from a whole db backup mysql

suppose we have dump.sql file for backup of whole database

fire following command
grep -n 'Table structure' dump.sql

suppose the table you want is shown with 40th line
and the table next to it is 62th line

extract the table using following command
sed -n '40,61 p' dump.sql > t2.sql

then restore table using mysql utility using t2.sql

rename database in sqlserver if database is running processes

use master;

Go

alter database M2wServices_tr_sh set single_user with rollback immediate;

GO

alter database M2wServices_tr_sh modify name = M2wServices_tr ;

GO

alter database M2wServices_tr set multi_user;

Go

set mysql password for root user if forget

service mysql stop
mysqld_safe --skip-grant-tables


open other putty login

mysql --user=root mysql
ocg#81@2012

use mysql
update user set Password=PASSWORD('mod#db#543') where user='root';
flush privileges;
exit;

create data base link in oracle

CREATE PUBLIC DATABASE LINK name_of_link
CONNECT TO oracle_username
IDENTIFIED BY password -- double quotes here
USING 'database_sid' -- single quotes here ;

eg.
CREATE PUBLIC DATABASE LINK talkies
CONNECT TO username talkies identified BY "talkies123"
USING 'vxmldb1';

/
-- test the dblink
select * from dual@name_of_link
/

Remote server backup of sqlserver

Go to services.msc and loginn as administrator for sqlserver services


map the drive of the other server to your database server

10.11.233.20\d$

once mapped ,run folowing command in sqlserver query prompt:


EXEC xp_cmdshell 'net use D: \\ip adress of remote server\d$ /user:username password'

eg.

EXEC xp_cmdshell 'net use D: \\10.11.233.20\d$ /user:Administrator atpl@123'


now you can schedule the backup using following command

DECLARE @MyFileName varchar(50)
SELECT @MyFileName = (select 'D:\SQLBACKUP\m2wservices_tr_'+replace((replace((replace((convert(varchar(50),GetDate(),20)),'-','')),':','')),' ','') +'.bak')
BACKUP DATABASE [m2wservices_tr] TO DISK=@MyFileName WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10

to delete the entry for a drive
EXEC XP_CMDSHELL 'net use D: /delete'

JOINS ORACLE

SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- -----------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)




SQL> desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> desc salgrade
Name Null? Type
----------------------------------------------------- -------- --------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER



equijoin

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno;


non-equijoin

select e.ename,e.sal,j.grade
from emp e,salgrade j
where e.sal between j.losal and j.hisal;

outer joins

select e.empno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);

selfjoin

select worker.ename||' '||'works for'||' '||manager.ename
from emp worker,emp manager
where worker.empno=manager.mgr;

create new user in oracle with separate datafiles and tablespace

create tablespace myatpl datafile 'D:\app\Administrator\oradata\vxmldb\myatpl01.dbf '
size 200m autoextend on next 100m maxsize unlimited;

create user myatpl identified by myatpl123 default tablespace myatpl;

grant resource,connect to myatpl

ALTER USER myatpl QUOTA unlimited ON myatpl;

Data warehouse

one example which could make this term clear, suppose a engineering student collects notes from
various sources like libaray books ,class notes, friends notes etc
and then make a perfect copy of it from which he could get the whole concept,
which will help him making the understanding of concept .

here the integration of all the notes from various sources can be thought of as data warehouse

#############################################################################################


The storage area for processed and integrated data across different sources
which can be operational data or external data
A data ware house allows its users to extract required data,for business analysis
and strategic decision making


conceptualy:A data warehouse is a home for 'second hand' data that originates
in either other corporate applications,or some data source externa;
to your comany

Formaly:A data ware house is a stand alone repositoy of information,integrated from several,
possibaly heterogeneous operational databases.



definition by Ralph Kimball :
A warehouse is a copy of transaction data specifically
structured for query and analyisis.
#######################################################################################
Chatrasal's def
dataware house is data collected from different source databases,data marts, reports,etc.
which are summarized and maintained in multidimensional form .

mysql clustering basics

#ndbcluseter engine is coming with mysql server inside mysql cluster


#pushes data to n/w to processes called datanodes and not stores anything on mysql servers

# create table........ENGINE=NDBCluster;

# Engine NDBCluster ...does it in your n/w

# mysqlserver ------->TCP/Ip-------->Datanodes(data lives here)$$$$most imp part of mysql-cluster

# innodb tables data and myisam data is still going to mysql server

# datanodes are ndbd processes-------like mysqd processes ----one m/c can have more than one datanode i.e more than one ndbd processes

#how records of table going to be store

#new entry-----> application---->mysql server(use ndbcluster engine)----->data nodes

#magic happens in data nodes supose we having two datanodes
*************data stores redundantly
*************stores two time to make sure data is available if somethngs happens n1....>>>>>>.....<<<<<<....n2

#datanodes tell mysql server that thesese are ready

# ndbcluster engine is transactional

#many mysql servers can access the data from data nodes at same time.

#how datanodes are storing the data>>>>?????

partionaing and replicas

######################################################################################
##
#1#-----------partition 1---------->>>>>> Datanode 1(id-1)-------->>>>make replica of partion 1 to Datanode 2(id-2)
#2#
#3#
#4#
as whole it is node gooup
###
#5#-----------partition 2------->>>>>>>>>>> Datanode 2(id-2)--------->>>make replica of partion 2 to Datanode 1(id-1)
#2#
#7#
#8#
no of partions = no of datanodes
hashing mechanism
#################################################################################

if we have more than one node group remember that each gp has have data that is half of one gp and half in other

#if one partener in gp goes down other takes it responsibilty


# cluster confgiguration contains ipadress


# load is eqaly divided amongst datanodes

#when one gp goes down clustering ends




#######################$$$

NODE TYPES


1 sqlnodes ======== MYSQL SERVER

2 data nodes

3 management node ----------------holds configuration-----every other node on cluster needs it-------1st thing to be started
--------------------monitoring logs
4 API------sql nodes are API nodes but otherwise is not mandatory