This discussion is archived
14 Replies Latest reply: Dec 11, 2012 11:09 PM by 976124 RSS

Upgrade Database from 10.2.0.5.0 to 11.2.0.3.0

976124 Newbie
Currently Being Moderated
Hi All,

I am a newbie here, attempting to upgrade DB 10.2.0.5.0 to 11.2.0.3.0 which is running on Microsoft Windows (32-bit).

Below are the steps I had performed:

1: Install Oracle 11G Release 2

2: Check for invalid objects:
SQL> select unique OBJECT_NAME, OBJECT_TYPE, OWNER
from DBA_OBJECTS
where STATUS='INVALID';

If there are invalid objects, recompile invalid objects with utlrp.sql:
SQL> @?/rdbms/admin/utlrp.sql;

3: Check for duplicate objects:
SQL> select OBJECT_NAME, OBJECT_TYPE
from DBA_OBJECTS
where OBJECT_NAME||OBJECT_TYPE in
(select OBJECT_NAME||OBJECT_TYPE
from DBA_OBJECTS
where OWNER='SYS')
and OWNER='SYSTEM'
and OBJECT_NAME not in
('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES',
'DBMS_REPCAT_AUTH');

4: Check for invalid components:
SQL> select substr(COMP_ID, 1, 10) compid,
substr(COMP_NAME, 1, 24) compname, STATUS, VERSION
from DBA_REGISTRY
where STATUS<>'VALID';

5: Purge the recyclebin:
SQL> purge DBA_RECYCLEBIN;

6: Run the pre-upgrade script:
SQL> @?/rdbms/admin/PreUpgrade/utlu112i_5.sql;


Below is the result of the pre-upgrade script:

***********************************************************************
Tablespaces: [make adjustments in the current environment]
***********************************************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 491 MB
.... minimum required size: 493 MB
.... increase current size by: 2 MB
.... tablespace is AUTOEXTEND ENABLED.
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 222 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
***********************************************************************
Rollback Segments: [make adjustments immediately prior to upgrading]
***********************************************************************
--> R0 in tablespace SYSTEM is OFFLINE; AUTOEXTEND is OFF
.... currently allocated: 7168K
.... next extent size: 32K; max extents: 32765
--> R1 in tablespace RBS_1 is ONLINE; AUTOEXTEND is OFF
.... currently allocated: 8192K
.... next extent size: 4096K; max extents: 32765
--> R2 in tablespace RBS_2 is ONLINE; AUTOEXTEND is OFF
.... currently allocated: 8192K
.... next extent size: 4096K; max extents: 32765
--> R3 in tablespace RBS_3 is ONLINE; AUTOEXTEND is OFF
.... currently allocated: 8192K
.... next extent size: 4096K; max extents: 32765
--> R4 in tablespace RBS_4 is ONLINE; AUTOEXTEND is OFF
.... currently allocated: 8192K
.... next extent size: 4096K; max extents: 32765
WARNING: --> For the upgrade, use a large (minimum 70M) public rollback segment
WARNING: --> Take smaller public rollback segments OFFLINE
.
***********************************************************************
Flashback: OFF
***********************************************************************
***********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 32-bit database.
***********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
WARNING: --> "shared_pool_size" needs to be increased o at least 236 MB
WARNING: --> "undo_management" is not defined and must have a value=MANUAL
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "shared_pool_size" needs to be increased o at least 472 MB
WARNING: --> "undo_management" is not defined and must have a value=MANUAL
.
***********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
***********************************************************************
-- No renamed parameters found. No changes are required.
.
***********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

***********************************************************************
--> background_dump_test 11.1 DEPRECATED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
.
***********************************************************************
Components: [The following database components will be upgraded or installed]
***********************************************************************
--> Oracle Catalog Views [Upgrade] VALID
--> Oracle Packages and Types [Upgrade] VALID
.
***********************************************************************
Miscellaneous Warnings
***********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle in is empty.
.


Currently, I am in a dilemma in solving the above mentioned warnings. Can anyone kindly guide me?

Thank you in advanced.

