oracle

Oracle Database 11G Installation on Solaris 10

I had installed Oracle Database 11g on Solaris 10 few days back. I thought of documenting the steps for easy reference. Please find below steps for the same

Installation Pre-Requisite

Refer to Oracle Database Installation Guide 11g Release 1 (11.1) for Solaris Operating System for checking Hardware and Software Requirements.

User Creation and Environment Settings

1)Create groups for Oracle account

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">#groupadd oinstall
#groupadd dba
#groupadd oper</span>

2)Create Oracle Default Home directory

<span style="font-family: arial,helvetica,sans-serif; font-size: small;"># mkdir /export/home
# mkdir /export/home/oracle</span>

3)Create Oracle user

<span style="font-family: arial,helvetica,sans-serif; font-size: small;"># useradd -g oinstall -G dba -d /export/home/oracle -s /usr/bin/bash oracle
# chown oracle:oinstall /export/home/oracle</span>

4)Create Project for Oracle for setting the kernel parameters

In case of Solaris 10, you can use projects to configure the kernel parameters instead of /etc/system file. This can be done as following

<span style="font-family: arial,helvetica,sans-serif; font-size: small;"># projadd -U oracle -K "project.max-shm-memory=(priv,4g,deny)" oracle
# projmod -sK "project.max-sem-nsems=(priv,256,deny)" oracle
# projmod -sK "project.max-sem-ids=(priv,100,deny)" oracle
# projmod -sK "project.max-shm-ids=(priv,100,deny)" oracle</span>

There are many more ways of creating project entries such as group.group-name or user.user-name. For more details refer to Solaris Administration documents.

Update

Last three settings made by projmod command are not required as these values are lower than the default. This was pointed by Mike Madland and he also gave a Sun documentation link

http://docs.sun.com/app/docs/doc/819-2724/6n50b0795?l=en&a=view#chapter1-33
You can check the values for max-sem-ids and max-shm-ids with this command:

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">prctl -n project.max-sem-ids -i task `ps -o taskid= -p $$`</span>

5)Create .bash_profile for Oracle user

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">#Oracle Environment Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u03/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
ORACLE_SID=TESTDB11G; export ORACLE_SID
PATH=$PATH:/usr/local/bin:/usr/ccs/bin:/usr/sfw/bin:$ORACLE_HOME/bin</span>

Now Set the Display to a X-windowing enabled system.

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">$ export DISPLAY=192.168.4.47:0.0</span>

Also allow the host to accept the connection by

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">$xhost +</span>

Oracle Software Installation

Go to the Oracle dump location and run runInstaller as Oracle user

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">$./runInstaller</span>

This will open Oracle Universal Installer(OUI) screen. If Oracle Universal Installer is not displayed, then ensure DISPLAY variable is set correctly. Select “Software only” option and install the software. If any of the pre-requisite’s are not met , then installation will fail. You would be required to make necessary changes to proceed.

Database Creation

We will be using ASM for the Database files. For this we need to perform some configuration

1)Prepare the Raw device for using as ASM Disks

<span style="font-family: arial,helvetica,sans-serif; font-size: small;"># ls -l
total 0
crw------- 1 root root 125, 1 Jun 20 10:39 1</span>

Disk should be owned by Oracle user and should have permission set to 660

<span style="font-family: arial,helvetica,sans-serif; font-size: small;"># chown oracle:dba 1
# chmod 660 1

- # ls -ltr
total 0
crw-rw---- 1 oracle dba 125, 1 Jun 20 10:39 1</span>

2)Configure CSS Service

In case of Solaris 10, we need to use Service Management Facility (SMF) for configuring CSS service else it will not start. Refer to my earlier post for this step i.e 11.1.0.6 ASM installation on Solaris fails -II

3) Configure ASM Instance

a)Go to $ORACLE_HOME/bin

b)Execute dbca from this directory (ensure dbca is properly set)

<span style="font-family: arial,helvetica,sans-serif; font-size: small;">$./dbca</span>

c) Select Configure ASM Instance option. This will create ASM instance for you. After this you can create Diskgroups using GUI or else use sqlplus to do the same.

4)Now continue creating database normally and enter Diskgroup Name after selecting Oracle Managed files as database file location.

While you navigate through GUI screens, it will prompt you to Specifying Security Settings

– Keep the enhanced 11g security settings(recommended)
– Revert to pre 11g settings

