Wednesday, April 10, 2013

Logoff Remote Desktop user remotely


In serveral situations, you might run into a situation where you can’t login to server using RDP because "The terminal server has exceeded the maximum number of allowed connection".
Even without asking the system admin you can detect and kill one of your already connected session and then login. Here is how we can do that:
We could use QWinsta windows tool to figure out what users are remotely connected to the server.
Replace the IP with your server IP.
Y:\>QWinsta /server:192.168.0.12
 SESSIONNAME       USERNAME                 ID  STATE   TYPE        DEVICE
 services                                    0  Disc
 console                                     1  Conn
 rdp-tcp#0         dbmgmt                    2  Active  rdpwd
 rdp-tcp                                 65536  Listen

I want to logout dbmgmt user. So I’ll have to take ID assigned to it and run the following command:

Y:\>logoff /server:193.168.0.12 2 /v
Logging off session ID 2

Friday, April 05, 2013

MySQL Replication Setup:

Here is a simple demonstration of MySQL Replication setup. For my case, I’ve installed MySQL 5.1 database on two servers.
Here are the steps:


1. Enable Binary Logging & Assigning Unique Server ID (Master)

On a replication master, you must enable binary logging and establish a unique server ID. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible.

Each server within a replication group must be configured with a unique server ID. This ID is used to identify individual servers within the group, and must be a positive integer between 1 and (232)–1. How you organize and select the numbers is entirely up to you.
Edit your my.cnf file like this: 

server-id                       = 1
log-bin                        = /u01/app/mysql/mysqllogs/bin-log


2. Restart mysql (as root)
service mysqld restart


3. Create REPLICANT user on MASTER
Create the REPLICANT user with REPLICATION SLAVE privilege. This user would pull data from SLAVE conneting to MASTER DB.

create user 'replicant'@%' identified by 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%';
4.  Transferring DUMPS to SLAVE with Master DB State Information:
Stop the production server, and obtain its state information. Most importantly, note the File and Position values:mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 240      | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

While the database has the READ LOCK applied, export the data using mysqldump, including master data information:
mysqldump --all-databases --master-data > dbdump.db

Then unlock the master database:
mysql> UNLOCK TABLES;

5. Setting the Replication Slave Configuration

On a replication slave, you must establish a unique server ID. Edit the my.cnf like this:
# Slave Config
[mysqld]
server-id                      = 2
read-only                      = 1
relay-log                      = /u01/app/mysql/mysqllogs/relay-log
relay-log-space-limit          = 16G
master-host                    =
master-user                    = replicant
master-password                =
master-connect-retry           = 30
 
After making the changes, restart the server. 
6.  Importing dumps into SLAVE DB:
Transfer the data (dbdump.db) file from Master and load it into the database:

shell> mysql < fulldb.dump
Set the file information – this needs to match the values you observed on the master when you locked the tables above. In this example the filename was mysql-bin.003 and the log position was 240:
mysql> change master to
MASTER_LOG_FILE='mysql-bin.003',
MASTER_LOG_POS=240;
7.  Start the slave:
 
mysql> start slave;

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.13.60
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: bin-log.000016
          Read_Master_Log_Pos: 462
               Relay_Log_File: relay-log.000040
                Relay_Log_Pos: 413
        Relay_Master_Log_File: bin-log.000016
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 462
              Relay_Log_Space: 705
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

Thats it!! Now you can create table/database and see the reflection on slave database!

Thursday, April 04, 2013

MySQL Database User Permissions

MYSQL Server uses an internal database called mysql to store database user information. We can play around with several information tables liket the followings to get idea about user permissions and related information.


select * from mysql.user;
select * from mysql.db;
select * from mysql.tables_priv;
select * from mysql.columns_priv;
select * from mysql.procs_priv;

