ORA-01033: ORACLE initialization or shutdown in progress in log_archive_dest_2 for standby

SQL> @error

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

Host Name          : ora-gold1

Ip Address         : 192.168.0.27

Error date         : 18-10-2014 12:25:49

Dest ID            : 2

Status             : ERROR

DB Name            : vihaan_stdy

DB Mode            : UNKNOWN

Recovery Mode      : IDLE

Protection Mode    : MAXIMUM PERFORMANCE

SRL Count          : 0

SRLActive          : 0

Archived Thread#   : 0

ArchivedSeq#       : 0

Applied Thread#    : 0

Destination        : stdy

Archiver           : LGWR

Transmit Mode      : ASYNCHRONOUS

Affirm             : NO

Asynchronous Blocks: 61440

Net Timeout        : 30

Delay (Mins)       : 0

Reopen (Secs)      : 300

Register           : YES

Binding            : OPTIONAL

Compression        : DISABLE

Error              : ORA-01033: ORACLE initialization or shutdown in progress

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

check whether are you able to connect with password primary and standby database with password ?

Check Primary to primary with password 

[oracle@ora-gold1 ~]$ sqlplus system/sys@prim

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 18 13:06:12 2014

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Check standby from primary 

[oracle@ora-gold1 ~]$ sqlplus system/sys@stdy

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 18 13:06:23 2014

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

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


so problem with password file .

copy password file from primary to standby ...

[oracle@ora-golden2 dbs]$ scp oracle@192.168.0.27:/u01/app/oracle/product/12.1.0/db_1/dbs/ora* .
oracle@192.168.0.27's password:
orapwvihaan                                                                                                                           100% 8704     8.5KB/s   00:00


on primary
set linesize 200 pagesize 200
col destination format a30
col ERROR format a40
select dest_id,destination,status,database_mode,recovery_mode,error from V$ARCHIVE_DEST_STATUS
where status != 'INACTIVE';
   DEST_ID DESTINATION                    STATUS    DATABASE_MODE   RECOVERY_MODE           ERROR
---------- ------------------------------ --------- --------------- ----------------------- ----------------------------------------
         1 /u01/app/oracle/ArchiveLog     VALID     OPEN            IDLE
         2 stdy                           VALID     MOUNTED-STANDBY MANAGED REAL TIME APPLY

Show sql for users in the RAC database ..... and Kill session SQL  

show_sql.sql

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||'@'||INST_ID||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             '-- alter system kill session '||''''||sid||','||serial#||'@'||INST_ID||''''|| ' immediate ;' kill ,
             to_char(LOGON_TIME,' Day DD YYYY HH24:MI') logon_time,
             to_char(sysdate,' Day DD YYYY HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from gv$session
       where 1=1
          and  status != 'INACTIVE'
      --  and last_call_ET >1000
      --  and MACHINE='ANUJ'
      --  and username ='USER'
          and rawtohex(sql_address) <> '00'
          and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                               sql_text
                     from gv$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like '%listener.get_cmd%' )
            --and           y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
            then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line(x.kill);
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
       end loop;
    end loop;
end;
 

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

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||'@'||INST_ID|| ') ospid = ' ||  process ||
                ' program = ' || program username,
             '-- alter system kill session '||''''||sid||','||serial#||'@'||INST_ID||''''|| ' immediate ;' kill ,
             to_char(LOGON_TIME,' Day DD YYYY HH24:MI') logon_time,
             to_char(sysdate,' Day DD YYYY HH24:MI') current_time,
            event,
            sql_id,
             sql_address, LAST_CALL_ET,
             status
        from gv$session
       where 1=1
          and  status != 'INACTIVE'
      --  and last_call_ET >1000
      --  and MACHINE='ANUJ'
      --  and username ='USER'
          and  SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
          and rawtohex(sql_address) <> '00'
          and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))    sql_text
                     from gv$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like '%listener.get_cmd%' )
            --and           y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
            then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line(x.kill);
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||  x.current_time|| ' last et = ' ||  x.LAST_CALL_ET);
                dbms_output.put_line( x.event );
                dbms_output.put_line( x.status );
                dbms_output.put_line( x.sql_id );
                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
            end if;
       end loop;
    end loop;
end;

output ....