Select the 11g settings which will enable Auditing by default and also enable Case sensitive passwords with Stronger password hashing algorithm.

I have not discussed GUI screens for DBCA and OUI in this article. These are pretty much standard screens. In case you need more information about it, then you can refer to Oracle 11g Install guide for Solaris

12 July, 2008 by Amit Bansal 11g 27

Effect of OS Terminal Setting “STTY” on Oracle Database

Few days back, while trying to create a synonym over dblink I experienced following error:

SQL> create synonym synonymn_name for dual@DBLINK9i;
SP2-0042: unknown command "DBLINK9i" - rest of line ignored

Strange error !!!

Also while trying to perform a simple select statement over another dblink:

SQL > select * from dual@dblink10g;
SP2-0042: unknown command "DBLINK10g" - rest of line ignored.

It also failed with a similar error, though the select over this dblink was working fine earlier.The tnsnames.ora entry was correct and double checked.So what happened to this dblink now?

From other database I was able to perform a simple select over same dblink.

While I was researching this, I came to know that sql queries also started failing :

SQL> UPDATE HRRECORD_TEST SET EMAIL_ADDRESS = '[email protected]' WHERE NAME = 'XYZ';
SP2-0734: unknown command beginning "123.com'..." - rest of line ignored.
SQL>

One Notable thing was that all the queries which failed contained “@” symbol and I also got this error while create a synonym over dblink (having “@” symbol).

These two issues were related, Then I tried to create a synonym locally and it went fine. This led to conclusion that sql prompt was not recognising “@” symbol.

So it was clear that the problem was with terminal settings at OS level for oracle user.

Searcing google for terminal setting at OS, I found a command which is used to Sets options for your terminal i.e “stty”. By using stty we can change the terminal settings.

Now checking the current terminal settings using:

# stty -a

speed 9600 baud; line = 0;
rows = 36; columns = 80
min = 4; time = 0;
intr = DEL; quit = ^\; erase = DEL; kill = @
eof = ^D; eol = ^@; eol2 <undef>; swtch <undef>
stop = ^S; start = ^Q; susp <undef>; dsusp <undef>
werase <undef>; lnext <undef>
parenb -parodd cs7 -cstopb hupcl -cread -clocal -loblk -crts

From this output I found that there were two settings causing problem:

1) kill = @

2) eol = ^@

Then, to overcome this I added the following two lines in the oracle user .profile

stty kill ^U

stty eol ^E

Above change will force oracle user to use ^U to kill any session and ^E to end a line instead of using @ for these purpose.

After making these changes in oracle user’s .profile everything went fine. In the end it turned out OS issue whose settings was modified by sysadmin. 😉

6 July, 2008 by Saurabh Sood Unix 1

Automatic Storage Management (ASM) FAQ

We have tried to consolidate list of Frequently Asked Questions (FAQ) on ASM. Please feel free to ask any other question or contribute to this FAQ by using comment section

Frequently Asked Question on ASM

1) What is ASM?

Automatic storage Management (ASM) is a new type of filesystem which was introduced with Oracle 10g. ASM is recommended filesystem for RAC and Single instance ASM for storing database files. This provides direct I/O to the file and performance is comparable with that provided by RAW Devices. Oracle creates a separate instance for this purpose.

2) How do we identify if we are connected to Normal Instance or ASM instance?

Issue following command to identify this

<span style="font-size: small; font-family: arial,helvetica,sans-serif;">
SQL&gt; show parameter instance_type</span>

<span style="font-size: small; font-family: arial,helvetica,sans-serif;">NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_type string asm</span>

In case you are connected to ASM instance, it will display value as asm, otherwise it will display value as RDBMS.

3) What are Diskgroup’s and Failuregroups?

Diskgroup is a terminology used for logical structure which holds the database files. Each Diskgroup consists of Disks/Raw devices where the files are actually stored. Any ASM file is completely contained within a single disk group. However, a disk group might contain files belonging to several databases and a single database can use files from multiple disk groups.

Failuregroups are used when using Normal/High Redundancy. They contain the mirrored ASM extents and must be containing different disks and preferably on separate disk controller.

4)Can ASM be used as replacement for RAID?

ASM is supposed to stripe the data and also mirror the data (if Using Normal, High Redundancy). So this can be used as a alternative for RAID 0+1 solutions

5) How does ASM provides Redundancy?

When you create a disk group, you specify an ASM disk group type based on one of the following three redundancy levels:

Normal for 2-way mirroring – When ASM allocates an extent for a normal redundancy file, ASM allocates a primary copy and a secondary copy. ASM chooses the disk on which to store the secondary copy in a different failure group other than the primary copy.
High for 3-way mirroring. In this case the extent is mirrored across 3 disks.
External to not use ASM mirroring. This is used if you are using Third party Redundancy mechanism like RAID, Storage arrays.

6) Can we change the Redundancy for Diskgroup after its creation.

No, we cannot modify the redundancy for Diskgroup once it has been created. To alter it we will be required to create a new Diskgroup and move the files to it. This can also be done by restoring full backup on the new Diskgroup. Following metalink note describes the steps

Note.438580.1 – How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

7) I am unable to open the ASM instance. What is the reason?

ASM instance does not have open stage. It has got only two options

Nomount- This starts the ASM instance
Mount- At this stage, Diskgroup defined in ASM_DISKGROUPS parameter are mounted

When you try to open the ASM instance , you get following error

<span style="font-size: small; font-family: arial,helvetica,sans-serif;">SQL&gt; alter database open;
alter database open
*
ERROR at line 1:
ORA-15000: command disallowed by current instance type</span>

8)Can ASM instance and database (rdbms) be on different servers?

ASM instance and Database (rdbms) have to be present on same server. Otherwise it will not work.

9)Can we see the files stored in the ASM instance using standard unix commands.

No, you cannot see the files using standard unix commands like ls. You need to use utility called asmcmd to do this. This is present in 10.2 and above.e.g
/home/oracle>asmcmd
Asmcmd>

You can use help command to see the options.

<span style="font-size: small; font-family: arial,helvetica,sans-serif;">Note: - You can use asmcmd for 10.1 database also. For this you can copy the </span><span style="font-size: small; font-family: arial,helvetica,sans-serif;">asmcmdcore and asmcmd file from 10.2 ORACLE_HOME to 10.1 ORACLE_HOME. Else you can download the files from Metalink </span><span style="font-size: small; font-family: arial,helvetica,sans-serif;"><strong>Note:332853.1</strong></span>

10) What kind of files can be stored on ASM Diskgroup.

You can store the following file types in ASM disk groups:

Control files
Datafiles, temporary datafiles, and datafile copies
SPFILEs
Online redo logs, archive logs, and Flashback logs
RMAN backups
Disaster recovery configurations
Change tracking bitmaps
Data Pump dumpsets

Note: Oracle executables and ASCII files, such as alert logs and trace files, cannot be stored in ASM disk groups.

11)Can we use ASM for storing Voting Disk/OCR in a RAC instance?

No. You cannot use ASM for storing the voting disk and OCR. It is due to the fact that Clusterware starts before ASM instance and it should be able to access these files which is not possible if you are storing it on ASM. You will have to use raw devices or OCFS or any other shared storage.

12)Does ASM instance automatically rebalances and takes care of hot spots?

No. This is a myth and ASM does not do it. It will initiate automatic rebalance only when a new disk is added to Diskgroup or we drop a disk from existing Diskgroup.

13) What is ASMLIB?

ASMLIB is the support library for the ASM. ASMLIB allows an Oracle database using ASM more efficient and capable access to diskgroups. The purpose of ASMLIB, is to provide an alternative interface to identify and access block devices. Additionally, the ASMLIB API enables storage and operating system vendors to supply extended storage-related features. You can read more about ASMLIB in

Note: 359266.1 – FAQ ASMLIB CONFIGURE, VERIFY, TROUBLESHOOT

ASMLIB for linux can be downloaded from following link

http://www.oracle.com/technology/tech/linux/asmlib/index.html

14) What is SYSASM role?
Starting from Oracle 11g, SYSASM role can be used to administer the ASM instances. You can continue using SYSDBA role to connect to ASM but it will generate following warning messages at time of startup/shutdown, create Diskgroup/add disk ,etc

<span style="font-size: small; font-family: arial,helvetica,sans-serif;">Alert entry
WARNING: Deprecated privilege SYSDBA for command 'STARTUP'</span>

15) How can we copy the files from/to ASM?

You can use RMAN or DBMS_FILE_TRANSFER.COPY_FILE procedure to copy the files to/from ASM from/to Filesystem. Starting from Oracle 11g, you can use cp command in asmcmd to perform the same between ASM Diskgroups and also to OS Filesystem.

