Check the latency of an apply process

COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATION HEADING 'Message Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 99999999999999

SELECT APPLY_NAME,
     (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
     TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
     TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
     DEQUEUED_MESSAGE_NUMBER  
  FROM V$STREAMS_APPLY_READER where apply_name='&APPLY';

How to set parameters for streams

Below you can see couple of examples:

exec dbms_apply_adm.set_parameter('&APPLY_NAME', 'disable_on_error', 'Y');

exec dbms_apply_adm.set_parameter('&APPLY_NAME','PARALLELISM','1')

exec dbms_capture_adm.set_parameter('&CAPTURE_NAME
','_CHECKPOINT_FREQUENCY','2000')

BEGIN
dbms_capture_adm.set_parameter(capture_name => '&CAPTURE_NAME',
                               parameter  => '_SGA_SIZE',
                               VALUE      => '300');
END;
/

How to see all the CAPTURE parameters

col capture_name format a30
col parameter format a50
col value format a50
col set_by_user format a3
select capture_name,parameter, value,set_by_user 
from dba_capture_parameters where capture_name 
like upper('&capture_name') and parameter like '%CHECKPOINT%';

How to check the APPLY parameters

col apply_name format a30
col parameter format a50
col value format a50
col set_by_user format a3
select apply_name,parameter, value,set_by_user from dba_apply_parameters 
where parameter like '%PARALL%';

How to add custom monitor credentials in EM12c

./emcli create_credential_set -target_type=oracle_database -set_name=PERSONALset -supported_cred_types=DBCreds

Changing SYS Password in Dataguard Environment

If you change SYS password with ALTER USER SYS IDENTIFIED BY NEWPASSWORD on the primary database of a dataguard environment, primary side stops to transfer archivelogs to standby and you will see an error on primary database alertlog file like:

------------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
--------------------------------------------- ---------------------

This is because “If you issue the ALTER USER statement to change the password for SYS, both the password stored in the data dictionary and the password stored in the password file are updated.” So your password file is updated in primary side but not in standby side. In this situation set your password file in standby server with:

orapwd file=$ORACLE_HOME/dbs/orapwSID password=newpassword; (don't forget to move/delete old one)

Conclusion: If you’re going to change your sys password in a dataguard environment you must set the password files with new password in both primary and standby servers.

In how much time will RMAN or Datapump finish the job?

This is how you can see it:

RMAN

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar,(sofar/totalwork) * 100 per_done,start_time, sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and lower(opname) not like ‘%aggregate%’
and lower(opname) like ‘rman%’;
SID START_TIME        TOTALWORK SOFAR   PER_DONE END_AT
—– —————– ———- ———- ———- —————– —————– 
491 18/08/10 20:13:45 1213440 1165439   96.04 18/08/10 20:18:06 
480 18/08/10 20:14:42 486816  172329    35.39 18/08/10 20:23:50

Export

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round ((sofar/totalwork) * 100) done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like '%EXPORT%';
SID START_TIME TOTALWORK SOFA PER_DONE END_AT
———- —————– ———- ———- ———- —————– —————–
113 18/08/10 21:03:48 3864 892 23.08 18/08/10 21:33:21

Import

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round ((sofar/totalwork) * 100) per_done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like '%IMPORT%';
SID START_TIME TOTALWORK SOFAR PER_DONE END_AT
———- —————– ———- ———- ———- —————– —————–
121 19/08/10 11:02:54 3004
532 17.70 19/08/10 11:33:05