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!

Note: Please make sure you’ve read the following metalink note:

Opatch Reports ‘libclntsh.so.10.1 not removed. Text file busy’ [ID 553263.1]

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

[php]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[/php]

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

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

PROD[/php]

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:

[php]

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

[/php]

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:

[php]REMAP_TABLESPACE=db01_tbs01:db02_tbs,db_01_tbs02:db02_tbs [/php]

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:

[php]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>[/php]

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:

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

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

[php]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

[/php]

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:

[php]du -s /u01/* | sort -n -r

…….

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

……..[/php]

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

Yesterday, Hemant K. Chitale, who’s co-author of my book “Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump” posted an interesting article where shows how CREATE INDEX statement performs INDEX FAST FULL SCAN over another index to speed up the process. You can read the article from the following link:

http://hemantoracledba.blogspot.com/2011/09/index-that-is-subset-of-pre-existing.html

I was requested to apply a patch on one of the production databases. During patch apply, I got the following error and rolled the patch back:

[code]

The following actions have failed:
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kqlm.o to /oracle/product/10.2.0/lib/libserver10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kkpod.o to /oracle/product/10.2.0/lib/libserver10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib/libserver10.a/kkpox.o to /oracle/product/10.2.0/lib/libserver10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgl.o to /oracle/product/10.2.0/lib/libgeneric10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgl2.o to /oracle/product/10.2.0/lib/libgeneric10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib/libgeneric10.a/kgh.o to /oracle/product/10.2.0/lib/libgeneric10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgl.o to /oracle/product/10.2.0/lib32/libgeneric10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgl2.o to /oracle/product/10.2.0/lib32/libgeneric10.a… ”
Archive not applied /oracle/orasoft/8528171/files/lib32/libgeneric10.a/kgh.o to /oracle/product/10.2.0/lib32/libgeneric10.a… ”
Do you want to proceed?
[y|n]
[/code]

After contacting Oracle Support we decided that there may be a corruption on the mentioned library files.

As a solution, I installed Oracle Software to the different directory, applied necessary patchset, created new database and tested the patch, it succeeded. So I rolled back the latest patch, copied three library files to the previous Oracle home (to the lib and lib32 folders) and applied the patch, and of course, it succeeded.

So if you get the above message “Archive not applied” when applying patch, don’t worry, this means that the mentioned library files might be corrupted. Just perform above steps and don’t forget to backup your old library files