16) Can we use BCV to clone the ASM Diskgroup on same host?

Diskgroup Cloning is not supported on the same host using BCV. You have no other option to use except RMAN DUPLICATE. You can find more detail on BCV and ASM in below whitepaper

http://www.oracle.com/technology/products/database/asm/pdf/asm-on-emc-5_3.pdf

17) Can we edit the ASM Disk header to change the Diskgroup Name?

No. This cannot be done. Please find details in

http://askdba.org/weblog/?p=128

18) Whats is Kfed?

kfed is a utility which can be used to view the ASM Disk information. Syntax for using it is

kfed read devicename

High Database Sessions

Yesterday I was working on issue where our Monitoring system reported increased number of database connections. We were already aware of this issue and application team was asked to look into it.

On checking GV$RESOURCE_LIMIT, we found that it had touched the limit value on Instance 1 (since startup).

 SELECT INST_ID "ID",RESOURCE_NAME "RESOURCE",CURRENT_UTILIZATION
CURRENT",MAX_UTILIZATION "MAX",INITIAL_ALLOCATION "INITIAL",LIMIT_VALUE FROM
V$RESOURCE_LIMIT WHERE RESOURCE_NAME='sessions';

ID RESOURCE CURRENT MAX  INITIAL LIMIT_VALUE
--  -------   --------- ----- ----     -----------
1   sessions     232       335   335        335
2   sessions     245       314   335        335

This was quite surprising as we did not get any Critical tickets. (I think this is because polling setting for tool for this parameter was set to 10 minutes).

I checked the alert log for any ORA-18 errors on Instance 1, but could not find any error. I had faced this situation sometime back (with different client) when user complained about getting ORA-18 error but during that time too, there were no entries in Alert log. It could be that ORA-18 errors are not recorded (unlike ORA -20, number of processes exceeding errors).

Since it was not recorded in Alert log (nor any trace file), I turned to AWR to help me out.In this case view WRH$_RESOURCE_LIMIT was the one which we needed to look as it stores statistics captured using AWR snapshots.

SQL> col BEGIN_INTERVAL_TIME for a28
SQL> col END_INTERVAL_TIME for a28
SQL>col RESOURCE_NAME for a10
SELECT A.SNAP_ID,A.INSTANCE_NUMBER "ID",B.BEGIN_INTERVAL_TIME,B.END_INTERVAL_TIME,A.RESOURCE_NAME,
CURRENT_UTILIZATION "CURRENT",MAX_UTILIZATION "MAX"
FROM WRH$_RESOURCE_LIMIT A, WRM$_SNAPSHOT B
WHERE A.RESOURCE_NAME LIKE '%session%'
AND A.SNAP_ID=B.SNAP_ID
AND A.INSTANCE_NUMBER= B.INSTANCE_NUMBER
AND ((A.INSTANCE_NUMBER=1
AND B.BEGIN_INTERVAL_TIME > (SELECT STARTUP_TIME FROM GV$INSTANCE WHERE INSTANCE_NUMBER=1)) OR
(A.INSTANCE_NUMBER=2 AND B.BEGIN_INTERVAL_TIME >
(SELECT STARTUP_TIME FROM GV$INSTANCE WHERE INSTANCE_NUMBER=2)))
order by A.INSTANCE_NUMBER,A.SNAP_ID;

SNAP ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME          RESOURCE CUR   MAX
---- ---------- ---------------    ------------------------  --------- ---  ---

936  1 28-JUN-08 05.00.04.133 AM  28-JUN-08 06.00.14.342 AM  sessions  212  297
937  1 28-JUN-08 06.00.14.342 AM  28-JUN-08 07.00.54.865 AM  sessions  255  323
938  1 28-JUN-08 07.00.54.865 AM  28-JUN-08 08.00.18.080 AM  sessions  227  323
939  1 28-JUN-08 08.00.18.080 AM  28-JUN-08 09.00.29.881 AM  sessions  101  335

936  2 28-JUN-08 05.00.03.797 AM  28-JUN-08 06.00.14.117 AM  sessions  233  292
937  2 28-JUN-08 06.00.14.117 AM  28-JUN-08 07.00.54.521 AM  sessions  247  310
938  2 28-JUN-08 07.00.54.521 AM  28-JUN-08 08.00.17.725 AM  sessions  253  314
939  2 28-JUN-08 08.00.17.725 AM  28-JUN-08 09.00.29.648 AM  sessions  101  314

