Kamran Agayev's Oracle Blog

Today, while configuring a standby database, I got the following internal error “during spfile creation”

[php]ORA-00600: internal error code, arguments: [kspsetpao1], [1753], [1700], [*], [user_dump_dest], [33], [], [][/php]

Actually it’s the first time I get an “internal error” while creating spfile. Not a message, not a warning, but internal error

After checking spfile I saw that I forgot to close the quote mark

[php]*.log_archive_dest_2=’service=standby[/php]

I added the quote mark and the problem solved.

Last week I’ve done a migration from 32 bit to 64 bit on Linux server. Just would like to share the experience and steps that should be performed during the migration

First of all, I would advise you to check Oracle Support for existence document. I’ve found the following document very useful:

How To Migrate a Database From Linux x86 ( 32-bit ) To Linux Itanium 64-bit (IA64) [ID 553868.1]

Before starting the migration (or any critical operation) take backup of the database and make sure you’ve tested the migration on the test environment (It’s very easy. Just create a new database on the 32bit OS, move it to 64 bit OS and practice the migration)

The first step is to get the list of all datafiles (dba_data_files), redo log files (v$logfile) and control files (v$controlfile).

Then, take backup of parameter file and extract creation script of the control file:

[php]

SQL> create pfile=’/tmp/pfile_mydb.ora’ from spfile;

SQL> alter database backup controlfile to trace as ‘/tmp/controlfile_script.dat’;

[/php]

The shutdown the database, copy all datafiles, redo log files, new parameter file and control file creation script to the new server. Make necessary changes to the parameter file (show the new destination for the controlfiles) and to the control file creation script (change the file names to the new folder)

If everything ok, start the database in NOMOUNT mode, and create the controlfiles by running the script:

[php]

SQL> startup nomount pfile=’/tmp/pfile_mydb.ora’;

SQL> create spfile from pfile=’/tmp/pfile_mydb.ora’;

SQL> startup nomount force;

[/php]

After the controlfile created successfully open the database. If you’ll try to open the database in normal mode, you’ll get some internal errors which is very scare to get at midnight on the production database

I got the following internal error after trying to open the database in normal mode as metalink note suggested (however, I was wondering and thinking to NOT open it in normal mode)

[php]

ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]

Error 0 in kwqmnpartition(), aborting txn
Error 0 in kwqmnpartition(), aborting txn
Error 0 in kwqmnpartition(), aborting txn
Error 0 in kwqmnpartition(), aborting txn
Shutting down instance: further logons disabled
Wed Jul 4 02:42:30 2012
Stopping background process QMNC
Wed Jul 4 02:42:30 2012
Stopping background process CJQ0
Wed Jul 4 02:42:32 2012
Stopping background process MMNL
Wed Jul 4 02:42:33 2012
Stopping background process MMON
Wed Jul 4 02:42:34 2012
Errors in file /home/oracle/admin/MYDB/udump/mydb_ora_3133.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06553: PLS-801: internal error [56319]

[/php]

And instance closed automatically …

I didn’t payed attention the that error (however panicked a little )

I’ve tried to open it again with UPGRADE option which succeeded

[php]

SQL> startup upgrade;

[/php]

There’s a metalink note on the above mentioned error:

ORA-00600 [17093] and ORA-00600 [kkmendsel-pin] Errors Continuously [ID 756592.1]

Actually I didn’t checked it when I got the error, just bypassed it and tried to open the database

So I suggest you to open with with RESETLOGS and UPGRADE option at once using the following command :

[php]

SQL> alter database open resetlogs upgrade;

[/php]

After the database opened successfully, you need to run the following sql files:

[php]

SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql;

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql ;

[/php]

Both scripts are different. The first one will INVALIDATE all objects at the database, and the second one VALIDATEs.

After both scripts completed, shutdown the instance and start it in normal mode. Then check if you have any INVALIDE objects

If you’re using OLAP components, (or not using but just installed), you’ll get some internal errors and get some objects INVALIDE. In this case either remove the OLAP objects (if you don’t use them) and install it again, or check the following metalink note :

Migrating OLAP From 32 To 64 Bits [ID 352306.1]

After finishing all, make sure you’ve moved listener.ora and tnsnames.ora file from the old server to the new one and start the listener

