pat98's always & forever

Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.1.0.6
This problem can occur on any platform.

Symptoms
- Getting ORA-4031 that is reported in the shared pool.
- Using auto SGA.
- Shared pool and SGA are set to appropriate values.
- Opening the ORA-4031 trace file, you can see a lot of space allocated to "Free Space" field.
- Running the following queries will return values less than 10

select a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm = '_shared_pool_reserved_pct';

Parameter                      Session Value          Instance Value
------------------------------------------------------------------------------

_shared_pool_reserved_pct         5                        5

Cause
The issue is Shared Pool fragmentation. This fragmentation is caused because the shared pool reserved size is set to less than 10% of the shared pool size (5% in the above example). It is always recommended that shared pool reserved size is 10% of the shared pool size to avoid fragmentation.
Modifying the shared pool in Auto-SGA is done only using a hidden parameter as below.
Solution
SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile
or set it in init.ora
"_shared_pool_reserved_pct"=10

And restart the instance.

ORA-00469: CKPT process terminated with error
ORA-07445: exception encountered: core dump [] [] [] [] [] []

9i or 10g 상관없이 에러 발생함.

잘 찾아보면 1번 노드에는 $ORACLE_HOME/rdbms/lib/libknlopt.a 화일이 있으나 2번에는 없음.

수동으로 동일한 위치에 copy 해주고 (DB 내리고 할것) 다시 relink 해주면 한방에 올라옴.

요로코롬 해결하면 됨. 자세한 방법은 아래의 metalink 문서 참조.

==================================================================================================

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.2
This problem can occur on any platform.
After installing any One-off patch in RAC nodes, one of those nodes may fail to start the instance.


NOTE: We did observe the similar problem on 10.2.0.2 RAC installations where certain libraries are not copied from the installing node to other RAC nodes. For such type of problem while performing installation, please refer to the following note.

Note 363840.1 - 10.2.0.2 RAC: ORA-7445 in KKXSYN or KKXCMS ON ALL NODES BUT INSTALL NODE

Symptoms

Oracle Instance does not startup on one of RAC the nodes after installing any One-off Patch.

If you see the alert and trace log files, you may see the following errors:

ORA-00469: CKPT process terminated with error

ORA-07445: exception encountered: core dump [] [] [] [] [] []

Cause

Solution

Copy the good library in the faulty node and manually re-link the Oracle again.

Ensure all instances are shut down !

Manually copy good $ORACLE_HOME/rdbms/lib/libknlopt.a from the local node to all remote faulty nodes and relink Oracle again on all faulty nodes.

eg: cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle

Applies to:

Goal

Solution

Symptoms

/usr/ccs/bin/make -f ins_net_client.mk mkldflags client_sharedlib install
ORACLE_HOME=/u01/app/oracle/product/9.2.0
/u01/app/oracle/product/9.2.0/bin/genclntsh
genclntsh: Could not locate /u01/app/oracle/product/9.2.0/network/admin/shrept.lst
genclntsh: exiting ...
make: Fatal error:

INFO: genclntsh: genclntsh: Could not locate  
/app/oracle/product/10.2.0/network/admin/shrept.lst
INFO: ***
Error exit code  1

INFO: ld: Can't find library or mismatched ABI for -lclntsh
Fatal error.

INFO: ld: I/O error, file "/app/oracle/product/10.2.0/lib/libclntsh.so": 
Invalid argument
Fatal error.

Changes

Cause

Solution

Please ensure the $ORACLE_HOME is set:

    env | grep ORACLE_HOME

If it is not set, set it and then follow one of the following options:

Option 1:
======
If the file is missing, create $ORACLE_HOME/network/admin/shrept.lst with a text editor and put the following lines into it.

network : snaumihi_inithostinfo
network : snaumbg_gmt
network : naedpwd_encrypt
network : naumbsb_bld_singlebyte
network : ztapis

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

Option 2:
======
If the file is missing, for Oracle9i extract it from the first 9.2.0.1.0 CD as follows:

cd $ORACLE_HOME/network/admin
jar xvf /cdrom/cdrom0/stage/Components/oracle.rsf.net_rsf/9.2.0.1.0/1/DataFiles/admin.1.1.jar shrept.lst

For Oracle10g extract the missing file from the first 10.2.0.1.0 CD as follows:

cd $ORACLE_HOME/network/admin
jar xvf <10.2.0.1staging_area>/stage/Components/oracle.network.rsf/10.2.0.1.
0/1/DataFiles/filegroup8.jar

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

Option 3:
======
Manually copy the file from another Oracle9i/10g installation

Using the 'ls -al' command, check that the file has 644 permissions:

-rw-r--r-- $ORACLE_HOME/network/admin/shrept.lst

If necessary, change the permissions using the command:

chmod 644 $ORACLE_HOME/network/admin/shrept.lst

Now manually relink using the command 'relink all'

이런 무모한 짓을 할 사람은 거의 없겠지만 필요하면 참고하시길 ^^
맥에 오라클이라..끔찍해.
=======================================
Installing Oracle Database 10g on Mac OS X

Recently Oracle and Apple announced the availability of Oracle Database 10g for Mac OS X. In in this — longer than usual — blog post, I document my experience installing the Oracle database on my Mac. In a subsequent post, I'll talk about my experience installing Oracle HTML DB. These instructions should not be considered a best practice, but they are steps that worked for me. I tried to incorporate as many screenshots as possible to help you when you are performing the install.

1. Hardware Requirements

The Quick Installation Guide states the following hardware requirements:

• 512 MB RAM
• 1GB or twice the size of RAM swap space
• 400MB of disk space in /tmp
• 2Gb disk space for software files
• 1.2Gb disk space for database files

I performed my installation on an iMac G5 with 1Gb of RAM and 160GB of disk.

2. Operating Sytems Requirements

Oracle Database 10g is only supported on Mac OS X Server. As I was just experimenting, I installed on regular OS X. There are no doubt minimum requirements. You can verify the version of Mac OS X, by executing the following command:

 # sw_vers

Below is what I saw when I executed this.

3. Install Developer Tools

To install the Oracle database, you need a C compiler. I didn't have one installed, so I signed up for a (free) Apple Developer Connection (ADC) account to download the Developer tools. To do this, sign in to ADC then click on Download Software then Developer Tools. First, download and install Xcode Tools v1.1 then download and install Dec 2003 gccLongBranch Tools Installing Xcode 1.1

After installing these packages you, verify you have the correct version of gcc installed by issuing the following command:

# gcc -v

Make sure you have at least the version as shown below.

4. Create Required User and Groups

The Quick Installation Guide suggests to start the Workgroup Manager. I didn't find one on my system, so I imagine this is a OS X Server thing. I used the command line instructions from the Database Installation Guide 10g for Mac OS X instead.

First, make sure you're root by executing in a Terminal:

# sudo sh

Determine Available Group ID

Execute the following command:

# nireport . /groups gid name | more

You'll see a list similar to the one below.

...
70      www     
74      mysql   
75      sshd    
76      qtss    
78      mailman 
79      appserverusr    
80      admin   
81      appserveradm    
99      unknown 
...

Choose an unused group ID. I chose 600.

Create oinstall Group

Using the available group ID, create a group called oinstall by executing the following three commands:

# nicl . -create /groups/oinstall
# nicl . -append /groups/oinstall gid 600
# nicl . -append /groups/oinstall passwd "*"

Create dba Group

Repeat the steps above to determine another available group ID. I chose 601. Then, create a dba group by executing these commands:

# nicl . -create /groups/dba
# nicl . -append /groups/dba gid 601
# nicl . -append /groups/dba passwd "*"

Create the Oracle Software Owner User

First, find an unused user ID, or UID, by executing the following command:

# nireport . /users uid name | more

You'll see a list similar to the following:

...
27      postfix 
70      www     
71      eppc    
74      mysql   
75      sshd    
76      qtss    
77      cyrus   
78      mailman 
79      appserver       
502     sleuniss 
...

I chose 601 as an available UID. Create the oracle user by executing these commands. Substitiute the gid and uid you chose where appropriate:

# nicl . -create /users/oracle
# nicl . -append /users/oracle uid 601
# nicl . -append /users/oracle gid 600
# nicl . -append /users/oracle shell /bin/bash
# nicl . -append /users/oracle home /Users/oracle
# nicl . -append /users/oracle realname "Oracle software owner"

Here's what it looked like.

Next, add the oracle user to the dba group:

# nicl . -append /groups/dba users oracle

Create a home directory and change the owner and group:

# mkdir /Users/oracle
# chown oracle:oinstall /Users/oracle

Finally, set the password for the oracle user:

# passwd oracle

5. Create Required Directories

Based on the suggestions in the Quick Installation Guide, I created two directories, one for the Oracle software, and one for the datafiles. Strictly speaking two separate directories are not required here.

Create the Oracle Base Directory

Create the Oracle base directory as follows:

# mkdir -p /Volumes/u01/app/oracle

Create Directory for Datafiles

Create the directory for datafile as follows:

# mkdir -p /Volumes/u02/oradata

Change Groups and Permissions for Directories

Change the groups and permissions for the directories you just created by executing these commands:

# chown -R oracle:oinstall /Volumes/u01/app/oracle 
# chown -R oracle:oinstall /Volumes/u02/oradata

then:

# chmod -R 775 /Volumes/u01/app/oracle
# chmod -R 775 /Volumes/u02/oradata

6. Configure Kernel Parameters

As is often the case when installing Oracle on Linux or other flavors or UNIX, certain kernel parameters will have to be adjusted for Oracle to run properly. The Quick Installation Guide specifies the proper minimum values.

To verify the kernel parameter values, execute:

# /usr/sbin/sysctl -a | grep corefile
# /usr/sbin/sysctl -a | grep sem
# /usr/sbin/sysctl -a | grep maxproc

Here's what I saw:

I only had to adjust two values by executing these commands:

To make these values stick, that is, presist after a reboot, edit /etc/sysctl.conf and add lines for values that need changing. I used vi to edit the file, but you can use any text editor.

Set Shell Limits

For performance reasons, shell limits need to be adjusted. Begin by navigating to the directory that contains the IPServices script.

# cd /System/Library/StartupItems/IPServices

Using any text editor, edit the file IPServices and add the following ulimit commands inside the StartService() function:

ulimit -Hu 2068
ulimit -Su 2068
ulimit -Hn 65536
ulimit -Sn 65536

Here's where I put them:

7. Configure Oracle User's Environment

The installer is run from the oracle user. Before you can run the installer, however, the environment for oracle has to be set properly.

Create .bash_profile

While logged in as oracle, I created a file called .bash_profile using a text editor.

Execute .bash_profile

Run the .bash_profile as follows:

oracle$ . ./.bash_profile

8. Add Hostname to /etc/hosts

Because I performed my installation on machine at home, which gets its IP address through DHCP from a wireless router, I had to make sure the installer could find this IP address by adding it to my /etc/hosts file. You may not need to do this, depending on how your machine is networked. If, during the install process, you see an error related to the installer not being able to determine the IP address, stop the installer, perform these steps and launch the installer again. As Root, edit the file /etc/hosts and add an entry with your hostname and IP address. I determined my IP address by executing the following commands as root:

sh-2.05b# hostname
sh-2.05b# ifconfig -a

The first command returned sergio-g5.local. The second returned configuration details for each network device in my iMac. The one I'm using is en1, an Airport card. The IP address for that is 192.168.0.5. So, I added a line to my /etc/hosts file to make it look like this:

9. Install Oracle

Download the Files

If you're like me and you don't have the CDs, you can download the necessary files from OTN. To install the database, you'll need the file ship_mac_db.cpio.gz While you're there, you might as well get the Companion CD, ship_mac_companioncd.cpio.gz and HTML DB 1.6 as well. I downloaded the files to the desktop while logged using my own account, so for the oracle user to access these files, I first moved the file from the Desktop to the /tmp directory and then copied them there to a stage directory in /Volumes/u01/app/oracle/, owned by oracle by executing the following commands from within a Terminal:

sleuniss% cd Desktop
sleuniss% mv ship_mac_db.cpio.gz /tmp
sleuniss% su - oracle
oracle$ mkdir /Volumes/u01/app/oracle/stage
oracle$ cd /Volumes/u01/app/oracle/stage
oracle$ cp /tmp/ship_mac_db.cpio.gz .

Uncompress the File

While in the stage directory, issue the following commands to uncompress the file:

oracle$ gunzip ship_mac_db.cpio.gz
oracle$ cpio -idm < ship_mac_db.cpio

When I did this, I was presented with a message after the first command, Operation not permitted. Not sure what that was about, but it seemed harmless.

Launch Installer

To launch the installer, run the script runInstaller in the Disk1 directory.

oracle$ cd Disk1
oracle$ ./runInstaller

Keep clicking Next until you are asked to run a script as root. To do so, launch a new Terminal and become root. Then execute these commands:

sh-2.05b# cd /Volumes/u01/app/oracle/oraInventory/
sh-2.05b# ./orainstRoot.sh 

Accept all the default values, and keep clicking Next.

Choose Edition

When prompted to choose a database edition, I selected Enterprise.

Choose Starter Database

I chose to create a General Purpose starter database

Choose Character Set

I changed from the default to AL32UTF8. This is not necessary unless you plan to to use Unicode or multibyte characters in your database.

Database File Storage

Earlier, during preparation for this installation, I set up a location for database files in /Volumes/u02/oradata. When prompted for the location on the file system for database files, change the default value.

Database Schema Passwords

The installer allows you to set all password to be the same.

Install

After a quick glance at the install summary, it's time to kick off the installation.

Run root.sh

When prompted to run root.sh, run the following commands as root:

sh-2.05b# cd /Volumes/u01/app/oracle/product/10.1.0/g5db/
sh-2.05b# ./root.sh

End of Installation

You've reached the end of the installation

Logging in

To run SQL*PLUS, you'll need to configure the PATH to the executables. While logged in as oracle, execute these commands:

oracle$ cd /Volumes/u01/app/oracle/product/10.1.0/g5db
oracle$ export ORACLE_HOME=`pwd`
oracle$ export PATH=$PATH:$ORACLE_HOME/bin

Now you can run SQL*PLUS. You may want to add these environment variable settings to the oracle user's .bash_profile for subsequent sessions.

That's it, the 10g database is installed. In the next few weeks, I'll write up some instructions to install Oracle HTML DB as well.