We cannot get the exact time, but we can see that number of Database sessions reached 335 sessions on Instance 1 between 28-JUN-08 08.00.18.080 AM AND 28-JUN-08 09.00.29.881

Similarly for Instance 2, number of Database sessions reached to value of 314 between 28-JUN-08 07.00.54.521 AM AND 28-JUN-08 08.00.17.725 AM

This information was sufficient at this time to ask Application support team to check from their end. Though it would have been really nice to find the exact time. Also finding which users were having most sessions would have also helped to point the module.

3 July, 2008 by Amit Bansal 10g 1

ORA-01426 After Upgrade to 10g

Oracle seems to have a tendency to make small changes to database code which impacts application in a big way. Sad part is, these changes are not always documented properly. One of these cases is what I encountered. A simple PL/SQL code errors out as below:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000;
5 END;
6 /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

Same block works fine in any previous versions like 9i and 8i. As per Oracle this is “Expected Behavior”. Oracle used to perform number arithmetic. In 10g integer arithmetic is being used. Any application using such code would require to undergo change as follows:

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_v2 number :=10000000;
4 v_code NUMBER(20);
5 BEGIN
6 v_code := v_v1 * v_v2 ;
7* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 declare
2 v_v1 number :=990;
3 v_code NUMBER(20);
4 BEGIN
5 v_code := v_v1 * 10000000 ;
6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
2 v_code NUMBER(20);
3 BEGIN
4 v_code := 990 * 10000000.0;
5 END;
6 /

PL/SQL procedure successfully completed.

Using any of the above methods, error is not observed. It is although not clear why oracle made this change. Another hurdle in moving to 10g!!. Hope Oracle provides Database capture feature on 9i and Replay on 10g.

2 July, 2008 by Leo 10g 0

New ASM Background Processes in 11G

Few hours back I installed Oracle Database 11g(Though still DB is yet to be created), so I started playing with the ASM instance. First thing I did was to check ASM alert.log. I used ADRCI (11g) to see it

adrci&gt; show incident

ADR Home = /u03/app/oracle/diag/asm/+asm/+ASM:
*********************************************************
0 rows fetched

adrci&gt; show alert

ADR Home = /u03/app/oracle/diag/asm/+asm/+ASM:
**********************************************************
Output the results to file: /tmp/alert_9572_1_+ASM_1.ado
"/tmp/alert_9572_1_+ASM_1.ado" 48 lines, 1964 characters
PMON started with pid=2, OS id=3672
DIAG started with pid=4, OS id=3678
<strong>VKTM started with pid=3, OS id=3674
VKTM running at (100ms) precision</strong>
2008-06-24 15:24:12.425000 +05:30
PSP0 started with pid=5, OS id=3680
<strong>DSKM started with pid=6, OS id=3682</strong>
<strong>DIA0 started with pid=7, OS id=3684</strong>
MMAN started with pid=6, OS id=3686
DBW0 started with pid=8, OS id=3689
LGWR started with pid=9, OS id=3691
CKPT started with pid=10, OS id=3694
SMON started with pid=11, OS id=3700
RBAL started with pid=12, OS id=3702
GMON started with pid=13, OS id=3705
ORACLE_BASE from environment = /u03/app/oracle
<strong>Spfile /u03/app/oracle/product/11.1.0/db_1/dbs/spfile+ASM.ora is in old pre-11 format and compatible &gt;= 11.0.0; converting to  new H.A.R.D. compliant format.</strong>

I have highlighted the things which were not present in 10g. According to Docs

DI A0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.

VK TM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority)

These were the definitions from docs. Oracle should have been more generous and also documented following

DIA0 – Does that mean we will have auto SystemState/Hanganalyze generated during hang? Will ORA-60 be handled by this process?

VKTM – What does this mean to us? Will this timer be used in 10046 timing information? Will it ensure Oracle Scheduler run jobs on time 🙂

DSKM – This is still not documented.

Last message kind of indicates that spfile is also made H.A.R.D complaint. So it should take care of corruptions.

I have created more confusion rather than explaining what these processes actually do 🙂 Anyways if you have any information, then it will be really nice if it can be shared…Thanks for Reading !!

24 June, 2008 by Amit Bansal 11g 0