I strongly believe you already know it, but would like to share if you’ve forgot and can’t understand why you’re not able to connect to the instance which is already running

[php]-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jul 11 09:24:18 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> exit

-bash-3.2$ ps -ef | grep smon
oracle 21202 1 0 Nov 8 ? 118:15 ora_smon_MYDB

[/php]

The database is running, but I can’t login to the instance. Let’s check ORACLE_HOME environment variable:

[php]

/home/oracle/product/10g/

[/php]

Do you see the slash in the end of the line? That’s the reason! Let’s set it again without the slash at the end and try again

[php]

-bash-3.2$ export ORACLE_HOME=/home/oracle/product/10g
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jul 11 09:33:39 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

[/php]

Here it is …

Today, one of the instances of 2 node RAC went down with ORA-07445 error. After checking metalink, I found out that this is a bug:

Bug 10422354: ORA-07445 [KFKPROCESSREQ()+429] [SIGSEGV] ON ASM INSTANCE

And it happens after the upgrade of RAC to 10.2.5 patchset. Unfortunately there was no workaround suggested by support. After checking ASM log file again, I find the following error:

ORA-15186: ASMLIB error function = [asm_open], error = [1], mesg = [Operation not permitted]

The ASM instance was not mounting. The instance wasn’t able to find the shared storage, so I changed asm_diskstring parameter as follows:

[php]asm_diskstring=’/dev/mapper/*'[/php]

It was a test RAC environment, both ASM instances were using different initialization parameter files (however, both database instances were using the same shared initialization parameter file)

I’ve created new server parameter file from the existing one, put it in the shared folder, changed parameter file of each instance (pfile=’/shared_folder/spfile+ASM.ora’) and started the failed ASM instance. Fortunately, it has started. Then I restarted the second ASM instance to make it use the shared parameter file and both instances were up

I was asked to grant a privilege for a user to create only temporary tables. I was sure there’s no the specified grant such as

[php]

SQL>GRANT CREATE TEMPORARY TABLE to user1;

[/php]

After making a simple research, I saw the following thread where Justin Cave mentions that “However, if you grant the user the CREATE TABLE privilege but do not grant the user quota on any permanent tablespace, they would be unable to create permanent tables but should be able to create global temporary tables”

https://forums.oracle.com/forums/thread.jspa?threadID=2197364

A simple test proved the above quote:

[php]

SQL> create user user1 identified by user1;
User created.

SQL> grant connect to user1;
Grant succeeded.

SQL> grant create table to user1;
Grant succeeded.

SQL> conn user1/user1
Connected.

SQL> create table a (id number);
create table a (id number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SQL> create global temporary table a (id number);
Table created.

SQL>

[/php]

After removing the RAC installation on the test server, I got the following error during the installation:

“The installer has detected the existence of 10g Oracle Clusterware on the remote node, —-, however, 10g Oracle Clusterware does not exist on the local node …

Although I run $CRS_HOME/install/rootdelete.sh, removed the $CRS_HOME and oraInventory folders and killed all running processes, it was throwing above mentioned error. After making some checks, I saw that /etc/oracle/ocr.loc file is still there. When removing that file, the error message disappeared. I was interested whether installation checks the file’s availability or its entry also, so created an empty file with the same name “ocr.loc” and tried the installation. Guess what? I got the error again. This means that Oracle looks only for the name of the file in the mentioned folder

In case you want to clear the failed RAC installation, check the following article:

http://blog.flimatech.com/2008/10/02/10g-rac-how-to-clean-up-after-a-failed-crs-install/

My friend and former ACE Director Porus Homi Havewala’s new book on Enterprise Manager 12c Cloud Control, will be available in September 2012 or earlier, perhaps the First EM 12c Cloud Control book in the world.

If you are interested in learning about the capabilities of Enterprise Manager 12c, please have a look. The book can be pre-ordered in advance. Electronic copies will be available too on publication.

http://www.packtpub.com/oracle-enterprise-manager-12c-cloud-control/book

Oracle Enterprise Manager 12c Cloud Control: Managing Data Center Chaos

I’m currently reviewing this book and must say that it’s very well written!

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

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

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

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

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:

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

Then I added “datavolume” parameter as follows:

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

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!

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]