Online database info

How to Install MongoDB on Ubuntu 14.04 ?

How to Install MongoDB on Ubuntu 14.04?

Step #1: Setup a the Package Database
First we’ll import the MongoDB public key used by the package management system:

# sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

Then we’ll create a list file for MongoDB:

# echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

Now reload the package database:

# sudo apt-get update

Step #2: Install Latest Stable Version MongoDB
At this point, installing MongoDB is as simple as running just one command:

sudo apt-get install -y mongodb-org

If you’d like MongoDB to auto-update with apt-get than you’re done with the installation. But, it’s possible to ‘pin’ the version of MongoDB you just installed to prevent apt-get from auto-updating.

Step #3: Get MongoDB Running

Start-Up MongoDB:-
# sudo service mongod start

Check MongoDB Service Status
#sudo service mongod status

Summary List of Status Statistics (Continuous)

#mongostat

Summary List of Status Statistics (5 Rows, Summarized Every 2 Seconds)

#mongostat --rowcount 5 2

Enter the MongoDB Command Line

#mongo

Step 4:-
Now we can configure and basic setting in Mongodb Database Server
# vi /etc/mongod.conf
logappend=true
logpath=logpath=/var/log/mongodb/mongod.log
port=27017
dbpath=/var/lib/mongo
smallfiles = true
bind_ip = 127.0.0.1,159.203.180.48 [To change and open a certain ip's access for mongodb ]
:wq

Register a Connected Server using 2012 SQL Server Management Studio

Register a Connected Server using 2012 SQL Server Management Studio :-

By registering the server, you can save the connection information for servers that we access frequently. A server can be registered before connecting, or at the time of connection
from Object Explorer.

To register a connected server:-
Open up sql server management studio 2012.
In Object Explorer, right-click a server to which you already are connected, and then click Register.

* Server name
Enter the name you want to use for the registered server. Registering a local or remote server using SQL Server Management Studio lets you store the server connection information
for future connections. This field defaults to the server name entered when you were connecting to the server. You can retain this server name or enter another easy-to-use
name for the server.

* Authentication
There are two type of authentication available .
a.Windows authentication
b.Sql server authentication

Choose anyone of above for the authentication method.

* Server description
Enter an optional description of the server. The maximum number of characters allowed is 250.

* Save
Click to save the information you have entered and create a registered server.

Different SQL Server Roles

Different SQL Server Roles:-

Server Roles:-

The Server Roles page lists all possible roles that can be assigned to the new login. The following options are available:
bulkadmin:-
Members of the bulkadmin fixed server role can run the BULK INSERT statement.

dbcreator:-
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

diskadmin:-
Members of the diskadmin fixed server role can manage disk files.

processadmin:-
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.

public:-
All SQL Server users, groups, and roles belong to the public fixed server role by default.

securityadmin:-
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE
database-level permissions. Additionally, they can reset passwords for SQL Server logins.

serveradmin:-
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

setupadmin :-
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.

sysadmin :-
Members of the sysadmin fixed server role can perform any activity in the Database Engine.

Database-Level Roles:-

db_owner :- Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database,
and can also drop the database.

db_securityadmin:- Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals
to this role could enable unintended privilege escalation.

db_accessadmin :- Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups,
and SQL Server logins.

db_backupoperator :- Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin :- Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter:- Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader :- Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter :- Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user
tables within a database.

db_denydatareader :- Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

How to setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu

Setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu
===================================================================

Our Testing Environment Consists of the Following Machines (Both are CentOS 6) :

Master: 192.168.72.220 ser1.kmi.com
Slave: 192.168.72.221 ser2.kmi.com

Step: 1. Bind Hosts File (Both Server) :

# vi /etc/hosts

192.168.72.220 ser1.kmi.com ser1
192.168.72.221 ser2.kmi.com ser2

-- Save & Quit (:wq)

Step: 2. Stop Iptables & Disabled Selinux (Both Server) :

For CentOS:

# service iptables stop
# chkconfig iptables off

For Ubuntu:

# service ufw stop

# vi /etc/sysconfig/selinux

SELINUX=disabled

-- Save & Quit (:wq)

Step: 3. Restart the Both Server :

# init 6

Step: 4. Install NTP :

# yum -y install ntp
# service ntpd restart
# chkconfig ntpd on
# ntpdate pool.ntp.org

Step: 5. Create MariaDB.repo on Both Server :

For CentOS:

# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB

# vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

-- Save & Quit (:wq)

For Ubuntu :

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'

Step: 6. Install MariaDB on Both Servers :

For CentOS:

# yum -y install MariaDB-server MariaDB-client

For Ubuntu:

# apt-get update
# apt-get install mariadb-server

Step: 7. Start MariaDB Service & Set MariaDB Root Password on Both Master & Slave Server :

# service mysqld start
# chkconfig mysqld on

# mysql_secure_installation

Step: 8. Check MariaDB Version on Both Node :

# mysql -V
mysql Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1

Step: 9. Configuring MariaDB Server on Master Node :

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
server_id=1
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 10. Restart the MariaDB Service :

# service mysqld restart

Step: 11. Log on to MariaDB Master Node & Create a User Called "Slave" & Give Appropriate Permission :

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
MySQL [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 181 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit

Step: 12. Now Take Backup of Full Database :

# mysqldump -u root -proot_password employees > /tmp/employees.sql

Step: 13. After Taken Dump, Log on to MariaDB & Unlock Tables :

# mysql -u root -proot_password

MySQL [(none)]> UNLOCK TABLES;
MySQL [(none)]> exit;

Step: 14. Copy the Dump File to the Slave Node :

# scp -r /tmp/employees.sql root@192.168.72.221:/tmp/

Step: 15. Restore the Dump File on Slave Node :

# mysql -u root -predhat

MySQL [(none)]> create database employees;
MySQL [(none)]> exit;

# mysql -u root -proot_password employees < /tmp/employees.sql

Step: 16. Configuring MariaDB on Slave Node :

Note: It needs to be a Different Integer than 1, as we used 1 in the Master:

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
server_id=2
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 17. Restart the MariaDB Server :

# service mysqld restart

Step: 18. Setting Up MariaDB Replication on Slave Node :

Note: Special attention to the "MASTER_LOG_FILE" & "MASTER_LOG_POS" Variables, which should match the Values returned by "SHOW MASTER STATUS" in STEP 10 of above.

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.72.220',
MASTER_USER='slave',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=181,
MASTER_CONNECT_RETRY=10;

Step: 19. Start the Slave & Check its Status :

MySQL [(none)]> START SLAVE;
MySQL [(none)]> SHOW SLAVE STATUS\G;

Note: If the SHOW SLAVE STATUS\G; command returns any Errors. Use those errors to troubleshoot & then run START SLAVE; to test again.

Step: 20. Test MariaDB Database Replication :

Add some Record to the "employees" Database in the Master Node :

MySQL [(none)]> use employees;
MySQL [(none)]> CREATE TABLE Guests1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

Verify that this Change was Replicated in the Slave Node :

MySQL [(none)]> USE employees;
MySQL [(none)]> show tables;

As you can see, Replication is Working Correctly from Master to Slave.

Done...!!!

Invisible index on oracle 11g

Database version:- oracle 11.2.0

Invisible Indexes, which are basically indexes that exist and are maintained by Oracle but are "invisible" to the Cost based optimizer(CBO). Specific sessions can be set to see these
invisible indexes as necessary.The invisible index is an alternative of dropping or making an index unusable. This feature is also functional when certain modules of an application
require a specific index without affecting the rest of the application.

First, create a simple table and associated index.
SQL> conn soumya/soumya
SQL> set autotrace on
SQL> set linesize 200

SQL> create table soumya_test (Rollno number(5),name varchar2(20));
Table created.

SQL> create index soumya_test_ind on soumya_test(Rollno);
Index created.

Now insert data into the table.
SQL> begin
2 for x in 1..1000 loop
3 insert into soumya_test ( Rollno , name)
4 values ( x , default);
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

By default, indexes are created as "VISIBLE" .
SQL> SELECT table_name,index_name, visibility FROM user_indexes WHERE index_name = 'SOUMYA_TEST_IND';

TABLE_NAME INDEX_NAME VISIBILITY
------------------------------ ------------------------------ ----------
SOUMYA_TEST SOUMYA_TEST_IND VISIBLE

SQL>

As the index is visible, it can be considered and used by the CBO .

Here we checked our indexed column by user_ind_columns.
We are now going to check how the query will process by the oracle server.

SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

19 rows selected.

Now alter the visibility of index SOUMYA_TEST_IND.
SQL> alter index SOUMYA_TEST_IND invisible;

Index altered.

Check the index status.
SQL> select index_name,visibility from user_indexes
where table_name='SOUMYA_TEST';

INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE

As, we can see that index is invisible now oracle wont use it.
now we again check the same explain plan for same query and compare the results.

SQL> explain plan for
2 select * from soumya_test where Rollno in (555,726,959);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

In above explain plan we see oracle didn't use the index like it did in previous query.

Now change back the visibility once again.

SQL> alter index SOUMYA_TEST_IND visible;

Index altered.

any specific sessions can be altered so they can "see" these invisible indexes.
SQL> select index_name,visibility from user_indexes
2 where table_name='SOUMYA_TEST';

INDEX_NAME VISIBILITY
------------------------------ ----------
SOUMYA_TEST_IND INVISIBLE

SQL> show parameter OPTIMIZER_USE_INVISIBLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE

SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = true;

Session altered.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3654503979

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SOUMYA_TEST | 3 | 75 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 - filter("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

17 rows selected.

We see that optimizer is still not using the invisilbe index so we need to query the explain plan again.

SQL> explain plan for
select * from soumya_test where Rollno in (555,726,959);
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2411823278

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 75 | 0 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SOUMYA_TEST | 3 | 75 | 0 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | SOUMYA_TEST_IND | 4 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ROLLNO"=555 OR "ROLLNO"=726 OR "ROLLNO"=959)

Note
-----
- dynamic sampling used for this statement (level=2)

19 rows selected.

Now we see the invisible index is being used.

How to configure mail on SQL Server 2012

Configure mail on SQL Server 2012

Link:- https://www.dropbox.com/s/gc1we4467ps7out/Configure%20Database%20Mail.doc?dl=0

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 drop undo tablespace in oracle 11g?

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE

SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

Now try to drop the old undotablespace.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf USERS
/u01/app/oracle/oradata/prim/undotbs02.dbf UNDOTBS2
/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE