Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘upgradation’ Category

DBUA failing with LOCAL_LISTENER parameter error

Posted by Pavan DBA on December 5, 2013


Today, lets discuss about an article related to Database upgrade failure…

Sometimes, when we are upgrading the database from 9i/10g to 11gR2 version (11.2.0.1 or 11.2.0.3) using DBUA, we might land up with an error saying “LOCAL_LISTENER parameter is empty”.

One of the steps before we start DBUA for database upgrade is to copy the pfile or spfile from current ORACLE_HOME/dbs to the upgrading version ORACLE_HOME/dbs location. For example, when we upgrade from 10g to 11g, we will copy pfile or spfile of 10g database from 10g ORACLE_HOME/dbs to 11g ORACLE_HOME/dbs.

In that pfile or spfile, if we don’t set any value for LOCAL_LISTENER parameter, then we will face the error mentioned above.

Also, we can observe following message in the upgrade log file.

createAddressListAlias TNS_ADMIN=null
createAddressListAlias ORACLE_HOME=/app/oracle/product/11.2.0/db_1

If it is a RAC environment, we may see more errors as below

ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_PRODDB1’

As DBUA failed, we need to restore the backup of the database.

Once done, we need to set TNS_ADMIN parameter to point to new ORACLE_HOME/network/admin path and also copy tnsnames.ora and listener.ora file from old ORACLE_HOME to new ORACLE_HOME.

export TNS_ADMIN=$11g_ORACLE_HOME/network/admin
cp $10g_ORACLE_HOME/network/admin/tnsnames.ora $11g_ORACLE_HOME/network/admin
cp $10g_ORACLE_HOME/network/admin/listener.ora $11g_ORACLE_HOME/network/admin

After the above steps, start DBUA once again and this time it would be successful.

 

Posted in upgradation | Tagged: , , , , , | Leave a Comment »

UPGRADE Vs MIGRATION

Posted by Pavan DBA on April 3, 2013


Today, let us see about the difference between upgrade and migration.

First of all let me give you a brief definition of both

UPGRADE:

Process of changing version of database from lower release to major release is called UPGRADE. For example, moving from 10.2.0.1 to 11.2.0.1. 10.2.0.4 to 11.2.0.3, 9.2.0.8 to 10.2.0.5 etc

Usually, we will upgrade the database without changing the physical server. Means, in the existing server itself we will upgrade the DB to new release.

Reasons for upgrade:

1. To continue support with Oracle (because Oracle will not support if you have a license of unspported version. for example, Oracle stopped support for 10g now, so if you have 10g database you cannot get any help from Oracle support to raise SR’s, to fix any bugs etc)

2. There is a serious bug which got fixed only in new release.

3. Client has license for new release

4. Application will work better with new version of database etc

MIGRATION:

Process of changing OS platform for a database server. For example, moving a database from Solaris server to Linux server, windows to Solaris etc.

Ideally in most of the migration projects, there will be a change in physical server i.e If we have a server with Solaris 5.10 and now client decided to move to latest server with RHEL 6, we call it as migration.

Some of the reasons why client need this…

1. the existing server became old and doesn’t have expanding capability (like we cannot extend memory, disk space or CPU etc)

2. existing OS license got expired and client want now to use cheaper OS (like Linux)

3. moving from one data center to another to reduce cost of maintenance etc

Many a times, migration also involves upgrade. usually, out of 100% migration projects, 80-90% will involve the task for upgrade database along with migration.

For example, there is a 10.2.0.4 database running on X server with Solaris 5.10 and client decided to move to 11.2.0.3 database on Y server with RHEL 6. In this case, it involves both migration (because you are changing the platform) and upgrade (because database release is changing).

How we can do migration & upgrade, I would be posting documents in https://pavandba.com/important-docs/

With this, I hope I have explained little bit about upgrade Vs migrate.

HAPPY LEARNING 🙂

Posted in upgradation | Tagged: , , , , , , , | 13 Comments »

How to resolve “Unable to create Patch Object” error?

Posted by Pavan DBA on December 18, 2011


Hi Friends, Its been ages that I blogged a technical article. Today I am going to post some info which might be useful for us.

Long back I posted about how to check patch conflicts before executing CPU patches in this article https://pavandba.com/2011/09/19/how-to-check-conflicts-while-applying-cpu-patch/

Yesterday, when I am supposed to patch a 11.2.0.1 database and checking conflicts, I got below error

FTU % opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419278

Invoking OPatch 11.1.0.6.0  
Oracle Interim Patch Installer version 11.1.0.6.0 Copyright (c) 2007, Oracle Corporation.  All rights reserved.  

PREREQ session  
Oracle Home       : /u02/app/oracle/product/11.2.0.1/FTU
Central Inventory : /u01/app/oracle/oraInventory   
from           : /etc/oraInst.loc
OPatch version    : 11.1.0.6.0
OUI version       : 11.2.0.1.0
OUI location      : /u02/app/oracle/product/11.2.0.1/FTU/oui Log file location : /u02/app/oracle/product/11.2.0.1/FTU/cfgtoollogs/opatch/opatch2011-11-05_01-50-34AM.log  

Invoking prereq “checkconflictagainstohwithdetail”
The location “./12419278/README.html” is not a directory or a valid patch zip file.
Prereq “checkConflictAgainstOHWithDetail” not executed
PrereqSession failed: Unable to create Patch Object.
Exception occured : Patch ID is null.  

OPatch failed with error code 73

We have two solutions for this problem

SOLUTION # 1
——————-

Move the text files like readme.html, readme.txt etc from the patch directory to some other location and try checking conflicts and it will be successful

But the problem is, this is not a permanent solution and just a workaround

SOLUTION # 2
——————–

This is the permanent solution for this problem. Cause for this error is we don’t have latest opatch version. As you can see in the output above my database version is 11.2.0.1, but my opatch version is 11.1.0.6. So, download latest opatch version from My Oracle Support with the help of patch # 6880880 and install it

Steps to install new opatch
1. copy the downloaded zip file for patch # 6880880 to $ORACLE_HOME
2. change location to ORACLE_HOME and rename the old OPatch directory
example : FTU%  cd $ORACLE_HOME
FTU%  mv OPatch OPatch.old
3. unzip the zip file which will create new OPatch directory
4. run below command to check if version is changed
FTU% $ORACLE_HOME/OPatch/opatch version

The above steps are also available in README file of the patch

Hope this post helps you in resolving the issue……………….. have a HAPPY LEARNING 🙂

Posted in upgradation | Tagged: , , , , , | 2 Comments »

How to check conflicts while applying CPU patch?

Posted by Pavan DBA on September 19, 2011


Hi Friends, today I want to bring an important update regarding Jul CPU 2011 patch applying.

most of the times, we will never check any conflicts for the patches when applying any CPU. ofcourse this conflict checking command is not there even in README.html file that is distributed with patch.

Please use below command to check the conflicts aganist the oracle_home and avoid to land in problems

step 1: unzip your patch zip file
step 2: run below command
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir <patch_directory>

Example:

$ unzip p9655017_10204_linux.zip
$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir 9655017
The other day, when I am doing patching on a RAC database, after executing the above conflict command, got below error

Following patches have conflicts. Please contact Oracle Support and get the merged patch of the patches :
6600051, 8836683
Whenever you get this type of error message, plz contact oracle support by raising a service request(SR)

In my case, Oracle support suggested to apply a merge patch 9347333 before applying Jul CPU 2011. Once done with applying merge patch, without any further issues I successfully applied CPU patch

Sometimes apart from above message you may see below warning messages which you can ignore

Summary of Conflict Analysis:

Patches that can be applied now without any conflicts are :
10013975, 10014009, 10014012, 10014015, 10325878, 10325885, 11787762, 11787763, 11787765, 11787766, 12419249, 12566121, 12566124, 12566126, 12566129, 12566131, 12566134, 12566136, 12566137, 12566139, 12566141, 12566142, 12566143, 7155248, 7155249, 7155250, 7155251, 7155252, 7155253, 7155254, 7197583, 7375611, 7375613, 7375617, 7609057, 8309592, 8309632, 8568395, 8568397, 8568398, 8568402, 8568404, 8836667, 8836671, 8836675, 8836677, 8836678, 8836683, 8836684, 8836686, 9173244, 9173253, 9442328, 9442331, 9442339, 9678690, 9678695, 9678697

Following patches are not required, as they are subset of the patches in Oracle Home or subset of the patches in the given list :
10249540, 8836681, 8568405

