Connect to Linux Desktop from Windows.

 

1. Start XVNC11 on the Linux Server

$ x11vnc -forever

Add a line like x11vnc -forever to the end of your /etc/rc.local file.

## Incase X11vnc not available. Refer to this link to download and install the rpm
http://pkgs.repoforge.org/x11vnc/

 

2. Download TightVNC or RealVNC and enter ip of your linux server and Bam !! you are logged into the Linux Desktop from a windows machine

Advertisements

Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

— source —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

— Detination —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;

SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

— on source —

SQL> grant select any dictionary to ivr;

— on target —

SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target

8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

— Source Configuration —

GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

— on source —
GGSCI (ivrhodb) 1> start dpe1

— on target —
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.

MRemote multi-tab remote connection manager

If you have multiple remote connections like VNC/ RDP/ SSH/ TELNET. There is a good software to manage multiple connections and save it for future use. It keeps all connections in a uniform tab-based format. Very powerful and useful tool. The best part : It is completely free and open source !

mRemote : full-featured, multi-tab remote connections manager. You can create Remote desktop, SSH, telnet, VNC and many more connections within the same Window. It can be downloaded from below site.

http://www.mremote.org/wiki/Downloads.ashx

 

Access Oracle tables in SQL Server 2005

In a heterogeneous environment it sometimes becomes necessary to access information across different database. In this exercise I will demonstrate how you can access Oracle db tables in SQL Server 2005.

1. First you need to install Oracle client on the SQL server 2005 server. You can install using the instant client option. I am using Oracle 11gR2 client.

2.  Download ODAC (Oracle Data Access Components from the Oracle website depending on your server platform 32-bit or 64-bit.

http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

3. Edit TNSNAMES.ora

ORACLELS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = FRIENDLYNAME)
    )
  )

There are a couple of things you need to change:

  • HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
  • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.

4.  REBOOT

5. Configure provider in SQL Server

OraOLEDB.Oracle Provider Menu Item

      1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”

    Enable "Allow inprocess"

  1. Create a linked server to the Oracle Database
    1. General
        1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
        2. Provider: Oracle Provider for OLE DB
        3. Product Name: “Oracle” is fine here
        4. Data Source: This should match the tnsnames.ora entry. For example in our case we will use ‘ORACLELS’ as the data source.
      Create a Linked ServerCreate a Linked Server
    2. Security
      1. Select Be made using this security context and supply the remote login and password.
  2. Query the linked server:  select * from openquery(oraclels, ‘select * from db_name.table_name where <clause> ‘)

The syntax for Insert and Update is :
INSERT OPENQUERY (linkedserver, ‘SELECT fieldlist FROM tablename‘) VALUES (valuelist);

UPDATE OPENQUERY (linkedserver, ‘SELECT * FROM tablename WHERE field = ”value”) SET list(field=value);

SELECT * from OPENQUERY(linkedserver,’SELECT COUNT(*) FROM tablename WHERE field = ”value”‘).

Simple and Easy. Now you can access  Oracle tables and run Oracle statements inside from SQL Server 2005. The other way around when you need to access SQL Server tables from Oracle is a little tricky. You need to use something called as heterogeneous services. I will post another article showing, how to do this using a Windows server and then using a Unix server. For Unix server you need ODBC drivers which is generally 3rd party tools. Since ODBC is not native to Unix/Linux.  Keep watching this space for more 🙂

Move datafiles to different location.

From time to time a DBA might need move an Oracle database datafile from one location to another. Reasons for this might include: I/O balancing for performance reasons, or to rectify incorrect naming standards etc. In this example we are using non-asm and windows environment. ASM would only use +DATA1 +DATA2 tag etc and for any unix os it will be “/” instead of windows tag 🙂

1. Select the current location of the datafile’s from

SQL>  SELECT NAME FROM V$DATAFILE;

2. Bring the tablespace offline

SQL> ALTER TABLESPACE USERS OFFLINE;

3.  Copy the files using OS command. In windows you can simply do copy paste to the new folder

4. Rename  datafile to new location to put  new information in the control file (Control file holds the structure of the database )

SQL> ALTER DATABASE RENAME DATAFILE ‘E:\APP\PSECC\ORADATA\SWX\SWX01.DBF’  TO  ‘F:\APP\PSECC\ORADATA\SWX\SWX01.DBF’

5. Bring the tablespace online

SQL> ALTER TABLESPACE USERS ONLINE;

Optionally if you want to delete the old files from the OS. Shutdown the database and delete the old files and bring up the database.

Alert log location in Oracle 11gR2

Oracle 11gR2 is a major release upgrade from 10gR2 and 11gR1 both. Many previous parameters like user_dump_dest etc have been deprecated. There is a single parameter called diag_dest.  Many DBA’s are so used to the location of alert log and trace files , that it becomes quite inconvenient when you start using Oracle 11g . But Oracle 11g has a powerful new utility called ADR CLI to navigate around the alert log and other trace files. I will demonstrate a few ways to check the alert log and trace files using “adrci” command.

C:\Documents and Settings\test>adrci

ADRCI: Release 11.2.0.1.0 – Production on Thu Mar 31 11:56:54 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = “e:\app\testdb”
adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
“HELP EXTENDED” to see the list

adrci> show alert

1: diag\clients\user_system\host_3726408541_76
2: diag\rdbms\testdb\testdb
3: diag\tnslsnr\test\listener
Q: to quit

Please select option:

 

Many of the adrci commands are very similar to the standard Unix vi commands. For example

SHOW ALERT -TAIL (to view the last 10 entries)

SHOW ALERT -TAIL 50 (to view the last 50 entries)

SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)

show alert -p “message_text like ‘%ORA-07445%’” (to show all occurences of the string ‘ORA-07445′ in the alert log.)

Purging Alert Log Content

The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.

The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.

So to purge all alert log entries older than 7 days the following command will be used:

adrci > purge -age 10080 -type ALERT

It is a powerful utility with much more functionality.

 

P.S: The general location of alert log on windows is : E:\app\<hostname>\diag\rdbms\<sid>\<sid>\trace

How to Add Date and Time to a Filename with Windows Command Line.

Many times it becomes necessary to append date stamp on your export dumps. In Unix systems, shell scripting is very liberal with variables you can define yourself eg:

expdate=`date ‘+%d%m%Y’`
dat=`date ‘+%m%d%y %H:%M:%S’`

And then go onto define in your script as

./expdp system/system directory=export_dir dumpfile=exp_swx_$expdate.dmp logfile=exp_swx_$expdate.log schemas=swx

But on Windows it can be done in a more easier way, using the Date and Time function. Just input the string below as part of the file name any time you want the current system date and/or time included as part of the file name.

Date:  %date:~4,2%-%date:~7,2%-%date:~12,2%
Time:  %time:~0,2%-%time:~3,2%-%time:~6,2%
Example: copy c:\test.txt c:\test-%date:~4,2%-%date:~7,2%-%date:~12,2%.txt will output c:\test-mm-dd-yy.txt

exp edate/edate file=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.dmp log=edate%date:~4,2%-%date:~7,2%-%date:~12,2%.txt owner=edate statistics=none