How to refresh MVIEW in parallel

Refresh TYPES are COMPLETE/FAST/ON COMMIT/ON DEMAND
Couple of things you should do to make a mview to refresh in parallel:
1.) Alter the master table to make sure it allows parallelism:

alter table parallel (degree 4);
To check
select degree from dba_tables where table_name='';

2.) When you create the mview log (incase of FAST refresh mviews), create it with a parallel clause:

create materialized view log on parallel (degree 4);
OR
Incase of existing mviews:
alter materialized view log on parallel (degree 4);

3.) When you create the mview, create it with a parallel clause:

create materialized view REFRESH COMPLETE ON DEMAND as select /*+ PARALLEL (table_name,4) */ * from @dblink;
OR
create materialized view REFRESH COMPLETE ON DEMAND parallel 4 as select * from @dblink;
Or
Incase of existing mviews:
alter table parallel (degree 4);
alter materialized view parallel (degree 4);

Lastly while executing the refresh:

EXECUTE DBMS_MVIEW.REFRESH(LIST=>'',PARALLELISM=>4);

This should make your mview refresh in parallel. With the above example the mview should run with parallel degree 4.

PS: Make sure that parallel is enabled for your database, check parameters:

PARALLEL_MAX_SERVERS
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_AUTOMATIC_TUNING

Make sure that the above parameter are set to allow any parallelism to work.
Sample values for these parameters:

SQL> show parameter PARALLEL_MAX_SERVERS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 120
SQL> show parameter PARALLEL_EXECUTION_MESSAGE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_execution_message_size integer 16384
SQL> show parameter PARALLEL_ADAPTIVE_MULTI_USER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean TRUE
SQL> show parameter PARALLEL_AUTOMATIC_TUNING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning boolean FALSE


Note:
1.) Replace with the table name.
2.) Replace with materialized view name.
3.) Replacedblink with the name of the database link.

Import Error: IMP-00032: SQL statement exceeded buffer length

While trying to import a big table with 1000s of partitions I got error:

IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TEST_PART_1" NOLOGGING COMPRESS, PARTITION "TEST_NOV_1" VALUES LESS THAN (2010112806) PCTFREE 0 PCTUSED 40...

The table was exported simiply with:

exp system/oracle file=tab.dmp tables=table_part

While run import:

imp system/oracle file=tab.dmp fromuser=scott touser=tiger

I got the above error.
I tried various combination of parameter buffer i.e.

imp system/oracle file=tab.dmp fromuser=scott touser=tiger buffer=1000000 commit=y

etc, but still got the error. So I wanted to know whats the value required for buffer.

Solution:
In the source database we need to run:

SQL> select dbms_lob.getlength (dbms_metadata.get_ddl('TABLE','table_part','SCOTT')) "SQL_LENGTH" from dual;

SQL_LENGTH
----------
3886502

The above output gives the value of buffer we need to have atleast to run the import, so I used:

imp system/o1ympic file=tabs.dmp log=tabs.log fromuser=SCOTT touser=TIGER commit=y ignore=Y buffer=44886502


This solved the problem.
Metalink Note: 846397.1

Oracle 11gr2 Grid: root.sh fails on node2, asmlib issue

A very interesting problem which took me quite a while to resolve.
Issue while running root.sh on node 2 during 11gr2 grid software installation i was receiving the following error:
Error in root.sh:

DiskGroup DG_SYS01 creation failed with the following message:
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification 'ORCL:DISK0' matches no disks


Configuration of ASM failed, see logs for details
Did not succssfully configure and start ASM
CRS-2500: Cannot stop resource 'ora.crsd' as it is not running
CRS-4000: Command Stop failed, or completed with errors.
Command return code of 1 (256) from command: /oragrid/product/11.2/bin/crsctl stop resource ora.crsd -init
Stop of resource "ora.crsd -init" failed
Failed to stop CRSD


Error in ASM alert log:

ORA-15183: ASMLIB initialization error [driver/agent not installed]
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
ERROR: diskgroup DG_SYS01 was not mounted
NOTE: cache deleting context for group DG_SYS01 1/-239075992
WARNING: Disk Group DG_SYS01 containing configured OCR is not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DG_SYS01" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DG_SYS01"
ERROR: ALTER DISKGROUP ALL MOUNT
Wed Sep 08 22:09:42 2010
SQL> CREATE DISKGROUP DG_SYS01 EXTERNAL REDUNDANCY DISK 'ORCL:DISK0' ATTRIBUTE 'compatible.asm'='11.2.0.0.0' /* ASMCA */
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification 'ORCL:DISK0' matches no disks
ERROR: CREATE DISKGROUP DG_SYS01 EXTERNAL REDUNDANCY DISK 'ORCL:DISK0' ATTRIBUTE 'compatible.asm'='11.2.0.0.0' /* ASMCA */
kfdp_dismount(): 3
kfdp_dismountBg(): 3
ERROR: diskgroup DG_SYS01 was not created


What was puzzling was that why is node2 trying to run CREATE diskgroup, while node1 has run successfully and created the diskgroup.
Other errors in cssd.log

2010-09-08 20:06:14.856: [ SKGFD][1151920448]ERROR: -14(asmlib /opt/oracle/extapi/64/asm/orcl/1/libasm.so version failed with 2)
...
2010-09-08 20:06:14.856: [ SKGFD][1151920448]Discovery skipping bad asmlib :ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:

2010-09-08 20:06:14.856: [ CSSD][1151920448]clssnmvDiskVerify: Successful discovery of 0 disks
2010-09-08 20:06:14.856: [ CSSD][1151920448]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2010-09-08 20:06:14.857: [ CSSD][1151920448]clssnmvFindInitialConfigs: No voting files found


This showed that it looked like an issue with the oracleasm library.
But I was able to execute all /etc/init.d/oracleasm commands without any problems.
e.g /etc/init.d/oracleasm listdisks --> this showed all the disk correctly.

I tried rerunning /etc/init.d/oracleasm configure. But still continued to get the error.

I used steps mentioned in http://jarneil.wordpress.com/2008/07/07/asmlib-troubleshooting/ to make sure the libraries were installed properly. This note was really helpful, I would like to thank the author.
The libraries were installed properly.

I had actually reinstalled the oracleasm libraries also:

[oracle@node2]/% rpm -qa |grep oracleasm
oracleasm-support-2.1.3-1.el5
oracleasm-2.6.18-128.el5debug-2.0.5-1.el5
oracleasm-2.6.18-128.el5-2.0.5-1.el5
oracleasm-2.6.18-128.el5xen-2.0.5-1.el5
oracleasm-2.6.18-128.el5-debuginfo-2.0.5-1.el5
oracleasmlib-2.0.4-1.el5


Finally after a lot of searching (google, metalink..etc...etc).
I found metalink note "FAQ ASMLIB CONFIGURE,VERIFY, TROUBLESHOOT [ID 359266.1]" on metalink and started with all the checks mentioned.
Finally I figured out that there was problem with my /etc/sysconfig/oracleasm file. This file on other servers is a sym link:

[oracle@node1]/% ls -lrt /etc/sysconfig/oracle*
-rw-r--r-- 1 root root 774 Sep 8 23:32 /etc/sysconfig/oracleasm-_dev_oracleasm
lrwxrwxrwx 1 root root 24 Sep 8 23:36 /etc/sysconfig/oracleasm -> oracleasm-_dev_oracleasm

But in my case it was:

[root@node2 sysconfig]# ls -lrt oracle*
-rw-r--r-- 1 root root 574 Mar 18 2009 oracleasm
lrwxrwxrwx 1 root root 24 Sep 7 23:30 oracleasm.rpmsave -> oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 774 Sep 8 23:32 oracleasm-_dev_oracleasm

And all the parameters were blank inside oracleasm. What I understood is that libasm.so used oracleasm file.
So I:

[root@node2 sysconfig]# ln -s oracleasm-_dev_oracleasm oracleasm
[root@node2 sysconfig]# ls -lrt oracle*
lrwxrwxrwx 1 root root 24 Sep 7 23:30 oracleasm.rpmsave -> oracleasm-_dev_oracleasm
-rw-r--r-- 1 root root 774 Sep 8 23:32 oracleasm-_dev_oracleasm
lrwxrwxrwx 1 root root 24 Sep 8 23:36 oracleasm -> oracleasm-_dev_oracleasm
[root@node2 sysconfig]# rm oracleasm.rpmsave
rm: remove symbolic link `oracleasm.rpmsave'? y


Now as I had already run root.sh and it had failed, i was unable to run it again:

[root@node2 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n)
[n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-09-08 23:37:12: Parsing the host name
2010-09-08 23:37:12: Checking for super user privileges
2010-09-08 23:37:12: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
CRS is already configured on this node for crshome=0
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home.


So first we have to deconfigure the previous run of root.sh:

[root@node2 11.2]# crs/install/rootcrs.pl -verbose -deconfig -force
2010-09-08 23:37:48: Parsing the host name
2010-09-08 23:37:48: Checking for super user privileges
2010-09-08 23:37:48: User has super user privileges
Using configuration parameter file: crs/install/crsconfig_params
PRCR-1035 : Failed to look up CRS resource ora.cluster_vip.type for 1
PRCR-1068 : Failed to query resources
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.gsd is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.ons is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.eons is registered
Cannot communicate with crsd

ACFS-9200: Supported
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'node2'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'node2'
CRS-2677: Stop of 'ora.drivers.acfs' on 'node2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'node2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
error: package cvuqdisk is not installed
Successfully deconfigured Oracle clusterware stack on this node


After this I restart oracleasm:

[root@node2 11.2]# /etc/init.d/oracleasm stop
Dropping Oracle ASMLib disks: [ OK ]
Shutting down the Oracle ASMLib driver: [ OK ]
[root@node2 11.2]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]


Check that the sym link is also in place, basically to see if restarting oracleasm changed anything:

lrwxrwxrwx 1 root root 24 Sep 8 23:36 oracleasm -> oracleasm-_dev_oracleasm

All looking fine i reran root.sh and it went through fine.

Some command that I ran in the pursuit to find the problem:

/etc/init.d/oracleasm listdisks
/etc/init.d/oracleasm start
/etc/init.d/oracleasm status
ls -rlt /dev/oracleasm/disks/
/etc/init.d/oracleasm querydisk DISK0
rpm -ql oracleasm-support
df -ha |grep asm
rpm -ql oracleasmlib
/usr/sbin/oracleasm-discover
/usr/sbin/oracleasm-discover 'ORCL:*'
/usr/sbin/oracleasm-discover 'ORCL:*'


Oracle 11gR2 Grid Infrastructure Installation Steps

Step By Step Installation 11gR2 Non ASM
========================================
Connect as root user:
1.) groupadd dba
2.) useradd -d /home/oracle -g dba -G dba oracle
3.) Create directories for oracle grid installation, the directory path should not be same as $ORACLE_BASE. Assuming $ORACLE_BASE=/oracle.
mkdir /oragrid
Make sure the directory as atleast 5GB free space.
4.) Change the owner of the directory to oracle:dba
chown -R oracle:dba /oragrid
5.) RPM requirements (From Metalink note# 880989.1:

1.) binutils-2.17.50.0.6-6.el5 (x86_64)
2.) compat-libstdc++-33-3.2.3-61 (x86_64) <<< both ARCH's are required. See next line.
3.) compat-libstdc++-33-3.2.3-61 (i386) <<< both ARCH's are required. See previous line.
4.) elfutils-libelf-0.125-3.el5 (x86_64)
5.) glibc-2.5-24 (x86_64) <<< both ARCH's are required. See next line.
6.) glibc-2.5-24 (i686) <<< both ARCH's are required. See previous line.
7.) glibc-common-2.5-24 (x86_64)
8.) ksh-20060214-1.7 (x86_64)
9.) libaio-0.3.106-3.2 (x86_64) <<< both ARCH's are required. See next line.
10.) libaio-0.3.106-3.2 (i386) <<< both ARCH's are required. See previous line.
11.) libgcc-4.1.2-42.el5 (i386) <<< both ARCH's are required. See next line.
12.) libgcc-4.1.2-42.el5 (x86_64) <<< both ARCH's are required. See previous line.
13.) libstdc++-4.1.2-42.el5 (x86_64) <<< both ARCH's are required. See next line.
14.) libstdc++-4.1.2-42.el5 (i386) <<< both ARCH's are required. See previous line.
15.) make-3.81-3.el5 (x86_64)
16.) elfutils-libelf-devel-0.125-3.el5.x86_64.rpm
a.) requires elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm as a prerequisite, as listed below.
b.) elfutils-libelf-devel and elfutils-libelf-devel-static each depend upon the other. Therefore, they must be installed together, in one (1) "rpm -ivh" command as follows:
rpm -ivh elfutils-libelf-devel-0.125-3.el5.x86_64.rpm elfutils-libelf-devel-static-0.125-3.el5.x86_64.rpm
17.) glibc-headers-2.5-24.x86_64.rpm
a.) requires kernel-headers-2.6.18-92.el5.x86_64.rpm as a prerequisite, as listed below
18.) glibc-devel-2.5-24.x86_64.rpm <<< both ARCH's are required. See next item.
19.) glibc-devel-2.5-24.i386.rpm <<< both ARCH's are required. See previous item.
20.) gcc-4.1.2-42.el5.x86_64.rpm
a.) requires libgomp-4.1.2-42.el5.x86_64.rpm as a prerequisite, as listed below
21.) libstdc++-devel-4.1.2-42.el5.x86_64.rpm
22.) gcc-c++-4.1.2-42.el5.x86_64.rpm
23.) libaio-devel-0.3.106-3.2.x86_64.rpm <<< both ARCH's are required. See next item
24.) libaio-devel-0.3.106-3.2.i386.rpm <<< both ARCH's are required. See previous item.
25.) sysstat-7.0.2-1.el5.x86_64.rpm
26.) unixODBC-2.2.11-7.1.x86_64.rpm <<< both ARCH's are required. See next item
27.) unixODBC-2.2.11-7.1.i386.rpm <<< both ARCH's are required. See previous item.
28.) unixODBC-devel-2.2.11-7.1.x86_64.rpm <<< both ARCH's are required. See next item
29.) unixODBC-devel-2.2.11-7.1.i386.rpm <<< both ARCH's are required. See previous item.

Command to check:

rpm -qa |grep -E "binutils|compat-libstdc++|elfutils-libelf-|glibc-|glibc-common-|ksh-|libaio-|libgcc-|libstdc++|make|gcc|sysstat|unixODBC"

6.) Values for /etc/sysctl.conf (these are suggested, these values should be calculated with respect to the memory/cpu/processes of the server.)

kernel.shmall = physical RAM size / pagesize For most systems
kernel.shmmax = 1/2 of physical RAM eg. for 32g ram it should be 17179869184.
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 409200
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

7.) Make sure u have configured a scan IP, for more details on what is scan IP please refer to: http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
Make sure your /etc/hosts file has entries or hostname, host IP address, Interconnect name, Interconnect IP address, virtual hostname, virtual host IP address for all RAC nodes and on all RAC nodes.

8a.) Since we are using Network Time Protocol (NTP) for synchronization of time across all the servers in the cluster, a mandatory requirement with 11gR2 is to enable the slewing option by adding ‘-x’ argument in the ntp configuration file as seen below: -
# vi /etc/sysconfig/ntpd

# Drop root to id 'ntp:ntp' by default.
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid" #add -x in the options string.
# Set to 'yes' to sync hw clock after successful ntpdate
SYNC_HWCLOCK=no
# Additional options for ntpdate
NTPDATE_OPTIONS=""

Restart ntpd daemon.
[root@node1 oracle]# /etc/init.d/ntpd stop
Shutting down ntpd: [ OK ]
[root@node1 oracle]# /etc/init.d/ntpd start
ntpd: Synchronizing with time server: [ OK ]
Starting ntpd: [ OK ]

8b.) Update /etc/security/limits.conf with:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

9.) Add following line in the /etc/pam.d/login file:

session required pam_limits.so

10.) Add the following lines to /etc/profile:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

11.) Setup user equivalence between the oracle user of the rac nodes.
12.) Run Cluverify (change directory where you have copied and unzipped the oracle 11gr2 grid software)
#./runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

--Fix any error you see.

13.) Start ./runInstaller
Screenshots:



Welcome Screen

14.)


Select Advanced Installation

15.)


Select all required languages, I have selected only English

16.)


Specify scan name, cluster name, and scan listener port.

17.)


Provide RAC node information, along with virtual name.

18.)


Review is the the interfaces, subnet mask are correct and select the public/private accordingly.(private is for interconnect).


19.)


Oracle will do some validation checks

20.)


Select ASM/shared file system, depending on what u are using. I am using shared file system.

21.)


Select location for OCR.

22.)


Select location of voting disks.

23.)


Failure Support, I dont plan to use this.

24.)


Select the OS groups.

25.)


Warning message, ignore it

26.)


Specify oracle_base and grid installation folder.

27.)


Specify Oracle Inventory Location.

28.)


Oracle perform pre-checks. I am ignoring swap space issue.

29.)


Final summary page!!

30.)


Ignore this warning

31.)


Shows installation Progress

32.)


Run the scripts

33.) Run /oracle/oraInventory/orainstRoot.sh

[root@node1 oraInventory]# ./orainstRoot.sh
Changing permissions of /oracle/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /oracle/oraInventory to dba.
The execution of the script is complete.


34.) Run $GRID_HOME/root.sh

[root@node1 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-08-27 23:25:19: Parsing the host name
2010-08-27 23:25:19: Checking for super user privileges
2010-08-27 23:25:19: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
root wallet
root wallet cert
root cert export
peer wallet
profile reader wallet
pa wallet
peer wallet keys
pa wallet keys
peer cert request
pa cert request
peer cert
pa cert
peer root cert TP
profile reader root cert TP
pa root cert TP
peer pa cert TP
pa peer cert TP
profile reader pa cert TP
profile reader peer cert TP
peer user cert
pa user cert
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
Now formatting voting disk: /u05/cludata/votedisk1.
Now formatting voting disk: /u01/oradata/orcl/cludata/votedisk2.
Now formatting voting disk: /u02/oradata/orcl/cludata/votedisk3.
CRS-4603: Successful addition of voting disk /u05/cludata/votedisk1.
CRS-4603: Successful addition of voting disk /u01/oradata/orcl/cludata/votedisk2.
CRS-4603: Successful addition of voting disk /u02/oradata/orcl/cludata/votedisk3.
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE a893117617e74ffebfd7a379396f281d (/u05/cludata/votedisk1) []
2. ONLINE 018eafc00dfd4fa9bf568fa245581dcf (/u01/oradata/orcl/cludata/votedisk2) []
3. ONLINE 0460f0a8560d4f0abf624c47870654fd (/u02/oradata/orcl/cludata/votedisk3) []
Located 3 voting disk(s).
CRS-2673: Attempting to stop 'ora.crsd' on 'node1'
CRS-2677: Stop of 'ora.crsd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'node1'
CRS-2677: Stop of 'ora.ctssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'node1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'node1'
CRS-2677: Stop of 'ora.cssd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'node1'
CRS-2677: Stop of 'ora.gpnpd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'node1'
CRS-2677: Stop of 'ora.gipcd' on 'node1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'node1'
CRS-2677: Stop of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.mdnsd' on 'node1'
CRS-2676: Start of 'ora.mdnsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'node1'
CRS-2676: Start of 'ora.gipcd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node1'
CRS-2676: Start of 'ora.gpnpd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node1'
CRS-2676: Start of 'ora.cssdmonitor' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node1'
CRS-2672: Attempting to start 'ora.diskmon' on 'node1'
CRS-2676: Start of 'ora.diskmon' on 'node1' succeeded
CRS-2676: Start of 'ora.cssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node1'
CRS-2676: Start of 'ora.ctssd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'node1'
CRS-2676: Start of 'ora.crsd' on 'node1' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'node1'
CRS-2676: Start of 'ora.evmd' on 'node1' succeeded

node1 2010/08/27 23:36:53 /oragrid/product/11.2/cdata/node1/backup_20100827_233653.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 12287 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/oraInventory
'UpdateNodeList' was successful.


Run the same on node2


[root@node2 11.2]# ./root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oragrid/product/11.2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2010-08-27 23:44:15: Parsing the host name
2010-08-27 23:44:15: Checking for super user privileges
2010-08-27 23:44:15: User has super user privileges
Using configuration parameter file: /oragrid/product/11.2/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Adding daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node node1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'node2'
CRS-2676: Start of 'ora.mdnsd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'node2'
CRS-2676: Start of 'ora.gipcd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'node2'
CRS-2676: Start of 'ora.gpnpd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'node2'
CRS-2676: Start of 'ora.cssdmonitor' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'node2'
CRS-2672: Attempting to start 'ora.diskmon' on 'node2'
CRS-2676: Start of 'ora.diskmon' on 'node2' succeeded
CRS-2676: Start of 'ora.cssd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'node2'
CRS-2676: Start of 'ora.ctssd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'node2'
CRS-2676: Start of 'ora.crsd' on 'node2' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'node2'
CRS-2676: Start of 'ora.evmd' on 'node2' succeeded

node2 2010/08/27 23:47:32 /oragrid/product/11.2/cdata/node2/backup_20100827_234732.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 12287 MB Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /oracle/oraInventory
'UpdateNodeList' was successful.


Press "OK" after running all the scripts on all the nodes.

35.)


I received the above error, this can be fixed later. Refer to the post
Oracle: Failed to initialize GPnP

Pres "OK" and then press "Skip" on the original screen.



36.)


Oracle 11gr2 Grid Infrastructure for a 2 Node RAC has completed successfully.

Oracle: Failed to initialize GPnP

Received error: Failed to initialize GPnP, during Oracle 11gR2 Grid installation.
This failure occurred in the step after running the root.sh, "Oracle Private Interconnect Configuration Assistant".
This error occurs when oracle tries to execute "oifcfg setif".
Error Stack:

INFO: Started Plugin named: Oracle Private Interconnect Configuration Assistant
INFO: Found associated job
INFO: Starting 'Oracle Private Interconnect Configuration Assistant'
INFO: Starting 'Oracle Private Interconnect Configuration Assistant'
INFO: Failed to initialize GPnP
WARNING:
INFO:
INFO: Completed Plugin named: Oracle Private Interconnect Configuration Assistant
INFO: Oracle Private Interconnect Configuration Assistant failed.
INFO: Oracle Private Interconnect Configuration Assistant failed.



The issue occurs when we start installation with a wrong ORA_NLS10 environment parameter. Best is to unset the variable before installation or change it to point to $GRID_HOME/nls/data:

unset ORA_NLS10
or
export ORA_NLS10=$GRID_HOME/nls/data


After doing that run the command:
[root@node1 oracle]# oifcfg setif -global eth1/10.100.3.0:cluster_interconnect
[root@node2 oracle]# oifcfg setif -global eth1/10.100.3.0:cluster_interconnect

Here eth1 is interconnect interface, 10.100.3.0 is the subnet for the interconnect IP address.

Syntax details for oifcfg command:

Name:
oifcfg - Oracle Interface Configuration Tool.

Usage: oifcfg iflist [-p [-n]]
oifcfg setif {-node | -global} {/:}...
oifcfg getif [-node | -global] [ -if [/] [-type ] ]
oifcfg delif [{-node | -global} [[/]]]
oifcfg [-help]

- name of the host, as known to a communications network
- name by which the interface is configured in the system
- subnet address of the interface
- type of the interface { cluster_interconnect | public }


ORA-12060: During Mview Creation

Well I had racked my brains a lot to create an mview on a large table across geographies, and it failed a few times.
Plus to increase my problem the character set was also different between the two DBs.:

Master Site:
SQL> select * from nls_database_parameters@link1 where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET WE8MSWIN1252

Mview Site:
SQL> select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8


I started by creating the mview directly:

create materialized view user.test1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "users"
PARALLEL 4
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH sysdate+0 NEXT SYSDATE+6/24
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select * from user.test1@link1;


This ran for ever and finally failed with ORA-01555, as expected, as it ran for almost a day.
To give a background the size of the table is 1G.

Then I finally decided to use the PREBUILT Table option:
So I took an export backup of that table in the master site and imported it in mview site.
And then tried to recreate the mview with PREBUILT TABLE option:

create materialized view user.test1 ON PREBUILT TABLE
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

Which failed with:
Col1,
*
ERROR at line 6:
ORA-12060: shape of prebuilt table does not match definition query


Well I checked the table structure and realized that the col size for char data type had become 2 folds as during import, due to character set conversion this happens.

Then I searched metalink, etc, and found an options called "WITH REDUCED PRECISION", to be used when there is col mis-match.

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

But it still failed:

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query

What resolved the problem is removing "with primary key for update"

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast on demand
as
select col1,col2 from user.test1@link1;

Materialized view created.