unable to extend temp segment by 8192 in tablespace MSCX
Issue
Development team send email with given below issue.
Concurrent program failed with given error.
----------------------------------------------------------
<
Sunday, September 14, 2008
Unable to invoke "SOAP Endpoint URI" URL
Issue Development team were unable to invokd "SOAP Endpoint URI" URL from ESB console. Environment SOA Suite 10.1.3.1 Installation and Update to 10.1.3.3 Produce Error Login to ESB console http://jai1.singh.com:8890/esb User id = oc4jadmin Password = ******** Click on “WSF_To_OM_Integration” “Validate_Line_Attribute” and then Link against “SOAP Endpoint URI” It opens blank page with error "The page cannot be found" Troubleshooting Steps http://jai1.singh.com:8890/em User id = oc4jadmin Password = Go to Home à “Web Services” à Click on Any “Port Name” e.g. “__soap_ChangeStatus_to_DB_RS_execute_ppt” It has reference of two test link one using 8890 (http) and other one 4443 (https). Note--> We never configured https protocol on this server. We searched for text file having reference to port 4443 and noticed $ORACLE_HOME/j2ee/home/config/secure-web-site.xml find . -type f -printxargs file cut -f1 -d: xargs grep -i -l "4443" tee -a 4443.log Solution - This file was not there on any instance and analyst advised to move this file to some other name. $mv $ORACLE_HOME/j2ee/home/config/secure-web-site.xml $ORACLE_HOME/j2ee/home/config/secure-web-site.xml.orig - Restarted opmn services
Monday, September 8, 2008
Change ONS Port
Unable To Start Opmn - "Listener bind failed" error ins ons.log
Problem
Unable To Start Opmn - "Listener bind failed" error ins ons.log
I was not able to start opmn services. I noticed given below error in $ORACLE_HOME/opmn/logs/ons.log file
08/09/08 18:43:18 [2] BIND (Address already in use)08/09/08 18:43:18 [2] 10.11.62.124:394526720 - listener BIND failed08/09/08 18:43:18 [4] Listener thread 2057: 10.11.62.124:394526720 (0x404) termi
ons port was 6220
$ cat $ORACLE_HOME/opmn/conf/ons.conf
nodes=ios5102e.idc.ikon.org:6220
I checked uses of 6220 port.
netstat -na grep 6220
There was no process still I was getting above error.
Solution
I performed following.
1. Changed ons port of $ORACLE_HOME/opmn/conf/opmn.xml file
$cp $ORACLE_HOME/opmn/conf/opmn.xml $ORACLE_HOME/opmn/conf/opmn.xml.090908
2. Changed notification-server port from 6020 to 6060.
Friday, August 31, 2007
Connect to multiple Applications 11i Instance using ADI
I had issue with connecting to ADI to individual instance when more than one Oracle Applications were running on same server. I performed following to overcome issue.
1. Defined the RRA: Service Prefix in each of your instances at site level as FNDFS_ntest.
2. Added in the Desktop PC tnsnames.ora in $ORACLE_HOME/network/admin and $ORACLE_HOME/net80/admin should be equal and include definitions for FNDFS which name should be FNDFS_ FNDFS_ntest_nfinox = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=nfinox)(PORT=1522))
(CONNECT_DATA=(SID=FNDFS))
)
Create Global oraInventory for Oracle Applications 11i
Some time I am not able to apply database patch (using opatch) to RDBMS home due to global inventory issue. So I thought of putting all the steps together to recreate global inventory problem. 1. Login as oracle user and identify the oracle Inventory location by referring to content of /var/opt/oracle/oraInst.loc.
$ cat /var/opt/oracle/oraInst.loc inventory_loc=/local/oracle/admin/ntst/oraInventory inst_group=dba2. Renamed old inventory
$cd /local/oracle/admin/ntst$mv oraInventory oraInventory.old$chmod 777 oraInventory
$ . $APPL_TOP/APPSORA.env$ pwd /reports/ntrg/patches/secjuly07 $ echo $IAS_ORACLE_HOME /apps/OEBS/ntrg/iAS $ unzip -od /apps/OEBS/ntrg/iAS p5035661_11i_HPUX11.zip$ cd $IAS_ORACLE_HOME/appsoui/setup $ pwd /apps/OEBS/ntst/iAS/appsoui/setup $ perl OUIsetup.plCheck log from above script and ensure it was successful.
$ pwd /local/oracle/admin/ntrg/oraInventory$ chmod -R 777 *
$cd /reports/ntrg/patches/secjuly07 $unzip -od $ORACLE_HOME p5035661_11i_HPUX11.zip$ cd $ORACLE_HOME/appsoui/setup $pwd /local/oracle/product/9.2.0.8.2/appsoui/setup$perl OUIsetup.plLog file : /local/oracle/product/9.2.0.8.2/appsoui/setup/OUIsetup.log
$cd $ORACLE_HOME/OPatch/$./opatch lsinventory
$export DISPLAY=:0.0 $cd $ORACLE_HOME/appsoui/oui/install$./runInstaller.shIt will invoke universal installer and you can see RDBS home registered there.
Custom Forms/Menu Failes with Listener Servlet with Oracle Applications 11i
Last week we configured oracle application forms from socket mode to servelete move in production. We were not able to start any forms/menus from oracle applications. When we further investigated issue and did following.
1. Go to $APACHE_TOP/Jserv/etc/
2. Add all custom tops in the formservlet.ini file.
Put custom top between “#Begin Customization” and “End Customization”.
#Begin Customization
SEXX_TOP=/apps/OEBS/
Thursday, August 30, 2007
How to turn on trace for apache and Jserv to troubleshoot Selfservice Issue?
Most of time Oracle ask us to generate trace file of Apache and Jserv so I though of putting all steps together. 1. Please enable debug in your Apache Conf files Bring down Apache - Set LogLevel debug in the following files httpd.conf, httpd.conf_pls, and jserv.conf - Edit the .../iAS/Apache/Jserv/etc/jserv.properties to enable debug for the java portion of the Apache jserv. example log=true / log.timestamp=true 2. Delete all existing logs from $IAS_ORACLE_HOME/A* ./Apache/logs/ ./Jserv/logs/ ./Jserv/jvm/logs/ 3. Restart Apache 4. Recreate your issue 5. Provide all log and config files with the following script To be executed from the .../iAS/Apache directory: zip logfile.zip \ ./Apache/logs/*_log* \ ./Apache/conf/*.conf \ ./Jserv/logs/*.log \ ./Jserv/logs/jvm/* \ ./Jserv/etc/*.properties \ ./Jserv/etc/*.conf \ ./Jserv/etc/*.ini \ ./Jserv/etc/*.txt
Oracle Applications Clear Cache
Refer given below to clear cache in E-Business Suite.
1. Apache
- shutdown iAS server- go to $OA_HTML or $COMMON_TOP (11.5.10) directory- remove
the directory _pages- restart iAS server
2. Browser
-Netscape :- go to menu Edit => Preferences…, - choose 'Cache' in 'Advanced' category,- click
on buttons 'Clear Memory Cache' and 'Clear Disk Cache'.- close all Netscape windows and
restart new browser session.
-Internet Explorer :- go to menu Tools => Internet Options…,- select 'General' tab,- click on
button 'Delete Files' in 'Temporary Internet files' zone- close all IE windows and restart new
browser session.
3. Jinitiator
-Two possibilities depending of Jinitiator version, for instance:
- delete all files in directory :
C:\Program Files\Oracle\Jinitiator 1.1.7.27\jcache\
(Jinitiator version directory may be different)
or
- go to Start => Parameters => Control Panel- double-click on Jinitiator 1.3.1.18 (or other
version)- in the new pop-up window, click on "Cache" Tab- click on "Clear Jar Cache "
button. On prompt, click Yes.
(you can also delete directly all files under directory:C:\Documents and Settings\
Friday, July 20, 2007
Oracle Critical Patch Update–July 2007
As part of security concern DBA has to apply Oracle Critical Patch regularly as and when there is critical security patch release from Oracle. Oracle started critical security patch in 2005 and since then there is four-security patches release in year. For year 2007 following are four dates:
- 17 July 2007
- 16 October 2007
- 15 January 2008
- 15 April 2008
Last we applied critical security patch July 2006. As part of regular critical security patch update we are going to apply “Oracle Critical Patch Update – July 2007” by referring given URL.
Oracle E-Business Suite Critical Patch Update Note July 2007
Note: 432882.1
Before analyzing Patch requirement I would list down product installed.
- Oracle E-Business Suite Release 11i 11.5.10 CU2
- Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
- Oracle Application Server 10g Release 2 (10.1.2), versions 10.1.2.0.2
We have running Oracle E-Business Suite Rel. 11i so I am referring “Section 2, "Oracle E-Business Suite Release 11i"” of Note: 432882.1
As per oracle guideline we should apply updates to systems that have highest risk first e.g. update systems that are external to firewall before upgrading internal network system. It our environment we do not have any external system.
Oracle has recommended patching Oracle environment in given order and I follow same.
1. Oracle Database
2. Oracle Application Server
3. Oracle E-Business Suite
Patching Considerations for Oracle E-Business Suite Release 11i
Oracle Database and Oracle Application Server critical patches are cumulative, which means it includes all patches for fixes from earlier Oracle critical security patch updates. CPU patches for Oracle E-Business Suite 11i are generally not cumulative, that means one has to refer earlier critical updates and apply all the e-business suite critical patches in chronological order.
In my case I had applied Critical update patch July 2006 and now I need to apply Critical update patch July 2007. So in this case I should apply patches as given below.
1. Oracle Database Critical Update patches is cumulative so I need to apply only critical update patch July 2007.
2. Oracle Applications Server Critical Update patches is cumulative so I need not to apply Critical update patch July 2007.
3. Oracle E-Business Suite Critical Update patches is not cumulative so I need to apply Critical update patch Oct 2006, Jan 2007, Apr 2007 before apply Critical update patch July 2007.
As per Note: 432882.1 Release 11.5.10 applications upgraded to release 11.5.10 CU2 without apply CPUApr2007 must now apply CPUApr2007. E-Business Suite are not cumulative, so we one should apply prior CPUs as appropriate before applying CPUApr2007.
Stop all process running using that Oracle home before patching. Patch one oracle home at a time.
Oracle recommends updating database tier and the middle tier at same time, one after the other. After applying patch start services in following order. We have only these two components in our environment.
1. Oracle Database
2. Oracle Application Server
‘opatch’ is required to apply database patch.
$ pwd
/local/oracle/product/9.2.0.8/OPatch
$ opatch -version
Oracle Interim Patch Installer version 1.0.0.0.56
Oracle recommends to use OPatch release 1.0.0.0.57 so need to install patch 2617419
Patches to be applied in Ebusiness Suit.
Noteà We are Running Ebusiness Suite in HP-UX 11i environment.
Oracle Database Patches
Oracle Database 9.2.0.8 Rel Patch 6079579
Oracle HTTP Server Patche
There are no new patches for CPUJul2007. Please uptake the patches listed for this release in CPUJan2007. See Note 402670.1: Oracle E-Business Suite Critical Patch Update Note January 2007.
As per Note 402670.1
1. If you have not already done so, upgrade to release 1.0.2.2.x. See Note 146468.1.
2. Upgrade to Oracle8i Database release 8.1.7.4 using patch 2376472 if your Oracle Database Client release is earlier than release 8.1.7.4. See Note 309026.1.
3. Apply CPUJan2007 patch 5700129 to the Oracle Application Server Oracle home.
We have applied upgraded to Orace8i 8.1.7.4 home using patch 2376472 so I need to apply only CPUJan2007 patch 5700129.
Oracle Developer Suite Patches
We are at Oracle Developer Suite Release 6.0.8.27.0. According to Note 402670.1 “There are no new patches for CPUApr2007. Please uptake the following patches which were listed for this release in CPUJan2007. “
· Oracle Forms (HP-UX): 5687261
· Oracle Reports (HP-UX): 5686997
· Release 8.0.6 Oracle home: 5078711
JInitiator Patches
We are using Jinitiator Release 1.3.1.26 and as Note 402670.1 “Per Oracle policy, customers on Oracle JInitiator Release 1.3 must move to version 1.3.1.26 or higher for CPUApr2007.” Refer to Note 124606.1 and Note 232200.1 for more information.
We’ll upgrade Jinitator Release from 1.3.1.26 to 1.3.1.28.
As per Note 124606.1 I need to apply following patches to upgrade Jinitiator to 1.3.1.28.
JInitiator Version JInitiator Patch Interop Patch
Apart from above patches following patches are required for respective product users. I am listing only those patches, which are applicable in our environment.
- Oracle Applications Framework Navigator Page
If you are using Oracle Applications prior to 11.5.10.2CU, please apply patch 4517707.
- General Ledger Hierarchy Manager / Financial Intelligence - Financial Dimension Hierarchy Manager Users
If you are upgrading to JInitiator 1.3.1.x and are using Oracle Applications Framework 5.7 or later, you must apply patch 3698893.
Oracle E-Business Suite Release 11i Patches
Oracle CPUApr2007 patches for E-Business Suite Release 11i are not generally cumulative. We need to apply the previous critical patch updates and Oracle security alerts related to ebusiness Suite before applying this critical patch update.
In my case I need to apply following patches.
E-Business Suite Critical Patch October 2006
· 5486407
· 5479643 Superceded by 5473858 (11i.ATG_PF.H.RUP5)
· 5500118 Superceded by 5473858 (11i.ATG_PF.H.RUP5)
· 5335967
E-Business Suite Critical Patch January 2007
· 5571208 Superceded by 5473858
· If your instance is at 11i.BNE.D, then apply patch 5518587 Supercede by patch 5473858
· 5658489
· 3748835
· 5711485
E-Business Suite Critical Patch April 2007
Patch 5021981 (FND)
Release 11.5.10 CU2
· 5893391
· 5900224
· 5352601
· 5720979
· 5738134
· 5909233
· 5873313
· 5904576
· 5967405
Oracle E-Business Suite 11i with 11i.ATG_PF.H RUP4 (4676589) installed
· 5873313
· 5904576
· 5900224
· 5938358
· Any other patches listed for the Oracle E-Business Suite Release 11i base release on which 11i.ATG_PF.H RUP4 is installed.
E-Business Suite Critical Patch July 2007
11i with 11i.ATG_PF.H RUP4 (4676589) installed
· 5973659
· 6045931
Release 11.5.10 CU2
· 6117954
· 6045983
· 5973898
· 5973651
· 5973629
· 6185758
· 6082483
· 6110945
· 6111066
· 6111008
· 6188705
Reference:
Oracle E-Business Suite Critical Patch Update Note January 2007 Note:402670.1
Oracle E-Business Suite Critical Patch Update Note April 2007
Note:420072.1
Oracle E-Business Suite Critical Patch Update Note July 2007
Note:432882.1
Wednesday, July 18, 2007
Generate SQL tkprof
Very often we need to generate tkprof output of sql/plsql to troubleshoot performance issue so I though of putting all steps together.
- Login as SQLPLUS.
SQL>connect apps/appsndev6 Connected.
- Set SQL Trace ON
SQL> ALTER SESSION SET SQL_TRACE = TRUE; SQL> alter session set events '10046 trace name context forever, level 8';
- Get SPID number to identify trace file generate at UDUMP location.
SQL> SELECT spid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE audsid=userenv('SESSIONID') ); SPID ------------ 18979
- Run SQL to generate trace
SQL> begin BIS_MV_REFRESH.refresh_wrapper ('FII_GL_BASE_MAP_MV', 'f', 0); end;
- Go to RDBMS udump location and list *SPID* return from previous query.
nysesapd:/local/oracle/admin/ndev6/udump $ls -l *18979* -rw-rw-r-- 1 oracle dba 238565 Jul 18 14:56 ndev6_ora_18979.trc
- Generate tkprof output as given below.
tkprof ndev6_ora_18979.trc ndev6_ora_18979.tkp sort=exeela,prsela,fchela mailto:explain=apps/appsndev6@ndev6
Tuesday, July 17, 2007
Forms Listener Servlet with Oracle Applications 11i
As part of DMZ configuration we were suppose to convert Forms to Forms Listener Servlet.
As part of configuration I performed following steps.
Ensured Forms Patchset level is 16 or higher. We are at patchset level 18.
Ensured Instance is Autoconfig enable.
Ensured patch 3594604 has been applied. We have applied 11.5.10 Rel 2 maintenance patch 3480000 which is superseded patch of 3594604.
Logged in to OAM. OAM Site Map -> AutoConfig-> Configuration Wizard -> Forms Listener Servlet and Choose the Enable or Disable button to enable or disable "Forms Listener Servlet".
Run autoconfig.
$ ./adautocfg.sh Enter the APPS user password: AutoConfig is configuring the Applications environment...
Tuesday, June 26, 2007
Copy Putty along with saved server
How to copy window based putty from one server to another along with its saved server configuration?
Very recently I got new desktop with high configuration. I copied all data and installed new software also. Only problem was how to copy putty along with saved server configuration? I performed following to copy putty along with save session.
1. Executed given putt.bat file on source server at .
c:\temp:\>putt.bat
Conetnt of putt.bat is given below
ECHO OFF regedit /ea new.reg HKEY_CURRENT_USERSoftwareSimonTathamPuTTY
2. Copied putty.exe and new.reg (registry) file to new server.
3. Double click on new.reg and click on yes on dialog box
4. You can now start putty and connect to server.
Note--> You need to ensure network connectivity before trying to connect to server :-).
Sunday, June 24, 2007
Automatic Storage Management
Automatic Storage Management provides easy file management. DBA need not to manage huge physical file manual. ASM manage database physical files in disk. ASM uses Oracle-managed files and provides benefits of mirroring and striping. It divides files into 1 MB extents and extents get store in all disk available in disk group. So manual intervention to tune disk IO is not required. ASM provides the redundancy level on a per-file basis. In this way two file (original and mirror) can be stored on same disk group. ASM provides mirroring at extent level. ASM provides three mirroring types.
· 2-Way Mirroring I mirror copy of every extent
· 3-Way Mirroring 2 mirror copy of every extent
· Unprotected No mirroring. (Mirroring provided by the disk subsystem)
ASM provides features to manage storage without stopping database. When you add/drop disk in/from disk group ASM re balances file data across all the disks of disk group. ASM can be used with old database file system. ASM requires one Oracle instance with SGA(smaller SGA) and background process. ASM instance can be used for more that one database. ASM diskgroup can be shared among all databases running on that server.
In RAC, each instance will have an ASM instance, which communicate to other node ASM instance. ASM instance in RAC environment can manage one or more that one RAC database or one or more than one single instance database. ASM supports database data files, control files, log files, archive files. You need to use RMAN to backup ASM database.
ASM Components
· Groups
A disk group is group of disk managed together as one unit. Disk group may store various database's files. Single database may use storage from multiple disk groups. Disk group type specifies disk group mirroring. There are three type of redundancy
Normal Redundancy (2 way) (Default Redundancy)
High Redundancy (3 way)
External Redundancy (none or unprotected)
· Disk
ADM disks is disks in disk group, witch is a partition of a logical unit number (LUN) or a network attached file.
· Failure Group
Failure group is applicable only to normal and high redundancy disk groups. Failure group define common potential failure mechanism.
· Files
Files are written on ASM disk called ASM files, which are spaced across all the ASM disks in the disk group. ASM automatically generate file names. User-friendly file name also can be specified. A ASM file can be stored in only one disk group.
· Templates
Collections of file attributes, which are used to set mirroring/striping of database file are created in ASM disk group are called templates
ASM Two new Background Process
ASM instace should be started before starting datbase instance. When you start ASM instace it does not mount ASM database it manages meta data required to make ASM files are available to all ASM storage database instances. . ASM starts background process and allocate SGA (SGA is very less compated to regulare database SGA). All the database running on ASM storage communicate to ASM database to get the disk group infomation. ASM instance starts two more background process RBAL and ASMB, which are not availagle in regualr oracle database or RAC database. RBAL coordinate rebalances activity for disk groups whereas ASMB performs the actual rebalance activity for AU movements. Rest of ASM background process are same as Oracle instance. When ASM instance starts, ASMB connects as a foreground process into the ASM instance and RBAL performs global open of disk groups.
ASM and Recovery feautre
In RAC enviorment where multiple ASM instance mounts same disk groups. If one instance fails other instance automatically recovers ASM metadata changes caused by failed instance.
In single ASM instance where multiple ASM instance mounts different disk groups, if ASM instance failes during its uses, disk groups are not recoverered util they are monted again. When ASM mounts failed disk groups it reads logs and recovers all changes. Such senario is called ASM crash recovery. So in RAC invironment it is always recommened to mount same set of disk group on all ASM instance.
ASMLibs
ASMLibs is ASM library to support ASM feature. It provides efficeint machanism for accessing block devices used by the ASM disk groups. ASM library provides ASM I/O to Linux disk without the limitations of the startard UNIX I/O API.
Detail installation of ASMLibs and ASM i will cover in my other post. I would like to discuss here only basic aspect of ASMLib.
Following three package are required to install ASMLib which can be downloaded from http://www.oracle.com/technology/tech/linux/asmlib/index.html
oracleasm-support-2.0.3-2
oracleasm-2.6.9-42.0.0.0.1-Elsmp-2.0.3-2
oracleasm-2.6.9-42.0.0.0.1.EL-2.0.3-2
Exact name depends on version of Linux(OS), which I will discuss in my next blog.
After installing ASMLib package you can configure ASMLib by executing following
# /etc/init.d/oracleasm configure
You can create ASM disk as given below
# /etc/init.d/oracleasm createdisk VAOL1 /dev/sdc1
You can scan ASM on other node in RAC
#/etc/init.d/oracleasm scandisks
To list ASM disk
#/etc/init.d/oracleasm listdisks
Install ASMLib
Check kernal version and system architecutre
# uname -r
2.6.9-42.0.0.0.1.ELsmp
I downloaded following ASMLib package
oracleasm-support-2.0.3-2
oracleasm-2.6.9-42.0.0.0.1-Elsmp-2.0.3-2
oracleasm-2.6.9-42.0.0.0.1.EL-2.0.3-2
Install ASM package.
$rpm -Uvh oracleasm-support-2.0.3-2 \
oracleasm-2.6.9-42.0.0.0.1-Elsmp-2.0.3-2 \
oracleasm-2.6.9-42.0.0.0.1.EL-2.0.3-2
Configure ASM
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library driver. The following questions will determine whether the driver is loaded on boot and what permissions it will have. The current values will be shown in brackets ('[]'). Hitting <ENTER> without typing an answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Creating /dev/oracleasm mount point: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
"oracleasm configure" creates "/etc/sysconfig/oracleasm" configuration file and "/dev/oracleasm" mount points. It loads oracleasm kernel module and mounts ASM library driver file system.
ASM Disk Creation
Lists disk name
# /sbin/fdisk -l
Create ASM disk
# /etc/init.d/oracleasm createdisk disk1 /dev/sdb1
Make available asm disk to other node.
# /etc/init.d/oracleasm scandisks
# /etc/init.d/oracleasm -help
Usage: /etc/init.d/oracleasm {startstoprestartenabledisableconfigurecreatediskdeletediskquerydisklistdisksscandiskstatus} start/stop/restart: To start/stop/restart ASM library driver without restarting the system. enable/disable: To enable/disable ASM library driver when system starts. configure: To configure the ASM library driver createdisk: To use disk with ASM library deletedisk: To unmark a name disk querydisk: To check if disk name is being used by ASM Library driver. listdisks: To list disk names of marked ASM library driver disksscandisks: To inform RAC node which shared disks are marked as ASM library disks on another node.
Once disk is prapared I have set ASM_DISKSTRING parameter as /dev/oracleasm/disks/*, default value is "ORCL:DISK*".
ASM Database Instance Creation
To create ASM based database DBCA checkes if ASM instance is running on current node, if yes, it lists disk groups. If ASM is not running DBCA creates a new ASM instance. ASM instance are very small, 64MB SGA is enough for largets ASM installations.
ASM Instance Initialization Parameters
INSTANCE_TYPE=ASM # Set to ASM for ASM instance DB_UNIQUE_NAME=+ASM # Uniqe DB Name, defaul value +ASM ASM_POWER_LIMIT = 1 # Controls rebalance operation speed, Range 1 to 11(fastest), default value is 1. ASM_DISKSTRING= '/dev/oracleasm/disks/*', , ,
# ASM instance discovers disk mounted using this parameter if specified value is NULL ASM_DISKGROUPS=ORCL_DATA1,FLASH_RECOVERY_ARE # List of disk groups which should be mounted when instance starts or when "ALTER DISKGROUP ALL MOUNT is used"LARGE_POOL_SIZE=8MB
ASM instance & RAC
Select DBCA "Oracle RAC" database option and then select all nodes. DBCA create one ASM instance on each node automatically.
CLUSTER_DATABASE=TURE # Set this parameter in each ASM RAC instace to start global cache services.
ASM_DISKGROUP= ' ' # Instances can have different value but it is recomended to mount same value. Shared disk must be mounted on all ASM instance.
ASM_DISKSTRING=' ' * Multiple instance may have different value
Startup/Shotdown ASM Instance
SQL> connect / as sysdba
SQL>startup
Mount option mounts disk groups specified by ASM_DISKGROUPS init parameter. OPEN is invalid and NOMOUNT starts up ASM without mounting any disk group.
You can not stop ASM instance till its all client database instance are stopped. In case of SHUTDOWN NORMAL ASM waits for all session to disconnect . ASM SHUTDOWN IMMEDIATE, TRANSACTIONA, or ABORT, ASM immediately terminates its database instance connections hence all database immediately abort. When IMMEDIATE or TRANSACTIONAL is uses, ASM waits for in-progress ASM SQL to complete before shutting down ASM instance.
SQL> connect / as sysdba
SQL> SHUTDOWN IMMEDIATE
Create Disk Group
SQL>CREATE DISKGROUP ORCL_DATA1 NORMAL REDUNDANCY DISK 'ORCL:VOL1';
Drop disk group
SQL> DROP DISKGROUP ORCL_DATA1 INCLUDING CONTENTS;
Add Disk
SQL> ALTER DISKGROUP ORCL_DATA1 ADD DISK
'ORCL:VOL2';
SQL> ALTER DISKGROP ORCL_DATA1 ADD DISK '/dev/oracleasm/disks/*';
Above SQL will add disks (/dev/oracleasm/disks/*), which were not not added. ASM instance ensure that disk is usable and then it formates and reblances. It moves AUs from every file onto the new disks.
Drop disks.
SQL> ALTER DISKGROUP ORCL_DATA1 DROP DISK '/dev/oracleasm/disks/VOL4';
Cancel Previous Drop, it works for only pending drops not after drop completion.
SQL> ALTER DISKGROUP ORCL_DATA1 UNDROP DISKS;
To rebalance disk. POWER level of "0" halts rebalancing until command is reinvoked.
SQL> ALTER DISKGROUP ORCL_DATA1 REBALANCE POWER 5;
Mount/Dismount DISK GROUP.
SQL> ALTER DISKGROUP ORCL_DATA1 DISMOUNT;
To very internal consistency of disk group metadata and repair if error found.
SQL> ALTER DISKGROUP ORCL_DATA1 CHECK ALL;
Some time it takes long time to rebalance AU across disk. Such operation can be monitored using V$ASM_OPERATION
ASM Files
ASM files, oracle managed files are stored in ASM disk groups. ASM oracle managed files are automatically deleted when not required. ASM files created using alias are not deleted automatically. Manual intervention is required to delete such files. Each file in a disk group is spread across all disks in the DISK group, so backup of single disk not not useful. RMAN must be use to backup ASM files.
ASM does not manage binaries, alert logs, trace files, password files, and Cluster Ready Services files.
ASM File Name are of following forms
Fully Qualified ASM file names are used to refer existing ASM files. It specify a disk group name, database name, a file type, a type-specific tag, a file number and an incarnation number. When file is created even using alias, a fully qualified name is also created. Fully qualified ASM file names are also called system aliases,
<group>/<dbname>/<file_type>/<tag>.<file#>.<incarnation#>
+ORCL_DATA1/orcl/controlfile/current.256.623848779
Numeric ASM File name are used for refererencing existing ASM files. It specifis a disk group name, a file number and an incarnation number.
+ORCL_DATA1.257.8675309
Alias ASM file name can be used for referncing existing ASM file and for creating new ASM files. Alias name specify a disk group name and inclu a user-fiendly string insteand of file and incarnation number. Alias file name uses hierarchical directory structure. Total length of Alias ASM file name is limited to 256 bytes. File name can have space in between but not at begining or at end. Alias ASM file names are case-insensitive.
+ORCL_DATA1/myfiles/control_file1
Using "ALTER DISKGROUP ADD ALIAS" an alias can be created for existing file.
Alias ASM file name with templates are used only while ASM file creation. It specifies a disk group name, an alias name, and a file creation template name. e.g. +ORCL_DATA/config1(spfile).
Incomplete ASM file names used during ASM file creation. It uses disk group name only. It uses a default template for incomplete ASM file name specified by their file type. +ORCL_DATA1
Incomplete ASM file names with templates are used for file creation. It uses disk group name followed by a template name. File creation attributes are determined by template name. e.g. +dgroupA(datafile).
Template and Alias
SQL>ALTER DISKGROUP ORCL_DATA1
ADD TEMPLATE DATA1TEMP ATTRIBUTES (MIRROR);
SQL> ALTER DISKGROUP ORCL_DATA1 DROP TEMPLATE DATA1TEMP;
SQL> ALTER DISKGROUP ORCL_DATA1 ADD DIRECTORY '+ORCL_DATA1/jai';
SQL> ALTER DISKGROUP ORCL_DATA1 RENAME DIRECTORY '+ORCL_DATA1/jai' TO '+ORCL_DATA1/jai';
SQL> ALTER DISKGROUP ORCL_DATA1 ADD ALIAS '+ORCL_DATA1/jai/users.dbf' FOR '+ORCL_DATA1/orcl/datafile/users.265.623849191';
SQL> ALTER DISKGROUP ORCL_DATA1 RENAME ALIAS '+ORCL_DATA1/jai/users.dbf' TO '+ORCL_DATA1/jai/users1.dbf' ;
SQL> ALTER DISKGROUP ORCL_DATA1 DROP ALIAS '+ORCL_DATA1/jai/users1.dbf' ;
SQL> ALTER DISKGROUP ORCL_DATA1 DROP DIRECTORY '+ORCL_DATA1/jai';
SQL> ALTER DISKGROUP ORCL_DATA1 DROP FILE '+ORCL_DATA1/jai/users1.dbf' ;
List Aliases Information
SQL> SELECT reference_index INTO :al_id FROM V$ASM_ALIAS WHERE NAME='+ORCL_DATA1';
SQL> SELECT reference_index INTO :al_id FROM V$ASM_ALIAS WHERE PARENT_INDEX=:al_id AND NAME='jai' ; REM jai is direcotry name
SQL> SELECT name FROM V$ASM_ALIAS WHERE parent_index=al_id;
ASM Data Dictionary View
V$ASM_DISKGROUP To check disk group information (Number, name, size, state, redundancy type). in DB instance one row for every disk group mounted by ASM instance.
V$ASM_DISK One row for every discovered disk in ASM instance. In DB instance, disk used by database.
V$ASM_FILE In ASM instance one row for every ASM file in all disk group mounted by ASM instance. DB instance contains no rows.
V$ASM_TEMPLATE In ASM and DB, one row for every templated present in disk group mounted by ASM instance.
V$ASM_ALIAS In ASM instance one row for every alias present in every disk mounted by ASM
V$ASM_OPERATION, To check long running ASM opertaion in ASM instance
V$ASM_CLIENT In ASM instance lists databases using disk group managed by the ASM instance. In DB instance list one row if database has any open ASM files.
Tuesday, June 12, 2007
Oracle 10g Real Application Cluster Concept
Very recently I installed two node Oracle 10g RAC Database using ASM on Oracle Enterprise Linux 4.0. I had to struggle for almost two weeks to get it done and finally I was successful. Before I jump to explain installation part of 10g RAC Database using ASM , I would like to discuss basic concept and understanding of Oracle Real Application Cluster. Whenever we think about RAC first questions comes to our mind is what is Cluster?
What is Cluster?
A Cluster is combination of more than one connected computer (or server). From applications prospective it seems to be single server processing applications requests. Multiple connected servers refer to physical files stored on shared area and are interconnected using physical network.
Oracle Real Application ClusterOracle Real Application Cluster is software that is installed on all interconnected server running on same operating system. It acts as if they were single server. Oracle clusterware has two components, a voting disk and OCR (Oracle Cluster Registry). A voting disk keeps record of all nodes’ membership whereas OCR keeps track of cluster configuration. These two component Voting disk and OCR must be on shared storage, which can be access by all interconnected cluster servers. As part of Oracle cluster ware each server (node) should be connected to private network as private interconnect. Oracle Real Application cluster provides feature to run multiple instance on various node on single database.
Advantage of Oracle Real Application ClusterOracle RAC is mainly for high availability. If one node goes down connection would get routed through other server. Service can be configured to run against multiple nodes so if one node goes down it would switch over to another node. You can add various nodes to increase processing power and you can remove node when you do not use node for processing any more. Services load management happens automatically across various nodes. RAC allows more concurrent batch process, great parallel execution with more degree of parallelism. It also allows more number of user connections.
Oracle Cluster Software ProcessesThere are several Oracle Cluster background processes for cluster related operations. Cluster background process are given below.
- Cluster Synchronization Services (CSS)
This background process manages oracle cluster configuration file. It keeps record of all nodes which are cluster members and inform other members when a node (server) join cluster or leaves the cluster. If cluster is from third party, then CSS process interacts and interfaces with clusterware to keep track of node information and manage node.
- Cluster Ready Services (CRS)
CRS manages high availability within a cluster. CRS manage cluster resources which are database, instance, service, listener, a virtual IP(VIP address), application process etc. CRS refers resource’s configuration information that is stored in the OCR (Oracle Cluster Registry) on the shared storage to manage start, stop, monitor, failover operations clusterware resources (database, instance, service, listener, VIP).
- Event Management (EVM)
This process publishes events that crs creates. The evm process scans a designated directory and invokes all of the scripts in that directory when an even occurs.
- Oracle Notification Services (ONS)
ONS publishes and subscribes services for communication Fast Application Notification (FAN) events.
- RACG
Helps clussterware to support Oracle-Specific requirements and complex resources. Runs server callout scripts when FAN events occurs.
- Process Monitor Daemon (OPROCD)
This process is locked in memory to monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running. If the wake up is beyond the expectedOracle Real Application Cluster Physical Files and Cluster Processing
time, then OPROCD resents the processor and reboots the node. An OPROCD failure results in the Oracle Clusterware restarting the node. OPROCD uses the hangcheck
timer on Linux platforms.
In Oracle RAC database all datafiles, controlfiles, PFILEs(SPFILE) and redo log files must reside on shared disk, which should be cluster compatible. So cluster node can access these physical file stored on cluster aware shared storage area.
A RAC database can have maximum 100 instances. Each RAC database has at least one additional thread of redo for each instance and undo talbespace for individual instance. For shared store following option can be used.
- OCFS (Oracle Cluster File System)
- A network file system
- Raw devices
- ASM à Oracle recommends to use ASM
RAC database has more than one instance. Each oracle RAC instance has its own memory and background process. RAC database has some additional memory structure and background processes than non RAC single instance database.
RAC instance uses bigger cache in SGA. It uses Cache Fusion so SGA size for RAC database is more than SGA size of single instance database.
To ensure data integrity for individual RAC instance, RAC use to background processes, GCS (Global Cache Service) and GES (Global Enqueue Service) . The Global Cache Service and Global Enqueue Service maintain records/status of each data file and each cached block using a GRD (Global Resource Directory).
Global Resource Directory contents are distributed across all the active instances, which increases SGA size of a RAC instance.
When one of RAC instance caches data, other same RAC database instance can obtain a block from this instance instead of reading the block from disk. Cache Fusion moves current blocks between instances rather re-reading the block from disk. If changed block is needed by another RAC instance, Cache Fusion transfers the block image directly between affected instances.
Real application uses private interconnect to communicate between instances and to transfer block. The GES monitor and the Instance Enqueue Process manages access to Cache Fusion resource and enqueue recovery.
There are additional background process for RAC instance which are used to maintain database coherency among each instance. They manage global resources:
- LMSx – Global Cache Service Process, where x can range from 0 to j
- LMD0 – Global Enqueue Service Daemon
- LMON – Global Enqueue Service Monitor
- LCK0 – Instance Enqueue Process
- DIAG – Diagnosiblity Process
Monday, June 11, 2007
Exclude SQL Net Connection from particular node (or list of nodes).
As I mentioned in previous post, we had tough time recovering production database which happened due to some SQL script update against production instead of development.
To prevent any sql client connection from development boxes to production server we did following.
1. Add following entry in $TNS_ADMIN/sqlnet.ora file on production database server.
>pwd
/local/oracle/product/9.2.0.8/network/admin
nxtffind:/local/oracle/product/9.2.0.8/network/admin
>cat sqlnet.ora
TCP.EXCLUDED_NODES= (nxtfsapq)
TCP.VALIDNODE_CHECKING = YES
nysefind:/local/oracle/product/9.2.0.8/network/admin
2. Restarted listener
$export ORACLE_SID=<sid>
$export ORACLE_HOME=/local/oracle/product/9.2.0.8
$export $TNS_ADMIN=$ORACLE_HOME/network/admin
$export PATH=$ORACLE_HOME/bin:$PATH
$lsnrctl stop ndev2
$lsnrctl start ndev2
Noteà Listener reload will not work so you need to stop listener and start it again.
3. Tried to connect to production database from development server which were included in sqlnet.ora file on RDBMS server to exclude sql * net connection coming from these server SQL client.
$hostname
nxtfsapq
$cat $TNS_ADMIN/network/tnsnames.ora
ndev2 = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=nxtffind)(PORT=1527))
(CONNECT_DATA=(SID=ndev2))
)
$ sqlplus apps@ndev2
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 11 22:34:13 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
ERROR:
ORA-12537: TNS:connection closed
Reference:
Metalink Note
What is Validnode Verification?
Doc ID: Note:1006500.6
