Today during RAC installation I got the following error while running root.sh on the first node

Setting the permissions on OCR backup directory
Setting up NS directories
PROT-1: Failed to initialize ocrconfig
Failed to upgrade Oracle Cluster Registry configuration

Then I checked ocrconfig log file and got the following output:

Oracle Database 10g CRS Release 10.2.0.1.0 Production Copyright 1996, 2005 Oracle. All rights reserved.
2012-05-17 15:16:35.490: [ OCRCONF][1521206416]ocrconfig starts...
2012-05-17 15:16:35.492: [ OCRCONF][1521206416]Failure initializing ocr in DEFAULT. REBOOT INSTALL. err :[PROC-32: Cluster Ready Ser
vices on the local node is not running Messaging error [9]]
2012-05-17 15:16:35.492: [ OCROSD][1521206416]utstoragetype: /ocfs/ocrfile is on FS type 1952539503. Not supported.
2012-05-17 15:16:35.492: [ OCROSD][1521206416]utopen:6'': OCR location /ocfs/ocrfile configured is not valid storage type. Return c
ode [37].
2012-05-17 15:16:35.492: [ OCRRAW][1521206416]proprinit: Could not open raw device
2012-05-17 15:16:35.492: [ default][1521206416]a_init:7!: Backend init unsuccessful : [37]
2012-05-17 15:16:35.492: [ OCRCONF][1521206416]Failure in initializing ocr in INSTALL level. error:[PROC-37: Oracle Cluster Registry
 does not support the storage type configured]
2012-05-17 15:16:35.492: [ OCRCONF][1521206416]Exiting [status=failed]...

There’re a lot of reasons and solutions for this error on the web, but in my case the problem was in the mount point parameter. When getting this error, the mount point parameter was as follows:

/dev/dm-1 on /ocfs type ocfs2 (rw,_netdev,heartbeat=local)

Then I added “datavolume” parameter as follows:

/dev/dm-2 on /ocfs2 type ocfs2 (rw,_netdev,datavolume,heartbeat=local)

And it worked!

Today, after changing password of the sysman user of the repository database of the Grid Control, I wasn’t able to connect to the Grid control and getting the following output:

503 Service Unavailable

This output means that there’s a problem with Repository database connection. In this case, you should check the OMS log files which reside under $OMS_HOME/sysman/log – emoms.trc

When I checked this file, I got the following error:

2012-05-10 15:50:37,740 [AJPRequestHandler-ApplicationServerThread-9] ERROR conn.ConnectionService verifyRepositoryEx.818 – Invalid Connection Pool. ERROR = ORA-01017: invalid username/password; logon denied

2012-05-10 15:51:08,004 [AJPRequestHandler-ApplicationServerThread-6] ERROR conn.ConnectionService verifyRepositoryEx.818 – Invalid Connection Pool. ERROR = ORA-01017: invalid username/password; logon denied

This means that OMS can’t connect to the repository database, as the password of the SYSMAN username was changed. In order to make OMS use new password, we need change two parameter in emoms.properties file which resides  $OMS_HOME/sysman/config directory:

oracle.sysman.eml.mntr.emdRepPwd=test

oracle.sysman.eml.mntr.emdRepPwdEncrypted=false

After making necessary changes I’ve tried to start OMS using the following command:

emctl start oms

But unfortunately I got the same error again … After working a while on this error, I’ve decided to take down all OMS applications using the following command and start them again:

$OMS_HOME/opmn/bin/opmnctl stopall

$OMS_HOME/opmn/bin/opmnctl startall

After all, while checking log file, the error disappeared.

If you’ve completely lost emoms.properties file, check the following metalink note to recreate it:

How to Recreate the emoms.properties File for 10g OMS from Scratch? [ID 733401.1]

These days I’m responsible on applying CPU patches on some critical databases. Actually I’m thinking to create a new blog post on this topic

But now I would like to share a nice blog post which can be useful to overcome some patch installation issues you get during the installation

Here’s the link:

http://oracletechtips.com/2011/cpu-patch-error-code-73-or-74/

I liked the latest (7) step which was also helped me to overcome “is not writable” error even the mentioned .so file wasn’t in use by any active process

And my last advise : Don’t forget to backup “Oracle Home” folder and even having the backup of the database itself!

During a Disaster Recovery check, I got an error while restoring SPFILE from autobackup:

RMAN> restore spfile to '/tmp/spfile.ora' from '/rman_backup/control_autobackup_8357269245-20120303-00.bkp;

<em>RMAN-00571: ===========================================================</em>
<em>RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============</em>
<em>RMAN-00571: ===========================================================</em>
<em>RMAN-03002: failure of restore command at 03/03/2011 18:06:11</em>
<em>RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

