1 2 Previous Next 25 Replies Latest reply: Feb 8, 2010 4:02 AM by Aman.... RSS

    Oracle 11g installation

    Parul Garg
      Hi,

      I have installed database Oracle 11g on my machine with SID orcl2 with Automatic Storage Management ( ASM ) enabled . It has one disk group +DATA . I started the db with the startup command and it worked .
      Then i changed the local_listener parameter with command :-
      alter system set local_listener='listener' scope=spfile;

      And rebounced the database . But now its not getting started with error in local_listener parameter.
      I can start with pfile path as
      startup pfile="<<pfile path>>"

      But i want to start it with spfile ! How can i make corrections in spfile ?

      Thanks,
      Parul
        • 1. Re: Oracle 11g installation
          jjk
          Hi Parul,
          Can you put the oracle error no ?

          regards,
          Rossy.Rocs
          • 2. Re: Oracle 11g installation
            Parul Garg
            Now when i am trying to connect to db , i am getting other error as

            C:\Documents and Settings\pargarg>sqlplus
            SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 5 11:48:23 2010
            Copyright (c) 1982, 2007, Oracle. All rights reserved.

            Enter user-name: sys@orcl2 as sysdba
            Enter password:
            ERROR:
            ORA-12518: TNS:listener could not hand off client connection

            Also , i tried googling and setting the parameter
            DIRECT_HANDOFF_TTC_LISTENER=OFF IN LISTENER.ORA file.

            And when i see the parameter through the lsnrctl utility with

            show direct_handoff ; it gives me error saying
            NL-00853: undefined show command "direct_handoff". Try "help show"
            • 3. Re: Oracle 11g installation
              530453
              create a new spfile from pfile and your database will come up with that spfile
              • 4. Re: Oracle 11g installation
                Asif Muhammad
                Hi Parul,

                If you can start your database from the pfile, just create an spfile from the pfile you have.

                1. Startup pfile=<path to your pfile, which is woriking>
                2. create spfile='$ORACLE_HOME/dbs/spfileorcl2.ora' from pfile='$ORACLE_HOME/test.ora'

                In the above command I assume,
                - You are using linux, if it is windows you will have directory 'database' instead of directory 'dbs'
                - And the pfile is located in the Oracle home directory and the name of my pfile is "test.ora"
                - THe name of my database is orcl2 - which is the same in your case as well

                3.shtudown immediate;
                4. Startup

                Hopefully this will help u,
                Best Regards,
                Asif Muhammad
                • 5. Re: Oracle 11g installation
                  Parul Garg
                  I tried creating spfile from pfile with

                  sql>create spfile from pfile;

                  But there was no change in the error .

                  How can i change the spfile without logging into the database ?

                  Thanks .
                  • 6. Re: Oracle 11g installation
                    Deepak_DBA
                    hi,


                    >
                    sql>create spfile from pfile;
                    SQL>create spfile from pfile='location';

                    then close the DB ans startup..


                    regards,
                    Deepak
                    • 7. Re: Oracle 11g installation
                      Parul Garg
                      Hello,

                      I tried doing that too yesterday with command :-

                      create spfile from pfile=<<pfile path>>
                      But that didnt work though !!

                      One more doubt :-

                      Is my spfile address going to be $ORACLE_HOME/dbs/spfileorcl2.ora or since it is an ASM installation , it is
                      something "+DATA/orcl2/spfileorcl2.ora" ?



                      But after all this , now my tns listener gives error too when i try to connect with db .

                      Enter user-name: sys@orcl2 as sysdba
                      Enter password:
                      ERROR:
                      ORA-12518: TNS:listener could not hand off client connection

                      How can i remove this now ?
                      • 8. Re: Oracle 11g installation
                        Deepak_DBA
                        hi,

                        check this thread

                        ORA-12518: TNS:listener could not hand off client connection

                        One more doubt :-

                        Is my spfile address going to be $ORACLE_HOME/dbs/spfileorcl2.ora or since it is an ASM installation , it is
                        something "+DATA/orcl2/spfileorcl2.ora" ?
                        show parameter spfile--- see the location

                        if asm give spfile and pfile location as ASM in create spfile command

                        >
                        But after all this , now my tns listener gives error too when i try to connect with db .

                        Enter user-name: sys@orcl2 as sysdba
                        Enter password:
                        ERROR:
                        ORA-12518: TNS:listener could not hand off client connection

                        How can i remove this now ?
                        hope the thread helps you..


                        regards,
                        Deepak
                        • 9. Re: Oracle 11g installation
                          530453
                          *I can start with pfile path as
                          startup pfile="<<pfile path>>"*


                          Just set teh environment i.e. sid,path..etc and then
                          $ sqlplus / as sysdba

                          No need to start the database in no mount just create the spfile like:

                          SQL> create spfile='path_in_asm' from pfile='Location_of_good_pfile';

                          exit

                          $cd $ORACLE_HOME/dbs

                          vi initsid.ora
                          Place the pointer to spfile in ASM

                          $ sqlplus / as sysdba

                          SQL>startup


                          Hope this helps
                          • 10. Re: Oracle 11g installation
                            Parul Garg
                            Simar, the problem is

                            I have sid orcl2 installed on my machine . My db is down now and when i try to connect to idle instance by

                            C:\Documents and Settings\pargarg>sqlplus

                            SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 5 12:26:28 2010

                            Copyright (c) 1982, 2007, Oracle. All rights reserved.

                            Enter user-name: sys@orcl2 as sysdba
                            Enter password:
                            ERROR:
                            ORA-12518: TNS:listener could not hand off client connection

                            So how can i modify the memory usage ?

                            I am not able to connect to even the idle instance !

                            What can be done about this ?
                            • 11. Re: Oracle 11g installation
                              530453
                              why are you trying to connect using tns.

                              Try to connect directly as :

                              sqlplus / as sysdba
                              • 12. Re: Oracle 11g installation
                                Parul Garg
                                That gives me :-

                                C:\Documents and Settings\pargarg>sqlplus / as sysdba

                                SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 5 12:44:49 2010

                                Copyright (c) 1982, 2007, Oracle. All rights reserved.

                                ERROR:
                                ORA-12560: TNS:protocol adapter error
                                • 13. Re: Oracle 11g installation
                                  Parul Garg
                                  I actually have two databases on my machine .
                                  One with sid orcl and other orcl2 .

                                  When i set ORACLE_SID=orcl and write
                                  sqlplus / as sysdba , it connects properly but when i set ORACLE_SID=orcl2 and write
                                  sqlplus / as sysdba , it gives me TNS protocol adapter error !

                                  Thanks.
                                  • 14. Re: Oracle 11g installation
                                    Parul Garg
                                    Hi simar ,

                                    I tried what you have said to me .

                                    SQL> startup pfile='C:\app\pargarg\admin\Orcl2\pfile\init.ora.132010153248'
                                    ORACLE instance started.

                                    Total System Global Area 535662592 bytes
                                    Fixed Size 1334380 bytes
                                    Variable Size 163578772 bytes
                                    Database Buffers 364904448 bytes
                                    Redo Buffers 5844992 bytes
                                    Database mounted.
                                    Database opened.

                                    SQL> create spfile='+DATA/Orcl2/spfileOrcl2.ora' from pfile
                                    2 ;

                                    File created.



                                    SQL> alter system set spfile = 'C:\app\pargarg\product\11.1.0\db_1\database\spfileorcl2.ora'
                                    2 ;
                                    alter system set spfile = 'C:\app\pargarg\product\11.1.0\db_1\database\spfileorcl2.ora'
                                    *
                                    ERROR at line 1:
                                    ORA-02097: parameter cannot be modified because specified value is invalid
                                    ORA-01565: error in identifying file
                                    'C:\app\pargarg\product\11.1.0\db_1\database\spfileorcl2.ora'
                                    ORA-27046: file size is not a multiple of logical block size
                                    OSD-04012: file size mismatch (OS 526)

                                    I tried changing the db_block_size also which is now set to 8196 but it doesnt allow me !!



                                    SQL> startup pfile='C:\app\pargarg\admin\Orcl2\pfile\init.ora.132010153248'
                                    ORA-00374: parameter db_block_size = 1024 invalid ; must be a multiple of 512 in the range [2048..16384]
                                    SQL> startup pfile='C:\app\pargarg\admin\Orcl2\pfile\init.ora.132010153248'
                                    ORACLE instance started.

                                    Total System Global Area 535662592 bytes
                                    Fixed Size 1334380 bytes
                                    Variable Size 163578772 bytes
                                    Database Buffers 364904448 bytes
                                    Redo Buffers 5844992 bytes
                                    ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 2048)


                                    How can i make my spfile work properly with the help of this pfile !!


                                    My correct pfile location is :-

                                    'C:\app\pargarg\admin\Orcl2\pfile\init.ora.132010153248'


                                    My asm spfile location is :-

                                    +DATA/Orcl2/spfileOrcl2.ora

                                    And i am using windows machine with ORACLE_HOME being

                                    'C:\app\pargarg\product\11.1.0\db_1\

                                    Please help me with this .
                                    1 2 Previous Next