Oraconsultancy - Your Multi Cloud Partner

Monday, 11 October 2010

Initializing and Tuning Parameters for Parallel Execution

PARALLEL_MAX_SERVERS <=> CPU_COUNT x PARALLEL_THREADS_PER_CPU x 2x 5 <=>Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance.

PARALLEL_EXECUTION_MESSAGE_SIZE <=> 2 KB (port specific) <=> Increase to 4k or 8k to improve parallel execution performance if sufficient SGA memory exists.

PARALLEL_ADAPTIVE_MULTI_USER <=> TRUE <=> Causes parallel execution SQL to throttle DOP requests to prevent system overload.

The following example shows a statement that sets the DOP to 4 on a table:

ALTER TABLE orders PARALLEL 4;
This next example sets the DOP on an index to 4:

ALTER INDEX iorders PARALLEL 4;

This last example sets a hint to 4 on a query:

SELECT /*+ PARALLEL(orders, 4) */ COUNT(*) FROM orders;

The initialization parameter PARALLEL_MIN_PERCENT specifies the desired minimum percentage of requested parallel execution servers. This parameter affects DML and DDL operations as well as queries

Thursday, 30 September 2010

cross Platforms - Transportable Tablespaces

Across Platforms Using Transportable Tablespaces :

Step for Cross platform Transportable tablespace

0.1 mkdir /u01/app/oracle/dumpdir
0.2 Create directory dumpdir as '/u01/app/oracle/dumpdir';

1. expdp TRANSPORT_TABLESPACES=TEST_EXP DUMPFILE=expdp_TEST_EXP.dmp DIRECTORY=dumpdir TRANSPORT_FULL_CHECK=y

2. RMAN> CONVERT TABLESPACE sales_1,sales_2

TO PLATFORM 'Microsoft Windows NT'

FORMAT '/temp/%U';

3. Copy to Destination Server/Host

4. RMAN> CONVERT DATAFILE

'/hq/finance/work/tru/tbs_31.f',

'/hq/finance/work/tru/tbs_32.f',

'/hq/finance/work/tru/tbs_41.f'

TO PLATFORM="Solaris[tm] OE (32-bit)"

FROM PLATFORM="HP TRu64 UNIX"

DB_FILE_NAME_CONVERT=

"/hq/finance/work/tru/", "/hq/finance/dbs/tru"

PARALLELISM=5;

5. IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir

TRANSPORT_DATAFILES=

/salesdb/sales_101.dbf,

/salesdb/sales_201.dbf

REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)

6. ALTER TABLESPACE sales_1 READ WRITE;

ALTER TABLESPACE sales_2 READ WRITE;

Oracle 11g: Use Direct NFS for Datafiles

Oracle 11g (11.1.1) : Use Direct NFS for Datafiles: -

With Oracle Database 11g release 1 (11.1), instead of using the operating system kernel NFS client, you can configure Oracle Database to access NFS V3 servers directly using an Oracle internal Direct NFS client.

To enable Oracle Database to use Direct NFS, the NFS file systems must be mounted and available over regular NFS mounts. The mount options used are not relevant, as Direct NFS manages settings after installation.

You can use optional oranfstab (in eighter/etc or to $ORACLE_HOME/dbs.) to specify additional paths for a mount point.

Direct NFS determines mount point settings to NFS storage devices based on the configurations in /etc/mtab, which are changed with configuring the /etc/fstab file.
Direct NFS searches for mount entries in the following order:

$ORACLE_HOME/dbs/oranfstab

/etc/oranfstab

/etc/mtab

Direct NFS uses the first matching entry found.

Direct NFS can use up to four network paths defined in the oranfstab file for an NFS server. The Direct NFS client performs load balancing across all specified paths. If a specified path fails, then Direct NFS reissues I/O commands over any remaining paths

Complete the following procedure to enable Direct NFS:

1. Create an oranfstab file
2. use an ODM library, libnfsodm11.so, to enable Direct NFS

1. Sample oranfstab file

server:  MyDataServer1
path:  132.34.35.12
path:  132.34.35.13
export: /vol/oradata1 mount: /mnt/oradata1
 
server: MyDataServer2
path:  NfsPath1
path:  NfsPath2
path:  NfsPath3
path:  NfsPath4
export: /vol/oradata2 mount: /mnt/oradata2
export: /vol/oradata3 mount: /mnt/oradata3
export: /vol/oradata4 mount: /mnt/oradata4
export: /vol/oradata5 mount: /mnt/oradata5

2. Steps to use ODM NFS Library.

Change directory to $ORACLE_HOME/lib.

Enter the following commands:

mv libodm11.so libodm11.so_stub
ln -s libnfsodm11.so libodm11.so

Wednesday, 22 September 2010

TNS-12502: TNS:listener received no CONNECT_DATA from client

TNS-12502: "TNS:listener received no CONNECT_DATA from client"