I’ve checked all available solutions that I know, but unable to restore it.

I started to confuse if it’s not a backup of the current database, so I used strings command and checked the database name inside the controlfile

[oracle @db] strings /rman_backup/control_autobackup_8357269245-20120303-00.bkp | grep prod

PROD

Prod is the name of my database, so this is the correct autobackup.

I’ve automated the RMAN backup and wrote a shell script which copies newly created backup files to the remote FTP host. I’ve copied the controlfile autobackup  from the FTP host to the test machine for the Disaster Recovery. Suddenly I remembered the mode that’s used to copy the backup files via FTP. It’s NOT binary! I’ve checked the size of controlfile that’s in FTP server with the one which is located at the production database. There were different! So it’s really “not a valid copy or piece” as RMAN states.

I’ve added FTP command – “binary”  to the shell script to switch to the binary mode, run the backup script again and got my backups copied to the FTP server. Then I tried to restore spfile from the backup and of course – succeeded.

So if copy RMAN backups to the FTP server, make sure you’re using binary mode and don’t forget to perform a Disaster Recovery to make sure your backups are valid

When importing data from different database, sometimes you get errors like:

ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'TBS01' does not exist
Failing sql is:

This means that the tablespace “TBS01″ doesn’t exist in the database where you’re importing the data. For this, you can use REMAP_TABLESPACE option. If you have more than one tablespace that doesn’t exist in the second database, use comma as follows:

REMAP_TABLESPACE=db01_tbs01:db02_tbs,db_01_tbs02:db02_tbs 

To get which remap script you need to create, check TABLESPACE_NAME column for the DBA_SEGMENTS view and find in which tablespaces your objects are reside

I strongly believe that you already know that NOT IN doesn’t take NULL values into account and those values are not displayed in the output

Check the following example:

SQL> create table my_table (id number, name varchar2(10));

Table created.

SQL> insert into my_table values(1,'test1');

1 row created.

SQL> insert into my_table values(2,null);

1 row created.

SQL> insert into my_table values(3,'test2');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> select * from my_table;

ID NAME
---------- ----------
 1 test1
 2
 3 test2

SQL> select * from my_table where name not in ('test2');

ID NAME
---------- ----------
 1 test1

SQL>

For more information check the following links:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:442029737684

http://jonathanlewis.wordpress.com/2007/02/25/not-in/

Today, when I was trying to connect to the OEM Grid Control, I got “Login operation failed” error

The first file to be checked are the log and trace files at  Grid side. So I checked emoms.trc and emoms.log files under $OMS_HOME/sysman/log and didn’t find anything. I checked the repository database, it was up and running. After a while I realized that it’s a connection problem and checked $OMS_HOME/sysman/config/emoms.properties file and saw that it is trying to connect to the 2222 port  as a Repository Connection:

oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=host03)(PORT\=2222)))( CONNECT_DATA\=(SID\=oemgrid)))

When I checked listener at the repository side, I saw that it wasn’t configured to accept connections from port 2222

I added the above port number to the non-default connection section at listener.ora file and started the listener

second_listener =

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=2222))))

SID_LIST_second_listener =

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=oemgrid)

(ORACLE_HOME=/u01/oracle/product/10.2.0/db_1)

(SID_NAME=oemgrid)))

-bash-3.2$ lsnrctl  start second_listener
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/second_listener.log
Listening Endpoints Summary...  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host3)(PORT=2222)))
Services Summary...
Service "oemgrid" has 1 instance(s).  
Instance "oemgrid", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

I tried to connect to the Grid Control again, and succeeded. So if you got “Login operation failed” error and didn’t get any information from the Grid Control log files, the listener and port issues can be the reason

Sometimes we got some folders filled with unknown files either due to any bug where Oracle creates 100 files in a second or due to the OS bugs. And in some cases we’re not able to find in which folder files were created. For that, you can use the simplest command in Linux to get the output for the folders with its size sorted. Here’s the command:

du -s /u01/* | sort -n -r

.......

28G     /u01/oracle/product/10.2.0/db_1/flash_recovery_area

........

From the output you will get the folder which consumes more space

In this video tutorial I show practical demonstrations on all Flashback Technologies that are avaiable in Oracle

To download the .mp4 format of the video tutorial, use the following link:

http://www.kamranagayev.com/Video_Tutorials/Flashback_Technologies.mp4

Oracle NoSQL is now available at OTN. Check the following link for more information, download and documentation:
http://www.oracle.com/technetwork/database/nosqldb/overview/index.html