Oracle 10g RAC databases on Linux Red Hat 3.0 single server
Oracle 10g RAC databases on Linux Red Hat 3.0 single server
Discover easy and very affordable way to install and configure a fully functional Oracle 10g RAC database on one only un-expensive single node running Linux Red Hat.
by Guenadi Nedkov Jilevski
If you are serious experienced Oracle DBA interested in setting an High Availability Oracle 10g RAC database testing environment than this paper shall give you the insides to accomplish it step by step using only a single Red Hat 3.0 server without additional investment in hardware and time to setup and maintain a second server to form a cluster and expensive shared storage.
Background / Overview
With the evolution of the Oracle High Availability solutions, now-a-days becoming the key to provide the desired zero downtime and nearzero time to repair functionality essential for the business continuity, and their relatively high cost and demand for a high end hardware platforms face the Seniors DBA toward the greatest challenge to look for an inexpensive solutions to practice, test and prototype their architecture and solutions. This article addresses the needs by providing a working Oracle 10g RAC database architecture based entirely on a single server with four Network Interface Cards (NIC) running a Linux Red Hat 3.0. The article shall cover in great details the prerequisites such as preliminary Linux Red Hat 3.0 setup, required by Oracle preliminary kernel parameters setup, preliminary NIC configuration for RAC setup. The paper shall place an emphasis on the installation of the Cluster Ready Services and Oracle RDBMS at a screen level and last but not least shall finish with the database creation with dbca and manual adjustments required for the second, third, n-th instance. The following article is a step-by-step guide (Cookbook) with tips and information for installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3. The primary objective of this article in it’s current version is to demonstrate a quick installation of Oracle 10g with RAC on RH AS 3.0 using a single server.
Step 1: Preliminary Linux Red Hat 3.0 Setup
Here I’ll describe the process of installation and configuration of the Linux Red Hat server on a PC. The configuration used is RAM 1024MB, two 120GB disks and four (4) Netwok Interface Cards (NIC). Install Red Hat 3.0 on the server using the Install Everything option to ensure that you have all of the necessary components. Install the kernel-unsupported-2.4.21-4.EL.i686.rpm from the Red Hat Disk3 on the server having device support for many nonstandard devices. Add the following lines to the /etc/modules.conf on the server.
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
Create Linux group dba and users for the Oracle Cluster Ready Services and Oracle RDBMS respectively oracle10crs and oracle10 members of the dba group as in the /etc/passwd and /etc/group files. Set user equivalence for the users in /etc/hosts.equiv or .rhosts as specified in the sample /etc/hosts.equiv. Create /var/opt/oracle directory with read write permissions for the Oracle users. Create the directories for the $ORACLE_BASE with respective permissions. Setup the profiles for the users as in the example of ~oracle10crs/.bash_profile and ~oracle10/.bash_profile to include the variables for the $ORACLE_SID, $ORACLE_HOME, $ORACLE_BASE and $PATH to include the $ORACLE_HOME/bin.
Step 2: Preliminary kernel parameter setup
Set the minimal required kernel parameters in the /etc/sysctl.conf as in the sample provided. Reboot the server for the completion of the preliminary setup of the Linux and Kernel to take effect.
Step 3: Network Configuration
To configure the network interfaces (in this example eth0, eth1, eth2 and eth3), run the following command on each node as root.
$ redhat-config-network
Do not configure the network alias names for the public VIP. This will be done by Oracle’s Virtual Internet Protocol Configuration Assistant (VIPCA). When the network configuration is done, it is important to make sure that the name of the public RAC nodes is displayed when you execute the following command:
$ hostname
pub1
You can verify the new configured NICs by running the command:
/sbin/ifconfig -a
Look at the sample /etc/hosts file.
Step 4: Cluster Ready Services Installation
To install CRS, insert the “Cluster Ready Services (CRS) R1 (10.1.0.2)” CD and as root issue the following commands:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– Inventory directory and credentials:
Click Next
– Unix Group Name: Use “dba”.
– Root Script Window: Open another window, login as root, and run /tmp/orainstRoot.sh
on the node where you launched runInstaller.
After you’ve run the script, click Continue.
– File Locations: I used the recommended default values:
Destination Name: OraCr10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/crs_1
Click Next
– Language Selection: Click Next
– Cluster Configuration:
Cluster Name: crs
Cluster Nodes: Public Node Name: pub1 Private Node Name: priv1
Click Next
– Private Interconnect Enforcement:
Interface Name: eth0 Subnet: 192.168.1.113 Interface Type: Public
Interface Name: eth1 Subnet: 192.168.2.112 Interface Type: Public
Interface Name: eth2 Subnet: 192.168.1.111 Interface Type: Private
Interface Name: eth3 Subnet: 192.168.2.110 Interface Type: Private
NOTE: See the output of the ifconfig –a
Click Next
– Oracle Cluster Registry:
OCR Location: /u02/oradata/ OCRFile
Click Next
– Voting Disk: Voting disk file name: /u02/oradata/ CSSFile
Click Next
– Root Script Window:
Open another window, login as root, and execute
/u01/app/oracle/oraInventory/orainstRoot.sh on ALL RAC Nodes!
NOTE: For any reason Oracle does not create the log directory
“/a02/app/oracle/product/10.1.0/crs_1/log”. If there are problems with
CRS, it will create log files in this directory, but only if it exists.
Therefore make sure to create this directory as oracle:
oracle$ mkdir /a02/app/oracle/product/10.1.0/crs_1/log
After you’ve run the script, click Continue.
– Setup Privileges Script Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/crs_1/root.sh on the server !
Note that his can take a while. On the last RAC node, the output of the
script was as follows:
…
CSS is active on these nodes.
Pub1
CSS is active on all nodes.
Oracle CRS stack installed and running under init(1M)
Click OK
– Summary: Click Install
– When installation is completed, click Exit.
One way to verify the CRS installation is to display all the nodes where CRS was installed:
oracle$ /a02/app/oracle/product/10.1.0/crs_1/bin/olsnodes -n
pub 1
Step 5: Installing Oracle Database 10g Software R1 (10.1.0.2) with Real Application Clusters (RAC)
To install the RAC Database software, insert the Oracle Database 10g R1 (10.1.0.2) CD “), and mount it on pub1:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– File Locations: I used the default values:
Destination Name: raDb10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/db_2
Click Next.
– Hardware Cluster Installation Mode:
Select “Cluster Installation”
Click “Select All” to select all servers: pub1
Click Next
– Installation Type:
I selected “Enterprise Edition”.
Click Next.
– Product-specific Prerequisite Checks:
Make sure that the status of each Check is set to “Succeeded”.
Click Next
– Database Configuration:
I selected “Do not create a starter database” since we have to create the
database with dbca. Oracle Database 10g R1 (10.1) OUI will not be able to
discover disks that are marked as Linux ASMLib. For more information, see
http://otn.oracle.com/tech/linux/asmlib/install.html#10gr1
Click Next
– Summary: Click Install
– Setup Privileges Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/db_1/root.sh the server !
NOTE: Make also sure that X is relinked to your local desktop since this
script will launch the “VIP Configuration Assistant” tool which is a
GUI based utility!
VIP Configuration Assistant Tool:
(This Assistant tool will come up only once when root.sh is executed the
first time in your RAC cluster)
– Welcome Click Next
– Network Interfaces: I selected both interfaces, eth0 and eth1.
Click Next
– Virtual IPs for cluster notes:
(for the alias names and IP address, see Setting Up the /etc/hosts File)
Node Name: pub1
IP Alias Name: vip1
IP address: 192.168.1.13
Subnet Mask: 255.255.255.0
Click Next
– Summary: Click Finish
– Configuration Assistant Progress Dialog:
Click OK after configuration is complete.
– Configuration Results:
Click Exit
Click OK to close the Setup Privilege Window.
– End of Installation:
Click Exit
Step 6: Installing Oracle Database 10g with Real Application Cluster (RAC)
To install the RAC database and the instances on the server run runInstaller on pub1.
Login as oracle10 and before you execute dbca, make sure that $ORACLE_HOME and $PATH are set:
oracle10$ . ~oracle10/.bash_profile
oracle10$ dbca
– Welcome Screen: Select “Oracle Real Application Clusters database”
Click Next
– Operations: Select “Create Database”
Click Next
– Node Selection: Click “Select All”. Make sure pub1 is selected!
Click Next
– Database Templates: I selected “General Purpose”.
Click Next
– Database Identification:
Global Database Name: orcl
SID Prefix: orcl
Click Next
– Management Option: Selected “Use Database Control for Database Management”.
Click Next
– Database Credentials:
I selected “Use the Same Password for All Accounts”. Enter the password and
make sure the password does not start with a digit number.
Click Next
– Storage Options: I selected “Cluster File System
Click Next
– Database File Location: Select “ Use common Location for All Database Files”
Enter the Database File Location “/a02/oradata”
Click Next
– Recovery Configuration:
Select the “Specify Flash Recovery Area” with defaults and “Enable Archiving”.
Click Next
– Database Content: Select Sample Schemas but not Custom Scripts.
Click Next
– Database Services: Click “Add” and enter a Service Name: I entered “orcltest”.
I selected TAF Policy “Basic”.
Click Next
– Initialization Parameters:
Change settings as needed.
Click Next
– Database Storage: Change settings as needed.
Click Next
– Creation Options: Check “Create Database”
Click Finish
– Summary: Click OK
Now the database is being created.
Your RAC cluster should now be up and running. To verify, try to connect to each instance from one of the RAC nodes:
$ sqlplus system@orcl1
After you connected to an instance, enter the following SQL command to verify your connection:
SQL> select instance_name from v$instance;
Step 7: Create a second, third, n-th instance
Create a parameter file from the service parameter file and add the parameters above. Modify the parameter file to include the parameters for second instance. For second instance add
orcl2.instance_number=2
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS2′
Restart orcl1 instance with the above parameter file. Create an undo tablespace UNDOTBS2 with the same specifications as UNDOTBS1. Create a redo log thread 2 and enable it using again instance orcl1.
create undo tablespace undotbs2 datafile ‘/a02/oradata/orcl/undotbs02.dbf’ size 31465472;
alter database add logfile thread 2
‘/a02/oradata/orcl/redo11.log’ size 10486272, ‘/a02/oradata/orcl/redo12.log’ size 10486272,
‘/a02/oradata/orcl/redo13.log’ size 10486272;
alter database enable thread 2;
Create a second telnet session loged in as oracle10 and set ORACLE__SID to orcl2. Copy the password file from the first instance to password file for the second instance. Startup the orcl2 instance. Create srvctl configuration or the second instance.
srvctl add instance -d orcl -i orcl2 -n pub1
srvctl status database -d orcl
srvctl start instance -d orcl -i orcl1
srvctl status database -d orcl
Use same approach for a third, fourth , n-th instance on the same server.
Step 8: Transparent Application Failover
Sample script testtaf.sql is used to test the Transparent application Failover. The NET tnsnames.ora and listener.ra are also in the content section. Connect using the orcltaf tnsnames alias and run he script testtaf.sql. From another telnet session kill the pmon of the database where the previous session is connected to and observe the output a select statement application failover below.
[oracle10@pub1 admin]$ sqlplus system/sys1@orcltaf
SQL*Plus: Release 10.1.0.2.0 – Production on Fri Sep 24 18:59:01 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> @/tmp/testtaf
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
250 74 SELECT BASIC NO
INSTANCE_NAME
—————-
orcl1
COUNT(*)
———-
292188
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
251 9 SELECT BASIC YES
INSTANCE_NAME
—————-
orcl2
COUNT(*)
———-
292188
Conclusion
Having implemented the solution described above the reader probably have noticed that has under his/hers belt a fully functional very cost effective High Availability Oracle 10g RAC database architecture for testing, prototyping and prove of concept. The article s extremely suitable for in-house training, consultants sand desk labs and class trainings which can not afford expensive clustered hardware solutions. Once a solution is piloted and configurations tested next essential step will be to build a real test and production environments resulting in considerable reduction of the time, cost solution reusability and much faster learning curve and plenty of hands on knowledge in the course of rolling over into production versus a full fledged prototyping requiring the pairs of servers for clusters and shared disk storage.
At the end of the file, after the text of the article, please supply the following:
- Content of the /etc/sysctl.conf, /etc/hotsts, /etc/hosts.equiv, /etc/passwd, /etc/group .bash_profile, listener.ora tnsnames.ora, testtaf.sql output from ifconfig files for the root, oracle10crs and oracle10 users.
Initoracl.ora
*.background_dump_dest=’/a02/app/oracle/admin/orcl/bdump’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’10.1.0.2.0′
*.control_files=’/a02/oradata/orcl/control01.ctl’,’/a02/oradata/orcl/control02.ctl’,’/a02/oradata/orcl/control03.ctl’
*.core_dump_dest=’/a02/app/oracle/admin/orcl/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/a02/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
orcl1.instance_number=1
orcl2.instance_number=2
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=250
*.remote_listener=’LISTENERS_ORCL’
*.remote_login_passwordfile=’exclusive’
*.shared_pool_size=99614720
*.sort_area_size=65536
orcl1.thread=1
orcl2.thread=2
*.undo_management=’AUTO’
orcl1.undo_tablespace=’UNDOTBS1′
orcl2.undo_tablespace=’UNDOTBS2′
*.user_dump_dest=’/a02/app/oracle/admin/orcl/udump’
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144net.core.wmem_default=262144net.core.rmem_max=262144
net.core.wmem_max=262144
/etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.113 pub1 zeus
192.168.1.112 pub2
192.168.1.111 priv1
192.168.1.110 priv2
192.168.1.13 vip1
192.168.1.12 vip2
ipconfig -a
eth0 Link encap:Ethernet HWaddr 00:C0:4F:36:E1:E7
inet addr:192.168.1.113 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:1428 dropped:0 overruns:1 frame:2079
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:960 (960.0 b)
Interrupt:11 Base address:0xcc00
eth1 Link encap:Ethernet HWaddr 00:30:BD:71:EE:4A
inet addr:192.168.1.112 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:5 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:300 (300.0 b) TX bytes:240 (240.0 b)
Interrupt:9 Base address:0x9c00
eth2 Link encap:Ethernet HWaddr 00:30:BD:72:04:B8
inet addr:192.168.1.111 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:38 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3549 (3.4 Kb) TX bytes:240 (240.0 b)
Interrupt:11 Base address:0xb800
eth3 Link encap:Ethernet HWaddr 00:30:BD:72:04:0F
inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:23 errors:0 dropped:0 overruns:0 frame:0
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2649 (2.5 Kb) TX bytes:960 (960.0 b)
Interrupt:9 Base address:0xd400
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:2903 errors:0 dropped:0 overruns:0 frame:0
TX packets:2903 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:154137 (150.5 Kb) TX bytes:154137 (150.5 Kb)
/etc/hosts.equiv
+pub1 oracle10
+pub2 oracle10
+priv1 oracle10
+priv2 oracle10
+pub1 oracle10crs
+pub2 oracle10crs
+priv1 oracle10crs
+priv2 oracle10crs
/etc/passwd
radvd:x:75:75:radvd user:/:/sbin/nologin
oracle10crs:x:500:501::/home/oracle10crs:/bin/bash
oracle10:x:501:501::/home/oracle10:/bin/bashswd
/etc/group
oracle10crs:x:500:
dba:x:501:oracle10crs,oracle10
/etc/modules.conf
alias eth0 3c59x
alias eth1 8139too
alias eth2 8139too
alias eth3 8139too
alias usb-controller usb-uhci
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
root’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
BASH_ENV=$HOME/.bashrc
USERNAME=”root”
export LD_ASSUME_KERNEL=2.4.19
export USERNAME BASH_ENV PATH
oracle10crs’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/crs_2
PATH=$PATH:$HOME/bin
export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
oracle10’s .bash_oprofile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/db_2
export LD_ASSUME_KERNEL=2.4.19
PATH=$PATH:$HOME/bin
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
[oracle10@pub1 oracle10]$
Testtaf.sql
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
Listener.ora
# listener.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_PUB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_PUB1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl1)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl2)
)
)
tnsnames.ora
# tnsnames.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENERS_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcltest)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLTAF =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(GLOBAL_NAME = ORCL)
(FAILOVER_MODE =
(TYPE=SELECT)
(METHOD=BASIC)
(BACKUP=ORCL)
)
)
)
- Links to more information about the technology at otn.oracle.com
- Guenadi Nedkov Jilevski (gjilevski@spearnet.net) is a [Senior Infrastructure ] at Perot Systems, (http://www.ps.com) a [Lake Success, NY] based [global provider of technology-based business solutions].
Oracle 10g RAC databases on Linux Red Hat 3.0 single server
Discover easy and very affordable way to install and configure a fully functional Oracle 10g RAC database on one only un-expensive single node running Linux Red Hat.
by Guenadi Nedkov Jilevski (gjilevski@spearnet.net)
If you are serious experienced Oracle DBA interested in setting an High Availability Oracle 10g RAC database testing environment than this paper shall give you the insides to accomplish it step by step using only a single Red Hat 3.0 server without additional investment in hardware and time to setup and maintain a second server to form a cluster and expensive shared storage.
Background / Overview
With the evolution of the Oracle High Availability solutions, now-a-days becoming the key to provide the desired zero downtime and nearzero time to repair functionality essential for the business continuity, and their relatively high cost and demand for a high end hardware platforms face the Seniors DBA toward the greatest challenge to look for an inexpensive solutions to practice, test and prototype their architecture and solutions. This article addresses the needs by providing a working Oracle 10g RAC database architecture based entirely on a single server with four Network Interface Cards (NIC) running a Linux Red Hat 3.0. The article shall cover in great details the prerequisites such as preliminary Linux Red Hat 3.0 setup, required by Oracle preliminary kernel parameters setup, preliminary NIC configuration for RAC setup. The paper shall place an emphasis on the installation of the Cluster Ready Services and Oracle RDBMS at a screen level and last but not least shall finish with the database creation with dbca and manual adjustments required for the second, third, n-th instance. The following article is a step-by-step guide (Cookbook) with tips and information for installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3. The primary objective of this article in it’s current version is to demonstrate a quick installation of Oracle 10g with RAC on RH AS 3.0 using a single server.
Step 1: Preliminary Linux Red Hat 3.0 Setup
Here I’ll describe the process of installation and configuration of the Linux Red Hat server on a PC. The configuration used is RAM 1024MB, two 120GB disks and four (4) Netwok Interface Cards (NIC). Install Red Hat 3.0 on the server using the Install Everything option to ensure that you have all of the necessary components. Install the kernel-unsupported-2.4.21-4.EL.i686.rpm from the Red Hat Disk3 on the server having device support for many nonstandard devices. Add the following lines to the /etc/modules.conf on the server.
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
Create Linux group dba and users for the Oracle Cluster Ready Services and Oracle RDBMS respectively oracle10crs and oracle10 members of the dba group as in the /etc/passwd and /etc/group files. Set user equivalence for the users in /etc/hosts.equiv or .rhosts as specified in the sample /etc/hosts.equiv. Create /var/opt/oracle directory with read write permissions for the Oracle users. Create the directories for the $ORACLE_BASE with respective permissions. Setup the profiles for the users as in the example of ~oracle10crs/.bash_profile and ~oracle10/.bash_profile to include the variables for the $ORACLE_SID, $ORACLE_HOME, $ORACLE_BASE and $PATH to include the $ORACLE_HOME/bin.
Step 2: Preliminary kernel parameter setup
Set the minimal required kernel parameters in the /etc/sysctl.conf as in the sample provided. Reboot the server for the completion of the preliminary setup of the Linux and Kernel to take effect.
Step 3: Network Configuration
To configure the network interfaces (in this example eth0, eth1, eth2 and eth3), run the following command on each node as root.
$ redhat-config-network
Do not configure the network alias names for the public VIP. This will be done by Oracle’s Virtual Internet Protocol Configuration Assistant (VIPCA). When the network configuration is done, it is important to make sure that the name of the public RAC nodes is displayed when you execute the following command:
$ hostname
pub1
You can verify the new configured NICs by running the command:
/sbin/ifconfig -a
Look at the sample /etc/hosts file.
Step 4: Cluster Ready Services Installation
To install CRS, insert the “Cluster Ready Services (CRS) R1 (10.1.0.2)” CD and as root issue the following commands:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– Inventory directory and credentials:
Click Next
– Unix Group Name: Use “dba”.
– Root Script Window: Open another window, login as root, and run /tmp/orainstRoot.sh
on the node where you launched runInstaller.
After you’ve run the script, click Continue.
– File Locations: I used the recommended default values:
Destination Name: OraCr10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/crs_1
Click Next
– Language Selection: Click Next
– Cluster Configuration:
Cluster Name: crs
Cluster Nodes: Public Node Name: pub1 Private Node Name: priv1
Click Next
– Private Interconnect Enforcement:
Interface Name: eth0 Subnet: 192.168.1.113 Interface Type: Public
Interface Name: eth1 Subnet: 192.168.2.112 Interface Type: Public
Interface Name: eth2 Subnet: 192.168.1.111 Interface Type: Private
Interface Name: eth3 Subnet: 192.168.2.110 Interface Type: Private
NOTE: See the output of the ifconfig –a
Click Next
– Oracle Cluster Registry:
OCR Location: /u02/oradata/ OCRFile
Click Next
– Voting Disk: Voting disk file name: /u02/oradata/ CSSFile
Click Next
– Root Script Window:
Open another window, login as root, and execute
/u01/app/oracle/oraInventory/orainstRoot.sh on ALL RAC Nodes!
NOTE: For any reason Oracle does not create the log directory
“/a02/app/oracle/product/10.1.0/crs_1/log”. If there are problems with
CRS, it will create log files in this directory, but only if it exists.
Therefore make sure to create this directory as oracle:
oracle$ mkdir /a02/app/oracle/product/10.1.0/crs_1/log
After you’ve run the script, click Continue.
– Setup Privileges Script Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/crs_1/root.sh on the server !
Note that his can take a while. On the last RAC node, the output of the
script was as follows:
…
CSS is active on these nodes.
Pub1
CSS is active on all nodes.
Oracle CRS stack installed and running under init(1M)
Click OK
– Summary: Click Install
– When installation is completed, click Exit.
One way to verify the CRS installation is to display all the nodes where CRS was installed:
oracle$ /a02/app/oracle/product/10.1.0/crs_1/bin/olsnodes -n
pub 1
Step 5: Installing Oracle Database 10g Software R1 (10.1.0.2) with Real Application Clusters (RAC)
To install the RAC Database software, insert the Oracle Database 10g R1 (10.1.0.2) CD “), and mount it on pub1:
mount /mnt/cdrom
xhost +
Login as oracle10 and execute runInstaller:
oracle$ /mnt/cdrom/runInstaller
– Welcome Screen: Click Next
– File Locations: I used the default values:
Destination Name: raDb10g_home1
Destination Path: /a02/app/oracle/product/10.1.0/db_2
Click Next.
– Hardware Cluster Installation Mode:
Select “Cluster Installation”
Click “Select All” to select all servers: pub1
Click Next
– Installation Type:
I selected “Enterprise Edition”.
Click Next.
– Product-specific Prerequisite Checks:
Make sure that the status of each Check is set to “Succeeded”.
Click Next
– Database Configuration:
I selected “Do not create a starter database” since we have to create the
database with dbca. Oracle Database 10g R1 (10.1) OUI will not be able to
discover disks that are marked as Linux ASMLib. For more information, see
http://otn.oracle.com/tech/linux/asmlib/install.html#10gr1
Click Next
– Summary: Click Install
– Setup Privileges Window:
Open another window, login as root, and execute
/a02/app/oracle/product/10.1.0/db_1/root.sh the server !
NOTE: Make also sure that X is relinked to your local desktop since this
script will launch the “VIP Configuration Assistant” tool which is a
GUI based utility!
VIP Configuration Assistant Tool:
(This Assistant tool will come up only once when root.sh is executed the
first time in your RAC cluster)
– Welcome Click Next
– Network Interfaces: I selected both interfaces, eth0 and eth1.
Click Next
– Virtual IPs for cluster notes:
(for the alias names and IP address, see Setting Up the /etc/hosts File)
Node Name: pub1
IP Alias Name: vip1
IP address: 192.168.1.13
Subnet Mask: 255.255.255.0
Click Next
– Summary: Click Finish
– Configuration Assistant Progress Dialog:
Click OK after configuration is complete.
– Configuration Results:
Click Exit
Click OK to close the Setup Privilege Window.
– End of Installation:
Click Exit
Step 6: Installing Oracle Database 10g with Real Application Cluster (RAC)
To install the RAC database and the instances on the server run runInstaller on pub1.
Login as oracle10 and before you execute dbca, make sure that $ORACLE_HOME and $PATH are set:
oracle10$ . ~oracle10/.bash_profile
oracle10$ dbca
– Welcome Screen: Select “Oracle Real Application Clusters database”
Click Next
– Operations: Select “Create Database”
Click Next
– Node Selection: Click “Select All”. Make sure pub1 is selected!
Click Next
– Database Templates: I selected “General Purpose”.
Click Next
– Database Identification:
Global Database Name: orcl
SID Prefix: orcl
Click Next
– Management Option: Selected “Use Database Control for Database Management”.
Click Next
– Database Credentials:
I selected “Use the Same Password for All Accounts”. Enter the password and
make sure the password does not start with a digit number.
Click Next
– Storage Options: I selected “Cluster File System
Click Next
– Database File Location: Select “ Use common Location for All Database Files”
Enter the Database File Location “/a02/oradata”
Click Next
– Recovery Configuration:
Select the “Specify Flash Recovery Area” with defaults and “Enable Archiving”.
Click Next
– Database Content: Select Sample Schemas but not Custom Scripts.
Click Next
– Database Services: Click “Add” and enter a Service Name: I entered “orcltest”.
I selected TAF Policy “Basic”.
Click Next
– Initialization Parameters:
Change settings as needed.
Click Next
– Database Storage: Change settings as needed.
Click Next
– Creation Options: Check “Create Database”
Click Finish
– Summary: Click OK
Now the database is being created.
Your RAC cluster should now be up and running. To verify, try to connect to each instance from one of the RAC nodes:
$ sqlplus system@orcl1
After you connected to an instance, enter the following SQL command to verify your connection:
SQL> select instance_name from v$instance;
Step 7: Create a second, third, n-th instance
Create a parameter file from the service parameter file and add the parameters above. Modify the parameter file to include the parameters for second instance. For second instance add
orcl2.instance_number=2
orcl2.thread=2
orcl2.undo_tablespace=’UNDOTBS2′
Restart orcl1 instance with the above parameter file. Create an undo tablespace UNDOTBS2 with the same specifications as UNDOTBS1. Create a redo log thread 2 and enable it using again instance orcl1.
create undo tablespace undotbs2 datafile ‘/a02/oradata/orcl/undotbs02.dbf’ size 31465472;
alter database add logfile thread 2
‘/a02/oradata/orcl/redo11.log’ size 10486272, ‘/a02/oradata/orcl/redo12.log’ size 10486272,
‘/a02/oradata/orcl/redo13.log’ size 10486272;
alter database enable thread 2;
Create a second telnet session loged in as oracle10 and set ORACLE__SID to orcl2. Copy the password file from the first instance to password file for the second instance. Startup the orcl2 instance. Create srvctl configuration or the second instance.
srvctl add instance -d orcl -i orcl2 -n pub1
srvctl status database -d orcl
srvctl start instance -d orcl -i orcl1
srvctl status database -d orcl
Use same approach for a third, fourth , n-th instance on the same server.
Step 8: Transparent Application Failover
Sample script testtaf.sql is used to test the Transparent application Failover. The NET tnsnames.ora and listener.ra are also in the content section. Connect using the orcltaf tnsnames alias and run he script testtaf.sql. From another telnet session kill the pmon of the database where the previous session is connected to and observe the output a select statement application failover below.
[oracle10@pub1 admin]$ sqlplus system/sys1@orcltaf
SQL*Plus: Release 10.1.0.2.0 – Production on Fri Sep 24 18:59:01 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> @/tmp/testtaf
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
250 74 SELECT BASIC NO
INSTANCE_NAME
—————-
orcl1
COUNT(*)
———-
292188
SID SERIAL# FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
—- ——— ————- ————— ———–
251 9 SELECT BASIC YES
INSTANCE_NAME
—————-
orcl2
COUNT(*)
———-
292188
Conclusion
Having implemented the solution described above the reader probably have noticed that has under his/hers belt a fully functional very cost effective High Availability Oracle 10g RAC database architecture for testing, prototyping and prove of concept. The article s extremely suitable for in-house training, consultants sand desk labs and class trainings which can not afford expensive clustered hardware solutions. Once a solution is piloted and configurations tested next essential step will be to build a real test and production environments resulting in considerable reduction of the time, cost solution reusability and much faster learning curve and plenty of hands on knowledge in the course of rolling over into production versus a full fledged prototyping requiring the pairs of servers for clusters and shared disk storage.
At the end of the file, after the text of the article, please supply the following:
- Content of the /etc/sysctl.conf, /etc/hotsts, /etc/hosts.equiv, /etc/passwd, /etc/group .bash_profile, listener.ora tnsnames.ora, testtaf.sql output from ifconfig files for the root, oracle10crs and oracle10 users.
Initoracl.ora
*.background_dump_dest=’/a02/app/oracle/admin/orcl/bdump’
*.cluster_database_instances=2
*.cluster_database=true
*.compatible=’10.1.0.2.0′
*.control_files=’/a02/oradata/orcl/control01.ctl’,’/a02/oradata/orcl/control02.ctl’,’/a02/oradata/orcl/control03.ctl’
*.core_dump_dest=’/a02/app/oracle/admin/orcl/cdump’
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’orcl’
*.db_recovery_file_dest=’/a02/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)’
orcl1.instance_number=1
orcl2.instance_number=2
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_format=’%t_%s_%r.dbf’
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=250
*.remote_listener=’LISTENERS_ORCL’
*.remote_login_passwordfile=’exclusive’
*.shared_pool_size=99614720
*.sort_area_size=65536
orcl1.thread=1
orcl2.thread=2
*.undo_management=’AUTO’
orcl1.undo_tablespace=’UNDOTBS1′
orcl2.undo_tablespace=’UNDOTBS2′
*.user_dump_dest=’/a02/app/oracle/admin/orcl/udump’
/etc/sysctl.conf
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144net.core.wmem_default=262144net.core.rmem_max=262144
net.core.wmem_max=262144
/etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.113 pub1 zeus
192.168.1.112 pub2
192.168.1.111 priv1
192.168.1.110 priv2
192.168.1.13 vip1
192.168.1.12 vip2
ipconfig -a
eth0 Link encap:Ethernet HWaddr 00:C0:4F:36:E1:E7
inet addr:192.168.1.113 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:1428 dropped:0 overruns:1 frame:2079
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:960 (960.0 b)
Interrupt:11 Base address:0xcc00
eth1 Link encap:Ethernet HWaddr 00:30:BD:71:EE:4A
inet addr:192.168.1.112 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:5 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:300 (300.0 b) TX bytes:240 (240.0 b)
Interrupt:9 Base address:0x9c00
eth2 Link encap:Ethernet HWaddr 00:30:BD:72:04:B8
inet addr:192.168.1.111 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:38 errors:0 dropped:0 overruns:0 frame:0
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:3549 (3.4 Kb) TX bytes:240 (240.0 b)
Interrupt:11 Base address:0xb800
eth3 Link encap:Ethernet HWaddr 00:30:BD:72:04:0F
inet addr:192.168.1.110 Bcast:192.168.1.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:23 errors:0 dropped:0 overruns:0 frame:0
TX packets:16 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2649 (2.5 Kb) TX bytes:960 (960.0 b)
Interrupt:9 Base address:0xd400
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:2903 errors:0 dropped:0 overruns:0 frame:0
TX packets:2903 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:154137 (150.5 Kb) TX bytes:154137 (150.5 Kb)
/etc/hosts.equiv
+pub1 oracle10
+pub2 oracle10
+priv1 oracle10
+priv2 oracle10
+pub1 oracle10crs
+pub2 oracle10crs
+priv1 oracle10crs
+priv2 oracle10crs
/etc/passwd
radvd:x:75:75:radvd user:/:/sbin/nologin
oracle10crs:x:500:501::/home/oracle10crs:/bin/bash
oracle10:x:501:501::/home/oracle10:/bin/bashswd
/etc/group
oracle10crs:x:500:
dba:x:501:oracle10crs,oracle10
/etc/modules.conf
alias eth0 3c59x
alias eth1 8139too
alias eth2 8139too
alias eth3 8139too
alias usb-controller usb-uhci
options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180
options sbp2 sbp2_exclusive_login=0
post-install sbp2 insmod sd_mod
post-remove sbp2 rmmod sd_mod
root’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
BASH_ENV=$HOME/.bashrc
USERNAME=”root”
export LD_ASSUME_KERNEL=2.4.19
export USERNAME BASH_ENV PATH
oracle10crs’s .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/crs_2
PATH=$PATH:$HOME/bin
export LD_ASSUME_KERNEL=2.4.19
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
oracle10’s .bash_oprofile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_BASE=/a02/app/oracle
export ORACLE_SID=orcl1
export ORACLE_HOME=/a02/app/oracle/product/10.1.0/db_2
export LD_ASSUME_KERNEL=2.4.19
PATH=$PATH:$HOME/bin
export PATH=$PATH:$ORACLE_HOME/bin
unset USERNAME
[oracle10@pub1 oracle10]$
Testtaf.sql
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
col sid format 999
col serial# format 99999999
col failover_type format a13
col failover_method format a15
col failed_over format a11
select sid, serial#, failover_type, failover_method, failed_over from v$session where username =’SYSTEM’;
select instance_name from v$instance;
select count(*) from
(
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source union
select * from dba_source
);
Listener.ora
# listener.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_PUB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.113)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_PUB1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl1)
)
(SID_DESC =
(SDU = 32768)
(TDU = 32768)
(ORACLE_HOME = /a02/app/oracle/product/10.1.0/db_2)
(SID_NAME = orcl2)
)
)
tnsnames.ora
# tnsnames.ora Network Configuration File: /a02/app/oracle/product/10.1.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENERS_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(INSTANCE_NAME = orcl1)
)
)
ORCLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcltest)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLTAF =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = OFF)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
(GLOBAL_NAME = ORCL)
(FAILOVER_MODE =
(TYPE=SELECT)
(METHOD=BASIC)
(BACKUP=ORCL)
)
)
)
- Links to more information about the technology at otn.oracle.com
- Guenadi Nedkov Jilevski (gjilevski@spearnet.net) is a [Senior Infrastructure ] at Perot Systems, (http://www.ps.com) a [Lake Success, NY] based [global provider of technology-based business solutions].
Improving Index creation speed in Oracle
Improving Index creation speed in Oracle
It is sometimes a time consuming task if you like to create index with much number of rows. For example you are asked to created an index over 1 billion of data. It may take over 6 hours on your computer and you want to make it faster.
With providing several options while creating index you can improve index creation speed dramatically.
1)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster.
On a server that have 6 CPUs you may give parallel 5 as below.
create index table_1_I on table_1(id,code) parallel 5;
2)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance.
create index table_1_I on table_1(id,code) parallel 5 nologging;
3)COMPRESS Option: With the COMPRESS option you will enable key compression, which eliminates repeated occurrence of key column values and may substantially reduce storage. Use integer to specify the prefix length (number of prefix columns to compress).
For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.
For nonunique indexes, the valid range of prefix length values is from 1 to the number of key columns. The default prefix length is the number of key columns.
You can use as,
create index table_1_I on table_1(id,code) parallel 5 nologging compress;
4)Index in a bigger block: You can create an index in a tablespace that uses bigger block size. If you have DSS environment then you can do it. This will improve performance while creating index.
You can do it by first creating a tablespace with 32k blocksize and then create index under it,
create tablespace 32k_ts
datafile ‘/u01/32k_file.dbf’
blocksize 32k;
create index table_1_I on table_1(id,code) parallel 5 nologging compress tablespace 32K;
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS