Thinking Out Loud

May 19, 2013

Connecting to RAC DB with SQL*Plus

Filed under: 11g,oracle,RAC — mdinh @ 2:05 am

Download

Instant Client Package – Basic Lite
Instant Client Package – SQL*Plus

http://www.oracle.com/technetwork/topics/winsoft-085727.html

Modify hosts file

C:\Windows\System32\drivers\etc>more hosts
# Copyright (c) 1993-2009 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a '#' symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

# localhost name resolution is handled within DNS itself.
#       127.0.0.1       localhost
#       ::1             localhost
192.168.56.31  dinh-scan1
192.168.56.32  dinh-scan2
192.168.56.33  dinh-scan3
192.168.56.21  rac01-vip
192.168.56.22  rac02-vip
192.168.56.23  rac03-vip

Modify tnsnames

D:\instantclient_11_2>more tnsnames.ora
jay =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan1)(PORT = 1551))
  (ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan2)(PORT = 1551))
  (ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan3)(PORT = 1551))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jay)
   )
)

jay1 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jay)
   )
)

jay2 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jay)
   )
)

jay3 =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = rac03-vip)(PORT = 1521))
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jay)
   )
)

Modify login.sql

D:\instantclient_11_2>more login.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set termout off
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@&_CONNECT_IDENTIFIER):'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr clear
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
define _editor=D:\PortableApps\npp.6.3.2.bin.minimalist\notepad++
set editfile E:\TMP
set termout on

Modify environment variables

D:\instantclient_11_2>set TNS_ADMIN
TNS_ADMIN=D:\instantclient_11_2

D:\instantclient_11_2>set SQLPATH
SQLPATH=D:\instantclient_11_2

D:\instantclient_11_2>set PATH
Path=D:\Program Files\Oracle\VirtualBox;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Windows\sys
tem32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Eng
ine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management
 Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\OpenCL SD
K\2.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x64;D:\instantclient_11_2
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC

D:\>sqlplus sys@\”dinh-scan1:1551/jay\” as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:31 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

RAC01:(SYS@dinh-scan1:1551/jay):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

D:\>sqlplus sys@jay2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:43 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

RAC02:(SYS@jay2):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

D:\>sqlplus sys@\”dinh-scan1:1551/jay\” as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:50 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

RAC03:(SYS@dinh-scan1:1551/jay):PRIMARY> exit
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 302 other followers

%d bloggers like this: