Archive

Archive for the ‘Oracle’ Category

Invalid Objects after wrong utlrp.sql

June 24th, 2009 Comments off

Be extra careful with the location of your utlrp.sql script when you decide to recompile the invalid objects for the entire database as sys.
The utlrp.sql script resides in the ORACLE_HOME/rdbms/admin and in CLIENT_HOME/rdbms/admin folders.
It is utterly wrong to use and recompile with the script in the client home folder.
After such a mistake of me the SYSMAN schema got completely corrupt.
I restored the SYSMAN using the steps described in my previous post OracleDBControl .

The remaining invalid objects are as follows:

select owner, object_name from all_objects where status='INVALID';

Read more...

Categories: Oracle Tags:

OracleDBControl

June 24th, 2009 Comments off

If you have ever exported and imported complete databases and moved them around from one server to another, you have probably noticed the issues that might occur with the EM repository. During the last delivery project that I worked on I had to import two databases in two Oracle servers (10.2.0.4) installed on Microsoft Windows 2003 x64. Everything was going smooth with the DBCA during the creation of the new database. I have selected the database to be manager using Enterprise Manager so the EM repository was being installed. Unfortunately it errored out. The logs are stored in the following location:

D:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\instance_name (ORACLE_HOME\cfgtoollogs\emca\instance_name)

I found out that everything with the creation of the EM repository has been successful except the actual start of the windows service. In the log files I found the following trace:

24.06.2009 10:11:50 oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
24.06.2009 10:14:32 oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
24.06.2009 10:14:42 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
24.06.2009 10:15:08 oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
24.06.2009 10:19:48 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
24.06.2009 10:21:13 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
24.06.2009 10:25:08 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
24.06.2009 10:24:08 oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...

Read more…

Categories: Oracle Tags:

Drop multiple tables in Oracle

April 23rd, 2009 13 comments

I just noticed that the Oracle instance that I am working with has the SMP_% tables added from the Oracle Enterprise Manager. Since I don’t need them I decided to drop them. Here is my sql that generates the delete statements:

select ‘drop table ‘||table_name||’;’ from user_tables where table_name like ‘SMP_%’;

or..

Read more…

Categories: Oracle Tags: ,

Extending VMDK file and resizing the partition – SLES 10 Linux

April 7th, 2009 Comments off

1. ESXi 3.5 Extending the VMDK file [Virtual Machine Hard Disk]

The main reason behind this is the fact that I needed more space for 2 more Oracle databases on a SLES10 Linux system. The partition mounted on /u01 was initially created as 21GB but I quickly depleted the space with 3 oracle 10g databases that took more than 17GB and the space left was not sufficient for the 2 new databases that I had to create.

Before I advanced with the extension of the VMDK file I connected to each separate instance and performed “shutdown immediate” command as sysdba.
Then I stopped the listener, dbconsole, isqlplus and once I confirmed that no oracle related processes were present on the system I performed a shutdown “shutdown -h now”.

In order to extend an existing hard drive attached to a virtual machine you have to make sure no snapshots of the virtual machine are present. I know that this is quite uncomfortable considering the risky operation that you are about to perform but there is a work around. (The work around is not in this post, so please let me know if you are interested or simply search for it. There is a good chance that I will have an article concerning that topic)

Read more…

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 10

February 20th, 2009 3 comments

When I finished the clusterware install in the lab environment I have continued with the database software itself but it appears that I needed a VMware ESXi Update 3 required! The kernel version of SLES failed verification and upon an upgrade of the kernel through YAST both machines were unusable since they would not boot.

This is an obstacle until completed since I can’t continue with the How-to until this is done.

In Part 9 of the Oracle RAC How-to we have completed successfully the installation of the Oracle Clusterware services on the shared storage for the two Suse Linux Enterprise Server 9 SP5 nodes.

Read more…

Oracle alter table change column datatype

February 17th, 2009 Comments off

Let’s say you had a field CITIZEN in table called EMPLOYEE that was created as VARCHAR2(10) but this has to be changed to VARCHAR2(3). The syntax to change the the column datatype in Oracle is as follows:


ALTER TABLE table_name MODIFY ( column_name VARCHAR2(3));


If there is existing column data you should first export that data and then import it after the datatype change. Make sure the exported data will be suitable for import upon the change (You can’t import a row with CITIZEN column of 5 after you have changed the column to VARCHAR2(3)).

Categories: Database, Oracle Tags: , ,

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 9

February 16th, 2009 Comments off

Verify the Oralce Clusterware Installation

With the introduction of Oracle RAC 10g, cluster management is controller by the evmd, ocssd and crsd processes.
Run the ps command on both nodes to make sure that the processes are running.


rac01:/u01/clusterware/cluvfy # ps -ef |grep d.bin
root 4694 1 0 Feb13 ? 00:00:00 /u01/crs1020/bin/crsd.bin reboot
oracle 5242 4692 0 Feb13 ? 00:00:00 /u01/crs1020/bin/evmd.bin
oracle 5344 5326 0 Feb13 ? 00:00:00 /u01/crs1020/bin/ocssd.bin
root 20078 10946 0 09:44 pts/1 00:00:00 grep d.bin

Next you should check the /etc/inittab file, which is processed whenever the runlevel changes:

Read more…

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 8

February 16th, 2009 3 comments

Oracle Clusterware Installation

Install the xntpd service and configure it.
You can use the Yast management console to do so.
It is extremely important that both nodes are configured to use ntp server and that they are regularly being updated.
If there is any difference at all within the date of all nodes this could result into inoperable cluster.

1. Copy the cpio.gz file to the first node and unzip the contents of the cpio file


#gunzip 10201_clusterware_linux_x86_64.cpio.gz
#cpio -idmv < 10201_clusterware_linux_x86_64.cpio

Read more…

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 7

February 15th, 2009 2 comments

Configuring and Using Raw Partitions for the Oracle Shared Storage

For the purpose of my cluster I will use raw partitions that are shared disks on the ESXi host.
First I will identify my needs for shared disks and them will create them and format them accordingly.

After some high level overview of my requirements I have created the following list of required files:

asm01.vmdk = 6GB [ ORADATA ]
asm02.vmdk = 2GB [Application data]
asm03.vmdk = 4GB [FLASH]
ocr.vmdk = 256MB [Cluster Registry]
voting.vmdk = 40MB [Voting disk]
spfile.vmdk = 16MB [Parameter configuration]

Read more…

Oracle 10g RAC on ESXi3 using SLES9 SP5 – Part 6

February 14th, 2009 Comments off

Linux OS Parameters

Here is the list of the required parameters for clusterware and oracle database 10g

Parameter Value File
semmsl semmns semopm semmni 25 032 000 100 128 /proc/sys/kernel/sem
shmmax The minimum of the following (4 GB – 1 byte), or half the size of physical memory (in bytes), whichever is lower. /proc/sys/kernel/shmmax
shmmni 4096 /proc/sys/kernel/shmmni
shmall 2097152 /proc/sys/kernel/shmall
file-max 65536 /proc/sys/fs/file-max
ip_local_port_range Minimum: 1024 Maximum: 65000 /proc/sys/net/ipv4/ip_local_port_range
rmem_default 262144 /proc/sys/net/core/rmem_default
rmem_max 4194304 /proc/sys/net/core/rmem_max
wmem_default 262144 /proc/sys/net/core/wmem_default
wmem_max 4194304 /proc/sys/net/core/wmem_max

In order to check the values in your system use the sysctl command.
You will probably get the following results from the default kernel configuration:

Read more…