Followings are some formatted reports based upon these basic tables.
List of DATABASE Privileges:
SELECT md.host `Host`,
       md.user `User`,
       md.db `Database`,
       REPLACE(
          RTRIM(
             CONCAT(
                IF(md.Select_priv = 'Y', 'Select ', ''),
                IF(md.Insert_priv = 'Y', 'Insert ', ''),
                IF(md.Update_priv = 'Y', 'Update ', ''),
                IF(md.Delete_priv = 'Y', 'Delete ', ''),
                IF(md.Create_priv = 'Y', 'Create ', ''),
                IF(md.Drop_priv = 'Y', 'Drop ', ''),
                IF(md.Grant_priv = 'Y', 'Grant ', ''),
                IF(md.References_priv = 'Y', 'References ', ''),
                IF(md.Index_priv = 'Y', 'Index ', ''),
                IF(md.Alter_priv = 'Y', 'Alter ', ''),
                IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
                IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
                IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
                IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
                IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
                IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
                IF(md.Execute_priv = 'Y', 'Execute ', ''),
                IF(md.Event_priv = 'Y', 'Event ', ''),
                IF(md.Trigger_priv = 'Y', 'Trigger ', ''))),
          ' ',
          ', ')
          AS `Privileges`
  FROM mysql.db md
ORDER BY md.Host, md.User, md.Db
 
LIST OF GLOBAL PRIVILEGES:
 