Following patches will be rolled back from Oracle Home on application of the patches in the given list :
10013975, 10014009, 10014012, 10014015, 10325878, 10249540, 8836681, 8568405, 7155248, 7155249, 7155250, 7155251, 7155252, 7197583, 7375611, 7375613, 7375617, 7609057, 8309592, 8309632, 8568395, 8568397, 8568398, 8568402, 8568404, 8836667, 8836671, 8836675, 8836677, 8836678, 8836683, 8836684, 8836686, 9173244, 9173253, 9442328, 9442331, 9442339, 9678690, 9678695

Hope this post helps you…..

Posted in upgradation | Tagged: , , , | Leave a Comment »

How to migrate Oracle11gR1 database from 32-bit linux to 64-bit linux?

Posted by Pavan DBA on May 10, 2011


Many a times, we will get a requirement to migrate a database which is there in 32-bit linux to 64-bit. Till 10g, we used to use export/import for this. But Oracle 11g Release 1 provides you an easiest way. Here it is…………

1) First of all, please perform the steps described below:

1.1) Start SQL*Plus:

C:\> sqlplus /NOLOG

1.2) Connect to the database instance as SYSDBA:

SQL> CONNECT / AS SYSDBA;

1.3) Create a .trc file to use as a template to re-create the control files on the 64-bit computer:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

1.4) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

1.5) Perform a full offline database backup.

1.6) Backup your 32-bit Oracle Home (this step is optional since you can restore it from a fresh installation).

2) Install Oracle Database 11g Release 1 (11.1) for 64-bit Linux.

3) Copy the 32-bit datafiles to the new 64-bit Oracle home.

4) Copy the 32-bit configuration files to the 64-bit Oracle home:

4.1) If your 32-bit initialization parameter file has an IFILE (include file) entry, then copy the file specified by the IFILE entry to the 64-bit Oracle home and edit the IFILE entry in the initialization parameter file to point to its new location.

4.2) If you have a password file that resides in the 32-bit Oracle home, then copy the password file to the 64-bit Oracle home. The default 32-bit password file is located in $ORACLE_BASE/$ORACLE_HOME/database/pwdSID.ora., where SID is your Oracle instance ID.

5) “This step is required on Windows platforms only”. In the 64-bit Oracle home, add the _SYSTEM_TRIG_ENABLED = false parameter to the $ORACLE_HOME/database/$ORACLE_SID/init.ora file before changing the word size. It is recommended to set _SYSTEM_TRIG_ENABLED=FALSE in the following document though:

=)> Oracle® Database Platform Guide
11g Release 1 (11.1) for Microsoft Windows
Part Number B32010-02

==)> Migrating an Oracle Database 11g Release 1 (11.1) Database

Note: On Linux/Unix there is not need to add _SYSTEM_TRIG_ENABLED=FALSE to init.ora file, when the database is started in upgrade mode _SYSTEM_TRIG_ENABLED is automatically set to FALSE.

After starting 10.2.0.3 in upgrade mode with 11.1.0.6 software alert log file shows:
==============================================================
” ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off.
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
ALTER SYSTEM SET enable_ddl_logging=FALSE SCOPE=MEMORY;
Resource Manager disabled during database migration: plan ” not set
ALTER SYSTEM SET resource_manager_plan=” SCOPE=MEMORY;
Resource Manager disabled during database migration
Starting background process FBDA ”
==============================================================

6) Go to the 64-bit ORACLE_HOME/rdbms/admin directory from the command prompt.

7) Start SQL*Plus:

SQL> sqlplus /NOLOG

8) Connect to the database instance as SYSDBA:

SQL> CONNECT / AS SYSDBA;

9) Re-create the 64-bit control files using the CREATE CONTROLFILE command using the trace file created in the step “1.3)“ as follow:

9.1) Edit the as follow:

9.2) If it is required, please change the paths to the datafiles, log files and control files to point to the Oracle home on the 64-bit computer. This creates the new control file in ORACLE_HOME/database.

9.3) Here is an example of a database named “orcl32” on a 32-bit computer moving to “orcl64” on a
64-bit computer:

CREATE CONTROLFILE REUSE DATABASE “T1” NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 ‘/oracle/product/11.1.0/oradata/orcl64/REDO03.LOG’ SIZE 1M,
# was ‘/oracle/product/11.1.0/oradata/orcl32/…LOG’
# on the 32-bit computer
GROUP 2 ‘/oracle/product/11.1.0/oradata/orcl64/REDO02.LOG’ SIZE 1M,
GROUP 3 ‘/oracle/product/11.1.0/oradata/orcl64/REDO01.LOG’ SIZE 1M
DATAFILE
‘/oracle/product/11.1.0/oradata/orcl64/SYSTEM01.DBF’,
# was ‘/oracle/product/11.1.0/oradata/orcl32/…DBF’
# on the 32-bit computer
‘/oracle/product/11.1.0/oradata/orcl64/RBS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/USERS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/TEMP01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/TOOLS01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/INDX01.DBF’,
‘/oracle/product/11.1.0/oradata/orcl64/DR01.DBF’
CHARACTER SET WE8ISO8859P1;

9.4) For additional information please check the next note:

10) Having a copy of the initialization parameter file (from the 32-bit computer), please include the new control file generated in the preceding step.

11) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

12) Start the database in UPGRADE mode to run utlirp.sql:

SQL> STARTUP UPGRADE;

You might need to use the PFILE option to specify the location of your initialization parameter
file.

13) Set the system to spool results to a log file for later verification of success. For example:

SQL> SPOOL mig32-64.log;

14) Enter the following command to view the output of the script on-screen:

SQL> SET ECHO ON; 

Handling for JVM during Migration
When migrating a database from 32 to 64bit (or vice versa) additional actions
are required for java.  In theory the format of java shared data objects (SRO)
is not compatible between 32 and 64 bit and so these objects need to be dropped
and regenerated.  In practice it may be the case prior to release 11 such
objects could interoperate but if so this would only be by chance and should
not be relied on.

The steps to do the regeneration are as follows.  These should be done
immediately before running utlirp.  They may take several minutes to complete.
They must be done connected as SYS.

begin
  update obj$ set status=5 where obj#=(select obj# from obj$,javasnm$ 
    where owner#=0 and type#=29 and short(+)=name and 
    nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
  commit;
  declare
    cursor C1 is select
       'DROP JAVA DATA "' || u.name || '"."' || o.name || '"'
       from obj$ o,user$ u where o.type#=56 and u.user#=o.owner#;

    ddl_statement varchar2(200);
    iterations number;
    previous_iterations number;
    loop_count number;
    my_err     number;
  begin
    previous_iterations := 10000000;
    loop
      -- To make sure we eventually stop, pick a max number of iterations
      select count(*) into iterations from obj$ where type#=56;
      exit when iterations=0 or iterations >= previous_iterations;
      previous_iterations := iterations;
      loop_count := 0;
      open C1;
      loop
        begin
          fetch C1 into ddl_statement;
          exit when C1%NOTFOUND or loop_count > iterations;
        exception when others then
           my_err := sqlcode;
           if my_err = -1555 then -- snapshot too old, re-execute fetch query
             exit;
           else
             raise;
           end if;
        end;
        initjvmaux.exec(ddl_statement);
        loop_count := loop_count + 1;
      end loop;
      close C1;
    end loop;
  end;
  commit;
  initjvmaux.drp('delete from java$policy$shared$table');
  update obj$ set status=1 where obj#=(select obj# from obj$,javasnm$ 
    where owner#=0 and type#=29 and short(+)=name and 
    nvl(longdbcs,name)='oracle/aurora/rdbms/Compiler');
  commit;
end;
/

create or replace java system
/

15) Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database (utlirp.sql script changes the words size):

SQL> @utlirp.sql;

16) Turn off the spooling of script results to the log file:

SQL> SPOOL OFF;

17) Check the spool file and verify that the packages and procedures compiled successfully. Correct any problems you find in this file.

18) Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

19) Start the database:

SQL> STARTUP;

20) Recompile existing PL/SQL modules in the format required by the 64-bit Oracle Database (utlrp.sql script recompile the invalid objects):

SQL> @utlrp.sql;

21) “On Windows platforms only, if you added the “_SYSTEM_TRIG_ENABLED = FALSE” parameter to your initialization parameter file in step “5)” above, remove the parameter from the initialization parameter file, and then shut down and restart the database.”

For more info, refer to MOS doc 548978.1

Posted in upgradation | Tagged: , , , , | Leave a Comment »

 
%d bloggers like this: