Month: October 2015

Posted in Dataguard

Dataguard missing archivelog

Posted on by mardaff

Below you can find one of the available solutions that can be applied to solve this issue:

PRI
select max(sequence#) from v$archived_log where applied='YES';

STBY
select max(sequence#) from v$archived_log where applied='YES';
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1          285           285

RMAN> list ARCHIVELOG FROM SEQUENCE 286 until SEQUENCE 285;

select name from v$archived_log where SEQUENCE#<=198895;

using target database control file instead of recovery catalog
specification does not match any archived log in the repository

RMAN> exit

PRI
RMAN>  list ARCHIVELOG FROM SEQUENCE 6299 until SEQUENCE 6300;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PRY
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
10474   1    6299    A 12-DEC-13
        Name: /u30/oradata/PRY/arch/1_6299_801284807.dbf

10472   1    6300    A 12-DEC-13
        Name: /u30/oradata/PRY/arch/1_6300_801284807.dbf


RMAN> exit


Recovery Manager complete.

scp the archivelogs to STBY (register them if it doesn”t take it)

# One way of registering a logfile is via the following:

SQL> alter database register logfile '/var/arch/arch_1_101.arc';

# So this is a straightforward way of registering a logfile giving the full path to the logfile. However, what if you have a very large number of logfiles to register, the above does not really scale all that well. Thankfully there is an rman command that enables you to register all logfiles within a directory:

rman> catalog start with ‘/var/arch’;

Advertisements
Posted in Dataguard, Standby

Error 1031 received logging on to the standby

Posted on by mardaff

Once I got the mentioned issue and the solution was pretty simple:

Log shipping user authentication problem
Error 1031 received logging on to the standby
PING[ARC0]: Heartbeat failed to connect to standby 'DB_STANDBY'. Error is 1031.
ORA-01031: insufficient privileges

SOLUTION
copy password file again from PRIMARY to STANDBY

Posted in Streams

LogMiner out-of-memory

Posted on by mardaff

First we need to get the name of the CAPTURE process and the actual value and then start tuning it:

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');
BEGIN
dbms_capture_adm.set_parameter(capture_name => '&capture_name',
                              parameter  => '_SGA_SIZE',
                              VALUE      => '300');
END;
/
Posted in Streams

ORA-24247: network access denied by access control list (ACL)

Posted on by mardaff

The error encountered is

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "OWNER.LDAP_NTFY", line 410
ORA-06512: at line 2

And the solution is this:

column acl format a30
column host format a20
column principal format a20
column privilege format a10
column is_grant format a8
set lines 1000
select acl , host , lower_port , upper_port from DBA_NETWORK_ACLS; --# it looks that here we have ONLY the IP. In this case we need to add also the HOSTNAME
select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_LDAPSYNC.xml','<hostname>',389,389);
Posted in Shell script, SQL

Grab stats for partitions

Posted on by mardaff

Useful scripts to grab stats for partitions:

set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS 
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE LAST_ANALYZED is null AND 
TABLE_NAME = '&TABLE_NAME' and partition_name like ('&PART_NAME');
BEGIN
  FOR I IN C1 LOOP
   dbms_output.put_line('Gathering stats for :'||I.P);
   LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1);' ;
   LV_SQL:= LV_SQL ||' END ;';
   EXECUTE IMMEDIATE LV_SQL USING  I.T,I.P;
  END LOOP;
END;
/

Or we can put it in a shell script:

#!/bin/bash

export ORACLE_SID=DB_NAME
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
export PATH=$PATH:$ORACLE_HOME/bin

cd /u01/app/oracle/admin/

sqlplus -S /nolog << EOF > gather_part_stats.log 2>> gather_part_stats.log
conn user/passwd

set serveroutput on
DECLARE
LV_SQL VARCHAR2(1000);
CURSOR C1 IS 
SELECT TABLE_NAME T , PARTITION_NAME P , LAST_ANALYZED L  FROM USER_TAB_PARTITIONS WHERE (LAST_ANALYZED is null or LAST_ANALYZED < sysdate -7) AND 
order by LAST_ANALYZED;
BEGIN
  FOR I IN C1 LOOP
   dbms_output.put_line('Gathering stats for :'||I.P);
   BEGIN
   LV_SQL:= 'BEGIN  ';
   LV_SQL:= LV_SQL ||'dbms_stats.gather_table_stats (''OWNER'','||':1'||',partname=>'||':2'||',granularity=>''partition'',estimate_percent => 1,CASCADE => TRUE, method_opt => ''FOR ALL COLUMNS SIZE 1'');' ;
   LV_SQL:= LV_SQL ||' END ;';
   EXECUTE IMMEDIATE LV_SQL USING  I.T,I.P;
   END;
  END LOOP;
END;
/
exit
EOF
Posted in SQL

Get the blocking sessions

Posted on by mardaff 
select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;

SELECT sid, lmode acquired, request, blocking_session, SQL_TEXT 
FROM v$lock l JOIN v$session s USING (sid) LEFT JOIN v$sqlarea USING (sql_id) WHERE block = 1 OR request > 0 ;

Tree like:

WITH sessions_info
AS
(SELECT sid, blocking_session, row_wait_obj#, sql_id FROM v$session)
SELECT DECODE(LEVEL, 1, 'Root Blocker') STATUS, LPAD (' ', LEVEL) || sid sid,
object_name,
SUBSTR (sql_text, 1, 50) sql_text
FROM sessions_info s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions_info)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL;

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;

select * from dba_waiters;

select * from dba_blockers;

If the DBA_WAITERS and DBA_BLOCKERS doesn’t exist, you can execute the script below and create them:

@?/rdbms/admin/catblock.sql