SELECT mu.host `Host`,
       mu.user `User`,
       REPLACE(
          RTRIM(
             CONCAT(
                IF(mu.Select_priv = 'Y', 'Select ', ''),
                IF(mu.Insert_priv = 'Y', 'Insert ', ''),
                IF(mu.Update_priv = 'Y', 'Update ', ''),
                IF(mu.Delete_priv = 'Y', 'Delete ', ''),
                IF(mu.Create_priv = 'Y', 'Create ', ''),
                IF(mu.Drop_priv = 'Y', 'Drop ', ''),
                IF(mu.Reload_priv = 'Y', 'Reload ', ''),
                IF(mu.Shutdown_priv = 'Y', 'Shutdown ', ''),
                IF(mu.Process_priv = 'Y', 'Process ', ''),
                IF(mu.File_priv = 'Y', 'File ', ''),
                IF(mu.Grant_priv = 'Y', 'Grant ', ''),
                IF(mu.References_priv = 'Y', 'References ', ''),
                IF(mu.Index_priv = 'Y', 'Index ', ''),
                IF(mu.Alter_priv = 'Y', 'Alter ', ''),
                IF(mu.Show_db_priv = 'Y', 'Show_db ', ''),
                IF(mu.Super_priv = 'Y', 'Super ', ''),
                IF(mu.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
                IF(mu.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
                IF(mu.Execute_priv = 'Y', 'Execute ', ''),
                IF(mu.Repl_slave_priv = 'Y', 'Repl_slave ', ''),
                IF(mu.Repl_client_priv = 'Y', 'Repl_client ', ''),
                IF(mu.Create_view_priv = 'Y', 'Create_view ', ''),
                IF(mu.Show_view_priv = 'Y', 'Show_view ', ''),
                IF(mu.Create_routine_priv = 'Y', 'Create_routine ', ''),
                IF(mu.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
                IF(mu.Create_user_priv = 'Y', 'Create_user ', ''),
                IF(mu.Event_priv = 'Y', 'Event ', ''),
                IF(mu.Trigger_priv = 'Y', 'Trigger ', ''))),
          ' ',
          ', ')
          AS `Privileges`
  FROM mysql.user mu
ORDER BY mu.Host, mu.User
 
List of privileges for TABLES:
 
SELECT mt.host `Host`,
       mt.user `User`,
       CONCAT(mt.Db, '.', mt.Table_name) `Tables`,
       REPLACE(mt.Table_priv, ',', ', ') AS `Privileges`
  FROM mysql.tables_priv mt
 WHERE mt.Table_name IN
          (SELECT DISTINCT t.table_name `tables`
             FROM information_schema.tables AS t
            WHERE    t.table_type IN
                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')
                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)
ORDER BY mt.Host,
         mt.User,
         mt.Db,
         mt.Table_name;
 
List of privileges for VIEWS:

SELECT mv.host `Host`,
       mv.user `User`,
       CONCAT(mv.Db, '.', mv.Table_name) `Views`,
       REPLACE(mv.Table_priv, ',', ', ') AS `Privileges`
  FROM mysql.tables_priv mv
 WHERE mv.Table_name IN (SELECT DISTINCT v.table_name `views`
                           FROM information_schema.views AS v)
ORDER BY mv.Host,
         mv.User,
         mv.Db,
         mv.Table_name;
 
List of privileges for TABLE COLUMNS:
 
SELECT mtc.host `Host`,
       mtc.user `User`,
       CONCAT(mtc.Db,
              '.',
              mtc.Table_name,
              '.',
              mtc.Column_name)
          `Tables Columns`,
       REPLACE(mtc.Column_priv, ',', ', ') AS `Privileges`
  FROM mysql.columns_priv mtc
 WHERE mtc.Table_name IN
          (SELECT DISTINCT t.table_name `tables`
             FROM information_schema.tables AS t
            WHERE    t.table_type IN
                        ('BASE TABLE', 'SYSTEM VIEW', 'TEMPORARY', '')
                  OR t.table_type <> 'VIEW' AND t.create_options IS NOT NULL)
ORDER BY mtc.Host,
         mtc.User,
         mtc.Db,
         mtc.Table_name,
         mtc.Column_name;
 
List of privileges for VIEW COLUMNS:
 
SELECT mvc.host `Host`,
       mvc.user `User`,
       CONCAT(mvc.Db,
              '.',
              mvc.Table_name,
              '.',
              mvc.Column_name)
          `Views Columns`,
       REPLACE(mvc.Column_priv, ',', ', ') AS `Privileges`
  FROM mysql.columns_priv mvc
 WHERE mvc.Table_name IN (SELECT DISTINCT v.table_name `views`
                            FROM information_schema.views AS v)
ORDER BY mvc.Host,
         mvc.User,
         mvc.Db,
         mvc.Table_name,
         mvc.Column_name;
 
List of privileges for PROCEDURES:

SELECT mp.host `Host`,
       mp.user `User`,
       CONCAT(mp.Db, '.', mp.Routine_name) `Procedures`,
       REPLACE(mp.Proc_priv, ',', ', ') AS `Privileges`
  FROM mysql.procs_priv mp
 WHERE mp.Routine_type = 'PROCEDURE'
ORDER BY mp.Host,
         mp.User,
         mp.Db,
         mp.Routine_name;
 
List of privileges for FUNCTIONS:
 
SELECT mf.host `Host`,
       mf.user `User`,
       CONCAT(mf.Db, '.', mf.Routine_name) `Procedures`,
       REPLACE(mf.Proc_priv, ',', ', ') AS `Privileges`
  FROM mysql.procs_priv mf
 WHERE mf.Routine_type = 'FUNCTION'
ORDER BY mf.Host,
         mf.User,
         mf.Db,
         mf.Routine_name;
 
 

Wednesday, April 03, 2013

Oracle BI Discoverer 11g Installation


We are going to install OBI 11g with following sequences.
  • Install Oracle database 11.2.0.3 standard edition database.
  • Install JDK jdk 1.6 or 1.7
  • Install Repository Creation Utility (RCU) 11.1.0.6 (ofm_rcu_win_11.1.1.6.0_disk1_1of1.zip)
  • Weblogic Server 10.3.6 (wls1036_generic.jar)
  • Oracle Portal, Reports,Forms & Discoverer 11g and patching with 11.1.6
  • Run configuration for Discoverer
pfrd 11.1.1.2 Includes

V18772-01_1of4.zip
V18772-01_2of4.zip
V18772-01_3of4.zip
V18772-01_4of4.zip

pfrd 11.1.1.6 Includes
V29883-01.zip

./config.sh to configure 11.1.1.6

Environments:
Make sure the /etc/hosts are like this
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
#::1 localhost6.localdomain6 localhost6
192.168.10.11 biserver.domain.com biserver

Packages:
With an Oracle Database 11g installed, you need only to install one package - compat-db


Install OBI Repository Creation Utility (RCU)

Download the installation files from the http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html on your desktop.

(Note** It has to be the same version as BI software). The name of the installer would be fm_rcu_win_11.1.1.6.0_disk1_1of1.zip

Unzip the file, navigate to the bin directory and start the RCU => rcu.bat.

Welcome > Next


Accept default of Create. Click Next:



·         Leave default of Oracle Database:
·         Set hostname
·         Set port
·         Set service name
·         Set username
·         Set password
·         Leave Role as SYSDBA 




Select the Oracle Business Intelligence Component.This action automatically selects the Metadata Services (MDS) schema (under the AS Common Schemas group).





So at this point, Repository Creation succeded!!

Our next step is to Install Weblogic server & creating middleware home


Install Oracle WebLogic Server and Create the Middleware Home:

Environment Preperation:

We need to make sure that weblogic is using jdk1.7.0_17 while running installer.

SETTING JAVA HOME:
We have installed JDK1.7 into following directory:

Cd /usr/java/jdk1.7.0_17/bin

[oracle@test bin]$ ./java -version
java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)

[oracle@test bin]$ cd..
[oracle@test jdk1.7.0_17]$ echo $PATH
/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ pwd
/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export JAVA_HOME=$PWD
[oracle@test jdk1.7.0_17]$  echo $JAVA_HOME
/usr/java/jdk1.7.0_17

[oracle@test jdk1.7.0_17]$ export PATH=$JAVA_HOME/bin:$PATH

[oracle@test jdk1.7.0_17]$ echo $PATH
/usr/java/jdk1.7.0_17/bin:/usr/sbin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/fnadvi/bin

[oracle@test jdk1.7.0_17]$ cd ../
[oracle@test java]$ java -version
java version "1.7.0_17"
Java(TM) SE Runtime Environment (build 1.7.0_17-b02)
Java HotSpot(TM) 64-Bit Server VM (build 23.7-b01, mixed mode)




Install weblogic 10.3.6
Make sure that the weblogic is running using jdk1.7.0 java binaries by running it from java directory, as shown below:

[oracle@test distr]$ /usr/java/jdk1.7.0_17/bin/java -jar wls1036_generic.jar
Extracting 0%....................................................................................................100%




Now that the Weblogic server is installed. Our final step is installing Oracle Discoverer 11g.


Installing Forms,Reports & Discoverer 11.1

1.  We will be installing Oracle Portal, Reports, Forms & Discoverer 11.1.1.2 and then will pathc it with 11.1.1.6



NOTE: Select Install Software - DO NOT CONFIGURE****
We'll do that after applying patches.






Patching to 11.1.1.6:






Configure Discoverer under 11.1.1.6 patched home:

Once everything is installed and patched. Run configure Portal,Forms,Reports & Discoverer.
Configure CONFIG.sh 


cd /u01/app/oracle/middleware/as_1/bin
./config.sh





At this point.....Installation of Oracle Discoverer 11g has been successfully installed.


Just one additional task is, you need to copy the database tns file, (where EUL Schema resides) needs to be copied over to Middleware Instance Directory. Ie, for my case it's

/u01/app/oracle/middleware/asinst_1/config

The link to use Discover would be prety much like this:http://hostname:8888/discoverer/viewer OR 
http://hostname:8090/discoverer/viewer


Note that, even if webcache is down, you can still access Discoverer with the 8888 port!!!