The Data Guard status of is Error ORA-19502: write error on file "", block number (block size=).

Error:  The Data Guard status of STLBAST is Error ORA-19502: write error on file "", block number (block size=).

Reason: Archive Destination become full in Standby Database server location.

Solution:

Step1: Check alert log both primary and standby database server.





Step2: Check Data Guard status log in primary Database server using following query


2015-DEC-01 09:58:18   ARC8: Completed archiving thread 1 sequence 57395 (10616070471665-10616070713099)
2015-DEC-01 09:58:51   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (19502)
2015-DEC-01 09:58:51   LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 09:58:51   Error 19502 for archive log file 4 to 'STLBAST'
2015-DEC-01 09:58:51   LNS: Failed to archive log 4 thread 1 sequence 57396 (19502)
2015-DEC-01 10:02:22   ARC9: Beginning to archive thread 1 sequence 57396 (10616070713099-10616070916651)
2015-DEC-01 10:02:23   ARC9: Completed archiving thread 1 sequence 57396 (10616070713099-10616070916651)
2015-DEC-01 10:04:48   ARCa: Beginning to archive thread 1 sequence 57397 (10616070916651-10616070982261)
2015-DEC-01 10:04:48   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
2015-DEC-01 10:04:48   LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 10:04:48   Error 270 for archive log file 6 to 'STLBAST'
2015-DEC-01 10:04:48   ARCa: Completed archiving thread 1 sequence 57397 (10616070916651-10616070982261)
2015-DEC-01 10:04:49   ARCb: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)
2015-DEC-01 10:04:49   ARCb: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
2015-DEC-01 10:04:49   FAL[server, ARCb]: Error 270 creating remote archivelog file 'STLBAST'
2015-DEC-01 10:10:49   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (270)

Step3: Login to Standby Database server and check the maximum archive log sequence that have already applied in Standby Database using following query

SELECT max(sequence#) AS "STANDBY", applied
FROM v$archived_log GROUP BY applied; 

or

SELECT THREAD#, SEQUENCE#, APPLIED
FROM V$ARCHIVED_LOG;

1 57405 YES
1 57404 YES
1 57403 YES
1 57402 YES
1 57401 YES
1 57400 YES

Step4: Purge old archive log that already applied in standby Database. Now check alert log in both primary and standby Database servers.


Thanks......Cheer

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

Problem:

----Using SCOTT Database user getting the following error---

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Solution

----execute the follwoing process using SYS user---

SQL> exec dbms_network_acl_admin.create_acl(acl => 'resolve.xml',description => 'resolve acl', principal =>'SCOTT', is_grant => true, privilege => 'resolve', start_date   => SYSTIMESTAMP);

PL/SQL procedure successfully completed.

SQL> exec dbms_network_acl_admin.assign_acl(acl => 'resolve.xml', host =>'*');

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'resolve.xml',principal => 'SCOTT',is_grant => true,privilege => 'connect');


----try to connect using SCOTT user----

select utl_inaddr.get_host_address,utl_inaddr.get_host_name
from dual


SELECT host, lower_port, upper_port, acl FROM dba_network_acls;


----Reference Query---

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;


------DELETE Privilege from ACL--------------

BEGIN
  DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE(
        acl         => 'resolve.xml',
        principal   => 'SCOTT');
END;

------DROP ACL---------------
DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
   acl           IN VARCHAR2);
   
BEGIN
   DBMS_NETWORK_ACL_ADMIN.DROP_ACL(
      acl => 'resolve.xml');
END;   

BEGIN
   DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(
     host        => '*.us.oracle.com',
     lower_port  => 80);
END;

Automatice Table creation with current date and time in Oracle Database

Solution:

Step1: Define a variable to keep date value with time

Declare
select_s VARCHAR2(2556);
v_sysdate VARCHAR2(10) := to_char(SYSDATE ,'MMDDYYYY:HH:MM');
BEgin
select_s := 'CREATE TABLE emp_'||v_sysdate||' AS SELECT * FROM scott.emp';

EXECUTE IMMEDIATE select_s;
COMMIT;
END;

Step2: Pass this program into a schedule to execute  

 -- Shell Script. 
 DBMS_SCHEDULER.create_program (
    program_name        => 'test_executable_prog',
    program_type        => 'EXECUTABLE',
    program_action      => '/u01/app/oracle/dba/table_backup.sh',
    number_of_arguments => 0,
    enabled             => TRUE,
    comments            => 'Program to backup table using a shell script.'); 

-- Create jobs.
BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Job defined entirely by the CREATE JOB procedure.');

Find Database Session Idle/Inactive more then 2 hours

### Find the Database sessions who are Idle more than two hours but connected through application###

select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate ;',logon_time,machine,status,osuser,lockwait
from v$session
where machine  like 'SBLC%'
and status ='INACTIVE'
and logon_time

Note: Here machine name should be application servers name.

###Kill the found session###

alter system kill session '656,31665' immediate ;

alter system kill session '1011,2111' immediate ;

alter system kill session '3478,45629' immediate ;

alter system kill session '5979,43717' immediate ;

alter system kill session '6293,17609' immediate ;

If any issue just give me a buzz....cheers.... 

Change Database User Password Expiry to Unlimited in Oracle 11gR2/10gR2

Step1: Check Database user profile

select username,expiry_date,created,profile from dba_users order by expiry_date desc

Step2: Check Password life time for above profile

select * from dba_profiles where profile = 'DEFAULT' and resource_name LIKE 'PASSWORD_LIFE_TIME';

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

Step3: Set Password life time for the profile to unlimited

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

[Solved] Error 6 initializing SQL*Plus SP2-0667: Message file sp1.msb not found

Error:

Error 6 initializing SQL*Plus
SP2-0667: Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software director

[oracle@bachapp mesg]$ /u01/app/oracle/product/11.2.0/db_1/bin/sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Reason:
ORACLE_HOME and ORACLE_SID not set properly.

Solution:

[oracle@bachapp mesg]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/
[oracle@bachapp mesg]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@bachapp mesg]$ export ORACLE_SID=orcl
[oracle@bachapp mesg]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 31 11:16:34 2014

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

Enter user-name: /as sysdba
Connected to an idle instance.