-- alter system kill session '1474,47941@1' immediate ;
ANUJ(1474,47941@1) ospid = 25799 program = sqlplus@rac01
(TNS V1-V3)
Friday    23 2018 07:02  Wednesday 07 2018 07:41 last et = 1039108
resmgr:cpu quantum
ACTIVE
bp061zzjnstuu
BEGIN
        WHILE  1 = 1
        LOOP
                NULL;
        END
LOOP;
END;

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


set linesize 300 pagesize 300 
column username           format a30 word_wrapped
column module             format a30 word_wrapped
column action             format a15 word_wrapped
column client_info        format a30 word_wrapped
col kill 				for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''  as kill,
 username,
       module,
       action,
       client_info,
      sql_id,
     prev_sql_id,
     event 
from gv$session s
where 1=1
and module||action||client_info is not null
and  schemaname is not null 
and  schemaname not in ('SYS')
;

Delete archivelogs using RMAN until date .....

RMAN delete archive until time

RMAN> run
{
DELETE archivelog until time "to_date('2010-10-08:05:00:00','YYYY-MM-DD:hh24:mi:ss')";

crosscheck archivelog all;
delete noprompt expired archivelog all;

}

PRVF-0002 Could not retrieve local nodename

Oracle Database: PRVF-0002 Could not retrieve local nodename

Cause: Unable to determine local host name using Java network functions. 

Action: Ensure hostname is defined correctly using the 'hostname' command.

Change your hosts file as following , restart your network services and try installer again 

[root@ora-gold1 ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.27 ora-gold1 ora-gold1

ORA-21561: OID generation failed

[oracle@ora-golden2 admin]$ sqlplus 'sys/vihaan@stdy as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 5 12:49:22 2014

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

ERROR:

ORA-21561: OID generation failed

I have no idea why I am getting above error ..

on Oracle linux 6.5 , work around is restart network services and try again 

as a root ......

[root@ora-golden2 ~]# service network restart

Shutting down interface eth0:  Device state: 3 (disconnected)

                                                           [  OK  ]

Shutting down loopback interface:                          [  OK  ]

Bringing up loopback interface:                            [  OK  ]

Bringing up interface eth0:  Active connection state: activating

Active connection path: /org/freedesktop/NetworkManager/ActiveConnection/3

state: activated

Connection activated

                                                           [  OK  ]

[root@ora-golden2 ~]# su - oracle

[oracle@ora-golden2 ~]$ sqlplus 'sys/vihaan@stdy as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 5 12:49:49 2014

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

Connected to an idle instance.

Oracle Data Pump process info

Datapump Attach Job   ... 

Attach and Manage a running Datapump job

impdp/expdp: Attaching to a running Job



http://anuj-singh.blogspot.co.uk/2014/08/oracle-data-pump-process-info.html

--- command for import !!!
impdp system/vihaan@ORCL directory=DATA_PUMP_EXP dumpfile=XXX.dmp logfile=DATA_PUMP_EXP:XXX_IMPORT.log REMAP_TABLESPACE=USERS:ANUJ  schemas=XXX CLUSTER=N full=N status=10 metrics=Y JOB_NAME=ANUJIMP &

=======================================================================================
Import: Release 19.0.0.0.0 - Production on Mon Dec 1 16:51:18 2025
Version 19.28.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation: nonexistent file or path [29434]


use the connection for node1 then try again
check connection 
 sqlplus 'system/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.10.111.6)(Port=1521))(CONNECT_DATA=(SID=ORCL1)))'
 
                                                               VIP
impdp system/vihaan123@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.10.111.6)(Port=1521))(CONNECT_DATA=(SID=ORCL1))) directory=DATA_PUMP_EXP dumpfile=XXX.dmp logfile=DATA_PUMP_EXP:XXX_IMPORT.log REMAP_TABLESPACE=USERS:ANUJ  schemas=XXX CLUSTER=N full=N status=10 metrics=Y JOB_NAME=ANUJIMP &



impdp via EZCONNECT

impdp system/anuj1@//vip:1521/pdb1


 

http://anuj-singh.blogspot.com/2011/02/connect-to-sqlplus-with-no-tnsnames.html

                                                           vip
export TWO_TASK='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.11.111.6)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)))'

impdp system/anuj directory=DATA_PUMP_EXP dumpfile=anuj.dmp logfile=anuj1.log  REMAP_TABLESPACE=USERS:ANUJ schemas=ANUJ1 CLUSTER=N full=N status=10 metrics=Y &

