1 2 Previous Next 26 Replies Latest reply on Jun 3, 2016 5:15 AM by Jason Brincat

    adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table

    user11952526


      Hi,

      when I am executing adop phase=cutover, getting the below error...

       

       

       

      [ERROR]     Failed to execute SQL statement:

         begin

          AD_ZD_ADOP.LOCK_SESSIONS_TABLE('erp1',60,2);

        end;

       

      [ERROR]     Error Message:

      [ERROR]     ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table. LOCK row seems to have been inserted by another session

      [ERROR]     ORA-06512: at "APPS.AD_ZD_ADOP", line 680

      [ERROR]     ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute)

      [UNEXPECTED]Unable to acquire lock on sessions table.

       

       

      [STATEMENT] Please run adopscanlog utility, using the command

       

      "adopscanlog -latest=yes"

       

      to get the list of the log files along with snippet of the error message corresponding to each log file.

       

      adop exiting with status = 3 (Fail)

       

      When executed as apps user...

       

      SQL> select * from ad_adop_sessions where edition_name='LOCK' ;
      SQL>
      ADOP_SESSION_ID PREPARE_STATUS  APPLY_STATUS FINALIZE_STATUS CUTOVER_STATUS  CLEANUP_STATUS  ABORT_STATUS STATUS     NODE_NAME  NODE_TYPE                      APPLTOP_ID EDITION_NAME                          PID ABANDON_FLAG SESSION_INPUT_DATA                                                               PREPARE_S PREPARE_E FINALIZE_ FINALIZE_ APPLY_STA APPLY_END CUTOVER_S CUTOVER_E CLEANUP_S CLEANUP_E ABORT_STA ABORT_END
      --------------- --------------- ------------ --------------- --------------- --------------- ------------ ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------ -------------------------------------------------------------------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
                    0 X               X            X               X               X               X            Y          erp1                                                 LOCK

       

       

      SQL> show user
      USER is "APPS"
      SQL>

      SQL> begin
          AD_ZD_ADOP.LOCK_SESSIONS_TABLE('erp1',60,2);
        end;
        /  2    3    4
      begin
      *
      ERROR at line 1:
      ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table. LOCK row
      seems to have been inserted by another session
      ORA-06512: at "APPS.AD_ZD_ADOP", line 680
      ORA-06512: at line 2

       

      Any help how to fix this ?

       

      Thanks.

      John.

        • 1. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
          Jason Brincat

          Looks like there may have been another adop session that was terminated and left the lock row !

           

          please run the following and send the results so I can have a look and determine 1) what happened, 2) next course of action

           

          regards

          Jason

          • 2. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
            user11952526

            Hi Jason,

             

            What to run ?

            I believe you missed to include the steps you want me to run.

             

            Regards.

            John.

             


            • 3. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
              BalaMali

              Hi,

              Try as below

               

              begin 

              AD_ZD_ADOP.UNLOCK_SESSIONS_TABLE('<hostname>',60,2); 

              end;  

              /

               

              Then try

               

              select * from ad_adop_sessions where edition_name='LOCK' ;

               

              If lock is removed try running adop again.

              • 4. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                Jason Brincat

                Apologies John,

                 

                Run the diagnostics here and send the results.

                If you are concerned with security when posting to the forum, you can email me directly at jason.brincat@primeq.com.au

                Script to Diagnose adop and Other AD-TXK Issues in Oracle E-Business Suite Release 12.2 [ Patch 19045166:R12.AD.C ] (Doc ID 1901242.1)

                 

                kind regards

                Jason

                • 5. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                  user11952526

                  Hi Jeson,

                  This what I am getting...I do not see the session ID

                  SQL> select * from ad_adop_sessions where edition_name='LOCK' ;

                  ADOP_SESSION_ID PREPARE_STATUS  APPLY_STATUS FINALIZE_STATUS CUTOVER_STATUS  CLEANUP_STATUS  ABORT_STATUS STATUS     NODE_NAME  NODE_TYPE                      APPLTOP_ID EDITION_NAME                          PID ABANDON_FLAG SESSION_INPUT_DATA                                                               PREPARE_S PREPARE_E FINALIZE_ FINALIZE_ APPLY_STA APPLY_END CUTOVER_S CUTOVER_E CLEANUP_S CLEANUP_E ABORT_STA ABORT_END
                  --------------- --------------- ------------ --------------- --------------- --------------- ------------ ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------ -------------------------------------------------------------------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
                                0 X               X            X               X               X               X            Y          erp1                                                 LOCK

                  SQL> show user
                  USER is "APPS"
                  SQL>

                  SQL> begin
                      AD_ZD_ADOP.LOCK_SESSIONS_TABLE('erp1',60,2);
                    end;
                    /  2    3    4
                  begin
                  *
                  ERROR at line 1:
                  ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table. LOCK row
                  seems to have been inserted by another session
                  ORA-06512: at "APPS.AD_ZD_ADOP", line 680
                  ORA-06512: at line 2

                  SQL>

                   

                  SQL> select * from ad_adop_sessions where edition_name='LOCK' ;

                  ADOP_SESSION_ID PREPARE_STATUS  APPLY_STATUS FINALIZE_STATUS CUTOVER_STATUS  CLEANUP_STATUS  ABORT_STATUS STATUS     NODE_NAME  NODE_TYPE                      APPLTOP_ID EDITION_NAME                          PID ABANDON_FLAG SESSION_INPUT_DATA                                                               PREPARE_S PREPARE_E FINALIZE_ FINALIZE_ APPLY_STA APPLY_END CUTOVER_S CUTOVER_E CLEANUP_S CLEANUP_E ABORT_STA ABORT_END
                  --------------- --------------- ------------ --------------- --------------- --------------- ------------ ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------ -------------------------------------------------------------------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
                                0 X               X            X               X               X               X            Y          erp1                                                 LOCK

                   

                  Regards.
                  John

                  • 6. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                    Jason Brincat

                    Hi John,

                    I have fixed this many times.

                    I always play it safe. yes the fix can be easy. backup the table, delete the row, run adop. However I prefer to review the diagnostics and make a decision based on what happened and what potentially can happen.

                     

                    regards

                    Jason

                    • 7. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                      user11952526

                      Hi Jason,

                       

                      I will send the diagnostic_info_.tar file. I am working on it.

                       

                      I have prepared the below steps... Is these steps okay ?

                       

                      =====================================================================================================================================================================================================
                      1) Search for locked sessions

                      select * from ad_adop_sessions where EDITION_NAME ='LOCK';


                      ADOP_SESSION_ID PREPARE_STATUS  APPLY_STATUS FINALIZE_STATUS CUTOVER_STATUS  CLEANUP_STATUS  ABORT_STATUS STATUS     NODE_NAME  NODE_TYPE                      APPLTOP_ID EDITION_NAME                          PID ABANDON_FLAG SESSION_INPUT_DATA                                                               PREPARE_S PREPARE_E FINALIZE_ FINALIZE_ APPLY_STA APPLY_END CUTOVER_S CUTOVER_E CLEANUP_S CLEANUP_E ABORT_STA ABORT_END
                      --------------- --------------- ------------ --------------- --------------- --------------- ------------ ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------ -------------------------------------------------------------------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------
                                    0 X               X            X               X               X               X            Y          erp1                                                 LOCK

                       


                      2) Backup ad_adop_sessions:

                           create table ad_adop_sessions_bkp as select * from ad_adop_sessions;

                           delete from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK'; ---> Got the ADOP_SESSION_ID from previous

                           commit;


                      3) Unlock session: Run as APPS user

                           begin
                             AD_ZD_ADOP.LOCK_SESSIONS_TABLE('spaderp-dev1-app',60,2);
                             end;
                             /

                       

                      4) backup and drop table APPLSYS.FND_INSTALL_PROCESSES ;

                      create table APPLSYS.FND_INSTALL_PROCESSES_bkp as select * from APPLSYS.FND_INSTALL_PROCESSES ;

                       


                      4) start again adop phase=cutover

                      =====================================================================================================================================================================================================

                      Regards.
                      John

                       

                       

                       

                      • 8. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                        Jason Brincat

                        Hi John,

                        looking at this now.

                        Wont be long

                         

                        regards

                        Jason

                        • 9. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                          Jason Brincat

                          Hi John,

                          I cannot see the dropbox file. The link is to a file called 'get started with dropbox'

                          can you please try send it again

                           

                          thanks

                          jason

                          • 10. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                            user11952526

                            Hi Jason,

                             

                            I have resent it once again now. Please check and update.

                             

                            Regards.

                            John.

                            • 11. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                              Jason Brincat

                              Looks like you had a session that terminated unexpectadly

                              The lock row will remain there and prevent you from running adop so we need to fix that.

                               

                              Steps

                              --------

                              1. backup table ad_adop_sessions

                               

                              2. delete the problem row

                              delete from ad_adop_sessions where edition_name='LOCK';

                              it should say 1 row deleted

                              if it does commit;

                              if not stop there

                               

                              3. Now re-try cutover

                               

                              if this works please make this thread as solved

                               

                              thanks

                              jason

                              • 12. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                                user11952526

                                Hi Jason,

                                 

                                I am getting the below error...

                                 

                                 

                                select * from ad_adop_sessions where EDITION_NAME ='LOCK';

                                 

                                 

                                ADOP_SESSION_ID PREPARE_STATUS  APPLY_STATUS FINALIZE_STATUS CUTOVER_STATUS  CLEANUP_STATUS  ABORT_STATUS STATUS     NODE_NAME  NODE_TYPE                      APPLTOP_ID EDITION_NAME                          PID ABANDON_FLAG SESSION_INPUT_DATA                                                               PREPARE_S PREPARE_E FINALIZE_ FINALIZE_ APPLY_STA APPLY_END CUTOVER_S CUTOVER_E CLEANUP_S CLEANUP_E ABORT_STA ABORT_END

                                --------------- --------------- ------------ --------------- --------------- --------------- ------------ ---------- ---------- ------------------------------ ---------- ------------------------------ ---------- ------------ -------------------------------------------------------------------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- ---------

                                              0 X               X            X               X               X               X            Y          erp1                                                 LOCK

                                 

                                 

                                SQL> create table ad_adop_sessions_bkp as select * from ad_adop_sessions;

                                 

                                Table created.

                                 

                                SQL> select count(*) from ad_adop_sessions_bkp

                                  2  ;

                                 

                                  COUNT(*)

                                ----------

                                         3

                                 

                                SQL> delete from ad_adop_sessions where adop_session_id=0 and EDITION_NAME='LOCK';

                                 

                                1 row deleted.

                                 

                                SQL> commit;

                                 

                                Commit complete.

                                 

                                SQL> begin

                                    AD_ZD_ADOP.LOCK_SESSIONS_TABLE('erp1',60,2);

                                  end;

                                  /  2    3    4

                                 

                                PL/SQL procedure successfully completed.

                                 

                                 

                                 

                                 

                                 

                                [applebt1@erp1][/home/applebt1]

                                $ adop phase=cutover ------------------------------> From Run file system

                                 

                                Enter the APPS password:

                                Enter the SYSTEM password:

                                Enter the WLSADMIN password:

                                 

                                Validating credentials.

                                 

                                1. Initializing.

                                    Run Edition context  : /u03/ebt1/fs1/inst/apps/EBT1_erp1/appl/admin/EBT1_erp1.xml

                                    Patch edition context: /u03/ebt1/fs2/inst/apps/EBT1_erp1/appl/admin/EBT1_erp1.xml

                                *** glibc detected *** /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl: double free or corruption (out): 0x0000000002de4970 ***

                                ======= Backtrace: =========

                                /lib64/libc.so.6[0x303607247f]

                                /lib64/libc.so.6(cfree+0x4b)[0x30360728db]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl(Perl_sv_setsv_flags+0xb73)[0x476643]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl(Perl_pp_sassign+0x7e)[0x4684ce]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl(Perl_runops_standard+0xe)[0x46801e]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl(perl_run+0x2dd)[0x46484d]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl(main+0xec)[0x41ed7c]

                                /lib64/libc.so.6(__libc_start_main+0xf4)[0x303601d994]

                                /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl[0x41ebfa]

                                ======= Memory map: ========

                                00400000-0053a000 r-xp 00000000 08:11 55478117                           /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl

                                0063a000-00641000 rw-p 0013a000 08:11 55478117                           /u03/ebt1/fs1/FMW_Home/webtier/perl/bin/perl

                                00fd2000-03d92000 rw-p 00000000 00:00 0                                  [heap]

                                3035c00000-3035c1c000 r-xp 00000000 08:01 2124515                        /lib64/ld-2.5.so

                                3035e1c000-3035e1d000 r--p 0001c000 08:01 2124515                        /lib64/ld-2.5.so

                                3035e1d000-3035e1e000 rw-p 0001d000 08:01 2124515                        /lib64/ld-2.5.so

                                3036000000-303614f000 r-xp 00000000 08:01 2124516                        /lib64/libc-2.5.so

                                303614f000-303634e000 ---p 0014f000 08:01 2124516                        /lib64/libc-2.5.so

                                303634e000-3036352000 r--p 0014e000 08:01 2124516                        /lib64/libc-2.5.so

                                3036352000-3036353000 rw-p 00152000 08:01 2124516                        /lib64/libc-2.5.so

                                3036353000-3036358000 rw-p 00000000 00:00 0

                                3036400000-3036482000 r-xp 00000000 08:01 2124520                        /lib64/libm-2.5.so

                                3036482000-3036681000 ---p 00082000 08:01 2124520                        /lib64/libm-2.5.so

                                3036681000-3036682000 r--p 00081000 08:01 2124520                        /lib64/libm-2.5.so

                                3036682000-3036683000 rw-p 00082000 08:01 2124520                        /lib64/libm-2.5.so

                                3036800000-3036802000 r-xp 00000000 08:01 2121828                        /lib64/libdl-2.5.so

                                3036802000-3036a02000 ---p 00002000 08:01 2121828                        /lib64/libdl-2.5.so

                                3036a02000-3036a03000 r--p 00002000 08:01 2121828                        /lib64/libdl-2.5.so

                                3036a03000-3036a04000 rw-p 00003000 08:01 2121828                        /lib64/libdl-2.5.so

                                3037000000-3037016000 r-xp 00000000 08:01 2124518                        /lib64/libpthread-2.5.so

                                3037016000-3037215000 ---p 00016000 08:01 2124518                        /lib64/libpthread-2.5.so

                                3037215000-3037216000 r--p 00015000 08:01 2124518                        /lib64/libpthread-2.5.so

                                3037216000-3037217000 rw-p 00016000 08:01 2124518                        /lib64/libpthread-2.5.so

                                3037217000-303721b000 rw-p 00000000 00:00 0

                                3039c00000-3039c0d000 r-xp 00000000 08:01 2124522                        /lib64/libgcc_s-4.1.2-20080825.so.1

                                3039c0d000-3039e0d000 ---p 0000d000 08:01 2124522                        /lib64/libgcc_s-4.1.2-20080825.so.1

                                3039e0d000-3039e0e000 rw-p 0000d000 08:01 2124522                        /lib64/libgcc_s-4.1.2-20080825.so.1

                                303c800000-303c815000 r-xp 00000000 08:01 2124537                        /lib64/libnsl-2.5.so

                                303c815000-303ca14000 ---p 00015000 08:01 2124537                        /lib64/libnsl-2.5.so

                                303ca14000-303ca15000 r--p 00014000 08:01 2124537                        /lib64/libnsl-2.5.so

                                303ca15000-303ca16000 rw-p 00015000 08:01 2124537                        /lib64/libnsl-2.5.so

                                303ca16000-303ca18000 rw-p 00000000 00:00 0

                                3044200000-3044202000 r-xp 00000000 08:01 2124540                        /lib64/libutil-2.5.so

                                3044202000-3044401000 ---p 00002000 08:01 2124540                        /lib64/libutil-2.5.so

                                3044401000-3044402000 r--p 00001000 08:01 2124540                        /lib64/libutil-2.5.so

                                3044402000-3044403000 rw-p 00002000 08:01 2124540                        /lib64/libutil-2.5.so

                                3044a00000-3044a09000 r-xp 00000000 08:01 2124542                        /lib64/libcrypt-2.5.so

                                3044a09000-3044c08000 ---p 00009000 08:01 2124542                        /lib64/libcrypt-2.5.so

                                3044c08000-3044c09000 r--p 00008000 08:01 2124542                        /lib64/libcrypt-2.5.so

                                3044c09000-3044c0a000 rw-p 00009000 08:01 2124542                        /lib64/libcrypt-2.5.so

                                3044c0a000-3044c38000 rw-p 00000000 00:00 0

                                7ffba07db000-7ffba09dd000 rw-p 00000000 00:00 0

                                7ffba09dd000-7ffba09e1000 r-xp 00000000 08:11 55479507                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Filter/Util/Call/Call.so

                                7ffba09e1000-7ffba0ae0000 ---p 00004000 08:11 55479507                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Filter/Util/Call/Call.so

                                7ffba0ae0000-7ffba0ae1000 rw-p 00003000 08:11 55479507                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Filter/Util/Call/Call.so

                                7ffba0ae1000-7ffba0ae5000 r-xp 00000000 08:11 55479548                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Digest/MD5/MD5.so

                                7ffba0ae5000-7ffba0be4000 ---p 00004000 08:11 55479548                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Digest/MD5/MD5.so

                                7ffba0be4000-7ffba0be5000 rw-p 00003000 08:11 55479548                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/Digest/MD5/MD5.so

                                7ffba0be5000-7ffba0bed000 r-xp 00000000 08:11 55479774                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/List/Util/Util.so

                                7ffba0bed000-7ffba0cec000 ---p 00008000 08:11 55479774                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/List/Util/Util.so

                                7ffba0cec000-7ffba0ced000 rw-p 00007000 08:11 55479774                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/5.10.0/x86_64-linux-thread-multi/auto/List/Util/Util.so

                                7ffba0ced000-7ffba0d0b000 r-xp 00000000 08:11 55478248                   /u03/ebt1/fs1/FMW_Home/webtier/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/auto/DBI/DBI.so/u03/ebt1/fs_ne/EBSapps/appl/ad/bin/adop: line 187: 11008 Aborted                 $ADPERLPRG $AD_TOP/bin/adzdoptl.pl $@

                                 

                                 

                                [STATEMENT] Please run adopscanlog utility, using the command

                                 

                                "adopscanlog -latest=yes"

                                 

                                to get the list of the log files along with snippet of the error message corresponding to each log file.

                                 

                                 

                                adop exiting with status = 134 (Fail)

                                [applebt1@erp1][/home/applebt1]

                                $ adopscanlog -latest=yes

                                Scanning /u03/ebt1/fs_ne/EBSapps/log/adop/adop_20160508_182629.log log file ...

                                 

                                No Errors.

                                [applebt1@erp1][/home/applebt1]

                                $ more /u03/ebt1/fs_ne/EBSapps/log/adop/adop_20160508_182629.log

                                [applebt1@erp1][/home/applebt1]

                                $ ls -l /u03/ebt1/fs_ne/EBSapps/log/adop/adop_20160508_182629.log

                                -rw-r--r-- 1 applebt1 dba 0 May  8 18:29 /u03/ebt1/fs_ne/EBSapps/log/adop/adop_20160508_182629.log

                                [applebt1@erp1][/home/applebt1]

                                $

                                 

                                ======================

                                Thanks.

                                John

                                • 13. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                                  Jason Brincat

                                  Hi John,

                                  looks like you have some other issues there.

                                  Can you re-run the diagnostics and send to me again. I will need to dig deeper into the logs to see why your having this new issue

                                   

                                  thanks

                                  Jason

                                  • 14. Re: adop phase=cutover / ORA-20010: ERROR: Unable to acquire lock on ad_adop_sessions table
                                    user11952526

                                    Hi Jason,

                                     

                                    I have uploaded the diagnostic files.

                                     

                                    Regards.

                                    John.

                                    1 2 Previous Next