*Cause: No CONNECT_DATA was passed to the listener.

*Action: Check that the service name resolved from TNSNAMES.ORA has the CONNECT_DATA component of the connect descriptor.

If you getting this error while registering database services with listener using local_listener or remote_listener problem might be with sqlnet.ora and tnsnames.ora setting not implemented by current instance and restarting the instance again after setting TNS_ADMIN parameter might resolve the problem.

Case Study : Implementing TCPS authentication
1. Once you have create all the configuartion and Listener to use TCPS connection and if listener is running on non default port, you would have to set local_listener parameter to register your database with new listener (TCPS). When you creating TNS alias for local_listener, you don't specify connect_data parameter as its not required for service register, however service registration will fail with TNS-12502 in listener.log until database instance is bounced.
Bouncing/Restarting the instance will read sqlnet.ora , tnsnames.ora with new security config and listener will be able to register the new service on TCPS port.

Monday, 20 September 2010

Install Weblogic Server 10.3.2.0 for Grid Control 11gR1

To Install Weblogic Server 10.3.2 on Linux X64 , download wls1032_generic.jar from oracle.com

$lynx http://download.oracle.com/otn/nt/middleware/11g/wls/wls1032_generic.jar

$ java -jar wls1032_generic.jar

Extracting 0%....................................................................................................100%

Unable to instantiate GUI, defaulting to console mode. - Weblogic Install

$ ./wls1032_linux32.binExtracting 0%....................................................................................................100%

Unable to instantiate GUI, defaulting to console mode.
<---------------------------------------------------------- Oracle Installer - WebLogic 10.3.2.0 --------------------------------------------------------->
Welcome:
--------
This installer will guide you through the installation of WebLogic 10.3.2.0. Type "Next" or enter to proceed to the next prompt. If you want to changedata entered previously, type "Previous". You may quit the installer at any time by typing "Exit".
Enter [Exit][Next]>

Solution :

Required java is missing on the server or weblogic server installer is failed to find one.
Install SUN JDK 1.6_18 or higher from sun/Oracle Website.

http://www.oracle.com/technetwork/java/javase/downloads/jdk6-jsp-136632.html

If problem persists.
Set JAVA_HOME

$java -fullversion

java full version "1.6.0_21-b06"

& Try Installing again.

Wednesday, 11 August 2010

Oracle Grid Control 11g Installation

Oracle 11g Grid Control no longer run on Oracle application server, you have to install Middleware Infrastructure Weblogic Server 10.3.2 before GC 11g.

We Can use the following sequence to Install the Grid Control Environment.

1 Install ORacle 11.2.0.1 database and Patches
2. Create database to be used for Grid Repository
3. Install Weblogic Server 10.3.2 and patches.
4. Install Grid Control Software.
5. Install Grid Agent on all the Servers.

1.1 Install RDBMS Software 11.2.0.1
1.2 Install patch.
NON RAC - 9654983 - Opatch apply - Oracle user
GI + RAC - 9655006 - Opatch auto - root user

1.3 Create Listener with PORT 1521 or

2.1 Create Database using DBCA or sqlplus command Line.

2.2 If you have created the database with default parameters, change the following parameters to ensure its meet minimum requirement for OMS.

sqlplus "/ as sysdba"
alter system set session_cached_cursors=210 scope=spfile;
alter system set processes=550 scope=spfile;

Ensure Undo tablespace is aleast 200m size;

-- bounce the database to ensure parameter are effective.
Startup force;

2.3 Deconfigure the EM REporsitory to avoid any failure.

$ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop -sys_PWD -SYSMAN_PWD

Enter the following Information :
Database SID:
Listener port number :
Password for SYS user:

3.1 WLS 10.3.2
Install WLS and JDK 1.6

3.2 Apply Patches. using $BASE/Middleware/utils/bsu

$./bsu.sh

8990616, 9100465, and 9221722

4.0 Install Management Server. ( Grid Control Server).

4.1 Unzip Grid control Installer
4.2 run Installer

./runInstaller

Complete Install by running allroot.sh from Middleware/oms11g/allroot.sh

5. Install Grid Agent on all the Servers.

cd /u03/app/product

32 bit Linux :-
wget http://host1.servers.com:4889/agent_download/11.1.0.0.0/linux/agentDownload.linux --no-check-certificate

chmod +x agentDownload.linux

./agentDownload.linux -b /u02/agent11gr2 -m servername.domain. -r 4489

64bit Linux :-

wget http://host1.servers.com:4889/agent_download/11.1.0.0.0/linux_x64/agentDownload.linux_x64 --no-check-certificate

chmod +x agentDownload.linux_x64

./agentDownload.linux_x64 -b /u02/agent11gr2 -m servername.domain. -r 4489

Note :You might need to use -y option to ensure , agent registration password prompt you.

Amazon Partner