export TWO_TASK=








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

Oracle Data Pump process info

set lines 150 pages 100 numwidth 7
col program  for a38
col username  for a10
col spid  for a7
col kill        for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') "To_DATE", s.program, s.status, s.username, d.job_name, p.spid, p.pid from gv$session s, gv$process p, dba_datapump_sessions d 
where 1=1 
and p.addr=s.paddr 
and s.saddr=d.saddr
and p.inst_id=s.inst_id;




set pages 200 lines 500
col OPNAME      for a10
col UNITS  for a10
col TARGET_DESC for a10 
col OWNER_NAME  for a10
col STATE  for a10
col JOB_MODE    for a10
col JOBNAME  for a20
col MESSAGE  for a30
col kill        for a15

col JOB_NAME  for a15

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,sofar,totalwork,units,target_desc,start_time,last_update_time,round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,message 
from gv$session_longops sl,gv$datapump_job dp 
where 1=1 
and sl.opname=dp.job_name 
and sofar != totalwork
/


--- > 12c With con_id

set pages 300 lines 500
col OPNAME      for a10
col UNITS  for a10
col TARGET_DESC for a10 
col OWNER_NAME  for a10
col STATE  for a10
col JOB_MODE    for a10
col JOBNAME  for a20
col MESSAGE  for a30
col kill        for a15
col JOB_NAME  for a15
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
select ''''||sl.sid ||','|| sl.serial#||',@'||sl.inst_id ||'''' kill,sl.CON_ID,sofar,totalwork,units,target_desc,start_time,last_update_time,round(time_remaining/60,2) "REMAINING MINS",round(elapsed_seconds/60,2) "ELAPSED MINS",round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS",
dp.owner_name,dp.state,dp.job_name,dp.job_mode,dp.workers,message 
from gv$session_longops sl,gv$datapump_job dp 
where 1=1 
and sl.opname=dp.job_name 
and sofar != totalwork
/





DBA_DATAPUMP_JOBS DBA_DATAPUMP_SESSIONS





set lines 200
col owner_name format a10
col state      format a11
col operation like state
col job_mode  like state
select owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs
where job_name not like 'BIN$%'
order by 1,2;


set linesize 200 pagesize 200 
col "Dropcommand" for a50
SELECT o.status, o.object_id, o.object_type,CREATED,'DROP TABLE ' ||o.owner||'.'||object_name||' ;' "Dropcommand" FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name 
AND o.object_name=j.job_name 
and state = 'NOT RUNNING'
order by created ;

