Wednesday, March 1, 2017

RMAN waiting for snapshot control file enqueue

oracledev.domain.com:DEMODB:/home/oracle $ . dbaenv
ORACLE_SID = [DEMODB] ? demodb
The Oracle base remains unchanged with value /opt/oracle
oracledev.domain.com:demodb:/home/oracle $ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Feb 28 08:54:29 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEMODB (DBID=77281235)

RMAN> connect catalog demodb_rman/*******@rcv

connected to recovery catalog database

RMAN> show all for db_unique_name all;

waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
waiting for snapshot control file enqueue
^C
user interrupt received
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 02/28/2017 09:13:52
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03099: job cancelled at user request

RMAN> exit


Check ANY RMAN jobs running/hanging in the Database:

SQL> SELECT SID, SERIAL#,SOFAR,TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2)  "%_COMPLETE",OPNAME,to_char(START_TIME, 'dd-mm-yy hh24:mi:ss') START_TIME FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'  AND OPNAME NOT LIKE '%aggregate%'   AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

no rows selected

SQL> select sid, CLIENT_INFO ch, seq#, event, state from v$session where program like '%rman%';

      SID CH                                                                     SEQ# EVENT                                                            STATE
---------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- -------------------
       172 rman channel=dev_0                                                    61014 imm op                                                           WAITING
       433 rman channel=dev_1                                                    46223 SQL*Net message from client                                      WAITING
       446                                                                       16546 SQL*Net message from client                                      WAITING
       471                                                                       28962 SQL*Net message from client                                      WAITING
       509 rman channel=dev_2                                                    17665 SQL*Net message from client                                      WAITING
       536 rman channel=dev_3                                                    16948 SQL*Net message from client                                      WAITING
       563 rman channel=dev_4                                                     8144 SQL*Net message from client                                      WAITING
       588 rman channel=dev_5                                                    54329 SQL*Net message from client                                      WAITING
       615 rman channel=dev_6                                                    50208 SQL*Net message from client                                      WAITING
       641 rman channel=dev_7                                                    54811 SQL*Net message from client                                      WAITING

10 rows selected.


Need to kill all above SID's .......................



SQL> select SESSION_KEY,SESSION_RECID,START_TIME,END_TIME,STATUS from V$RMAN_BACKUP_JOB_DETAILS where STATUS='RUNNING';

SESSION_KEY   SESSION_RECID   START_TIM   END_TIME  STATUS
--------------- ------------- --------- ---------     -----------------------
        313           313    19-FEB-17   28-FEB-17    RUNNING

                               
SQL> col TIME_TAKEN_DISPLAY for a33
SQL> select session_key, command_id, status, time_taken_display from v$rman_backup_job_details where STATUS='RUNNING';

SESSION_KEY COMMAND_ID                        STATUS                  TIME_TAKEN_DISPLAY
----------- --------------------------------- ----------------------- ---------------------------------
        313 2017-02-19T18:00:16               RUNNING                 ###:19:04




SQL> select sid from v$rman_status where COMMAND_ID='2017-02-19T18:00:16';

       SID
----------
       446
       446
       446


SQL> select SID,serial#,username,osuser,status,machine,program  from v$session where sid=446 ;

       SID    SERIAL# USERNAME                       OSUSER                         STATUS   MACHINE                        PROGRAM
---------- ---------- ------------------------------ ------------------------------ -------- ------------------------------ ---------------------------------------
       446      35822 SYS                            oracle                         INACTIVE oracledev.domain.com          rman@oracledev.domain.com (TNS V1-V3)
                  

SQL> alter system kill session '446,35822' immediate;

System altered.

SQL> select session_key, command_id, status, time_taken_display from v$rman_backup_job_details where STATUS='RUNNING';

no rows selected



0 comments:

Post a Comment

Auto Scroll Stop Scroll