Data access methods using data pump utility

Data access methods using data pump utility

A Data Pump export and import job can access the table data in either of the two ways:
Direct-Path :
This access uses the Direct Path API
Direct-path exports and imports lead to improved performance, since the direct path internal stream format is the same as the format stored in Oracle dump files. This leads to reduced need for data conversion

Oracle always uses direct-path method to load or unload data except under some conditions:
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns

It is mandatory to use direct-path access methods under the following conditions :

Table has any LONG data

External-tables :
This feature lets oracle read data from and write data to operating system files that lie outside the database.
Direct-path doesn’t support intra-partition parallelism and it is imperative to use external tables in such cases.
Under following conditions only external tables can be used :
1) Clustered tables
2) Active triggers in tables
3) A single partition in a table with a global index
4) Referrential integrity constraints
5) Domain indexes on LOB columns
6) Tables with fine-grained access control enabled in the insert mode
7) Tables with BFILE or opaque type columns

Note :
The data file format is identical in direct-path access method and external tables. So we can easily export data with one method and import data with other method.

February 19, 2008 Posted by gjilevski | oracle | Leave a comment

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.

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=262144

net.core.wmem_default=262144

net.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].

February 17, 2008 Posted by gjilevski | oracle | Leave a comment

New features in 11g for DBA

New features in 11g for DBA

This post will discuss about new features of oracle database 11g administration. Every Oracle database major release comes with new features and step by step some new features are discussed here briefly that comes with Oracle database 11g.

1)Improved Automatic Memory Management:
In 10g in case of automatic memory management we knew that if SGA_TARGET is set then all dynamic SGA components will be automatically managed and if PGA_AGGREGATE_TARGET is set then pga components will be automatically managed. But starting with oracle 11g both pga and sga memory can be managed automatically by setting only MEMORY_TARGET parameter. However you can still assign minimum sizes individually for the SGA and instance PGA.

2)New fault diagnosability infrastructure:
The fault diagnosability infrastructure are for easy diagnosis for any critical problems. After a problem has been occurred it gather necessary information against the problem and then an Incident Packaging Service packages all diagnostic data for a problem into a zip file so that you can transmit that to Oracle Support and thus reduce time to gather information about the problems.

3)Invisible Indexes:
Now in 11g you can make an index as invisible state instead of dropping it or mark them unusable. It is very effective in order to test performance of a query to see whether removing index will improve the performance. By default an invisible index is ignored by an optimizer but is maintained during DML statements. You can change the initialization parameter to cause the optimizer to use invisible indexes.

4)Virtual Columns:
In tables you can now include virtual columns. In fact the value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, or even user-defined PL/SQL functions. You can create index and do partition on the virtual columns.

5)Enhanced Security:
In 11g now by default your password is case sensitive. So you can use mixed case password. Though by editing initialization parameter you can make password case-insensitive.

6)Database resident connection pooling:
Database resident connection pooling provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. Database resident connection pooling significantly reduces database resources needed to support a large number of client connections and thereby boosting the scalability of both middle-tier and database tiers.

7)Tablespace level Encryption:
You can now encrypt a tablespace that contains sensitive data. Tablespace encryption is completely transparent to your application and when you encrypt a tablespace, all tablespace blocks are encrypted also.

8)Invalidation of dependent schema objects are greatly reduced:
Before 11g if we make any changes in the main object then dependent object become invalid. In 11g invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced and thus increasing application availability during maintenance, upgrades, and online table redefinition.

9)Enhanced automated maintenance task infrastructure:
Though in Oracle 10g you had automatic optimizer statistics gathering, Automatic Segment Advisor, and Automatic SQL Tuning Advisor. In 11g you can now exercise finer control over automated maintenance task scheduling and those task ran automatically.

10)Transparent data encryption enhanced:
Now in 11g using the transparent data encryption feature you can now encrypt SecureFile LOBs.

11)Table compression now supported in OLTP environments:
On compressed tables you can now run DML operations and adding or dropping columns of a compressed tables.

12)Query result cache in sga:
Now query results can be cached in sga memory in the result cache. The database can then use cached results to answer future executions of that query. It is also true for query fragments. As because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

13)Default automatic undo management mode:
In 10g if you create database manually then the parameter UNDO_MANAGEMENT became MANUAL as by default UNDO_MANAGEMENT is manual in oracle 10g. However if you created database by dbca it makes the parameter AUTO. In 11g by default UNDO_MANAGEMENT is set to AUTO. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.

14)Enhancements to Oracle Database Resource Manager:

15)Enhancements to Oracle Scheduler:

16)Enhanced online index creation and rebuild:
Before oracle 11g we can do online index creation and rebuild operation but it required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. The DML blocking lock can lock other DMLs and thus performance problem can arise while doing online index creation and rebuild operation. In 11g this lock is no longer required, making these online index operations fully transparent.

17)Tables with materialized view logs can now be redefine online:
Prior to 11g online redefinition of a table that have materialized view logs were not possible. But in 11g tables with materialized view logs can now be redefined online. Materialized view logs are now considered one of the dependent objects. So they can be copied to interim table using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

18)Facility to make a table read only mode:
Now to avoid any update or insert or delete on a table you don’t need to make whole tablespace in read only mode. In 11g with ALTER TABLE statement you can place a single table into read only mode.

19)DDL commands can wait for locks:
Prior to 11g if you have any DML operation running against the table then you can’t do DDL commands on the table and instantly ORA-54 message would return as demonstrated in ORA-54 Resource Busy. But in 11g you can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails. So we might not need wait for prompting further command to test whether we can run DDL commands. Oracle will check and if no DML operations DDL commands will be performed if it get resource free within DDL_LOCK_TIMEOUT time.

20)Enhancements to initialization parameter management:
There are several enhancement in the initialization parameters.

Upon startup, values of initialization parameters are written to the alert log in
such a way as to make it easy to copy and paste them to create a new PFILE.

The name and path of the PFILE or SPFILE used to start the instance is written
to the alert log so we need not to search which one is used to startup database.

February 2, 2008 Posted by gjilevski | oracle | Leave a comment