alter session set nls_date_format='dd-mm-yyyy HH24:mi' ;
set pagesize 200 linesize 200
col "Long Operations w/SQL" for a150
select
--  distinct
'Kill_id            : '||''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''||chr(10)||
'USERNAME           : '||a.username  ||chr(10)||
'PROGRAM            : '||a.program   ||chr(10)||
'MACHINE            : '||a.machine   ||chr(10)||
'OS User            : '||a.osuser    ||chr(10)||
'Operation          : '||e.opname    ||chr(10)||
'Target             : '||e.target    ||chr(10)||
'Total Work         : '||e.totalwork ||chr(10)||
'Sofar              : '||e.sofar     ||chr(10)||
'Perc Complete%     : '||round(( e.sofar / decode ( nvl2(e.totalwork,e.totalwork,1) ,0,1, nvl2(e.totalwork,e.totalwork,1))) * 100) ||chr(10)||
'Start Time         : '||e.start_time   ||chr(10)||
'Last Update Time   : '||e.last_update_time           ||chr(10)||
'Remaining Minutes  : '||round(e.time_remaining/60)   ||chr(10)||
'Elapsed Minutes    : '||round(e.elapsed_seconds/60)  ||chr(10)||
'Status             : '||a.status   ||chr(10)||
'Process            : '||a.Process    ||chr(10)||
'Event              : '||a.event     ||chr(10)||
'SQL ID             : '||a.sql_id     ||chr(10)||
'Current Statement  : '||d.sql_text  "Long Operations w/SQL"
from gv$session a, gv$sess_io b,gv$sesstat c,gv$sql d,sys.gv$session_longops e
where e.sid   = a.sid (+)
and   e.serial#  =a.serial#(+)
and   e.sofar  != e.totalwork
and   a.sid   = b.sid(+)
and   a.sid   = c.sid(+) 
and (c.statistic# = 12 OR c.statistic# IS NULL)
and a.sql_address = d.address(+) 
and a.sql_hash_value = d.hash_value(+)
and (d.child_number = 0 OR d.child_number IS NULL)
-- and  a.SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
ORDER BY a.username;

===

Prompt with con id

alter session set nls_date_format='dd-mm-yyyy HH24:mi' ;

set pagesize 200 linesize 200

col "Long Operations w/SQL" for a150

select

--  distinct

'Kill_id            : '||''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||''''||chr(10)||

'Con Id**           : '|| a.con_id ||chr(10)||

'USERNAME           : '||a.username  ||chr(10)||

'PROGRAM            : '||a.program   ||chr(10)||

'MACHINE            : '||a.machine   ||chr(10)||

'OS User            : '||a.osuser    ||chr(10)||

'Operation          : '||e.opname    ||chr(10)||

'Target             : '||e.target    ||chr(10)||

'Total Work         : '||e.totalwork ||chr(10)||

'Sofar              : '||e.sofar     ||chr(10)||

'Perc Complete%     : '||round(( e.sofar / decode ( nvl2(e.totalwork,e.totalwork,1) ,0,1, nvl2(e.totalwork,e.totalwork,1))) * 100) ||chr(10)||

'Start Time         : '||e.start_time   ||chr(10)||

'Last Update Time   : '||e.last_update_time           ||chr(10)||

'Remaining Minutes  : '||round(e.time_remaining/60)   ||chr(10)||

'Elapsed Minutes    : '||round(e.elapsed_seconds/60)  ||chr(10)||

'Status             : '||a.status   ||chr(10)||

'Process            : '||a.Process    ||chr(10)||

'SQL ID             : '||a.sql_id     ||chr(10)||

'Current Statement  : '||d.sql_text  "Long Operations w/SQL"

from gv$session a, gv$sess_io b,gv$sesstat c,gv$sql d,sys.gv$session_longops e

where e.sid   = a.sid (+)

and   e.serial#  =a.serial#(+)

and   e.sofar  != e.totalwork

and   a.sid   = b.sid(+)

and   a.sid   = c.sid(+) 

and (c.statistic# = 12 OR c.statistic# IS NULL)

and a.sql_address = d.address(+) 

and a.sql_hash_value = d.hash_value(+)

and (d.child_number = 0 OR d.child_number IS NULL)

--and  a.module like '%Worker%'

-- and  a.SCHEMANAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

ORDER BY a.username;

set linesize 300 pagesize 300 

col sql_text for a50 wrap

col kill for a15

col event for a20

col username for a20

select distinct ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, dp.job_name, dp.session_type, s.username,  s.event, s.sql_id,dj.operation, dj.state ,q.sql_text

from gv$session s, dba_datapump_sessions dp, dba_datapump_jobs dj, gv$sql q

where s.saddr   = dp.saddr

and dp.job_name = dj.job_name

and s.sql_id    = q.sql_id

-- and s.inst_id in (1) 

order by s.inst_id;

set linesize 300 pagesize 1000

COLUMN instance_name FORMAT a10 HEADING 'Instance'

COLUMN sid HEADING 'Oracle|SID'

COLUMN serial_num HEADING 'Serial|#'

--COLUMN opname FORMAT a30 HEADING 'RMAN|Operation'

COLUMN start_time FORMAT a18 HEADING 'Start|Time'

COLUMN totalwork HEADING 'Total|Work'

COLUMN sofar HEADING 'So|Far'

COLUMN pct_done HEADING 'Percent|Done'

COLUMN elapsed_seconds HEADING 'Elapsed|Seconds'

COLUMN time_remaining HEADING 'Seconds|Remaining'

COLUMN done_at FORMAT a18 HEADING 'Done|At'

col kill for a15

col EVENT for a30

col PROGRAM for a20

SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill

,a.sql_id

,PREV_SQL_ID

, b.opname opname

, TO_CHAR(b.start_time, 'dd/mm/yy HH24:MI:SS') start_time

, b.totalwork totalwork

, b.sofar sofar

, ROUND( (b.sofar/DECODE( b.totalwork, 0, 0.001, b.totalwork)*100),2) pct_done

, b.elapsed_seconds elapsed_seconds

, b.time_remaining time_remaining

,a.event

, DECODE( b.time_remaining, 0, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'dd/mm/yy HH24:MI:SS')

, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'dd/mm/yy HH24:MI:SS')) done_at

,PROGRAM

FROM gv$session a, gv$session_longops b

where 1=1

and a.sid=b.sid

and a.serial#=b.serial#

and a.inst_id=b.inst_id

-- and a.program LIKE 'rman%' AND b.opname LIKE 'RMAN%' AND b.opname NOT LIKE '%aggregate%'

AND b.totalwork > 0

ORDER BY  b.start_time;

Attach and Manage a running Datapump job
impdp/expdp: Attaching to a running Job

set pages 200 lines 200

TTITLE 'Currently Active DataPump Operations'
COL owner_name          FORMAT A06      HEADING 'Owner'
COL job_name            FORMAT A20      HEADING 'JobName'
COL operation           FORMAT A12      HEADING 'Operation'
COL job_mode            FORMAT A12      HEADING 'JobMode'
COL state               FORMAT A12      HEADING 'State'
COL degree              FORMAT 9999     HEADING 'Degr'
COL attached_sessions   FORMAT 9999     HEADING 'Sess'
SELECT
 owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs
/




— jobname from dba_datapump_jobs
expdp \"/ as sysdba\" attach='JOB_NAME'
or
impdp \"/ as sysdba\" attach='JOB_NAME'

for pdbs!!!

export ORACLE_PDB_SID=ANUJ
impdp \"/ as sysdba\" attach='SYS_IMPORT_FULL_01'

Import> status

Job: SYS_IMPORT_FULL_01
  Operation: IMPORT
  Mode: FULL
  State: EXECUTING

To kill a datapump job
STOP_JOB=IMMEDIATE
KILL_JOB

To stop the datapump job
STOP_JOB


Export> parallel=4

Export> start_job

Export> status

set lines 200 pagesize 300 
col owner_name for a10
col job_name for a20
col state for a12 
col operation like owner_name
col job_mode  like owner_name
select owner_name, job_name, operation, job_mode,state, attached_sessions from dba_datapump_jobs;


OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
SYS        ANUJ                 EXPORT     SCHEMA     EXECUTING                    1


expdp  \'/ as sysdba\' attach=ANUJ


Export> status


KILL_JOB Detach and delete job.

PARALLEL Change the number of active workers for current job.

REUSE_DUMPFILES Overwrite destination dump file if it exists [N]. 

START_JOB Start or resume current job. Valid keyword values are: SKIP_CURRENT.

STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available.

STOP_JOB Orderly shutdown of job execution and exits the client.Valid keyword values are: IMMEDIATE.

Export> stop_job  Are you sure you wish to stop this job ([yes]/no): yes

restart the job 

Import> continue_client

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

set linesize 120 pagesize 120

col name  heading 'Name'  format a18

col status  heading 'Status'  format a20

col timeout  heading 'Timeout'  format 999999

col error_number  heading 'Error Number'  format 999999

col error_msg  heading 'Message'  format a44

select session_id, name,status, timeout, error_number, error_msg from dba_resumable;

set linesize 200 pagesize 120

col object_name heading 'Object Name' format a14

col object_type heading 'Object Type' format a14

col reason heading 'Reason' format a34

col suggested_action      heading 'Suggested action' format a34

select object_name,object_type,reason,suggested_action from dba_outstanding_alerts;

set linesize 120  pagesize 120

col seq# heading 'Seq#' format 9999

col event heading 'Wait Event' format a50

col wait_time heading 'Wait time' format 9999

col seconds_in_wait heading 'Seconds' format 9999

col state heading 'State' format a18

col seq# heading 'Seq#' format 9999

col seq# heading 'Seq#' format 9999

col seq# heading 'Seq#' format 9999

col seq# heading 'Seq#' format 9999

col kill for a16

select 

 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill  ,

 s.con_id,

 sw.seq#,sw.event, sw.wait_time,sw.seconds_in_wait,sw.state from gv$session_wait sw, gv$session s,dba_datapump_sessions d

where sw.wait_class <> 'Idle'

and sw.sid=s.sid

and sw.inst_id=s.inst_id

and s.saddr=d.saddr;

How To Break And Restart A DataPump Export Or Import Job (Doc ID 1400974.1)

set lines 500 pagesize 300

col opname format a35

col target format a25

col units format a10

col kill for a17

col message for a20

col kill for a17

select * from (

      select

      ''''||sid ||','||serial#||',@'||inst_id ||'''' kill , sql_id,

      opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min

      ,sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id,  to_char(sql_exec_start, 'dd-mm-yyyy hh24:mi:ss') sql_exec_start

      ,message

      from gv$session_longops

      WHERE sofar < totalwork

      order by start_time desc

  )

/

set lines 200

col logn_time for a25

col sid_serial for a20

col username for a20

col backup_type for a15

col job_name for a30

col kill for a17

select

''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,

   to_char(s.logon_time,'dd-mm-yyyy hh24:mi:ss') logon_time

  ,s.status

  ,s.username

  ,s.sid||','||s.serial# sid_serial

  ,case when s.program like 'rman%'                       then 'RMAN'

        when s.program like 'ude%' or s.program like '%DM%' or s.program like '%DW%' then 'DATAPUMP'

        else '-'

   end backup_type

  ,dps.job_name job_name

from gv$session s, dba_datapump_sessions dps

where (program like 'rman%' or program like 'ude%' or program like '%DM%' or program like '%DW%')

   and dps.saddr(+) = s.saddr

order by backup_type, logon_time

;

CLEAR BREAKS

CLEAR COLUMNS

SET PAGESIZE 66  HEADING ON  VERIFY OFF LINESIZE 400  FEEDBACK ON SQLCASE UPPER  NEWPAGE 1
SET SQLCASE mixed
ALTER SESSION SET nls_date_format='DD.MM.YYYY HH24:MI:SS';
ALTER SESSION SET nls_timestamp_format='DD.MM.YYYY HH24:MI:SS';

COLUMN sid                      FORMAT 999999 WRAP HEADING "SID"
COLUMN serial#                  FORMAT 999999 WRAP HEADING "Serial"
COLUMN file_id                  FORMAT 9999 heading "File ID"
COLUMN start_time               FORMAT A20 heading "Start Time"
COLUMN end_time                 FORMAT A20 heading "Estimated End Time"
COLUMN elapsed_seconds          FORMAT 999999999999 heading "Elapsed (Seconds)"
COLUMN formatted_elapsed_time   FORMAT A15 heading "Elapsed"
COLUMN throughput               FORMAT 999999999999 heading "Throughput (Bytes)"
COLUMN throughput_formatted     FORMAT A12 heading "Throughput"
COLUMN sofar                    FORMAT 999999999999 heading "Processed (Bytes)"
COLUMN sofar_formatted          FORMAT A12 heading "Processed"
COLUMN totalwork                FORMAT 999999999999 WRAP HEADING "Total (Bytes)"
COLUMN totalwork_formatted      FORMAT A12 WRAP HEADING "Total"
COLUMN percent                  FORMAT 999.99 WRAP HEADING "Completted (%)"
COLUMN status                   FORMAT A10 WRAP HEADING "Status"
COLUMN opname                   FORMAT A20 WRAP HEADING "Operation Name"
 col kill for a17
 col USERNAME for a20
 col MESSAGE for a30
TTITLE  ' Long Running '
SELECT
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill,
con_id,
USERNAME,
    to_number(regexp_substr(message, 'data file (\d+)', 1, 1, NULL, 1)) as file_id,
    start_time,
    start_time + (time_remaining/86400) AS end_time,
    --elapsed_seconds,
    lpad(CASE
        WHEN elapsed_seconds < 60 THEN elapsed_seconds || 's'
        WHEN elapsed_seconds < 3600 THEN
            FLOOR(elapsed_seconds / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's'
        WHEN elapsed_seconds < 86400 THEN
            FLOOR(elapsed_seconds / 3600) || 'h ' || FLOOR(MOD(elapsed_seconds, 3600) / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's'
        ELSE
            FLOOR(elapsed_seconds / 86400) || 'd ' || FLOOR(MOD(elapsed_seconds, 86400) / 3600) || 'h ' || FLOOR(MOD(elapsed_seconds, 3600) / 60) || 'm ' || MOD(elapsed_seconds, 60) || 's'
    END,15,' ') AS formatted_elapsed_time,
    --sofar/elapsed_seconds as throughput,
    lpad(CASE
        WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 1) THEN ROUND(sofar/nullif(elapsed_seconds, 0), 2) || ' B'
        WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 2) THEN ROUND(sofar/nullif(elapsed_seconds, 0) / POWER(1024, 1), 2) || ' KB'
        WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 3) THEN ROUND(sofar/nullif(elapsed_seconds, 0) / POWER(1024, 2), 2) || ' MB'
        WHEN sofar/nullif(elapsed_seconds, 0) < POWER(1024, 4) THEN ROUND(sofar/nullif(elapsed_seconds, 0)/ POWER(1024, 3), 2) || ' GB'
        ELSE ROUND(sofar/nullif(elapsed_seconds, 0)  / POWER(1024, 4), 2) || ' TB'
    END,12,' ')  AS throughput_formatted,
    --sofar,
    lpad(CASE
        WHEN sofar < POWER(1024, 1) THEN ROUND(sofar, 2) || ' B'
        WHEN sofar < POWER(1024, 2) THEN ROUND(sofar / POWER(1024, 1), 2) || ' KB'
        WHEN sofar < POWER(1024, 3) THEN ROUND(sofar / POWER(1024, 2), 2) || ' MB'
        WHEN sofar < POWER(1024, 4) THEN ROUND(sofar / POWER(1024, 3), 2) || ' GB'
        ELSE ROUND(sofar / POWER(1024, 4), 2) || ' TB'
    END,12,' ')  AS sofar_formatted,
    --totalwork,
    lpad(CASE
        WHEN totalwork < POWER(1024, 1) THEN ROUND(totalwork, 2) || ' B'
        WHEN totalwork < POWER(1024, 2) THEN ROUND(totalwork / POWER(1024, 1), 2) || ' KB'
        WHEN totalwork < POWER(1024, 3) THEN ROUND(totalwork / POWER(1024, 2), 2) || ' MB'
        WHEN totalwork < POWER(1024, 4) THEN ROUND(totalwork / POWER(1024, 3), 2) || ' GB'
        ELSE ROUND(totalwork / POWER(1024, 4), 2) || ' TB'
    END,12,' ') AS totalwork_formatted,
    sofar/totalwork*100 as percent,
    CASE
        WHEN time_remaining>0 THEN 'running'
        ELSE 'finished'
    END AS status
    ,opname
	,sql_id
	,substr(MESSAGE,1,30) MESSAGE
FROM
    gv$session_longops
WHERE 1=1
--opname LIKE 'XXX%'
and sofar != totalwork
ORDER BY
    file_id ASC,
    status DESC,
    start_time DESC;
	
set linesize 300 pagesize 300
col SQL_TEXT for a50
col FIRST_LOAD_TIME for a25
col LAST_LOAD_TIME for a25
col CPU_TIME for 99999999999
select elapsed_time/1000000 seconds, s.inst_id, s.users_executing, s.cpu_time,
       s.first_load_time, s.last_load_time,MODULE, s.sql_text
   from gv$sql s
    where users_executing > 0
and MODULE='Data Pump Worker'
    union
 select elapsed_time/1000000 seconds, s.inst_id,  s.users_executing, s.cpu_time,
       s.first_load_time, s.last_load_time,MODULE, s.sql_text
from gv$sql s
     where rownum < 50
and MODULE='Data Pump Worker'
   order by last_load_time
   /

--   Parameters Affecting Datapump Performance: 
	 
set pagesize 0 
select 'ALTER SYSTEM SET STREAMS_POOL_SIZE='||(max(to_number(trim(c.ksppstvl)))+67108864)/1024/1024||' mb ; '
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx 
and lower(a.ksppinm) in ('__streams_pool_size','streams_pool_size')
;

col OPNAME for a20
col kill for a17
SELECT  
''''||sid ||','|| serial#||',@'||'1'||'''' kill ,OPNAME,CONTEXT,SOFAR,TOTALWORK,   ROUND(SOFAR/TOTALWORK*100,2) "PERCENTAGE_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME in
(
select d.job_name
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr
)
AND  OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;

Oracle Recommended Patches Oracle Metalink Note 756671.1

Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1)