Edited by: Maxwell T on Nov 27, 2012 6:32 PM
  • 1. Re: Resolve Pre-Upgrade Script before Upgrading DB
    976124 Newbie
    Currently Being Moderated
    I had increased the SYSTEM tablespace through the following query:
    SQL> ALTER DATABASE DATAFILE 'C:\...\DATABASE\SYS01.DBF' RESIZE 493M;

    and am now having the following updated result:

    ***********************************************************************
    Tablespaces: [make adjustments in the current environment]
    ***********************************************************************
    WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
    .... minimum required size: 493 MB
    --> SYSAUX tablespace is adequate for the upgrade.
    .... minimum required size: 222 MB
    --> TEMP tablespace is adequate for the upgrade.
    .... minimum required size: 60 MB
    .
    ***********************************************************************
    Rollback Segments: [make adjustments immediately prior to upgrading]
    ***********************************************************************
    --> R0 in tablespace SYSTEM is OFFLINE; AUTOEXTEND is OFF
    .... currently allocated: 7168K
    .... next extent size: 32K; max extents: 32765
    --> R1 in tablespace RBS_1 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R2 in tablespace RBS_2 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R3 in tablespace RBS_3 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R4 in tablespace RBS_4 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    WARNING: --> For the upgrade, use a large (minimum 70M) public rollback segment
    WARNING: --> Take smaller public rollback segments OFFLINE
    .
    ***********************************************************************
    Flashback: OFF
    ***********************************************************************
    ***********************************************************************
    Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    Note: Pre-upgrade tool was run on a lower version 32-bit database.
    ***********************************************************************
    --> If Target Oracle is 32-Bit, refer here for Update Parameters:
    WARNING: --> "shared_pool_size" needs to be increased o at least 236 MB
    WARNING: --> "undo_management" is not defined and must have a value=MANUAL
    .
    --> If Target Oracle is 64-Bit, refer here for Update Parameters:
    WARNING: --> "shared_pool_size" needs to be increased o at least 472 MB
    WARNING: --> "undo_management" is not defined and must have a value=MANUAL
    .
    ***********************************************************************
    Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
    ***********************************************************************
    -- No renamed parameters found. No changes are required.
    .
    ***********************************************************************
    Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]

    ***********************************************************************
    --> background_dump_test 11.1 DEPRECATED replaced by "diagnostic_dest"
    --> user_dump_dest 11.1 DEPRECATED replaced by "diagnostic_dest"
    .
    ***********************************************************************
    Components: [The following database components will be upgraded or installed]
    ***********************************************************************
    --> Oracle Catalog Views [Upgrade] VALID
    --> Oracle Packages and Types [Upgrade] VALID
    .
    ***********************************************************************
    Miscellaneous Warnings
    ***********************************************************************
    WARNING: --> Database is using a timezone file older than version 14.
    .... After the release migration, it is recommended that DBMS_DST package
    .... be used to upgrade the 10.2.0.5.0 database timezone version
    .... to the latest version which comes with the new release.
    WARNING: --> Your recycle bin is turned on and currently contains no objects.
    .... Because it is REQUIRED that the recycle bin be empty prior to upgrading
    .... and your recycle bin is turned on, you may need to execute the command:
    PURGE DBA_RECYCLEBIN
    .... prior to executing your upgrade to confirm the recycle in is empty.
    .


    SYSTEM tablespace's AUTOEXTEND is enabled, but why does the status remain as "OFF"?
  • 2. Re: Resolve Pre-Upgrade Script before Upgrading DB
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    >
    ...
    6: Run the pre-upgrade script:
    SQL> @?/rdbms/admin/PreUpgrade/utlu112i_5.sql;
    ...
    >

    This does not seem to be the official script, unless you have renamed it for some unknown reason.

    http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#CACHIDJD

    >
    ...
    SYSTEM tablespace's AUTOEXTEND is enabled, but why does the status remain as "OFF"?
    ...
    >

    Where are you seeing this ?

    HTH
    Srini
  • 3. Re: Resolve Pre-Upgrade Script before Upgrading DB
    976124 Newbie
    Currently Being Moderated
    Hi Srini,

    First of all, thank you for your reply.
    Srini Chavali wrote:
    >
    ...
    6: Run the pre-upgrade script:
    SQL> @?/rdbms/admin/PreUpgrade/utlu112i_5.sql;
    ...
    >

    This does not seem to be the official script, unless you have renamed it for some unknown reason.

    http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm#CACHIDJD
    The file name is of default as could be seen in the screenshot below:
    https://picasaweb.google.com/lh/photo/c-lrJhi2ttvdhRSwed3q8Sny0kb2Juq24a2dVk68TeM?feat=directlink

    >
    >
    ...
    SYSTEM tablespace's AUTOEXTEND is enabled, but why does the status remain as "OFF"?
    ...
    >

    Where are you seeing this ?
    It could be seen in the result of the pre-upgrade script in post 1:
    ***********************************************************************
    Tablespaces: [make adjustments in the current environment]
    ***********************************************************************
    WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
    .... currently allocated size: 491 MB
    .... minimum required size: 493 MB
    .... increase current size by: 2 MB
    *.... tablespace is AUTOEXTEND ENABLED.*
  • 4. Re: Resolve Pre-Upgrade Script before Upgrading DB
    976124 Newbie
    Currently Being Moderated
    Currently, I am trying to figure out how to expand the rollback segments' size but to no avail.
    ***********************************************************************
    Rollback Segments: [make adjustments immediately prior to upgrading]
    ***********************************************************************
    --> R0 in tablespace SYSTEM is OFFLINE; AUTOEXTEND is OFF
    .... currently allocated: 7168K
    .... next extent size: 32K; max extents: 32765
    --> R1 in tablespace RBS_1 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R2 in tablespace RBS_2 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R3 in tablespace RBS_3 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R4 in tablespace RBS_4 is ONLINE; AUTOEXTEND is OFF
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    WARNING: --> For the upgrade, use a large (minimum 70M) public rollback segment
    WARNING: --> Take smaller public rollback segments OFFLINE
  • 5. Re: Resolve Pre-Upgrade Script before Upgrading DB
    976124 Newbie
    Currently Being Moderated
    I had managed to solve the *"Update Parameters for 32-bit DB"* section and had tried to set all Tablespaces' AutoExtend to 'On'. Only the first Rollback Segment is unable to be set as "Autoextend On" as shown below:
    ***********************************************************************
    Rollback Segments: [make adjustments immediately prior to upgrading]
    ***********************************************************************
    --> R0 in tablespace SYSTEM is OFFLINE; AUTOEXTEND is OFF
    .... currently allocated: 7168K
    .... next extent size: 32K; max extents: 32765
    --> R1 in tablespace RBS_1 is ONLINE; AUTOEXTEND is ON
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R2 in tablespace RBS_2 is ONLINE; AUTOEXTEND is ON
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R3 in tablespace RBS_3 is ONLINE; AUTOEXTEND is ON
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    --> R4 in tablespace RBS_4 is ONLINE; AUTOEXTEND is ON
    .... currently allocated: 8192K
    .... next extent size: 4096K; max extents: 32765
    WARNING: --> For the upgrade, use a large (minimum 70M) public rollback segment
    WARNING: --> Take smaller public rollback segments OFFLINE
    .
    After that, I ran the *"Net Configuration Assistant"* followed by the *"DBUA"*.

    This time, I am able to complete the *"Upgrading Oracle Server"* step in *"DBUA: Progress"* up to *"Performing Post Upgrade"*.

    Unfortunately, I hit an error at the *"Configuring Database with Enterprise Manager"* step as shown below:

    https://picasaweb.google.com/lh/photo/04_CEOP-IdvyeODPymcRbiny0kb2Juq24a2dVk68TeM?feat=directlink

    https://picasaweb.google.com/lh/photo/pE2h08ikSUEBLYGwX3BSCyny0kb2Juq24a2dVk68TeM?feat=directlink

    Edited by: Maxwell T on Nov 27, 2012 6:04 PM
  • 6. Re: Resolve Pre-Upgrade Script before Upgrading DB
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    You can ignore these errors for now and configure database control later.

    How To Drop, Create And Recreate the Database Control (DB Control) Release 10g and 11g [ID 278100.1]

    HTH
    Srini
  • 7. Running EMCA
    976124 Newbie
    Currently Being Moderated
    Hi Srini,

    I had completed the upgrade and am following the instructions of the *"Option C.1. Recreate the DB Control Configuration Files only (leave Repository intact)"* section in the Support Document below:
    How To Drop, Create And Recreate the Database Control (DB Control) Release 10g and 11g [ID 278100.1]
    by keying in the following command:
    C:\Oracle\11g\DBSwLoc\dbs> emca -config dbcontrol db
    NOTE:
    Before I run the above query, I manually started DB 10g's Listener as 11g's Listener is not possible to be started.

    But I am being presented with the following errors:

    STARTED EMCA at NOV 28, 2012 12:19:18 pm
    EM Configuration Assistant, Version 11.2.0.3.0 Production
    Copyright (C) 2003, 2011, Oracle. All rights reserved.

    Enter the following information:
    Database SID: LV01
    Nov 28, 2012 12:119:21 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly

    WARNING: ORA-01031: insufficient privileges

    Nov 28, 2012 12:19:21 pm oracle.sysman,emcp.DatabaseChecks throwDBUnavailableException
    SEVERE:
    Database instance is unavailable.  Fix the ORA error thrown and run EM Configuration Assistant again.

    Some of the possible reasons may be :

    1) Database may not be up.
    2) Database is started setting environment variable ORACLE_HOME with trailing '/'. Reset ORACLE_HOME and bounce the database.

    For eg. Database is started setting environment variable ORACLE_HOME=/scratch/db/ . Reset ORACLE_HOME=/scratch/db and bounce the database.
    Edited by: Maxwell T on Nov 28, 2012 3:35 PM
  • 8. Re: Running Enterprise Manager Configuration Assistant
    976124 Newbie
    Currently Being Moderated
    Maxwell T wrote:
    Hi Srini,

    I had completed the upgrade and am following the instructions of the *"Option C.1. Recreate the DB Control Configuration Files only (leave Repository intact)"* section in the Support Document below:
    How To Drop, Create And Recreate the Database Control (DB Control) Release 10g and 11g [ID 278100.1]
    by keying in the following command:
    C:\Oracle\11g\DBSwLoc\dbs> emca -config dbcontrol db
    NOTE:
    Before I run the above query, I manually started DB 10g's Listener as 11g's Listener is not possible to be started.

    But I am being presented with the following errors:

    STARTED EMCA at NOV 28, 2012 12:19:18 pm
    EM Configuration Assistant, Version 11.2.0.3.0 Production
    Copyright (C) 2003, 2011, Oracle. All rights reserved.

    Enter the following information:
    Database SID: LV01
    Nov 28, 2012 12:119:21 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly

    WARNING: ORA-01031: insufficient privileges

    Nov 28, 2012 12:19:21 pm oracle.sysman,emcp.DatabaseChecks throwDBUnavailableException
    SEVERE:
    Database instance is unavailable.  Fix the ORA error thrown and run EM Configuration Assistant again.

    Some of the possible reasons may be :

    1) Database may not be up.
    2) Database is started setting environment variable ORACLE_HOME with trailing '/'. Reset ORACLE_HOME and bounce the database.

    For eg. Database is started setting environment variable ORACLE_HOME=/scratch/db/ . Reset ORACLE_HOME=/scratch/db and bounce the database.
    Edited by: Maxwell T on Nov 28, 2012 3:35 PM
    I managed to solve the error by enabling the following in the sqlnet.ora file (which is located in an external folder [based on Oracle DB 10g's setup previously]):
    sqlnet.authentication_services =(nts)
    But am still presented with the same error:
    Nov 28, 2012 4:17:46 PM oracle.sysman.emcp.EMConfig perform
    INFO: This operation is being logged at C:\Oracle\11g\cfgtoollogs\emca\LV01\emca_2012_11_28_16_05_07.log.
    Nov 28, 2012 4:17:46 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
    WARNING: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Nov 28, 2012 4:17:46 PM oracle.sysman.emcp.EMConfig perform
    SEVERE:

    Database connection through listener failed. Fix the error and run EM Configuration Assistant again.

    Some of the possible reasons may be:

    1) Listener port 1521 provided is incorrect. Provide the correct port.
    2) Listener is not up. Start the Listener.
    3) Database service LV01.world is not registered with listener. Register the database service.
    4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host. Unset ORACLE_HOSTNAME environment variable.
    5) Listener is up on virtual host. Set environment variable ORACLE_HOSTNAME=(virtual host).
    6) /etc/hosts does not have correct entry for hostname.

    Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\LV01\emca_2012_11_28_16_05_07.log for more details.
    Could not complete the configuration. Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\LV01\emca_2012_11_28_16_05_07.log for more details.
    It is strange, when I ran *"Oracle Net Configuration Assistant: Listener Configuration, Select Listener"*:
    Select a listener you want to reconfigure: ORACLE11GR2LISTENER
    and clicked "Next", I am being shown with the following error:
    This listener is currently running. Are you sure you want to stop and modify the listener with the name ORACLE11GR2LISTENER ?
    The current enabled listener is in fact Oracle DB 10g's listener (when I checked with "*Services.msc*"):
    OracleOracleClient10gTNSListener
    The following NEW listener could not be enabled:
    OracleOraDb11g_home1TNSListenerORACLE11GR2LISTENER
    I suspect that this happens because both listeners are pointing to the same port 1521. During the upgrade, in order to create the new listener with port 1521, I had manually disabled listener "*OracleOracleClient10gTNSListener*" through "*Services.msc*".

    Is there any fix to this?

    I tried to start Oracle DB 11g's listener through:
    C:\Oracle\11g\DBSwLoc\BIN> start lsnrctl
    LSNRCTL for 32-bit Windows: Version 11.2.0.3.0 - Production on 28-NOV-2012 18:00:01

    Copyright (C) 1991, 2011, Oracle. All rights reserved.

    Welcome to LSNRCTL, type "help" for information.

    LSNRCTL> start
    Starting tnslsnr: please wait...

    TNS-12560: TNS:protocol adapter error
    TNS-00530: Protocol adapter error

    LSNRCTL> status
    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 61: Unknown error
    but to no avail.

    Edited by: Maxwell T on Nov 28, 2012 6:02 PM
  • 9. Re: Running Enterprise Manager Configuration Assistant
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post the contents of the listener.ora and sqlnet.ora files in the $TND_ADMIN location of the 11gR2 ORACLE_HOME

    HTH
    Srini
  • 10. Re: Running Enterprise Manager Configuration Assistant
    976124 Newbie
    Currently Being Moderated
    After I modified the "listener.ora" file, the new listener *(OracleOraDb11g_home1TNSListener)* can now be enable, but I am still unable to run the emca:
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.EMConfig perform
    INFO: This operation is being logged at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log.
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
    WARNING: ORA-12516: TNS:listener could not find available handler with matching protocol stack
    
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.EMConfig perform
    SEVERE:
    
    Database connection through listener failed.  Fix the error and run EM Configuration Assistant again.
    
    Some of the possible reasons may be:
    
    1) Listener port 1521 provided is incorrect.  Provide the correct port.
    2) Listener is not up.  Start the Listener.
    3) Database service DB01.world is not registered with listener.  Register the database service.
    4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host.  Unset ORACLE_HOSTNAME environment variable.
    5) Listener is up on virtual host.  Set environment variable ORACLE_HOSTNAME=(virtual host).
    6) /etc/hosts/ does not have correct entry for hostname.
    
    Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log for more details.
    Could not complete the configuration.  Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log for more details.
    listener.ora_
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC.TESTING.COM)(PORT = 1521))
            (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
          )
        )
      )
    
    PASSWORDS_LISTENER= (oracle)
    
    SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (SID_NAME = EXTPROC)
          (ORACLE_HOME = C:\Oracle\11g)
          (PROGRAM = EXTPROC)
          (ENVS = "EXTPROC_DLLS=ANY")
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB01)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB02)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB03)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB04)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB05)
        )
        (SID_DESC =
          (GLOBAL_DBNAME = Oracle8)
          (SID_NAME = DB06)
        )
      )
    
    ADR_BASE_LISTENER = C:\Oracle\11g
    sqlnet.ora_
    TRACE_LEVEL_CLIENT = OFF
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    
    NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
    NAMES.DEFAULT_DOMAIN = world
    NAME.DEFAULT_ZONE = world
    AUTOMATIC_IPC = OFF
    SQLNET.EXPIRE_TIME = 10
    tnsnames.ora_
    DB01_LOCALPC.WORLD =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID = DB01)
        )
      )
    
    DB01.WORLD =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC.TESTING.COM)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = DB01.world)
        )
      )
    
    EXTPROC_CONNECTION_DATA.WORLD =
      (DESCRIPTION -
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
        (CONNECT_DATA =
          (SID = EXTPROC)
        )
      )
    
    DB01_COMPIP.WORLD =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = COMPIP)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SID = DB01)
        )
      )
    Another problem about the listener is, I have to start *(OracleOraDb11g_home1TNSListener)* manually, every time after I restarted the computer.

    Edited by: Maxwell T on Dec 3, 2012 6:01 PM
  • 11. Re: Running Enterprise Manager Configuration Assistant
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    How did you modify the listener ? Pl confirm you used netca executable to make changes to the listener. If not, use netca to make needed changes and try again

    HTH
    Srini
  • 12. Re: Running Enterprise Manager Configuration Assistant
    976124 Newbie
    Currently Being Moderated
    I modified the listener.ora file to the one I posted in the last post. Net Configuration Assistant was used to configure the listener but it does not start automatically during Windows' startup.

    There is a documentation on this and I had followed the instructions:
    http://docs.oracle.com/cd/E11882_01/win.112/e10845/ap_unix.htm#NTQRF531

    But the listener still require manual start up through "Services".

    Edited by: Maxwell T on Dec 5, 2012 11:56 AM
  • 13. Re: Running Enterprise Manager Configuration Assistant
    976124 Newbie
    Currently Being Moderated
    Maxwell T wrote:
    After I modified the "listener.ora" file, the new listener *(OracleOraDb11g_home1TNSListener)* can now be enable, but I am still unable to run the emca:
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.EMConfig perform
    INFO: This operation is being logged at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log.
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
    WARNING: ORA-12516: TNS:listener could not find available handler with matching protocol stack
    
    Dec 3, 2012 5:25:29 PM oracle.sysman.emcp.EMConfig perform
    SEVERE:
    
    Database connection through listener failed.  Fix the error and run EM Configuration Assistant again.
    
    Some of the possible reasons may be:
    
    1) Listener port 1521 provided is incorrect.  Provide the correct port.
    2) Listener is not up.  Start the Listener.
    3) Database service DB01.world is not registered with listener.  Register the database service.
    4) Listener is up on physical host and ORACLE_HOSTNAME environment variable is set to virtual host.  Unset ORACLE_HOSTNAME environment variable.
    5) Listener is up on virtual host.  Set environment variable ORACLE_HOSTNAME=(virtual host).
    6) /etc/hosts/ does not have correct entry for hostname.
    
    Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log for more details.
    Could not complete the configuration.  Refer to the log file at C:\Oracle\11g\cfgtoollogs\emca\DB01\emca_2012_12_03_17_24_36.log for more details.
    listener.ora_
    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC.TESTING.COM)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = extproc))
    )
    )
    )
    
    PASSWORDS_LISTENER= (oracle)
    
    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = EXTPROC)
    (ORACLE_HOME = C:\Oracle\11g)
    (PROGRAM = EXTPROC)
    (ENVS = "EXTPROC_DLLS=ANY")
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB01)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB02)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB03)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB04)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB05)
    )
    (SID_DESC =
    (GLOBAL_DBNAME = Oracle8)
    (SID_NAME = DB06)
    )
    )
    
    ADR_BASE_LISTENER = C:\Oracle\11g
    sqlnet.ora_
    TRACE_LEVEL_CLIENT = OFF
    SQLNET.AUTHENTICATION_SERVICES = (NTS)
    
    NAMES.DIRECTORY_PATH = (TNSNAMES, HOSTNAME)
    NAMES.DEFAULT_DOMAIN = world
    NAME.DEFAULT_ZONE = world
    AUTOMATIC_IPC = OFF
    SQLNET.EXPIRE_TIME = 10
    tnsnames.ora_
    DB01_LOCALPC.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = DB01)
    )
    )
    
    DB01.WORLD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALPC.TESTING.COM)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DB01.world)
    )
    )
    
    EXTPROC_CONNECTION_DATA.WORLD =
    (DESCRIPTION -
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    (CONNECT_DATA =
    (SID = EXTPROC)
    )
    )
    
    DB01_COMPIP.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = COMPIP)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = DB01)
    )
    )
    Another problem about the listener is, I have to start *(OracleOraDb11g_home1TNSListener)* manually, every time after I restarted the computer.

    Edited by: Maxwell T on Dec 3, 2012 6:01 PM
    There is a workaround for the listener to start automatically during system boot up, which is to disable "Public Networks" firewall.  But I am still at a lost regarding the emca error...
  • 14. Re: Upgrade Database from 10.2.0.5.0 to 11.2.0.3.0
    976124 Newbie
    Currently Being Moderated
    Just an update, this issue have been solved by removing all duplicate "listener.ora" files. (For e.g., Oracle 10g's NETWORK > ADMIN folder)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points