MODIFYING THE STORAGE FOR LOB SEGMENTS

This morning i got an alarm for a lob segment which was reaching its max extents , i checked for the available free space in the tablespace where the lob segment was available  , the tablespace was filled by only 50%.
So the next solution was to modify/increase the MAX EXTENTS storage clause for the lob segment .

Here is how i did it .

1) CHECKING THE LOB SEGMENT DETAILS  : 

select tablespace_name,segment_type,max_extents,next_extent,extents from dba_segments where segment_name='SYS_LOB0000007907C00009$$';

2) IDENTIFYING THE TABLE NAME AND COLUMN NAME : 

select owner||'.'||table_name "TABLE", column_name, segment_name from all_lobs where segment_name='SYS_LOB0000007907C00009$$';

3) MODIFYING THE STORAGE CLAUSE:

alter table owner.tablename modify lob (COLUM_NAME) (storage (maxextents unlimited ) );

IMP-00037: Character set marker unknown

                   IMP-00037: Character set marker unknown

Today while i was doing a migration from a 9i database 11g R2 database i received the error  IMP-00037 : Character Set marker unknown and the import terminated .

Here is how i rectified the issue .

SOURCE BOX : AIX 

TARGET BOX : Linux 

This issue usually arises  when the dump is corrupted .

In my case the it wasn't the corruption. 

To reduce the file size the dump file is compressed by the Unix COMPRESS utility .

In the Linux server (target) i did not un-compress  it , so the import failed . 

To resolve this i used the GUNZIP Linux utility and the re-imported the data and it completed successfully .

HOW TO FIND IF A DATABASE IS A RAC DATABASE OR A STANDALONE DATABASE

USING PARAMETERS :

SQL> show parameter CLUSTER_DATABASE;

NAME                                 TYPE        VALUE
               
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

Or  you can check in parameter file from $ORACLE_HOME/dbs

*.cluster_database=TRUE

If  the value for cluster_database is  "TRUE"  the it means that the node is RAC enabled.

OR

SQL> select name, value from v$parameter where name=’cluster_database’;

NAME VALUE
——————— ———————
cluster_database TRUE

OR

USING PL/SQL PACKAGE

set serveroutput on
BEGIN
IF dbms_utility.is_cluster_database THEN
dbms_output.put_line(‘Running in SHARED/RAC mode.’);
ELSE
dbms_output.put_line(‘Running in EXCLUSIVE mode.’);
END IF;
END;
/

Running in SHARED/RAC mode.

PL/SQL procedure successfully completed.

You can tell how many instances are active by:-

SQL> select * from v$active_instances;

INST_NUMBER   INST_NAME

  1             Stage1
  2             Stage2

ORACLE ALERT ORA-28

The alert is new alert  in Oracle Database version 11g . This is an information alert signifying that a process has been killed by a privileged user .The alert may occur like the below sample 

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

           ORA-28 : opiodr aborting process unknown ospid (12041_1)

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

Here is a breakdown of the sample message: "opiodr aborting process unknown ospid (12041_1) as a result of ORA-28"

"unknown" => means it is not a background or shadow process that is killed
"ospid (28342)" => this is the OS pid of the process which opiodr is aborting
"as a result of" => this precedes the error message which is the reason for opiodr to kill the process
"ORA-28" => this is the reason that opiodr killed the process. In this case, it is ora-28.

Here is the error text for ORA-28:

Error: ORA 28
Text: your session has been killed
-------------------------------------------------------------------------------
Cause: A privileged user killed the session and it is no longer logged in to the database.
Action: Contact the database administrator.
The administrator may be attempting to perform an operation that requires users to be logged out.
When the database administrator announces that the database is available, log in and resume work.

MOVING LOB SEGMENTS FROM ONE TABLESPACE TO ANOTHER

Moving a lob segment from on tablespace to another is a little tricky ,And it is little different than moving a table from a tablespace to another.

The Syntax for moving LOB segments would be

alter table tablename move lob (CONTENTS) store as (tablespace tablespacename);

EXAMPLE:

alter table sys.abc move lob (CONTENTS) store as (tablespace users);

The index associated with the lob segments cannot be moved to another tablespace that is indifferent from the lob segment tablespace.

INSTALLATION OF ORACLE DATABASE IN LINUX

Being my first post i will start with the installation of Oracle 10G RDBMS in Linux

Oracle Database works very well under in *inx environment than windows .Oracle software can be installed in several ways in this post i am going talk about interactive installation(GUI MODE)

STEPS FOR INSTALLATION:

Prerequsites:
MINIMUM HARDWARE REQUIREMENTS
• 1024 MB of physical random access memory (RAM).
• 2 GB of swap space (or twice the size of RAM).
• Swap space between one and two times the size of RAM (on systems with 2 GB or more of RAM).
• 400 MB of disk space in the /tmp directory .
• 2.1 GB of disk space for the Oracle software and Sample Schema Database.
Being an interactive installation (GUI) an X-Terminal window is required .

Checking The requirements:
login into server as ROOT
su - root
Password
Checking the amount of RAM:
# grep MemTotal /proc/meminfo

Checking the Swap space:
# grep SwapTotal /proc/meminfo

Checking for Temp Space:
# df -k /tmp

Checking the Total Disk Space:
# df -k

Checking the CPU architecture:
# grep "model name" /proc/cpuinfo
MINIMUM SOFTWARE REQUIREMENTS
Operating system One of the following operating system versions:

■ Red Hat Enterprise Linux AS/ES 3.0 (Update 4 or later)
■ Red Hat Linux 4.0
■ SUSE Linux Enterprise Server 9.0 with SP 2 or later
■ Asianux 1.0
■ Asianux 2.0
Linux Kernal Requirements:

Red Hat Enterprise Linux 3.0 and Asianux 1.0:
2.4.21-27.EL
Note: This is the default kernel version.

Red Hat Enterprise Linux 4.0 and Asianux 2.0:
2.6.9-5.EL

SUSE Linux Enterprise Server 9.0:
2.6.5-7.201

To check the Kernal version:
# uname -r

Packages Needed:
For installation of Oracle some Linux libraries are required .The packages are

Red Hat Enterprise Linux 3.0 and Asianux 1.0:
make-3.79.1
gcc-3.2.3-34
glibc-2.3.2-95.20
compat-db-4.0.14-5
compat-gcc-7.3-2.96.128
compat-gcc-c++-7.3-2.96.128
compat-libstdc++-7.3-2.96.128
compat-libstdc++-devel-7.3-2.96.128
openmotif21-2.1.30-8
setarch-1.3-1

Red Hat Enterprise Linux 4.0 and Asianux 2.0:
binutils-2.15.92.0.2-13.EL4
compat-db-4.1.25-9
compat-libstdc++-296-2.96-132.7.2
control-center-2.8.0-12
gcc-3.4.3-22.1.EL4
gcc-c++-3.4.3-22.1.EL44
glibc-2.3.4-2.9
glibc-common-2.3.4-2.9
gnome-libs-1.4.1.2.90-44.1
libstdc++-3.4.3-22.1
libstdc++-devel-3.4.3-22.1
make-3.80-5
pdksh-5.2.14-30
sysstat-5.0.5-1
xscreensaver-4.18-5.rhel4.2
setarch-1.6-1

SUSE Linux Enterprise Server 9:
binutils-2.15.90.0.1.1-32.5
gcc-3.3.3-43.24
gcc-c++-3.3.3-43.24
glibc-2.3.3-98.28
gnome-libs-1.4.1.7-671.1
libstdc++-3.3.3-43.24
libstdc++-devel-3.3.3-43.24
make-3.80-184.1
pdksh-5.2.14-780.1
sysstat-5.0.1-35.1
xscreensaver-4.16-2.6

To check the available packages :
# rpm -q package_name
eg: # rpm -q gcc-3.2.3-34

Creating Required Operating System Groups and Users:
1. The OSDBA group (dba) - OS users who would be ORACLE DBAs (SYSDBA previlaged people).
2. The OSOPER group (oper) - OS users who would have limited DBA privilage (SYSOPER).
3. The Oracle Inventory group (oinstall)- Oracle inventory owner. Note: Oracle Inventory is the catalog of oracle software installed.
4. The Oracle software owner user ( oracle). Note:This user must have the Oracle Inventory group as its primary group. It must also have theOSDBA and OSOPER groups as secondary groups.
Steps for creating the Groups:
1. # /usr/sbin/groupadd oinstall
2. # /usr/sbin/groupadd dba
3. # /usr/sbin/groupadd oper
Creating an Oracle Software Owner User:

# /usr/sbin/useradd -g oinstall -G dba[,oper] oracle
-g -primary group
-G secondary group

Set the password of the oracle user:

# passwd oracle
input your password

Modifying an Oracle Software Owner User:
# /usr/sbin/usermod -g oinstall -G dba[,oper] oracle

Configuring Kernel Parameters:
The kernel parameters are used to determine the way Oracle software should behave the Linux operating system.
/proc/sys/kernel/sem
/proc/sys/kernel/shmall
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmmni
/proc/sys/fs/file-max
/proc/sys/net/ipv4/ip_local_port_range
/proc/sys/net/core/rmem_default
/proc/sys/net/core/rmem_max
/proc/sys/net/core/wmem_default
/proc/sys/net/core/wmem_max

The values can be edited by

vi /etc/sysctl.conf

Ideal values should be the following or greater

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

Add the following line to /etc/profile

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

Creating ORACLE Software installation Directories:

mkdir /u01/app /u01/app/oracle /u01/oradata
chown oracle:oinstall /u01/app /u01/app/oracle /u01/oradata
chmod 775 /u01/app /u01/app/oracle /u01/oradata


INSTALLATION OF ORACLE SOFTWARE:

Download the software from:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Run The following as ORACLE user, By logging into oracle user

UNZIP THE SOFTWARE :
1. unzip 10201_database_linux32.zip.
2. Navigate to the unzip-id folder
3. in the terminal run ./run_installer.
4. select the type of installation
5. give the directory structures
6. ORACLE UNIVERSAL INSTALLER prompts to run the scripts.
7. Run the Scripts as Root user
The output will be:

$ su - root
# /u01/app/oracle/product/10.1.0/db_1/root.sh
Running Oracle10 root.sh script...
\nThe following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/10.1.0/db_1
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 ...
\nCreating /etc/oratab file...
Adding entry to /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.
/var/opt/oracle does not exist. Creating it now.
/etc/oracle does not exist. Creating it now.



Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Oracle Cluster Registry for cluster has been initialized
Adding to inittab
Checking the status of Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
mars
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
#
Click OK
- End of Installation:
Click Exit
After the installation is over the .bash_profile should be edited:

$ vi .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Here the input your oracle base directory structures.

After installation to make sure every thing works
connect to sqlplus and see.
sqlplus username/password as sysdba

ADDITIONAL INFORMATIONS:
To start Enterprise Manager:
In the terminal window
$ Emctl start

Finding listener status:
$ lsnrct status